Quick tip: Using dbt with SingleStoreDB

Abstract

This short article will show how to set up dbt for use with SingleStoreDB. We'll also quickly apply a dbt example to test the SingleStore dbt adapter.

Create a SingleStoreDB Cloud account

To use dbt with SingleStoreDB Cloud, we'll first create a free account from the portal. After we've logged in to the portal, we'll create a new workspace from + New Deployment.

On the next page, we can choose Starter or Standard. For Standard, we'll be presented with three main options:

1. Workspace Group Name. We'll call our group dbt Demo Group.
2. Cloud Provider. We have the option to use AWS, GCP or Azure. In this article, we'll use AWS.
3. Region. A drop-down menu provides a list of supported regions. We can choose a region or use the default.

At the bottom of the webpage, we'll click Next.

On the next page, we'll be presented with two main options:

1. Workspace Details. We'll call our workspace dbt-demo.
2. Size. S-00 will be fine for initial testing.

At the bottom of the page, we'll click Create Workspace.

After a short time, the Workspace Group should be successfully created and available.

Under the Overview tab (Figure 1),

if we scroll down, we'll see our dbt-demo workspace. Using the Connect pulldown, we'll see the information we need to connect to SingleStoreDB Cloud using various clients. We'll choose one option and make a note of the host.

In Figure 1, we can also see Access and Firewall tabs. We'll change these as follows:

• Access. We'll create a password using the Generate Strong Password button and save the password in a safe place.
• Firewall. For initial setup and testing, we'll allow Access from anywhere. We can change this later.

From the left-hand navigation pane, we'll select DEVELOP > Data Studio > Open SQL Editor and create a new database, as follows:

CREATE DATABASE IF NOT EXISTS dbt_demo;

Install dbt

First, we'll install dbt-singlestore, as follows:

pip install dbt-core==1.7
pip install dbt-singlestore==1.7

Create SingleStoreDB dbt profile

We'll now create a profiles.yml file and save it in the ~/.dbt directory. Here are the details we need:

jaffle_shop:
  outputs:
    dev:
      type: singlestore
      host: <host>
      port: 3306
      user: admin
      password: <password>
      database: dbt_demo
      schema: dbt_demo
      threads: 1
  target: dev

We'll replace <host> and <password> with the values from our SingleStoreDB Cloud account.

Testing the dbt project

First, we'll clone the following repository:

git clone https://github.com/dbt-labs/jaffle_shop

We'll now change to jaffle_Shop:

cd jaffle_shop

Next, we'll check our profile, as follows:

dbt debug

The output should be:

All checks passed!

We'll now run the following commands, in the order shown, one by one:

dbt seed

dbt run

dbt test

dbt docs generate

dbt docs serve

Each command should successfully complete.

Summary

Through a quick example, we have seen how dbt can be used with SingleStoreDB. If you are already a dbt professional user, then using your dbt skills with SingleStoreDB will be a breeze.