Data blood relationship analysis method and device for ETL system and electronic equipment

Abstract

The invention provides a data consanguinity analysis method and device for an ETL system and electronic equipment, and the device comprises an acquisition module, an analysis module, an integration module, a storage module and a display module. And the acquisition module is used for acquiring metadata information related to ETL operation from various data sources, and the acquisition modes comprise active data acquisition through an API interface, database connection and the like, or data push from other systems is received. According to the data consanguinity analysis method and device for the ETL system and the electronic equipment, the input and output data streams of the nodes are deeply analyzed, the transmission path of the data among the nodes is accurately identified, the data consanguinity of all the nodes in the flow is integrated, and a complete and consistent flow-level data consanguinity graph is generated.

Description

Technical Field

[0001] The present invention relates to the technical field of data lineage analysis, and specifically discloses a data lineage analysis method, device, and electronic equipment for an ETL system. Background Art

[0002] As the amount of enterprise data continues to grow, data sources are becoming more and more diverse, including databases, data warehouses, data lakes, APIs, file systems, etc. After entering the ETL (Extract, Transform, Load) system, these data undergo a series of extraction, transformation, and loading processes before being stored in the target system.

[0003] In ETL data governance, data lineage analysis is a key component that helps enterprises understand the entire life cycle of data from source to target, including the data extraction, transformation and loading process. Data lineage analysis plays an important role in data governance, including ensuring data transparency, compliance, quality management, business process optimization, data security and privacy protection.

[0004] However, with the continuous growth of enterprise data and the diversification of data sources, the data types, formats and structures that ETL systems need to process are becoming more and more diverse. This diversity and complexity brings many challenges to data lineage analysis, resulting in the shortcomings of insufficient accuracy and completeness of related data lineage analysis methods when processing complex data flows.

[0005] In the existing technology, data lineage analysis for ETL systems (such as analysis methods based on SQL statements) can only obtain basic data lineage relationships. When the system involves multiple data sources, multiple data formats, complex conversion logic and multiple processing stages, the existing technology cannot effectively handle complex data flows and dependencies.

[0006] In the existing data lineage analysis technology, the integration of intermediate node relationships and the integration of indirect data lineage relationships are not perfect, and there are problems such as difficulty in identifying intermediate nodes and inaccurate identification of intermediate node dependency relationships. For example, in the ETL process, data may pass through multiple intermediate nodes (such as temporary tables, caches, intermediate data sets, etc.). The existing data lineage analysis technology has difficulties in identifying and recording these intermediate nodes, resulting in imperfect integration of intermediate node relationships. In view of the problems existing in the prior art, the present invention provides an automated data lineage processing method, system, terminal and storage medium for the ETL system. Summary of the invention

[0007] In view of the above defects or deficiencies in the prior art, the present application aims to provide a data lineage analysis device for an ETL system, the device comprising an acquisition module, a parsing module, an integration module, a storage module, and a display module, characterized in that;

[0008] The acquisition module is responsible for acquiring metadata information related to ETL jobs from various data sources, including actively acquiring data through API interfaces, database connections, etc., or receiving data push from other systems;

[0009] The parsing module is used to parse the acquired ETL job metadata to parse out data lineage information, where the parsed information includes a data source table, a target table, and data conversion rules;

[0010] The integration module is used to integrate the data blood relationship information extracted by the analysis module to construct a complete data blood relationship map;

[0011] The storage module is used to persistently store the integrated data bloodline map for subsequent query and analysis;

[0012] The display module is used to update the business data lineage map corresponding to the business data according to the business data dependency relationship, and display the updated business data lineage map.

[0013] The data lineage analysis electronic device for the ETL system, the data lineage analysis device for the ETL system is installed in the data lineage analysis electronic device, and the data lineage analysis electronic device for the ETL system is composed of a processor, a memory, a bus, an input / output interface, and a communication interface.

[0014] A data lineage analysis method for an ETL system, the data lineage analysis method for an ETL system is applied to the data lineage analysis device for an ETL system according to claim 1, and the data lineage analysis method for an ETL system comprises the following steps:

[0015] Step 1: Automated data lineage analysis based on ETL node configuration: With ETL nodes as the basic processing unit, the system automatically reads and analyzes the configuration information of each node. By deeply analyzing the input and output data flows of the nodes, the system can accurately identify the data transmission path between nodes, thereby building a preliminary data lineage relationship diagram.

[0016] Step 2: Data lineage integration and storage at the ETL process level: Based on the analysis of a single node, the system further integrates the data lineage relationships of all nodes in the process based on the ETL process. By merging, verifying and optimizing the lineage information of each node, the system generates a complete and consistent process-level data lineage graph. Subsequently, the lineage graph will be stored in the system's local database or distributed storage system for subsequent query and analysis.

[0017] Step 3. Synchronization of data lineage and integration with metadata management system: To ensure the real-time and consistency of data lineage information, the system will automatically synchronize the integrated process-level data lineage to the metadata management system through close integration with the metadata management system.

