About Shopee

At Shopee, we use StarRocks across several data platforms to cover a range of analytical needs. This article covers three of them: Data Service, where StarRocks materialized views on Hive tables accelerate API queries; Data Go, where StarRocks speeds up multi-table join queries; and Data Studio, where we tested StarRocks against Presto for ad-hoc queries.

When joins and nested subqueries dominate the run time, the StarRocks optimizer gives a clear advantage over Presto. When I/O dominates, the difference is much smaller.

Key takeaway · Optimizer-bound queries benefit most

This article was originally written by Ma Teng at Shopee.

The challenge

All three platforms queried Hive tables through Presto, and each ran into a different limit.

Data Service: complex API queries

Data Service is a platform at Shopee for turning data into APIs and managing the entire API lifecycle. Users select Hive tables and columns, configure filtering conditions, and create APIs. Data Service generates queries on the backend based on the user's configuration, and when users call the API, it runs the query and returns the data. When the query is too complex, Presto may take minutes to hours to finish, and sometimes it never finishes.

Data Go: multi-table joins

Data Go is a codeless query building platform for business users who need specific metrics (for example, shop performance or order GMV) from the data models they can access. A data admin creates each data model from one or more tables and columns. Users choose a data model, select output columns, and apply filters. When they run the query from the UI, Data Go uses Presto to retrieve the data and download it as a CSV file. When data comes from multiple tables, multi-table join queries are hard to optimize, which increases resource consumption and query time.

Data Studio: complex ad-hoc queries

Data Studio is a data development platform for ad-hoc and scheduled data jobs. For an ad-hoc query, users create an empty file, enter the query, and click Run. This consumes considerable resources, and complex analytical queries often take a long time to return results.

The solution

StarRocks is a SQL query engine with a vectorized execution engine, a cost-based optimizer, data cache, and materialized views (MVs) with transparent query rewrite. Besides its own table format, it can directly query data lake table formats such as Hive, Iceberg, Delta Lake, and Hudi. With its built-in catalog capabilities, data lake tables can be queried after a single CREATE EXTERNAL CATALOG statement.

Data Service: materialized views on external Hive tables

We chose StarRocks for Data Service for three reasons:

StarRocks supports queries on external Hive tables in production.
StarRocks supports building materialized views based on external Hive tables.
With StarRocks MVs on external Hive tables, we don't have to maintain a real-time or near-real-time ingestion pipeline from Hive into StarRocks internal tables. This saves development effort and the components a real-time pipeline requires. Users don't need to change anything to get faster API queries.

We also considered ClickHouse, which can use materialized views to precompute data. However, ClickHouse has limited support for data lakes, and there are no plans for it in its community roadmap. Building OLAP-like functionality with ClickHouse would require manual API development or moving Hive data into ClickHouse for precomputation with ClickHouse MVs. Another option is to implement real-time precomputation in Flink and write the results to ClickHouse. Either way, it takes significant engineering effort and adds ETL pipeline components.

Architecture comparison over Hive: on the left, data reaches ClickHouse materialized views either through raw data ingestion into ClickHouse storage or through Flink precomputing; on the right, a StarRocks cluster of FE and BE nodes builds a materialized view directly on Hive — ClickHouse precomputation vs. StarRocks MV on Hive

We call this feature "OLAP-speedup." Data Service rewrites the user's SQL into an OLAP-speedup query. In the example below, the SQL inside the WITH clause is a common table expression (CTE).

SQL example of a Data Service API query: a CTE named base selects forecast columns from a Hive table, and the outer query filters by region, SOC ID, time points and hub IDs, then aggregates yhat — Example Data Service API query with a CTE

Data Service builds the CTE part of user queries as StarRocks materialized views. Queries that reference these MVs read precomputed results instead of recomputing them.

Flow diagram: when an API is defined, the CTE is extracted and turned into a CREATE MATERIALIZED VIEW statement on the StarRocks cluster, which queries Hive and saves the precomputed result; when the API is called, a system table is checked and, if the MV is ready, the query is served from the StarRocks MV — OLAP-speedup flow in Data Service

During an MV refresh, StarRocks pulls the precomputed data from Hive into local StarRocks storage according to the MV's logic, so the MV provides both ETL and precomputation. Neither the business users of Data Service nor its developers need extra work to maintain a real-time write pipeline, which saves labor and the cost of the ETL components such a pipeline needs.

Data Go: StarRocks for join queries

Data Go now uses StarRocks instead of Presto to query join tables. The main reason is the StarRocks cost-based optimizer, which is purpose-built for its vectorized execution engine and lets StarRocks run multi-table join plans quickly and at low cost. Data Go's cluster is configured with a 10 GB in-memory data cache and a 400 GB disk data cache.

Data Studio: StarRocks on Hive for ad-hoc queries

StarRocks supports external catalogs such as Hive, so users can query Hive tables directly. Its massively parallel processing (MPP) framework splits a single query into multiple physical computing units that run in parallel across machines and use all available CPU cores. This makes StarRocks well suited to complex analytical queries with aggregations and GROUP BY.

