Heterogeneous ETL task arrangement method and system based on virtual table relationship

Abstract

The present application relates to big data processing technical field, disclose a kind of heterogeneous ETL task arrangement method and system based on virtual table relationship.The method divides the task of the temporary task list needing to be arranged into SQL class task and / or non-SQL class task.SQL parsing library is used to carry out syntax check to SQL class task, and the input table and output table of SQL class task and mapping relationship are extracted.The input table and output table of non-SQL class task and mapping relationship are extracted, and a node, i.e.virtua table, is defined for each non-SQL class task lacking output table as output table in the extraction process.The input table and output table of all temporary tasks and their task relationship are analyzed, the dependency relationship between each temporary task is obtained accordingly, and each temporary task is connected by dependency relationship, to form directed acyclic graph after scheduling strategy is configured in user end, and the task of routine execution is formed according to directed acyclic graph.The present application can realize the automatic arrangement of heterogeneous task.

Description

Technical Field

[0001] This invention relates to the field of big data processing technology, specifically to a heterogeneous ETL task orchestration method based on virtual table relationships, and a heterogeneous ETL task orchestration system applying this method. Background Technology

[0002] ETL (Extract-Transform-Load) task orchestration is an essential part of big data task scheduling, a way to transform ad-hoc tasks into routine tasks. ETL tasks can be categorized into SQL (Structured Query Language) tasks and non-SQL tasks. SQL tasks, such as Hive SQL, Spark SQL, and Flink SQL, are script tasks implemented entirely with SQL code. Non-SQL tasks are generally implemented using Spark or Flink's Domain Specific Language (DSL), and this code may contain SQL scripts or DSL scripts.

[0003] Traditional ETL task orchestration methods are mostly implemented through manual configuration or semi-automation. Existing technologies generally only automate the orchestration of SQL tasks, lacking consideration for non-SQL tasks. For example, patent application CN104536811A proposes a scheduling method and apparatus based on HIVE tasks. This method ensures ordered task execution through dependencies between tasks, solving the task automation problem, but it is limited to HIVE SQL tasks and is not applicable to other task types. Furthermore, current ETL task orchestration still requires cumbersome user configuration. For instance, patent CN110232085B proposes data processing components and data flow directions, generating a model description language from the user-orchestrated ETL tasks and parsing it into a directed acyclic graph to form the task orchestration. The introduction of a descriptive language increases the system's complexity.

[0004] Therefore, as the scale of tasks grows, how to automate the orchestration of heterogeneous ETL tasks of any type (SQL and non-SQL) becomes an urgent problem to be solved. Summary of the Invention

[0005] To avoid and overcome the technical problems existing in the prior art, this invention provides a heterogeneous ETL task orchestration method and system based on virtual table relationships. This invention can automate the orchestration of both SQL-based and non-SQL-based ETL tasks, particularly improving the efficiency and convenience of task scheduling for non-SQL-based tasks.

[0006] To achieve the above objectives, the present invention provides the following technical solution:

[0007] This invention discloses a heterogeneous ETL task orchestration method based on virtual table relationships, comprising the following steps:

[0008] S1. Determine the list of temporary tasks that need to be arranged.

[0009] S2. Divide all temporary tasks into SQL tasks and / or non-SQL tasks according to their task type.

[0010] S3. When there are SQL-type tasks in the temporary task list, use the SQL parsing library to perform syntax checks on the SQL-type tasks, extract and save the input and output tables of the SQL-type tasks, and form a mapping relationship between the SQL-type tasks and the input and output tables.

[0011] S4. When non-SQL tasks exist in the temporary task list, extract and save the input and output tables of the non-SQL tasks, and establish a mapping relationship between SQL tasks and their input and output tables. The specific steps for extracting the input and output tables of non-SQL tasks include:

[0012] S41. Determine whether any non-SQL tasks within the scope defined by the preset list exist among all non-SQL tasks. If yes, proceed to step S42; otherwise, proceed to step S43. The non-SQL tasks defined by the preset list include project code implemented in the DSL language.

[0013] S42. For non-SQL tasks that match the preset list, use dynamic code parsing to obtain the initial input and output entity tables, and then execute step S43.

[0014] S43. Automatically extract the input and output tables for all non-SQL tasks based on the agreed task configuration. During the extraction process, priority is given to extracting from a historical relational table that represents the mapping relationship between "tables and file paths". For each non-SQL task that lacks an output table, a node, i.e., a virtual table, is defined as the output table.

[0015] S44. Merge and deduplicate all input and output table information obtained in step S42 and / or step S43 to complete the extraction of input and output tables for non-SQL tasks.

