--- title: ClickHouse description: ClickHouse `dlt` destination keywords: [ clickhouse, destination, data warehouse ] --- # ClickHouse ## Install dlt with ClickHouse **To install the DLT library with ClickHouse dependencies:** ```sh pip install "dlt[clickhouse]" ``` ## Destination capabilities The following table shows the capabilities of the Clickhouse destination: | Feature | Value | More | |---------|-------|------| | Preferred loader file format | jsonl | [File formats](../file-formats/) | | Supported loader file formats | parquet, jsonl, model | [File formats](../file-formats/) | | Preferred staging file format | jsonl | [File formats](../file-formats/) | | Supported staging file formats | parquet, jsonl | [File formats](../file-formats/) | | Has case sensitive identifiers | True | [Naming convention](../../general-usage/naming-convention#case-sensitive-and-insensitive-destinations) | | Supported merge strategies | delete-insert, scd2 | [Merge strategy](../../general-usage/merge-loading#merge-strategies) | | Supported replace strategies | truncate-and-insert, insert-from-staging | [Replace strategy](../../general-usage/full-loading#choosing-the-correct-replace-strategy-for-your-full-load) | | Sqlglot dialect | clickhouse | [Dataset access](../../general-usage/dataset-access/dataset) | | Supports tz aware datetime | True | [Timestamps and Timezones](../../general-usage/schema#handling-of-timestamp-and-time-zones) | | Supports naive datetime | True | [Timestamps and Timezones](../../general-usage/schema#handling-of-timestamp-and-time-zones) | *This table shows the supported features of the Clickhouse destination in dlt.* ## Setup guide ### 1. Initialize the dlt project Let's start by initializing a new `dlt` project as follows: ```sh dlt init chess clickhouse ``` `dlt init` command will initialize your pipeline with chess as the source and ClickHouse as the destination. The above command generates several files and directories, including `.dlt/secrets.toml` and a requirements file for ClickHouse. You can install the necessary dependencies specified in the requirements file by executing it as follows: ```sh pip install -r requirements.txt ``` or with `pip install "dlt[clickhouse]"`, which installs the `dlt` library and the necessary dependencies for working with ClickHouse as a destination. ### 2. Setup ClickHouse database To load data into ClickHouse, you need to create a ClickHouse database. While we recommend asking our GPT-4 assistant for details, we've provided a general outline of the process below: 1. You can use an existing ClickHouse database or create a new one. 2. To create a new database, connect to your ClickHouse server using the `clickhouse-client` command line tool or a SQL client of your choice. 3. Run the following SQL commands to create a new database, user, and grant the necessary permissions: ```sql CREATE DATABASE IF NOT EXISTS dlt; CREATE USER dlt IDENTIFIED WITH sha256_password BY 'Dlt*12345789234567'; GRANT CREATE, ALTER, SELECT, DELETE, DROP, TRUNCATE, OPTIMIZE, SHOW, INSERT, dictGet ON dlt.* TO dlt; GRANT SELECT ON INFORMATION_SCHEMA.COLUMNS TO dlt; GRANT CREATE TEMPORARY TABLE, S3 ON *.* TO dlt; ``` ### 3. Add credentials 1. Next, set up the ClickHouse credentials in the `.dlt/secrets.toml` file as shown below: ```toml [destination.clickhouse.credentials] database = "dlt" # The database name you created. username = "dlt" # ClickHouse username, default is usually "default". password = "Dlt*12345789234567" # ClickHouse password, if any. host = "localhost" # ClickHouse server host. port = 9000 # ClickHouse native TCP protocol port, default is 9000. http_port = 8443 # ClickHouse HTTP port, default is 9000. secure = 1 # Set to 1 if using HTTPS, else 0. ``` :::info Network Ports The `http_port` parameter specifies the port number to use when connecting to the ClickHouse server's HTTP interface. The default non-secure HTTP port for ClickHouse is `8123`. This is different from the default port `9000`, which is used for the native TCP protocol. You must additionally set `http_port` if you are not using external staging (i.e., you don't set the `staging` destination parameter in your pipeline). This is because dlt's built-in ClickHouse local storage staging uses the [clickhouse-connect](https://github.com/ClickHouse/clickhouse-connect) library, which communicates with ClickHouse over HTTP. Make sure your ClickHouse server is configured to accept HTTP connections on the port specified by `http_port`. For example: - If you set `http_port = 8123` (default non-secure HTTP port), then ClickHouse should be listening for HTTP requests on port 8123. - If you set `http_port = 8443`, then ClickHouse should be listening for secure HTTPS requests on port 8443. If you're using external staging, you can omit the `http_port` parameter, since clickhouse-connect will not be used in this case. For local development and testing with ClickHouse running locally, it is recommended to use the default non-secure HTTP port `8123` by setting `http_port=8123` or omitting the parameter. Please see the [ClickHouse network port documentation](https://clickhouse.com/docs/en/guides/sre/network-ports) for further reference. ::: 2. You can pass a database connection string similar to the one used by the `clickhouse-driver` library. The credentials above will look like this: ```toml # Keep it at the top of your TOML file, before any section starts destination.clickhouse.credentials="clickhouse://dlt:Dlt*12345789234567@localhost:9000/dlt?secure=1" ``` ### 3. Add configuration options You can set the following configuration options in the `.dlt/secrets.toml` file: ```toml [destination.clickhouse] dataset_table_separator = "___" # The default separator for dataset table names from the dataset. table_engine_type = "merge_tree" # The default table engine to use. dataset_sentinel_table_name = "dlt_sentinel_table" # The default name for sentinel tables. staging_use_https = true # Whether to connect to the staging bucket via https (defaults to True) select_sequential_consistency = 1 # Ensures read-after-write consistency on ClickHouse Cloud (defaults to 1) ``` ## Write disposition All [write dispositions](../../general-usage/incremental-loading#choosing-a-write-disposition) are supported. ## Data loading Data is loaded into ClickHouse using the most efficient method depending on the data source: - For local files, the `clickhouse-connect` library is used to directly load files into ClickHouse tables using the `INSERT` command. - For files in remote storage like S3, Google Cloud Storage, or Azure Blob Storage, ClickHouse table functions like `s3`, `gcs`, and `azureBlobStorage` are used to read the files and insert the data into tables. ### Read-after-write consistency By default, dlt sets `select_sequential_consistency=1` on all ClickHouse connections. This ensures that `SELECT` queries always see the results of preceding writes, even on ClickHouse Cloud (SharedMergeTree) or self-hosted clusters where queries may be routed to different nodes. If you are running read-only workloads and want to avoid the small metadata check overhead, set it to `0` in your configuration: ```toml [destination.clickhouse] select_sequential_consistency = 0 ``` ## Datasets ClickHouse does not support multiple datasets in one database; dlt relies on datasets to exist for multiple reasons. To make ClickHouse work with `dlt`, tables generated by `dlt` in your ClickHouse database will have their names prefixed with the dataset name, separated by the configurable `dataset_table_separator`. Additionally, a special sentinel table that doesn't contain any data will be created, so dlt knows which virtual datasets already exist in a clickhouse destination. :::tip `dataset_name` is optional for ClickHouse. When skipped `dlt` will create all tables without prefix. Note that staging dataset tables will still be prefixed with `_staging` (or other name that you configure). ::: ## Supported file formats - [JSONL](../file-formats/jsonl.md) is the preferred format for both direct loading and staging. - [Parquet](../file-formats/parquet.md) is supported for both direct loading and staging. The `clickhouse` destination has a few specific deviations from the default SQL destinations: 1. ClickHouse has an experimental `object` datatype, but we've found it to be a bit unpredictable, so the dlt `clickhouse` destination will load the `json` datatype to a `text` column. If you need this feature, get in touch with our Slack community, and we will consider adding it. 2. ClickHouse does not support the `time` datatype. Time will be loaded to a `text` column. 3. ClickHouse does not support the `binary` datatype. Binary will be loaded to a `text` column. When loading from JSONL, this will be a base64 string; when loading from parquet, this will be the `binary` object converted to `text`. 4. ClickHouse accepts adding columns to a populated table that aren’t null. 5. ClickHouse can produce rounding errors under certain conditions when using the float/double datatype. Make sure to use decimal if you can’t afford to have rounding errors. Loading the value 12.7001 to a double column with the loader file format jsonl set will predictably produce a rounding error, for example. ## Supported column hints ClickHouse supports the following [column hints](../../general-usage/schema#tables-and-columns): - `primary_key` - marks the column as part of the primary key. Multiple columns can have this hint to create a composite primary key. ## Choosing a table engine dlt defaults to the `MergeTree` table engine. You can specify an alternate table engine in two ways: ### Setting a default table engine in the configuration You can set a default table engine for all resources and dlt tables by adding the `table_engine_type` parameter to your ClickHouse credentials in the `.dlt/secrets.toml` file: ```toml [destination.clickhouse] # ... (other configuration options) table_engine_type = "merge_tree" # The default table engine to use. ``` ### Setting the table engine for specific resources You can also set the table engine for specific resources using the clickhouse_adapter, which will override the default engine set in `.dlt/secrets.toml` for that resource: ```py from dlt.destinations.adapters import clickhouse_adapter @dlt.resource() def my_resource(): ... clickhouse_adapter(my_resource, table_engine_type="merge_tree") ``` Supported values for `table_engine_type` are: - `merge_tree` (default) - creates tables using the `MergeTree` engine, suitable for most use cases. [Learn more about MergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree). - `shared_merge_tree` - creates tables using the `SharedMergeTree` engine, optimized for cloud-native environments with shared storage. This table is **only** available on ClickHouse Cloud, and it is the default selection if `merge_tree` is selected. [Learn more about SharedMergeTree](https://clickhouse.com/docs/en/cloud/reference/shared-merge-tree). - `replicated_merge_tree` - creates tables using the `ReplicatedMergeTree` engine, which supports data replication across multiple nodes for high availability. [Learn more about ReplicatedMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replication). This defaults to `shared_merge_tree` on ClickHouse Cloud. - `replacing_merge_tree` - creates tables using the `ReplacingMergeTree` engine, which deduplicates rows with the same sorting key during background merges. See [native deduplication with ReplacingMergeTree](#native-deduplication-with-replacingmergetree) below. [Learn more about ReplacingMergeTree](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/replacingmergetree). - Experimental support for the `Log` engine family with `stripe_log` and `tiny_log`. For local development and testing with ClickHouse running locally, the `MergeTree` engine is recommended. ### Native deduplication with ReplacingMergeTree When loading data with `append` write disposition, you can use ClickHouse's `ReplacingMergeTree` engine to handle deduplication and deletes at the storage engine level — without using dlt's `merge` write disposition. This is useful when you want ClickHouse to manage upserts natively, for example when appending CDC events or syncing data from an external source where rows may repeat. To enable this, combine `replacing_merge_tree` with dlt column hints: - **`primary_key`** — defines the dedup key. Rows with the same key are considered duplicates. - **`dedup_sort`** (optional) — specifies the version column. ClickHouse keeps the row with the **highest** value in this column. Must be a non-nullable `UInt*`, `Date`, or `DateTime` type. If omitted, ClickHouse uses last-write-wins semantics (the most recently inserted row survives). - **`hard_delete`** (optional) — specifies a `UInt8` deletion marker column. Rows where this column is `1` are filtered out when querying with `FINAL`. To physically remove deleted rows during background merges, set the `clean_deleted_rows` table setting (see example below). Requires `dedup_sort` to be set (ClickHouse constraint: `is_deleted` requires a version column). dlt automatically wires these hints into the `ReplacingMergeTree` engine parameters. :::note Dedup key and sorting key In ClickHouse, `ReplacingMergeTree` deduplicates based on the `ORDER BY` columns, not `PRIMARY KEY`. When no explicit sorting key is set via `clickhouse_adapter(sort=...)`, ClickHouse defaults `ORDER BY` to `PRIMARY KEY`, so `primary_key` effectively controls deduplication. However, if you set a custom sorting key with `clickhouse_adapter(sort=...)`, that sorting key becomes the dedup key and may override your `primary_key` intent. Avoid combining `sort` with `replacing_merge_tree` unless you want the sorting key to define which rows are duplicates. ::: ```py import dlt from dlt.destinations.adapters import clickhouse_adapter @dlt.resource( write_disposition="append", primary_key="id", columns={ "version": {"dedup_sort": "desc", "nullable": False}, "is_deleted": {"hard_delete": True, "nullable": False}, }, ) def events(data): yield from data clickhouse_adapter( events, table_engine_type="replacing_merge_tree", settings={"clean_deleted_rows": "Always"}, ) pipeline = dlt.pipeline("my_pipeline", destination="clickhouse") # first load pipeline.run(events([ {"id": 1, "name": "Alice", "version": 1, "is_deleted": False}, {"id": 2, "name": "Bob", "version": 1, "is_deleted": False}, ])) # second load: update Alice, delete Bob pipeline.run(events([ {"id": 1, "name": "Alice Updated", "version": 2, "is_deleted": False}, {"id": 2, "name": "Bob", "version": 2, "is_deleted": True}, ])) ``` This generates `ENGINE = ReplacingMergeTree(version, is_deleted)`, so ClickHouse keeps only the row with the highest `version` per `id`. The `clean_deleted_rows` setting tells ClickHouse to physically remove rows marked as deleted during background merges. :::caution Data consistency `ReplacingMergeTree` deduplicates during **background merges**, which happen asynchronously. Until a merge runs, duplicate rows may be visible in queries. To get consistent (deduplicated) results, use the `FINAL` modifier: ```sql SELECT * FROM my_table FINAL ``` Without `clean_deleted_rows`, rows with `is_deleted=1` are only filtered out by `FINAL` queries but remain physically stored. With `clean_deleted_rows='Always'`, ClickHouse removes them during merges. This is different from dlt's `merge` write disposition, which deduplicates immediately during loading via explicit SQL statements. Choose `ReplacingMergeTree` when eventual consistency is acceptable and you want ClickHouse to manage deduplication natively. ::: ## Sorting and partitioning You can use the `clickhouse_adapter` to specify a [sorting](https://clickhouse.com/docs/engines/table-engines/mergetree-family/mergetree#order_by) and/or [partition](https://clickhouse.com/docs/engines/table-engines/mergetree-family/custom-partitioning-key) key: ```py from dlt.destinations.adapters import clickhouse_adapter @dlt.resource def my_resource(): ... clickhouse_adapter( my_resource, sort = sorting_key, partition = partition_key ) ``` `sort` and `partition` are used to generate the `ORDER BY` and `PARTITION BY` clauses of the table creation statement, and they accept either a **sequence of column names** or a **SQL expression**: 1. **sequence of column names:** recommended if column transformations are not required 2. **SQL expression:** use if column transformations are required The two examples below show both approaches to adapt `my_resource`: ```py @dlt.resource( columns={ "timestamp": {"nullable": False}, "town": {"nullable": False}, "street": {"nullable": False} } ) def my_resource(): ... ``` ### Example 1: sequence of column names ```py clickhouse_adapter( my_resource, sort=["timestamp", "street"], partition=["town"] ) ``` Corresponding SQL: ```sql CREATE TABLE ... ... ORDER BY (timestamp, street) PARTITION by town ``` ### Example 2: SQL expression ```py clickhouse_adapter( my_resource, sort="(upper(town), street)", partition="toYYYYMMDD(timestamp)" ) ``` Corresponding SQL: ```sql CREATE TABLE ... ... ORDER BY (upper(town), street) PARTITION BY toYYYYMMDD(timestamp) ``` ### Usage notes SQL expressions are used **as is** to generate the SQL clauses. Hence, when providing a SQL expression, use normalized column names: ```py @dlt.resource(columns={"TIMESTAMP": {"nullable": False}}) # non-normalized column name (upper case) def my_resource(): ... clickhouse_adapter(my_resource, partition="toYYYYMMDD(timestamp)") # RIGHT: normalized column name (lower case) clickhouse_adapter(my_resource, partition="toYYYYMMDD(TIMESTAMP)") # WRONG: non-normalized column name (lower case) ``` :::note - The sorting/partitioning key can only be set when the table is first created. The value for `sort`/`partition` is ignored for existing tables. - We explicitly mark the sorting/partition columns as **not nullable** in the examples above, because, by default, ClickHouse does not allow nullable columns in the sorting/partition key. Set `allow_nullable_key` to `True` in your [table settings](#mergetree-table-settings) if you insist on nullable key columns. ::: ### `sort` and `partition` column hints `dlt` automatically creates `sort`/`partition` [column hints](../../general-usage/schema.md#tables-and-columns) for columns present in the `sort`/`partition` value provided to `clickhouse_adapter` (when this value is a SQL expression, we parse it to extract the column names). Although it's possible to set `sort`/`partition` column hints directly, we recommend using `clickhouse_adapter` instead. If you still choose to set `sort`/`partition` column hints yourself, know that: - columns are added to the `ORDER BY`/`PARTITION BY` clause in order of appearance in the schema - they may be overridden/removed if you also use `clickhouse_adapter`: the adapter takes precedence, and it will set column hints in accordance with the values provided to its `sort`/`partition` parameters ## MergeTree table settings Use the `settings` parameter of the `clickhouse_adapter` to specify [MergeTree settings](https://clickhouse.com/docs/operations/settings/merge-tree-settings) for the table: ```py from dlt.destinations.adapters import clickhouse_adapter @dlt.resource def my_resource(): ... clickhouse_adapter( my_resource, settings = { "allow_nullable_key": True, "max_suspicious_broken_parts": 500, "deduplicate_merge_projection_mode": "ignore" } ) ``` The `settings` parameter is used to generate the `SETTINGS` clause of the table creation statement: ```sql CREATE TABLE ... ... SETTINGS allow_nullable_key = true, max_suspicious_broken_parts = 500, deduplicate_merge_projection_mode = 'ignore' ``` ## Column codecs Use the `codecs` parameter of the `clickhouse_adapter` to specify [codecs](https://clickhouse.com/docs/sql-reference/statements/create/table#column_compression_codec) for the table's columns: ```py from dlt.destinations.adapters import clickhouse_adapter @dlt.resource def my_resource(): ... clickhouse_adapter( my_resource, codecs = { "town": "ZSTD(3)", "number": "Delta, ZSTD(2)" } ) ``` The codecs are used in the table creation statement as follows: ```sql CREATE TABLE my_resource ( `town` String CODEC(ZSTD(3)), `street` String, -- no codec specified `number` Int64 CODEC(Delta, ZSTD(2)) ) ... ``` ## Staging support ClickHouse supports Amazon S3, Google Cloud Storage, and Azure Blob Storage as file staging destinations. `dlt` will upload Parquet or JSONL files to the staging location and use ClickHouse table functions to load the data directly from the staged files. Please refer to the filesystem documentation to learn how to configure credentials for the staging destinations: - [Amazon S3](./filesystem.md#aws-s3) - [Google Cloud Storage](./filesystem.md#google-storage) - [Azure Blob Storage](./filesystem.md#azure-blob-storage) To run a pipeline with staging enabled: ```py pipeline = dlt.pipeline( pipeline_name='chess_pipeline', destination='clickhouse', staging='filesystem', # add this to activate staging dataset_name='chess_data' ) ``` ### Using Google Cloud or S3-compatible storage as a staging area dlt supports using S3-compatible storage services, including Google Cloud Storage (GCS), as a staging area when loading data into ClickHouse. This is handled automatically by ClickHouse's [GCS table function](https://clickhouse.com/docs/en/sql-reference/table-functions/gcs), which dlt uses under the hood. The ClickHouse GCS table function only supports authentication using Hash-based Message Authentication Code (HMAC) keys, which is compatible with the Amazon S3 API. To enable this, GCS provides an S3 compatibility mode that emulates the S3 API, allowing ClickHouse to access GCS buckets via its S3 integration. For detailed instructions on setting up S3-compatible storage with dlt, including AWS S3, MinIO, and Cloudflare R2, refer to the [dlt documentation on filesystem destinations](../../dlt-ecosystem/destinations/filesystem#using-s3-compatible-storage). To set up GCS staging with HMAC authentication in dlt: 1. Create HMAC keys for your GCS service account by following the [Google Cloud guide](https://cloud.google.com/storage/docs/authentication/managing-hmackeys#create). 2. Configure the HMAC keys (`aws_access_key_id` and `aws_secret_access_key`) as well as `endpoint_url` in your dlt project's ClickHouse destination settings in `config.toml`, similar to how you would configure AWS S3 credentials: ```toml [destination.filesystem] bucket_url = "s3://my_awesome_bucket" [destination.filesystem.credentials] aws_access_key_id = "JFJ$$*f2058024835jFffsadf" aws_secret_access_key = "DFJdwslf2hf57)%$02jaflsedjfasoi" project_id = "my-awesome-project" endpoint_url = "https://storage.googleapis.com" ``` :::warning When configuring the `bucket_url` for S3-compatible storage services like Google Cloud Storage (GCS) with ClickHouse in dlt, ensure that the URL is prepended with `s3://` instead of `gs://`. This is because the ClickHouse GCS table function requires the use of HMAC credentials, which are compatible with the S3 API. Prepending with `s3://` allows the HMAC credentials to integrate properly with dlt's staging mechanisms for ClickHouse. ::: When using S3 for a staging area you can alternatively have ClickHouse authenticate using Role-based access with the [supported](https://clickhouse.com/docs/sql-reference/table-functions/s3#using-s3-credentials-clickhouse-cloud) `extra_credentials` argument by setting this with the destination credentials: ```py import dlt from dlt.destinations import clickhouse destination = clickhouse( credentials={ # Other credentials you need "s3_extra_credentials": { 'role_arn': 'arn:your:role' # The AWS Role assumed by ClickHouse } } ) pipeline = dlt.pipeline( pipeline_name='chess_pipeline', destination=destination, staging='filesystem', # add this to activate staging dataset_name='chess_data' ) ``` ### dbt support Integration with [dbt](../transformations/dbt/dbt.md) is generally supported via dbt-clickhouse but not tested by us. Note how we support datasets by prefixing the table names. You should take it into account when writing models (or use empty dataset to avoid prefixing). ### Syncing of `dlt` state This destination fully supports [dlt state sync](../../general-usage/state#syncing-state-with-destination). ## Additional Setup guides - [Load data from Notion to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/notion/load-data-with-python-from-notion-to-clickhouse) - [Load data from Bitbucket to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/bitbucket/load-data-with-python-from-bitbucket-to-clickhouse) - [Load data from Shopify to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/shopify_dlt/load-data-with-python-from-shopify_dlt-to-clickhouse) - [Load data from Salesforce to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/salesforce/load-data-with-python-from-salesforce-to-clickhouse) - [Load data from Soundcloud to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/soundcloud/load-data-with-python-from-soundcloud-to-clickhouse) - [Load data from Adobe Commerce (Magento) to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/magento/load-data-with-python-from-magento-to-clickhouse) - [Load data from Airtable to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/airtable/load-data-with-python-from-airtable-to-clickhouse) - [Load data from Sentry to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/sentry/load-data-with-python-from-sentry-to-clickhouse) - [Load data from Apple App-Store Connect to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/apple_app_store_connect/load-data-with-python-from-apple_app_store_connect-to-clickhouse) - [Load data from Qualtrics to ClickHouse in python with dlt](https://dlthub.com/docs/pipelines/qualtrics/load-data-with-python-from-qualtrics-to-clickhouse)