Multi-source database fusion method based on DAG and Smart ETL

Abstract

According to the multi-source database fusion method based on the DAG and the Smart ETL, tasks are arranged based on the DAG, the DAG provides a task scheduling framework for the Smart ETL, and the Smart ETL serves as a specific data processing executor. When the DAG scheduler schedules a certain task node according to a topological sorting result, if the task node is a data extraction task, starting an extraction task; and if the task node is a data processing task (such as a data extraction, conversion or loading task), starting a corresponding Smart ETL module to execute the task. The task scheduling system based on the DAG can reasonably arrange the task execution sequence according to the task dependency relationship, mutually independent tasks are executed in parallel, and the time of multi-source database fusion is greatly shortened. The method can adapt to various types of data sources and complex data formats, data fusion and processing can be completed without a large amount of manual intervention, and diversified data processing requirements of enterprises are met.

Description

Technical Field

[0001] The present invention relates to the technical field of data set processing, and in particular to a multi-source database fusion method based on DAG and Smart ETL, an electronic device, and a computer-readable storage medium. Background Art

[0002] DAG (Directed Acyclic Graph) task orchestration is a task management model that uses nodes to represent independent tasks and edges to represent dependencies. This acyclic structure avoids deadlocks and ensures that tasks execute in the order of dependencies. It is often used for parallel scheduling of complex processes, such as task scheduling and resource optimization in data pipelines.

[0003] Smart ETL (Smart Extract-Transform-Load) is a zero-code, drag-and-drop data processing tool that allows you to configure data cleansing, transformation, and loading processes through a visual interface, supporting the construction of lightweight data warehouses. Its core features include real-time preview and error correction, access to multiple data sources, and Spark-based big data processing capabilities, making it suitable for non-technical personnel to quickly complete professional-level data processing.

[0004] The Smart ETL task execution process can essentially be abstracted into a DAG structure. Each data processing step (such as input, cleaning, and output) corresponds to a DAG node. The order and dependencies between steps are defined by edges, forming an acyclic execution chain.

[0005] DAG orchestration layer: responsible for task scheduling optimization, dependency management, and resource allocation to ensure efficient task execution.

[0006] ETL execution layer: Implements specific data processing logic within the DAG framework, such as field mapping and aggregation calculations, and relies on DAG to ensure execution order and fault tolerance.

[0007] DAG task orchestration is a general methodology for task scheduling, while Smart ETL is a specific implementation tool for ETL scenarios. The former provides an execution framework and dependency management capabilities for the latter, while the latter encapsulates business logic and interactive interfaces based on the former, forming an integrated solution from orchestration to execution.

[0008] However, in the process of integrating multi-source databases, due to the presence of several different types of data sources and complex data formats, manual analysis and orchestration of DAG integration tasks and ETL execution logic are required. Therefore, this process takes a lot of time and requires manual intervention to complete the data integration processing and realize multi-source database integration, which cannot meet the diverse data processing needs and timeliness requirements of enterprises. Summary of the Invention

[0009] In order to solve the technical problems existing in the prior art, the present invention provides the following technical solutions:

[0010] In one aspect, a multi-source database fusion method based on DAG and Smart ETL is provided. The method is implemented by an electronic device and includes:

[0011] S1. Perform DAG task orchestration based on the multi-source database to be integrated to obtain the DAG topology structure.

[0012] S2. Mark the task nodes in the DAG topology structure that are suitable for execution by the Smart ETL module as non-executable nodes, and mark the other nodes as executable nodes;

[0013] S3. Execute the DAG topology structure in layers and generate temporary result data for the corresponding topology layer;

[0014] S4. Input the temporary result data into the input database of the corresponding topology layer according to the dependency relationship, and the input database executes the pre-built Smart ETL task to fuse the temporary result data from multiple source databases according to the corresponding ETL fusion logic and output it.

[0015] Preferably, the marking rule of the executable node is:

[0016] If the node type of the current task node is an input dataset or an output dataset, the current task node is marked as an executable node.

[0017] Preferably, the marking rule for the non-executable nodes is:

[0018] If the node type of the current task node is task execution logic: select columns and / or associations, the current task node is marked as a non-executable node.

[0019] Preferably, executing the DAG topology structure in layers and generating temporary result data of the corresponding topology layers includes:

