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.

Overview of dbt-singlestore

  • 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: Supported
  • Minimum data platform version: v7.5

Installation and Distribution

Installing dbt-singlestore

pip is the easiest way to install the adapter:

pip install dbt-singlestore

Installing dbt-singlestore will also install dbt-core and any other dependencies.

Configuring dbt-singlestore

For SingleStore-specifc configuration please refer to SingleStore Configuration

For further info, refer to the GitHub repository: memsql/dbt-singlestore

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.

target: 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

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 }}__{{ }}
{%- 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.