[0018] Preferably, the system saves the node configuration information in the ETL process to a local database or distributed storage system. Before each execution of data lineage analysis, the system will automatically determine whether the node configuration information needs to be updated. If a change in the configuration information is detected, the system will trigger an update mechanism to ensure the accuracy and real-time nature of the data lineage analysis.

[0019] Preferably, the data lineage is parsed based on the query SQL, including the data table-level lineage and the field lineage, and the data lineage only includes the source node, wherein the query SQL parses the data lineage using the JSqlParser tool class.

[0020] Preferably, query SQL to parse data lineage, using the JSqlParser tool class, the steps are as follows:

[0021] Step 1: Parse the data table lineage node information in the SQL statement. The system identifies and extracts all data tables involved in the SQL statement and builds table-level lineage relationships.

[0022] Step 2: Parse the query data field lineage node list in the SQL statement. The system further parses the query field in the SQL statement, identifies and records the field information involved in the calculation. If the field is nested or complexly calculated, the system will recursively parse and store the relevant information.

[0023] Step 3: Compare the parsed table-level lineage and field lineage with the data fields in the node configuration. Through the comparison, the system can identify and delete the data lineage of the node that is not output, ensuring the simplicity and accuracy of the lineage relationship.

[0024] Step 4: Store node data lineage.

[0025] Preferably, the integration of original data lineage is mainly divided into the following steps:

[0026] S1. Get the process node list: extract all nodes from the data processing process and sort them in the order of node execution. This step ensures that the tracking order of data lineage is consistent with the actual data processing order;

[0027] S2. Obtaining the original data lineage node by node: Obtain the automatically parsed original data lineage of each node one by one according to the sorted node order. These lineage information are stored in an original data lineage set for subsequent integration processing;

[0028] S3, pre-integration of raw data lineage, before complete integration, pre-processing the raw data lineage set, this step includes deduplication, merging similar lineage paths, and preliminary data lineage relationship optimization to improve the efficiency and accuracy of subsequent integration;

[0029] S4. Completely integrate data lineage. Based on pre-integration, complete data lineage integration is performed. This step involves complex algorithms and logic to ensure that the data lineage relationships of all nodes are correctly connected and represented. The integrated data lineage can clearly show the flow and changes of data in the processing process.

[0030] S5. Store the integrated complete data lineage in a persistent storage system.

[0031] Preferably, the pre-integration of data lineage includes the following steps:

[0032] Step 1: Pre-integration, supplement the data lineage of the data field, and identify the data lineage type:

[0033] Data lineage that only contains source nodes: This type of data lineage comes directly from the data source and has no preceding lineage relationship;

[0034] Data lineage that only contains the target node: This type of data lineage is the final output data node and has a preceding lineage relationship;

[0035] Data lineage that includes both source nodes and target nodes: This type of data lineage includes both the data source and the final output node, and a complete lineage chain needs to be established;

[0036] Step 2: Process different types of data lineage: For data lineage that only contains source nodes: directly add its lineage nodes to the lineage node set without any other processing;

[0037] For data lineage that only contains target nodes: search for the predecessor lineage node with the same field name as the target node from the lineage node set, and establish the lineage relationship between the target node and the predecessor lineage node through consistent matching of field names. For data lineage that contains both source nodes and target nodes: search for the predecessor lineage node with the same field name as the source node from the lineage node set, and establish the lineage relationship between the source node and the predecessor lineage node through consistent matching of field names. Complete the lineage chain between the source node and the target node to ensure the accuracy and completeness of the lineage relationship: use consistent matching of field names to ensure the accuracy of the lineage relationship, perform integrity checks on all data lineage relationships, and ensure the integrity of the lineage chain.

[0038] Beneficial effects: 1. A method for storing and managing data lineage relationships, by defining a clear data model and storage structure, to achieve efficient data lineage relationship management. In the specific implementation, two core tables are mainly designed, namely, the data lineage relationship table and the lineage node table, such as Figure 5 The data lineage table shown is used to record the flow relationship of data between different nodes, and is mainly composed of three fields: source node, target node, and lineage relationship. The source node indicates the starting point of data flow, the target node indicates the end point of data flow, and the lineage relationship describes the relationship between the source node and the target node. As shown in the figure, the lineage node table is mainly composed of the following fields: unique number, node data type, child node data type, data source number, data source name, data source connection IP, data source connection port, data source type, database name, data table name, data table alias, data column name, data column alias, and lineage nodes involved in the calculation of fields.

