Building a Fast Real‑Time Streaming Platform with Kafka and ClickHouse

Problem

Aggregating high‑volume point‑of‑sale data for real‑time analytics requires a ingestion pipeline that can handle bursts without overwhelming ClickHouse.

Why Direct INSERTs to ClickHouse Fail

ClickHouse expects controlled ingestion speed and parallelism. Uncontrolled INSERTs generate “too many parts” errors (see https://clickhouse.com/blog/common-getting-started-issues-with-clickhouse#1-too-many-parts) and can exhaust CPU/I‑O resources.

Kafka as a Buffer

Kafka absorbs spikes, provides ordered delivery, and decouples producers from ClickHouse consumers, matching ClickHouse’s preference for buffered, paced ingestion.

Implementation Options

ClickHouse Kafka Engine

The built‑in Kafka engine reads from a Kafka topic and materializes rows directly into a ClickHouse table.

-- Queue wrapper (Kafka engine)
CREATE TABLE demo_events_queue ON CLUSTER '{cluster}' (
    user_ts String,
    id UInt64,
    message String
) ENGINE = Kafka SETTINGS
    kafka_broker_list = 'KAFKA_HOST:9091',
    kafka_topic_list = 'TOPIC_NAME',
    kafka_group_name = 'uniq_group_id',
    kafka_format = 'JSONEachRow';

Target table that stores enriched rows:

CREATE TABLE demo_events_table ON CLUSTER '{cluster}' (
    topic String,
    offset UInt64,
    partition UInt64,
    timestamp DateTime64,
    user_ts DateTime64,
    id UInt64,
    message String
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/demo_events_table', '{replica}')
PARTITION BY toYYYYMM(timestamp)
ORDER BY (topic, partition, offset);

Materialized view that transforms virtual Kafka columns into the target schema:

CREATE MATERIALIZED VIEW readings_queue_mv TO demo_events_table AS
SELECT
    _topic AS topic,
    _offset AS offset,
    _partition AS partition,
    _timestamp AS timestamp,
    toDateTime64(parseDateTimeBestEffort(user_ts), 6, 'UTC') AS user_ts,
    id,
    message
FROM demo_events_queue;

This keeps read → transform → write entirely inside ClickHouse, which works for modest workloads but can cause CPU and I/O contention at scale.

Kafka Connect Sink

Kafka Connect moves parsing and loading logic out of ClickHouse into a dedicated connector, preserving exactly‑once semantics while offloading work from the database.

Configuration example is documented at https://clickhouse.com/docs/en/integrations/kafka/clickhouse-kafka-connect-sink.

External Writer (DoubleCloud Transfer)

DoubleCloud Transfer provides a managed writer that handles offset management, observability, and independent scalability.

resource "doublecloud_transfer" "clickstream-transfer" {
  name       = "clickstream-transfer"
  project_id = var.project_id
  source     = doublecloud_transfer_endpoint.clickstream-source[count.index].id
  target     = doublecloud_transfer_endpoint.clickstream-target[count.index].id
  type       = "INCREMENT_ONLY"
  activated  = true
}

Source endpoint parser maps JSON fields to ClickHouse column types:

parser {
  json {
    schema {
      fields {
        field { name = "user_ts" type = "datetime" }
        field { name = "id"      type = "uint64" }
        field { name = "message" type = "utf8" }
      }
    }
    null_keys_allowed = false
    add_rest_column    = true
  }
}

Target ClickHouse connection definition (example):

clickhouse_target {
  clickhouse_cleanup_policy = "DROP"
  connection {
    address { cluster_id = doublecloud_clickhouse_cluster.target-clickhouse.id }
    database = "default"
    user     = "admin"
  }
}

Link source and target into a transfer resource (example):

resource "doublecloud_transfer" "clickstream-transfer" {
  ...
  source = doublecloud_transfer_endpoint.clickstream-source[count.index].id
  target = doublecloud_transfer_endpoint.clickstream-target[count.index].id
}

Challenges and Trade‑offs

Offset management : With the native Kafka engine, malformed records can stall the pipeline until an operator manually deletes the offending offset.

Observability : Processing inside ClickHouse limits visibility to ClickHouse logs.

Scalability : Parsing and writing inside the ClickHouse cluster can create CPU and I/O contention under peak load.

External Writer Features (DoubleCloud Transfer)

Automatic offset handling : Damaged rows are isolated without manual intervention.

Enhanced observability : Dedicated dashboards and alerts expose latency, rows transferred, and bytes transferred.

Dynamic scalability : Transfer jobs run in Kubernetes, EC2, or GCP instances, scaling independently of the ClickHouse cluster.

Supports automatic schema evolution and dead‑letter queues for failed records.

Alternative Managed Writer (ClickPipes)

ClickPipes offers a hosted integration platform that connects Kafka to ClickHouse with similar capabilities, documented at https://clickhouse.com/cloud/clickpipes and https://clickhouse.com/docs/en/integrations/clickpipes.

Conclusion

Choosing a Kafka‑ClickHouse integration depends on workload size and operational constraints:

Native ClickHouse Kafka engine – simple, suitable for small streams, but limited observability and scalability.

Kafka Connect sink – offloads parsing and loading to a dedicated service, preserving exactly‑once semantics.

External writer (e.g., DoubleCloud Transfer) – provides automatic offset management, rich monitoring, and independent scaling for large‑scale production pipelines.