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

• US 9,529,873 B2
• Filed: 05/16/2013
• Issued: 12/27/2016
• 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/895,392
• Publication Number:
  US 20140089252A1
• Time in Patent Office: 1,321 Days
• Field of Search: None
• 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 implemented method for accelerating a read stage and a write stage of an extract, transform, and load job comprising:

receiving a file having configuration information that defines a plurality of stages of at least one extract, transform, and load (ETL) job;

creating 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;

identifying 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;

adding the identified read stage to the configuration file;

in response to identify the read stage that is preceded by the write stage within the file, replacing the read stage with a decompressor stage that reads data from a hard disk, decompresses 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;

in response to identifying the read stage that is preceded by the write stage within the file, replacing 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;

receiving 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;

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

executing the decompressor stage and the compressor stage on the FPGA based on the configuration file.

2. The method 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 method 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 method 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 method 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 method of claim 1, wherein the FPGA is programmatically customized with data compression and data decompression functionality.

7. The method 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 method 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.