Cloud Data Warehouse Table Editing via Modification Tables
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Manipulating and efficiently composing database queries in cloud-based data warehouses is beyond the abilities of most users, requiring complex query construction that is often difficult to manage.
Innovation Solution
A table manager system that receives instructions through a client computing system to edit database tables on a cloud-based data warehouse by generating database statements to apply edits to a modifications table associated with the base table, separating edits from the base table itself.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If users directly manipulate database tables on cloud-based data warehouses, then data access and editing capabilities are improved, but system security and data integrity are compromised
Solution Approach 1:
The patent introduces an intermediary computing system that acts as a mediator between users and the cloud-based data warehouse. This intermediary receives table edit instructions from users, generates appropriate database statements, and applies them to modified base tables through modifications tables. This intermediary layer enables users to edit data without direct access to base tables, thus maintaining security and data integrity while providing editing capabilities.
2Productivity
If complex database queries are allowed for data manipulation, then data processing capabilities are improved, but user accessibility and ease of use deteriorate
Solution Approach 1:
The patent implements a self-service mechanism where the intermediary computing system automatically generates complex database statements and queries based on simple user instructions. Users provide high-level edit instructions without needing to understand complex SQL syntax or query construction, and the system automatically translates these into the necessary database operations, enabling both high productivity and ease of use.
Solution Approach 2:
The intermediary computing system serves as a translator between simple user instructions and complex database operations. It receives user-friendly edit requests and automatically generates the appropriate complex database statements, queries, and SQL commands needed for data manipulation, thus bridging the gap between user capability and system power.
3Productivity
If edits are applied directly to base tables, then editing efficiency is improved, but system resilience and backup capabilities are compromised
Solution Approach 1:
The patent segments the data storage structure into base tables and modifications tables. Base tables contain the original data and remain immutable, while modifications tables store all edits and changes. This segmentation allows efficient editing through modifications tables while preserving the integrity and resilience of base tables, enabling system backup and recovery capabilities.
Solution Approach 2:
The system performs preliminary actions by creating modifications tables in advance that are designed to capture all future edits. This preliminary structure allows edits to be applied efficiently to modifications tables without affecting base tables, while maintaining system resilience through the preserved original data structure.
Data Source
AI summary
Editing and updating database tables on a cloud-based data warehouse including receiving, by a table manager via a client computing system, an instruction to edit a modified base table on the cloud-based data warehouse, wherein the instruction comprises a table edit targeting the modified base table; locating, by the table manager, a modifications table on the cloud-based data warehouse associated with the modified base table, wherein the modified base table comprises a base table and the modifications table; and servicing the instruction to edit the modified base table by generating a database statement targeting the cloud-based data warehouse to apply the table edit to the modifications table associated with the modified base table.