Transform Data with dbt

The dbt-singlestore adapter can be used to connect with your SingleStore database to build data transformation pipelines using dbt. dbt provides a development environment to create transformation workflows on data that is already in SingleStore, 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:

SingleStore

dbt

7.5.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 SingleStore cluster.

No

user

The SingleStore database username.

No

password

Password of the SingleStore user.

No

database

The name of the SingleStore 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 SingleStore, 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.