System and method for automating ETL application
Novel tools for development and operation of ETL (Extract Transform Load) systems for populating databases. An embodiment uses metadata tables to describe relationships between jobs to run for processing data. These relationships can include parent-child job relation, and priority. These tools create a DataStage Service Architecture (DSA) that helps automate and control the ETL process. Other tools allow developers to easily view and update the ETL process.
1. A computer-implemented method comprising:
selecting a plurality of candidate jobs to execute in a current job run, wherein the candidate jobs comprise pending jobs from a previous run that have not been completed due to an error condition and that are to be executed again, or new jobs in which execution has not yet been attempted, and wherein each candidate job performs an extracting, transforming or loading function for populating a database;
storing, in a job status metadata table that stores information about each job's status when the previous run was completed in a STATUS field and that further stores information specifying a priority level for each job in a PRIORITY field, a new session identifier value in a SESSIONID field;
selecting a subset of the candidate jobs to execute in the current run;
selecting a first job of the subset of candidate jobs to execute based on parent job-child job relationship information stored for the first job in a parent job metadata table, and further based on the priority level stored for the first job in the job status metadata table;
determining that the first job does not actually exist within a particular repository or that the first job is not compiled; and
based on determining that the first job does not exist within a particular repository or that the first job is not compiled, updating, by one or more computers, the STATUS field for the job in the job status metadata table to reflect that the job has not been completed due to an error condition, and halting execution of all remaining jobs in the subset.
2. The method of claim 1 , further comprising determining the priority level for the first job from the PRIORITY field of the job status metadata table.
3. The method of claim 2 , wherein selecting the first job comprises determining that the priority level for the first job has a highest priority level among multiple child jobs that have become eligible to execute in the current run based on a parent job successfully completing.
4. The method of claim 1 further comprising determining that two or more parent jobs of the first job have successfully completed.
5. The method of claim 4 , wherein, in response to determining that the two or more parent jobs of the first job have successfully completed, selecting the first job further comprises determining, from the information stored for the first job in the parent job metadata table, that the first job is eligible to be executed.
6. The method of claim 1 , further comprising determining, from one of the metadata tables, a quantity of candidate jobs to select in the subset.
7. The method of claim 6 , further comprising selecting, as the subset, the determined quantity of candidate jobs.
8. A system comprising:
one or more computers; and
a computer-readable medium coupled to the one or more computers having instructions stored thereon which, when executed by the one or more computers, cause the one or more computers to perform operations comprising:
selecting a plurality of candidate jobs to execute in a current job run, wherein the candidate jobs comprise pending jobs from a previous run that have not been completed due to an error condition and that are to be executed again, or new jobs in which execution has not yet been attempted, and wherein each candidate job performs an extracting, transforming or loading function for populating a database,
storing, in a job status metadata table that stores information about each job's status when the previous run was completed in a STATUS field and that further stores information specifying a priority level for each job in a PRIORITY field, a new session identifier value in a SESSIONID field,
selecting a subset of the candidate jobs to execute in the current run,
selecting a first job of the subset to execute based on parent job-child job relationship information stored for the first job in a parent job metadata table, and further based on the priority level stored for the first job in the job status metadata table,
determining that the first job does not actually exist within a particular repository or that the first job is not compiled, and
based on determining that the first job does not exist within a particular repository or that the first job is not compiled, updating the STATUS field for the job in the job status metadata table to reflect that the job has not been completed due to an error condition, and halting execution of all remaining jobs in the subset.
9. The system of claim 8 , wherein the operations further comprise determining the priority level for the first job from the PRIORITY field of the job status metadata table.
10. The system of claim 9 , wherein selecting the first job comprises determining that the priority level for the first job has a highest priority level among multiple child jobs that have become eligible to execute in the current run based on a parent job successfully completing.
11. The system of claim 8 wherein the operations further comprise determining that two or more parent jobs of the first job have successfully completed.
12. The system of claim 11 , wherein, in response to determining that the two or more parent jobs of the first job have successfully completed, selecting the first job further comprises determining, from the information stored for the first job in the parent job metadata table, that the first job is eligible to be executed.
13. The system of claim 8 , wherein the operations further comprise determining, from one of the metadata tables, a quantity of candidate jobs to select in the subset.
14. The system of claim 13 , wherein the operations further comprise selecting, as the subset, the determined quantity of candidate jobs.
15. A computer storage medium encoded with a computer program, the program comprising instructions that when executed by one or more computers cause the one or more computers to perform operations comprising:
selecting a plurality of candidate jobs to execute in a current job run, wherein the candidate jobs comprise pending jobs from a previous run that have not been completed due to an error condition and that are to be executed again, or new jobs in which execution has not yet been attempted, and wherein each candidate job performs an extracting, transforming or loading function for populating a database;
storing, in a job status metadata table that stores information about each job's status when the previous run was completed in a STATUS field and that further stores information specifying a priority level for each job in a PRIORITY field, a new session identifier value in a SESSIONID field;
selecting a subset of the candidate jobs to execute in the current run;
selecting a first job of the subset to execute based on parent job-child job relationship information stored for the first job in a parent job metadata table, and further based on the priority level stored for the first job in the job status metadata table;
determining that the first job does not actually exist within a particular repository or that the first job is not compiled; and
based on determining that the first job does not exist within a particular repository or that the first job is not compiled, updating the STATUS field for the job in the job status metadata table to reflect that the job has not been completed due to an error condition, and halting execution of all remaining jobs in the subset.
16. The computer storage medium of claim 15 , wherein the operations further comprise determining the priority level for the first job from the PRIORITY field of the job status metadata table.
17. The computer storage medium of claim 16 , wherein selecting the first job comprises determining that the priority level for the first job has a highest priority level among multiple child jobs that have become eligible to execute in the current run based on a parent job successfully completing.
18. The computer storage medium of claim 15 wherein the operations further comprise determining that two or more parent jobs of the first job have successfully completed.
19. The computer storage medium of claim 18 , wherein, in response to determining that the two or more parent jobs of the first job have successfully completed, selecting the first job further comprises determining, from the information stored for the first job in the parent job metadata table, that the first job is eligible to be executed.
20. The computer storage medium of claim 15 , wherein the operations further comprise determining, from one of the metadata tables, a quantity of candidate jobs to select in the subset.