Cross-Department Query Rewriting for Resource-Controlled SQL Execution
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Modern Cloud-Based Enterprise Data Warehouses face challenges in managing resource conflicts and data access when multiple Enterprise Applications (EAs) co-exist, particularly in scenarios where complex queries require access to data owned by different departments, leading to inefficiencies and potential security issues.
Innovation Solution
A method is implemented where a cloud-based enterprise data warehouse is segmented into departmental portions, with each department controlling its own secondary compute clusters, allowing queries to be rewritten or executed strategically to minimize resource usage and security concerns by identifying and utilizing the appropriate compute resources controlled by the source and accessed departments.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If data storage and compute resources are partitioned by department, then each department can control and secure its own resources, but a department cannot efficiently access data owned by another department when needed for complex queries
Solution Approach 1:
The patent introduces an intermediary query rewriting mechanism that acts as a mediator between departments. When a query needs data from another department, the system automatically rewrites the query to execute on the data owner's compute cluster, with results returned to the original requester. This intermediary process enables cross-departmental access while maintaining resource control boundaries.
Solution Approach 2:
The system segments the data warehouse into departmental compute clusters, each owning and controlling its own data and resources. This segmentation allows independent departmental control while the query rewriting mechanism coordinates across segments to enable necessary cross-departmental access without compromising security or resource ownership.
2Productivity
If multiple enterprise applications co-exist on a single data warehouse, then resource utilization increases, but resource conflicts and contention between applications increase
Solution Approach 1:
The data warehouse is segmented into multiple departmental compute clusters, each independently managing its own resources. This segmentation eliminates resource conflicts between departments while maintaining high overall utilization, as each cluster can be independently scaled and managed without interfering with others.
Solution Approach 2:
Each departmental compute cluster serves multiple functions: it processes queries from its own department and also serves as a data source for other departments through the query rewriting mechanism. This multi-functionality increases overall system utilization while maintaining clear resource boundaries.
3Ease of operation
If a department's query needs to access another department's data, then the query can be completed, but the source department's resources may be depleted affecting their own operations
Solution Approach 1:
The query rewriting mechanism acts as an intermediary that redirects queries to the appropriate departmental compute clusters. Instead of allowing a department to consume another department's resources directly, the system rewrites the query to execute on the data owner's cluster, ensuring resources are used by the department that owns them while still completing the query successfully.
Data Source
AI summary
A database system analyzes a query to determine the resources required to execute the query by identifying the department that issued the query (the “source department”) and identifying query-recited tables and determining the department by which each of the query-recited tables is controlled (the “accessed departments”). The database system determines that the resources required to execute the query are controlled by more than one department: the source department and one or more accessed departments. For each accessed department, a cost of a first strategy involving a SQL rewrite and a cost of a second strategy not involving a SQL rewrite are determined by preparing a query execution plan, determining the cost of the resources, and considering whether data controlled by the accessed department is not accessible except to the accessed department. The first strategy is chosen for at least one of the accessed departments because the first-strategy cost is lower.