CN105701117A - ETL (Extract-Transform-Load) dispatching method and apparatus - Google Patents

Abstract

An embodiment of the present invention provides an ETL scheduling method and device, wherein the method includes: first, determining the first data warehouse corresponding to the task execution rules of each stage, and the first data warehouse is the source in the data warehouse of each stage Data warehouse or destination data warehouse; secondly, establish a task replication table according to the logical relationship between the source data warehouse and the destination data warehouse and the first data warehouse, and establish a task assignment table according to the distributed method adopted by the corresponding server of the second data warehouse, and finally , schedule the tasks of each stage according to the task replication table and the task allocation table. Since there is no need for multiple independent ETL devices in each stage of the system, only one ETL device is needed, and the tasks of each stage can be scheduled by establishing a task copy table and a task allocation table, thereby improving the management efficiency of the ETL device and reducing maintenance the complexity.

Description

ETL scheduling method and device

technical field

Embodiments of the present invention relate to communication technologies, and in particular to an Extract-Transform-Load (ETL) scheduling method and device.

Background technique

With the development of big data technology, there are more and more distributed data storage systems. Big data applications generally need to integrate multiple different data storage systems to build data warehouses for different applications. ETL is used to describe the process of transferring data from the source data warehouse The process of extracting, transforming, and loading into a destination data warehouse. Usually ETL devices or ETL tools are used to be responsible for the scheduling control and resource allocation of system running programs.

Generally, the servers corresponding to the above-mentioned data warehouses generally adopt a distributed deployment method, but the deployment methods are different. Currently, the main deployment methods are: Shated Nothing architecture and Shared Disk architecture. Architecture means that the corresponding nodes (servers) in each data warehouse have independent central processing units (Central Processing Unit, CPU), memory, and disk resources, and data are distributed on different nodes according to rules. The shared disk architecture means that the corresponding nodes of each data warehouse have independent CPU and memory, but the disk space is shared between nodes, and the data is stored uniformly. In the prior art, massively parallel processing (MassivelyParallelProcessing, MPP) includes multiple data warehouses. Since each data warehouse corresponds to a different server deployment method, each stage will correspond to an ETL device to achieve task distribution and scheduling. .

However, in the prior art, the management efficiency of the discrete ETL device is low and the maintenance is complicated.

Contents of the invention

The invention provides an ETL scheduling method and device, thereby improving the management efficiency of the ETL device and reducing maintenance complexity.

In the first aspect, an embodiment of the present invention provides an ETL scheduling method, including: determining the first data warehouse corresponding to the task execution rules of each stage, and the first data warehouse is in the data warehouse of each stage The source data warehouse or the destination data warehouse; according to the logical relationship between the source data warehouse and the destination data warehouse and the first data warehouse, a task replication table is established, and the task replication table includes: the source data warehouse The entry of the entry and the entry of the destination data warehouse; according to the distributed mode adopted by the server corresponding to the second data warehouse, the task allocation table is established, and the second data warehouse is the source data warehouse in the data warehouse of each stage or the destination data warehouse, the task allocation table includes: the distributed mode adopted by each server corresponding to the second data warehouse; according to the task replication table and the task allocation table, the tasks of each stage are performed scheduling.

With reference to the first aspect, in a first possible implementation manner of the first aspect, the task replication table further includes: a first parameter and a second parameter; the first parameter is used to indicate that the first data warehouse is the The source data warehouse of the stage; the second parameter is used to indicate that the first data warehouse is the target data warehouse of the stage.

With reference to the first possible implementation manner of the first aspect, in the second possible implementation manner of the first aspect, according to the logical relationship between the source data warehouse and the destination data warehouse and the first data The warehouse creates a task copy table, specifically including: determining the entry of the source data warehouse and the entry of the destination data warehouse according to the logical relationship between the source data warehouse and the destination data warehouse; according to the first The data warehouse determines the first parameter and the second parameter; establishes the task replication according to the entry of the source data warehouse, the entry of the destination data warehouse, the first parameter and the second parameter surface.

In combination with the first aspect or the first possible implementation manner of the first aspect or the second possible implementation manner of the first aspect, in the third possible implementation manner of the first aspect, it further includes: the distributed manner includes: There are no shared distribution method and shared disk distribution method.

