Enhancing performance of extract, transform, and load (ETL) jobs

• US 9,542,461 B2
• Filed: 09/21/2012
• Issued: 01/10/2017
• Est. Priority Date: 09/21/2012
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bhide, Manish A., Bonagiri, Krishna K., Mittapalli, Srinivas K., Negi, Sumit
• Primary Examiner(s): Young, Kevin L
• Application Number:
  US13/624,273
• Publication Number:
  US 20140089251A1
• Time in Patent Office: 1,572 Days
• Field of Search: 707/602
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses

Abstract

A computer receives one or more files having configuration information that includes data that defines a plurality of stages of an extract, transform, and load (ETL) job, wherein the plurality of stages comprise a read stage that is preceded by a write stage, and wherein the read stage reads data from a source location, and wherein the data that is read or a modified version of the data that is read is being written by the write stage that writes data to the source location. The computer replaces the read stage with a decompressor stage. The computer replaces the write stage with a compressor stage. The computer executes the decompressor stage and compressor stage on a field-programmable gate array that is programmatically customized with data compression and data decompression functionality to enhance the performance of the ETL job.

Claims

1. A computer program product for accelerating a read stage and a write stage of an extract, transform, load job comprising:

a computer readable hardware storage medium and program instructions stored on the computer readable hardware storage medium, the program instructions comprising;

program instructions to receive a file having configuration information that includes data that defines a plurality of stages of at least one extract, transform, and load (ETL) job;

program instructions to create a configuration file, wherein the configuration file is associated with the file, and wherein the configuration file includes one or more compression parameters, and wherein each compression parameter of the one or more compression parameters is selected from the group consisting of a compressor stage or a decompressor stage;

program instructions to identify a read stage that is preceded by a write stage within the file, wherein the read stage reads data from a source location, and wherein the data that is read or a modified version of the data that is read is being written by the write stage that writes data to the source location, and wherein the read stage and write stage each have a first parameter that represents a name of a stage, and wherein the first parameter is associated with a second parameter that represents a type of stage;

program instructions to add the identified read stage to the configuration file;

program instructions, in response to identifying the read stage that is preceded by the write stage within the file, to replace the read stage, with a decompressor stage that reads data from a hard disk, decompress the data from the hard disk and passes the data that is decompressed to another stage or to a computer program for further processing, wherein the decompressor stage is configured to be executed on a field-programmable gate array (FPGA), and wherein parameters for the read stage are a decompressor stage in the configuration file;

program instructions, in response to identifying the read stage that is preceded by the write stage within the file, to replace the write stage, with a compressor stage that compresses the data, and writes the data to a hard disk, wherein the compressor stage is configured to be executed on the FPGA, and wherein the parameters for the write stage are a compressor stage in the configuration file;

program instructions to receive one or more first indications from a user, wherein each first indication of the one or more first indications includes adding, modifying, or deleting a first stage of the plurality of stages;

program instructions to receive one or more second indications from the user, wherein each second indication of the one or more second indications include one or more parameters of a second stage of the plurality of stages in the configuration file; and

program instructions to execute the decompressor stage and the compressor stage on the FPGA based on the configuration file.

2. The computer program product of claim 1, wherein the data that defines the plurality of stages is in a format that allows for parsing of the file.

3. The computer program product of claim 2, wherein the format is a stage name parameter that defines a name of a stage, followed by a stage type parameter that defines types of operations performed by the stage, followed by a list of filenames and additional stage information needed to execute a stage of the at least one ETL job.

4. The computer program product of claim 1, wherein the type of stage comprises a read stage type that is associated with data read operations, a write stage type that is associated with data write operations, a join stage type that is associated with database join operations, an extract stage type that is associated with database extract operations, or a compute stage type that is associated with data compute operations and data comparison operations.

5. The computer program product of claim 1, wherein identifying the read stage and the write stage is based on parsing the file for the first parameter and the second parameter, and utilizing the second parameter to determine whether the stage is a read stage or write stage.

