Distributed balanced optimization for an extract, transform, and load (ETL) job

• US 10,120,918 B2
• Filed: 06/07/2016
• Issued: 11/06/2018
• Est. Priority Date: 04/24/2015
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Dhayapule, Raghavendra R., Mamou, Jean-Claude, Sheng, Yeh-Heng
• Primary Examiner(s): Saeed, Usmaan
• Assistant Examiner(s): Weinrich, Brian E.
• Application Number:
  US15/175,926
• Publication Number:
  US 20160314176A1
• Time in Patent Office: 882 Days
• Field of Search: 707602
• 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 using 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. The job segments 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. The job segments are distributed to the participating ETL servers based on the mappings for parallel execution. Also, the distributed job execution plan utilizes statistics to reduce data movement and redundancies and to balance workloads across the distributed systems.

Claims

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

receiving a data flow graph with links and stages for an ETL job to be executed by participating ETL servers wherein the stages include multiple source system stages and one or more partition aggregation stages, wherein the multiple source system stages account for distributed table partitions for a complete data extraction of tables, and wherein the one or more partition aggregation stages combine data extracted from the distributed table partitions;

generating multiple distributed job execution plans for the data flow graph based on data source mappings that indicate which participating ETL servers have access to which of the tables in particular data sources and link mappings that indicate one or more networks associated with the participating ETL servers for each of the links;

selecting a distributed job execution plan from the multiple distributed job execution plans that meets an optimization criteria, wherein the distributed job execution plan breaks the data flow graph into job segments that each include a subset of the links and stages and map to a different participating ETL server from the participating ETL servers, 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.

2. The method of claim 1, 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.

3. The method of claim 1, further comprising:

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

4. The method of claim 1, 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.

5. The method of claim 1, further comprising:

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

adding a new workload option to utilize server workload information.

6. The method of claim 1, wherein a Software as a Service (SaaS) is configured to perform method operations.