[0020] Traversing all executable nodes and all non-executable nodes of each topological layer on the DAG topological structure in sequence, and asynchronously executing the executable nodes and the non-executable nodes in parallel, and starting the Smart ETL module when encountering the non-executable node;

[0021] The execution results of each layer are collected to obtain the temporary result data of the corresponding topology layer.

[0022] Preferably, executing the DAG topology structure in layers and generating temporary result data of the corresponding topology layers further includes:

[0023] When the task nodes on the topology layer are completed, the task nodes on which the task has been completed are marked as completed;

[0024] Continue to traverse and execute the next topology layer in the order of DAG task arrangement.

[0025] Preferably, inputting the temporary result data into an input database of a corresponding topological layer according to the dependency relationship comprises:

[0026] The DAG orchestrator schedules the execution of task nodes of each topology layer in sequence according to the dependency relationship of the DAG topology structure. When the task nodes are scheduled to the input database, the DAG orchestrator constructs a corresponding Smart ETL task for the input database of the topology layer.

[0027] The DAG orchestrator converts the temporary result data of all the executable nodes upstream of the input database into a virtual input data set and schedules the data to be input into the input database.

[0028] On the other hand, an electronic device is provided, comprising: a processor; and a memory, wherein the memory stores computer-readable instructions, and when the computer-readable instructions are executed by the processor, any one of the multi-source database fusion methods based on DAG and Smart ETL is implemented.

[0029] On the other hand, a computer-readable storage medium is provided, wherein at least one instruction is stored in the storage medium, and the at least one instruction is loaded and executed by a processor to implement any one of the above-mentioned multi-source database fusion methods based on DAG and Smart ETL.

[0030] The beneficial effects brought about by the technical solution provided by the embodiment of the present invention include at least:

[0031] This application orchestrates tasks based on DAG. DAG provides a task scheduling framework for Smart ETL, which acts as the specific data processing executor. When the DAG scheduler schedules a task node based on the topological sorting results, if the task node is a data extraction task, an extraction task is started; if the task node is a data processing task (such as a data extraction, transformation, or loading task), the corresponding Smart ETL module is started to execute the task.

[0032] The DAG-based task scheduling system can rationally arrange task execution order based on task dependencies and execute independent tasks in parallel, significantly reducing the time required to integrate multi-source databases. It can adapt to various data sources and complex data formats, completing data integration and processing without extensive manual intervention, meeting the diverse data processing needs of enterprises. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the technical solutions in the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0034] Figure 1 This is a flow chart of a multi-source database fusion method based on DAG and Smart ETL provided by an embodiment of the present invention;

[0035] Figure 2 This is a schematic diagram of a DAG topology structure for DAG task orchestration and Smart ETL execution provided by an embodiment of the present invention;

[0036] Figure 3 This is a schematic diagram of marking a node execution state provided by an embodiment of the present invention;

[0037] Figure 4 This is a schematic diagram of a status mark of an input database provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0038] The technical solution of the present invention is described below in conjunction with the accompanying drawings.

[0039] In the embodiments of the present invention, words such as "exemplarily" and "for example" are used to indicate examples, illustrations, or explanations. Any embodiment or design described as an "exemplary" in the present invention should not be interpreted as being preferred or advantageous over other embodiments or designs. Rather, the use of the word "exemplary" is intended to present concepts in a concrete manner. Furthermore, in the embodiments of the present invention, "and / or" can mean both or either of the two.

[0040] In the embodiments of the present invention, the terms "image" and "picture" may be used interchangeably. It should be noted that, when the distinction between them is not emphasized, their intended meanings are the same. The terms "of," "corresponding," and "corresponding" may be used interchangeably. It should be noted that, when the distinction between them is not emphasized, their intended meanings are the same.

[0041] In the embodiments of the present invention, sometimes a subscript such as W1 may be mistakenly written as a non-subscript form such as W1. When the difference is not emphasized, the meanings to be expressed are the same.

[0042] In order to make the technical problems, technical solutions and advantages to be solved by the present invention clearer, a detailed description will be given below with reference to the accompanying drawings and specific embodiments.

[0043] The embodiment of the present invention provides a multi-source database fusion method based on DAG and Smart ETL, which can be implemented by an electronic device, which can be a terminal or a server. Figure 1 The flowchart of the multi-source database fusion method based on DAG and Smart ETL is shown. The processing flow of this method may include the following steps:

[0044] S1. Perform DAG task orchestration based on the multi-source database to be integrated to obtain the DAG topology structure.

