SingleStore configurations
Performance Optimizations
SingleStore Physical Database Schema Design documentation is helpful if you want to use specific options (that are described below) in your dbt project.
Storage type
SingleStore supports two storage types: In-Memory Rowstore and Disk-based Columnstore (the latter is default). See the docs for details. The dbt-singlestore adapter allows you to specify which storage type your table materialization would rely on using storage_type
config parameter.
{{ config(materialized='table', storage_type='rowstore') }}
select ...
Keys
SingleStore tables are sharded and can be created with various column definitions. The following options are supported by the dbt-singlestore adapter, each of them accepts column_list
(a list of column names) as an option value. Please refer to Creating a Columnstore Table for more informartion on various key types in SingleStore.
primary_key
(translated toPRIMARY KEY (column_list)
)sort_key
(translated toKEY (column_list) USING CLUSTERED COLUMNSTORE
)shard_key
(translated toSHARD KEY (column_list)
)unique_table_key
(translated toUNIQUE KEY (column_list)
)
{{
config(
primary_key=['id', 'user_id'],
shard_key=['id']
)
}}
select ...
{{
config(
materialized='table',
unique_table_key=['id'],
sort_key=['status'],
)
}}
select ...
Indexes
Similarly to the Postgres adapter, table models, incremental models, seeds, and snapshots may have a list of indexes
defined. Each index can have the following components:
columns
(list, required): one or more columns on which the index is definedunique
(boolean, optional): whether the index should be declared uniquetype
(string, optional): a supported index type,hash
orbtree
As SingleStore tables are sharded, there are certain limitations to indexes creation, see the docs for more details.
{{
config(
materialized='table',
shard_key=['id'],
indexes=[{'columns': ['order_date', 'id']}, {'columns': ['status'], 'type': 'hash'}]
)
}}
select ...
Other options
You can specify the character set and collation for the table using charset
and/or collation
options. Supported values for charset
are binary
, utf8
, and utf8mb4
. Supported values for collation
can be viewed as the output of SHOW COLLATION
SQL query. Default collations for the corresponding charcter sets are binary
, utf8_general_ci
, and utf8mb4_general_ci
.
{{
config(
charset='utf8mb4',
collation='utf8mb4_general_ci'
)
}}
select ...