ClickHouse
The ClickHouse destination connector syncs data from Airbyte sources to ClickHouse. This high-performance columnar database is designed for online analytical processing (OLAP). The connector writes correctly typed data directly to ClickHouse tables. This enables fast analytical queries on your replicated data.
This connector completely replaces the legacy v1 connector. It’s rebuilt using Airbyte’s Bulk CDK framework.
Version 2 improvements
Version 2.0.0 completely redesigns the ClickHouse destination connector architecture. It includes these significant improvements:
- All sync modes supported: Full Refresh (Overwrite and Append) and Incremental (Append and Append + Deduped) sync modes are now fully supported.
- Direct Load with typed columns: Airbyte writes data directly to typed columns that match your source schema. It no longer stores everything as JSON in raw tables. This improves query performance and reduces storage requirements.
- Improved performance: The new architecture loads data faster using ClickHouse’s native binary protocol and batch inserts.
- Active maintenance: The connector uses Airbyte’s modern CDK framework. The Airbyte team provides ongoing development and support.
Supported sync modes
| Sync mode | Supported? |
|---|---|
| Full Refresh - Overwrite | Yes |
| Full Refresh - Append | Yes |
| Full Refresh - Overwrite + Deduped | Yes |
| Incremental Sync - Append | Yes |
| Incremental Sync - Append + Deduped | Yes |
ClickHouse’s ReplacingMergeTree table engine handles deduplication. See Deduplication below for details.
Self-managed clusters
By default, the connector stores data only on the node receiving each insert. In a self-managed ClickHouse cluster, other replicas won’t know about the inserted rows. As a result, reads through a load balancer may return incomplete data. Table definitions (CREATE, ALTER, DROP) also apply only to the connected node.
Two advanced options make the connector cluster-aware. ClickHouse Cloud needs neither option because it uses SharedMergeTree and handles replication automatically.
- Enable Replication (
use_replicated_engines): ensures synced data is replicated across all cluster nodes. The replicated engine doesn’t include explicit Keeper path arguments, so the server must derive them. This works within a database using theReplicateddatabase engine. It also works whendefault_replica_path/default_replica_namemacros are configured on the server. This option requires ClickHouse Keeper (or ZooKeeper). - Cluster Name (
cluster_name): automatically propagates table definitions to all cluster nodes when provided. Clusters whose databases use the default Atomic engine require this option. Leave it empty when the target database uses theReplicateddatabase engine. In that case, DDL replicates through the database itself, so you don’t need a cluster name. The connector validates the name against the charset[A-Za-z0-9_.-]. It rejects the connection if the name contains invalid characters.
Typical combinations:
| Topology | Enable Replication | Cluster Name |
|---|---|---|
| Single node / ClickHouse Cloud | off | empty |
Cluster with Replicated database engine | on | empty |
Cluster with Atomic databases + remote_servers | on | set to your cluster name |
Deduplication
For reliable deduplication in Incremental - Append + Deduped mode, use a cursor column with one of these types:
- Integer types (
Int64, etc.) - Date
- Timestamp (
DateTime64)
For other cursor types, such as string, the connector uses the _airbyte_extracted_at timestamp for deduplication ordering. This fallback may not reflect your source data’s natural order accurately. You’ll also see a warning in the sync logs.
:::warning
Airbyte’s ClickHouse connector uses the ReplacingMergeTree table engine for deduplication.
Add the FINAL operator to your query string to guarantee deduplicated results at query time. For example:
SELECT * FROM your_table FINAL
Without this operator, your query may return duplicated or deleted results.
:::
Alternatively, you may be able to tune your merge settings to better match your query patterns.
Requirements
To use the ClickHouse destination connector, you need:
- A ClickHouse instance (ClickHouse Cloud or self-hosted)
- ClickHouse server version 21.8.10.19 or later. The connector is tested against ClickHouse 26.7.
- Network access from Airbyte to your ClickHouse instance
- A ClickHouse user with appropriate permissions (see below)
Setup guide
1. Configure network access
Make sure Airbyte can access your ClickHouse database.
| Airbyte deployment | Clickhouse deployment | Do this |
|---|---|---|
| Cloud | Cloud | Add Airbyte Cloud’s IP addresses to the allowlist in your ClickHouse Cloud settings. |
| Cloud | Self-managed | Configure your firewall to allow inbound connections on port 8443 (HTTPS) or 8123 (HTTP) from Airbyte Cloud’s IP addresses. |
| Self-managed | Cloud | Whitelist your Airbyte server’s public IP address in ClickHouse Cloud settings. |
| Self-managed | Self-managed | Ensure port 8443 (HTTPS) or 8123 (HTTP) is accessible from your Airbyte host. If both are in the same private network, configure security groups or firewall rules to allow traffic between them. |
If you can’t expose ClickHouse publicly, use SSH Tunneling through a bastion host that can reach ClickHouse.
2. Create a dedicated user
:::tip Create a dedicated ClickHouse user for Airbyte instead of using an existing user. This improves security and makes Airbyte’s database operations easier to audit. :::
Create a ClickHouse user for Airbyte with these permissions:
- Create and manage databases
- Create, alter, drop, and truncate tables
- Insert and select data
Run these SQL commands in your ClickHouse instance to create a user with the required permissions:
-- Create the user (replace 'your_password' with a secure password)
CREATE USER airbyte_user IDENTIFIED BY 'your_password';
-- If async_insert is enabled in ClickHouse, disable it for the Airbyte user to ensure connection checks and data syncs work correctly. This fixes the "Error: Failed to insert expected rows into check table. Actual written: 0" error.
ALTER USER airbyte_user SETTINGS async_insert = 0;
-- Grant permissions on the default database
GRANT CREATE ON * TO airbyte_user;
GRANT CREATE ON {database}.* TO airbyte_user;
GRANT ALTER ON {database}.* TO airbyte_user;
GRANT TRUNCATE ON {database}.* TO airbyte_user;
GRANT INSERT ON {database}.* TO airbyte_user;
GRANT SELECT ON {database}.* TO airbyte_user;
GRANT CREATE DATABASE ON {database}.* TO airbyte_user;
GRANT CREATE TABLE ON {database}.* TO airbyte_user;
GRANT DROP TABLE ON {database}.* TO airbyte_user;
Replace {database} with the database name configured in the connector settings. This is typically default.
If your Airbyte connections use custom namespaces, grant permissions for each namespace:
GRANT CREATE ON {namespace}.* TO airbyte_user;
GRANT ALTER ON {namespace}.* TO airbyte_user;
GRANT TRUNCATE ON {namespace}.* TO airbyte_user;
GRANT INSERT ON {namespace}.* TO airbyte_user;
GRANT SELECT ON {namespace}.* TO airbyte_user;
GRANT CREATE DATABASE ON {namespace}.* TO airbyte_user;
GRANT CREATE TABLE ON {namespace}.* TO airbyte_user;
GRANT DROP TABLE ON {namespace}.* TO airbyte_user;
Replace {namespace} with each custom namespace you plan to use.
3. Configure the connector
-
In Airbyte, select Destinations > ClickHouse.
-
Configure the destination with the following information.
- Hostname: Your ClickHouse server hostname, without a protocol prefix such as
http://orhttps:// - Port: ClickHouse HTTP port. The defaults are 8123 for HTTP and 8443 for HTTPS.
- Protocol (self-hosted only): Choose HTTP or HTTPS. In Airbyte Cloud, the platform manages and hides this option.
- Database: Target database name (default:
default) - Username: The ClickHouse user you created, for example,
airbyte_user - Password: The password for the ClickHouse user
- Enable JSON: Whether to use ClickHouse’s JSON type for object fields. This is recommended if your ClickHouse version supports it.
- Record Window Size (advanced): The maximum number of records written in one batch. Adjusting this value can affect performance. The batch size remains limited to 70 MB, regardless of this setting. Most users don’t need to change this value.
- Enable Replication (advanced): Enable this for self-managed ClickHouse clusters with multiple replicas. It ensures data is synchronised across all nodes. See Self-managed clusters for details.
- Cluster Name (advanced): Your ClickHouse cluster name. When provided, table definitions propagate to all cluster nodes. See Self-managed clusters for details.
- Hostname: Your ClickHouse server hostname, without a protocol prefix such as
4. Configure an SSH tunnel (optional)
:::warning SSH tunnelling support is currently in Beta. :::
If Airbyte can’t access your ClickHouse instance directly, use SSH tunnelling to establish a secure connection. In the connector configuration, enter your SSH host, port, username and authentication method. You can authenticate with a password or private key.
Output schema
Airbyte writes each stream to a separate ClickHouse table. It creates each table in the configured default database, typically default. Alternatively, it uses the database matching the stream namespace specified when you set up the connection.
The connector maps Airbyte data types to ClickHouse types as follows:
- Decimal types →
Decimal(38, 9)(38 digit precision with 9 decimal places) - Timestamp types →
DateTime64(3)(millisecond precision) - Object types →
JSONif you enable JSON in the connector configuration, otherwise →String - Integer types →
Int64 - Boolean types →
Bool - String types →
String - Union types →
String - Array types →
String
:::note For compatibility, the connector converts arrays and unions to strings. To query them as structured data, parse the string values using ClickHouse’s JSON functions. :::
Schema evolution
The connector supports automatic schema evolution. When the source schema changes, it adds new destination columns and modifies column types as needed.
Unlike most other Airbyte destinations, when a column is removed from the source, ClickHouse drops the column and its historical data from the destination table. Other destinations typically keep removed columns and write NULL for later rows. ClickHouse drops these columns because its ReplacingMergeTree engine deduplicates by deleting and reinserting records. Unlike other destinations, it doesn’t use MERGE queries. This table recreation approach requires the connector to drop obsolete columns.
Namespace support
This destination supports namespaces. Each namespace maps to a ClickHouse database.
Changelog
Expand to review
| Version | Date | Pull Request | Subject |
|---|---|---|---|
| 2.1.34 | 2026-10-28 | 89658 | Upgrade to Bulk CDK 1.1.9: fix critical CVEs in Apache MINA (mina-core 2.0.31), MINA SSHD 2.20.0 and BouncyCastle 1.86; netty 4.1.139. |
| 2.1.33 | 2026-10-27 | 90134 | Upgrade ClickHouse Java client to 0.11.0. |
| 2.1.32 | 2026-10-09 | 88421 | Upgrade ClickHouse Java client to 0.10.0; test against ClickHouse 26.7. |
| 2.1.31 | 2026-10-01 | 87612 | Upgrade to Bulk CDK 1.1.1. |
| 2.1.30 | 2026-09-15 | 86332 | Version bump to republish the Cloud-specific connector spec. |
| 2.1.29 | 2026-08-25 | 85033 | Support self-managed clusters: optional replicated table engines and ON CLUSTER DDL |
| 2.1.28 | 2026-08-24 | 84983 | Upgrade to Bulk CDK 1.0.25. |
| 2.1.27 | 2026-08-05 | 83747 | Upgrade CDK to 1.0.20; document column drop behavior during schema evolution |
| 2.1.26 | 2026-07-21 | 82684 | fix(destination-clickhouse): avoid failed count for missing temp tables |
| 2.1.25 | 2026-07-14 | 81550 | Use CREATE TABLE IF NOT EXISTS for non-replace table creation to prevent accidental data loss |
| 2.1.24 | 2026-05-20 | 77673 | Upgrade CDK to 1.0.13. Migrate component tests to Testcontainers. |
| 2.1.23 | 2026-02-04 | 72857 | No user-facing changes (Upgrade CDK to 0.2.8) |
| 2.1.22 | 2026-01-26 | 71784 | No user-facing changes (internal refactor SSH tunnel logic) |
| 2.1.21 | 2026-01-20 | 72294 | Upgrade CDK to 0.2.0 |
| 2.1.20 | 2026-01-15 | 71120 | Add schema and table identifier regression tests; upgrade CDK to 0.1.105 |
| 2.1.19 | 2026-01-08 | 70996 | Upgrade to CDK 0.1.97 |
| 2.1.18 | 2025-12-17 | 70963 | Internal refactor: Remove name generator classes, simplify naming utilities |
| 2.1.17 | 2025-12-10 | 70835 | Fix: Skip CDC cursor for version column consideration for dedupe. |
| 2.1.16 | 2025-12-12 | 70897 | Promoting release candidate 2.1.16-rc.3 to a main version. |
| 2.1.16-rc.3 | 2025-12-09 | 70835 | Pick up CDK fixes for namespace / prefix handling |
| 2.1.16-rc.2 | 2025-12-09 | 70358 | Internal refactor: Use TableSchemaMapper for schema operations cont. |
| 2.1.16-rc.1 | 2025-12-04 | 70279 | Internal refactor: Use TableSchemaMapper for schema operations |
| 2.1.15 | 2025-12-16 | 69829 | Bump ClickHouse client to 0.9.4 |
| 2.1.14 | 2025-11-13 | 69245 | Upgrade to CDK 0.1.78 |
| 2.1.13 | 2025-11-11 | 69116 | Upgrade to CDK 0.1.74 (internal refactor for schema evolution) |
| 2.1.12 | 2025-11-06 | 69226 | Improved additional statistics handling |
| 2.1.11 | 2025-11-05 | 69200 | Add support for observability metrics |
| 2.1.10 | 2025-11-04 | 69154 | Fix decimal validation |
| 2.1.9 | 2025-11-03 | 69100 | Upgrade to CDK 0.1.61 to fix state index bug |
| 2.1.8 | 2025-10-29 | 68186 | Upgrade to CDK 0.1.59 |
| 2.1.7 | 2025-10-21 | 67153 | Implement new proto schema implementation |
| 2.1.6 | 2025-10-16 | 68144 | Implement TableOperationsSuite component tests. |
| 2.1.5 | 2025-10-10 | 67598 | Improve handling of heavily interleaved streams. |
| 2.1.4 | 2025-09-29 | 66743 | Activate speed mode. |
| 2.1.3 | 2025-09-29 | 66743 | Promoting release candidate 2.1.3-rc.1 to a main version. |
| 2.1.3-rc.1 | 2025-09-25 | 66699 | Prepare for speed mode. Fix interleaved stream state handling. |
| 2.1.2 | 2025-09-09 | 66143 | Improve schema propagation. |
| 2.1.1 | 2025-09-09 | 66134 | Update the type we are setting for the number type to Decimal(38, 9). |
| 2.1.0 | 2025-09-03 | 65929 | Promoting release candidate 2.1.0-rc.2 to a main version. |
| 2.1.0-rc.2 | 2025-08-29 | #65626 | Pick up CDK fix for rare array OOB exception. |
| 2.1.0-rc.1 | 2025-08-21 | #65144 | Migrate to dataflow model. |
| 2.0.13 | 2025-08-20 | #65125 | Update docs permissioning advice. |
| 2.0.12 | 2025-08-20 | #65120 | Check should properly surface protocol related config errors. |
| 2.0.11 | 2025-07-23 | #65117 | Fix a bug related to the column duplicates name. |
| 2.0.10 | 2025-07-23 | #64104 | Add an option to configure the batch size (both bytes and number of records). |
| 2.0.9 | 2025-07-23 | #63738 | Set clickhouse as an airbyte connector. |
| 2.0.8 | 2025-07-23 | #63760 | Throw an error if an invalid target table exist before the first sync. |
| 2.0.7 | 2025-07-23 | #63751 | Only copy intersection columns when there is a dedup change. |
| 2.0.6 | 2025-07-22 | #63724 | Apply clickhouse column name transformation for columns. |
| 2.0.5 | 2025-07-22 | #63721 | Fix schema change with PKs. |
| 2.0.4 | 2025-07-21 | #62948 | SSH support BETA. |
| 2.0.3 | 2025-07-11 | #62946 | Publish metadata changes. |
| 2.0.2 | 2025-07-10 | #62928 | Makes json optional in spec to work around UI issue. |
| 2.0.1 | 2025-07-10 | #62906 | Adds bespoke validation for legacy hostnames that contain a protocol. |
| 2.0.0 | 2025-07-10 | #62887 | Cut 2.0.0 release. Replace existing connector. |
| 0.1.11 | 2025-07-09 | #62883 | Only set JSON properties on client if enabled to support older CH deployments. |
| 0.1.10 | 2025-07-08 | #62861 | Set user agent header for internal CH telemetry. |
| 0.1.9 | 2025-07-03 | #62509 | Simplify union stringification behavior. |
| 0.1.8 | 2025-06-30 | #62100 | Add JSON support. |
| 0.1.7 | 2025-06-24 | #62047 | Remove the use of the internal namespace. |
| 0.1.6 | 2025-06-24 | #62047 | Hide protocol option when running on cloud. |
| 0.1.5 | 2025-06-24 | #62043 | Expose database protocol config option. |
| 0.1.4 | 2025-06-24 | #62040 | Checker inserts into configured DB. |
| 0.1.3 | 2025-06-24 | #62038 | Allow the client to connect to the resolved DB. |
| 0.1.2 | 2025-06-23 | #62028 | Enable the registry in OSS and cloud. |
| 0.1.1 | 2025-06-23 | #62022 | Publish first beta version and pin the CDK version. |
| 0.1.0 | 2025-06-23 | #62024 | Release first beta version. |