A real-time data lineage analysis method and system based on a spark logical execution plan

Abstract

The application discloses a kind of real-time data blood relationship analysis method and system based on Spark logic execution plan, method includes:SQL capture and message transmission, from multiple computing engines real-time capture executing SQL statement, and captured SQL statement and its source engine information are sent as message to message queue;SQL syntax conversion, receive and convert SQL statement from message queue;SparkSession construction and logic execution plan generation, build and configure SparkSession in parsing service;With the built SparkSession, converted SQL statement is parsed into logic execution plan;Blood relationship analysis, the complete blood relationship data is obtained by traversing and analyzing the generated logic execution plan;Blood data storage.Through high decoupling with specific computing engine, it is unnecessary to install parsing plug-in in these engines or modify its configuration, avoid the stability hidden danger possibly introduced due to system change, reduce compatibility problem caused by specific engine internal mechanism.

Description

Technical Field

[0001] This invention belongs to the field of big data processing and data management technology, and relates to a real-time data lineage analysis method and system based on Spark logical execution plans. Background Technology

[0002] With the rapid development of big data technology, data warehouses (DW) have become a core component of enterprise data management and analysis. In a complex data warehouse environment, data is organized in multiple hierarchical structures, such as source layers, historical layers, model layers, and data mart layers. These layers are interconnected through a series of data processing and transformation processes. While this multi-layered, multi-source data structure improves the flexibility and scalability of data management, it also brings complexity to tracing data lineage.

[0003] Data lineage refers to all the transformations and dependencies that data undergoes throughout its lifecycle, including relationships between tables and fields. Accurate and real-time acquisition of this lineage information is crucial for scenarios such as data profiling, change impact analysis, data quality monitoring, and data governance. However, in current data warehousing practices, tracing and managing data lineage still faces many challenges.

[0004] First, traditional data lineage tracing methods often rely on manually parsing SQL scripts or query statements. This approach identifies dependencies between tables and fields by constructing and traversing an Abstract Syntax Tree (AST). While theoretically this method can cover multiple computing engines, it faces several limitations in practical applications: differences in SQL dialects between different computing engines lead to poor parsing compatibility; parsing complex SQL or special syntax (such as the WITH clause in Spark SQL) is difficult; and the parsing results may be inaccurate or miss important information.

[0005] Secondly, another common approach is to integrate lineage tracking plugins into the computing engine, such as LineageLogger in Apache Hive. This method captures lineage information in real time during SQL execution, offering high accuracy and real-time performance. However, this method also has significant limitations: it is highly dependent on a specific computing engine, requiring additional development work for non-Hive environments (such as Spark, Impala, Trino, etc.), which is also more difficult. Furthermore, plugin integration typically requires modifications to environment configurations, increasing system complexity and maintenance costs.

[0006] In summary, existing technologies have many shortcomings in data lineage tracking, failing to comprehensively and accurately cover multiple computing engines and struggling to adapt to complex and ever-changing data warehouse environments. Therefore, a new technical solution is urgently needed to overcome the shortcomings of existing technologies and achieve cross-engine, comprehensive, accurate, and real-time data lineage tracking. Summary of the Invention

[0007] The purpose of this invention is to solve the problem that existing technologies cannot comprehensively and accurately cover multiple computing engines in data lineage tracing and are difficult to adapt to complex and ever-changing data warehouse environments. This invention provides a real-time data lineage analysis method and system based on Spark logical execution plans.

[0008] To achieve the above objectives, the present invention employs the following technical solution: A real-time data lineage analysis method based on Spark logical execution plans includes the following steps: SQL capture and message passing: Captures executing SQL statements from multiple computing engines in real time, and sends the captured SQL statements and their source engine information as messages to the message queue; SQL syntax conversion: Receives and converts SQL statements from the message queue; SparkSession construction and logical execution plan generation: In the parsing service, SparkSession is built and configured, set to Local mode, and Hive support is enabled. Hive metadata is associated by configuring the metadata storage address. The constructed SparkSession is used to parse the transformed SQL statement into a logical execution plan. Lineage analysis involves traversing and parsing the generated logical execution plan. By recursively traversing specific types of nodes in the logical execution plan, it identifies and extracts the relationships between tables and fields in the SQL statement to obtain complete lineage data. Bloodline data storage: Save the parsed bloodline data to the graph database.

