Integrating dbt and ClickHouse

ruimsramos

Integrating dbt and ClickHouse

In this we will be following the integration steps to use dbt and clickouse with sample IMDB data.

Configure ClickHouse sources

Setup clickhouse check this article if you would like more information on this product.

Then connect with a client and run the following DDL scripts

CREATE DATABASE imdb;

CREATE TABLE imdb.actors
(
    id         UInt32,
    first_name String,
    last_name  String,
    gender     FixedString(1)
) ENGINE = MergeTree ORDER BY (id, first_name, last_name, gender);

CREATE TABLE imdb.directors
(
    id         UInt32,
    first_name String,
    last_name  String
) ENGINE = MergeTree ORDER BY (id, first_name, last_name);

CREATE TABLE imdb.genres
(
    movie_id UInt32,
    genre    String
) ENGINE = MergeTree ORDER BY (movie_id, genre);

CREATE TABLE imdb.movie_directors
(
    director_id UInt32,
    movie_id    UInt64
) ENGINE = MergeTree ORDER BY (director_id, movie_id);

CREATE TABLE imdb.movies
(
    id   UInt32,
    name String,
    year UInt32,
    rank Float32 DEFAULT 0
) ENGINE = MergeTree ORDER BY (id, name, year);

CREATE TABLE imdb.roles
(
    actor_id   UInt32,
    movie_id   UInt32,
    role       String,
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree ORDER BY (actor_id, movie_id);

After creating the source tables lets fill them with data from AWS, running the following code.

INSERT INTO imdb.actors
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/imdb/imdb_ijs_actors.tsv.gz',
'TSVWithNames');

INSERT INTO imdb.directors
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/imdb/imdb_ijs_directors.tsv.gz',
'TSVWithNames');

INSERT INTO imdb.genres
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/imdb/imdb_ijs_movies_genres.tsv.gz',
'TSVWithNames');

INSERT INTO imdb.movie_directors
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/imdb/imdb_ijs_movies_directors.tsv.gz',
        'TSVWithNames');

INSERT INTO imdb.movies
SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/imdb/imdb_ijs_movies.tsv.gz',
'TSVWithNames');

INSERT INTO imdb.roles(actor_id, movie_id, role)
SELECT actor_id, movie_id, role
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/imdb/imdb_ijs_roles.tsv.gz',
'TSVWithNames');

Setup DBT

Starting by setting up DBT environment

pip install dbt-core
pip install dbt-clickhouse

Init the dbt project

dbt init imdb

Update the file dbt_project.yml and make sure to add the actors

models:
  imdb:
    # Config indicated by + and applies to all files under models/example/
    actors:
      +materialized: view

Create the following file models/actors/schema.yml with the following content

version: 2

sources:
- name: imdb
  tables:
  - name: directors
  - name: actors
  - name: roles
  - name: movies
  - name: genres
  - name: movie_directors

Create the following file models/actors/actor_summary.sql with the content

{{ config(order_by='(updated_at, id, name)', engine='MergeTree()', materialized='table') }}

with actor_summary as (
SELECT id,
    any(actor_name) as name,
    uniqExact(movie_id)    as num_movies,
    avg(rank)                as avg_rank,
    uniqExact(genre)         as genres,
    uniqExact(director_name) as directors,
    max(created_at) as updated_at
FROM (
        SELECT {{ source('imdb', 'actors') }}.id as id,
                concat({{ source('imdb', 'actors') }}.first_name, ' ', {{ source('imdb', 'actors') }}.last_name) as actor_name,
                {{ source('imdb', 'movies') }}.id as movie_id,
                {{ source('imdb', 'movies') }}.rank as rank,
                genre,
                concat({{ source('imdb', 'directors') }}.first_name, ' ', {{ source('imdb', 'directors') }}.last_name) as director_name,
                created_at
        FROM {{ source('imdb', 'actors') }}
                    JOIN {{ source('imdb', 'roles') }} ON {{ source('imdb', 'roles') }}.actor_id = {{ source('imdb', 'actors') }}.id
                    LEFT OUTER JOIN {{ source('imdb', 'movies') }} ON {{ source('imdb', 'movies') }}.id = {{ source('imdb', 'roles') }}.movie_id
                    LEFT OUTER JOIN {{ source('imdb', 'genres') }} ON {{ source('imdb', 'genres') }}.movie_id = {{ source('imdb', 'movies') }}.id
                    LEFT OUTER JOIN {{ source('imdb', 'movie_directors') }} ON {{ source('imdb', 'movie_directors') }}.movie_id = {{ source('imdb', 'movies') }}.id
                    LEFT OUTER JOIN {{ source('imdb', 'directors') }} ON {{ source('imdb', 'directors') }}.id = {{ source('imdb', 'movie_directors') }}.director_id
        )
GROUP BY id
)

