DBMS Map of Operations for External Data Ingestion
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Database management systems (DBMS) face inefficiencies when processing external data due to the need for additional I/O operations and computational resources to match query schemas with ingest schemas, leading to potential inaccuracies and resource wastage.
Innovation Solution
Generating a map of operations to convert ingested data from an ingest schema to a requested query schema, allowing surplus data to supplement differences between schemas without re-ingesting external data, thus avoiding costly ETL processes for new query schemas.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If apriori standardization of the ingest schema is used, then data loading process is simplified, but the schema lacks information needed for accurate query results and is rigid and inadequate
Solution Approach 1:
The system dynamically adjusts the ingest schema based on the specific query requirements. Instead of using a fixed apriori schema, the schema is adapted at query execution time to include only the necessary fields and transformations, making the system both efficient and flexible.
Solution Approach 2:
The ingest schema parameters are changed based on the query type and requirements. The system modifies field selections, transformation rules, and data format parameters dynamically to match the specific needs of each query, resolving the contradiction between simplification and adaptability.
2Reliability
If query schema changes are handled by re-ingesting external data, then accurate results are ensured, but additional I/O operations and computational resources are wasted
Solution Approach 1:
The system performs preliminary data ingestion once, storing the external data in an intermediate format that can be efficiently queried multiple times. When query schemas change, the pre-ingested data is reused with minimal additional processing, eliminating the need for repeated I/O operations while maintaining result accuracy.
Solution Approach 2:
Instead of re-ingesting the original external data for each query schema change, the system creates and uses copies of the ingested data in memory. These copies can be efficiently transformed and queried without accessing the external data source again, significantly improving productivity while ensuring accuracy through controlled transformations.
3Reliability
If ETL process is performed for each new query schema, then data accuracy is maintained, but computational overhead and resource consumption increase
Solution Approach 1:
The system extracts only the necessary transformations and field mappings needed for each specific query from the full ETL process. Instead of performing complete ETL operations for every query schema change, it isolates and applies only the relevant data transformations, maintaining accuracy while reducing computational overhead.
Solution Approach 2:
The system applies partial ETL operations - performing only the subset of extraction, transformation, and loading steps that are necessary for the current query requirements. This partial action approach maintains data accuracy for the needed fields while avoiding the computational cost of processing all possible data transformations.
Data Source
AI summary
Techniques are described for generating a map of operations to ingest external data. In an embodiment, DBMS receives a query schema that specifies an external data source and client-defined columns to arrange queried data from data fields of the external data source. DBMS generates an ingest schema for the queried data of the external data source such that the ingest schema specifies ingest data fields from the data fields of the external data source to be mapped to the one or more client-defined columns. Additionally, the DBMS identifies surplus data in the external data source, which has not been specified by the ingest schema and is different from the queried data defined by the client-requested query schema. The process not only ingests the ingest data fields from the external data source but also the surplus data from the external data source. In response to receiving a query according to a different query schema, DBMS generates a result set that based on the surplus data of the external data source. The result generation is based on the DBMS generating a map of operations, which when performed, generates a result set for the received query regardless whether the query is based on the different query schema.