Real‑Time Analytics Engine Based on ClickHouse: Architecture, MergeTree, Data Ingestion, and Query Optimization

To provide business teams with real‑time data insight and early‑warning capabilities, JD.com’s Algorithmic Intelligence department built a ClickHouse‑based real‑time analytics engine, focusing on resource‑position data aggregation and second‑level query performance.

ClickHouse is a column‑oriented DBMS originally created by Yandex for online traffic analysis; it can store trillions of rows while returning 90% of queries within one second. Its columnar storage, high compression, and full DBMS features enable dynamic, sub‑second aggregation, making it ideal for read‑heavy, batch‑write scenarios such as BI, ad traffic, IoT, and click‑stream monitoring.

The core table engine is MergeTree, which provides primary‑key indexing, partitioning, and sorting. Partitions reduce scan ranges, while the sparse primary‑key index and per‑column files allow the server to skip irrelevant data blocks, achieving fast reads. Variants like ReplacingMergeTree and AggregatingMergeTree support deduplication and pre‑aggregation.

Data ingestion combines real‑time Kafka streams processed by Flink and offline Hive data loaded via MapReduce. The pipeline includes filtering, transformation, and batch writes to ClickHouse. Batch writes use JDBC; a typical Maven dependency and Java code are shown:

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.2.4</version>
</dependency>

Class.forName(Ck.DRIVER);
Connection connection = DriverManager.getConnection(Ck.URL, Ck.USERNAME, Ck.PASSWORD);
connection.setAutoCommit(false);
PreparedStatement state = connection.prepareStatement(INSERT_SQL);
// loop over batch records
state.set...(index, value);
state.addBatch();
state.executeBatch();
connection.commit();

Table creation follows the business’s partition‑by‑time and order‑by‑key strategy, e.g.:

CREATE TABLE table_name (
    Event_ts DateTime,
    T1 String,
    T2 UInt32,
    T3 String
) ENGINE = ReplicatedMergeTree('/clickhouse/ck.test/tables/{layer}-{shard}/table_name', '{replica}')
PARTITION BY toYYYYMM(Event_ts)
ORDER BY (T1, T2);

When data volume grows to hundreds of billions of rows, simple partitioning and sorting are insufficient. Query optimization includes identifying long‑running SQL, using materialized views to pre‑aggregate (e.g., countState, uniqState), avoiding joins on detail tables, and scaling hardware. An example materialized view definition is provided.

CREATE MATERIALIZED VIEW test_db.app_hp_btn_event_test ON CLUSTER test_cluster ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/ck.test/tables/{layer}-{shard}/test_db/app_hp_btn_event_test', '{replica}')
PARTITION BY toYYYYMMDD(time) ORDER BY (btn_id, cate2) TTL time + toIntervalDay(3) SETTINGS index_granularity = 8192 AS
SELECT toStartOfHour(event_time) AS time, btn_id, countState(uid) PV, uniqState(uid) AS UV FROM test_db.app_hp_btn_event_test GROUP BY btn_id, toStartOfHour(event_time);

Comprehensive monitoring (Kafka lag, core operator exceptions, ClickHouse CPU/memory/disk alerts) and Grafana dashboards help detect anomalies early, while large‑SQL tracking ensures problematic queries are tuned.

In summary, mastering ClickHouse storage, MergeTree mechanics, and query patterns enables the creation of business‑specific tables that deliver sub‑second aggregation over billions of rows.