How ByteDance Scaled Real‑Time Analytics with ClickHouse and Kafka Engine

Early Practice and External Transactions

Initially, ClickHouse supported two offline business scenarios—user‑behavior analysis and agile BI—using T+1 metrics. Since ClickHouse lacks native transactions, an external transaction layer was built: data prepared in HDFS/Hive, processed by Spark, split into N parts, and imported into each ClickHouse shard via INSERT queries. If any shard failed, the import service rolled back the partial data and retried until all shards succeeded, ensuring queries only see fully imported data.

Risks of Direct INSERTs

Directly writing to ClickHouse can use either Distributed tables or MergeTree tables. Writing to Distributed tables buffers data locally before async distribution; node failures can cause data loss or duplication, and high shard counts explode the number of small part files, overwhelming the filesystem. Writing directly to MergeTree avoids distribution overhead but still suffers from high‑frequency write pressure that can outpace background merges.

Typical Case 1: Recommendation System

Real‑time metrics are needed for AB experiments to quickly evaluate recommendation algorithms. Requirements include debugging at both aggregate and detail levels, handling hundreds of dimensions, efficient filtering by experiment ID (stored as arrays), and supporting ML/statistical metrics such as AUC.

Compared to Druid and Elasticsearch, ClickHouse offers better support for detailed queries and dynamic dimensions via Map columns.

Bloom filter indexes enable fast experiment‑ID filtering.

Solution Design and Comparison

Conventional Approach: Flink consumes Kafka, then writes to ClickHouse via JDBC. This separates responsibilities but adds resource overhead, makes write‑frequency control hard, and increases operational cost.

Kafka Engine Approach: ClickHouse’s built‑in Kafka Engine creates a Kafka‑type table that internally runs a consumer thread, pulls data from Kafka partitions, parses it, and writes directly to MergeTree tables on each node. This reduces data‑transfer steps and simplifies the architecture, though it sacrifices some extensibility.

Kafka Engine Mechanics

Three tables are required: a MergeTree table for storage, a Kafka table describing the topic and parsing logic, and a materialized view that links the two and can perform ETL‑style transformations. When all three exist, a consumption task is automatically started.

Typical Case 2: Advertising Delivery

Advertising teams need real‑time insight into campaign performance, which involves multi‑day partitions and hundreds of dimensions. Druid struggled with segment explosion and pre‑aggregation inefficiency, so the system migrated to ClickHouse + a custom agile BI platform, consuming raw detail data.

Improvements for Advertising Use‑case

Buffer Engine Enhancement: Buffer Engine caches incoming rows in memory, reducing the number of parts generated. Integration with Kafka Engine allows optional buffering and supports ReplicatedMergeTree queries.

Consumption Stability: Offsets are bound to part files, forming an atomic transaction that rolls back both offset and data on failure, preventing data loss or duplication.

Practice & Experience

A platform was built to simplify creation, review, and management of Kafka → ClickHouse consumption tasks, abstracting the three‑table requirement into a user‑friendly UI. The platform also infers table schemas from Kafka data, reducing manual effort.

Diagnostic Enhancements

System tables such as system.kafka_log and system.kafka_tables record consumption events (POLL, WRITE, EXCEPTION) and partition assignments, enabling operators to monitor throughput, detect anomalies, and quickly locate problematic topics, partitions, or offsets.

SQL Extensions

New commands like SYSTEM START/STOP and SYSTEM RESTART allow fast control of consumption jobs. ALTER queries have been extended to modify table schemas without rebuilding tables.

Future Outlook

Implement distributed transactions in ClickHouse to stabilize Kafka Engine consumption and direct INSERTs.

Introduce read‑write separation and dynamic scaling of consumption nodes.

Add WAL and Buffer layers to handle high‑frequency writes and eventually support distributed table‑style data distribution.

Re‑enable direct INSERT usage once the above features mature.

Overall, ByteDance’s internal ClickHouse ecosystem has evolved from simple offline ingestion to a sophisticated real‑time analytics platform, addressing performance, consistency, and operational challenges through engine enhancements, platform tooling, and roadmap‑driven feature development.