Materialized Views Over External Tables for Cloud Database Scalability
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Traditional database management systems face limitations in scalability due to bottlenecks in shared-disk architectures and require significant resources for infrastructure and maintenance, especially when dealing with large datasets and external data access.
Innovation Solution
A cloud-based database platform that allows clients to manage and query external data without ingesting it, using external tables and materialized views to improve performance and reduce computational load, by generating metadata and refreshing views in response to data changes.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional database systems use shared-disk architecture to store large datasets, then data accessibility is provided, but scalability is limited due to bottlenecks in shared-disk access
Solution Approach 1:
The patent divides the database into internal tables stored in the database system and external tables stored outside the database system. This segmentation allows the system to scale by adding external storage capacity without being constrained by shared-disk bottlenecks, while maintaining fast access to frequently queried data in internal tables.
Solution Approach 2:
The patent introduces external tables as an intermediary storage layer between the database system and the shared-disk architecture. External tables act as a mediator that can be accessed efficiently without relying on shared-disk performance, thereby resolving the scalability bottleneck while maintaining data accessibility.
2Productivity
If traditional database systems ingest and store all external data internally, then data can be queried efficiently, but infrastructure resources and maintenance costs increase significantly
Solution Approach 1:
The patent extracts data that is not frequently modified or queried from the internal database storage and places it in external tables. This extraction reduces the infrastructure resources required for storing and managing all data internally, while query performance is maintained through efficient external table access mechanisms and selective materialization of frequently accessed data.
Solution Approach 2:
Instead of ingesting and storing all external data internally (excessive action), the system selectively stores only the necessary data in internal tables and maintains the rest in external tables (partial action). This partial ingestion approach reduces infrastructure requirements while maintaining adequate query performance for most operations.
3Speed
If materialized views are created to improve query performance on external tables, then query speed increases, but maintenance complexity and computational load increase
Solution Approach 1:
The patent creates materialized views in advance for frequently queried external tables to improve query response time. These pre-computed views are updated periodically or on change, providing fast query performance without requiring complex real-time processing during query execution. The preliminary materialization of data reduces the computational load during actual queries.
Data Source
AI summary
Systems, methods, and devices for generating a materialized view over an external table. 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 generating, by the database platform, a materialized view over the external table. The method includes receiving a notification that a modification has been made to the external table, the modification comprising one or more of an addition of a file, a deletion of a file, or an update to a file in a source location for the external table. The method includes, in response to the external table being modified, refreshing the materialized view such that the materialized view comprises an accurate representation of the external table.