ClickHouse

The ClickHouse destination connector syncs data from Pipeloom sources to ClickHouse, a high-performance columnar database designed for online analytical processing (OLAP). This connector writes data directly to ClickHouse tables with proper typing, enabling fast analytical queries on your replicated data.

This is a complete rewrite of the ClickHouse destination connector built on Pipeloom's Bulk CDK framework, replacing the legacy v1 connector.

How version 2 improves on version 1

Version 2.0.0 represents a complete architectural redesign of the ClickHouse destination connector with 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: Pipeloom writes data directly to typed columns matching your source schema, rather than storing everything as JSON in raw tables. This improves query performance and reduces storage requirements.
• Improved performance: The new architecture uses ClickHouse's native binary protocol and batch inserts for faster data loading.
• Active maintenance: Built on Pipeloom's modern CDK framework with ongoing development and support from the Pipeloom team.

Supported sync modes

Deduplication leverages ClickHouse's ReplacingMergeTree table engine. See Deduplication below for details.

Self-managed clusters

By default, the connector stores data only on the node that receives each insert. On a self-managed ClickHouse cluster with multiple replicas, this means reads through a load balancer may return incomplete data because the other nodes are unaware of the inserted rows. Similarly, table definitions (CREATE, ALTER, DROP) are only applied on the connected node.

Two advanced options make the connector cluster-aware. ClickHouse Cloud does not need either of these — it uses SharedMergeTree and handles replication automatically.

• Enable Replication (use_replicated_engines): ensures synced data is replicated across all cluster nodes. The replicated engine is emitted without explicit Keeper path arguments, so the server must be able to derive them — which is the case inside a database using the Replicated database engine, or when default_replica_path/default_replica_name macros are configured in the server. Requires ClickHouse Keeper (or ZooKeeper).
• Cluster Name (cluster_name): when provided, table definitions are automatically propagated to all cluster nodes. Required for clusters whose databases use the default Atomic engine. Leave empty when the target database uses the Replicated database engine — there, DDL replicates through the database itself and a cluster name is unnecessary. The name is validated against the charset [A-Za-z0-9_.-] and the connection is rejected if invalid characters are used.

Typical combinations:

Deduplication

For optimal deduplication in Incremental - Append + Deduped sync mode, use a cursor column with one of these types:

• Integer types (Int64, etc.)
• Date
• Timestamp (DateTime64)

If you use a different cursor column type, like string, the connector falls back to using the _airbyte_extracted_at timestamp for deduplication ordering. This fallback may not accurately reflect the natural ordering of your source data, and you'll see a warning in the sync logs.

:::warning

Pipeloom's ClickHouse connector leverages the ReplacingMergeTree table engine to handle deduplication. To guarantee deduplicated results at query time, you can add the FINAL operator to your query string. For example:

SELECT * FROM your_table FINAL

Without this, you may see duplicated or deleted results when querying your data.

:::

Alternatively, you may also 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
• Network access from Pipeloom to your ClickHouse instance
• A ClickHouse user with appropriate permissions (see below)

Setup guide

1. Configure network access

Ensure your ClickHouse database is accessible from Pipeloom.

If you can't expose ClickHouse publicly, use SSH Tunneling via a bastion host that can reach ClickHouse.

2. Create a dedicated user with permissions

:::tip It's best to create a dedicated ClickHouse user for Pipeloom rather than using an existing user. This improves security and makes it easier to audit Pipeloom's database operations. :::

Create a ClickHouse user for Pipeloom with the following permissions:

• Create and manage databases
• Create, alter, drop, and truncate tables
• Insert and select data

To create a user with the required permissions, run the following SQL commands in your ClickHouse instance:

-- 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 Pipeloom 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 you configure in the connector settings. It's typically default.

If you configure custom namespaces in your Pipeloom connections, 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 Pipeloom, click Destinations > ClickHouse.

2. Configure the destination with the following information.

   • Hostname: Your ClickHouse server hostname (without protocol prefix like http:// or https://)
   • Port: HTTP port for ClickHouse (defaults are 8123 for HTTP and 8443 for HTTPS)
   • Protocol (self-hosted only): Choose HTTP or HTTPS. In Pipeloom, this option is hidden and managed by the platform.
   • 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 (recommended if your ClickHouse version supports it)
   • Record Window Size (advanced): The maximum number of records to write in a single batch. Tuning this parameter can impact performance. The batch size is also 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 to ensure data is synchronized across all nodes. See Self-managed clusters for details.
   • Cluster Name (advanced): Name of your ClickHouse cluster. When provided, table definitions are propagated to all cluster nodes. See Self-managed clusters for details.

4. SSH tunnel (optional)

:::warning SSH tunneling support is currently in Beta. :::

If your ClickHouse instance isn't directly accessible from Pipeloom, you can use SSH tunneling to establish a secure connection. Configure the SSH tunnel settings in the connector configuration with your SSH host, port, username, and authentication method (password or private key).

Output schema

Pipeloom writes each stream to its own table in ClickHouse. It creates tables in either the configured default database, typically default, or in a database corresponding to the namespace you specify for the stream when you set up your connection.

The connector converts Pipeloom 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 The connector converts arrays and unions to strings for compatibility. If you need to query these as structured data, use ClickHouse's JSON functions to parse the string values. :::

Schema evolution

This connector supports automatic schema evolution. When the source schema changes, the connector automatically adds new columns to destination tables and modifies column types as needed.

Unlike most other Pipeloom destinations, when a column is removed from the source, ClickHouse drops the column and its historical data from the destination table. Other destinations typically retain removed columns and write NULL for subsequent rows. ClickHouse drops columns because its ReplacingMergeTree engine handles deduplication by deleting and re-inserting records rather than using MERGE queries like other destinations. This table recreation approach requires dropping obsolete columns.

Namespace support

This destination supports namespaces. The namespace maps to a ClickHouse database.