SingleStoreDB v8.1

Transform Data with dbt

The dbt-singlestore adapter can be used to connect with your SingleStoreDB database to build data transformation pipelines using dbt. dbt provides a development environment to create transformation workflows on data that is already in SingleStoreDB, which dbt turns into tables and views through SELECT statements. See dbt documentation for more information.

Prerequisites

The dbt-singlestore adapter is compatible with the the following versions:

SingleStoreDB	dbt
7.6.x	1.0.1

Download and Install the SingleStore dbt Adapter

You can download the dbt-singlestore adapter from the Python Package Index repository (PyPI). Run the following command to install the adapter available on PyPI:

pip install dbt-singlestore

You can also install the package from GitHub:

pip install git+https://github.com/memsql/dbt-singlestore.git

Configure the SingleStore Profile

dbt uses the profiles.yml file as a standard profile for SQL data sources. Here's a sample configuration:

default:
  outputs:
    dev:
      type: singlestore
      host: <database_host>  
      port: <database_port>  
      user: <database_user>  
      password: <database_user_password>
      database: <database_name>  # required
      schema: <name_for_dbt_internal_processing>  # required
      threads: <number of threads>  # default: 1
  target: dev

Note

Always specify the type as singlestore. You must include the type either in profiles.yml or dbt_project.yml.

Configuration	Description	Required
`host`	The IP address or the hostname of the SingleStoreDB cluster.	No
`user`	The SingleStoreDB database username.	No
`password`	Password of the SingleStoreDB user.	No
`database`	The name of the SingleStoreDB database. If you are using custom database names in your model configuration, you must create them before running the models.	Yes
`schema`	See Schema and Concurrent Development.	Yes
`threads`	The number of threads available to dbt.	No

Schema and Concurrent Development

In SingleStoreDB, database and schema denote the same concepts. In dbt, a schema refers to a namespace within a database. Therefore, you must specify the schema in profile.yml for dbt to work with the project metadata.

To support concurrent development, prefix the table names that dbt is building within the database with schema. The following macro prefixes the table names with schema:

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

For example, if you have a model named customers and schema is set to dev, without this macro, dbt creates a table named customers in the database. When this macro is added to the project, dbt creates a table named dev__customers.

See SingleStore Profile for more information.

In this section: