Validating code of an extract, transform and load (ETL) tool

• US 9,547,702 B2
• Filed: 11/30/2015
• Issued: 01/17/2017
• Est. Priority Date: 07/15/2014
• Status: Active Grant

• Current Assignee: Maplebear Inc.
• Original Assignee: International Business Machines Corporation
• Inventors: Vilakkumadathil, Rokky
• Primary Examiner(s): TECKLU, ISAAC TUKU
• Application Number:
  US14/954,114
• Publication Number:
  US 20160078113A1
• Time in Patent Office: 414 Days
• Field of Search: None
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3604 Software analysis for verifying properties of programs byte-code verification G06F9/44589
• CPC Class Codes: G06F 11/3612 by runtime analysis performance monitoring G06F11/3466
• CPC Class Codes: G06F 11/3616 using software metrics
• CPC Class Codes: G06F 16/2365 Ensuring data consistency and integrity
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 9/44589 Program code verification, e.g. Java bytecode verification, proof-carrying code high-level semantic checks G06F8/43; testing and debugging software G06F11/36

Abstract

An approach for validating code for an extract, transform and load tool is provided. Naming, coding, and performance standards for the code are received. The code is exported to a job definition file and parsed. Violations of the standards are determined by a mismatch between the parsed code and the standards. A report identifying the violations is generated. Based on a review of the report and a rework of the code to comply with the standards, the reworked code is exported to another job definition file and parsed, and subsequently is determined to not include the violations of the standards. A second report is generated that indicates the reworked code does not include the violations. An approval of the reworked code is received based on the second report. Based on attributes of a job included in the code, a violation of one of the performance standards is determined.

Claims

1. A method of validating code of an extract, transform and load (ETL) tool, the method comprising the steps of:

responsive to a receipt of naming, coding, and performance standards for the code of the ETL tool and an export of the code of the ETL tool to a job definition file, a computer parsing the code of the ETL tool in the job definition file;

the computer determining violations of the naming, coding, and performance standards in part by determining the parsed code of the ETL tool does not match the naming, coding, and performance standards;

the computer generating a report which identifies the violations;

based at least in part on a review of the report and a rework of the code of the ETL tool to comply with the naming, coding and performance standards and responsive to an export of the reworked code of the ETL tool to another job definition file, the computer parsing the reworked code of the ETL tool in the other job definition file, determining that the parsed reworked code of the ETL tool does not include the violations of the naming, coding and performance standards, and generating a second report that indicates that the reworked code of the ETL tool does not include the violations;

the computer receiving maximum numbers of aggregator stages of a job included in the code of the ETL tool, transformer stages of the job, occurrences of repartitioning of data sets in the job, sort stages of the job, database read/write operations of the job, and sequential file read/write operations of the job;

the computer receiving a minimum ratio of a number of stages of the job to a number of stages of the job that are annotated;

the computer receiving minimum sizes of a transaction for any insert, update or delete operation of the job and an array employed for any insert, update or delete operation of the job; and

based on aggregator stages of the job exceeding the maximum number of aggregator stages, transformer stages of the job exceeding the maximum number of transformer stages of the job, occurrences of repartitioning of data sets in the job exceeding the maximum number of occurrences of repartitioning of data sets in the job, sort stages of the job exceeding the maximum number of sort stages, database read/write operations of the job exceeding the maximum number of database read/write operations, sequential file read/write operations of the job exceeding the maximum number of sequential file read/write operations, a ratio of the number of stages of the job to the number of stages of the job that are annotated being less than the minimum ratio of the number of stages to the number of stages that are annotated, a size of a transaction for an insert, update or delete operation of the job being less than the minimum size of the transaction, or a size of an array employed for an insert, update or delete operation of the job being less than the minimum size of the array, the computer determining a violation of a performance standard included in the naming, coding, and performance standards.

2. The method of claim 1, further comprising the steps of:

the computer sending the other job definition file of the approved reworked code of the ETL tool to a quality assurance (QA) environment;