[0009] The computing engines include Spark, Hive, Impala, and Trino.

[0010] When receiving an SQL statement from the message queue, the source engine of the SQL statement is determined. If the SQL statement does not come from the Spark engine, an SQL conversion tool or regular expression is used to convert the SQL statement into syntax compatible with Spark SQL.

[0011] The SparkSession construction and logic execution plan generation are as follows: Create a SparkSession instance that runs in local mode and configure its application name as SparkLineageAnalyser; at the same time, specify the connection address for Hive metadata to enable support for Hive, allowing SparkSession to access Hive metadata when generating logical execution plans; Get SessionState: Retrieve the SessionState object from the constructed SparkSession. The SessionState object is responsible for managing all the states of the Spark SQL session. Parse SQL statements and generate logical execution plans. Use the SQL parser in SessionState to parse the input SQL statements and generate an abstract syntax tree. Combine Hive metadata to perform metadata binding, mapping table names and column name identifiers in the SQL statements to specific metadata objects. Finally, based on the parsing and binding results, a logical execution plan for the SQL statement is generated.

[0012] The blood relationship analysis specifically includes: Initialize the parsing environment and set up parsing tools and libraries; Load the logical execution plan, submit the SQL statement to Spark SQL, and obtain the generated logical execution plan, which represents the logical representation of the SQL query; Traverse the logical execution plan tree using a recursive traversal method; during the traversal, identify the data processing steps based on the type of the node.

[0013] Identify relationships between tables; during the traversal process, identify the associations between tables based on the join nodes. Extract information about the join nodes, including the table names and join conditions, to determine the lineage between the source and target tables; Identify the relationships between fields, traverse projections and select nodes; extract the source and destination information of fields, determine the value relationships, filtering relationships and calculation or transformation logic between fields; For join nodes, analyze the join key field to determine the relationships between cross-table fields.

[0014] The step of saving the parsed bloodline data to a graph database specifically involves using a graph data structure to store the extracted bloodline data, including an adjacency list, an adjacency matrix, or a relational database.

[0015] In the graph data structure, nodes represent tables or fields, edges represent relationships between tables or fields, and the weights or attributes of edges contain detailed information about the relationships.

[0016] A real-time data lineage analysis system based on Spark logical execution plans includes the following modules: The SQL capture and message passing module is used to capture the SQL statements being executed from multiple computing engines in real time, and send the captured SQL statements and their source engine information as messages to the message queue. The SQL syntax conversion module is used to receive and convert SQL statements from the message queue. The SparkSession building and logical execution plan generation module is used to build and configure SparkSession in the parsing service, set it to Local mode, enable Hive support, and associate Hive metadata by configuring the metadata storage address; using the built SparkSession, the transformed SQL statement is parsed into a logical execution plan. The lineage relationship parsing module is used to traverse and parse the generated logical execution plan. By recursively traversing specific type nodes in the logical execution plan, it identifies and extracts the inter-table relationships and inter-field relationships in the SQL statement to obtain complete lineage relationship data. A bloodline data storage module is used to save the parsed bloodline relationship data to a graph database.

[0017] An apparatus includes a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein the processor, when executing the computer program, performs the steps of the method as described in any of the preceding items.

[0018] A computer-readable storage medium storing a computer program that, when executed by a processor, implements the steps of the method as described in any of the preceding claims.

[0019] Compared with the prior art, the present invention has the following beneficial effects: The real-time data lineage analysis method based on Spark logical execution plans in this invention, through high decoupling from specific computing engines, eliminates the need to install parsing plugins or modify their configurations within these engines. This avoids potential stability issues arising from system changes and reduces compatibility problems caused by reliance on specific engine internal mechanisms. It eliminates the need to modify existing computing engine configurations, making system deployment and maintenance more convenient, while minimizing the impact on existing production environments and ensuring business continuity. Furthermore, the highly decoupled design facilitates subsequent upgrades and iterations of the parsing logic, as upgrades primarily focus on the system's parsing module without involving modifications to the underlying computing engine.

[0020] Furthermore, by uniformly converting the captured SQL statements into Spark SQL syntax, broad compatibility with different computing engines is achieved, ensuring that the system can accurately parse and process the SQL statements regardless of which computing engine they originally came from. Since the SQL statements are obtained directly from the computing engine, there is no need to pay attention to the SQL submission entry and method, thereby avoiding the SQL omission problem caused by monitoring blind spots and ensuring the comprehensiveness of data lineage analysis.

