Persistent Query Service Virtual Table Data Propagation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current real-time database systems face challenges in providing timely and accurate reports due to the inefficiencies of the ETL process, which can take hours or days for large datasets, and the use of SQL triggers leads to performance issues and excessive file storage, making it difficult to maintain real-time data updates and aggregations.
Innovation Solution
A real-time database system that supports persistent queries through a client software application with a persistent query service, creating virtual tables and listeners to manage data changes, allowing for incremental updates and efficient data propagation without compromising performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If ETL process is used to consolidate and transform data from multiple sources, then data can be consolidated into a uniform arrangement, but the process takes hours or days for large datasets, making real-time reporting impossible
Solution Approach 1:
The system pre-consolidates data from multiple sources into a unified virtual table structure before queries are executed. Virtual tables maintain pre-transformed and normalized data representations, so when a query is made, the data is already in the required uniform arrangement, eliminating the need for time-consuming ETL processes during report generation.
Solution Approach 2:
The patent creates virtual tables that are logical copies or representations of underlying data sources rather than physical copies. These virtual tables provide a unified view of data from multiple sources without requiring actual data movement or transformation during query execution, enabling real-time access to consolidated information.
2Reliability
If SQL triggers are used to maintain real-time data updates, then data synchronization can be achieved, but performance deteriorates and excessive file storage is required
Solution Approach 1:
The patent introduces virtual tables as intermediary structures between data sources and queries. These virtual tables act as mediators that maintain data synchronization through their query result set caching mechanism, eliminating the need for SQL triggers while achieving reliable data consistency across multiple sources without performance penalties.
Solution Approach 2:
The system extracts the real-time update functionality from traditional SQL trigger mechanisms and implements it through query result set caching in virtual tables. This extraction removes the harmful performance impact of triggers while preserving the essential data synchronization capability.
3Adaptability or versatility
If comprehensive data consolidation infrastructure is implemented, then data can be unified across sources, but the infrastructure becomes extensive and complex
Solution Approach 1:
The virtual table structure provides a universal mechanism that handles data consolidation, transformation, and query execution simultaneously. A single virtual table can represent data from multiple sources with different schemas, providing multi-functional capabilities without requiring separate infrastructure components for each function.
Solution Approach 2:
The patent segments the data consolidation function into individual virtual tables, each representing a specific logical view of data from one or more sources. This segmentation allows the system to handle complex data integration by composing simpler virtual table units rather than requiring a monolithic complex infrastructure.
Data Source
AI summary
A database system supporting persistent queries, using an enhanced persistent query service and various data sources. On receiving a request to create a persistent query from a client software application, the persistent query service: creates a query virtual table; parses the persistent query; creates a plurality of intermediate virtual tables; establishes listeners for the query virtual table; creates a plurality of data source virtual tables; causes the plurality of data source virtual tables to retrieve initial data from data sources; and propagates data via intermediate virtual tables to the persistent query virtual table.