in response to a second rework of the code of the ETL tool based on the other job definition, the computer exporting the code of the ETL tool resulting from the second rework to yet another job definition file, parsing the code of the ETL tool resulting from the second rework, determining that the code of the ETL tool resulting from the second rework does not include the violations of the naming, coding and performance standards, generating a third report that indicates that the code of the ETL tool resulting from the second rework does not include the violations, receiving an approval of the code of the ETL tool resulting from the second rework, and sending a job definition file of the approved code of the ETL tool resulting from the second rework to the QA environment;

the computer receiving an indication of a successful end-to-end run of the ETL tool based on the approved code of the ETL tool resulting from the second rework; and

based on the received indication of the successful end-to-end run of the ETL tool, the computer generating release notes for the approved code of the ETL tool resulting from the second rework.

3. The method of claim 1, further comprising the steps of:

the computer determining the code of the ETL tool matches a regular expression specified by a coding standard included in the naming, coding, and performance standards; and

based on the code of the ETL tool matching the regular expression, the computer determining the code of the ETL tool includes hardcoding, wherein the step of generating the report includes generating the report which identifies the hardcoding, wherein the step of generating the second report is based in part on a rework of the code of the ETL tool to eliminate the hardcoding, and wherein the second report indicates the reworked code of the ETL tool does not include the hardcoding.

4. The method of claim 1, further comprising the steps of:

the computer determining a name of an object included in the code of the ETL tool;

the computer determining a naming standard of the object includes a regular expression or a sequence of characters that is not a regular expression, the naming standard included in the naming, coding, and performance standards;

if the naming standard of the object includes a regular expression, the computer determining a name of the object does not match the regular expression, or if the naming standard of the object includes the sequence of characters, the computer determining the name of the object does not begin with the sequence of characters; and

based on the name of the object not matching the regular expression or not beginning with the sequence of characters, the computer determining a violation of the naming standard, which is included in the violations of the naming, coding, and performance standards.

5. The method of claim 1, further comprising the step of the computer determining job names, stage names, stage types, violation types, and descriptions of the violations, wherein the step of generating the report includes generating the report to include the job names, stage names, stage types, violation types and descriptions of the violations.

6. The method of claim 1, further comprising the step of:

providing at least one support service for at least one of creating, integrating, hosting, maintaining, and deploying computer-readable program code in the computer system, the program code being executed by a processor of the computer system to implement the steps of parsing the code of the ETL tool, determining the violations, generating the report, parsing the reworked code of the ETL tool, determining that the parsed reworked code of the ETL tool does not include the violations, generating the second report, receiving the maximum numbers of the aggregator stages of the job, the transformer stages of the job, the occurrences of the repartitioning of the data sets, the sort stages of the job, the database read/write operations, and the sequential file read/write operations, receiving the minimum ratio of the number of stages of the job to the number of stages of the job that are annotated, receiving the minimum sizes of the transaction for any insert, update or delete operation of the job and the array employed for any insert, update or delete operation of the job, and determining the violation of the performance standard.

7. A computer system comprising:

a central processing unit (CPU);

a memory coupled to the CPU; and

a computer-readable storage device coupled to the CPU, the storage device containing instructions that are executed by the CPU via the memory to implement a method of validating code of an extract, transform and load (ETL) tool, the method comprising the steps of;

responsive to a receipt of naming, coding, and performance standards for the code of the ETL tool and an export of the code of the ETL tool to a job definition file, the computer system parsing the code of the ETL tool in the job definition file;

the computer system determining violations of the naming, coding, and performance standards in part by determining the parsed code of the ETL tool does not match the naming, coding, and performance standards;

the computer system generating a report which identifies the violations;

based at least in part on a review of the report and a rework of the code of the ETL tool to comply with the naming, coding and performance standards and responsive to an export of the reworked code of the ETL tool to another job definition file, the computer system parsing the reworked code of the ETL tool in the other job definition file, determining that the parsed reworked code of the ETL tool does not include the violations of the naming, coding and performance standards, and generating a second report that indicates that the reworked code of the ETL tool does not include the violations;

the computer system receiving maximum numbers of aggregator stages of a job included in the code of the ETL tool, transformer stages of the job, occurrences of repartitioning of data sets in the job, sort stages of the job, database read/write operations of the job, and sequential file read/write operations of the job;