[0021] The logical execution plan in this method is generated by the Spark engine, adhering to a unified specification to ensure a consistent format and accurate content. This accuracy forms the basis for subsequent lineage resolution. The logical execution plan includes all metadata information of the tables used, providing rich data support for precise lineage resolution. This rich metadata allows the system to gain a deeper understanding of the data structure and data flow behind the SQL statements. Furthermore, it eliminates the differences caused by the special syntax of different computing engines. This approach simplifies the parsing logic and improves the system's versatility and stability. Attached Figure Description

[0022] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation on the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0023] Figure 1 This is a flowchart of the real-time data lineage analysis method based on Spark logical execution plan of the present invention. Detailed Implementation

[0024] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, 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. The components of the embodiments of the present invention described and shown in the accompanying drawings can generally be arranged and designed in various different configurations.

[0025] Therefore, the following detailed description of the embodiments of the invention provided in the accompanying drawings is not intended to limit the scope of the claimed invention, but merely to illustrate selected embodiments of the invention. All other embodiments obtained by those skilled in the art based on the embodiments of the invention without inventive effort are within the scope of protection of the invention.

[0026] It should be noted that similar labels and letters in the following figures indicate similar items. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures.

[0027] The present invention will now be described in further detail with reference to the accompanying drawings: See Figure 1 The real-time data lineage analysis method based on Spark logical execution plans in this invention specifically includes the following steps: S1, SQL capture and message passing, captures executing SQL statements in real time from multiple computing engines and sends the captured SQL statements and their source engine information as messages to the message queue; computing engines include but are not limited to Spark, Hive, Impala, Trino, etc. The SQL statements of each computing engine are identified by their source engine before being sent for subsequent processing.

[0028] S2, SQL syntax transformation, receives and transforms SQL statements from the message queue; it determines the source engine, and if the SQL statement does not come from the Spark engine, it uses SQL transformation tools (such as sqlglot) or regular expressions to convert the SQL statement into syntax compatible with Spark SQL to ensure that a unified logical execution plan can be generated subsequently.

[0029] S3, SparkSession construction and logical execution plan generation, builds and configures SparkSession in the parsing service, sets it to Local mode, enables Hive support, and associates Hive metadata by configuring the metadata storage address; using the built SparkSession, the transformed SQL statement is parsed into a logical execution plan.

[0030] S3.1, build SparkSession, create a SparkSession instance running in local mode, and configure its application name as SparkLineageAnalyser; at the same time, specify the connection address for Hive metadata to enable support for Hive, allowing SparkSession to access Hive metadata when generating logical execution plans.

[0031] S3.2, Get SessionState: Retrieve the SessionState object from the constructed SparkSession. The SessionState object is responsible for managing all states of the Spark SQL session.

[0032] S3.3 parses SQL statements and generates logical execution plans. It uses the SQL parser in SessionState to parse the input SQL statements and generate an abstract syntax tree. It also combines Hive metadata to perform metadata binding, mapping table names and column name identifiers in the SQL statements to specific metadata objects.

[0033] S3.4 Finally, based on the results of parsing and binding, a logical execution plan for the SQL statement is generated.

[0034] S4, lineage relationship parsing, traverses and parses the generated logical execution plan, and identifies and extracts the inter-table and inter-field relationships in the SQL statement by recursively traversing specific type nodes in the logical execution plan to obtain complete lineage relationship data; S4.1 Initialize the parsing environment and set up parsing tools and libraries.

[0035] S4.2 loads the logical execution plan, submits the SQL statement to Spark SQL, and obtains the generated logical execution plan, which represents the logical representation of the SQL query.

[0036] S4.3 Traverse the logical execution plan tree using a recursive traversal method; during the traversal, identify the data processing steps based on the type of the node.

[0037] S4.4 Identify relationships between tables. During the traversal, identify the relationships between tables based on the connection nodes.

[0038] S4.5 Extract information about the join nodes, including the table names and join conditions, to determine the lineage between the source and target tables.

[0039] S4.6 Identify the relationships between fields, traverse projections and select nodes; extract the source and destination information of fields, determine the value relationships, filtering relationships and calculation or transformation logic between fields.

