Method and Apparatus for Generating Code for an Extract, Transform, and Load (ETL) Data Flow

• US 20080092112A1
• Filed: 10/11/2006
• Published: 04/17/2008
• Est. Priority Date: 10/11/2006
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Jin, Qi, Xu, Lin, Liao, Hui
• Granted Patent: US 8,099,725 B2
• Time in Patent Office: Days
• US Class Current: 717/106
• CPC Class Codes: G06F 16/2433 Query languages
• CPC Class Codes: G06F 16/2448 for particular applications; for extensibility, e.g. user defined types

Abstract

Methods, computer program products, and system for generating code for an extract, transform, and load (ETL) data flow are provided. In one implementation, the method includes receiving an ETL data flow representing a logical transformation and flow of data, placing a staging table at a pre-determined location in the ETL data flow to reduce a total number of staging tables required by the transformation, and generating code for the transformation based on the ETL data flow including the staging table placed at the pre-determined location.

Claims

1. A computer-implemented method for generating code for an extract, transform, and load (ETL) data flow in an ETL system, the method comprising:

receiving an ETL data flow representing a logical transformation and flow of data;

placing a staging table at a pre-determined location in the ETL data flow to reduce a total number of staging tables required by the transformation; and

generating code for the transformation based on the ETL data flow including the staging table placed at the pre-determined location.

2. The method of claim 1, wherein placing a staging table at a pre-determined location in the in the ETL data flow includes placing a staging table in the ETL data flow at one or more of the following locations:

a user-specified location;

an output of a data flow operator that requires two or more outputs;

a data flow location that crosses databases;

a location that serves as a connection of a row-based/set-based subflow;

or

a location corresponding to a persistent point for the ETL data flow that is used for error recovery purposes.

3. The method of claim 1, wherein the staging table placed in the ETL data flow is initially assigned as a declared global temporary table.

4. The method of claim 3, wherein generating code for the transformation further includes removing the staging table from the ETL data flow responsive to the staging table no longer being used in a subsequent transformation within the ETL data flow.

5. The method of claim 1, wherein generating code for the transformation further includes building an index on a column of a staging table that appears in a JOIN condition following the staging table.

6. The method of claim 1, wherein placing a staging table at a pre-determined location in the ETL data flow includes partitioning the staging table.

7. The method of claim 1, further comprising:

reducing a size of the ETL data flow using a data dependency graph; and

wherein generating code for the transformation further includes generating the code for the transformation using the reduced ETL data flow.

8. The method of claim 7, wherein reducing a size of the ETL data flow includes removing a column from the ETL data flow that satisfies a predetermined criteria.

9. The method of claim 8, wherein the predetermined criteria includes removing a column from the ETL data flow that does not have a path leading to a column in a data flow relevant column set, the data flow relevant column set including columns in a target table of the ETL data flow and columns referred in a predicate of an operator in the ETL data flow.

10. The method of claim 9, wherein removing a column from the ETL data flow includes determining whether to remove a column only from a source table or a staging table in the ETL data flow.

11. The method of claim 1, wherein generating code for the transformation includes generating structured query language (SQL) code for the transformation.

12. A computer program product, tangibly stored on a computer-readable medium, for generating code for an extract, transform, and load (ETL) data flow in an ETL system, the computer program product comprising instructions for causing a programmable processor to:

receive an ETL data flow representing a logical transformation and flow of data;

place a staging table at a pre-determined location in the ETL data flow to reduce a total number of staging tables required by the transformation; and

generate code for the transformation based on the ETL data flow including the staging table placed at the pre-determined location.

13. The computer program product of claim 12, wherein the instructions to place a staging table at a pre-determined location in the in the ETL data flow include instructions to place a staging table in the ETL data flow at one or more of the following locations:

a user-specified location;

an output of a data flow operator that requires two or more outputs;

a data flow location that crosses databases;

a location that serves as a connection of a row-based/set-based subflow;

or

a location corresponding to a persistent point for the ETL data flow that is used for error recovery purposes.

14. The computer program product of claim 12, wherein the staging table placed in the ETL data flow is initially assigned as a declared global temporary table.

15. The computer program product of claim 14, wherein the instructions to generate code for the transformation further include instructions to remove the staging table from the ETL data flow responsive to the staging table no longer being used in a subsequent transformation within the ETL data flow.

16. The computer program product of claim 12, wherein the instructions to generate code for the transformation further include instructions to build an index on a column of a staging table that appears in a JOIN condition following the staging table.

17. The computer program product of claim 12, wherein the instructions to place a staging table at a pre-determined location in the ETL data flow include instructions to partition the staging table.

18. The computer program product of claim 12, further comprising instructions to cause a programmable processor to:

reduce a size of the ETL data flow using a data dependency graph; and

wherein the instructions to generate code for the transformation further include instructions to generate the code for the transformation using the reduced ETL data flow.

19. An extract, transform, and load (ETL) system comprising:

a code generation system to receive an ETL data flow representing a logical transformation and flow of data;

a first engine associated with the code generation system to place a staging table at a predetermined location in the ETL data flow to reduce a total number of staging tables required by the transformation; and

a second engine associated with the code generation system to generate code for the transformation based on the ETL data flow including the staging table placed at the pre-determined location.

20. The ETL system of claim 19, wherein the code comprises structure query language (SQL) code.