the computer system receiving a minimum ratio of a number of stages of the job to a number of stages of the job that are annotated;

the computer system receiving minimum sizes of a transaction for any insert, update or delete operation of the job and an array employed for any insert, update or delete operation of the job; and

based on aggregator stages of the job exceeding the maximum number of aggregator stages, transformer stages of the job exceeding the maximum number of transformer stages of the job, occurrences of repartitioning of data sets in the job exceeding the maximum number of occurrences of repartitioning of data sets in the job, sort stages of the job exceeding the maximum number of sort stages, database read/write operations of the job exceeding the maximum number of database read/write operations, sequential file read/write operations of the job exceeding the maximum number of sequential file read/write operations, a ratio of the number of stages of the job to the number of stages of the job that are annotated being less than the minimum ratio of the number of stages to the number of stages that are annotated, a size of a transaction for an insert, update or delete operation of the job being less than the minimum size of the transaction, or a size of an array employed for an insert, update or delete operation of the job being less than the minimum size of the array, the computer system determining a violation of a performance standard included in the naming, coding, and performance standards.

8. The computer system of claim 7, wherein the method further comprises the steps of:

the computer system sending the other job definition file of the approved reworked code of the ETL tool to a quality assurance (QA) environment;

in response to a second rework of the code of the ETL tool based on the other job definition, the computer system exporting the code of the ETL tool resulting from the second rework to yet another job definition file, parsing the code of the ETL tool resulting from the second rework, determining that the code of the ETL tool resulting from the second rework does not include the violations of the naming, coding and performance standards, generating a third report that indicates that the code of the ETL tool resulting from the second rework does not include the violations, receiving an approval of the code of the ETL tool resulting from the second rework, and sending a job definition file of the approved code of the ETL tool resulting from the second rework to the QA environment;

the computer system receiving an indication of a successful end-to-end run of the ETL tool based on the approved code of the ETL tool resulting from the second rework; and

based on the received indication of the successful end-to-end run of the ETL tool, the computer system generating release notes for the approved code of the ETL tool resulting from the second rework.

9. The computer system of claim 7, wherein the method further comprises the steps of:

the computer system determining the code of the ETL tool matches a regular expression specified by a coding standard included in the naming, coding, and performance standards; and

based on the code of the ETL tool matching the regular expression, the computer system determining the code of the ETL tool includes hardcoding, wherein the step of generating the report includes generating the report which identifies the hardcoding, wherein the step of generating the second report is based in part on a rework of the code of the ETL tool to eliminate the hardcoding, and wherein the second report indicates the reworked code of the ETL tool does not include the hardcoding.

10. The computer system of claim 7, wherein the method further comprises the steps of:

the computer system determining a name of an object included in the code of the ETL tool;

the computer system determining a naming standard of the object includes a regular expression or a sequence of characters that is not a regular expression, the naming standard included in the naming, coding, and performance standards;

if the naming standard of the object includes a regular expression, the computer system determining a name of the object does not match the regular expression, or if the naming standard of the object includes the sequence of characters, the computer system determining the name of the object does not begin with the sequence of characters; and

based on the name of the object not matching the regular expression or not beginning with the sequence of characters, the computer system determining a violation of the naming standard, which is included in the violations of the naming, coding, and performance standards.

11. The computer system of claim 7, wherein the method further comprises the step of the computer system determining job names, stage names, stage types, violation types, and descriptions of the violations, wherein the step of generating the report includes generating the report to include the job names, stage names, stage types, violation types and descriptions of the violations.

12. A computer program product, comprising:

a computer-readable storage device; and

a computer-readable program code stored in the computer-readable storage device, the computer-readable program code containing instructions that are executed by a central processing unit (CPU) of a computer system to implement a method of validating code of an extract, transform and load (ETL) tool, the method comprising the steps of;

responsive to a receipt of naming, coding, and performance standards for the code of the ETL tool and an export of the code of the ETL tool to a job definition file, the computer system parsing the code of the ETL tool in the job definition file;

the computer system determining violations of the naming, coding, and performance standards in part by determining the parsed code of the ETL tool does not match the naming, coding, and performance standards;

the computer system generating a report which identifies the violations;

