Automatic generation of an extract, transform, load (ETL) job
View Patent ↗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.
1. A system for automatically generating one or more Extract, Transform and Load (ETL) jobs based on data comprising:
a computer system including at least one processor configured to:
receive a data set including input data in a source format and output data in a target format;
analyze 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 generate 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;
generate a plurality of ETL jobs each using a different one of the mapping models;
execute each of the ETL jobs using the input data and compare the results of each of the executed ETL jobs to the output data; and
select 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 resource utilization of the computer system.
2. The system of claim 1 , wherein the at least one processor is further configured to:
detect that the schema has been modified to form a modified schema; and
update the plurality of mapping models based on the modified schema.
3. The system 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 system 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 system of claim 1 , wherein the at least one processor is further configured to:
update a mapping model, in response to detecting a violation of that mapping model in additional input data.
6. The system 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.
7. A computer program product for automatically generating one or more Extract, Transform and Load (ETL) jobs based on data comprising:
a computer readable storage device having computer readable program code embodied therewith, the computer readable program code comprising computer readable program code configured to:
receive a data set including input data in a source format and output data in a target format;
analyze 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 generate 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;
generate a plurality of ETL jobs each using a different one of the mapping models;
execute each of the ETL jobs using the input data and compare the results of each of the executed ETL jobs to the output data; and
select 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.
8. The computer program product of claim 7 , wherein the selecting further comprises:
obtaining user input to select the ETL job from among the plurality of ETL jobs.
9. The computer program product of claim 7 , wherein the selecting further comprises:
selecting the ETL job for use with the fewest errors based on the comparing.
10. The computer program product of claim 7 , wherein the computer readable program code is further configured to:
update a mapping model, in response to detecting a violation of that mapping model in additional input data.
11. The computer program product of claim 7 , 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.
12. The computer program product of claim 7 , wherein the computer readable program code is further configured to:
detect that the schema has been modified to form a modified schema; and
update the plurality of mapping models based on the modified schema.