With reference to the third possible implementation manner of the first aspect, in a fourth possible implementation manner of the first aspect, the scheduling of the tasks of each stage according to the task replication table and the task allocation table, Specifically, it includes: scheduling tasks in each stage between the source data warehouse and the target data warehouse in the determined distributed manner in each stage.

In the second aspect, an embodiment of the present invention provides an ETL scheduling device, including: a determination module, configured to determine the first data warehouse corresponding to the task execution rules of each stage, the first data warehouse is the The source data warehouse or the destination data warehouse in the data warehouse of the stage; the establishment module is used to establish a task replication table according to the logical relationship between the source data warehouse and the destination data warehouse and the first data warehouse, the The task duplication table includes: the entry of the source data warehouse and the entry of the destination data warehouse; the establishment module is also used to establish a task distribution table according to the distributed method adopted by the corresponding server of the second data warehouse, and the The second data warehouse is the source data warehouse or the destination data warehouse in the data warehouses of each stage, and the task allocation table includes: the distributed mode adopted by each server corresponding to the second data warehouse; a scheduling module, It is used for scheduling the tasks of each stage according to the task replication table and the task allocation table.

With reference to the second aspect, in a first possible implementation manner of the second aspect, the task replication table further includes: a first parameter and a second parameter; the first parameter is used to indicate that the first data warehouse is the The source data warehouse of the stage; the second parameter is used to indicate that the first data warehouse is the target data warehouse of the stage.

With reference to the first possible implementation manner of the second aspect, in the second possible implementation manner of the second aspect, the establishment module is specifically configured to: according to the logic between the source data warehouse and the destination data warehouse determining the entry of the source data warehouse and the entry of the destination data warehouse; determining the first parameter and the second parameter according to the first data warehouse; according to the entry of the source data warehouse, The entry of the target data warehouse, the first parameter and the second parameter establish the task replication table.

In combination with the second aspect or the first possible implementation manner of the second aspect or the second possible implementation manner of the second aspect, in the third possible implementation manner of the second aspect, it further includes: the distributed manner includes: There are no shared distribution method and shared disk distribution method.

With reference to the third possible implementation manner of the second aspect, in a fourth possible implementation manner of the second aspect, the scheduling module is specifically configured to: the source data warehouse and the destination data warehouse in each stage The warehouses schedule the tasks of each stage according to the determined distributed manner.

An embodiment of the present invention provides an ETL scheduling method and device, wherein the method includes: determining the first data warehouse corresponding to the task execution rules of each stage, and the first data warehouse is the data warehouse of each stage A source data warehouse or a destination data warehouse in the source data warehouse; a task replication table is established according to the logical relationship between the source data warehouse and the destination data warehouse and the first data warehouse, and the task replication table includes: the source data The entry of the warehouse and the entry of the destination data warehouse; establish a task assignment table according to the distributed mode adopted by the corresponding server of the second data warehouse, and the second data warehouse is the data warehouse in each stage For the source data warehouse or the destination data warehouse, the task allocation table includes: the distributed mode adopted by each server corresponding to the second data warehouse; tasks are scheduled. Since there is no need for multiple independent ETL devices in each stage of the system, only one ETL device is needed, and the tasks of each stage can be scheduled by establishing a task copy table and a task allocation table, thereby improving the management efficiency of the ETL device in the system , reducing maintenance complexity.

Description of drawings

Fig. 1 is the flowchart of a kind of ETL scheduling method that an embodiment of the present invention provides;

FIG. 2 is a schematic structural diagram of an MPP system provided by an embodiment of the present invention;

Fig. 3 is a schematic structural diagram of an ETL scheduling device provided by an embodiment of the present invention.

detailed description

In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments It is a part of embodiments of the present invention, but not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by persons of ordinary skill in the art without making creative efforts belong to the protection scope of the present invention.

Fig. 1 is a flow chart of an ETL scheduling method provided by an embodiment of the present invention, the method is applicable to an application scenario including multiple data warehouses, wherein the execution subject of the method is: an ETL scheduling device, and the scheduling device may be an ETL Tool, an ETL scheduling method specifically includes the following processes:

