Advanced Analytica Advanced Analytica: IBOM
← All connectors
ClickHouse logo

Destination connector

ClickHouse

Send governed data into ClickHouse with a clear, practical setup guide.

Version 2.1.34 certified generally available

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 modeSupported?
Full Refresh - OverwriteYes
Full Refresh - AppendYes
Full Refresh - Overwrite + DedupedYes
Incremental Sync - AppendYes
Incremental Sync - Append + DedupedYes

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 the Replicated database engine. It also works when default_replica_path/default_replica_name macros 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 the Replicated database 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:

TopologyEnable ReplicationCluster Name
Single node / ClickHouse Cloudoffempty
Cluster with Replicated database engineonempty
Cluster with Atomic databases + remote_serversonset 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 deploymentClickhouse deploymentDo this
CloudCloudAdd Airbyte Cloud’s IP addresses to the allowlist in your ClickHouse Cloud settings.
CloudSelf-managedConfigure your firewall to allow inbound connections on port 8443 (HTTPS) or 8123 (HTTP) from Airbyte Cloud’s IP addresses.
Self-managedCloudWhitelist your Airbyte server’s public IP address in ClickHouse Cloud settings.
Self-managedSelf-managedEnsure 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

  1. In Airbyte, select Destinations > ClickHouse.

  2. Configure the destination with the following information.

    • Hostname: Your ClickHouse server hostname, without a protocol prefix such as http:// or https://
    • 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.

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 → JSON if 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
VersionDatePull RequestSubject
2.1.342026-10-2889658Upgrade 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.332026-10-2790134Upgrade ClickHouse Java client to 0.11.0.
2.1.322026-10-0988421Upgrade ClickHouse Java client to 0.10.0; test against ClickHouse 26.7.
2.1.312026-10-0187612Upgrade to Bulk CDK 1.1.1.
2.1.302026-09-1586332Version bump to republish the Cloud-specific connector spec.
2.1.292026-08-2585033Support self-managed clusters: optional replicated table engines and ON CLUSTER DDL
2.1.282026-08-2484983Upgrade to Bulk CDK 1.0.25.
2.1.272026-08-0583747Upgrade CDK to 1.0.20; document column drop behavior during schema evolution
2.1.262026-07-2182684fix(destination-clickhouse): avoid failed count for missing temp tables
2.1.252026-07-1481550Use CREATE TABLE IF NOT EXISTS for non-replace table creation to prevent accidental data loss
2.1.242026-05-2077673Upgrade CDK to 1.0.13. Migrate component tests to Testcontainers.
2.1.232026-02-0472857No user-facing changes (Upgrade CDK to 0.2.8)
2.1.222026-01-2671784No user-facing changes (internal refactor SSH tunnel logic)
2.1.212026-01-2072294Upgrade CDK to 0.2.0
2.1.202026-01-1571120Add schema and table identifier regression tests; upgrade CDK to 0.1.105
2.1.192026-01-0870996Upgrade to CDK 0.1.97
2.1.182025-12-1770963Internal refactor: Remove name generator classes, simplify naming utilities
2.1.172025-12-1070835Fix: Skip CDC cursor for version column consideration for dedupe.
2.1.162025-12-1270897Promoting release candidate 2.1.16-rc.3 to a main version.
2.1.16-rc.32025-12-0970835Pick up CDK fixes for namespace / prefix handling
2.1.16-rc.22025-12-0970358Internal refactor: Use TableSchemaMapper for schema operations cont.
2.1.16-rc.12025-12-0470279Internal refactor: Use TableSchemaMapper for schema operations
2.1.152025-12-1669829Bump ClickHouse client to 0.9.4
2.1.142025-11-1369245Upgrade to CDK 0.1.78
2.1.132025-11-1169116Upgrade to CDK 0.1.74 (internal refactor for schema evolution)
2.1.122025-11-0669226Improved additional statistics handling
2.1.112025-11-0569200Add support for observability metrics
2.1.102025-11-0469154Fix decimal validation
2.1.92025-11-0369100Upgrade to CDK 0.1.61 to fix state index bug
2.1.82025-10-2968186Upgrade to CDK 0.1.59
2.1.72025-10-2167153Implement new proto schema implementation
2.1.62025-10-1668144Implement TableOperationsSuite component tests.
2.1.52025-10-1067598Improve handling of heavily interleaved streams.
2.1.42025-09-2966743Activate speed mode.
2.1.32025-09-2966743Promoting release candidate 2.1.3-rc.1 to a main version.
2.1.3-rc.12025-09-2566699Prepare for speed mode. Fix interleaved stream state handling.
2.1.22025-09-0966143Improve schema propagation.
2.1.12025-09-0966134Update the type we are setting for the number type to Decimal(38, 9).
2.1.02025-09-0365929Promoting release candidate 2.1.0-rc.2 to a main version.
2.1.0-rc.22025-08-29#65626Pick up CDK fix for rare array OOB exception.
2.1.0-rc.12025-08-21#65144Migrate to dataflow model.
2.0.132025-08-20#65125Update docs permissioning advice.
2.0.122025-08-20#65120Check should properly surface protocol related config errors.
2.0.112025-07-23#65117Fix a bug related to the column duplicates name.
2.0.102025-07-23#64104Add an option to configure the batch size (both bytes and number of records).
2.0.92025-07-23#63738Set clickhouse as an airbyte connector.
2.0.82025-07-23#63760Throw an error if an invalid target table exist before the first sync.
2.0.72025-07-23#63751Only copy intersection columns when there is a dedup change.
2.0.62025-07-22#63724Apply clickhouse column name transformation for columns.
2.0.52025-07-22#63721Fix schema change with PKs.
2.0.42025-07-21#62948SSH support BETA.
2.0.32025-07-11#62946Publish metadata changes.
2.0.22025-07-10#62928Makes json optional in spec to work around UI issue.
2.0.12025-07-10#62906Adds bespoke validation for legacy hostnames that contain a protocol.
2.0.02025-07-10#62887Cut 2.0.0 release. Replace existing connector.
0.1.112025-07-09#62883Only set JSON properties on client if enabled to support older CH deployments.
0.1.102025-07-08#62861Set user agent header for internal CH telemetry.
0.1.92025-07-03#62509Simplify union stringification behavior.
0.1.82025-06-30#62100Add JSON support.
0.1.72025-06-24#62047Remove the use of the internal namespace.
0.1.62025-06-24#62047Hide protocol option when running on cloud.
0.1.52025-06-24#62043Expose database protocol config option.
0.1.42025-06-24#62040Checker inserts into configured DB.
0.1.32025-06-24#62038Allow the client to connect to the resolved DB.
0.1.22025-06-23#62028Enable the registry in OSS and cloud.
0.1.12025-06-23#62022Publish first beta version and pin the CDK version.
0.1.02025-06-23#62024Release first beta version.
Advanced Analytica

Most businesses are sitting on information they can’t use at speed. The people who hold it are busy, and when they leave, the knowledge goes with them. The documents are static, and the processes around them weren’t designed for AI.

Advanced Analytica works with these businesses to turn that information into structured data assets. Once they're integrated, AI systems can govern it, and the business can use it. We bring in the strategy, the specification and the AI skills to rework how the business operates.

MOVE FAST. STAY SAFE.