Lakehouse Architecture at Bilibili: Query Acceleration and Index Enhancement Practices

The article introduces the concept of a lake‑warehouse (lakehouse) architecture, explaining the characteristics of data lakes—unified storage, support for all data types, and openness to multiple compute engines—and data warehouses—strong schema, high query efficiency, and reliable data quality.

It then outlines Bilibili's current big‑data platform built on Hadoop (HDFS, Hive, Spark, Presto) and the need for a dedicated distributed warehouse (ClickHouse) for interactive analytics, highlighting the challenges of data duplication and consistency when moving data between the lake and warehouse.

Three primary goals of Bilibili's lakehouse are presented: (1) retain the flexibility of a data lake using unified HDFS storage and seamless integration with Spark, Flink, Presto, etc.; (2) achieve the efficiency of a data warehouse by optimizing data organization, indexing, and pre‑computation, especially using Iceberg tables; (3) provide a low‑friction, intelligent user experience that automates data sorting, indexing, and query optimization.

The core of Bilibili's lakehouse is Iceberg, chosen over Hudi and Delta Lake. Real‑time data flows from Kafka through Flink into Iceberg on HDFS, while batch data is written via Spark. The MagnuS service continuously optimizes Iceberg tables (sorting, indexing) using Spark jobs, and Trino serves as the query engine with Alluxio caching metadata and index data.

To improve query performance, the article discusses data sorting strategies. Using a star‑schema benchmark, it shows how Z‑Order sorting (interleaving bits of multiple columns) and Hilbert curve ordering can significantly reduce data scanning. It also describes the challenges of preserving ordering across multiple filter columns and introduces a boundary‑based interleave index that ensures positive integer Z‑Values.

Index enhancements include file‑level Bloom filters for equality queries and BitMap indexes for range and multi‑condition queries. To address the high storage cost of BitMaps, the article introduces Range‑Encoded BitMaps (allowing efficient range queries with at most two BitMaps) and Bit‑Slice Encoded BitMaps (compressing many cardinalities into a few BitMaps). Combining these techniques yields 1‑10× query speedups and up to 400× reduction in file reads.

Finally, the article notes the implementation of new SQL APIs in Iceberg and Spark for specifying file‑level sorting (distributed by, locally ordered by) with options such as hash, range, Z‑Order, and Hilbert curve, and provides configuration guidelines to achieve minimal file access in multi‑dimensional analytical workloads.