New in Gluesync 2.3: the ClickHouse target agent

Gluesync 2.3 adds a new ClickHouse target agent. Every change lands as a new version of its key in a ReplacingMergeTree(_version, _is_deleted) table that Gluesync generates, written in multi-row INSERT … VALUES batches over HTTP/S JDBC. Deletes become tombstones. Query Studio and the Validator read with final = 1, so you see the current row of each key.

ClickHouse is a target in Gluesync. Any supported source feeds it through a pipeline in Core Hub: snapshot first, then the source agent's CDC stream. The agent runs against any ClickHouse deployment, on-prem or DBaaS on Amazon AWS, Google Cloud or Azure.

Docs: ClickHouse agent · Target setup · Changelog. Overview: Replicate to ClickHouse with Gluesync.

What the agent does

• Writes every change as a new version of its key in a generated ReplacingMergeTree(_version, _is_deleted) table, in multi-row INSERT … VALUES batches over HTTP/S JDBC, with deletes written as tombstones
• Reads with final = 1 in Query Studio and the Validator
• Writes user-owned tables of another shape in append mode, with a warning
• Maps every Gluesync data type

Why versions and tombstones

ClickHouse is built for appends. Row-by-row UPDATE and DELETE fight that design. So the agent writes the way ClickHouse likes to be written:

• An insert or an update is a new row with a new _version and _is_deleted = 0.
• A delete is a tombstone: the old key (the before-image when the source sends one), a new _version, and _is_deleted = 1.
• FINAL shows the last version of each key and hides tombstones.

_version is epoch nanoseconds from the agent, strictly increasing within the agent, and the connector writes it on every row. The generated column also declares DEFAULT toUnixTimestamp64Nano(now64(9)) for any INSERT that leaves it out.

Your CDC stream becomes a clean, queryable history in ClickHouse, with the current state one FINAL away.

Tables Gluesync generates

Source table	Generated ClickHouse table
Has keys	ReplacingMergeTree(_version, _is_deleted), ordered by those keys, plus _version (UInt64) and _is_deleted (UInt8)
No keys	MergeTree ORDER BY tuple()

_version and _is_deleted are reserved names on a generated keyed table. Keyless tables stay on MergeTree, because ReplacingMergeTree would collapse them to one row.

Tables you create

Bring your own table and the agent adapts to its engine:

• ReplacingMergeTree, ReplicatedReplacingMergeTree or SharedReplacingMergeTree whose last two arguments are _version and _is_deleted: written with versions and tombstones.
• Any other engine: append mode. Updates are appended as new rows, deletes are lightweight DELETE mutations that match the old key. A keyed table in append mode raises a warning the first time it is written, since it keeps every version of a row.

Write path

• Batches are multi-row INSERT … VALUES. Bulk insert batch size (maxWriteBatchSize) defaults to 10000 per chunk.
• The transaction is written in its own order. The connector deliberately skips grouping by operation, since that could delete a row inserted again later in the same transaction. That is also why bulk load mode is off for this agent.
• Delete mutation batch size (maxDeleteBatchSize) defaults to 1000 and applies to append-mode tables.
• Snapshots populate ClickHouse tables for initial loads, and the agent honors the Core Hub "TRUNCATE before snapshot" option. Turn it off per target with disableTruncateOnSnapshot.
• Connection pooling is supported across pipelines.

Optional switches: HTTP compression (on by default), ClickHouse async inserts (asyncInsert, off by default, with a flushIntervalMs of 1000 when enabled). Leave useRowBinary off: the write path is the textual INSERT … VALUES batch.

Data types

The agent maps every Gluesync type. A few defaults worth knowing:

Gluesync type	Default ClickHouse type
BIG_DECIMAL	Decimal(P, S), up to 76 digits. Unsized is Decimal(38, 9)
LOCAL_DATE	Date32
LOCAL_TIME	Time64(6)
LOCAL_DATE_TIME	DateTime64(p), p from the source precision
OFFSET_DATE_TIME	DateTime64(6, 'UTC')
ARRAY	Array(Nullable(T))
MAP	Map(String, Nullable(String))

Array and Map can't be nullable in ClickHouse, so a NULL array or map is stored as [] or {}. The full table, with alternatives per type, is in the target setup docs.

Connection setup

In the Control Plane, add ClickHouse as the target of a pipeline:

• Hostname / IP Address: your ClickHouse HTTP endpoint
• Port: defaults to 8123
• Database name, Username, Password

Credentials need read and write access to the target tables and database: the agent inserts rows, truncates tables, reads metadata and creates target tables when Gluesync generates them. enableTls switches the JDBC URL to https. trustServerCertificate accepts self-signed certificates.

Prefer the API? The same connection goes through the Core Hub REST endpoint:

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
  "hostCredentials": {
    "connectionName": "myAgentNickName",
    "host": "host-address",
    "port": 8123,
    "databaseName": "db_name",
    "username": "",
    "password": "",
    "maxConnectionsCount": 25,
    "enableTls": false,
    "trustServerCertificate": false
  }
}'

Where it fits

Operational databases on one side, ClickHouse analytics on the other. Pick any Gluesync source, IBM i, Oracle, SQL Server, PostgreSQL, Couchbase and the rest of the catalog, and land its snapshot and CDC changes in ClickHouse as versioned rows ready for FINAL queries. The full list of sources and targets lives on the Gluesync page.

Next steps

1. Read the ClickHouse target setup.
2. Deploy Gluesync 2.3 and add ClickHouse as the target of a pipeline in Core Hub.
3. Query with FINAL and watch each key resolve to its latest version.

Want to see it on your data? Request a free trial or book a demo. Questions on a concrete build: I am around.