Connect dlt to ClickHouse

## Supported column hints[​](#supported-column-hints "Direct link to Supported column hints")

ClickHouse supports the following [column hints](https://dlthub.com/docs/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.

## Table engine[​](#table-engine "Direct link to Table engine")

By default, tables are created using the `ReplicatedMergeTree` table engine in ClickHouse. You can specify an alternate table engine using the `table_engine_type` with the clickhouse adapter:

```
from dlt.destinations.adapters import clickhouse_adapter

@dlt.resource()
def my_resource():
  ...

clickhouse_adapter(my_resource, table_engine_type="merge_tree")
```

Supported values are:

- `merge_tree` - creates tables using the `MergeTree` engine
- `replicated_merge_tree` (default) - creates tables using the `ReplicatedMergeTree` engine

## Staging support[​](#staging-support "Direct link to 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](https://dlthub.com/docs/dlt-ecosystem/destinations/filesystem#aws-s3)
- [Google Cloud Storage](https://dlthub.com/docs/dlt-ecosystem/destinations/filesystem#google-storage)
- [Azure Blob Storage](https://dlthub.com/docs/dlt-ecosystem/destinations/filesystem#azure-blob-storage)

To run a pipeline with staging enabled:

```
pipeline = dlt.pipeline(
  pipeline_name='chess_pipeline',
  destination='clickhouse',
  staging='filesystem',  # add this to activate staging
  dataset_name='chess_data'
)
```

### Using Google Cloud Storage as a staging area[​](#using-google-cloud-storage-as-a-staging-area "Direct link to Using Google Cloud Storage as a staging area")

dlt supports using 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/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. To enable this, GCS provides an S3 compatibility mode that emulates the Amazon S3 API. ClickHouse takes advantage of this to allow accessing GCS buckets via its S3 integration.

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 as well as the `client_email`, `project_id` and `private_key` for your service account in your dlt project's ClickHouse destination settings in `config.toml`:

```
[destination.filesystem]
bucket_url = "gs://dlt-ci"

[destination.filesystem.credentials]
project_id = "a-cool-project"
client_email = "[email protected]"
private_key = "-----BEGIN PRIVATE KEY-----\nMIIEvQIBADANBgkaslkdjflasjnkdcopauihj...wEiEx7y+mx\nNffxQBqVVej2n/D93xY99pM=\n-----END PRIVATE KEY-----\n"

[destination.clickhouse.credentials]
database = "dlt"
username = "dlt"
password = "Dlt*12345789234567"
host = "localhost"
port = 9440
secure = 1
gcp_access_key_id = "JFJ$$*f2058024835jFffsadf"
gcp_secret_access_key = "DFJdwslf2hf57)%$02jaflsedjfasoi"
```

Note: In addition to the HMAC keys `bashgcp_access_key_id` and `gcp_secret_access_key`), you now need to provide the `client_email`, `project_id` and `private_key` for your service account under `[destination.filesystem.credentials]`. This is because the GCS staging support is now implemented as a temporary workaround and is still unoptimized.

dlt will pass these credentials to ClickHouse which will handle the authentication and GCS access.

There is active work in progress to simplify and improve the GCS staging setup for the ClickHouse dlt destination in the future. Proper GCS staging support is being tracked in these GitHub issues:

- Make filesystem destination  [work](https://github.com/dlt-hub/dlt/issues/1272) with gcs in s3 compatibility mode
- Google Cloud Storage staging area [support](https://github.com/dlt-hub/dlt/issues/1181)

### Dbt support[​](#dbt-support "Direct link to Dbt support")

Integration with [dbt](https://dlthub.com/docs/dlt-ecosystem/transformations/dbt/) is generally supported via dbt-clickhouse.

### Syncing of `dlt` state[​](#syncing-of-dlt-state "Direct link to syncing-of-dlt-state")

This destination fully supports [dlt](https://dlthub.com/docs/general-usage/state#syncing-state-with-destination) state sync.

- [Install dlt with ClickHouse](#install-dlt-with-clickhouse)
  - [To Install the `dlt` library with ClickHouse dependencies:](#to-install-the-dlt-library-with-clickhouse-dependencies)- [Setup guide](#setup-guide)
    - [Initialize the dlt Project](#1-initialize-the-dlt-project)- [Setup ClickHouse Database](#2-setup-clickhouse-database)- [Add credentials](#3-add-credentials)- [Write disposition](#write-disposition)- [Data loading](#data-loading)- [Datasets](#datasets)- [Supported file formats](#supported-file-formats)- [Supported column hints](#supported-column-hints)- [Table engine](#table-engine)- [Staging support](#staging-support)
                  - [Using Google Cloud Storage as a staging area](#using-google-cloud-storage-as-a-staging-area)- [Dbt support](#dbt-support)- [Syncing of `dlt` state](#syncing-of-dlt-state)

Was this page helpful?