[0039] 2. The processor of the computer device is mainly used to provide computing and control capabilities to ensure efficient completion of data processing and task scheduling. The memory part includes non-volatile storage media and internal memory. The non-volatile storage medium stores an operating system, computer programs and related databases. The internal memory provides necessary environmental support for the operation of the operating system and computer programs to ensure that there is sufficient memory space when the program is executed. In addition, the database is used to store information related to the data lineage analysis method. The function of the input / output interface is to enable the processor to exchange information with external devices, such as receiving user input or outputting calculation results. The communication interface is used to communicate with external terminals through a network connection to support data transmission and service requests. When the computer program is executed by the processor, it can implement a data lineage analysis method to help users understand and manage the transmission relationship of data flows between different systems;

[0040] 3. In-depth analysis of the input and output data flows of the nodes, accurately identifying the data transmission paths between nodes, and integrating the data lineage relationships of all nodes in the process to generate a complete and consistent process-level data lineage graph. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] Other features, objects and advantages of the present application will become more apparent by reading the detailed description of non-limiting embodiments made with reference to the following drawings:

[0042] Figure 1 This is the overall flow chart of the data blood relationship analysis of the present invention;

[0043] Figure 2 This is a flow chart of data lineage integration of the present invention;

[0044] Figure 3 This is a diagram of the data bloodline analysis device of the present invention;

[0045] Figure 4 It is a diagram of electronic equipment for data lineage analysis of the present invention;

[0046] Figure 5 It is the data blood relationship table of the present invention;

[0047] Figure 6 It is the data lineage node table of the present invention. DETAILED DESCRIPTION

[0048] The present application is further described in detail below in conjunction with the accompanying drawings and embodiments. It is to be understood that the specific embodiments described herein are only used to explain the relevant invention, rather than to limit the invention. It is also necessary to explain that, for ease of description, only the parts related to the invention are shown in the accompanying drawings.

[0049] The accompanying drawings in the embodiments of the present invention: different types of section lines in the drawings are not marked according to national standards, nor do they impose requirements on the materials of the components. Instead, they are used to distinguish the cross-sectional views of the components in the drawings.

[0050] See also Figure 1-6 , a data lineage analysis device for ETL system, the device includes an acquisition module, a parsing module, an integration module, a storage module, and a display module,

[0051] The acquisition module is responsible for acquiring metadata information related to ETL jobs from various data sources, including actively acquiring data through API interfaces, database connections, etc., or receiving data push from other systems;

[0052] The parsing module is used to parse the acquired ETL job metadata to parse out data lineage information, where the parsed information includes a data source table, a target table, and data conversion rules;

[0053] The integration module is used to integrate the data blood relationship information extracted by the analysis module to construct a complete data blood relationship map;

[0054] The storage module is used to persistently store the integrated data bloodline map for subsequent query and analysis;

[0055] The display module is used to update the business data lineage map corresponding to the business data according to the business data dependency relationship, and display the updated business data lineage map.

[0056] The data lineage analysis electronic device for the ETL system, the data lineage analysis device for the ETL system is installed in the data lineage analysis electronic device, and the data lineage analysis electronic device for the ETL system is composed of a processor, a memory, a bus, an input / output interface, and a communication interface.

[0057] A data lineage analysis method for an ETL system is applied to the data lineage analysis device for an ETL system, and the data lineage analysis method for an ETL system comprises the following steps:

[0058] Step 1: Automated data lineage analysis based on ETL node configuration: With ETL nodes as the basic processing unit, the system automatically reads and analyzes the configuration information of each node. By deeply analyzing the input and output data flows of the nodes, the system can accurately identify the data transmission path between nodes, thereby building a preliminary data lineage relationship diagram.

[0059] Step 2: Data lineage integration and storage at the ETL process level: Based on the analysis of a single node, the system further integrates the data lineage relationships of all nodes in the process based on the ETL process. By merging, verifying and optimizing the lineage information of each node, the system generates a complete and consistent process-level data lineage graph. Subsequently, the lineage graph will be stored in the system's local database or distributed storage system for subsequent query and analysis.

[0060] Step 3. Synchronization of data lineage and integration with metadata management system: To ensure the real-time and consistency of data lineage information, the system will automatically synchronize the integrated process-level data lineage to the metadata management system through close integration with the metadata management system.

[0061] Among them, the system saves the node configuration information in the ETL process to the local database or distributed storage system. Before each execution of data lineage analysis, the system will automatically determine whether the node configuration information needs to be updated. If it detects that the configuration information has changed, the system will trigger the update mechanism to ensure the accuracy and real-time performance of data lineage analysis.

[0062] The data lineage is parsed based on the query SQL, including the data table-level lineage and the field lineage. The data lineage only includes the source node. The query SQL parses the data lineage using the JSqlParser tool class.

[0063] Among them, query SQL to parse data lineage, use JSqlParser tool class, the steps are as follows:

[0064] Step 1: Parse the data table lineage node information in the SQL statement. The system identifies and extracts all data tables involved in the SQL statement and builds table-level lineage relationships.

[0065] Step 2: Parse the query data field lineage node list in the SQL statement. The system further parses the query field in the SQL statement, identifies and records the field information involved in the calculation. If the field is nested or complexly calculated, the system will recursively parse and store the relevant information.