select *
from actor_summary

Configure the clickstream connection on the following file ~/.dbt/profiles.yml

imdb:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: imdb_dbt
      host: localhost
      port: 8123
      user: default
      password: ''
      secure: False

After this updates run the dbt debug command.
To make sure the connection is working properly

dbt debug
00:31:58  Running with dbt=1.7.6
00:31:58  dbt version: 1.7.6
00:31:58  python version: 3.11.6
00:31:58  python path: /home/rramos/Development/local/dbt/bin/python
00:31:58  os info: Linux-6.6.10-zen1-1-zen-x86_64-with-glibc2.38
00:31:58  Using profiles dir at /home/rramos/.dbt
00:31:58  Using profiles.yml file at /home/rramos/.dbt/profiles.yml
00:31:58  Using dbt_project.yml file at /home/rramos/Development/local/dbt/imdb/dbt_project.yml
00:31:58  adapter type: clickhouse
00:31:58  adapter version: 1.7.1
00:31:58  Configuration:
00:31:58    profiles.yml file [OK found and valid]
00:31:58    dbt_project.yml file [OK found and valid]
00:31:58  Required dependencies:
00:31:58   - git [OK found]
...
00:31:58  Registered adapter: clickhouse=1.7.1
00:31:58    Connection test: [OK connection ok]

If the connection test passed properly, one just need to create the model via dbt.

dbt run

And you should have a similar output

dbt run
00:38:13  Running with dbt=1.7.6
00:38:13  Registered adapter: clickhouse=1.7.1
00:38:13  Unable to do partial parsing because a project config has changed
00:38:15  Found 1 model, 6 sources, 0 exposures, 0 metrics, 421 macros, 0 groups, 0 semantic models
00:38:15  
00:38:15  Concurrency: 1 threads (target='dev')
00:38:15  
00:38:15  1 of 1 START sql view model `imdb`.`actor_summary` ............................. [RUN]
00:38:15  1 of 1 OK created sql view model `imdb`.`actor_summary` ........................ [OK in 0.17s]
00:38:15  
00:38:15  Finished running 1 view model in 0 hours 0 minutes and 0.27 seconds (0.27s).
00:38:15  
00:38:15  Completed successfully
00:38:15  
00:38:15  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Test query the model

SELECT *
FROM imdb_dbt.actor_summary
WHERE num_movies > 5
ORDER BY avg_rank DESC

Conclusion

In this article I've went trough the process of setup a Clickhouse database and setup dbt to setup the models with IMDB test data for actors, directors, movies, etc.

This two systems work like a charm together. Clickstream shows great performance for analytical queries, and dbt compiles and runs your analytics code against your data platform, enabling you and your team to collaborate on a single source of truth for metrics, insights, and business definitions.

I would like to extend this exercise by incorporating github actions related with dbt test actions before promoting to production, and extending the model.

But that will be for another time.

Let me know you bump into some issues or have some improvement suggestions.

Integrating dbt and ClickHouse

Integrating dbt and ClickHouse

Configure ClickHouse sources

Setup DBT

Conclusion

References