Query Execution System Using Single-Node Partitioned Joins

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Conventional data processing systems, such as Hadoop-based systems, face performance issues when handling structured data due to an unoptimal storage layer and lack of efficient query execution strategies, leading to high I/O operations and network transfers during complex data warehousing queries.

Innovation Solution

The method involves partitioning tables based on determined attributes to perform joins on a single node, utilizing a distributed file system and MapReduce processing tasks to optimize data processing, and employing techniques like hash-partitioning and collocation of related tables to minimize data movement and enhance query execution efficiency.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Productivity

If MapReduce is used to execute complex data warehousing queries, then scalability and processing capability are improved, but query execution time and I/O operations increase significantly

Engineering Contradiction:

Improvequery processing capabilityVSAvoidquery execution time

Core Design Contradiction:

ProductivityVSLoss of time

Solution Approach 1:

The patent segments the query execution into two distinct phases: a relational database phase for processing structured data with joins and aggregations, and a MapReduce phase for processing unstructured data. This segmentation allows each phase to be optimized independently, reducing overall query execution time by avoiding the inefficiencies of executing all queries through MapReduce alone.

Inventive Principle:

Principle #1Segmentation

Solution Approach 2:

The patent introduces an intermediary data exchange mechanism between the relational database layer and MapReduce layer. Structured data is extracted from the relational database, transformed into a format suitable for MapReduce processing, and exchanged between the two systems. This intermediary mechanism enables efficient data sharing while maintaining the performance benefits of both systems.

Inventive Principle:

Principle #24Intermediary (Mediator)

2Quantity of substance

If data is stored in a distributed file system, then storage capacity and accessibility are improved, but data movement and network transfers increase during query execution

Engineering Contradiction:

Improvestorage capacityVSAvoiddata movement overhead

Core Design Contradiction:

Quantity of substanceVSLoss of energy

Solution Approach 1:

The patent applies local quality by maintaining structured data in a relational database with optimized storage structures (indexes, partitioning, materialized views) while using distributed file systems for unstructured data. This allows each data type to be stored in the most appropriate location, reducing unnecessary data movement during queries.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The patent performs preliminary actions by pre-processing and transforming structured data into a format optimized for MapReduce operations before the actual query execution. This includes pre-aggregating data, pre-filtering records, and preparing data in the correct format, which significantly reduces the amount of data that needs to be moved and processed during query execution.

Inventive Principle:

Principle #10Preliminary action

3Ease of manufacture

If conventional Hadoop storage layer is used, then system simplicity and ease of deployment are improved, but query performance and processing efficiency deteriorate

Engineering Contradiction:

Improvesystem deployment simplicityVSAvoidquery processing efficiency

Core Design Contradiction:

Ease of manufactureVSProductivity

Solution Approach 1:

The patent implements universality by creating a hybrid system that combines relational database capabilities with MapReduce functionality. The system can handle both structured and unstructured data, execute complex queries with joins and aggregations, and maintain the simplicity of Hadoop deployment while achieving superior query processing efficiency through the integrated architecture.

Inventive Principle:

Principle #6Universality (Multi-functionality)

Data Source

PatentUS8935232B2Query execution systems and methods

Publication Date: 2015.01.13 YALE UNIVERSITY

AI summary

System, method, and computer program product for processing data are disclosed. The method includes receiving a query for processing of data, wherein the data is stored in a table in a plurality of tables, wherein the table is stored on at least one node within the database system, determining an attribute of the table and another table in the plurality of tables, partitioning one of the table and the another table in the plurality of tables using the determined attribute into a plurality of partitions, and performing a join of at least two partitions of the table and the another table using the determined attribute. The join is performed on a single node in the database system.