Column based data transfer in extract transform and load (ETL) systems
Executing a plurality of transform stages in an extract, transform and load (ETL) job including, for each of the transform stages, receiving a plurality of input row identifiers (RIDs) corresponding to a first plurality of source database table rows in a source database table. Data is retrieved directly from a subset of the source database table columns in the first plurality of source database table rows based on the input RIDs and transform logic. Partial row data including data from the subset of the source database table columns is generated for each of the first plurality of source database table rows. Transformed data is generated based on the partial row data and to the transform logic. Output RIDs corresponding to a second plurality of rows in the source database table that include a least a subset of the transformed data are output to a downstream stage.
1. A method comprising:
receiving an extract, transform and load (ETL) job, the ETL job configured to access a source database that includes data organized into source database table columns and source database table rows;
predicting a first resource requirement to execute the ETL job using the source database table in a row representation configured to read all of the source database table columns in a source database table row as a unit;
predicting a second resource requirement to execute the ETL job using the source database table in a column representation configured to read at least a subset of the source database table rows in a source database column as a unit;
responsive to the first resource requirement being larger than the second resource requirement, converting the source database table to the column representation and executing the ETL job as a column based data transfer ETL job; and
responsive to the first resource requirement being less than or equal to the second resource requirement, converting the source database table to the row representation and executing the ETL job as a row based data transfer ETL job.
2. The method of claim 1 , wherein the first resource requirement and the second resource requirement include at least one of an input/output (I/O) overhead, a number of I/Os, and a memory usage.
3. A system, comprising:
a computer processor; and
logic executable by the computer processor, the logic configured to implement a method, the method comprising:
receiving an extract, transform and load (ETL) job, the ETL job configured to access a source database that includes data organized into source database table columns and source database table rows;
predicting a first resource requirement to execute the ETL job using the source database table in a row representation configured to read all of the source database table columns in a source database table row as a unit;
predicting a second resource requirement to execute the ETL job using the source database table in a column representation configured to read at least a subset of the source database table rows in a source database column as a unit;
responsive to the first resource requirement being larger than the second resource requirement, converting the source database table to the column representation and executing the ETL job as a column based data transfer ETL job; and
responsive to the first resource requirement being less than or equal to the second resource requirement, converting the source database table to the row representation and executing the ETL job as a row based data transfer ETL job.
4. The system of claim 3 , wherein the first resource requirement and the second resource requirement include at least one of an input/output (I/O) overhead, a number of I/Os, and a memory usage.
5. A computer program product comprising a tangible storage medium readable by a processing circuit and storing instructions for execution by the processing circuit for performing a method comprising:
receiving an extract, transform and load (ETL) job, the ETL job configured to access a source database that includes data organized into source database table columns and source database table rows;
predicting a first resource requirement to execute the ETL job using the source database table in a row representation configured to read all of the source database table columns in a source database table row as a unit;
predicting a second resource requirement to execute the ETL job using the source database table in a column representation configured to read at least a subset of the source database table rows in a source database column as a unit;
responsive to the first resource requirement being larger than the second resource requirement, converting the source database table to the column representation and executing the ETL job as a column based data transfer ETL job; and
responsive to the first resource requirement being less than or equal to the second resource requirement, converting the source database table to the row representation and executing the ETL job as a row based data transfer ETL job.
6. The computer program product of claim 5 , wherein the first resource requirement and the second resource requirement include at least one of an input/output (I/O) overhead, a number of I/Os, and a memory usage.