How Bilibili Supercharged OLAP Queries with Iceberg Lakehouse Optimizations

Background

Bilibili’s original Hadoop‑based data service stack combined multiple storage engines (Elasticsearch, TiDB, ClickHouse, InfluxDB, HBase) behind a unified query service. The architecture suffered from complex data extraction, inconsistent data copies, and performance bottlenecks.

To obtain the flexibility of a data lake while retaining warehouse‑level query speed, the team introduced a lake‑warehouse architecture based on Apache Iceberg on top of Hadoop.

Lake‑Warehouse Architecture in Bilibili’s OLAP Platform

Data sources (APP event logs, MySQL business tables, service logs) are ingested via the internal Lancer transmission service and written to Iceberg tables in the ODS, DWS, and DWD layers.

Near‑real‑time ingestion: Flink jobs with 5‑minute checkpoint intervals write directly to Iceberg.

Batch ingestion: Hourly or daily loads also target Iceberg tables.

Query workloads are served by Trino as a unified SQL engine; Alluxio provides caching and stability. For latency‑critical use cases, Iceberg tables are exported to ClickHouse.

Iceberg Core Optimizations

Data‑Organization Sorting

Iceberg stores file‑level metadata (manifest files) with column min/max statistics. By sorting data on high‑cardinality columns, the query engine can prune entire files, dramatically reducing I/O.

Z‑Order Sorting

Linear sorting on a single high‑cardinality column often leaves secondary columns unsorted, limiting pruning. Bilibili implemented Z‑order sorting, which interleaves the binary encodings of 2‑4 selected columns to produce a Z‑value that clusters rows spatially. In large tables this yields 80‑90% file‑pruning effectiveness.

Secondary Indexes

Bloom filter indexes for low‑overhead point lookups on high‑cardinality fields.

Bitmap indexes for multi‑field combination queries and range scans.

Specialized string indexes: BloomRF, TokenBF, NGramBF for log‑search scenarios.

Benchmarks on the SSB 1000 wide‑table workload showed query latency improvements of 1‑10× and a reduction of file reads by 2‑400×.

Star‑Schema & Pre‑Computation

For join‑heavy queries, dimension‑table predicates are pushed down using dynamic filters. Pre‑computed aggregates (count, avg, max, min, sum, distinct‑count, approx‑distinct, percentile, top‑N) are materialized as cubes. Query rewrite logic can hit these pre‑computed results, delivering several‑fold speedups on 1 TB test datasets.

Magnus – Intelligent Management Platform

Magnus automates Iceberg metadata management, optimization execution, and recommendation generation.

It monitors commit events from Spark, Flink, and ETL jobs, extracts table snapshots, manifests, and partition statistics.

Based on these metadata, Magnus schedules optimization jobs such as small‑file merging, data‑organization sorting, index building, cube generation, and metadata rewriting.

Job scheduling respects per‑datacenter YARN queues and configurable resource limits to avoid interference with ad‑hoc queries.

Magnus also collects Trino query logs, analyses filter predicates, field cardinalities, and query frequencies. Using weighted heuristics it recommends partitioning keys, sorting columns, and index types for each table.

Future Work

Introduce Star‑Tree pre‑computation to handle high‑cardinality dimension fields.

Extend recommendation support to historical analysis workloads.

Close the recommendation‑application loop with full automation (no manual intervention).

Broaden CDC (change‑data‑capture) ingestion capabilities for additional business scenarios.