S101: Determine a first data warehouse corresponding to the task execution rules of each stage, where the first data warehouse is a source data warehouse or a destination data warehouse in the data warehouses of each stage.

Specifically, systems such as massively parallel processing (MPP) usually include multiple data warehouses, and each stage of the data flow includes an active data warehouse and a destination data warehouse, and each data warehouse has its own task. Execution rules. The task execution rules here include: execution time, execution mode, etc. The ETL device can determine to select one of the source data warehouse and the destination data warehouse as the first data warehouse. The corresponding task execution rules are carried out. The method of how the ETL device determines the first data warehouse is not limited in this embodiment.

S102: Establish a task replication table according to the logical relationship between the source data warehouse and the target data warehouse and the first data warehouse.

Wherein, the task replication table includes: entries of the source data warehouse and entries of the destination data warehouse. The task replication table also includes: a first parameter and a second parameter; the first parameter is used to indicate that the first data warehouse is the source data warehouse of this stage; the second parameter is used to indicate that the The first data warehouse is the target data warehouse at this stage.

Optionally, the establishing a task replication table according to the logical relationship between the data warehouses in each stage and the first data warehouse specifically includes: according to the logical relationship between the source data warehouse and the target data warehouse Determining the entry of the source data warehouse and the entry of the target data warehouse; determining the first parameter and the second parameter according to the first data warehouse; according to the entry of the source data warehouse, the The task replication table is established by describing the entry of the target data warehouse, the first parameter and the second parameter.

For example, Fig. 2 is a schematic structural diagram of an MPP system provided by an embodiment of the present invention, assuming that the MPP system includes the following data warehouses: data source (DataSource) 201, detailed list library 202, analysis database (AnalysisDatabase) 203 and user characteristics Database 204, they correspond to file server, Hive server, SybaseIQ server and RTANA server respectively, wherein the number of file server, SybaseIQ server and RTANA server is all three, Hive cluster realizes distributed internal scheduling relying on Hadoop cluster, provides A unified entry is established, that is, it can be understood that there is only one Hive server provided to the ETL device, as shown in Figure 2, the logical relationship between the data warehouses in each stage includes: the source data warehouse in the first stage is the data source 201, The detailed list library 202; the source data warehouse of the second stage is the detailed list library 202, and the target data warehouse is the analysis database 203; the source data warehouse of the third stage is the analysis database 203, and the target data warehouse is the user characteristic database 204.

The task copy table includes: source data warehouse table items and destination data warehouse table items. The task replication table also includes: a first parameter and a second parameter; the first parameter is used to indicate that the first data warehouse is the source data warehouse of this stage; the second parameter is used to indicate that the The first data warehouse is the target data warehouse at this stage. Suppose the first parameter is 1 and the second parameter is 2. For example: assuming that the first data warehouse corresponding to the task execution rules of the first stage determined in step S101 is the data source, that is, the task execution rules follow the execution rules of the data source, then set the first row and the first column of the table as the first Parameter 1, also the first parameter 1 at the second row and second column, and the second parameter 2 at the third row and third column. Through the above method, a task copy table can be established.

The task copy table provided by this embodiment is as follows:

S103: Establish a task allocation table according to the distributed mode adopted by the server corresponding to the second data warehouse.

Specifically, the second data warehouse is the source data warehouse or the destination data warehouse in the data warehouses of each stage, and the task allocation table includes: each of the second data warehouses corresponds to the distributed data warehouse adopted by the server. Way. The distributed manner includes: a shared nothing distributed manner and a shared disk distributed manner. The deployment mode between servers in the present invention can also be master-backup mode, etc., and is not limited to this distributed mode. The task allocation table includes the corresponding servers of the second data warehouse in each stage. Assuming that no shared distribution mode is represented by 3, shared The disk distribution mode is represented by 4. The second data warehouse in this embodiment happens to be the destination data warehouse. The first row of the task allocation table is the file server, the SybaseIQ server and the RTANA server from left to right. The distribution methods are: shared disk distribution method, shared disk distribution method and no-shared distribution method.

The task allocation table provided by this embodiment is as follows:

Hive server Sybase IQ Server RTANA server 3 3 4

S104: Scheduling the tasks of each stage according to the task replication table and the task allocation table.

