Real‑time OLAP Analytics for QQ Music Using ClickHouse and Tencent Cloud EMR

OLAP (On‑Line Analytical Processing) is a core application model of data‑warehouse systems, enabling analysts to explore data from multiple angles. This article presents a real‑time big‑data analytics solution for QQ Music, built on a partnership between QQ Music’s data team and Tencent Cloud EMR.

QQ Music serves over 800 million registered users and generates petabyte‑scale daily data from music streams and user behavior. Traditional offline analysis with Hive could only provide T+1 latency, which is insufficient for the platform’s need for instant insights into PV, UV, DAU, user segments, and popular tracks.

The QQ Music data team constructed a high‑availability, low‑latency OLAP platform based on ClickHouse and Superset, leveraging Tencent Cloud EMR’s elastic Hadoop services. The platform processes trillions of new records per day, runs on tens of thousands of CPU cores, and delivers second‑level query response times.

Key technical challenges and solutions:

1. SSD‑based ZooKeeper: Replacing HDD ZooKeeper with SSD dramatically improved metadata I/O, eliminating replication delays under high write concurrency.

2. Data write consistency: A global load‑balancing strategy ensures idempotent writes to the same shard, guaranteeing consistency between ClickHouse and Hive results.

3. Real‑time and offline data ingestion: Using the Tube message queue, the system uniformly distributes both streaming and batch data, achieving efficient, safe ingestion of petabyte‑scale workloads.

4. Table partition optimization: Limiting partitions to fewer than 10 000 and converting hourly partitions to daily partitions reduces file descriptor usage and improves query performance.

5. Read/write separation: Temporary ClickHouse nodes perform merge and sorting tasks, then sync results to the production cluster, enabling high‑throughput writes without degrading read performance.

6. Cross‑table query localization: Consistent hashing routes rows with the same primary key to the same shard, allowing local joins and avoiding costly global joins.

ClickHouse’s columnar storage, vectorized execution, and SIMD code generation deliver query speeds 3‑6× faster than Presto/SparkSQL and 30‑100× faster than Hive for typical workloads.

Superset provides a modern, enterprise‑grade BI web UI that connects to ClickHouse, offering thousands of visualizations. Over 60 % of the charts are created by non‑technical users, achieving “self‑service BI” across product, operations, finance, and research teams.

Integrating ClickHouse + Superset on Tencent Cloud EMR combines the openness of the open‑source stack with the operational simplicity of a managed service: rapid cluster provisioning (hundreds of nodes in minutes), elastic scaling, automated monitoring, and 24/7 professional support.

The solution has been applied to real‑time analytics, recommendation systems, and tag storage (via HBase), demonstrating the versatility of a cloud‑native big‑data architecture for internet‑scale services.