Dictionary Filtering for Columnar Database Query Efficiency
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing technologies for querying columnar databases in cloud-based storage systems are inefficient, particularly when dealing with large columns, as they often require fetching entire columns and performing filtering, which can be costly and time-consuming.
Innovation Solution
The proposed solution involves a dictionary filtering approach, where a metric is calculated based on factors such as dictionary size and estimated query cost. If the metric is below a threshold, dictionary filtering is performed by checking if the value of interest is present in the dictionary before retrieving the column from cloud storage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If entire columns are fetched from cloud storage for query processing, then complete data availability is ensured, but storage costs and query runtime increase significantly
Solution Approach 1:
The patent extracts only the dictionary portion from the columnar data structure, separating it from the full column data. This allows the system to work with a smaller, more manageable subset of the data that contains the essential value-to-identifier mappings, thereby reducing storage costs while maintaining query capability
Solution Approach 2:
The patent performs preliminary filtering by checking the dictionary before fetching full column data. By evaluating filter conditions against the compact dictionary representation first, the system can determine whether full column retrieval is necessary, preventing unnecessary data fetches and reducing storage costs
2Measurement precision
If entire columns are fetched and filtered for query processing, then accurate query results are obtained, but query runtime increases
Solution Approach 1:
The patent segments the query processing into two distinct phases: first filtering against the compact dictionary structure, then selectively retrieving only necessary column data. This segmentation allows the system to quickly eliminate non-matching rows using the small dictionary before incurring the cost of fetching large column data, thereby reducing query runtime while preserving accuracy
Solution Approach 2:
The patent applies partial filtering by operating on the dictionary (a partial representation of the full column data) rather than the complete column. This partial action suffices to determine query results in many cases, avoiding the excessive action of fetching and processing entire columns, thus reducing query runtime without sacrificing accuracy
3Productivity
If cloud-based storage systems throttle expensive queries, then resource management is improved, but query performance deteriorates
Solution Approach 1:
The patent changes the key parameter from column size to dictionary size for the filtering operation. By transforming the filter evaluation to operate on the compact dictionary representation rather than full column data, the system reduces the computational parameters (data volume, memory usage) that trigger throttling, thereby maintaining query performance while improving resource management efficiency
Data Source
AI summary
Disclosed herein is a method, system, or non-transitory computer readable medium for evaluating a query on a columnar dataset comprising one or more dictionaries associated with columns in the dataset. The method includes receiving a request to perform a query comprising at least a operator and a request to return information about a value of interest in a columnar dataset stored on cloud storage. At least one column in the columnar dataset is based on a dictionary. The dictionary maps one or more values for a column to one or more respective identifiers. The method determines whether to perform dictionary filtering for the query by calculating a metric based on one or more factors. Responsive to the metric being below a threshold, which may be predetermined, the method performs the dictionary filtering.