Skip to main content

Connecting ClickHouse

  1. Create a dbt project. In this case we name this after our imdb source. When prompted, select clickhouse as the database source.

    [email protected]:~$ dbt init imdb

    16:52:40 Running with dbt=1.1.0
    Which database would you like to use?
    [1] clickhouse

    (Don't see the one you want? https://docs.getdbt.com/docs/available-adapters)

    Enter a number: 1
    16:53:21 No sample profile found for clickhouse.
    16:53:21
    Your new dbt project "imdb" was created!

    For more information on how to configure the profiles.yml file,
    please consult the dbt documentation here:

    https://docs.getdbt.com/docs/configure-your-profile
  2. cd into your project folder:

    cd imdb
  3. At this point, you will need the text editor of your choice. In the examples below, we use the popular VSCode. Opening the IMDB directory, you should see a collection of yml and sql files:

    New dbt project
  4. Update your dbt_project.yml file to specify our first model - actor_summary and set profile to clickhouse_imdb.

    dbt profiledbt profile
  5. We next need to provide dbt with the connection details for our ClickHouse instance. Add the following to your ~/.dbt/profiles.yml.

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

    Note the need to modify the user and password. There are additional available settings documented here.

  6. From the IMDB directory, execute the dbt debug command to confirm whether dbt is able to connect to ClickHouse.

    [email protected]:~/imdb$ dbt debug
    17:33:53 Running with dbt=1.1.0
    dbt version: 1.1.0
    python version: 3.10.1
    python path: /home/dale/.pyenv/versions/3.10.1/bin/python3.10
    os info: Linux-5.13.0-10039-tuxedo-x86_64-with-glibc2.31
    Using profiles.yml file at /home/dale/.dbt/profiles.yml
    Using dbt_project.yml file at /opt/dbt/imdb/dbt_project.yml

    Configuration:
    profiles.yml file [OK found and valid]
    dbt_project.yml file [OK found and valid]

    Required dependencies:
    - git [OK found]

    Connection:
    host: localhost
    port: 8123
    user: default
    schema: imdb_dbt
    secure: False
    verify: False
    Connection test: [OK connection ok]

    All checks passed!

    Confirm the response includes Connection test: [OK connection ok] indicating a successful connection.