[0040] S4.7 For join nodes, analyze the join key field to determine the relationships between cross-table fields.

[0041] S5, Bloodline Data Storage, saves the parsed bloodline data to the graph database.

[0042] Graph databases can effectively represent and store complex network data structures, facilitating the understanding and analysis of lineage relationships between data. Graph data structures, including adjacency lists, adjacency matrices, or specialized graph databases, are used to store extracted lineage data. Nodes in a graph can represent tables or fields, edges can represent relationships between tables or fields, and edge weights or attributes can contain detailed relationship information, such as connection conditions and transformation logic.

[0043] One embodiment of the present invention is a real-time data lineage analysis system based on Spark logical execution plans, comprising the following modules: The SQL capture and message passing module is used to capture executing SQL statements from multiple computing engines in real time, and send the captured SQL statements and their source engine information as messages to a message queue. The message queue is used to temporarily store and forward these SQL statement messages to ensure the real-time nature and order of the data.

[0044] The SQL syntax conversion module receives and converts SQL statements from the message queue, ensuring that all SQL statements are compatible with the subsequent Spark SQL processing engine.

[0045] The SparkSession building and logical execution plan generation module is used to build and configure SparkSession in the parsing service, set it to Local mode, enable Hive support, and associate Hive metadata by configuring the metadata storage address; using the built SparkSession, the transformed SQL statement is parsed into a logical execution plan.

[0046] The lineage relationship parsing module is used to traverse and parse the generated logical execution plan. By recursively traversing specific type nodes in the logical execution plan, it identifies and extracts the inter-table relationships and inter-field relationships in the SQL statement to obtain complete lineage relationship data.

[0047] A bloodline data storage module is included to save the parsed bloodline relationship data to a graph database. Through the graph database, users can easily query, visualize, and further analyze bloodline relationships.

[0048] The system in this invention ensures the real-time performance and accuracy of data lineage analysis by capturing SQL statements in real time, rapidly generating logical execution plans, and accurately resolving lineage relationships. Simultaneously, through the use of SparkSession and message queues in Local mode, the system maintains high performance while reducing its impact on the production environment.

[0049] One embodiment of the present invention provides a terminal device. This terminal device includes a processor, a memory, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, it implements the steps in the various method embodiments described above. Alternatively, when the processor executes the computer program, it implements the functions of each module / unit in the various device embodiments described above.

[0050] The computer program can be divided into one or more modules / units, which are stored in the memory and executed by the processor to complete the present invention.

[0051] The device / terminal equipment may be a desktop computer, laptop, handheld computer, cloud server, or other computing device. The device / terminal equipment may include, but is not limited to, a processor and a memory.

[0052] The processor may be a central processing unit (CPU), or other general-purpose processors, digital signal processors (DSPs), application-specific integrated circuits (ASICs), field-programmable gate arrays (FPGAs), or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc.

[0053] The memory can be used to store the computer program and / or module. The processor implements various functions of the device / terminal equipment by running or executing the computer program and / or module stored in the memory and calling the data stored in the memory.

[0054] If the modules / units integrated in the device / terminal equipment are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. Based on this understanding, all or part of the processes in the methods of the above embodiments can also be implemented by a computer program instructing related hardware. The computer program can be stored in a computer-readable storage medium, and when executed by a processor, it can implement the steps of the various method embodiments described above. The computer program includes computer program code, which can be in the form of source code, object code, executable files, or certain intermediate forms. The computer-readable medium can include: any entity or device capable of carrying the computer program code, recording media, USB flash drives, portable hard drives, magnetic disks, optical disks, computer memory, read-only memory (ROM), random access memory (RAM), electrical carrier signals, telecommunication signals, and software distribution media, etc. It should be noted that the content included in the computer-readable medium can be appropriately added or removed according to the requirements of legislation and patent practice in the jurisdiction. For example, in some jurisdictions, according to legislation and patent practice, computer-readable media do not include electrical carrier signals and telecommunication signals.

[0055] The above are merely preferred embodiments of the present invention and are not intended to limit the present invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.

Claims

