Cloud Database Querying External Tables via Metadata Filtering
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional database management systems face challenges such as high costs, limited scalability, and susceptibility to data loss due to their reliance on on-premises infrastructure. Additionally, managing external data not stored within the database platform is inefficient.
Innovation Solution
The system enables a cloud-based database platform to read and query external data stored in external tables, generate metadata, and create materialized views without managing or writing to the external data. This allows clients to use the database platform for operations on external data without ingesting it into the platform.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional database management systems use on-premises infrastructure, then data storage and access are provided, but costs increase and scalability is limited
Solution Approach 1:
The patent introduces an intermediary layer between the database platform and external data sources. This intermediary enables the database to query and analyze external data without requiring the external data to be ingested into the database platform, thereby avoiding infrastructure complexity while maintaining scalability.
Solution Approach 2:
The system segments data management into two distinct parts: data stored within the database platform and data stored externally. This segmentation allows the database to maintain its core functionality while externally storing data that requires access but does not need to be managed by the database infrastructure, thus reducing complexity and enabling scalability.
2Quantity of substance
If external data is stored outside the database platform, then infrastructure costs are reduced, but query efficiency deteriorates
Solution Approach 1:
The system performs preliminary actions by pre-processing and indexing external data before queries are executed. The database platform creates materialized views and caches frequently accessed external data, so that when queries are run, the data is already prepared and readily available, maintaining high query efficiency without ingesting all external data into the platform.
Solution Approach 2:
Instead of copying all external data into the database platform, the system creates virtual copies or views of external data structures. These virtual copies allow the database to query external data as if it were stored locally, maintaining query efficiency while avoiding the storage costs and infrastructure requirements of actually storing the data.
3Adaptability or versatility
If data is stored in external tables, then scalability is improved, but data loss risk increases
Solution Approach 1:
The system implements feedback mechanisms where the database platform continuously monitors external data sources and automatically updates materialized views and caches when external data changes. This feedback loop ensures data freshness and consistency without requiring the external data to be fully ingested into the platform, maintaining scalability while improving reliability through active synchronization.
Solution Approach 2:
The database platform creates redundant copies of external data in the form of materialized views and caches before queries are executed. This beforehand cushioning ensures that even if external data sources become unavailable or corrupted, the database can still query the cached data, thereby reducing data loss risk while maintaining scalability.
Data Source
AI summary
System and method for efficiently querying external tables are described herein. In an embodiment, a database platform receives a query that is directed at least in part to external data in an external table stored on a data storage platform that is external to the database platform. The external table includes a plurality of partitions. The database platform identifies, from external-table metadata, a subset of the plurality of partitions of the external table as including data that potentially satisfies the query. The external-table metadata is stored by the database platform. The database platform identifies data that satisfies the query by scanning the identified subset of the partitions, and responds to the query at least in part with the identified data that satisfies the query.