NoSQL Data Extraction via Dynamic JSON Schema Mapping
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Column-oriented NoSQL databases, such as HBase, store data in a format that is not easily usable for analysis, requiring costly and time-consuming operations to convert it into a more traditional format compatible with SQL databases or data analysis tools like Spark DataFrames, due to their unstructured nature and varying column structure across rows.
Innovation Solution
A method and system for extracting data from a column-oriented NoSQL database by scanning rows, creating a schema using JSON structure, associating timestamps, and saving the data in a new format suitable for SQL databases or flat files, allowing incremental updates and efficient data extraction without reprocessing entire datasets.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If data is stored in column-oriented NoSQL database format, then flexibility and horizontal scalability are improved, but data usability for analysis deteriorates
Solution Approach 1:
The patent introduces an intermediary extraction system that acts as a mediator between the NoSQL database and analysis tools. This system reads column-oriented data from HBase, dynamically determines schemas by scanning sample rows, and converts the data into SQL-compatible formats or DataFrames, enabling seamless interoperability without requiring changes to either the database or analysis tools.
Solution Approach 2:
The patent extracts only the necessary data from the NoSQL database in a converted format suitable for analysis. By taking out the data transformation process and performing it externally through the extraction system, the patent maintains the flexibility of NoSQL storage while providing usable data formats for analytical purposes.
2Ease of operation
If data is converted from NoSQL format to traditional format, then data usability for analysis is improved, but operational cost and time consumption increase
Solution Approach 1:
The patent performs preliminary actions by scanning a sample of rows to dynamically determine the schema structure before full data extraction. This preliminary schema determination prevents repeated scanning and processing during the actual data conversion, significantly reducing operational costs and time consumption for large datasets.
Solution Approach 2:
The patent uses partial action by scanning only a representative sample of rows (e.g., 100 rows) to determine the schema rather than scanning the entire dataset. This partial scanning approach provides sufficient information for schema creation while minimizing the computational resources required.
3Stability of the object's composition
If schema is defined upfront for data extraction, then data organization is improved, but adaptability to varying column structures deteriorates
Solution Approach 1:
The patent implements dynamic schema determination by scanning sample rows from the NoSQL database to automatically identify column structures. This dynamic approach allows the extraction system to adapt to varying column definitions across different tables and datasets without requiring predefined schemas, maintaining both organization and adaptability.
Solution Approach 2:
The extraction system performs self-service by automatically determining its own schema requirements through sample row scanning. Rather than requiring external configuration or predefined schemas, the system independently identifies the data structure and configures the extraction process accordingly, enabling seamless handling of varying column structures.
4Loss of information
If full data extraction is performed, then complete data availability is improved, but processing time and resource consumption increase
Solution Approach 1:
The patent performs preliminary schema determination by scanning a small sample of rows before full data extraction. This preliminary action establishes the data structure in advance, allowing the subsequent full extraction to proceed efficiently without repeated structural analysis, thus maintaining data completeness while minimizing processing time.
Solution Approach 2:
The extraction process is segmented into distinct phases: schema determination through sample scanning, and full data extraction using the established schema. This segmentation allows the system to invest minimal resources in schema discovery and then efficiently extract complete data using the pre-determined structure, optimizing the balance between completeness and processing time.
Data Source
AI summary
A method and/or system of extracting a table having data in a plurality of rows from a Not Only Structured Query Language (NoSQL) database to a different type of database that includes: scanning all the rows in a desired table in the NoSQL database and producing a list of column families and associated column names; creating a schema for a new table having a table catalog of new column names using a Java Script Object Notation (JSON) structure to extract the columns names from the list of column families; reading and extracting at least a portion of the data from the desired table in the NoSQL database into the new table having the table catalog of new columns names; associating a creation timestamp with the new table; and saving the new table having the table catalog of new column names to the different database.