clickhouse integration streamsets — Pipelines, CRUD Headers, and OLAP Sinks

StreamSets pipelines think in origins → processors → destinations, often with CDC headers and streaming batching built into the runtime. ClickHouse^® thinks in parts, sort keys, and eventual merges.

A useful clickhouse integration streamsets design translates pipeline records into analytical tables without forcing ClickHouse® to behave like MySQL.

What StreamSets should own vs what ClickHouse® should own

If StreamSets starts computing every dashboard metric inside Expression Evaluators, you will rebuild those expressions for every new consumer.

Destination choice: JDBC Producer vs HTTP Client

JDBC Producer

Install the ClickHouse® JDBC driver as an external library on the JDBC stage library (commonly streamsets-datacollector-jdbc-lib). Map fields to columns in the UI.

JDBC URL:    jdbc:clickhouse://ch.example:8443/commerce?ssl=true
Table:       commerce.orders_cdc
Operation:   driven by sdc.operation.type when present
Batching:    enable multi-row where validated for your driver version

Good when operators live in the Data Collector UI and want visible column maps.

HTTP Client

Serialize to NDJSON, POST to ClickHouse®:

POST https://ch.example:8443/?query=INSERT%20INTO%20commerce.orders_cdc%20FORMAT%20JSONEachRow

Good for ClickHouse® Cloud and environments where shipping JDBC jars to every runtime is the expensive part.

Neither path auto-designs a MergeTree table. Create DDL first.

The CDC header problem

StreamSets CDC origins often set sdc.operation.type (insert / update / delete). ClickHouse® destinations that "support CRUD" still do not give you Oracle-style transactional updates.

Land changes as facts:

CREATE TABLE commerce.orders_cdc (
    order_id       String,
    customer_id    String,
    status         LowCardinality(String),
    amount         Float64,
    source_ts      DateTime64(3),
    sdc_operation  LowCardinality(String),
    ingested_at    DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(source_ts)
PARTITION BY toYYYYMM(source_ts)
ORDER BY order_id;

Pipeline rules:

1. Map the CDC operation into a real column (sdc_operation)
2. Always include a monotonic source_ts (binlog/LSN time beats pipeline now())
3. Treat delete as a row with sdc_operation = 'DELETE' (or soft-delete flag), not as a ClickHouse® ALTER DELETE per event
4. Expose current state through a view:

CREATE VIEW commerce.v_orders_current AS
SELECT
    order_id,
    customer_id,
    status,
    amount,
    source_ts
FROM commerce.orders_cdc FINAL
WHERE sdc_operation != 'DELETE';

That model survives replay, late events, and pipeline reprocessing.

Streaming origins: batch on the way out

Kafka-fed pipelines feel "real-time" until the destination writes one record per network call.

Inside StreamSets:

• Raise destination batch size
• Prefer multi-row JDBC inserts after testing
• For HTTP, accumulate records before POST
• Watch pipeline batch wait time vs ClickHouse® insert duration

Freshness budget example:

Source delay
  + pipeline batch wait (e.g. 2s)
  + insert time
  + MergeTree visibility
≈ user-visible lag

Write that budget down. "Real-time" without numbers becomes an argument, not an SLA.

Control Hub / runtime realities

If you run Control Hub with multiple Data Collectors:

• The JDBC driver must exist on every runtime that can schedule the pipeline
• Secrets should be runtime credentials, not plain pipeline parameters committed to git
• Rolling pipeline upgrades need a canary Data Collector; a bad driver bump fails randomly based on scheduling
• Error records need a dedicated lane (Kafka DLQ or S3 error prefix), not only the red pipeline badge

Transformer jobs (Spark-ish batch) can also write via JDBC, but the same ClickHouse® truths apply: pre-create tables, batch writes, avoid chatty commits.

Field processors that pay rent

Useful before the ClickHouse® destination:

• Field Type Converter for DateTime / numerics (bad strings become quarantine, not poisoned parts)
• Expression Evaluator for source_ts normalization to UTC
• Field Masker / Hasher for PII before analytical storage
• Stream Selector to split CDC deletes into a side channel if you want different retention

Avoid turning Expression Evaluator into an undocumented metrics warehouse.

Serving layer: when pipelines are not enough

Pipelines get data in. Products need to get answers out.

Tinybird fits when StreamSets already collects the stream and you want managed ClickHouse® with HTTP endpoints:

StreamSets HTTP Client
  → POST https://api.tinybird.co/v0/events?name=orders_cdc

NODE open_order_value
SQL >
    SELECT
        status,
        count() AS orders,
        sum(amount) AS gmv
    FROM orders_cdc
    WHERE sdc_operation != 'DELETE'
      AND source_ts >= {{ DateTime(start_time, '2026-07-01 00:00:00') }}
    GROUP BY status

TYPE endpoint

StreamSets keeps ownership of capture and delivery. Tinybird owns analytical serving for user-facing analytics.

Lag and health signals to watch

1. Origin lag (Kafka offsets / CDC LSN distance)
2. Destination error record rate
3. ClickHouse® insert failures (auth, type mismatch, readonly)
4. Watermark age (max(source_ts) vs now)
5. Parts count on hot tables after destination batch changes

If (1) is fine and (4) is stale, the sink batching is wrong. If (4) is fine and dashboards are wrong, your FINAL / delete filter is wrong.

Bring-up sequence

1. Create ClickHouse® / Tinybird schema with explicit ORDER BY
2. Run the pipeline against a captured CDC slice in DEV
3. Prove update + delete semantics in v_orders_current
4. Prove replay does not double-count
5. Promote to PROD with credentials + driver baked into the runtime image
6. Only then open the firehose

FAQ

Is there a dedicated StreamSets ClickHouse® destination?

Use JDBC Producer with the ClickHouse® driver, or HTTP Client to the HTTP interface. That is the supported practical approach.

Can StreamSets UPDATEs land as true ClickHouse® UPDATEs?

Not as an OLTP pattern. Land versions into ReplacingMergeTree (or another deliberate CDC layout) and read current state from a view.

What breaks during Control Hub upgrades?

Missing external JDBC libraries on new Data Collectors. Bake driver install into the node image and verify before shifting pipeline load.

When should StreamSets write to S3 instead of ClickHouse® directly?

Large historical backfills and spiky batch origins. Let ClickHouse® pull Parquet from object storage; keep the streaming path for the live CDC/traffic feed.