[0066] Step 3: Compare the parsed table-level lineage and field lineage with the data fields in the node configuration. Through the comparison, the system can identify and delete the data lineage of the node that is not output, ensuring the simplicity and accuracy of the lineage relationship.

[0067] Step 4: Store node data lineage.

[0068] Among them, the integration of original data lineage is mainly divided into the following steps:

[0069] S1. Get the process node list: extract all nodes from the data processing process and sort them in the order of node execution. This step ensures that the tracking order of data lineage is consistent with the actual data processing order;

[0070] S2. Obtaining the original data lineage node by node: Obtain the automatically parsed original data lineage of each node one by one according to the sorted node order. These lineage information are stored in an original data lineage set for subsequent integration processing;

[0071] S3, pre-integration of raw data lineage, before complete integration, pre-processing the raw data lineage set, this step includes deduplication, merging similar lineage paths, and preliminary data lineage relationship optimization to improve the efficiency and accuracy of subsequent integration;

[0072] S4. Completely integrate data lineage. Based on pre-integration, complete data lineage integration is performed. This step involves complex algorithms and logic to ensure that the data lineage relationships of all nodes are correctly connected and represented. The integrated data lineage can clearly show the flow and changes of data in the processing process.

[0073] S5. Store the integrated complete data lineage in a persistent storage system.

[0074] The pre-integration of data lineage includes the following steps:

[0075] Step 1: Pre-integration, supplement the data lineage of the data field, and identify the data lineage type:

[0076] Data lineage that only contains source nodes: This type of data lineage comes directly from the data source and has no preceding lineage relationship;

[0077] Data lineage that only contains the target node: This type of data lineage is the final output data node and has a preceding lineage relationship;

[0078] Data lineage that includes both source nodes and target nodes: This type of data lineage includes both the data source and the final output node, and a complete lineage chain needs to be established;

[0079] Step 2: Process different types of data lineage: For data lineage that only contains source nodes: directly add its lineage nodes to the lineage node set without any other processing;

[0080] For data lineage that only contains target nodes: search for the predecessor lineage node with the same field name as the target node from the lineage node set, and establish the lineage relationship between the target node and the predecessor lineage node through consistent matching of field names. For data lineage that contains both source nodes and target nodes: search for the predecessor lineage node with the same field name as the source node from the lineage node set, and establish the lineage relationship between the source node and the predecessor lineage node through consistent matching of field names. Complete the lineage chain between the source node and the target node to ensure the accuracy and completeness of the lineage relationship: use consistent matching of field names to ensure the accuracy of the lineage relationship, perform integrity checks on all data lineage relationships, and ensure the integrity of the lineage chain.

[0081] In one embodiment, Figure 1 As shown, an automated data lineage processing method for ETL systems is provided. The method is designed to achieve efficient analysis, integration and management of data lineage, and is applicable to a variety of computing environments, including but not limited to terminals, servers, and distributed systems composed of terminals and servers. Through the collaborative work of terminals and servers, the method can achieve automated processing and synchronization of data lineage.

[0082] In this embodiment, the method is applied to a data lineage system as an example for detailed description. A data lineage system is a system specifically used for data lineage analysis, and its core function is to track and record the flow path and dependencies of data in the ETL process. The core steps of the method are as follows:

[0083] 1. Automated data lineage analysis based on ETL node configuration: With ETL nodes as the basic processing unit, the system automatically reads and analyzes the configuration information of each node. By deeply analyzing the input and output data flows of the nodes, the system can accurately identify the data transmission path between nodes, thereby building a preliminary data lineage relationship diagram.

[0084] 2. Data lineage integration and storage at the ETL process level: Based on the analysis of a single node, the system further integrates the data lineage relationship of all nodes in the process based on the ETL process. By merging, verifying and optimizing the lineage information of each node, the system generates a complete and consistent process-level data lineage graph. Subsequently, the lineage graph will be stored in the system's local database or distributed storage system for subsequent query and analysis.

[0085] 3. Synchronization of data lineage and integration with metadata management system: In order to ensure the real-time and consistency of data lineage information, the system will automatically synchronize the integrated process-level data lineage to the metadata management system (MDM). Through close integration with MDM, this method can not only achieve centralized management of data lineage, but also provide strong support for data governance, auditing and compliance checks.

[0086] Provide a detailed description of the ETL system's automated data lineage processing process.

[0087] The first is the automated data lineage analysis based on ETL node configuration, such as Figure 2 As shown:

[0088] The system saves the node configuration information in the ETL process to the local database or distributed storage system. Before each data lineage analysis, the system will automatically determine whether the node configuration information needs to be updated. If the configuration information is detected to have changed, the system will trigger the update mechanism to ensure the accuracy and real-time performance of the data lineage analysis.

