Connect dlt to ClickHouse

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/data-ingestion/etl-tools/dlt-and-clickhouse.md)

Partner Integration

[dlt](https://dlthub.com/docs/intro) is an open-source library that you can add to your Python scripts to load data from various and often messy data sources into well-structured, live datasets.

## Install dlt with ClickHouse[​](#install-dlt-with-clickhouse "Direct link to Install dlt with ClickHouse")

### To Install the `dlt` library with ClickHouse dependencies:[​](#to-install-the-dlt-library-with-clickhouse-dependencies "Direct link to to-install-the-dlt-library-with-clickhouse-dependencies")

```
pip install "dlt[clickhouse]"
```

## Setup guide[​](#setup-guide "Direct link to Setup guide")

Initialize the dlt Project

### Initialize the dlt Project[​](#1-initialize-the-dlt-project "Direct link to Initialize the dlt Project")

Start by initializing a new `dlt` project as follows:

```
dlt init chess clickhouse
```

Note

This 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:

```
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.

Setup ClickHouse Database

### Setup ClickHouse Database[​](#2-setup-clickhouse-database "Direct link to Setup ClickHouse Database")

To load data into ClickHouse, you need to create a ClickHouse database. Here's a rough outline of what should you do:

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:

```
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;
```

Add credentials

### Add credentials[​](#3-add-credentials "Direct link to Add credentials")

Next, set up the ClickHouse credentials in the `.dlt/secrets.toml` file as shown below:

```
[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 HTTP port, default is 9000
http_port = 8443                         # HTTP Port to connect to ClickHouse server's HTTP interface. Defaults to 8443.
secure = 1                               # Set to 1 if using HTTPS, else 0.

[destination.clickhouse]
dataset_table_separator = "___"          # Separator for dataset table names from dataset.
```

HTTP\_PORT

The `http_port` parameter specifies the port number to use when connecting to the ClickHouse server's HTTP interface. This is different from default port 9000, which is used for the native TCP protocol.

You must set `http_port` if you're not using external staging (i.e. you don't set the staging parameter in your pipeline). This is because the built-in ClickHouse local storage staging uses the [clickhouse content](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 = 8443`, then ClickHouse should be listening for HTTP requests on port 8443. If you're using external staging, you can omit the `http_port` parameter, since clickhouse-connect won't be used in this case.

You can pass a database connection string similar to the one used by the `clickhouse-driver` library. The credentials above will look like this:

```
# 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"
```

## Write disposition[​](#write-disposition "Direct link to Write disposition")

All [write dispositions](https://dlthub.com/docs/general-usage/incremental-loading#choosing-a-write-disposition)
are supported.

Write dispositions in the dlt library define how the data should be written to the destination. There are three types of write dispositions:

**Replace**: This disposition replaces the data in the destination with the data from the resource. It deletes all the classes and objects and recreates the schema before loading the data. You can learn more about it [here](https://dlthub.com/docs/general-usage/full-loading).

**Merge**: This write disposition merges the data from the resource with the data at the destination. For `merge` disposition, you would need to specify a `primary_key` for the resource. You can learn more about it [here](https://dlthub.com/docs/general-usage/incremental-loading).

**Append**: This is the default disposition. It will append the data to the existing data in the destination, ignoring the `primary_key` field.

## Data loading[​](#data-loading "Direct link to 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.

## Datasets[​](#datasets "Direct link to Datasets")

`Clickhouse` doesn't support multiple datasets in one database, whereas `dlt` relies on datasets due to multiple reasons. In order 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, allowing `dlt` to recognize which virtual datasets already exist in a `Clickhouse` destination.

## Supported file formats[​](#supported-file-formats "Direct link to Supported file formats")

- [jsonl](https://dlthub.com/docs/dlt-ecosystem/file-formats/jsonl) is the preferred format for both direct loading and staging.
- [parquet](https://dlthub.com/docs/dlt-ecosystem/file-formats/parquet) 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 have found it to be a bit unpredictable, so the dlt clickhouse destination will load the complex 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` doesn't support the `time` datatype. Time will be loaded to a `text` column.
3. `Clickhouse` doesn't support the `binary` datatype. Instead, binary data will be loaded into a `text` column. When loading from `jsonl`, the binary data will be a base64 string, and when loading from parquet, the `binary` object will be 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 or double datatype. If you can't afford to have rounding errors, make sure to use the decimal datatype. For example, loading the value 12.7001 into a double column with the loader file format set to `jsonl` will predictably produce a rounding error.