Hybrid Set-Based ETL for Cloud Database Resource Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing data loading and transformation approaches for cloud databases require powerful middle-tier servers and ample staging space, making them unsuitable for cloud environments with limited resources, and lack frequent commit capabilities, leading to inefficiencies in data processing.
Innovation Solution
A hybrid set-based extract, load, transform approach that sends small batches of data in parallel streams to a cloud database, transforming and loading each subset before processing the next, using the database as a transformation engine and minimizing resource requirements, with parallelized write operations and minimal disk space usage.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of manufacture
If traditional ETL approaches are used with powerful middle-tier servers and ample staging space, then data transformation capability is improved, but device complexity and resource requirements worsen
Solution Approach 1:
The patent extracts the transformation logic from the traditional middle-tier server and relocates it directly into the cloud database itself. The database performs transformation operations on incoming data streams using in-memory processing and temporary tables, eliminating the need for separate powerful middle-tier servers and reducing overall system complexity.
Solution Approach 2:
The cloud database is designed to perform multiple functions: storing data, transforming data, and managing the extraction process. By making the database multi-functional, the system eliminates dedicated transformation servers and reduces infrastructure complexity while maintaining transformation capabilities.
2Quantity of substance
If large amounts of data are loaded using traditional approaches, then data processing volume is improved, but loss of time and processing efficiency worsen
Solution Approach 1:
The patent segments large data loads into smaller batches processed in parallel streams. Each stream handles a subset of data independently, allowing concurrent processing that reduces total processing time. The system can handle large volumes of data by managing multiple smaller streams simultaneously rather than processing everything sequentially.
Solution Approach 2:
The system maintains continuous data processing by keeping multiple parallel streams active and feeding data continuously into the database transformation engine. This continuous operation with multiple concurrent streams maximizes processing throughput and minimizes idle time compared to batch processing approaches.
3Productivity
If frequent commits are implemented in cloud databases, then productivity is improved, but loss of energy and resource consumption worsen
Solution Approach 1:
The patent segments data loading into smaller batches that can be committed frequently. By processing data in smaller streams rather than large batches, the system can commit changes more frequently, improving productivity and reducing the need for large transaction rollback operations that consume more resources.
Solution Approach 2:
The system changes the commit frequency parameter from rare to frequent by implementing continuous small-batch processing. This parameter change allows the database to acknowledge and commit smaller data segments regularly, improving throughput while the in-memory processing reduces the energy cost of frequent commits compared to disk-based operations.
Data Source
AI summary
In accordance with an embodiment, described herein is a system and method for loading and transforming data to a cloud database, using a hybrid set-based extract, load, transform approach. During the loading and transforming of large amounts of data, from one or more data sources, a data synchronization application sends small batches of data, in parallel streams, to the database, which operates as a transformation engine to load the data. Each stream can be associated with a separate staging table in the database. For each staging table, the subset of data therein is transformed and loaded, before the next subset is processed. A transform and merge process operating at the database can then be used to transform and merge the data, from each of the staging tables, to the target table.