DISTRIBUTED BALANCED OPTIMIZATION FOR AN EXTRACT, TRANSFORM, AND LOAD (ETL) JOB

• US 20160314175A1
• Filed: 04/24/2015
• Published: 10/27/2016
• Est. Priority Date: 04/24/2015
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Sheng, Yeh-Heng, Dhayapule, Raghavendra R., Mamou, Jean-Claude
• Granted Patent: US 10,108,683 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 16/84 Mapping; Conversion
• CPC Class Codes: G06F 16/9024 Graphs; Linked lists G06F16/9027 takes precedence
• CPC Class Codes: H04L 47/125 by balancing the load, e.g. traffic engineering
• CPC Class Codes: H04L 67/1001 for accessing one among a plurality of replicated servers

Abstract

Provided are techniques for distributed balanced optimization for an Extract, Transform, and Load (ETL) job across distributed systems of participating ETL servers. A data flow graph with links and stages for an ETL job to be executed by participating ETL servers is received. A distributed job execution plan is generated that breaks the data flow graph into job segments that each include a subset of the links and stages and map to one participating ETL server from the distributed systems to meet an optimization criteria across the distributed systems, wherein the distributed job execution plan utilizes statistics to reduce data movement and redundancies and to balance workloads across the distributed systems. Each of the job segment is distributed to the participating ETL servers based on the mappings for parallel execution.

Claims

1-8. -8. (canceled)

9. A computer program product for distributed balanced optimization of an Extract Transform Load (ETL) job across distributed systems of participating ETL servers, the computer program product comprising a computer readable storage medium having program code embodied therewith, the program code executable by at least one processor to perform:

receiving a data flow graph with links and stages for an ETL job to be executed by participating ETL servers;

generating a distributed job execution plan that breaks the data flow graph into job segments that each include a subset of the links and stages and map to one participating ETL server from the distributed systems to meet an optimization criteria across the distributed systems, wherein the distributed job execution plan utilizes statistics to reduce data movement and redundancies and to balance workloads across the distributed systems; and

distributing each of the job segments to the participating ETL servers based on the mappings for parallel execution.

10. The computer program product of claim 9, wherein the data flow graph includes an ETL network stage that, for output stages, sends data and control signals to ETL network stages on other nodes and, for input stages, receives data and control signals from ETL network stages on other nodes.

11. The computer program product of claim 9, wherein the program code is executable by the at least one processor to perform:

maintaining statistical metadata about data sources, tables, data extraction speed, network speed, data transmission costs, data processing speed, and data processing costs.

12. The computer program product of claim 9, wherein the optimization criteria are selected from a group consisting of minimum time, minimum cost, minimum time within a set maximum cost, and minimum cost within a set maximum time.

13. The computer program product of claim 9, wherein the program code is executable by the at least one processor to perform:

generating multiple distributed job execution plans; and

selecting one of the multiple distributed job execution plans based on the optimization criteria.

14. The computer program product of claim 9, wherein the program code is executable by the at least one processor to perform:

adding a workload of each participating ETL server to a data source registry for use in selecting one of the multiple execution plans that do not exceed a target ETL server capacity; and

adding a new workload option to utilize the server workload information.

15. The computer program product of claim 9, wherein the program code is executable by the at least one processor to perform:

before generating the distributed job execution plan,

adding multiple copies of each source system stage to the data flow graph to account for distributed table partitions for a complete data extraction of a source table; and

adding a partition aggregation stage to the data flow graph to combine data extracted from the distributed table partitions.

16. The computer program product of 9, wherein a Software as a Service (SaaS) is configured to perform the computer program product operations.

17. A computer system for distributed balanced optimization of an Extract Transform Load (ETL) job across distributed systems of participating ETL servers, comprising:

one or more processors, one or more computer-readable memories and one or more computer-readable, tangible storage devices; and

program instructions, stored on at least one of the one or more computer-readable, tangible storage devices for execution by at least one of the one or more processors via at least one of the one or more memories, to perform;

receiving a data flow graph with links and stages for an ETL job to be executed by participating ETL servers;

generating a distributed job execution plan that breaks the data flow graph into job segments that each include a subset of the links and stages and map to one participating ETL server from the distributed systems to meet an optimization criteria across the distributed systems, wherein the distributed job execution plan utilizes statistics to reduce data movement and redundancies and to balance workloads across the distributed systems; and

distributing each of the job segments to the participating ETL servers based on the mappings for parallel execution.

18. The computer system of claim 17, wherein the data flow graph includes an ETL network stage that, for output stages, sends data and control signals to ETL network stages on other nodes and, for input stages, receives data and control signals from ETL network stages on other nodes.

19. The computer system of claim 17, wherein the operations further comprise:

maintaining statistical metadata about data sources, tables, data extraction speed, network speed, data transmission costs, data processing speed, and data processing costs.

20. The computer system of claim 17, wherein a Software as a Service (SaaS) is configured to perform the system operations.

21. The computer system of claim 17, wherein the optimization criteria are selected from a group consisting of minimum time, minimum cost, minimum time within a set maximum cost, and minimum cost within a set maximum time.

22. The computer system of claim 17, wherein the operations further comprise:

generating multiple distributed job execution plans; and

selecting one of the multiple distributed job execution plans based on the optimization criteria.

23. The computer system of claim 17, wherein the operations further comprise:

adding a workload of each participating ETL server to a data source registry for use in selecting one of the multiple execution plans that do not exceed a target ETL server capacity; and

adding a new workload option to utilize the server workload information.

24. The computer system of claim 17, wherein the operations further comprise:

before generating the distributed job execution plan,

adding multiple copies of each source system stage to the data flow graph to account for distributed table partitions for a complete data extraction of a source table; and

adding a partition aggregation stage to the data flow graph to combine data extracted from the distributed table partitions.