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

Refer to the dbt-singlestore GitHub repository for the compatibility matrix between SingleStore and dbt.

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:

Shell

pip install dbt-singlestore

You can also install the package from GitHub:

Shell

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:

YAML

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`	Refer to 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.

Refer to SingleStore Profile for more information.

Transform Data with dbt

On this page

Prerequisites

Download and Install the SingleStore dbt Adapter

Configure the SingleStore Profile

Schema and Concurrent Development

Was this article helpful?

On this page

Was this article helpful?