Real‑Time Game Analytics: Hologres Replaces Hive+Presto

Background

Le Elements, founded in 2009, operates popular mobile games such as Happy Aquarium , Happy Match , and Sea Beach Match . With several games reaching daily active users (DAU) of over ten million, massive game‑log data is generated each day.

The company needed a data‑driven approach to improve player experience, reduce churn, and support rapid product iteration. Challenges included huge data volume, complex analysis requirements, and the need for fast, flexible, and scalable analytics.

Why a Real‑Time, Flexible, Scalable Platform Was Needed

Game operations require multi‑dimensional analysis: active users, retention, churn, promotion effectiveness, and payment behavior. Real‑time insights are essential for adjusting game levels, launching events, and responding to player behavior quickly.

Existing Architecture and Its Limitations

The original stack used a self‑built HDP cluster for offline storage, Hive for data tables, and Presto for ad‑hoc queries. Real‑time data flowed from game servers to Kafka, then to Flink, and finally to Hive partitions, providing minute‑level latency.

Problems identified:

Data latency of several minutes, longer during peak traffic.

Presto performance lagged behind specialized OLAP engines.

High operational cost due to aging HDP clusters and complex maintenance.

High development effort for custom real‑time loading modules.

Engine Selection

After evaluating ClickHouse, Presto, and Hologres, the team chose Hologres for its strong real‑time write performance, superior query speed, low operational overhead, and built‑in game‑analytics functions.

Supports 20‑30k RPS writes per core and integrates seamlessly with Flink.

Delivers 5‑10× faster query performance compared to Presto on the same hardware.

Provides resource queues, slow‑query monitoring, and connection management.

Includes native retention and user‑profile functions for game scenarios.

Warehouse Architecture Upgrade

Hologres replaced Hive + Presto as the unified storage layer. Real‑time data from Flink and accelerated offline data are written directly to Hologres, which then serves all analytical queries.

Since the May 2022 launch, query latency for high‑frequency calculations has dropped by several times. Games such as Sea Beach Match and Idol Dream Season have fully migrated to the new architecture.

Hologres instances (128 cores) handle offline bulk loads of ~4 million RPS.

Real‑time reads/writes reach 10‑20k QPS, with data size around 10‑15 TB per instance.

Typical 7‑day retention or 30‑day funnel calculations now finish within 10 seconds, with base‑line operations in milliseconds.

Typical Game‑Behavior Analysis Scenarios

1. Retention Analysis

Retention analysis tracks user engagement over time. Previously, Presto required multiple costly joins on billions of event rows.

With Hologres, a single‑scan range_retention_count function computes 1‑, 3‑, and 7‑day retention efficiently.

WITH init_events AS (SELECT uid, ds AS init_date FROM dwd_events WHERE ds BETWEEN '2021-07-14' AND '2021-08-13' AND event_id='sdk_login' GROUP BY 1,2),
return_events AS (SELECT uid, ds AS retention_date FROM dwd_events WHERE ds BETWEEN '2021-07-14' AND '2021-08-20' AND event_id='app_lifecycle' GROUP BY 1,2),
... SELECT i.init_date, COUNT(CASE WHEN r.retention_date - i.init_date = 1 THEN 1 END) AS retention_1, ... FROM init_events i JOIN (SELECT r.*, m.* FROM return_events r JOIN mertic_events m ON r.uid=m.uid AND r.retention_date=m.ds) r ON i.uid=r.uid GROUP BY i.init_date;

2. Funnel Analysis

Funnel analysis identifies drop‑off points in user conversion flows. The old platform required custom UDFs; Hologres provides a built‑in windowfunnel function and clustering keys for efficient processing.

Impact

Operational Efficiency : Query times for user segmentation, activity analysis, retention, and funnel calculations improved by several folds; retention and funnel performance increased nearly tenfold.

Platform Stability : Hologres handles peak real‑time write pressure, reduces maintenance effort, and enables minute‑level auto‑scaling.

Cost Savings : Approximately 50% reduction in machine costs, saving hundreds of thousands of yuan annually.

Future Outlook

Introduce hot‑cold data tiering in Hologres to move infrequently accessed data to cold storage, further lowering costs.

Adopt Hologres External Schema and data‑lake integration to automate offline data ingestion and reduce manual operations.