Denormalized Virtual Table for Semi-Structured Data Querying
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Querying large volumes of semi-structured data, such as JSON and XML, is inefficient due to the need to traverse complex hierarchies, especially when schema information is unknown, leading to costly operations and suboptimal performance even after normalization or denormalization.
Innovation Solution
A denormalized data arrangement is generated by flattening semi-structured data into a flat relational scheme, allowing queries to extract data directly from columns using predicates and scan or join techniques, with path expressions converted into relational statements for efficient execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Stability of the object's composition
If semi-structured data is stored in hierarchical documents to preserve hierarchical information, then data storage convenience and hierarchical information preservation are improved, but query performance deteriorates due to costly hierarchical traversal operations
Solution Approach 1:
The patent segments the hierarchical semi-structured data into multiple normalized relational tables based on the hierarchy levels and relationships. Each table represents a specific level or aspect of the hierarchy, allowing queries to access only the necessary segments without traversing the entire hierarchical structure, thus improving query performance while preserving hierarchical information through foreign key relationships.
Solution Approach 2:
The patent introduces an intermediary mapping layer that translates hierarchical data access patterns into efficient relational database operations. This intermediary mechanism handles the conversion between hierarchical navigation and relational table joins, eliminating the need for expensive hierarchical traversal while maintaining the logical hierarchical structure through relational integrity constraints.
2Quantity of substance
If semi-structured data is normalized and stored in separate relational tables to avoid repetition, then storage efficiency is improved, but query performance deteriorates due to substantial join operations consuming resources
Solution Approach 1:
The patent performs preliminary action by pre-computing and materializing common join results into denormalized views or summary tables. These pre-computed structures cache the results of frequent join operations, allowing queries to retrieve data from these optimized structures without performing expensive real-time joins, thus improving query performance while maintaining storage efficiency through selective denormalization.
3Device complexity
If data is stored in a single normalized table to simplify structure, then device complexity is reduced, but storage efficiency deteriorates due to repetition of data
Solution Approach 1:
The patent segments the data into multiple normalized tables organized by hierarchical levels and relationship types. This segmentation eliminates data repetition by storing each piece of data in only one table, while maintaining referential integrity through foreign keys. The segmented structure provides both storage efficiency and manageable complexity through clear separation of concerns.
Data Source
Figure 1A
Figure 1B
Figure 2
AI summary
Techniques are provided for denormalizing semi-structured hierarchical data into a virtual table. At least portion of semi-structured data document collection is denormalized for improving the execution of queries that involves a traversal of the semi-structured data hierarchy of the semi-structured data document collection, in an embodiment. Based on the extracted schema of the semi-structured data, a denormalized arrangement is generated in which the hierarchical relationship of the semi-structured data is converted into a set of columns. The denormalized arrangement is materialized in a virtual table by applying the denormalized arrangement onto the semi-structured data. A received query that involves a traversal of the semi-structured data hierarchy is converted to a relational query that can be executed on the virtual table in an embodiment. The execution of the relational query on the virtual table improves the performance in generating the resulting data set.