[0089] Parse data lineage based on query SQL, including table-level lineage and field lineage (data lineage only includes source nodes). Query SQL to parse data lineage using the JSqlParser tool class. The main steps are as follows: 1. Parse the data table lineage node information in the SQL statement. The system identifies and extracts all data tables involved in the SQL statement and builds table-level lineage relationships. 2. Parse the query data field lineage node list in the SQL statement. The system further parses the query fields in the SQL statement, identifies and records the field information involved in the calculation. If the field is nested or complexly calculated, the system will recursively parse and store the relevant information.

[0090] Compare the parsed table-level lineage and field lineage with the data fields in the node configuration. Through comparison, the system can identify and delete the data lineage of the node that is not output, ensuring the simplicity and accuracy of the lineage relationship.

[0091] Storage node data lineage

[0092] The integration of raw data lineage is mainly divided into the following steps:

[0093] Get the process node list: Extract all nodes from the data processing process and sort them in the order in which they are executed. This step ensures that the order of data lineage tracking is consistent with the actual data processing order.

[0094] Obtaining the original data lineage node by node: Obtain the automatically parsed original data lineage of each node one by one according to the sorted node order. These lineage information are stored in a raw data lineage set for subsequent integration processing.

[0095] Pre-integrate the original data lineage, before the complete integration, pre-process the original data lineage set. This step includes deduplication, merging similar lineage paths, and preliminary data lineage relationship optimization to improve the efficiency and accuracy of subsequent integration.

[0096] Completely integrate data lineage. Based on pre-integration, complete data lineage integration is performed. This step involves complex algorithms and logic to ensure that the data lineage relationships of all nodes are correctly connected and represented. The integrated data lineage can clearly show the flow and changes of data in the processing process.

[0097] The integrated complete data lineage is stored in a persistent storage system.

[0098] Pre-integration of data lineage

[0099] Pre-integration, supplementing the data lineage of data fields

[0100] 1. Identify data lineage type:

[0101] Data lineage that only contains source nodes: This type of data lineage comes directly from the data source and has no preceding lineage relationship.

[0102] Data lineage that only contains the target node: This type of data lineage is the final output data node and has a preceding lineage relationship.

[0103] Data lineage that includes both source nodes and target nodes: This type of data lineage includes both the data source and the final output node, and a complete lineage chain needs to be established.

[0104] 2. Handling different types of data lineage:

[0105] For data lineage that only contains source nodes: directly add its lineage nodes to the lineage node set without any other processing.

[0106] For data lineage that only includes the target node:

[0107] Find the predecessor lineage node with the same field name as the target node from the lineage node set.

[0108] By matching the field names consistently, a blood relationship between the target node and the preceding blood relationship node is established.

[0109] For data lineage that includes both source and target nodes:

[0110] Search the preceding lineage node from the lineage node set with the same field name as the source node.

[0111] The blood relationship between the source node and the preceding blood relationship node is established through consistent matching of field names.

[0112] Complete the blood relationship chain between the source node and the target node.

[0113] Ensure the accuracy and completeness of blood relationship:

[0114] The field name is matched consistently to ensure the accuracy of blood relationship.

[0115] Perform integrity checks on all data lineage relationships to ensure the integrity of the lineage chain.

[0116] Pre-integration, supplementary calculation of data field data lineage

[0117] Supplement the data lineage between the data fields involved in the calculation and the calculation result fields:

[0118] 1. From the lineage node set, find the predecessor lineage node that matches the calculated data field node.

[0119] 2. Establish the blood relationship between the calculated data field node and the preceding blood relationship node through consistent matching of field names.

[0120] 3. Complete the blood relationship chain between the calculated data field node and the calculated result field node.

[0121] Pre-integration, supplementary data table data lineage

[0122] Supplement the data lineage between the data tables to which the data fields belong:

[0123] From the constructed data field data lineage, identify the data table to which each data field belongs.

[0124] Based on the blood relationship between data fields, the blood relationship between data tables is deduced. For example, if field a in data table A is related to field b in data table B, it is deduced that there is a blood relationship between data table A and data table B.

[0125] 3. Add the derived blood relationship between data tables to the data blood relationship diagram.

[0126] Complete integration of data lineage

[0127] Integrate intermediate nodes

[0128] Find the data lineage containing the intermediate node in the pre-integrated data lineage

[0129] Source node data lineage collection construction: Use a hash map or dictionary structure, with the source node unique number as the key, to construct a source node data lineage collection. The content of the collection is the data lineage relationship that is directly related to the source node.

[0130] 3. Traverse the data lineage relationships including the intermediate nodes and identify the data lineage relationships of all non-intermediate nodes.

[0131] 3-1 Source node judgment: Use conditional judgment statements to determine whether the source node of the data lineage is a non-intermediate node. If the source node is an intermediate node, skip the data lineage relationship.

[0132] 3-2 Link tracing: If the source node is a non-intermediate node, use the depth-first search (DFS) or breadth-first search (BFS) algorithm, starting from the source node, and trace the data lineage relationships of all non-intermediate nodes that can be connected in series with the source node according to the link.

