SQL Query Execution Plan Pushdown for Data Warehouse Storage
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern data warehouses with compute-storage disaggregation face high latency and bandwidth issues between the SQL compute engine and cloud storage, leading to excessive compute consumption and high cloud bills due to parallel fetching from cloud storage, which is inefficient and costly.
Innovation Solution
A method and system for processing SQL queries within data warehouse architecture that involves receiving and optimizing queries, determining an execution plan tree, and distributing fragments of this plan to a decoupled storage layer to reduce data volume and computational load, allowing parallel execution and efficient data processing.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Volume of stationary object
If data is stored in cloud storage services with compute-storage disaggregation, then storage scalability and cost-effectiveness are improved, but access latency increases and bandwidth decreases
Solution Approach 1:
The system performs preliminary actions by pushing down execution operations to the storage layer in advance. The storage layer executes filtering, projection, and aggregation operations directly on stored data before returning results to the compute layer, eliminating the need to transfer unnecessary data over the network and reducing access latency for required data.
Solution Approach 2:
The system introduces an intermediary mechanism (execution plan tree distribution system) that coordinates between the compute layer and storage layer. This intermediary enables the storage layer to execute operations locally and return only necessary results, effectively mediating the speed mismatch between compute and storage operations.
2Productivity
If multiple parallel compute instances are launched to fetch data concurrently from cloud storage, then data throughput is improved, but compute resource consumption and costs increase
Solution Approach 1:
The system extracts computation operations from the compute layer and places them directly at the storage layer. By pushing down execution operations (filtering, projection, aggregation) to where the data resides, the system eliminates the need for multiple parallel compute instances to concurrently fetch and process data, significantly reducing compute resource consumption while maintaining throughput.
Solution Approach 2:
Instead of fetching all data and processing it completely at the compute layer (excessive action), the system performs partial processing at the storage layer and only transfers necessary results to the compute layer. This partial action approach reduces both network transfer volume and compute resource requirements.
3Speed
If data is fetched from cloud storage to local memory for processing, then processing speed is improved, but network data transfer volume increases
Solution Approach 1:
The storage layer performs preliminary filtering and projection operations before data transfer. By executing these operations at the storage layer, the system ensures that only the specific data subsets required by the query are transferred to the compute layer, minimizing network data transfer volume while maintaining processing speed for the necessary data.
Solution Approach 2:
The system applies local quality by executing different operations at different layers based on their capabilities and data location. The storage layer handles filtering, projection, and aggregation operations locally on stored data, while the compute layer focuses on higher-level processing, optimizing the overall processing speed and minimizing network transfer.
Data Source
AI summary
Embodiments of the disclosure describe a system and method for processing Structured Query Language (SQL) query within data warehouse architecture. The method includes receiving, by nodes associated with an engine layer, a SQL query from a client device, the engine layer indicates a component of the data warehouse architecture. Further, the method includes receiving a topology from a storage layer in response to receiving the SQL query, the topology indicates an arrangement of the stored data among partitions associated with the storage layer. Further, the method includes determining an execution plan tree, the execution plan tree indicates operations to be executed by the engine layer and the storage layer corresponding to the SQL query. The method includes distributing, a fragment of the execution plan tree to the storage layer based on the topology, for processing the operations.