Automatic generation of an extract, transform, load (ETL) job

Patent metadata

Field	Value
Publication number	US-9607060-B2
Application number	US-201414298125-A
Country	US
Kind code	B2
Filing date	Jun 6, 2014
Priority date	Oct 3, 2013
Publication date	Mar 28, 2017
Grant date	Mar 28, 2017

Abstract

According to one embodiment of the present invention, a method automatically generates one or more Extract, Transform and Load (ETL) jobs. Input data in a source format and output data in a target format is received. The input data and output data is analyzed to determine properties and relationships thereof. One or more mapping models are automatically generated using the properties and relationships, wherein the mapping models describe the mapping and transformation of the input data to the output data. One or more ETL jobs are generated using the mapping models. Embodiments further include a system and program product apparatus for automatically generating one or more ETL jobs.

First claim

What is claimed is:

1. A computer-implemented method for automatically generating one or more Extract, Transform and Load (ETL) jobs comprising:
receiving a data set including input data in a source format and output data in a target format;
analyzing the data set to generate a schema using the input data and output data and determine properties and relationships between the input data and output data using the generated schema;
automatically generating a plurality of mapping models from the analyzing using the determined properties and relationships between the input data and output data, wherein each of the mapping models describes a different mapping and transformation of the input data to the output data;
generating a plurality of ETL jobs each using a different one of the mapping models;
executing each of the ETL jobs using the input data and comparing the results of each of the executed ETL jobs to the output data; and
selecting the ETL job for use from among the plurality of ETL jobs based on a combination of metrics pertaining to accuracy determined from the comparing and computing resource utilization.

2. The method of claim 1 , further comprising:
detecting that the schema has been modified to form a modified schema; and
updating the plurality of mapping models based on the modified schema.

3. The method of claim 1 , wherein the selecting further comprises:
obtaining user input to select the ETL job from among the plurality of ETL jobs.

4. The method of claim 1 , wherein the selecting further comprises:
selecting the ETL job for use with the fewest amount of errors based on the comparing.

5. The method of claim 1 , further comprising:
updating a mapping model, in response to detecting a violation of that mapping model in additional input data.

6. The method of claim 1 , wherein automatically generating the plurality of mapping models further comprises:
generating a complex mapping from input data to output data using a transformation operation, the transformation operation selected from a group consisting of: join, split, composer, and sort.

Assignees

• IBM

Inventors

• Li Jeff J
• Nusbickel Wendi L

Classifications

• G06F16/254 Primary

  Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses · CPC title

• G06F17/30563 Primary

  Physics · mapped topic