[0133] 3-3 Data lineage construction: Construct the data lineage relationship between the source node and the target node, integrate the intermediate nodes, and form a simplified data lineage path. Use the shortest path algorithm in graph theory (such as Dijkstra algorithm) or dynamic programming method to optimize the data lineage path.

[0134] Example: Assume that the following data relationship exists:

[0135] A->B (intermediate node)

[0136] B->C (middle node)

[0137] C->D

[0138] Through the method of the present invention, the integrated data lineage relationship is:

[0139] A->D

[0140] Integrate indirect nodes

[0141] Find the data lineage that does not contain intermediate nodes in the pre-integrated data lineage

[0142] The unique number of the source node in the data lineage is Key, and the set of lineage nodes that are indirectly related to the source node is Content. Construct a set of all nodes that are not directly connected to the source node.

[0143] 1. Traverse the data lineage relationship and find all nodes directly connected to the source node.

[0144] 2. Recursively obtain the set of all target nodes connected to the source node (including direct or indirect connections)

[0145] 3. Remove directly related bloodline nodes from all related bloodline nodes

[0146] 4. Store the bloodline nodes that are indirectly related to the source node into the collection

[0147] Traverse the data lineage and retain the data lineage without indirect relationship between nodes

[0148] 1. Traverse all data blood relationships and determine whether there is an indirect relationship between the source node and the target node in each blood relationship

[0149] 2. If an indirect relationship is found between the source node and the target node in a blood relationship, remove the blood relationship.

[0150] Example:

[0151] Assume the following data relationship:

[0152] A->B

[0153] B->C

[0154] A->C

[0155] Goal: Delete A->C, because A->C can be derived from A->B and B->C, that is, A->C is an indirect relationship.

[0156] The present invention provides a method for storing and managing data lineage relationships, which realizes efficient data lineage relationship management by defining a clear data model and storage structure. In the specific implementation, two core tables are mainly designed, namely, the data lineage relationship table and the lineage node table. Figure 5 The data lineage table shown is used to record the flow relationship between different nodes of data, and is mainly composed of three fields: source node, target node, and lineage relationship; the source node indicates the starting point of data flow, the target node indicates the end point of data flow, and the lineage relationship describes the relationship between the source node and the target node. As shown in the figure, the lineage node table is mainly composed of the following fields: unique number, node data type, child node data type, data source number, data source name, data source connection IP, data source connection port, data source type, database name, data table name, data table alias, data column name, data column alias, and lineage nodes involved in the calculation field.

[0157] Based on the method in the above example, the embodiment of the present application also provides an ETL automated data lineage processing and analysis device, which is exemplary: Figure 3 A schematic diagram of the structure of an ETL automated data lineage processing and analysis device provided in an embodiment of the present application is shown. The device may include an acquisition module, a parsing module, an integration module, a storage module, and a display module.

[0158] Acquisition module: The main function is to obtain metadata information related to ETL jobs from various data sources. The acquisition methods include actively acquiring data through API interfaces, database connections, etc., or receiving data push from other systems.

[0159] Parsing module: parses the obtained ETL job metadata to extract data lineage information. The parsed information includes data source table, target table, data conversion rules, etc.

[0160] Integration module: Integrate the data lineage information extracted by the analysis module to build a complete data lineage map

[0161] Storage module: persistently stores the integrated data lineage map for subsequent query and analysis.

[0162] Display module: used to update the business data corresponding to the business data according to the business data dependency relationship

[0163] According to the bloodline map, and display the updated business data bloodline map.

[0164] In one of the embodiments, the acquisition module is specifically used to obtain metadata information related to ETL operations from various data sources. These metadata information are the basis for building data lineage relationships, so the efficiency and accuracy of the acquisition module directly affect the performance of the entire device. First, identify and connect the data source, including relational databases, non-relational databases, data warehouses, file systems, data lakes, etc. The system will select a suitable connection method for different data source types. For the acquisition method, active acquisition supports active acquisition of data through API interfaces, database connections, etc. Supports scheduled tasks to regularly pull the latest metadata information from the data source. Supports incremental acquisition, only obtains metadata information that has changed, and improves efficiency. Passive acceptance supports receiving data push from other systems, such as ETL tools, data governance platforms, etc. Supports receiving data push through message queues, Webhooks, etc.

[0165] Each module of the above-mentioned data lineage analysis device can be implemented by software, hardware or a combination thereof. In hardware, for example, hardware accelerators such as application-specific integrated circuits (ASICs) and field-programmable gate arrays (FPGAs) are used to implement computationally intensive tasks such as data acquisition, analysis and integration to improve processing efficiency and performance. In software, each module can also be stored in the form of software in the memory of a computer device, such as a hard disk, solid-state drive or memory. Software modules usually exist in the form of executable files, dynamic link libraries (DLLs) or scripts, and are called by a processor to perform corresponding operations. In practical applications, a combination of software and hardware is usually used to implement each module of a data lineage analysis device. For example, hardware accelerators are used to handle computationally intensive tasks, while software modules are used to achieve flexibility and scalability.

