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 |
---|---|---|
| The IP address or the hostname of the SingleStoreDB cluster. | No |
| The SingleStoreDB database username. | No |
| Password of the SingleStoreDB user. | No |
| 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 |
| Yes | |
| 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.