To address Data Studio's problems with complex queries, we are running experiments that compare StarRocks with our existing engine, Presto, on performance and error rates. The test setup:

Computing resources: StarRocks and Presto each with 400 cores and 2,700 GB memory.
SQL: Data Studio daily ad-hoc queries from March 25 and March 29, 2024.
Method: concurrent stress tests on StarRocks and Presto with 1, 5, 20, 40, and 50 threads.
Latency statistics: average, p90, and p99 latency for each engine at each concurrency level.

StarRocksApache Hive

The results

Data Service: 10 to 2,000 times faster API queries

OLAP-speedup made queries 10 to 2,000 times faster and reduced resource consumption. Data Service plans to launch 10 high-traffic APIs on OLAP-speedup, accounting for 70% of total platform traffic. Five of them are already online.

The table below shows execution time statistics for these five APIs. Each one is run once through Presto querying Hive and once through StarRocks materialized views, so the p90 and p99 execution times can be compared directly.

Table of five Data Service APIs showing 24-hour query counts, result consistency ratios, StarRocks p90 and p99 latency in milliseconds, and Presto-to-StarRocks latency ratios at p90 (11 to 1,024) and p99 (10 to 2,187) — Data Service APIs: StarRocks MVs vs. Presto on Hive

query_count is the number of calls to the API in the selected 24-hour window.
result_consistency_ratio compares the StarRocks-accelerated results with Presto querying Hive directly. The fifth API scores low because its source table is updated hourly while its marker is set for daily updates, and StarRocks MV refreshes follow the marker configuration.
sr_p90 and sr_p99 are query latencies with StarRocks MVs; the last two columns are the p90 and p99 improvement of StarRocks MVs over Presto on Hive.

Some APIs improved by more than 1,000 times because StarRocks finishes within the time window while Presto is slower and tends to queue, so a large share of the API latency on Presto was queuing time.

Data Go: 3 to 10 times faster with 60% less CPU

We tested StarRocks and Presto across multiple data models in several countries.

Line charts for April 16 to 29, 2024 comparing average and maximum SQL duration of StarRocks and Presto across Data Go data models by country, including order, product, SKU and user groups; StarRocks stays well below Presto in both charts — Data Go: StarRocks vs. Presto query duration by data model

Monitoring of live Data Go workloads shows that StarRocks is 3 to 10 times faster than Presto for queries on external Hive tables. For the "Product Group - MY" data model, a complex query like the sample below takes 364 seconds on average with Presto and 20 seconds with StarRocks. StarRocks can therefore process more queries in the same time, with fewer resources allocated to each query.

SQL for the Product Group - MY data model: two CTEs aggregate item GMV and placed orders, which are then left-joined with an item features table and filtered by shop ID — Sample Product Group - MY query

CPU usage of the two clusters:

CPU usage of the StarRocks Data Go cluster from April 16 to May 2, 2024, peaking around 150 cores — StarRocks cluster CPU usage

StarRocks: peak 150 cores, average 40 cores.

VCores utilization of the Presto datago-scheduled queue from April 2 to 8, 2024, with peaks near 2,000 cores — Presto cluster CPU usage

Presto: peak 2,000 cores, average 100 cores.

In Data Go's online usage, StarRocks queries on external Hive tables are 3 to 10 times faster on average than Presto on Hive while using 60% less CPU. There are two main reasons:

Data Go's query patterns are fairly fixed, mainly joins across multiple tables that are updated daily. With the cluster's 10 GB in-memory and 400 GB disk data cache, the cache hit rate for these queries is high.
The StarRocks optimizer is more efficient than Presto's. For complex queries where the execution plan and computation take most of the time (such as joins and nested subqueries), StarRocks has a clear advantage. For queries where I/O takes most of the time, the difference is much smaller.

Data Studio: 2 to 3 times faster on the same resources

Table of average, p90 and p99 elapsed time in seconds for StarRocks and Presto at 1, 5, 20, 40 and 50 concurrent threads; at 50 threads, p99 is 301.1 seconds for StarRocks and 3,600.7 seconds for Presto — Data Studio test results

Bar and line charts of the same elapsed times by concurrency; Presto p99 rises to about 3,600 seconds while StarRocks stays at or below about 300 seconds — Elapsed time comparison by concurrency

With the same computing resources (400 cores and 2,700 GB memory), StarRocks was 2 to 3 times faster than Presto in the Data Studio tests. In other words, StarRocks can deliver comparable compute capacity with 50% of the resources Presto uses.

Here the gain comes mainly from the StarRocks optimizer. Daily ad-hoc queries in Data Studio have no fixed pattern; they are mostly complex queries with many nested subqueries and joins, which is the query pattern StarRocks handles best. Because ad-hoc query patterns are unpredictable, data cache has little effect in this scenario.

StarRocks is now used across Shopee, including Data Service, Data Go, and Data Studio. In these platforms it gives us fast queries on external data lake tables, faster multi-table joins, and lower resource usage than Presto.