Distributed balanced optimization for an Extract, Transform, and Load (ETL) job

A 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 distributed network of participating Extract Transform Load (ETL) servers;
• generating data source mappings for the distributed network, wherein the data source mappings indicate which participating ETL servers in the distributed network have access to which tables in data sources;
• obtaining network metrics that indicate, for each pair of participating ETL servers, an average data transmission speed and a unit cost;
• obtaining data source metrics for the tables in the data sources;
• generating a link mappings table with a first column for a link, a second column for a first stage of a plurality of stages, a third column for a second stage of the plurality of stages, and a fourth column for mappings, wherein the link connects the first stage and the second stage in a data flow graph, wherein the mappings identify one or more participating ETL servers that access a data source identified by one of the first stage and the second stage, and wherein the plurality of stages comprise data source stages and computation stages;
• generating a plurality of execution plans using the network metrics, the data source metrics, and the link mappings table, wherein an execution plan of the plurality of execution plans includes job segments, and wherein each of the job segments maps to a participating ETL server of the participating ETL servers;
• selecting an execution plan from the plurality of execution plans according to an optimization criteria;
• and
• executing the selected execution plan.

A computer system, 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 computer-readable memories, to perform operations comprising:
• receiving a distributed network of participating Extract Transform Load (ETL) servers;
• generating data source mappings for the distributed network, wherein the data source mappings indicate which participating ETL servers in the distributed network have access to which tables in data sources;
• obtaining network metrics that indicate, for each pair of participating ETL servers, an average data transmission speed and a unit cost;
• obtaining data source metrics for the tables in the data sources;
• generating a link mappings table with a first column for a link, a second column for a first stage of a plurality of stages, a third column for a second stage of the plurality of stages, and a fourth column for mappings, wherein the link connects the first stage and the second stage in a data flow graph, wherein the mappings identify one or more participating ETL servers that access a data source identified by one of the first stage and the second stage, and wherein the plurality of stages comprise data source stages and computation stages;
• generating a plurality of execution plans using the network metrics, the data source metrics, and the link mappings table, wherein an execution plan of the plurality of execution plans includes job segments, and wherein each of the job segments maps to a participating ETL server of the participating ETL servers;
• selecting an execution plan from the plurality of execution plans according to an optimization criteria;
• and
• executing the selected execution plan.