ClickHouse in Self‑Service Analytics: OLAP Selection, Platform Architecture, Optimization Practices, and Future Outlook

The article begins by outlining the need for a real‑time, multi‑dimensional analytics engine to replace traditional offline pre‑computation methods, and presents the criteria for OLAP engine selection: performance, flexibility, and complexity.

It evaluates several open‑source OLAP solutions—including Kylin, Druid, Impala, Presto, Doris, and ClickHouse—highlighting their strengths and weaknesses, and concludes that ClickHouse best meets the requirements due to its columnar storage, vectorized execution, and millisecond‑level query response.

The next section introduces the Gauss self‑service analytics platform, describing its two core functions (event data management and self‑service analysis) and its four‑layer architecture: data collection (MySQL, Kafka, Flume), storage (Kafka, HDFS, ClickHouse), service (HTTP API), and application (analysis and user‑profile products).

Specific business scenarios on the platform are illustrated, such as behavior analysis using materialized views and aggregate tables, and AB‑test analysis powered by Flink‑CDC streaming data into ClickHouse for real‑time metric calculation.

Optimization practices are then detailed: memory‑related tuning (using approximate functions, adjusting max_bytes_before_external_group_by/sort), key performance parameters (max_concurrent_queries, max_memory_usage, background_pool_size, etc.), and techniques for handling billion‑row joins by pre‑partitioning data on the join key to enable local‑node joins.

The article concludes with a discussion of current pain points—limited high‑concurrency capability, lack of transactional DDL, absence of row‑level updates/deletes, and manual re‑balancing—and proposes future directions such as service platformization, containerized deployment, hybrid engine selection (ClickHouse + Doris), and kernel‑level enhancements to support distributed transactions and eliminate Zookeeper dependencies.

Overall, the piece shares practical insights into ClickHouse deployment for large‑scale analytics, covering ecosystem overview, architectural design, performance tuning, and roadmap considerations.