Search docs.molo17.com

    Clickhouse glyph screenshot 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 DELETE statements. A table written in append mode uses lightweight DELETE mutations 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 to 8123;

    • Database name (databaseName): Name of your target database;

    • Username (username): Username with read & write access to the target tables. Required (MissingHostUsernameException when missing);

    • Password (password): Password belonging to the given username. Required (MissingHostPasswordException when missing).

    Specific configuration

    • Disable truncate on target table before snapshot inserts (disableTruncateOnSnapshot): default false. Target-scoped property on ClickHouseSpecificConfiguration.

    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): default true. The driver properties compress (responses) and decompress (requests) are both set from this value.

    • Use RowBinary for prepared INSERT batches (driver beta, ClickHouse 25.x and older only) (useRowBinary): default false. Leave this off. The write path is a textual multi-row INSERT …​ VALUES batch. The driver property is beta.row_binary_for_simple_insert.

    • Enable ClickHouse async inserts (asyncInsert): default false. When true, 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): default 1000. Sent only when async inserts are enabled, as clickhouse_setting_async_insert_busy_timeout_ms.

    Per entity (ClickHouseEntityConfiguration):

    • Bulk insert batch size (maxWriteBatchSize): default 10000. This is the chunk size of the batch path, not a bulk loader. Each chunk is one multi-row INSERT.

    • Delete mutation batch size (maxDeleteBatchSize): default 1000. 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 _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. The connector writes it on every row. It is not left to a server column default. The generated column also declares DEFAULT toUnixTimestamp64Nano(now64(9)) for an INSERT that 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 ReplacingMergeTree(_version, _is_deleted) ordered by the keys, with columns _version UInt64 and _is_deleted UInt8, then run a new snapshot.

    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

    SHORT

    Int16

    Alternatives: Int8, UInt8.

    INT

    Int32

    Alternative: UInt16.

    LONG

    Int64

    Alternative: UInt32.

    FLOAT

    Float32

    Alternative: Float64.

    DOUBLE

    Float64

    Alternative: Float32.

    BIG_DECIMAL

    Decimal(P, S)

    Precision and scale come from the source, up to 76 digits. An unsized decimal is Decimal(38, 9). The value is sent as plain text (toPlainString()), not scientific notation. Alternative: UInt64.

    BOOLEAN

    Bool

    STRING

    String

    Alternatives include FixedString(n), UUID, Enum8, Enum16, IPv4, and IPv6. A generated Enum8 or Enum16 column is created as LowCardinality(String), because the enum values are not known. Nullable is inside that wrapper when the column is nullable.

    BYTE_ARRAY

    String

    Raw bytes. Alternative: FixedString(n).

    LOCAL_DATE

    Date32

    Alternative: Date.

    LOCAL_TIME

    Time64(6)

    If Time types are disabled on the server, the column is created as String. Alternatives: Time, String.

    OFFSET_TIME

    String

    Text HH:MM:SS.ffffff+HH:MM (pattern HH:mm:ss.SSSSSSxxx). Alternatives Time64(6) and Time are normalized to UTC and drop the offset.

    LOCAL_DATE_TIME

    DateTime64(p)

    p is the source fractional precision (6 when the source has none, at most 9). Alternatives: DateTime, DateTime64(6, 'UTC'), String.

    OFFSET_DATE_TIME

    DateTime64(6, 'UTC')

    The instant is kept and shown in UTC. Alternatives: DateTime64, DateTime, String.

    ARRAY

    Array(Nullable(T))

    T comes from the source array element type; otherwise String. Alternative: String.

    MAP

    Map(String, Nullable(String))

    Alternative: String.

    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.