RedNote Delivers Integrated Advertising Analytics with StarRocks

About RedNote

RedNote (Xiaohongshu) runs a lifestyle-sharing platform where users record their lives in short videos and pictures and share them. Since 2017, our business and registered user base have grown rapidly, and with that came the need to improve our data analytics and applications, such as business intelligence, dashboards, user behavior analytics, and policy-based algorithms. Our big data team introduced several OLAP engines, including StarRocks, which helped us build an integrated data analysis platform that simplifies data processing and handles highly concurrent requests.

This article was originally written by the big data team at RedNote.

The challenge

From Redshift to Presto and ClickHouse

Before 2017, our data volume was small and we used AWS Redshift for data storage and computing. Our data warehouse was not fully established, and Redshift alone handled ETL, data modeling, and dashboards. As business complexity and data volume grew, this setup hit a bottleneck:

• Redshift could not auto-scale without hurting online SQL query performance. Scaling could trigger data redistribution and reduce cluster performance and availability.
• Parallel ETL jobs competed for resources, which slowed analysis and caused queries to time out. Under overload, the entire cluster could become unavailable.
• Without separation of storage and compute, storage capacity could not keep up with the growth of business data.

We then built a data warehouse on Apache Hadoop and Apache Hive, ran ETL entirely on Hadoop clusters, and used Presto for OLAP analysis through the Hive metastore. As real-time business and data applications grew, we added ClickHouse for real-time queries.

Next, the big data team decided to redesign the real-time data warehouse and build a unified data service platform for internal and B2B applications. This platform has strict requirements for low latency, high concurrency, and complex queries, and StarRocks was our choice for it.

Advertising analytics before StarRocks

RedNote's core advertising data has two parts. One is advertising exposures, click-through rate (CTR), and actual purchases. The other is attribution data, such as order conversion rate (OCR) and post engagement (followers, comments, and likes). We need both for real-time, multidimensional business analytics.

Previously, Apache Flink wrote large volumes of unstructured data into separate OLTP and OLAP databases, such as MySQL, Redis, HDFS, and ClickHouse. Flink handled the complex processing:

• Joining data streams, such as news feed ads and ads from algorithm-based recommendations.
• Detecting and preventing commercial cheating.
• Writing aggregated data for different scenarios to different databases.

This approach had drawbacks:

• Aggregating complex and disorganized data was difficult.
• ClickHouse is not suited to high-concurrency scenarios, and its storage capacity cannot scale on demand.
• Complex Flink jobs could not ensure high availability.

Requirements

We wanted a well-organized OLAP system for advertising analytics with:

• High throughput
• Multidimensional queries
• TP99 latency under 100 ms
• Materialized views
• More than 10,000 QPS
• Real-time extraction of data from MySQL binary logs, with data encapsulation
• Multi-table joins

The solution

Choosing StarRocks

After evaluating ClickHouse, StarRocks, and TiDB, we chose StarRocks. It now serves as the integrated data analysis platform for ads algorithms, the Live Spend Counter, and ads dashboards.

The new design has three main advantages:

• A clearer data processing architecture. Apache Flink focuses on data cleaning, and the business logic is moved from Flink into StarRocks.
• Unified and consistent advertising statistics through a standardized data ingestion and transformation platform.
• High QPS. The dual-active architecture better supports high-QPS workloads.

Data architecture

• Collection: Apache Flume pulls website event tracking data from server logs and app logs and distributes it to Amazon S3 and Apache Kafka. Canal, an open-source middleware developed by Alibaba Group, captures data changes from MySQL binary logs.
• Offline processing: Apache Hive and Apache Spark run ETL and data modeling in the offline data warehouse. Scheduling tools load offline tables into ClickHouse or StarRocks.
• Online processing: Apache Flink runs a streaming ETL pipeline that enriches, joins, and aggregates data in real time, and writes it to ClickHouse or StarRocks through Flink connectors.
• Analysis: TiDB, Apache HBase, ClickHouse, and StarRocks store offline and real-time data and serve queries. StarRocks and ClickHouse serve OLAP queries for dashboards, ad hoc queries, APIs, and data products such as traffic analysis and user profiling.

Database design

Data models

StarRocks provides three data models: the duplicate key model, the aggregate key model, and the primary key model. Each fits a different advertising analytics scenario.

• Data on advertising exposures and clicks is written to the aggregate key model. It pre-aggregates data across multiple dimensions and generates metrics on demand at query time. Dimensions are attributes with non-quantitative values, such as advertisers, ad types, ideas, ad units, search keywords, regions, and user attributes.
• The primary key model updates real-time ads data extracted from MySQL.
• The duplicate key model processes data from the offline data warehouses.

Partitioning and bucketing

Partitioning and bucketing split ads data into manageable pieces and improve query performance. If recent data is queried most often, a DATE or DATETIME column can be the partitioning column. To look up specific advertisers, advertiser ID can be the leading column of the sort key. Hash bucketing distributes data based on the hash value of one or more columns, such as advertiser ID.

Materialized views

StarRocks materialized views can be built in real time or in batches, added and dropped on demand, and are used transparently by queries. We create materialized views at several granularities, such as advertisers, user attributes, advertising units, and ideas. These views significantly accelerate queries.

Data ingestion

Real-time data reaches StarRocks in two ways:

• Data processed by ETL jobs is transformed in Apache Flink and written to StarRocks with the StarRocks connector for Apache Flink.
• Data in the real-time data warehouse is loaded by StarRocks Routine Load, which schedules one batch every 10 seconds.

For offline data, a data import template based on StarRocks Broker Load is built into RedNote's scheduling platform, so data from the offline data warehouses can be imported into StarRocks through a visual interface.

The results

Query performance

StarRocks is a massively parallel processing (MPP) database that distributes data with range and hash partitioning, which suits highly concurrent queries from advertisers. In stress tests, each StarRocks frontend node handled 2,000 QPS. The entire cluster handled more than 10,000 QPS with TP99 latency under 100 ms.

Availability and scaling

StarRocks provides high availability and scalability, which our ads data center needs. Its architecture of distributed frontends and backends, with data replication, keeps the system reliable when nodes fail. It also supports auto-scaling without downtime, which is vital to our business.

Current status

Since 2021, we have been using five StarRocks clusters for our advertising analytics system. Two of them already provide stable online data analytics services, and the others are in trial operation. StarRocks gives us an integrated data analysis platform and simplifies real-time data processing while meeting our requirements for high concurrency and low latency. We plan to use StarRocks in more business scenarios.