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.

Install dbt

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

pip install dbt-core==1.1

It may be necessary to use sudo if there are insufficient privileges to complete the installation.

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

pip install dbt-singlestore

We can now check the installation:

dbt --version

The output should be similar to the following:

Core:
  - installed: 1.1.0
  - latest:    1.2.1 - Update available!

Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - singlestore: 1.1.2 - Up to date!

We are ready to work with dbt.

Create a SingleStoreDB Cloud account

To use dbt with SingleStoreDB Cloud, we'll first create a free account from the portal. After we have logged in to the portal, we need to check that we can see the option to Create a new workspace group in the left-hand navigation pane, as shown in Figure 1.

In the centre of the webpage, we should also see the option to Create Workspace, as shown in Figure 2.

We'll click the Create Workspace button. On the next page, we'll be presented with three 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 three options:

1. Workspace Details. We'll call our workspace dbt-demo.
2. Size. S-00 will be fine for initial testing.
3. Confirm Workspace Group. We should check that the dbt Demo Group is selected in the drop-down menu.

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 3),

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

In Figure 3, 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 the SQL Editor and create a new database, as follows:

CREATE DATABASE dbt_demo;

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: <TO DO>
      port: 3306
      user: admin
      password: <TO DO>
      database: dbt_demo
      schema: dbt_demo
      threads: 1
  target: dev

We'll replace the <TO DO> for 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.