Optionally, the scheduling the tasks of each stage according to the task replication table and the task allocation table specifically includes: between the source data warehouse and the destination data warehouse in each stage Schedule the tasks of each stage according to the determined distributed manner.

Following the above example, assume that the tasks in the three stages are:

Task 1: Download the original detailed bill from the file server, and load the data directly into the detailed bill library of the Hive server.

Task 2: Export the original detailed list from the detailed list library of the Hive server, and load the data to the SybaseIQ server after cleaning and aggregation.

Task 3: Export user attributes from the SybaseIQ server, load them to the RTANA server, and calculate user characteristics on the RTANA server.

It is determined according to the tasks of the three stages, that is, the logical relationship between data warehouses: in the first stage, the source data warehouse is the data source, and the destination data warehouse is the detailed list library; in the second stage, the source data warehouse is the detailed list library, and the target data warehouse is the analysis database; the source data warehouse of the third stage is the analysis database, and the destination data warehouse is the user characteristic database. Finally, a task duplication table is established according to the logical relationship between the data warehouses in each stage and the first data warehouse.

Since the first line of the established task allocation table is the Hive server, SybaseIQ server, and RTANA server corresponding to the data source from left to right, the distribution methods they adopt are: shared disk distribution method, shared disk distribution method and no-shared distribution Way. The specific scheduling steps of the three tasks include:

1. According to the task replication table, three tasks 1 will be copied according to the number of file servers, and then according to the task allocation table, all tasks will be executed on the Hive server.

2. According to the task replication table, a task 2 will be copied according to the Hive server, and then according to the task allocation table, the task 2 will be scheduled by an idle SybaseIQ server.

3. According to the task copy table, three task three will be copied according to the number of RTANA servers, and then according to the task allocation table, each RTANA server will schedule a task three.

This embodiment provides an ETL scheduling method, including: first, determining the first data warehouse corresponding to the task execution rules of each stage, wherein the first data warehouse is the source data warehouse or the destination data warehouse in the data warehouse of each stage data warehouse; secondly, establish a task replication table according to the logical relationship between the source data warehouse and the destination data warehouse and the first data warehouse, establish a task assignment table according to the distributed mode adopted by the corresponding server of the second data warehouse, and finally, according to The task duplication table and the task allocation table schedule the tasks in each stage. Since there is no need for multiple independent ETL devices in each stage of the MPP system, only one ETL device is needed, and the tasks of each stage can be scheduled by establishing a task copy table and a task allocation table, thereby improving the efficiency of the ETL device in the MPP system. Management efficiency and reduced maintenance complexity.

Fig. 3 is a schematic structural diagram of an ETL scheduling device provided by an embodiment of the present invention, wherein the device includes: a determination module 301, configured to determine the first data warehouse corresponding to the task execution rules of each stage, the first A data warehouse is the source data warehouse or the target data warehouse in the data warehouses of each stage; the establishment module 302 is used for according to the logical relationship between the source data warehouse and the target data warehouse and the first The data warehouse establishes a task replication table, the task replication table includes: the entry of the source data warehouse and the entry of the destination data warehouse; the establishment module 302 is also configured to The distributed method used to establish a task distribution table, the task distribution table includes: the second data warehouse is the source data warehouse or the destination data warehouse in the data warehouse of each stage, and each of the second data warehouses Corresponding to the distributed mode adopted by the server; the scheduling module 303 is configured to schedule the tasks of each stage according to the task replication table and the task allocation table.

Further, the task replication table further includes: a first parameter and a second parameter; the first parameter is used to indicate that the first data warehouse is the source data warehouse of this stage; the second parameter is used to Indicates that the first data warehouse is the target data warehouse at this stage.

Optionally, the establishment module 302 is specifically configured to: determine the entries of the source data warehouse and the entries of the destination data warehouse according to the logical relationship between the source data warehouse and the destination data warehouse; Determine the first parameter and the second parameter according to the first data warehouse; according to the entry of the source data warehouse, the entry of the destination data warehouse, the first parameter and the second The two parameters create the task replication table.

Optionally, the distributed manner includes: a shared nothing distributed manner and a shared disk distributed manner.

