Streaming Query Pre-Aggregation for Faster Rollup Analytics
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing systems face inefficiencies in executing analytical queries on streaming data due to high computational intensity and storage requirements, leading to slow query execution and high costs.
Innovation Solution
A database system that generates a pre-aggregation table based on incoming data and executes ingest and completion queries to efficiently process rollup queries on a periodic basis, using a combination of ingest and completion computations to reduce redundant processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If analytical queries process all received data to ensure accurate results, then measurement precision is improved, but loss of time increases due to repeated computations
Solution Approach 1:
The system performs preliminary aggregation computations during data ingestion and stores intermediate results in a pre-aggregation table. When a rollup query is executed, the system retrieves and combines these pre-computed results rather than重新processing all raw data, thus maintaining accuracy while dramatically reducing execution time
Solution Approach 2:
The query processing is divided into two segments: (1) pre-aggregation of data during ingestion into grouped intermediate results, and (2) final query execution by combining pre-aggregated results. This segmentation allows computational work to be distributed over time and avoids repeated full-data processing
2Productivity
If the system stores very large amount of data to enable efficient query execution, then productivity is improved, but device complexity increases due to storage system requirements
Solution Approach 1:
The storage system is segmented into two distinct components: (1) a raw data storage system that archives all incoming data without performance requirements, and (2) a pre-aggregation table that stores only the grouped intermediate results needed for query execution. This segmentation eliminates the need for a single complex high-performance storage system
Solution Approach 2:
The system extracts only the essential aggregated data needed for query execution from the raw data stream and stores it separately in the pre-aggregation table. This extraction reduces storage complexity by eliminating the need to manage and access entire datasets for every query
3Measurement precision
If the system performs complete aggregation computations on all data to ensure accurate rollup results, then measurement precision is improved, but use of energy increases due to computational intensity
Solution Approach 1:
Aggregation computations are performed preliminarily during data ingestion rather than repeatedly during query execution. The pre-aggregation table stores intermediate results that can be combined to produce final query results, eliminating redundant computational energy consumption while maintaining accuracy
Solution Approach 2:
The system maintains continuous aggregation computations during data ingestion, building up the pre-aggregation table incrementally as data arrives. This continuous useful action ensures that when queries are executed, the computational work has already been performed and stored, avoiding energy-intensive repeated computations
Data Source
AI summary
A database system receives a database language query specifying a grouping clause and one or more aggregate result values. The database system generates a pre-aggregation table based on the query. The database system generates an ingest query configured to generate data for populating the pre-aggregation table and a completion query configured to processes data stored in the pre-aggregation table. The database system periodically receives a set of input records and executes the ingest query to generate a set of pre-aggregation records. The set of pre-aggregation records are combined with the pre-aggregation table. The database system executes the completion query on the data stored in the pre-aggregation table to generate a result set and sends the generated result set to the requestor of the results.