Optimizing Hotel List Page Data Warehouse Flow at Qunar.com: A Technical Case Study

Hui Shanxiang, who joined Qunar.com in 2021, works in the domestic hotel data warehouse team responsible for offline and real‑time data warehouse development, maintenance, and optimization, focusing on computational efficiency, data governance, SLA assurance for S‑level reports, and link uniqueness.

Background : With the tourism market rebounding and travel demand surging, Qunar’s hotel daily bookings reached record highs, causing the hotel data warehouse to process ever‑increasing volumes. To ensure timely production of S‑level reports, continuous optimization of the data processing pipeline was required.

The core wide tables for hotel traffic include search (S page), list (L page), detail (D page), booking (B page), and order (O page) tables, representing user traffic across the main hotel flow.

Problem : The L‑page traffic table, the primary entry point for downstream reports, suffered from long execution chains and increasing latency, with SLA buffers shrinking and tasks often missing the 9 AM baseline.

Current Situation Analysis : A diagram (not shown) illustrated two major tasks—L‑page intermediate table and L‑page traffic table—highlighting upstream dependencies and staggered completion times. Several stages (e.g., tasks ⑩, ⑥, ⑧) exhibited delayed execution due to large data volumes, low parallelism, or unstable upstream sources.

Optimization Plan :

1.1 "Split" large SQL logic : Decompose monolithic queries into five logical streams (USER, DIM, LIST, EVENT, SEARCH) to allow independent execution once upstream dependencies are satisfied.

1.2 "Lift" immediate execution : Trigger sub‑tasks as soon as their upstream data is ready and pre‑filter unnecessary columns to reduce data volume.

1.3 "Parallel" non‑dependent sub‑queries : Run independent branches concurrently to improve resource utilization and avoid contention.

1.4 "Delay" buffer for unstable dependencies : Move late‑producing or unstable tasks to the end of the pipeline, providing extra buffer time.

2. Eliminate long‑tail effects : Identify and address dynamic partition sorting issues that cause task skew and OOM, and add random salts to NULL join keys to distribute load.

3. Optimize compression timing :

Compress at storage level to reduce space.

Compress intermediate shuffle data to lower network traffic.

Compress final output to shrink result size.

Specific actions included converting intermediate tables to TEXTFILE without compression to increase split count, adjusting ORC compression settings, and reordering join tables to improve MapJoin conversion.

Implementation Details :

Key SQL snippet used during the process:

insert overwrite table dw_hotel_search_di partition(dt=${DATE},platform,hour)

Analysis of Hive execution plans revealed that Stage‑6 (the final Reduce output) caused a long tail due to unnecessary sorting; disabling hive.optimize.sort.dynamic.partition=true for low‑cardinality partitions eliminated this overhead.

Results :

Production time advanced by nearly 4 hours (from ~9:30 AM to ~5:30 AM).

Storage consumption reduced by 93 % (from ~230 GB to ~15 GB) while keeping the same schema.

Query performance improved six‑fold (from 2 h 21 min to 22 min for a set of 28 analytical queries).

Overall, the "split‑lift‑parallel‑delay" methodology streamlined the L‑page traffic pipeline, mitigated data skew, enhanced compression efficiency, and delivered stable, faster data delivery for downstream analytics and reporting.