Optionally, the scheduling module 303 is specifically configured to: schedule tasks in each stage between the source data warehouse and the target data warehouse in the determined distributed manner in each stage.

The ETL scheduling device provided in this embodiment can be used to execute the technical solution of the ETL scheduling method corresponding to FIG. 1 , and its implementation principle and technical effect are similar, and will not be repeated here.

Finally, it should be noted that: the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit them; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that: it can still be Modifications are made to the technical solutions described in the foregoing embodiments, or equivalent replacements are made to some of the technical features; and these modifications or replacements do not make the essence of the corresponding technical solutions deviate from the spirit and scope of the technical solutions of the various embodiments of the present invention.

Claims (10)

1. A method of extracting, converting, and loading ETL dispatching, characterized in that, comprising: determining the first data warehouse corresponding to the task execution rules of each stage, the first data warehouse being the source data warehouse or the destination data warehouse in the data warehouses of each stage; Establish a task replication table according to the logical relationship between the source data warehouse and the destination data warehouse and the first data warehouse, and the task replication table includes: entries of the source data warehouse and the destination data warehouse table entry; A task allocation table is established according to the distributed mode adopted by the corresponding server of the second data warehouse, the second data warehouse is the source data warehouse or the destination data warehouse in the data warehouse of each stage, and the task allocation table includes: each A distributed mode adopted by the server corresponding to the second data warehouse; The tasks of each stage are scheduled according to the task replication table and the task allocation table. 2. The method according to claim 1, wherein the task replication table further comprises: a first parameter and a second parameter; The first parameter is used to indicate that the first data warehouse is the source data warehouse of this stage; The second parameter is used to indicate that the first data warehouse is the target data warehouse of this stage. 3. The method according to claim 2, wherein the establishment of a task copy table according to the logical relationship between the source data warehouse and the destination data warehouse and the first data warehouse specifically includes: determining entries in the source data warehouse and entries in the destination data warehouse according to the logical relationship between the source data warehouse and the destination data warehouse; determining said first parameter and said second parameter based on said first data repository; The task replication table is established according to the entry of the source data warehouse, the entry of the target data warehouse, the first parameter and the second parameter. 4. The method according to any one of claims 1-3, further comprising: The distributed manner includes: a shared nothing distributed manner and a shared disk distributed manner. 5. The method according to claim 4, wherein the scheduling of tasks at each stage according to the task copy table and the task assignment table specifically includes: The tasks of each stage are scheduled according to the determined distributed manner between the source data warehouse and the target data warehouse in each stage. 6. An ETL scheduling device, characterized in that, comprising: A determining module, configured to determine the first data warehouse corresponding to the task execution rules of each stage, the first data warehouse being the source data warehouse or the destination data warehouse in the data warehouses of each stage; An establishment module, configured to establish a task replication table according to the logical relationship between the source data warehouse and the target data warehouse and the first data warehouse, the task replication table including: entries of the source data warehouse and The entry of the target data warehouse; The establishment module is also used to establish a task allocation table according to the distributed mode adopted by the corresponding server of the second data warehouse, the second data warehouse is the source data warehouse or the destination data warehouse in the data warehouse of each stage, The task allocation table includes: the distributed mode adopted by each server corresponding to the second data warehouse; A scheduling module, configured to schedule the tasks of each stage according to the task replication table and the task allocation table. 7. The device according to claim 6, wherein the task replication table further comprises: a first parameter and a second parameter; The first parameter is used to indicate that the first data warehouse is the source data warehouse of this stage; The second parameter is used to indicate that the first data warehouse is the target data warehouse of this stage. 8. The device according to claim 7, wherein the establishment module is specifically used for: determining entries in the source data warehouse and entries in the destination data warehouse according to the logical relationship between the source data warehouse and the destination data warehouse; determining said first parameter and said second parameter based on said first data repository; The task replication table is established according to the entry of the source data warehouse, the entry of the target data warehouse, the first parameter and the second parameter. 9. The device according to any one of claims 6-8, further comprising: The distributed manner includes: a shared nothing distributed manner and a shared disk distributed manner. 10. The device according to claim 9, wherein the scheduling module is specifically used for: The tasks of each stage are scheduled according to the determined distributed manner between the source data warehouse and the target data warehouse in each stage.