[0045] S2. Mark the task nodes in the DAG topology structure that are suitable for execution by the Smart ETL module as non-executable nodes, and mark the other nodes as executable nodes;

[0046] S3. Execute the DAG topology structure in layers and generate temporary result data for the corresponding topology layer;

[0047] S4. Input the temporary result data into the input database of the corresponding topology layer according to the dependency relationship, and the input database executes the pre-built Smart ETL task to fuse the temporary result data from multiple source databases according to the corresponding ETL fusion logic and output it.

[0048] During task execution, the Smart ETL module of the present invention obtains required input data based on the task dependencies defined in the DAG. For example, a data conversion task obtains input data from the output of a data extraction task. After the task is completed, Smart ETL passes the task's output to the nodes in the DAG that subsequently depend on it. Through this collaborative working mechanism, the DAG ensures that data processing tasks are executed in the correct order and according to their dependencies, and Smart ETL ensures that each data processing task runs in an orderly manner, thereby achieving efficient integration of multi-source databases.

[0049] When starting to build a multi-source database fusion task, you can build a DAG topology structure based on the multi-source database to be fused (such as Figure 2 The topology of DAG task orchestration and Smart ETL execution is shown):

[0050] DAG task scheduling framework: The DAG orchestrator constructs a directed acyclic graph (DAG), where nodes represent task units and edges represent task dependencies. It generates the task execution order through topological sorting and dynamically schedules tasks based on node types.

[0051] Smart ETL task execution module: starts the corresponding data processing logic according to the task type (data extraction, transformation, loading) scheduled by the DAG scheduler; during the execution process, it obtains input data from upstream dependent nodes and passes the output results to downstream nodes.

[0052] like Figure 2 The following is an example of a DAG task orchestration and Smart ETL execution graph. This method marks all nodes with the "Input Database" and "Output Dataset" types as executable nodes. It also marks Smart ETL nodes with "Select Column" and "Relate" types as non-executable nodes.

[0053] Node marking rules:

[0054] Mark external database nodes (such as MySQL, Oracle) and output dataset nodes as executable nodes to directly trigger asynchronous task execution;

[0055] Mark intermediate data processing nodes (such as "Select Column" and "Relate") as non-executable nodes, prohibiting independent operation and requiring them to be called through the Smart ETL task chain.

[0056] Node types include:

[0057] Input database node: connects to the external database, is marked as an executable node, asynchronously triggers the data extraction task, and generates a temporary data set;

[0058] Data processing intermediate nodes: represent operations such as field mapping, association, and filtering. They are marked as non-executable nodes and serve only as logical units in the Smart ETL task chain.

[0059] Output dataset node: Marked as an executable node, triggers the Smart ETL task chain, and writes the upstream data into the target database after logical processing.

[0060] Start execution and execute the hierarchical execution process: In the initial stage, all executable external database nodes are executed asynchronously and in parallel, temporary result data is generated and marked as completed; based on the DAG dependency relationship, subsequent executable nodes (output data sets) are triggered layer by layer, and upstream data is integrated through the Smart ETL task chain to generate the final output.

[0061] Preferably, executing the DAG topology structure in layers and generating temporary result data of the corresponding topology layers includes:

[0062] Traversing all executable nodes and all non-executable nodes of each topological layer on the DAG topological structure in sequence, and asynchronously executing the executable nodes and the non-executable nodes in parallel, and starting the Smart ETL module when encountering the non-executable node;

[0063] The execution results of each layer are collected to obtain the temporary result data of the corresponding topology layer.

[0064] The asynchronous parallel task running includes:

[0065] In the initial stage, the DAG orchestrator identifies all executable external database nodes and generates a parallel task queue;

[0066] Each task is executed independently, and after completion, a temporary data set is generated and the node status is updated;

[0067] Temporary datasets are stored in a unified format (such as Parquet or temporary tables) for downstream nodes to call.

[0068] Preferably, executing the DAG topology structure in layers and generating temporary result data of the corresponding topology layers further includes:

[0069] When the task nodes on the topology layer are completed, the task nodes on which the task has been completed are marked as completed;

[0070] Continue to traverse and execute the next topology layer in the order of DAG task arrangement.

