How ClickHouse Powers a Billion‑User Profiling Platform at Sub‑5‑Second Latency

Business Background

NetEase’s location product line uses a user‑profile platform for questionnaire automation, digital compass and other scenarios, providing tag classification, crowd size estimation, crowd portrait and crowd‑package creation to enable fine‑grained user operation and precise targeting.

Challenges

Large data volume : Over 1.2 billion users, 30 k+ tags, crowd packages can reach billions of rows.

Complex calculations : Tag selection may involve hundreds of conditions requiring extensive set operations.

Short query latency : Crowd estimation and creation must return within 5 seconds.

Complex business scenarios : Multi‑billion‑row joins and aggregations are required.

Why Choose ClickHouse?

ClickHouse offers complete DBMS features (DML, DDL, permissions, backup, distributed management), column‑store with compression, relational model with SQL, diverse table engines, data sharding and distributed query, a rich function set, and true real‑time query performance.

Core Table Design

The platform relies on four core tables:

upp_user : Stores >1 billion users and their tag information (wide table). Uses ReplicatedMergeTree engine with ORDER BY and index_granularity=8192.

upp_label_bitmap : Stores tag enumerations and associated user ID lists as bitmap columns ( groupBitmapState, AggregateFunction(groupBitmap, UInt64)).

upp_customer_group_bitmap : Stores crowd IDs and their user ID bitmap lists.

upp_user_geo : Stores longitude and latitude for geo‑based crowd selection.

Data Ingestion Layer

Offline data is imported via a Spark job that syncs wide tables and tag data from Hive to MySQL and ClickHouse (using ClickHouse‑JDBC). Example SQL to generate bitmap data:

insert into upp_label_bitmap (tag_key, dwids) select 'age#5', groupBitmapState(dwid) from upp_user where has(splitByChar('^', age), '5');

Data Storage Layer

ClickHouse is deployed with Chproxy and multiple nodes for load balancing and high reliability.

Data Service Layer

Tag Management handles hierarchical tag management and provides query interfaces; tags are stored in MySQL (~30 k entries).

Crowd Management creates, updates, deletes crowds and computes crowd size using bitmap operations (AND, OR, XOR). Example to count males in a crowd:

Perform bitmapAnd() between the gender bitmap and the crowd bitmap.

Use bitmapCardinality() to get male count.

Use bitmapCardinality() on the crowd bitmap for total count.

Divide to obtain the percentage.

Bitmap functions used include bitmapCardinality(), bitmapAnd(), bitmapOr(), groupBitmapOrState(), and greatCircleDistance() for geo‑based selection.

Problems and Solutions

Slow bitmap queries : Unordered bitmap values caused large storage and read amplification. Solution: Make dwid an ordered auto‑increment field, reducing query time from 3 s to 20 ms.

Slow tag‑based bitmap operations : Large index granularity caused read amplification. Solution: Reduce index_granularity to ≤50, cutting execution time to ~300 ms.

Future Plans

Generate bitmap objects directly in Hive using a Roaring Bitmap UDF to speed up ingestion, add caching layers to handle high‑concurrency queries, and choose appropriate table engines (Join, SummingMergeTree) based on workload.

Conclusion

ClickHouse meets the demand for real‑time, large‑scale data analysis, providing sub‑second feedback for arbitrary metrics and dimensions. This article introduced ClickHouse’s core concepts, architecture, practical experience building a user‑profile platform, and future optimization directions.