[0016] S5. Analyze the mapping relationship between all temporary tasks and their respective input and output tables, obtain the dependency relationship between each temporary task, and connect each temporary task through the dependency relationship. After configuring the scheduling strategy, a directed acyclic graph is formed, and routine tasks are formed based on the directed acyclic graph.

[0017] As a further improvement to the above scheme, step S3, which involves using an SQL parsing library to perform syntax checks on SQL-type tasks, includes: first, performing static syntax checks on the SQL-type tasks to verify their validity; and then performing dynamic syntax checks on the SQL-type tasks through an execution plan connected to the server.

[0018] As a further improvement to the above scheme, in step S41, the DSL language is the DSL language of Spark or Flink.

[0019] As a further improvement to the above scheme, in step S44, the merged and deduplicated input table and output table information are also displayed to the user in a visual form. After the user confirms the information, the extraction of the input table and output table for non-SQL tasks is completed.

[0020] As a further improvement to the above scheme, in step S5, the table names and file path relationships of the virtual tables in the input and output tables of each temporary task are mapped and stored in the storage system for use when parsing the virtual tables.

[0021] This invention also discloses a heterogeneous ETL task orchestration system based on virtual table relationships, applying the aforementioned heterogeneous ETL task orchestration method based on virtual table relationships. The orchestration system includes: a task acquisition module, a first extraction module, a second extraction module, and an automated DAG module.

[0022] The task acquisition module is used to determine the list of temporary tasks that need to be orchestrated, and to classify all temporary tasks into SQL tasks and / or non-SQL tasks according to their task type.

[0023] The first extraction module is used to perform syntax checks on SQL-type tasks when there are SQL-type tasks in the temporary task list, and to extract the input and output tables of the SQL-type tasks and their task relationships.

[0024] The second extraction module is used to extract the input and output tables of non-SQL tasks and their task relationships when non-SQL tasks exist in the temporary task list.

[0025] The automated DAG module is used to analyze the input and output tables of all temporary tasks and their task relationships, thereby obtaining the dependencies between each temporary task and connecting them through these dependencies. After the scheduling strategy is configured on the user side, a directed acyclic graph is formed for use by the scheduling system.

[0026] Compared with the prior art, the beneficial effects of the present invention are:

[0027] 1. The heterogeneous ETL task orchestration method disclosed in this invention automatically parses the input and output tables of non-SQL ETL tasks by introducing virtual tables into ETL task scheduling, converting non-SQL tasks into SQL tasks. Then, by analyzing the input and output tables of each temporary task, the dependency relationships between SQL and non-SQL heterogeneous ETL tasks are identified, enabling automated orchestration of heterogeneous tasks. Based on this, preliminary extraction is performed through dynamic code parsing, followed by sequential extraction according to agreed-upon task configurations, and finally, a merging and deduplication operation. This parallel extraction method ensures more accurate and reliable results, reduces manual operation, and prevents the possibility of omissions.

[0028] 2. The heterogeneous ETL task orchestration method disclosed in this invention eliminates the need for cumbersome configuration by the user and achieves task scheduling and configuration in an automated manner.

[0029] 3. The heterogeneous ETL task orchestration system disclosed in this invention can apply the above method and produce the same beneficial effects as the above method, which will not be elaborated here. Attached Figure Description

[0030] Figure 1 This is a flowchart of the heterogeneous ETL task orchestration method based on virtual table relationships in Embodiment 1 of the present invention.

[0031] Figure 2 This is a flowchart illustrating the extraction of the input and output tables for non-SQL tasks in Embodiment 1 of the present invention.

[0032] Figure 3 This is a framework diagram of the heterogeneous ETL task orchestration system based on virtual table relationships in Embodiment 2 of the present invention. Detailed Implementation

[0033] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0034] Example 1

[0035] Please see Figure 1 This embodiment provides a heterogeneous ETL task orchestration method based on virtual table relationships, including the following steps:

[0036] S1. Determine the list of temporary tasks that need to be arranged.

[0037] In this embodiment, the temporary task list contains both SQL-type tasks and non-SQL-type tasks. In some embodiments, the temporary task list can consist entirely of SQL-type tasks or entirely of non-SQL-type tasks.

[0038] S2. Divide all temporary tasks into SQL tasks and non-SQL tasks according to their task type.

[0039] In this embodiment, it can be determined whether the temporary task is an SQL-type task based on the script type of the temporary task.

