About Lenovo
Liansheng Zhida is part of Lenovo Group, a world-leading PC manufacturer. It builds an intelligent logistics platform and provides integrated logistics solutions for industrial enterprises in China and abroad.
After the change, the RDBMS layer focuses on data cleaning and reporting calculation moved from Apache Hive to StarRocks, which became the endpoint for data service.
Key takeaway · One endpoint for data service
This article was originally written by the big data team at Liansheng Zhida, part of Lenovo Group.
Our big data team introduced several OLAP analysis engines over time, and StarRocks stood out for its performance. With StarRocks, we built a new unified data service platform that significantly reduces the complexity of our data pipelines and makes BI analysis much more efficient.
The challenge
From SQL Server to Hadoop and Presto
Before 2018, our data volume was not large. We used a traditional relational database, SQL Server, instead of a self-built data warehouse, and SQL scripts were enough.
As business complexity and data volume grew, this model hit a bottleneck. Queries slowed down: tasks that used to take 10 or 20 minutes started taking an hour. Storage capacity also became a bottleneck.
In 2019, we built a data warehouse on Apache Hadoop and Apache Hive and moved all ETL jobs to the Hadoop cluster. We used dozens of Presto nodes for OLAP analysis, because Presto shares metadata with Hive and supports flexible queries, and Tableau for data analytics and data mining.
The existing architecture
The system had four layers:
- Collection: Apache Sqoop read data from RDBMS into Apache Hive, and Apache Flume synchronized log files to Hive. Data crawled from the Internet was stored in RDBMS and then imported into Hive through Sqoop.
- Storage: Apache Hive handled all offline ETL and data modeling.
- Analysis: after ETL, data was written to RDBMS or an MPP database to serve self-service reporting (Tableau), multi-dimensional fixed reporting, and ad-hoc queries.
- Application: reporting services for management and operations staff. Queries in this layer were complex and unpredictable and needed real-time responses, especially for ad-hoc analysis.
The SEC data center
The core data of SEC (shipping to end customers) comes from two sources: consumer business and SMB business. The data center summarizes the related business metrics and provides analytics services.
Before StarRocks, a large number of Apache Hive jobs ran the ETL. After ETL, part of the data stayed in Hive and part was written to MySQL or SQL Server. Presto connected to Hive, MySQL, and SQL Server for reporting and data visualization. This architecture had two main problems:
- The data platform was not well integrated, which made it hard to maintain and slow to respond to new requirements.
- Presto was slow on the complex SQL generated by Tableau and could not meet the need for real-time data viewing.
In 2021, the big data team redesigned the offline data warehouse. We wanted to unify the system on one OLAP engine that could:
- Support low-latency BI reporting, complex ad-hoc queries, and real-time queries on detailed data.
- Ingest data at high throughput.
- Respond to flexible multi-dimensional queries in under 100 ms.
- Provide better support for multi-table joins.
- Respond in under 100 ms to queries on a single table with more than 1 billion rows.
The solution
Why StarRocks
We introduced StarRocks in 2021 because it supports ad-hoc queries with multi-table joins and complex nested subqueries, and because it supports queries on detailed data and multi-dimensional materialized views, where it outperformed ClickHouse.
Our comparison of the two engines:
| ClickHouse | StarRocks | |
|---|---|---|
| Advantages | Strong single-table query performance, suitable for multi-dimensional analysis; rich MergeTree family with pre-aggregation; well suited to large-scale log analysis | Supports both single-table and multi-table queries; high concurrency; real-time ETL in micro-batches; stream and batch data ingestion; compatible with the MySQL protocol and standard SQL |
| Disadvantages | No real deletes and updates; join support is limited; relatively low concurrency; MergeTree merges are not complete | Insufficient large-scale ETL capabilities; resource isolation not yet complete |
StarRocks met our overall requirements, and the new architecture has these advantages:
- The structure is clear. The RDBMS layer focuses on data cleaning, and reporting calculation moves from Apache Hive to StarRocks, which becomes the endpoint for data service.
- StarRocks keeps a unified data platform: one data input, multiple application interfaces.
- The StarRocks MPP architecture handles distributed aggregation and join queries better.
- StarRocks works well with Tableau for BI analysis.
Upgraded architecture
Data model design
StarRocks provides three data models: Duplicate Key, Aggregate Key, and Primary Key. For the SEC business, the Duplicate Key model is currently the main one. We will consider the others if new scenarios come up.
Partitioning and bucketing
Partitioning and bucketing greatly improve the performance of detailed queries on historical inventory and turnover data. A typical query is inventory turnover over a period of time. Partitioning by delivery time filters out unneeded data and narrows the date range the query covers. Partitioning, bucketing, prefix indexes, and other features make these queries much faster, and they also prepare the system for business growth and higher concurrency.
Materialized views
StarRocks materialized views can be built in real time and on demand, and are selected automatically at query time. We build materialized views on SN inventory, product type, depot, and distributor, which greatly accelerate queries.
Data ingestion
We ingest data into StarRocks in two ways:
- Broker Load loads offline Apache Hive tables into StarRocks.
- DataX loads data from SQL Server and MySQL into StarRocks.
The results
StarRocks replaced a dozen nodes in a Presto cluster with 1/4 of the resources and is running stably online.
Faster BI reports
We originally built BI reporting on SQL Server and MySQL. As the business grew, some reports became more complex, and Tableau reports took an unacceptably long time to load when users filtered on multiple dimensions.
With StarRocks, we ingest SQL Server data through DataX using the StarRocks-Writer plug-in, which imports data efficiently. MySQL data can be ingested with INSERT INTO ... SELECT or queried directly as external tables. Tableau charts now appear in seconds.
Flexible modeling and less repeated development
StarRocks supports both flat tables and star schemas. Flat tables and materialized views provide performance and concurrency, while the star schema lets analytical workloads keep the same model as the transactional systems and run join queries directly, which improves data consistency and development efficiency.
Many tables stay in MySQL and are queried as external tables, so they don't need an ingestion process. Distributed joins and materialized views let us build a unified analysis layer that serves different BI reports, which keeps metrics consistent and reduces repeated development.
Lower maintenance cost
The data center is a core online service with strict requirements for high availability and flexible scaling. StarRocks keeps multiple replicas of data, and its architecture has only two roles, FE and BE, so the cluster stays available when a node fails. StarRocks can also scale out online with large data volumes, with no downtime and no impact on online business.
What's next
Liansheng Zhida began evaluating StarRocks in April 2021. With StarRocks, we have a unified data service that greatly simplifies offline data processing while meeting our query latency requirements. We plan to use it to improve data services and query capabilities in more business scenarios.