1. A real-time data lineage analysis method based on Spark logical execution plans, characterized in that, Includes the following steps: SQL capture and message passing: Captures executing SQL statements from multiple computing engines in real time, and sends the captured SQL statements and their source engine information as messages to the message queue; SQL syntax conversion: Receives and converts SQL statements from the message queue; SparkSession construction and logical execution plan generation: SparkSession is built and configured in the parsing service, set to Local mode, and Hive support is enabled. Hive metadata is associated by configuring the metadata storage address. The constructed SparkSession is then used to parse the transformed SQL statements into logical execution plans. Specifically, SparkSession construction and logical execution plan generation involve: Create a SparkSession instance that runs in local mode and configure its application name as SparkLineageAnalyser; at the same time, specify the connection address for Hive metadata to enable support for Hive, allowing SparkSession to access Hive metadata when generating logical execution plans; Get SessionState: Retrieve the SessionState object from the constructed SparkSession. The SessionState object is responsible for managing all the states of the Spark SQL session. Parse the SQL statement and generate a logical execution plan. Use the SQL parser in SessionState to parse the input SQL statement and generate an abstract syntax tree. Metadata binding is performed by combining Hive metadata, mapping table names and column name identifiers in SQL statements to specific metadata objects; Finally, based on the parsing and binding results, a logical execution plan for the SQL statement is generated; Lineage analysis involves traversing and parsing the generated logical execution plan. By recursively traversing specific types of nodes in the logical execution plan, it identifies and extracts the relationships between tables and fields in the SQL statement to obtain complete lineage data. The blood relationship analysis specifically includes: Initialize the parsing environment and set up parsing tools and libraries; Load the logical execution plan, submit the SQL statement to Spark SQL, and obtain the generated logical execution plan, which represents the logical representation of the SQL query; Traverse the logical execution plan tree using a recursive traversal method; during the traversal, identify the data processing steps based on the type of the node; Identify relationships between tables; during the traversal process, identify the associations between tables based on the join nodes. Extract information about the join nodes, including the table names and join conditions, to determine the lineage between the source and target tables; Identify the relationships between fields, traverse projections and select nodes; extract the source and destination information of fields, determine the value relationships, filtering relationships and calculation or transformation logic between fields; For join nodes, analyze the join key field to determine the relationships between cross-table fields; Bloodline data storage: Save the parsed bloodline relationship data to the graph database.

2. The real-time data lineage analysis method based on Spark logical execution plans as described in claim 1, characterized in that, The computing engines include Spark, Hive, Impala, and Trino.

3. The real-time data lineage analysis method based on Spark logical execution plans as described in claim 1, characterized in that, When receiving an SQL statement from the message queue, the source engine of the SQL statement is determined. If the SQL statement does not come from the Spark engine, an SQL conversion tool or regular expression is used to convert the SQL statement into syntax compatible with Spark SQL.

4. The real-time data lineage analysis method based on Spark logical execution plans as described in claim 1, characterized in that, The step of saving the parsed bloodline data to a graph database specifically involves using a graph data structure to store the extracted bloodline data, including an adjacency list, an adjacency matrix, or a relational database.

5. The real-time data lineage analysis method based on Spark logical execution plans as described in claim 4, characterized in that, In the graph data structure, nodes represent tables or fields, edges represent relationships between tables or fields, and the weights or attributes of edges contain detailed information about the relationships.

6. A real-time data lineage analysis system based on Spark logical execution plans, employing the real-time data lineage analysis method based on Spark logical execution plans as described in any one of claims 1-5, characterized in that, Includes the following modules: The SQL capture and message passing module is used to capture the SQL statements being executed from multiple computing engines in real time, and send the captured SQL statements and their source engine information as messages to the message queue. The SQL syntax conversion module is used to receive and convert SQL statements from the message queue. The SparkSession building and logic execution plan generation module is used to build and configure SparkSession in the parsing service, set it to Local mode, enable Hive support, and associate Hive metadata by configuring the metadata storage address. Using the constructed SparkSession, the transformed SQL statement is parsed into a logical execution plan; The lineage relationship parsing module is used to traverse and parse the generated logical execution plan. By recursively traversing specific type nodes in the logical execution plan, it identifies and extracts the inter-table relationships and inter-field relationships in the SQL statement to obtain complete lineage relationship data. A bloodline data storage module is used to save the parsed bloodline relationship data to a graph database.

7. An apparatus comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the steps of the method as described in any one of claims 1-5.

8. A computer-readable storage medium storing a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the method as described in any one of claims 1-5.

Images