[0040] S3. When SQL-type tasks exist in the temporary task list, use the SQL parsing library to perform syntax checks on the SQL-type tasks, extract and save the input and output tables of the SQL-type tasks, and establish a mapping relationship between the SQL-type tasks and the input and output tables.<task,(input,output)> .

[0041] Step S3, the specific process of performing syntax checks on SQL tasks using an SQL parsing library, may include: First, performing a static syntax check on the SQL tasks to verify their validity, such as missing SQL keywords. Second, performing a dynamic syntax check on the SQL tasks by connecting to the execution plan on the server. After the syntax check, all input and output tables of the SQL tasks are parsed out. Finally, the parsed tables and task relationships are stored.

[0042] S4. If non-SQL tasks exist in the temporary task list, extract and save the input and output tables of the non-SQL tasks, and establish a mapping relationship between the non-SQL tasks and the input and output tables.<task,(input,output)> .

[0043] Please see Figure 2 The method for extracting the input and output tables for non-SQL tasks specifically includes the following steps:

[0044] S41. Determine whether there are any non-SQL tasks that fit the range defined by the preset list among all non-SQL tasks. If yes, proceed to step S42; otherwise, proceed to step S43.

[0045] The non-SQL tasks defined in the preset list include engineering code implemented in a DSL language. In this embodiment, the DSL language can be the DSL language of Spark or Flink, which are commonly used in the field of big data tasks. Of course, in some embodiments, the DSL language can also be other types.

[0046] S42. For non-SQL tasks that match the preset list, use dynamic code parsing to obtain the initial input and output entity tables, and then execute step S43.

[0047] S43. Automatically extract the input and output tables for all non-SQL tasks based on the agreed task configuration. During the extraction process, priority is given to extracting from a historical relational table that represents the mapping relationship between "tables and file paths", and a node, i.e., a virtual table, is defined for each non-SQL task that lacks an output table as the output table.

[0048] It should be noted that the so-called agreed task configuration refers to the use of a .json or .xml configuration file in the specific implementation process, in which input=xxx and output=yyy are defined.

[0049] The system will read this configuration file and parse out the corresponding input and output values.

[0050] The above `input = xxx` represents the input of a task as `xxx`. However, a task may have more than one `xxx` input, such as `xxx = table1, table2, path1, path2`. `path1` and `path2` are the file paths that the task depends on. These file paths need to be mapped to corresponding tables, which may be real or virtual. There is a mapping relationship between all tables and file paths, recorded through the aforementioned historical relationship table.

[0051] S44. Merge and deduplicate all input and output table information obtained in step S42 and / or step S43 to complete the extraction of input and output tables for non-SQL tasks.

[0052] In this embodiment, the merged and deduplicated input and output table information can also be displayed to the user in a visual form. After the user confirms the information, the extraction of the input and output tables for non-SQL tasks is completed.

[0053] It's important to note that Spark and Flink's execution engines allow for initial extraction via dynamic code parsing. Subsequently, according to the agreed-upon task configuration, extraction is performed sequentially. This process may result in duplicate extractions, necessitating a merging and deduplication operation. For example, if S42 extracts A, B, and C, and S43 extracts B, C, and D, the merged and deduplicated result will be A, B, C, and D. This parallel extraction method ensures more accurate and reliable results, reduces manual intervention, and prevents omissions.

[0054] S5. Analyze the mapping relationship between all temporary tasks and their respective input and output tables, obtain the dependency relationship between each temporary task, and connect each temporary task through the dependency relationship. After configuring the scheduling strategy on the user side, a directed acyclic graph is formed for the scheduling system to use. The scheduling system can generate routine tasks based on the directed acyclic graph.

[0055] In step S5, the table names and file path relationships of the virtual tables in the input and output tables of each temporary task are mapped and stored in the storage system for use when parsing the virtual tables.

[0056] Example 2

[0057] This embodiment provides a heterogeneous ETL task orchestration system 100 based on virtual table relationships, which can apply the heterogeneous ETL task orchestration method based on virtual table relationships in Embodiment 1.

[0058] Please see Figure 3 The orchestration system 100 includes: a task acquisition module 101, a first extraction module 102, a second extraction module 103, and an automated DAG module 104. Here, DAG (Database Availability Group) refers to a database availability group.

[0059] The task acquisition module 101 is used to determine the list of temporary tasks that need to be orchestrated, and to divide all temporary tasks into SQL tasks and / or non-SQL tasks according to task type.

[0060] The first extraction module 102 is used to perform syntax checks on SQL-type tasks using an SQL parsing library when there are SQL-type tasks in the temporary task list, and to extract the input and output tables of the SQL-type tasks and their task relationships.