[0166] In an exemplary embodiment, Figure 4A schematic diagram of the structure of a computer device provided by the present embodiment is shown. The computer device includes a processor, a memory, an input / output interface, a communication interface and a bus. Among them, the processor, the memory and the input / output interface are connected through a system bus, and the communication interface is connected to the system bus through the input / output interface. Specifically, the processor of the computer device is mainly used to provide computing and control capabilities to ensure efficient completion of data processing and task scheduling. The memory part includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a related database. The internal memory provides necessary environmental support for the operation of the operating system and the computer program to ensure that there is enough memory space when the program is executed. In addition, the database is used to store information related to the data lineage analysis method. The function of the input / output interface is to enable the processor to exchange information with external devices, such as receiving user input or outputting calculation results. The communication interface is used to communicate with an external terminal through a network connection to support data transmission and service requests. When the computer program is executed by the processor, it can implement a data lineage analysis method to help users understand and manage the transmission relationship between data flows in different systems.

[0167] The processor can be a central processing unit (CPU), a microprocessor (Microprocessor), an application-specific integrated circuit (ASIC), or one or more integrated circuits (IC). The CPU is the most important processor in a computer system, responsible for performing various general computing tasks, and is suitable for scenarios that require general computing capabilities. A microprocessor is a single-chip system that integrates components such as a processor core, memory, and input / output interfaces, and is usually used in embedded systems. ASIC is a customized processor designed for a specific application, usually used to implement a specific function or algorithm. An integrated circuit is a circuit that integrates multiple electronic components on a semiconductor chip, which can implement a variety of complex logical functions and signal processing.

[0168] The memory can be implemented in the form of a read-only memory (ROM), a random access memory (RAM), a static storage device, a dynamic storage device, and the like. A read-only memory is a memory that can only read data, and its stored data is stable and will not be lost due to power failure. A random access memory is a memory that can read and write data randomly, and its content can be modified at any time. It has a fast read and write speed and a short access time. A static storage device refers to a storage device that stores data stably and does not require regular data refresh. Common types include SRAM, Flash Memory, SSD, and the like. A dynamic storage device refers to a storage device that stores data unstable and requires regular data refresh. Common types include DRAM, SDRAM, DDR SDRAM, and the like. The memory can store operating systems and other applications. When the data lineage analysis solution provided in this embodiment is implemented by software or hardware, the relevant program code is saved in the memory and handed over to the processor for call and execution.

[0169] The Input / Output Interface (I / O Interface) is an interface in a computer system that is used to connect external devices to the internal components of the computer. It is responsible for transmitting data and control signals between the computer and external devices. Input devices include keyboards, mice, touchpads, microphones, cameras, scanners, etc.; external devices include monitors, speakers, vibrators, indicator lights, etc.

[0170] A communication interface is an interface in a computer device that is used to connect to a network, external devices, or other computer systems to achieve data communication and resource sharing.

[0171] A bus is a channel used to connect various components in a computer system to achieve data transmission and control signal transmission. Buses can be divided into many types, each with its own specific functions and application scenarios.

[0172] It should be noted that, in the absence of conflict, the embodiments and features in the embodiments of the present application can be combined with each other. The present application will be described in detail below with reference to the accompanying drawings and in combination with the embodiments.

[0173] The above description is only a preferred embodiment of the present application and an explanation of the technical principles used. Those skilled in the art should understand that the scope of the invention involved in the present application is not limited to the technical solution formed by a specific combination of the above technical features, but should also cover other technical solutions formed by any combination of the above technical features or their equivalent features without departing from the inventive concept. For example, the above features are replaced with (but not limited to) technical features with similar functions disclosed in the present application.

Claims

1. A data lineage analysis device for the ETL system, which includes an acquisition module, an analysis module, an integration module, a storage module, and a display module. It is characterized by: The acquisition module is responsible for acquiring metadata information related to ETL jobs from various data sources, including actively acquiring data through API interfaces, database connections, etc., or receiving data push from other systems; The parsing module is used to parse the acquired ETL job metadata to parse out data lineage information, where the parsed information includes a data source table, a target table, and data conversion rules; The integration module is used to integrate the data blood relationship information extracted by the analysis module to construct a complete data blood relationship map; The storage module is used to persistently store the integrated data bloodline map for subsequent query and analysis; The display module is used to update the business data lineage map corresponding to the business data according to the business data dependency relationship, and display the updated business data lineage map.

2. An electronic device for data lineage analysis for an ETL system, wherein the data lineage analysis device for an ETL system in claim 1 is installed in the electronic device for data lineage analysis, and is characterized in that: The data lineage analysis electronic device for the ETL system is composed of a processor, a memory, a bus, an input / output interface, and a communication interface.

