Comparative Analysis of Apache Kylin and ClickHouse: Architecture, Storage, Optimization, and Use Cases

Apache Kylin and ClickHouse are two widely used big‑data OLAP engines. Kylin, originally developed by eBay China and open‑sourced to Apache in 2014, provides sub‑second query latency and high concurrency through pre‑computed MOLAP cubes on Hadoop, later evolving to use Spark and Parquet. ClickHouse, created by Yandex in 2016, follows an MPP architecture with a shared‑nothing design, leveraging vectorized execution, log‑merge trees, sparse indexes, and SIMD to achieve near‑CPU‑limit performance.

01 Technical Principles

Kylin builds multi‑dimensional cubes on Hadoop (using MapReduce, Spark, or Flink) and stores them in HBase or Parquet, enabling fast query responses without accessing raw data. ClickHouse implements a distributed relational OLAP engine where each node processes a portion of data independently, using a column‑oriented storage engine and vectorized execution.

02 Storage

Kylin relies on Hadoop’s ecosystem, using HBase row‑key indexes or Parquet row‑group indexes for fast access, and supports various data sources such as Hive, Kafka, and RDBMS. ClickHouse manages its own storage with the MergeTree family, employing data compression, columnar storage, and background merges to maintain performance.

03 Optimization Methods

Kylin’s optimizations focus on pre‑computation: defining aggregation groups, joint dimensions, derived dimensions, dimension table snapshots, dictionary encoding, row‑key ordering, and shard‑by columns to reduce data scanning and CPU usage.

Set aggregation groups to prune unnecessary cube combinations.

Define joint dimensions to combine frequently co‑occurring attributes.

Use derived dimensions (e.g., year, month from a date) to avoid redundant calculations.

Materialize dimension table snapshots in memory.

Apply dictionary encoding and row‑key design.

Specify shard‑by columns to limit scanned rows.

ClickHouse optimizations include partitioning, sharding, sorting keys, secondary indexes, and using specialized engines such as SummingMergeTree and AggregateMergeTree. Materialized views and flat table designs can replace costly joins, while distributed clusters add replicas and compute resources.

04 Advantage Scenarios

Kylin excels in fixed‑pattern, high‑concurrency aggregation queries (e.g., dashboards, reporting, count‑distinct, top‑N, percentile) on massive data volumes (tens to hundreds of billions of rows). ClickHouse is better suited for flexible, ad‑hoc queries with many columns and less intense concurrency, especially when detailed analysis is required.

05 Summary

The two engines complement each other: Kylin’s pre‑computed MOLAP approach delivers ultra‑fast, high‑throughput analytics for stable query patterns, while ClickHouse’s MPP, on‑the‑fly computation provides flexibility for exploratory analysis. Selecting the appropriate engine depends on query stability, data volume, and operational considerations.