[0061] The second extraction module 103 is used to extract the input and output tables of non-SQL tasks and their task relationships when there are non-SQL tasks in the temporary task list.

[0062] The automated DAG module 104 is used to analyze the input and output tables of all temporary tasks and their task relationships, thereby obtaining the dependencies between each temporary task and connecting each temporary task through the dependencies. After the scheduling strategy is configured on the user end, a directed acyclic graph is formed for use by the scheduling system.

[0063] The above description is only a preferred embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any equivalent substitutions or modifications made by those skilled in the art within the scope of the technology disclosed in the present invention, based on the technical solution and inventive concept of the present invention, should be covered within the scope of protection of the present invention.

Claims

1. A heterogeneous ETL task orchestration method based on virtual table relationships, characterized in that, Including the following steps: S1. Determine the list of temporary tasks that need to be arranged; S2. Divide all temporary tasks into SQL tasks and / or non-SQL tasks according to their task type; S3. When there are SQL-type tasks in the temporary task list, use the SQL parsing library to perform syntax checks on the SQL-type tasks, extract and save the input and output tables of the SQL-type tasks, and form a mapping relationship between the SQL-type tasks and the input and output tables. S4. When non-SQL tasks exist in the temporary task list, extract and save the input and output tables of the non-SQL tasks, and form a mapping relationship between the non-SQL tasks and the input and output tables; wherein, the method for extracting the input and output tables of non-SQL tasks specifically includes the following steps: S41. Determine whether there are any non-SQL tasks that fit the range defined by the preset list among all non-SQL tasks; if yes, proceed to step S42, otherwise proceed to step S43; wherein, the non-SQL tasks defined by the preset list include project code implemented in DSL language; S42. For non-SQL tasks that match the preset list, use dynamic code parsing to obtain the initial input and output entity tables, and then execute step S43; S43. Automatically extract the input and output tables of all non-SQL tasks according to the agreed task configuration; in the extraction process, priority is given to extracting from a historical relationship table that represents the mapping relationship between "table and file path", and a node, i.e. a virtual table, is defined for each non-SQL task that lacks an output table as the output table; S44. Merge and deduplicate all input and output table information obtained in step S42 and / or step S43 to complete the extraction of input and output tables for non-SQL tasks; S5. Analyze the mapping relationship between all temporary tasks and their respective input and output tables, obtain the dependency relationship between each temporary task, and connect each temporary task through the dependency relationship. After configuring the scheduling strategy, a directed acyclic graph is formed, and routine tasks are formed according to the directed acyclic graph.

2. The heterogeneous ETL task orchestration method based on virtual table relationships according to claim 1, characterized in that, In step S3, the specific process of using the SQL parsing library to perform syntax checks on SQL-type tasks includes: first, performing static syntax checks on the SQL-type tasks to check their validity; and then performing dynamic syntax checks on the SQL-type tasks by connecting to the execution plan on the server.

3. The heterogeneous ETL task orchestration method based on virtual table relationships according to claim 1, characterized in that, In step S41, the DSL language is the DSL language of Spark or Flink.

4. The heterogeneous ETL task orchestration method based on virtual table relationships according to claim 1, characterized in that, In step S44, the merged and deduplicated input and output table information is also displayed to the user in a visual form. After the user confirms the information, the extraction of the input and output tables for non-SQL tasks is completed.

5. The heterogeneous ETL task orchestration method based on virtual table relationships according to claim 1, characterized in that, In step S5, the table names and file path relationships of the virtual tables in the input and output tables of each temporary task are mapped and stored in the storage system for use when parsing the virtual tables.

6. A heterogeneous ETL task orchestration system based on virtual table relationships, characterized in that, Apply the heterogeneous ETL task orchestration method based on virtual table relationships as described in any one of claims 1 to 5; The orchestration system includes: The task acquisition module is used to determine the list of temporary tasks that need to be orchestrated, and to divide all temporary tasks into SQL tasks and / or non-SQL tasks according to task type. The first extraction module is used to perform syntax checking on the SQL-type tasks using an SQL parsing library when there are SQL-type tasks in the temporary task list, and to extract the input and output tables of the SQL-type tasks and their task relationships. The second extraction module is used to extract the input and output tables of non-SQL tasks and their task relationships when non-SQL tasks exist in the temporary task list; and The automated DAG module is used to analyze the input and output tables of all temporary tasks and their task relationships, thereby obtaining the dependencies between each temporary task and connecting them through these dependencies. After the scheduling strategy is configured on the user side, a directed acyclic graph is formed for use by the scheduling system.

Images