6. The computer program product of claim 1, wherein the FPGA is programmatically customized with data compression and data decompression functionality.

7. The computer program product of claim 1, wherein the decompressor stage comprises instructions to read the data from the source location, decompress the data, pass the data that is decompressed to one of:

another stage ETL job or to another computer program code for further processing.

8. The computer program product of claim 1, wherein the compressor stage comprises instructions to read the data from the source location, buffer the data, and write the data to the source location.

9. A computer system for acceleration a read stage and a write stage of an extract, transform, and load job comprising:

one or more processors, one or more computer readable memories, one or more computer readable storage media and program instructions stored on the one or more storage media for execution by the one or more processors via the one or more memories, the program instructions comprising;

program instructions to receive a file having configuration information that includes data that defines a plurality of stages of at least one extract, transform, and load (ETL) job;

program instructions to create a configuration file, wherein the configuration file is associated with the file, and wherein the configuration file includes one or more compression parameters, and wherein each compression parameter of the one or more compression parameters is selected from the group consisting of a compressor stage or a decompressor stage;

program instructions to identify a read stage that is preceded by a write stage within the file, wherein the read stage reads data from a source location, and wherein the data that is read or a modified version of the data that is read is being written by the write stage that writes data to the source location, and wherein the read stage and write stage each have a first parameter that represents a name of a stage, and wherein the first parameter is associated with a second parameter that represents a type of stage;

program instructions to add the identified read stage to the configuration file;

program instructions, in response to identifying the read stage that is preceded by the write stage within the file, to replace the read stage, with a decompressor stage that reads data from a hard disk, decompress the data from the hard disk and passes the data that is decompressed to another stage or to a computer program for further processing, wherein the decompressor stage is configured to be executed on a field-programmable gate array (FPGA), and wherein parameters for the read stage are a decompressor stage in the configuration file;

program instructions, in response to identifying the read stage that is preceded by the write stage within the file, to replace the write stage, with a compressor stage that compresses the data, and writes the data to a hard disk, wherein the compressor stage is configured to be executed on the FPGA, and wherein the parameters for the write stage are a compressor stage in the configuration file;

program instructions to receive one or more first indications from a user, wherein each first indication of the one or more first indications includes adding, modifying, or deleting a first stage of the plurality of stages;

program instructions to receive one or more second indications from the user, wherein each second indication of the one or more second indications include one or more parameters of a second stage of the plurality of stages in the configuration file; and

program instructions to execute the decompressor stage and the compressor stage on the FPGA based on the configuration file.

10. The computer system of claim 9, wherein the data that defines the plurality of stages is in a format that allows for parsing of the one or more files.

11. The computer system of claim 10, wherein the format is a stage name parameter that defines a name of a stage, followed by a stage type parameter that defines types of operations performed by the stage, followed by a list of filenames and additional stage information needed to execute a stage of the at least one ETL job.

12. The computer system of claim 9, wherein the type of stage comprises a read stage type that is associated with data read operations, a write stage type that is associated with data write operations, a join stage type that is associated with database join operations, an extract stage type that is associated with database extract operations, or a compute stage type that is associated with data compute operations and data comparison operations.

13. The computer system of claim 9, wherein identifying the read stage and the write stage is based on parsing the one or more files for the first parameter and the second parameter, and utilizing the second parameter to determine whether the stage is a read stage or write stage.

14. The computer system of claim 9, wherein the FPGA is programmatically customized with data compression and data decompression functionality.

15. The computer system of claim 9, wherein the decompressor stage comprises instructions to read the data from the source location, decompress the data, pass the data that is decompressed to one of:

another stage ETL job or to another computer program code for further processing.

16. The computer system of claim 9, wherein the compressor stage comprises instructions to read the data from the source location, buffer the data, and write the data to the source location.