Originally published at: StarRocks Engineering on Medium
About iQIYI
iQIYI is one of the largest online video platforms, serving a pan-Asian entertainment audience that is mostly young and on mobile. Viewers spend about 6 billion hours a month on the service, and it has more than 500 million monthly active users.
Each workload had brought its own OLAP engine, and running all of them had become an operational burden. StarRocks covered both the real-time warehouse path and the lakehouse query path, which gave iQIYI a way to consolidate.
Key takeaway · One engine for warehouse and lake
The original post was written by Hao Lin, Head of Big Data OLAP Services at iQIYI.
iQIYI uses data in two directions. Looking back, it drives reports, series popularity and membership operations. Looking forward, it feeds data products such as search, recommendations and ads, and predictions of user growth and revenue.
The challenge
An engine for every workload
iQIYI's OLAP stack sits on three storage tiers: Apache Hive for offline data, Apache Kafka for real-time data and Apache Iceberg for recent, near-real-time data. Over the years the team added query engines on top as new needs came up. An Intelligent SQL Gateway built in-house (Pilot in the diagrams) keeps that engine mix hidden from the applications above it.
Keeping so many engines running took a lot of operations work, so the team decided to cut the number down. On the warehouse side, it had moved from MySQL and Elasticsearch (MySQL did not scale and Elasticsearch was slow) to Apache Impala with Apache Kudu on Hadoop, which performed better but was hard to scale. Apache Druid came next for large time-series data, though it could not serve detail-level queries or joins, and ClickHouse was added for speed. On the lake side, Hive gave way to Apache Spark and Trino for ad-hoc analysis and troubleshooting, where cost mattered most.
StarRocks fit both paths. For the warehouse, it matched ClickHouse on real-time performance and also handled the detailed analytics Druid could not. For the lake, it ran faster than Spark and Trino.
Advertising needs consistent data under load
The advertising platform has to answer many concurrent queries with low latency and keep its numbers consistent. It follows a Lambda design: data streams in from Kafka in real time, and offline jobs from Iceberg later overwrite it, for example after anti-fraud corrections. Kudu held the real-time data, HDFS the offline data, and Impala queried both. As data and query volume grew, this setup hit its limits.
Why the team ruled out ClickHouse
ClickHouse was evaluated as the replacement. Consistency was the main reason it lost, and two other points added to it:
- Consistency. ClickHouse's official Flink connector only guarantees at-least-once delivery, so a restarted Apache Flink job can write duplicates. The StarRocks Flink connector supports exactly-once. ClickHouse distributed tables also cannot replace data atomically, so overwrites run one node at a time, and an empty temporary table can wipe the target. StarRocks replaces data atomically per partition.
- Lake access. ClickHouse reads the lake through external tables, with no unified catalog, and its Iceberg support was weaker when iQIYI evaluated it. For disaster recovery, ClickHouse needs a standby cluster kept in sync, which costs money and maintenance. With StarRocks, the team can start an elastic cluster and query the Hive tables directly as external tables. In iQIYI's tests, those queries performed about as well as queries on internal tables.
- Operations. iQIYI compares ClickHouse to a manual transmission. Historical data does not rebalance when nodes are added, node failures need manual work to avoid data loss or missing replicas, and shrinking a cluster is close to impossible. StarRocks rebalances on its own and keeps replicas in sync automatically.
The solution
Ads platform on StarRocks
For ads, iQIYI runs two kinds of StarRocks cluster: a shared-nothing cluster with storage and compute on the same physical machines, and an elastic compute cluster. An Alluxio-based cache speeds up the elastic side. The team may later test StarRocks' built-in cache against Alluxio.
The SQL gateway sits on top. It hides engine details such as user credentials, provides high availability and routes each query according to cluster health and performance. Because every SQL statement goes through it, the gateway also records the data the team needs for later tuning and materialized view analysis.
Ad-hoc queries in Magic Mirror
Magic Mirror is iQIYI's internal ad-hoc query platform, with more than 500 users and over 1,400 queries a day, all on Spark SQL. Spark was too slow for interactive analysis. Added up, the time analysts spent waiting came to about 12.5 person-days every day.
The team looked at Trino first. However, Trino's SQL differs a lot from Spark SQL, which would have made the move hard. In iQIYI's benchmarks, StarRocks ran 2 to 3 times faster than Trino and accepted Spark SQL syntax well, so the team chose StarRocks.
The results
Advertising
With StarRocks in place of Impala and Kudu, the ad platform's overall performance improved by 400%, and P90 query latency is 4.6 times faster.
Ad-hoc queries
StarRocks took over from Spark for about two-thirds (67%) of the queries the team tried to migrate. That is a bit lower than the dual-engine tests had suggested. For the queries that did move, P50 latency improved 33x and P90 latency 15x.
Analysts now wait much less. The change freed 4.6 person-days of analyst time, about what one full-time employee contributes.
What's next
- Shared-nothing: upgrade to StarRocks 3.3, confirm the benefit of materialized views, look at tiered storage that moves historical data to HDFS, and retire ClickHouse and Druid within the next year.
- Shared-data: move more query types, fix UDF compatibility to raise the two-thirds migration rate, use materialized views to speed up external-table queries without changing them, and replace Trino.