Skip to main content

SingleStore setup

Vendor-supported plugin

Certain core functionality may vary. If you would like to report a bug, request a feature, or contribute, you can check out the linked repository and open an issue.

  • Maintained by: SingleStore, Inc.
  • Authors: SingleStore, Inc.
  • GitHub repo: memsql/dbt-singlestore
  • PyPI package: dbt-singlestore
  • Slack channel: db-singlestore
  • Supported dbt Core version: v1.0.0 and newer
  • dbt Cloud support: Not supported
  • Minimum data platform version: v7.5

Installing dbt-singlestore

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

Configuring dbt-singlestore

For SingleStore-specific configuration, please refer to SingleStore configs.

Set up a SingleStore Target

SingleStore targets should be set up using the following configuration in your profiles.yml file. If you are using SingleStore Managed Service, required connection details can be found on your Cluster Page under "Connect" -> "SQL IDE" tab.

~/.dbt/profiles.yml
singlestore:
target: dev
outputs:
dev:
type: singlestore
host: [hostname] # optional, default localhost
port: [port number] # optional, default 3306
user: [user] # optional, default root
password: [password] # optional, default empty
database: [database name] # required
schema: [prefix for tables that dbt will generate] # required
threads: [1 or more] # optional, default 1

It is recommended to set optional parameters as well.

Description of SingleStore Profile Fields

FieldRequiredDescription
typeYesMust be set to singlestore. This must be included either in profiles.yml or in the dbt_project.yml file.
hostNoThe host name of the SingleStore server to connect to.
userNoYour SingleStore database username.
passwordNoYour SingleStore database password.
databaseYesThe name of your database. If you are using custom database names in your models config, they must be created prior to running those models.
schemaYesThe string to prefix the names of generated tables if generate_alias_name macro is added (see below). If you are using a custom schema name in your model config, it will be concatenated with the one specified in profile using _.
threadsNoThe number of threads available to dbt.

Schema and Concurrent Development

SingleStore doesn't have a concept of schema that corresponds to one used in dbt (namespace within a database). schema in your profile is required for dbt to work correctly with your project metadata. For example, you will see it on "dbt docs" page, even though it's not present in the database.

In order to support concurrent development, schema can be used to prefix table names that dbt is building within your database. In order to enable this, add the following macro to your project. This macro will take the schema field of your profiles.yml file and use it as a table name prefix.

-- macros/generate_alias_name.sql
{% macro generate_alias_name(custom_alias_name=none, node=none) -%}
{%- if custom_alias_name is none -%}
{{ node.schema }}__{{ node.name }}
{%- else -%}
{{ node.schema }}__{{ custom_alias_name | trim }}
{%- endif -%}
{%- endmacro %}

Therefore, if you set schema=dev in your .dbt/profiles.yml file and run the customers model with the corresponding profile, dbt will create a table named dev__customers in your database.

0