Design and Implementation of Zhaozhuan One-Service Unified Data Query Platform

1. Evolution of Zhaozhuan Data Services

In the first stage, rapid business growth required quick statistical data delivery, mainly via Excel files or direct database queries to support decision‑making.

The second stage introduced theme‑based reports and multi‑dimensional analysis, but each theme built its own query service, leading to a siloed development model.

The third stage unified these services into the One‑Service platform, providing a single, stable, secure, and controllable query entry point for all themes, supporting various data stores.

2. One‑Service Query Service Construction

2.1 Background

Different databases and OLAP engines required separate query services, causing duplicated development. One‑Service aims to centralize query logic across storage engines.

2.2 System Goals

Provide a unified, stable, convenient, secure, and extensible query gateway supporting common OLTP and OLAP engines such as MySQL, Kylin, Druid, ClickHouse, and StarRocks, while ensuring controllable and safe access for BI, data services, and application development.

2.3 Overall Architecture

The platform configures data sources and datasets via metadata, integrates a permission‑management center, and includes modules for access control, query history, and monitoring/alerting.

Base Data Platform: Generates datasets from metadata of tables and metrics.

Management Console: Handles data source, dataset, and permission management; abstracts datasets using SQL or sub‑queries and performs field conversion and filtering.

Permission Management Hub: Controls dataset‑level access.

Query Service: Provides query APIs, backend management, storage‑engine adapters, and auxiliary functions.

2.4 Query Engine Design

Abstract classes define common parameters (Map‑type source config, dataset config) and interface methods. Concrete engine adapters inherit these classes, enabling easy extension and optimization.

getSchemas – retrieve schema information

getTables – retrieve table information

getDatasetDetail – fetch dataset configuration

queryDimVals – query dimension values

queryAggData – query aggregated data

viewAggDataQuery – generate SQL or execution plan

getAggregateDataDownload – synchronous data download

getDataDownloadAsync – asynchronous data download

Query parameters are assembled as JSON (shown below) and sent to the unified service, which translates them into engine‑specific queries.

{
    "rows": [
        {"columnName": "date", "filterType": "eq", "values": []},
        {"columnName": "channelname", "filterType": "eq", "values": []}
    ],
    "filters": [
        {"columnName": "date", "filterType": "=", "values": ["2023-05-10-00"]}
    ],
    "values": [
        {"column": "active_count", "aggType": "sum"}
    ],
    "pageSize": 10,
    "pageNum": 1
}

Key fields: rows (group‑by dimensions), filters (filter conditions), values (metrics with aggregation types such as count, sum, avg, max, min, distinct, raw), orders (sorting), pageSize , and pageNum .

Front‑end or back‑end developers can generate the cfg from dataset metadata, send it to One‑Service, which selects the appropriate engine adapter, executes the query, and returns results.

2.5 Data Security

Security is enforced at the dataset level via two mechanisms: (1) integration with an authentication system that maps user roles to dataset permissions, and (2) token‑based access for internal services without user login. Monitoring and alert modules track abnormal queries, authentication failures, and slow queries.

2.6 Intelligent Query

To avoid sending lightweight queries to heavy OLAP engines, the platform selects the most suitable engine based on dataset priority and query conditions, using a hierarchy such as MySQL (high priority) → Kylin (medium) → ClickHouse (low).

Wide tables in Hive are split into frequently used dimension datasets.

Data subsets are stored in engines like ClickHouse for final hit queries.

Priority rules determine which engine handles a given query.

An engine selector matches the dataset and query conditions to the optimal engine.

2.7 Platform Construction

After core features, a self‑service analysis UI was built, allowing users to view authorized datasets, configure dimensions, filters, and metrics, and see the generated cfg and final SQL, facilitating rapid adoption by front‑end and back‑end developers.

3. Future Plans

Fine‑grained permission management down to metric‑field level and enhanced anomaly detection.

Support for additional engines such as Redis and Elasticsearch.

Online service isolation to guarantee high QPS stability and performance monitoring.

Improved usability with preset query parameter templates and richer self‑service analysis features.

About the Author

Yecheng Zhang, Senior Data Development Engineer at Zhaozhuan Data Intelligence Department, focusing on data development and platform construction.