Object Tables for SQL Analytics on Unstructured Warehouse Data
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Analytics programs configured for structured data are unable to efficiently process unstructured data without manual conversion, which is costly and lacks effective lineage tracking.
Innovation Solution
Implementing an object table system that organizes unstructured data in a tabular format, using metadata columns, enabling analytics programs to process unstructured data as structured data, with features like row access policies and periodic updates.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If analytics programs use SQL for handling data, then structured data processing is efficient, but unstructured data cannot be processed without manual conversion
Solution Approach 1:
The patent introduces an intermediary layer that translates unstructured data queries into SQL-compatible structured queries. This intermediary component enables analytics programs to process unstructured data without requiring manual conversion, effectively bridging the gap between SQL-based analytics tools and unstructured data formats while maintaining the existing SQL processing architecture
Solution Approach 2:
The patent enhances the analytics program to handle multiple data types (both structured and unstructured) through a unified interface. By making the system universal, it can process various data formats directly without requiring separate conversion processes, thereby improving adaptability while maintaining operational simplicity
2Adaptability or versatility
If manual conversion of unstructured data to structured data is performed, then analytics programs can process the data, but implementation costs increase and data lineage is lost
Solution Approach 1:
The intermediary translation layer automatically manages the conversion process between unstructured and structured data formats, eliminating the need for expensive manual conversion processes. This automated intermediary system preserves data lineage by tracking transformations while reducing implementation costs through elimination of manual labor
Solution Approach 2:
The system performs preliminary organization and structuring of unstructured data before analytics processing, creating intermediate representations that maintain data lineage information. This preliminary action enables cost-effective processing by preparing data in advance rather than requiring expensive post-hoc conversion
3Productivity
If unstructured data is processed without object tables, then data repository flexibility is maintained, but query efficiency and data organization deteriorate
Solution Approach 1:
The patent segments unstructured data into organized object tables with specific columns for different data attributes. This segmentation improves query processing efficiency by allowing targeted access to specific data elements while maintaining the flexibility of unstructured data storage through the modular table structure
Data Source
AI summary
A method for unstructured data analytics in data warehouses includes receiving an unstructured data query from a user, the unstructured data query requesting the data processing hardware determine one or more unstructured data files stored at a data repository that match query parameters. The method includes determining, using an object table, a set of unstructured data files stored at the data repository that matches the query parameters. The object table includes a plurality of rows, each row of the plurality of rows associated with a respective unstructured data file stored at the data repository, and a plurality of columns, each column of the plurality of columns comprising metadata associated with the respective unstructured data file of each row of the plurality of rows. The method includes returning, to the user, a structured data table including the determined set of unstructured data files.