ClickHouse agent
Target ClickHouse
Prerequisites
To have Gluesync working on your ClickHouse instance you will need to have:
-
Valid user credentials with permission to read, and write to the target tables and respective database.
The connector uses those credentials to insert rows, truncate tables, read metadata, and create a target table when Gluesync generates one. Deletes on a table the connector generated with keys are tombstone rows, not
DELETEstatements. A table written in append mode uses lightweightDELETEmutations instead.
Setup via Web UI
The host, port, database, username, and password fields below are the hostCredentialsFields() keys.
Their displayName values in code are empty, so the labels match the existing target-connector pages.
-
Hostname / IP Address (
host): DNS record or IP address of your ClickHouse HTTP endpoint; -
Port (
port): Optional, defaults to8123; -
Database name (
databaseName): Name of your target database; -
Username (
username): Username with read & write access to the target tables. Required (MissingHostUsernameExceptionwhen missing); -
Password (
password): Password belonging to the given username. Required (MissingHostPasswordExceptionwhen missing).
Specific configuration
-
Disable truncate on target table before snapshot inserts (
disableTruncateOnSnapshot): defaultfalse. Target-scoped property onClickHouseSpecificConfiguration.
Connection options
enableTls (no display name in code) defaults to false. When it is true, the JDBC URL uses https; otherwise http.
trustServerCertificate (no display name in code) defaults to false. When it is true, the driver property ssl_mode is set to TRUST, which accepts any server certificate, including a self-signed one. When it is false, ssl_mode is not set.
These are the target connection properties on ClickHouseCustomHostCredentials. Defaults are the code defaults.
-
Enable HTTP compression (requests and responses) (
compression): defaulttrue. The driver propertiescompress(responses) anddecompress(requests) are both set from this value. -
Use RowBinary for prepared INSERT batches (driver beta, ClickHouse 25.x and older only) (
useRowBinary): defaultfalse. Leave this off. The write path is a textual multi-rowINSERT … VALUESbatch. The driver property isbeta.row_binary_for_simple_insert. -
Enable ClickHouse async inserts (
asyncInsert): defaultfalse. Whentrue, the connector sets only these prefixed server settings (unprefixed names are refused by the driver):-
clickhouse_setting_async_insert=1 -
clickhouse_setting_wait_for_async_insert=1 -
clickhouse_setting_async_insert_busy_timeout_ms= the flush interval below
-
-
Async insert flush interval (ms) (
flushIntervalMs): default1000. Sent only when async inserts are enabled, asclickhouse_setting_async_insert_busy_timeout_ms.
Per entity (ClickHouseEntityConfiguration):
-
Bulk insert batch size (
maxWriteBatchSize): default10000. This is the chunk size of the batch path, not a bulk loader. Each chunk is one multi-rowINSERT. -
Delete mutation batch size (
maxDeleteBatchSize): default1000. Used only when the table is written in append mode.
Writes
Bulk load is not supported (allowedBulkMode is NONE).
The connector does not group a transaction by operation, because that could delete a row that was inserted again later in the same transaction.
The batch path writes the transaction in its own order, and each chunk is one multi-row INSERT … VALUES.
Tables Gluesync generates
A generated table that has keys is ReplacingMergeTree(_version, _is_deleted), ordered by those keys.
The connector adds _version (UInt64) and _is_deleted (UInt8).
Those names are reserved on a generated keyed table.
-
An insert or an update is a new row with a new
_versionand_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. -
FINALshows the last version of each key and hides tombstones. -
_versionis epoch nanoseconds from the agent, strictly increasing within the agent. The connector writes it on every row. It is not left to a server column default. The generated column also declaresDEFAULT toUnixTimestamp64Nano(now64(9))for anINSERTthat does not set_version.
A generated table without keys stays MergeTree ORDER BY tuple().
ReplacingMergeTree would collapse that table to one row.
Tables you create
A table you created is written with versions and tombstones only when its engine is already a ReplacingMergeTree, ReplicatedReplacingMergeTree, or SharedReplacingMergeTree whose last two arguments are _version and _is_deleted.
Any other engine stays append-only: updates are appended as new rows, and deletes are lightweight DELETE mutations that match the old key.
|
A keyed table written in append mode raises a warning the first time it is written: the table keeps every version of a row.
To store updates as versions and deletes as tombstones, recreate it as |
Query Studio and the validator open a dedicated connection with clickhouse_setting_final = 1 (final = 1), so a ReplacingMergeTree shows the current row of each key.
An update that sets a column to NULL writes NULL.
Array and Map cannot be nullable: a NULL array or map is stored as [] or {}.
A delete matches the old key (the before-image when the source sends one).
Data types
Defaults below are what the connector creates. Only types present in ClickHouseDataTypeMapping at this connector revision are listed.
| Gluesync type | Default ClickHouse type | Notes |
|---|---|---|
|
|
Alternatives: |
|
|
Alternative: |
|
|
Alternative: |
|
|
Alternative: |
|
|
Alternative: |
|
|
Precision and scale come from the source, up to 76 digits. An unsized decimal is |
|
|
|
|
|
Alternatives include |
|
|
Raw bytes. Alternative: |
|
|
Alternative: |
|
|
If Time types are disabled on the server, the column is created as |
|
|
Text |
|
|
|
|
|
The instant is kept and shown in UTC. Alternatives: |
|
|
|
|
|
Alternative: |
Setup via Rest APIs
Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.
Connect the agent
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
}
}'
port defaults to 8123. maxConnectionsCount defaults to 25. enableTls defaults to false. trustServerCertificate defaults to false.