Query Rewriting With Non-Partitioning Field-to-File Mappings
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional data lakes face performance degradation when queries include filters that do not align with partitions or the columnar structure of Parquet files, particularly when dealing with high cardinality fields, leading to exhaustive scans of large data volumes.
Innovation Solution
Implement a mechanism to map non-partitioning fields in data lakes, allowing queries to be rewritten to target only relevant Parquet files containing specified filter values, eliminating the need for comprehensive scans.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If queries include filters on non-partitioning fields, then query flexibility and versatility are improved, but query processing performance deteriorates due to exhaustive scans
Solution Approach 1:
The system performs preliminary actions by generating and caching a mapping between non-partitioning field values and containing Parquet files during data ingestion or preprocessing. This mapping is stored in advance, enabling the query system to quickly identify relevant files without performing exhaustive scans when filters on non-partitioning fields are applied, thus resolving the performance degradation issue while maintaining query flexibility
Solution Approach 2:
The patent introduces an intermediary mapping structure that bridges non-partitioning fields and the physical Parquet file structure. This mapping acts as a mediator that translates filter conditions on non-partitioning fields into targeted file selections, enabling efficient query processing without requiring the data to be physically partitioned by those fields, thus maintaining both query versatility and performance
2Speed
If data is organized into partitions and Parquet files, then storage efficiency and query speed are improved, but queries on non-partitioning fields require exhaustive scans
Solution Approach 1:
The system performs preliminary actions by pre-computing and caching the mapping between non-partitioning field values and the Parquet files containing them. This mapping is generated in advance during data loading or ETL processes, allowing subsequent queries to quickly locate relevant files through the cached mapping rather than scanning entire partitions, thus maintaining ease of operation for non-partitioning field queries while preserving query speed
Solution Approach 2:
The patent introduces a mapping table as an intermediary layer between the logical data model (with non-partitioning fields) and the physical storage structure (partitions and Parquet files). This intermediary enables straightforward filtering on non-partitioning fields by translating filter conditions into targeted file selections, eliminating the need for complex query operations or exhaustive scans while maintaining simple query syntax
3Reliability
If comprehensive data scans are performed, then query accuracy and completeness are improved, but processing time and resource consumption increase
Solution Approach 1:
The system extracts and caches the mapping information between non-partitioning field values and containing Parquet files separately from the main data storage. This extracted mapping is stored in a readily accessible format, allowing the query system to quickly identify and access only the specific Parquet files relevant to the filter conditions without scanning unrelated data, thus maintaining query accuracy while significantly reducing processing time
Solution Approach 2:
The mapping between non-partitioning fields and Parquet files is generated and cached in advance during data ingestion or preprocessing operations. This preliminary action ensures that when queries with filters on non-partitioning fields are executed, the system can immediately use the pre-computed mapping to identify relevant files, eliminating the need for time-consuming exhaustive scans while ensuring complete and accurate query results
Data Source
AI summary
A query of a data repository is received. The query includes a non-partitioning field in a filter. Based on a mapping that correlates values of the non-partitioning field with data files of the data repository that store those values, a subset of data files corresponding to the filter is identified. The query is rewritten into a rewritten query that includes filters based on the identified subset of data files, where the rewritten query specifies the data files. Results of executing the rewritten query are then transmitted.