[0071] like Figure 3 As shown, each marked node is a task. Through DAG construction, all executable nodes (MySQL database, Oracle database) on the start node are obtained, and the tasks are run through asynchronous parallel tasks. After the tasks are completed, temporary result data is generated and the two nodes are marked as completed. The DAG arrangement sequence is continued to search for executable nodes layer by layer. The intermediate nodes are prohibited from running nodes. The "Output Dataset" is used as an executable node to build a Smart ETL task. This task will obtain all the nodes that need to run the node, and use the data output before the "Input Database" node as the virtual node "Input Dataset" required for the Smart ETL task to participate in the task running, and the execution results are written to the target dataset (the next layer / specified target database).

[0072] Preferably, inputting the temporary result data into an input database of a corresponding topological layer according to the dependency relationship comprises:

[0073] The DAG orchestrator schedules the execution of task nodes of each topology layer in sequence according to the dependency relationship of the DAG topology structure. When the task nodes are scheduled to the input database, the DAG orchestrator constructs a corresponding Smart ETL task for the input database of the topology layer.

[0074] The DAG orchestrator converts the temporary result data of all the executable nodes upstream of the input database into a virtual input data set and schedules the data to be input into the input database.

[0075] When dispatched to the output dataset node, the DAG orchestrator builds the corresponding Smart ETL task chain. The task chain automatically converts the output data of the upstream executable node into a virtual input dataset as the input of the intermediate node. The data processing logic (such as conversion and association) is executed sequentially and finally written to the target database.

[0076] The generation method of the virtual input data set includes:

[0077] All upstream nodes of the output dataset node are parsed through DAG dependency relationships; the temporary result data of the upstream nodes are merged to generate a unified virtual dataset; the virtual dataset is used as the input of the Smart ETL task chain, shielding the execution details of the intermediate nodes.

[0078] like Figure 4 As shown, the input database of this topology layer has a node type of "Output to Dataset," which is an "Input Dataset" used to input the virtual input dataset upstream of this node. The ETL operator template in the preset Smart ETL task executes the corresponding data fusion strategy (the specific fusion strategy is set by the administrator). For more information on ETL algorithm templates, please refer to the applicant's application for "CN117112662A ETL Operator Parsing and Conversion Algorithm and Its Application," etc.

[0079] This method is applicable to multi-source heterogeneous data fusion scenarios, including: data extraction and integration across databases (such as MySQL, Oracle, and Hive); optimizing resource allocation through DAG scheduling in real-time or offline tasks; and achieving zero-code complex data processing through Smart ETL task chains. Executable nodes (external databases, output data sets) and non-executable nodes (intermediate steps in data processing) are dynamically scheduled through the DAG orchestrator, and hierarchical data processing is achieved in combination with Smart ETL task chains. In the initial stage, external database nodes are asynchronously executed to generate temporary data. In subsequent stages, intermediate logic is connected in series through virtual data sets, and finally the output node triggers the Smart ETL task chain to complete data integration. This solution solves the problems of dependency management, parallel optimization, and zero-code development in multi-source heterogeneous data processing.

[0080] The above embodiments can be implemented in whole or in part through software, hardware (such as circuits), firmware, or any other combination. When implemented using software, the above embodiments can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions or computer programs. When the computer instructions or computer program are loaded or executed on a computer, the processes or functions described in accordance with the embodiments of the present invention are generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a computer-readable storage medium or transmitted from one computer-readable storage medium to another. For example, the computer instructions can be transmitted from one website, computer, server, or data center to another website, computer, server, or data center via a wired method (such as infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device such as a server or data center that contains a collection of one or more available media. The available medium can be a magnetic medium (such as a floppy disk, hard disk, or magnetic tape), an optical medium (such as a DVD), or a semiconductor medium. The semiconductor medium can be a solid-state drive.

[0081] It should be understood that the term "and / or" as used herein simply describes a relationship between associated objects, indicating that three possible relationships exist. For example, "A and / or B" can represent: A alone, A and B together, or B alone. A and B can be singular or plural. Furthermore, the character " / " as used herein generally indicates an "or" relationship between the associated objects, but it may also indicate an "and / or" relationship. For specific understanding, please refer to the context.

[0082] In this disclosure, "at least one" means one or more, and "plurality" means two or more. "At least one of the following" or similar expressions refers to any combination of these items, including any combination of single or plural items. For example, "at least one of a, b, or c" can mean: a, b, c, ab, ac, bc, or abc, where a, b, and c can be single or plural.

[0083] It should be understood that in various embodiments of the present invention, the size of the serial numbers of the above-mentioned processes does not mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiments of the present invention.

[0084] Those skilled in the art will appreciate that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, or a combination of computer software and electronic hardware. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of the present invention.

[0085] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the above-described equipment, devices and units can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0086] In the several embodiments provided by the present invention, it should be understood that the disclosed devices, apparatuses and methods can be implemented in other ways. For example, the device embodiments described above are merely illustrative. For example, the division of the units is merely a logical function division. In actual implementation, there may be other division methods, such as multiple units or components can be combined or integrated into another device, or some features can be ignored or not executed. Another point is that the mutual coupling or direct coupling or communication connection shown or discussed can be through some interface, indirect coupling or communication connection of the device or unit, which can be electrical, mechanical or other forms.

