Joining Data Across Parallel Database and Distributed Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current systems face inefficiencies in joining data across parallel databases and distributed processing systems, particularly when dealing with large datasets, as they often require pulling all data from one system to another, which can be prohibitively expensive and inefficient.
Innovation Solution
The method involves applying local predicates and projections to data in both parallel databases and distributed processing systems, determining optimal data transfer thresholds, and using techniques like broadcast join, DB-side join, zig-zag join, and repartition join to minimize data movement and leverage processing power from both environments.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If all data is pulled from one system to another for joining, then complete data access is achieved, but data transfer cost and time increase prohibitively
Solution Approach 1:
The patent segments the data joining process into multiple strategies: broadcast join (sending small table to all nodes), DB-side join (performing join in parallel database), zig-zag join (iterative filtering between systems), and repartition join (dividing large tables by partition keys). Each segment handles different data size scenarios efficiently.
Solution Approach 2:
The patent applies preliminary filtering using local predicates and projections before joining operations. Query optimizers evaluate statistics to determine which filters to apply early, reducing the data volume that needs to be transferred between systems before the actual join occurs.
2Reliability
If all data is pulled from one system to another for joining, then complete data access is achieved, but data transfer cost increases prohibitively
Solution Approach 1:
The patent segments the data joining process into multiple strategies: broadcast join (sending small table to all nodes), DB-side join (performing join in parallel database), zig-zag join (iterative filtering between systems), and repartition join (dividing large tables by partition keys). Each segment handles different data size scenarios efficiently.
Solution Approach 2:
The patent applies preliminary filtering using local predicates and projections before joining operations. Query optimizers evaluate statistics to determine which filters to apply early, reducing the data volume that needs to be transferred between systems before the actual join occurs.
3Productivity
If data is filtered and processed locally in both systems, then data transfer is minimized, but query optimization complexity increases
Solution Approach 1:
The patent implements dynamic query optimization that adapts to data distribution and size characteristics. The system evaluates table sizes, data skew, predicate selectivity, and network conditions to dynamically select the most appropriate join strategy, rather than using a fixed approach.
Solution Approach 2:
The system uses feedback from data statistics and query execution patterns to improve optimization decisions. Query optimizers leverage histogram statistics, data skew information, and past performance data to make informed decisions about filtering strategies and join methods.
Data Source
AI summary
Embodiments relate to joining data across a parallel database and a distributed processing system. Aspects include receiving a query on data stored in parallel database T and data stored in distributed processing system L, applying local query predicates and projection to data T to create T′, and applying local query predicates and projection to L to create L′. Based on determining that a size of L′ is less than a size of T′ and that the size of L′ is less than a first threshold, transmitting L′ to the parallel database and executing a join between T′ and L′. Based on determining that a number of the nodes distributed processing system n multiplied by the size of T′ is less than the size of L′ and that the size of T′ is less than a second threshold; transmitting T′ to the distributed processing system and executing a join between T′ and L′.