dbt-clickhouse
This plugin ports dbt functionality to Clickhouse.
We do not support older versions of Clickhouse. The plugin uses syntax that requires version 21 or newer.
Installation
Use your favorite Python package manager to install the app from PyPI, e.g.
pip install dbt-clickhouseSupported features
- Table materialization
- View materialization
- Incremental materialization
- Seeds
- Sources
- Docs generate
- Tests
- Snapshots
- Ephemeral materialization
Usage Notes
Database
The dbt model database.schema.table is not compatible with Clickhouse because Clickhouse does not support a schema.
So we use a simple model schema.table, where schema is the Clickhouse's database. Please, don't use default database!
Model Configuration
| Option | Description | Required? |
|---|---|---|
| engine | The table engine (type of table) to use when creating tables | Optional (default: MergeTree()) |
| order_by | A tuple of column names or arbitrary expressions. This allows you to create a small sparse index that helps find data faster. | Optional (default: tuple()) |
| partition_by | A partition is a logical combination of records in a table by a specified criterion. The partition key can be any expression from the table columns. | Optional |
| inserts_only | This property is relevant only for incremental materialization. If set to True, incremental updates will be inserted directly to the target table without creating intermediate table. This option has the potential of significantly improve performance and avoid memory limitations on big updates. | Optional |
Example Profile
your_profile_name:
target: dev
outputs:
dev:
type: clickhouse
schema: [database name]
# optional
port: [port] # default 8123
user: [user] # default 'default'
host: [db.clickhouse.com] # default localhost
password: [password] # default ''
verify: [verify] # default True
secure: [secure] # default False
connect_timeout: [10] # default 10 seconds.
Running Tests
This adapter passes all of dbt basic tests as presented in dbt's official docs: https://docs.getdbt.com/docs/contributing/testing-a-new-adapter#testing-your-adapter.
Note: The only feature that is not supported and not tested is Ephemeral materialization.
Tests running command:
pytest tests/integration
You can customize a few test params through environment variables. In order to provide custom params you'll need to create test.env file under root (remember not to commit this file!) and define the following env variables inside:
- HOST_ENV_VAR_NAME - Default=
localhost - USER_ENV_VAR_NAME - your ClickHouse username. Default=
default - PASSWORD_ENV_VAR_NAME - your ClickHouse password. Default=''
- PORT_ENV_VAR_NAME - ClickHouse client port. Default=8123
- RUN_DOCKER_ENV_VAR_NAME - Identify whether to run clickhouse-server docker image (see tests/docker-compose.yml). Default=False. Set it to True if you'd like to raise a docker image (assuming docker-compose is installed in your machine) during tests that launches a clickhouse-server. Note: If you decide to run a docker image you should set PORT_ENV_VAR_NAME to 10900 too.
Original Author
ClickHouse wants to thank @silentsokolov for creating this connector and for their valuable contributions.
Update 05/31/2022
- Incremental changes of an incremental model are loaded into a MergeTree table instead of in-memory temporary table. This removed memory limitations - Clickhouse recommends that in-memory table engines should not exceed 100 million rows.
- Incremental model supports 'inserts_only' mode where incremental changes are loaded directly to the target table instead of creating a temporary table for the changes and running another insert-into command. This mode is relevant only for immutable data, and can accelerate dramatically the performance of the incremental materialization.
- Fix update and delete in snapshots.