External Tables and Materialized Views for Cloud Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional database management systems face scalability issues due to bottlenecks in shared-disk architectures and require significant resources for infrastructure and maintenance, while managing external data can be inefficient and costly.
Innovation Solution
A cloud-based database platform that allows clients to ingest, store, and query external data without direct management, using external tables and materialized views to improve query performance and reduce computational load.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If traditional shared-disk database architecture is used, then data consistency and reliability are maintained, but scalability is limited due to bottlenecks
Solution Approach 1:
The patent segments the database architecture by separating compute nodes from storage nodes. Compute nodes process queries and operations, while storage nodes hold data in distributed fashion across the cluster. This segmentation eliminates the shared-disk bottleneck by allowing independent scaling of compute and storage resources, maintaining data consistency through distributed transaction protocols while enabling horizontal scalability.
Solution Approach 2:
The patent introduces a distributed file system as an intermediary layer between compute nodes and raw storage. This intermediary manages data distribution, replication, and consistency across the distributed storage network, allowing compute nodes to access data without direct coordination between storage nodes, thus improving scalability while maintaining reliability.
2Adaptability or versatility
If external data is queried directly from external sources, then data accessibility is improved, but query performance and computational efficiency deteriorate
Solution Approach 1:
The patent implements materialized views that pre-compute and store query results from external data sources in advance. When external data is accessed, the system retrieves pre-computed results from materialized views rather than executing complex queries in real-time, significantly improving query performance while maintaining broad data accessibility.
Solution Approach 2:
The patent creates copies of external data within the distributed database system through external tables and materialized views. These copies are stored in the database's distributed storage architecture, allowing queries to be executed against local copies rather than repeatedly accessing external sources, thus improving query performance while maintaining data accessibility.
3Ease of manufacture
If cloud-based database platform is used, then infrastructure costs and maintenance requirements are reduced, but control over external data management is limited
Solution Approach 1:
The patent implements a universal external table interface that works with multiple external data sources and formats through a unified API. This allows the cloud-based platform to maintain ease of management while providing flexible control over external data, as users can access diverse external sources through standardized mechanisms without needing to manage underlying infrastructure.
Solution Approach 2:
The patent enables self-service data loading and management capabilities where users can independently configure external data sources, define external tables, and manage their own data pipelines through the cloud platform's interface. This maintains ease of infrastructure management while providing users with direct control over their external data management needs.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
Systems, methods, and devices for querying over an external table are disclosed. A method includes connecting a database platform to an external table such that the database platform has read access for the external table and does not have write access for the external table. The method includes receiving a query comprising a predicate, the query directed at least to data in the external table. The method includes determining, based on metadata, one or more partitions in the external table comprising data satisfying the predicate. The method includes pruning, based on the metadata, all partitions in the external table that do not comprise any data satisfying the predicate. The method includes generating a query plan comprising a plurality of discrete subtasks. The method includes assigning, based on the metadata, the plurality of discrete subtasks to one or more nodes in an execution platform.