Skip to content
Streamkap
Esc
↑↓navigate↵open⌘Jpreview
On this page

ClickHouse Cloud

Configure a ClickHouse Cloud destination in Streamkap, covering upsert and append delivery modes, data type mappings, and IP access list setup.

Prerequisites

  • ClickHouse User
  • ClickHouse database hostname
  • For nested arrays/structs: flatten_nested=0 must be set on the ClickHouse server or user level
  • Dialect version must match your ClickHouse server version (e.g., ClickHouse 22.x requires dialect version 22)

Delivery Guarantees

Streamkap offers at-least-once delivery guarantee for ClickHouse and defaults to upsert mode.

For append (or insert) mode, duplicate records will be inserted into destination tables, but, ClickHouse materialized views can filter them out.

With upsert mode (default) we carry out deduplication using the records primary key.

Data duplication

Although using the ClickHouse FINAL query modifier has drawbacks , we have seen situations where users SELECT queries on the destination tables return some duplicate records and the FINAL query modifier helps avoid that.

Behind-the-scenes, upsert ingestion mode relies on ClickHouse’s ReplacingMergeTree table engine to deduplicate data in the background instead of when the data’s inserted or merged (e.g. MERGE INTO for other data warehouses like Snowflake or DataBricks) into the destination tables.

To save having to specify the FINAL query modifier in every query, you can consider altering the querying user’s role as shown below:

-- Replace { ... } placeholders as required
ALTER ROLE {ROLENAME} SETTINGS final = 1;

Data Type Support

Our Connector supports the following data types and mappings:

Streamkap ClickHouse
INT8 Int8
INT16 Int16
INT32 Int32
FLOAT32 Float32
FLOAT64 Float64
BOOLEAN Bool
BYTES BLOB(String)
org.apache.kafka.connect.data.Decimal DECIMAL(38, 0)
org.apache.kafka.connect.data.Timestamp io.debezium.time.ZonedTimestamp DateTime64
org.apache.kafka.connect.data.Date Date
io.debezium.data.Json String(JSON) or JSON(if supported by ClickHouse server & JSON Support is enabled)
STRUCT(e.g. maps, key/value pairs) Tuple
ARRAY Array

ClickHouse Setup

It’s recommended to create a separate user and role for Streamkap to access your ClickHouse database. Below is an example script that does that.

-- Replace { ... } placeholders as required
CREATE ROLE STREAMKAP_ROLE;
GRANT SELECT, CREATE, SHOW, INSERT, ALTER ON {database}.* TO STREAMKAP_ROLE;

CREATE USER streamkap_user IDENTIFIED WITH sha256_password BY '{password}'
GRANT STREAMKAP_ROLE TO streamkap_user;

Allowing Streamkap access

IP access lists in ClickHouse determine what services can connect to your ClickHouse database.

From your ClickHouse Cloud services list:

  1. Choose the service and then Settings
  2. Click + Add entry and enter Streamkap IP Addresses
  3. Enter a description e.g. Streamkap app
  4. Click Add entry

Obtaining connection details

You will need the hostname of your ClickHouse database when setting up the Destination in Streamkap.

From your ClickHouse Cloud services list:

  1. Choose the service and then Connect
  2. At the bottom of the connection window you will see https:// followed by the hostname like this abc123.us-west-2.aws.clickhouse.cloud. The part after https:// (excluding the port :port) is the hostname

Handling semi-structured data

To support complex data structures such as nested arrays of structs and nested structs containing arrays, the Connector’s user role needs to be configured as shown below:

ALTER ROLE STREAMKAP_ROLE SETTINGS allow_experimental_object_type = 1;

Streamkap Setup

  1. Go to Destinations and choose ClickHouse

  2. Input the following information:

    1. Name - A unique and memorable name for this Connector
    2. Hostname - The IP or URL of your ClickHouse instance
    3. Ingestion Mode (default: upsert) - See Inserts/Upserts for information about ingestion modes
    4. Port (default: 8443)
    5. Username (case sensitive) - STREAMKAP_USER or the username you chose
    6. Password - Password for the database user
    7. Database - The name of the database
    8. SSL - SSL is enabled by default on ClickHouse instances
    9. Tasks - If Pipelines for this Destination have lag that’s continuously growing over time and not reducing, increase the number of Tasks, otherwise, leave as default
    10. JSON Support - Allow JSON data type in ClickHouse, make sure the ClickHouse server supports it.

  3. Click Save

Metadata Columns

Column Description
_STREAMKAP_SOURCE_TS_MS Timestamp in milliseconds (UTC) for when the event occurred in the source database
_STREAMKAP_TS_MS Timestamp in milliseconds (UTC) for when Streamkap received the event
__DELETED Indicates whether this event/row has been deleted in the source database (true/false)
_STREAMKAP_OFFSET Offset value in relation to the events processed, useful for debugging
_STREAMKAP_OP Operation type: c (create/insert), u (update), d (delete), r (read/snapshot), t (truncate)
_STREAMKAP_PARTITION Kafka partition the record was consumed from

For usage patterns, see Metadata.

Troubleshooting

Performance tuning

If you are experiencing performance issues or lag with your ClickHouse destination, adjustments to poll records, partition counts, and task parallelism can help. See Performance Tuning for detailed guidance on optimizing pipeline throughput and latency.