Skip to main content

ClickHouse setup

Some core functionality may be limited. If you're interested in contributing, check out the source code for each repository listed below.

  • Maintained by: Community
  • Authors: Geoff Genz & Bentsi Leviav
  • GitHub repo: ClickHouse/dbt-clickhouse
  • PyPI package: dbt-clickhouse
  • Slack channel: #db-clickhouse
  • Supported dbt Core version: v0.19.0 and newer
  • dbt support: Not Supported
  • Minimum data platform version: n/a

Installing dbt-clickhouse

Use pip to install the adapter. Before 1.8, installing the adapter would automatically install dbt-core and any additional dependencies. Beginning in 1.8, installing an adapter does not automatically install dbt-core. This is because adapters and dbt Core versions have been decoupled from each other so we no longer want to overwrite existing dbt-core installations. Use the following command for installation:

python -m pip install dbt-core dbt-clickhouse

Configuring dbt-clickhouse

For Clickhouse-specific configuration, please refer to Clickhouse configs.

Connecting to ClickHouse

To connect to ClickHouse from dbt, you'll need to add a profile to your profiles.yml configuration file. Follow the reference configuration below to set up a ClickHouse profile:

profiles.yml
clickhouse-service:
target: dev
outputs:
dev:
type: clickhouse
schema: [ default ] # ClickHouse database for dbt models

# optional
host: [ <your-clickhouse-host> ] # Your clickhouse cluster url e.g., abc123.clickhouse.cloud. Defaults to `localhost`.
port: [ 8123 ] # Defaults to 8123, 8443, 9000, 9440 depending on the secure and driver settings
user: [ default ] # User for all database operations
password: [ <empty string> ] # Password for the user
secure: [ False ] # Use TLS (native protocol) or HTTPS (http protocol)

For a complete list of configuration options, see the ClickHouse documentation.

Create a dbt project

You can now use this profile in one of your existing projects or create a new one using:

dbt init project_name

Navigate to the project_name directory and update your dbt_project.yml file to use the profile you configured to connect to ClickHouse.

profile: 'clickhouse-service'

Test connection

Execute dbt debug with the CLI tool to confirm whether dbt is able to connect to ClickHouse. Confirm the response includes Connection test: [OK connection ok], indicating a successful connection.

Supported features

dbt features

TypeSupported?Details
ContractsYES
Docs generateYES
Most dbt-utils macrosYES(now included in dbt-core)
SeedsYES
SourcesYES
SnapshotsYES
TestsYES

Materializations

TypeSupported?Details
TableYESCreates a table. See below for the list of supported engines.
ViewYESCreates a view.
IncrementalYESCreates a table if it doesn't exist, and then writes only updates to it.
Microbatch incrementalYES
Ephemeral materializationYESCreates a ephemeral/CTE materialization. This model is internal to dbt and does not create any database objects.
Materialized ViewYES, ExperimentalCreates a materialized view.
Distributed table materializationYES, ExperimentalCreates a distributed table.
Distributed incremental materializationYES, ExperimentalIncremental model based on the same idea as distributed table. Note that not all strategies are supported, visit this for more info.
Dictionary materializationYES, ExperimentalCreates a dictionary.

Note: Community-developed features are labeled as experimental. Despite this designation, many of these features, like materialized views, are widely adopted and successfully used in production environments.

Documentation

See the ClickHouse documentation for more details on using the dbt-clickhouse adapter to manage your data model.

Contributing

We welcome contributions from the community to help improve the dbt-ClickHouse adapter. Whether you're fixing a bug, adding a new feature, or enhancing the documentation, your efforts are greatly appreciated!

Please take a moment to read our Contribution Guide to get started. The guide provides detailed instructions on setting up your environment, running tests, and submitting pull requests.

Was this page helpful?

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

0