3. A data lineage analysis method for an ETL system, the data lineage analysis method for an ETL system being applied to the data lineage analysis device for an ETL system as claimed in claim 1, characterized in that: The data lineage analysis method for the ETL system includes the following steps: Step 1: Automated data lineage analysis based on ETL node configuration: With ETL nodes as the basic processing unit, the system automatically reads and analyzes the configuration information of each node. By deeply analyzing the input and output data flows of the nodes, the system can accurately identify the data transmission path between nodes, thereby building a preliminary data lineage relationship diagram. Step 2: Data lineage integration and storage at the ETL process level: Based on the analysis of a single node, the system further integrates the data lineage relationships of all nodes in the process based on the ETL process. By merging, verifying and optimizing the lineage information of each node, the system generates a complete and consistent process-level data lineage graph. Subsequently, the lineage graph will be stored in the system's local database or distributed storage system for subsequent query and analysis. Step 3. Synchronization of data lineage and integration with metadata management system: To ensure the real-time and consistency of data lineage information, the system will automatically synchronize the integrated process-level data lineage to the metadata management system through close integration with the metadata management system.

4. The data lineage analysis method for the ETL system according to claim 3, characterized in that: The system saves the node configuration information in the ETL process to the local database or distributed storage system. Before each execution of data lineage analysis, the system will automatically determine whether the node configuration information needs to be updated. If it detects that the configuration information has changed, the system will trigger the update mechanism to ensure the accuracy and real-time performance of data lineage analysis.

5. The data lineage analysis method for the ETL system according to claim 3, characterized in that: Parse data lineage based on query SQL, including table-level lineage and field lineage. Data lineage only includes source nodes. Query SQL parses data lineage using the JSqlParser tool class.

6. The data lineage analysis method for the ETL system according to claim 5, characterized in that: To query SQL and parse data, use the JSqlParser tool class. The steps are as follows: Step 1: Parse the data table lineage node information in the SQL statement. The system identifies and extracts all data tables involved in the SQL statement and builds table-level lineage relationships. Step 2: Parse the query data field lineage node list in the SQL statement. The system further parses the query field in the SQL statement, identifies and records the field information involved in the calculation. If the field is nested or complexly calculated, the system will recursively parse and store the relevant information. Step 3: Compare the parsed table-level lineage and field lineage with the data fields in the node configuration. Through the comparison, the system can identify and delete the data lineage of the node that is not output, ensuring the simplicity and accuracy of the lineage relationship. Step 4: Store node data lineage.

7. The data lineage analysis method for the ETL system according to claim 3, characterized in that: The integration of raw data lineage is mainly divided into the following steps: S1. Get the process node list: extract all nodes from the data processing process and sort them in the order of node execution. This step ensures that the tracking order of data lineage is consistent with the actual data processing order; S2. Obtaining the original data lineage node by node: Obtain the automatically parsed original data lineage of each node one by one according to the sorted node order. These lineage information are stored in an original data lineage set for subsequent integration processing; S3, pre-integration of raw data lineage, before complete integration, pre-processing the raw data lineage set, this step includes deduplication, merging similar lineage paths, and preliminary data lineage relationship optimization to improve the efficiency and accuracy of subsequent integration; S4. Completely integrate data lineage. Based on pre-integration, complete data lineage integration is performed. This step involves complex algorithms and logic to ensure that the data lineage relationships of all nodes are correctly connected and represented. The integrated data lineage can clearly show the flow and changes of data in the processing process. S5. Store the integrated complete data lineage in a persistent storage system.

8. The data lineage analysis method for the ETL system according to claim 3, characterized in that: The pre-integration of data lineage includes the following steps: Step 1: Pre-integration, supplement the data lineage of the data field, and identify the data lineage type: Data lineage that only contains source nodes: This type of data lineage comes directly from the data source and has no preceding lineage relationship; Data lineage that only contains the target node: This type of data lineage is the final output data node and has a preceding lineage relationship; Data lineage that includes both source nodes and target nodes: This type of data lineage includes both the data source and the final output node, and a complete lineage chain needs to be established; Step 2: Process different types of data lineage: For data lineage that only contains source nodes: directly add its lineage nodes to the lineage node set without any other processing; For data lineage that only contains target nodes: search for the predecessor lineage node with the same field name as the target node from the lineage node set, and establish the lineage relationship between the target node and the predecessor lineage node through consistent matching of field names. For data lineage that contains both source nodes and target nodes: search for the predecessor lineage node with the same field name as the source node from the lineage node set, and establish the lineage relationship between the source node and the predecessor lineage node through consistent matching of field names. Complete the lineage chain between the source node and the target node to ensure the accuracy and completeness of the lineage relationship: use consistent matching of field names to ensure the accuracy of the lineage relationship, perform integrity checks on all data lineage relationships, and ensure the integrity of the lineage chain.

Images