Applying ClickHouse for Offline and Real‑Time Data Analysis in JD's Golden Eye Business

ClickHouse is an open‑source columnar analytical database that achieves extreme query performance through vectorized execution and SIMD instructions, allowing a single SIMD call per batch of column data and delivering multiple‑fold speed gains. JD's Golden Eye business leverages this capability for offline traffic data analysis.

The Golden Eye platform serves as a central analytics entry for JD’s procurement and operations, providing dashboards that support strategy formulation and performance review. Rapid changes in personnel, organization, brands, and categories generate massive, constantly updating data—daily updates exceed hundreds of billions of rows—requiring a stable, high‑throughput query engine.

Easy OLAP Design

Data sources include real‑time JDQ (Kafka topics) and offline Hive tables. ClickHouse is accessed via the official JDBC driver; real‑time streams are imported through Flink, while offline data is loaded using Spark jobs.

Monitoring of the Easy OLAP ClickHouse pipeline relies on node_exporter for host‑level metrics and ClickHouse’s Prometheus port for service‑level metrics, which are scraped by Prometheus and stored in a time‑series database. Grafana can query ClickHouse directly, and many system tables have been materialized as distributed tables for dashboard visualisation.

High‑Availability Architecture

Key concepts:

Cluster: a physical ClickHouse cluster.

Group: a logical grouping within a cluster that provides replica‑level HA.

Node: a physical machine.

Instance: a ClickHouse server process.

shard_replica: identifiers such as 0_0, 0_1 indicating replicas within the same shard.

Design highlights include the logical Group concept for replica HA, storage‑policy‑driven multi‑instance and multi‑disk deployment on a single machine, and using Group as the minimal unit for scaling, allowing cluster expansion with minimal or no data migration.

ClickHouse Data Ingestion Tool

Instead of Waterdrop, JD built a Spark‑based automatic ingestion tool. By configuring ClickHouse datasource, table name, partition, and index, the tool can push data from HDFS to ClickHouse. It also supports data trimming, schema changes, transformations, and on‑the‑fly aggregations.

1. Cluster Checks

Metadata completion: verify existence and consistency of system.tables on all nodes; auto‑map Hive schema to ClickHouse types and create local and distributed tables on‑cluster.

Cluster load: query node CPU, memory, and load; if thresholds (CPU > 80 %, memory > 70 %, load > 60 %) are exceeded, dynamically reduce concurrency or pause ingestion.

Data deletion check: handle large partitions (>1 GB) by detaching then dropping later; small partitions are verified via count after deletion.

Metadata update: apply DDL on‑cluster for added, modified, or removed columns, recreating distributed tables as needed.

2. Data Transfer

Data sharding: two methods – generate a UUID per offline batch and hash it, or compute coalesce(cast(abs(hash(A,B,…)) as int) % shardCount,0) using high‑cardinality fields to ensure balanced distribution.

Concurrent ingestion: set parallelism to ¼ of shard count; Spark dynamic allocation limits executors (e.g., spark.dynamicAllocation.maxExecutors=4) to keep per‑node write threads below 15, minimising query impact.

Exception handling: capture rows that fail to load, count them, and report for upstream correction, ensuring source rows = ClickHouse rows + exceptions.

3. Data Validation

Volume check: source rows minus exception rows should equal rows inserted into ClickHouse.

Metric sanity: UV, PV etc. should not exceed expected peaks outside major promotions; alerts trigger upstream verification.

Null check: zero‑row tasks or fields with many nulls raise warnings.

Daily Spark ingestion processes add billions of rows, peaking at hundreds of millions per minute, with replica sync within a minute. The unified service supports multi‑dimensional queries at sub‑second latency, handling 30+ QPS with zero‑downtime guarantees.

ClickHouse Query Architecture

1. Materialized Views: asynchronous views replace fields with AggregateFunction(uniq, String) (or variants like uniqCombined, uniqHLL12). Data is inserted using uniqState(A) and queried with uniqMerge(A), reducing data volume by ~70 %.

2. Dictionary Functions: concurrent updates via ON CLUSTER; large dictionaries (tens of GB) use HASHED() or COMPLEX_KEY_HASHED(). Example query:

select dictGetString('db.dict_dept','dept',tuple('1')) AS id, dictGetString('db.dict_id_name','name',tuple(id)) AS name

.

3. Data Refresh (刷岗): pre‑aggregate traffic detail tables daily, reducing raw data by ~70 %; dictionary tables are refreshed in memory, cutting query time by ~30 % and supporting 30+ QPS under high concurrency.

4. Multi‑Active Clusters: to overcome ClickHouse’s concurrency limits, JD deploys multiple active clusters. Queries are split by size (day vs. month) and routed proportionally based on cluster load, with independent thread pools and circuit‑breakers to protect each cluster.

Comparison with Doris shows ClickHouse’s bottleneck lies in concurrency; multi‑replica and multi‑active strategies mitigate this, while caching layers (SQL cache, request‑parameter cache, model cache) stored in JIMDB (distributed Redis) further improve latency.

Technical Trade‑offs

Ingestion tasks launch independent Spark jobs per shard; before each launch, node CPU, memory, and load are checked, pausing low‑priority jobs when resources are tight.

Single‑metric aggregation tables accelerate queries but increase storage and write I/O pressure.

UV/ PV metrics use HLL approximations, sacrificing a small accuracy loss (~0.15 %) for faster response.

Optimizations for Major Promotions (e.g., 11.11)

Materialized views at various granularities (PV, UV, active user, purchasing user) support sub‑second QPS during peak traffic.

Thread pools per cluster are sized (60‑120) based on load; queries exceeding 30 s are killed; CPU > 90 % triggers ingestion throttling.

Index tuning based on top‑200 slow queries from system.query_log, moving selective fields forward.

Pre‑caching of frequent department‑level queries reduces real‑time load.

Conclusion and Outlook

Replacing Presto with ClickHouse in the Golden Eye business improves development efficiency, adapts to multidimensional changes, and reduces compute resources by using ClickHouse as the aggregation layer of the offline warehouse. Ongoing challenges such as memory overflow, metadata inconsistency, and node failures are addressed through quota optimization, automated monitoring, and tooling. Future work includes leveraging bitmap indexes for precise UV deduplication to reach 50+ QPS, further partitioning, query‑type thread‑pool separation, and rate‑limiting to protect the cluster.