Join Query Processing via Clustered Index Pre-grouping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
The existing methods for processing join queries in distributed computing frameworks based on MapReduce are inefficient due to the scattering of key values, leading to high calculation and network overheads, resulting in low join query efficiency.
Innovation Solution
A method that determines frequent table combinations and creates clustered indexes to centrally save records with the same join key, reducing the number of buckets and data transmission during the join query process, thereby improving efficiency.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If distributed hash join technology is used to determine equi-join of columns between two tables, then join query can be performed in distributed environment, but key values of data in data block become scattered requiring large quantity of calculations for grouping into buckets at map stage
Solution Approach 1:
The patent applies preliminary action by pre-grouping data into buckets during the data loading phase before the MapReduce job executes. The grouping operation is performed once during data ingestion, and the bucket identifiers are stored with the data. This eliminates the need to perform hash calculations and grouping operations during the map stage of query execution, significantly reducing processing time while maintaining distributed join capability.
2Adaptability or versatility
If data in multiple buckets exists in each data block, then distributed hash join can handle scattered key values, but network connection overheads and data transmission overheads increase in shuffle process
Solution Approach 1:
The patent performs the grouping action in advance during data loading, assigning bucket identifiers to data rows based on hash calculations performed once at ingestion time. These pre-computed bucket identifiers are stored with the data. During the shuffle phase, data can be routed using these pre-assigned bucket identifiers without requiring additional hash calculations or complex routing logic, reducing network overhead and improving transmission efficiency.
3Productivity
If records with same join key are saved in one data block, then calculation amount of bucketing data at map stage is reduced, but requires creating clustered indexes and determining frequent table combinations
Solution Approach 1:
The patent performs data reorganization and bucket assignment during the data loading phase, before any query execution. By determining frequent table combinations and creating clustered indexes upfront, the system prepares data in an optimized layout that groups related records together. This preliminary organization eliminates the need for complex runtime operations and reduces query processing complexity, as the data is already arranged for efficient join operations.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Embodiments of the present invention disclose a method and apparatus for processing join query, which relate to the field of communications technologies, and can resolve a problem of low join query efficiency. The method in the embodiments of the present invention includes: determining a frequent table combination, where the frequent table combination is a table combination whose emergence frequency in a historical query record is greater than a preset value, and the table combination includes a join key and tables that are joined by using the join key; creating a clustered index according to information about the join key in the frequent table combination; and performing a shuffle operation according to an index column in the clustered index, and saving records that have a same index column value into at least one data block to form a table cluster corresponding to the frequent table combination. The present invention is applied to a join query performed on a table.