[0087] The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of these units may be selected to achieve the purpose of this embodiment according to actual needs.

[0088] In addition, each functional unit in each embodiment of the present invention may be integrated into one processing unit, or each unit may exist physically separately, or two or more units may be integrated into one unit.

[0089] If the functions are implemented in the form of software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention, or the part that contributes to the prior art, or the part of the technical solution, can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to execute all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes various media that can store program codes, such as a USB flash drive, a mobile hard disk, a read-only memory (ROM), a random access memory (RAM), a magnetic disk, or an optical disk.

[0090] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any modifications or substitutions that can be easily conceived by a person skilled in the art within the technical scope disclosed in the present invention should be included in the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be based on the scope of protection of the claims.

Claims

1. A multi-source database fusion method based on DAG and Smart ETL, characterized by: The method comprises: S1. Perform DAG task orchestration based on the multi-source database to be integrated to obtain the DAG topology structure. S2. Mark the task nodes in the DAG topology structure that are suitable for execution by the Smart ETL module as non-executable nodes, and mark the other nodes as executable nodes; S3. Execute the DAG topology structure in layers and generate temporary result data for the corresponding topology layer; S4. Input the temporary result data into the input database of the corresponding topology layer according to the dependency relationship, and the input database executes the pre-built Smart ETL task to fuse the temporary result data from multiple source databases according to the corresponding ETL fusion logic and output it.

2. The multi-source database fusion method based on DAG and Smart ETL according to claim 1 is characterized in that: The marking rules for the executable nodes are: If the node type of the current task node is an input dataset or an output dataset, the current task node is marked as an executable node.

3. The multi-source database fusion method based on DAG and Smart ETL according to claim 1 is characterized in that: The marking rules for the non-executable nodes are: If the node type of the current task node is task execution logic: select columns and / or associations, the current task node is marked as a non-executable node.

4. The multi-source database fusion method based on DAG and Smart ETL according to claim 1 is characterized in that: The layered execution of the DAG topology structure and the generation of temporary result data of the corresponding topology layer include: Traversing all executable nodes and all non-executable nodes of each topological layer on the DAG topological structure in sequence, and asynchronously executing the executable nodes and the non-executable nodes in parallel, and starting the Smart ETL module when encountering the non-executable node; The execution results of each layer are collected to obtain the temporary result data of the corresponding topology layer.

5. The multi-source database fusion method based on DAG and Smart ETL according to claim 4 is characterized in that: The step of executing the DAG topology structure in layers and generating temporary result data of the corresponding topology layer further includes: When the task nodes on the topology layer are completed, the task nodes on which the task has been completed are marked as completed; Continue to traverse and execute the next topology layer in the order of DAG task arrangement.

6. The multi-source database fusion method based on DAG and Smart ETL according to claim 5 is characterized in that: Inputting the temporary result data into the input database of the corresponding topology layer according to the dependency relationship includes: The DAG orchestrator sequentially schedules the execution of task nodes at each topology layer according to the dependency relationship of the DAG topology structure. When the task nodes are scheduled to the input database, the DAG orchestrator constructs a corresponding Smart ETL task for the input database of the topology layer. The DAG orchestrator converts the temporary result data of all the executable nodes upstream of the input database into a virtual input data set and schedules the data to be input into the input database.

7. An electronic device, characterized in that: The electronic device comprises: processor; A memory having computer-readable instructions stored thereon, wherein when the computer-readable instructions are executed by the processor, the method according to any one of claims 1 to 6 is implemented.

8. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores program code, which can be called by a processor to execute the method according to any one of claims 1 to 6.

Images