based at least in part on a review of the report and a rework of the code of the ETL tool to comply with the naming, coding and performance standards and responsive to an export of the reworked code of the ETL tool to another job definition file, the computer system parsing the reworked code of the ETL tool in the other job definition file, determining that the parsed reworked code of the ETL tool does not include the violations of the naming, coding and performance standards, and generating a second report that indicates that the reworked code of the ETL tool does not include the violations;

the computer system receiving maximum numbers of aggregator stages of a job included in the code of the ETL tool, transformer stages of the job, occurrences of repartitioning of data sets in the job, sort stages of the job, database read/write operations of the job, and sequential file read/write operations of the job;

the computer system receiving a minimum ratio of a number of stages of the job to a number of stages of the job that are annotated;

the computer system receiving minimum sizes of a transaction for any insert, update or delete operation of the job and an array employed for any insert, update or delete operation of the job; and

based on aggregator stages of the job exceeding the maximum number of aggregator stages, transformer stages of the job exceeding the maximum number of transformer stages of the job, occurrences of repartitioning of data sets in the job exceeding the maximum number of occurrences of repartitioning of data sets in the job, sort stages of the job exceeding the maximum number of sort stages, database read/write operations of the job exceeding the maximum number of database read/write operations, sequential file read/write operations of the job exceeding the maximum number of sequential file read/write operations, a ratio of the number of stages of the job to the number of stages of the job that are annotated being less than the minimum ratio of the number of stages to the number of stages that are annotated, a size of a transaction for an insert, update or delete operation of the job being less than the minimum size of the transaction, or a size of an array employed for an insert, update or delete operation of the job being less than the minimum size of the array, the computer system determining a violation of a performance standard included in the naming, coding, and performance standards.

13. The computer program product of claim 12, wherein the method further comprises the steps of:

the computer system sending the other job definition file of the approved reworked code of the ETL tool to a quality assurance (QA) environment;

in response to a second rework of the code of the ETL tool based on the other job definition, the computer system exporting the code of the ETL tool resulting from the second rework to yet another job definition file, parsing the code of the ETL tool resulting from the second rework, determining that the code of the ETL tool resulting from the second rework does not include the violations of the naming, coding and performance standards, generating a third report that indicates that the code of the ETL tool resulting from the second rework does not include the violations, receiving an approval of the code of the ETL tool resulting from the second rework, and sending a job definition file of the approved code of the ETL tool resulting from the second rework to the QA environment;

the computer system receiving an indication of a successful end-to-end run of the ETL tool based on the approved code of the ETL tool resulting from the second rework; and

based on the received indication of the successful end-to-end run of the ETL tool, the computer system generating release notes for the approved code of the ETL tool resulting from the second rework.

14. The computer program product of claim 12, wherein the method further comprises the steps of:

the computer system determining the code of the ETL tool matches a regular expression specified by a coding standard included in the naming, coding, and performance standards; and

based on the code of the ETL tool matching the regular expression, the computer system determining the code of the ETL tool includes hardcoding, wherein the step of generating the report includes generating the report which identifies the hardcoding, wherein the step of generating the second report is based in part on a rework of the code of the ETL tool to eliminate the hardcoding, and wherein the second report indicates the reworked code of the ETL tool does not include the hardcoding.

15. The computer program product of claim 12, wherein the method further comprises the steps of:

the computer system determining a name of an object included in the code of the ETL tool;

the computer system determining a naming standard of the object includes a regular expression or a sequence of characters that is not a regular expression, the naming standard included in the naming, coding, and performance standards;

if the naming standard of the object includes a regular expression, the computer system determining a name of the object does not match the regular expression, or if the naming standard of the object includes the sequence of characters, the computer system determining the name of the object does not begin with the sequence of characters; and

based on the name of the object not matching the regular expression or not beginning with the sequence of characters, the computer system determining a violation of the naming standard, which is included in the violations of the naming, coding, and performance standards.

16. The computer program product of claim 12, wherein the method further comprises the step of the computer system determining job names, stage names, stage types, violation types, and descriptions of the violations, wherein the step of generating the report includes generating the report to include the job names, stage names, stage types, violation types and descriptions of the violations.