A sql-based data blood relationship graph analysis method and device

Abstract

The application provides a SQL-based data blood relationship graph analysis method and device. The method comprises the following steps: performing standardization processing on a SQL statement; performing lexical and syntactic analysis on the SQL statement and generating an abstract syntax tree; traversing the abstract syntax tree to obtain a data element set and an association relationship; determining whether there is a dependency between data elements based on the association relationship; calculating the dependency strength between the data elements with the dependency based on the association relationship; constructing a data blood relationship graph, in which the data elements are graph nodes, the node attributes are database names, table names or field names, and there is an edge between two data elements with the dependency, and the edge attributes include the dependency strength between the data elements connected by the edge; and visually displaying the data blood relationship graph. Through the data blood relationship graph and the dependency strength between the data elements as the edge attributes, the blood relationship between the data can be quickly queried, and the data management efficiency and quality are improved.

Description

Technical Field

[0001] This invention relates to the field of data processing technology, and in particular to a method and apparatus for data lineage graph analysis based on SQL. Background Technology

[0002] As enterprises become increasingly information-driven, data has become a crucial foundation for business operations and decision-making. Large amounts of data are stored in various databases and processed using languages ​​such as SQL. However, with increasing business complexity, data processing workflows are becoming longer and more intricate, making the relationships between data difficult to understand and trace.

[0003] The importance of data lineage is becoming increasingly apparent. Data lineage refers to the flow and changes of data from its source to its destination, helping to understand the origin, destination, and processing of data. This is crucial for data governance, data quality management, and data security. For example, when data issues arise, data lineage can quickly pinpoint the source, reducing troubleshooting time. Furthermore, it allows for impact analysis, assessing the impact of data changes on other systems or business operations.

[0004] Data lineage analysis primarily aims to clarify the relationships between data points. However, existing methods for clarifying these relationships include:

[0005] (1) Based on relational databases, such as MySQL, governance can only be carried out in stages and nodes. Each stage and node corresponds to a set of two-dimensional relational tables. When querying the link relationship, it is necessary to perform recursive queries based on the data chain. The complexity of horizontal queries is determined by the length of the data chain, while vertical queries are time-consuming, inefficient, and slow to respond.

[0006] (2) Based on non-relational databases, such as JSON data, each nested layer corresponds to a stage. When updating data for each stage or node attribute, the source relationship must be updated, and horizontal data analysis cannot be supported.

[0007] (3) Based on the hybrid use of relational and non-relational databases, the complexity of system design increases exponentially. Data engineers and business backbones need to put a lot of effort into maintaining this part of the data relationship, and the accuracy and consistency of the data are still difficult to guarantee.

[0008] Furthermore, the aforementioned data lineage analysis methods often rely on the analysis of SQL code, which is not only time-consuming and error-prone, but also fails to intuitively demonstrate the relationships between data. Moreover, with the development of big data and cloud computing technologies, data storage and processing methods are constantly evolving, making traditional analysis methods inadequate to meet new demands. Summary of the Invention

[0009] The present invention aims to at least solve the technical problems existing in the prior art, and provide a data lineage graph analysis method and device based on SQL, so as to intuitively reflect the dependencies between data and improve the efficiency and quality of data governance.

[0010] To achieve the above-mentioned objectives of the present invention, according to a first aspect of the present invention, the present invention provides a data lineage graph analysis method based on SQL, comprising: collecting multiple SQL statements and standardizing the SQL statements; performing lexical and syntactic analysis on the standardized SQL statements and generating an abstract syntax tree; traversing the abstract syntax tree to obtain a set of data elements and the relationships between the data elements, wherein the set of data elements includes databases, tables, and fields; determining whether there is a dependency between the data elements based on the relationships between the data elements; calculating the dependency strength between the dependent data elements based on the relationships between the data elements; constructing a data lineage graph, wherein the data elements are graph nodes, the node attributes are database name, table name, or field name, and an edge connects two dependent data elements, the attributes of which include the dependency strength between the data elements connected at both ends of the edge; and visually displaying the data lineage graph.

[0011] To achieve the above-mentioned objectives of the present invention, according to a second aspect of the present invention, the present invention provides a data lineage graph analysis device based on SQL, used to implement the data lineage graph analysis method based on SQL described in the first aspect of the present invention, comprising: a standardization module, which collects multiple SQL statements and performs standardization processing on the SQL statements; an abstract syntax tree generation module, which performs lexical and syntactic analysis on the standardized SQL statements and generates an abstract syntax tree; a data element set and association relationship acquisition module, which traverses the abstract syntax tree to obtain a data element set and the association relationships between data elements, wherein the data element set includes databases, tables, and fields; a dependency judgment module, which determines whether there is a dependency between data elements based on the association relationships between data elements; a dependency strength calculation module, which calculates the dependency strength between data elements that have a dependency through the association relationships between data elements; a lineage graph construction module, which constructs a data lineage graph, wherein data elements are graph nodes in the data lineage graph, and the node attributes are database name, table name, or field name, and two data elements that have a dependency are connected by an edge, the attributes of which include the dependency strength between the data elements connected at both ends of the edge; and a visualization module, which visualizes the data lineage graph.

[0012] To achieve the above-mentioned objectives of the present invention, according to a third aspect of the present invention, the present invention provides a computer program product, including a computer program / instructions, characterized in that, when the computer program / instructions are executed by a processor, they implement the steps of the SQL-based data lineage graph analysis method described in the first aspect of the present invention.

[0013] To achieve the above-mentioned objectives of the present invention, according to a fourth aspect of the present invention, an electronic device is provided, the electronic device comprising: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to execute the SQL-based data lineage graph analysis method described in the first aspect of the present invention.

[0014] This invention performs lexical and syntactic analysis on standardized SQL statements to generate an abstract syntax tree. By traversing the abstract syntax tree, it obtains data element sets and relationships. Based on these data element sets and relationships, it constructs a data lineage graph. This graph visually displays the relationships between data elements, facilitating rapid retrieval of metadata lineage relationships and improving the efficiency and quality of data governance. Furthermore, the edge attributes in the data lineage graph include the dependency strength between data elements. This dependency strength helps data engineers identify and optimize bottlenecks in data flows. When a data source or intermediate result changes, the fault node can be quickly located. For queries with high dependency strength, performance tuning can be prioritized to reduce overall query response time. It also clearly displays the source, changes, and flow of data, ensuring data accuracy and compliance. Attached Figure Description

[0015] Figure 1 This is a flowchart illustrating a preferred embodiment of the data lineage graph analysis method based on SQL according to the present invention.

[0016] Figure 2 This is a flowchart illustrating the implementation of a SQL-based data lineage graph analysis method in another preferred embodiment of the present invention.

[0017] Figure 3 This is a hardware structure block diagram of an electronic device in a preferred embodiment of the present invention. Detailed Implementation

[0018] Embodiments of the present invention are described in detail below. Examples of these embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout. The embodiments described below with reference to the accompanying drawings are exemplary and are only used to explain the present invention, and should not be construed as limiting the present invention.

[0019] In the description of this invention, it should be understood that the terms "longitudinal", "lateral", "up", "down", "front", "rear", "left", "right", "vertical", "horizontal", "top", "bottom", "inner", "outer", etc., indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings. They are only for the convenience of describing this invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation. Therefore, they should not be construed as limitations on this invention.

[0020] In the description of this invention, unless otherwise specified and limited, it should be noted that the terms "installation", "connection" and "linking" should be interpreted broadly. For example, they can refer to mechanical or electrical connections, or internal connections between two components. They can be direct connections or indirect connections through an intermediate medium. Those skilled in the art can understand the specific meaning of the above terms according to the specific circumstances.

[0021] This invention discloses a data lineage graph analysis method based on SQL. In a preferred embodiment, as shown in the figure, it includes:

[0022] Step S1: Collect multiple SQL statements and standardize them.

[0023] In this implementation, SQL statements are used to query and manipulate relational databases. Multiple relational databases can be configured. Since different database systems may use different SQL dialects or syntaxes, the collected SQL statements need to be standardized for subsequent extraction of data element sets.

[0024] To facilitate SQL statement processing across various database systems, the standardized SQL statement processing procedure preferably includes:

[0025] Step S11: Perform standard template matching on the SQL statement to obtain matching templates. Standard templates include public templates and dialect-specific templates.

[0026] Reference Figure 2As shown, a public template library and multiple dialect-specific template libraries are pre-built. Each template library includes templates corresponding to various operation types, conversion functions, and join conditions of SQL statements, with the templates being regular expressions. The multiple dialect-specific template libraries are not limited to, but include, a MySQL dialect-specific template library, a SQL dialect-specific template library, and a Hive dialect-specific template library.

[0027] The preferred, but not limited to, matching process is as follows: First, match the SQL statement to a public template library or a dialect-specific personalized template library based on the syntax and / or function characteristics used in the SQL statement. Then, select the corresponding template as the matching template based on the operators, function fields, etc. in the SQL statement.

[0028] Step S12: Trim and replace redundant fields in the SQL statement based on the matching template.

[0029] The SQL statement is processed using regular expressions that match the template, including trimming and replacement, to remove redundant fields. Redundant fields refer to fields in the SQL statement other than operators, function names, join conditions, and metadata (database name, table name, field name, data type, etc.). In one example, the standardized SQL statement is represented as:

[0030] INSERT INTO target_db.target_table(.*?)SELECT…FROM source_db.source_table.

[0031] Step S2 involves performing lexical and syntactic analysis on the standardized SQL statement and generating an abstract syntax tree.

[0032] In this embodiment, it is preferred, but not limited to, using Calcite parser.jj, an open-source lexical and syntax parser code generator based on JavaCC, to parse the SQL statement and generate an abstract syntax tree based on the parsing results.

[0033] In one example, the standardized SQL statement is:

[0034] insert into rjsn_db.sr_rds_limit_cnt(sr_rda,sr_rdb,sr_rdc)

[0035] select rs_ard,rs_brd / rs_cnt,rs_crd*rs_prc from xxjn_db.jr_ass_pro_cnt

[0036] The results of Calcite parser.jj parsing are:

[0037] [{"from_db":"xxjn_db","from_tb":"jr_ass_pro_cnt","from_fields":["rs_ard"],

[0038] "to_db":"rjsn_db","to_tb":"sr_rds_limit_cnt","to_fields":["sr_rda"]},

[0039] {"from_db":"xxjn_db","from_tb":"jr_ass_pro_cnt","from_fields":["rs_brd","rs_cnt"],

[0040] "to_db":"rjsn_db","to_tb":"sr_rds_limit_cnt","to_fields":["sr_rdb"]},{"from_db":"xxjn_db","from_tb":"jr_ass_pro_cnt","from_fields":["rs_crd","rs_prc"],

[0041] "to_db":"rjsn_db","to_tb":"sr_rds_limit_cnt","to_fields":["sr_rdc"]}]

[0042] Step S3: Traverse the abstract syntax tree to obtain the data element set and the relationships between the data elements. The data element set includes the database, tables, and fields.

[0043] In this embodiment, a depth-first search or breadth-first search algorithm is preferably used to traverse the abstract syntax tree to obtain data elements and the relationships between them. The relationships between data elements include the transformation process between data elements, as well as the transformation functions, join conditions, and SQL operation types within that transformation process.

[0044] Step S4: Determine whether there is a dependency between data elements based on the relationships between them. Specifically, if there is no transformation process between data elements (i.e., no transformation function, join condition, or SQL operation type), then it is considered that there is no transformation process between them, the two data elements have no dependency relationship, and there is no connecting edge between them in the data lineage graph constructed later. If there is a transformation process between data elements (i.e., there is at least one transformation function, at least one join condition, or at least one SQL operation type), then it is considered that there is a transformation process between them, the two data elements have a dependency relationship, and there is a connecting edge between them in the data lineage graph constructed later.

[0045] Step S5: Calculate the dependency strength between data elements that have dependencies based on the association relationships between data elements.

[0046] Step S6: Construct a data lineage graph. In the data lineage graph, data elements are graph nodes. The node attributes are database name, table name, or field name. Two data elements that have a dependency are connected by an edge. The attributes of the edge include the dependency strength between the data elements connected at both ends of the edge.

[0047] In this embodiment, it is preferred, but not limited to, storing nodes, node attributes, edges, and edge attributes in a graph database NEO4j to construct a data lineage graph, thereby discovering potential associations and dependencies between data.

[0048] In this embodiment, to more intuitively illustrate the data flow and transformation process, preferably, the edges of the data lineage graph are directed edges, with the direction of the edges pointing from the dependent data element to the dependent data element, that is, the dependent data element is obtained by calculation, operation or connection based on the dependent data element.

[0049] Step S7: Visualize and display the kinship chart of the data.

[0050] In this embodiment, the constructed data lineage graph is preferably, but not limited to, displayed in chart form using visualization libraries such as D3.js or ECharts, including information such as nodes, edges, and attributes. Specifically, the data lineage graph supports operations such as zooming, dragging, drill-down, and clicking. Figure 2 As shown, it also supports multi-condition filtering based on nodes, node attributes, edges, and edge attributes, so that users can better understand and analyze the dependencies between data.

[0051] In a preferred embodiment, for relatively simple SQL statements, the SQL parser can quickly obtain the lexical and syntactic parsing results. However, for complex SQL statements, the accuracy and speed of the SQL parser's lexical and syntactic parsing decrease significantly. Therefore, to improve the parsing speed and accuracy, such as... Figure 2 As shown, in step S2, lexical and syntactic analysis is performed on the standardized SQL statement to generate an abstract syntax tree, including:

[0052] The process involves obtaining the complexity score of the standardized SQL statement. If the complexity score is less than a preset threshold, the parser Calciteparser.jj is used to perform lexical and syntactic analysis on the SQL statement to obtain the parsing results. If the complexity score is greater than or equal to the preset threshold, a pre-trained natural language model is used to segment and tag the SQL statement with words. Based on the segmentation and tagging results, a rule-based or machine learning-based SQL parser (such as ANTLR or SqlParser) is used to transform the complex SQL statement into an abstract syntax tree. Key nodes in the abstract syntax tree, such as SELECT clauses, FROM clauses, and JOIN conditions, are extracted as the basis for subsequent analysis. Then, a semantic role labeling tool is used to label the key nodes of the abstract syntax tree, identifying the semantic relationships between various elements (vocabularies, phrases, and clauses that constitute the SQL statement). Through the labeling results, the role of each field or expression (such as subject, object, modifier, etc.) and the dependencies between them can be clarified. Finally, based on the semantic role labeling results, the data element set and relationships in the SQL statement are extracted, such as the table name, field name, data type, function, join relationship, operation type, conversion function, etc.

[0053] In this embodiment, the complexity of the SQL statement is preferably obtained and a complexity score is output by using Apache Calcite's functions for parsing, validating, optimizing, and executing SQL statements. The parsing speed is improved by performing word segmentation and part-of-speech tagging on the SQL statement using a natural language model. The natural language model can be the one disclosed in patent publication number CN113032579A, which will not be elaborated further here.

[0054] In a preferred embodiment, for data elements i and j that have dependencies, data element i depends on data element j, and the dependency strength between data element i and data element j is at least one of the following three factors: the inverse of the dependency path length, the number of common dependencies, and the transformation complexity; or, the dependency strength between data element i and data element j is the weighted sum of at least two of the following three factors: the inverse of the dependency path length, the number of common dependencies, and the transformation complexity, where i and j are both data element indices, and i ≠ j;

[0055] In this implementation, the dependency path length represents the number of transformation steps from data element j to data element i in the SQL query. The association from data element i to data element j includes the transformation process from data element j to data element i, as well as the transformation functions, join conditions, and SQL operation types included in this transformation process. In SQL, join conditions are typically represented by the keyword "JOIN," and there are various join types, such as inner join, left outer join, right outer join, and full outer join. SQL operation types include INSERT, UPDATE, DELETE, and SELECT. A transformation function, a join condition, or an SQL operation type constitutes one transformation step.

[0056] In this embodiment, more preferably, when there are multiple paths between two data elements, the shortest path length between the two data elements in the SQL query is calculated, and the shortest path length is used as the dependency path length. The longer the dependency path length, the lower the dependency strength.

[0057] In this implementation, the number of common dependencies represents the number of data elements in the intersection of the set of data elements needed to directly determine data element j in the SQL query and the set of data elements needed to directly determine data element i; that is, the number of data elements contained in the intersection. The set of data elements needed to directly determine data element i / j in the SQL query represents the number of data elements required to obtain data element i / j through only one transformation step (the transformation step closest to data element i / j). The larger the number of data elements in the intersection, the stronger the dependency.

[0058] In this embodiment, the transformation complexity is calculated as follows: The number of transformation steps in the transformation process from data element j to data element i is counted, the computational difficulty of each transformation step is determined, and the computational difficulties of all transformation steps are summed to obtain the transformation complexity from data element j to data element i. More preferably, determining the computational difficulty of each transformation step includes: determining the transformation step category, and querying the difficulty value corresponding to the transformation step category to obtain the computational difficulty of that transformation step. Transformation complexity reflects the computational resources consumed; complex transformations increase the dependency intensity.

[0059] In this embodiment, preset difficulty values ​​corresponding to different conversion functions, different connection conditions, and different operation types are stored in advance. The conversion step categories include different conversion functions, different connection conditions, and different operation types.

[0060] In a preferred embodiment, when the dependency strength between data element i and data element j is the weighted sum of the inverse of the dependency path length, the number of common dependencies, and the transformation complexity:

[0061]

[0062] Where DS represents the dependency strength between data element i and data element j, w1 represents the first weight, PL represents the dependency path length (PL≠0), w2 represents the second weight, DC represents the number of common dependencies, w3 represents the third weight, and TC represents the transformation complexity.

[0063] In a preferred embodiment, to quickly distinguish between direct and indirect dependencies, the edge attributes also include dependency type, which includes direct dependency and indirect dependency. Direct dependency means that there is only one transformation between the data elements connected at both ends of the edge, while indirect dependency means that there are more than two transformations between the data elements connected at both ends of the edge.

[0064] This invention also discloses a data lineage graph analysis device based on SQL, used to implement the aforementioned data lineage graph analysis method based on SQL. In a preferred embodiment, the device includes: a standardization module, which collects multiple SQL statements and standardizes them; an abstract syntax tree generation module, which performs lexical and syntactic analysis on the standardized SQL statements and generates an abstract syntax tree; a data element set and association relationship acquisition module, which traverses the abstract syntax tree to obtain a data element set and the association relationships between data elements, wherein the data element set includes databases, tables, and fields; a dependency judgment module, which determines whether there is a dependency between data elements based on the association relationships between them; a dependency strength calculation module, which calculates the dependency strength between dependent data elements based on the association relationships between them; a lineage graph construction module, which constructs a data lineage graph, in which data elements are graph nodes, and node attributes are database names, table names, or field names, and two dependent data elements are connected by an edge, the attributes of which include the dependency strength between the data elements connected at both ends of the edge; and a visualization module, which visualizes the data lineage graph.

[0065] In this embodiment, the standardization module, abstract syntax tree generation module, data element set and association relationship acquisition module, dependency judgment module, dependency strength calculation module, lineage relationship graph construction module and visualization module correspond one-to-one with steps S1 to S7 in the above method, and will not be described again here.

[0066] The present invention also discloses a computer program product, including a computer program / instruction, characterized in that, when the computer program / instruction is executed by a processor, it implements the steps of the above-mentioned SQL-based data lineage graph analysis method provided by the present invention. The computer program product should be understood as a software product that mainly implements its solution through a computer program, such as a program product integrated in the cloud or software library.

[0067] The present invention also discloses an electronic device, in one embodiment of which the electronic device includes at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to execute the SQL-based data lineage graph analysis method provided by the present invention.

[0068] like Figure 3 The diagram shown is a schematic representation of an electronic device for a data pedigree graph analysis method based on SQL, according to an embodiment of the present invention. The electronic device may include a processor 10, a memory 11, a communication bus 12, and a communication interface 13. It may also include a computer program, such as a data pedigree graph analysis method program based on SQL, stored in the memory 11 and executable on the processor 10.

[0069] In some embodiments, the processor 10 may be composed of integrated circuits, such as a single packaged integrated circuit or multiple integrated circuits with the same or different functions, including combinations of one or more central processing units (CPUs), microprocessors, digital processing chips, graphics processors, and various control chips. The processor 10 is the control unit of the electronic device, connecting various components of the entire electronic device through various interfaces and lines. It executes programs or modules stored in the memory 11 (e.g., executing SQL-based data lineage graph analysis methods) and calls data stored in the memory 11 to perform various functions of the electronic device and process data.

[0070] The memory 11 includes at least one type of readable storage medium, including flash memory, portable hard drive, multimedia card, card-type memory (e.g., SD or DX memory), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the memory 11 can be an internal storage unit of an electronic device, such as a portable hard drive. In other embodiments, the memory 11 can be an external storage device of the electronic device, such as a plug-in portable hard drive, Smart Media Card (SMC), Secure Digital (SD) card, Flash Card, etc. Furthermore, the memory 11 can include both internal and external storage units of the electronic device. The memory 11 can be used not only to store application software and various types of data installed on the electronic device, such as the code of a SQL-based data lineage graph analysis method program, but also to temporarily store data that has been output or will be output.

[0071] The communication bus 12 can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. This bus can be divided into an address bus, a data bus, a control bus, etc. The bus is configured to enable communication between the memory 11 and at least one processor 10, etc.

[0072] Communication interface 13 is used for communication between the aforementioned electronic device and other electronic devices, including a network interface and a user interface. Optionally, the network interface may include a wired interface and / or a wireless interface (such as a Wi-Fi interface, Bluetooth interface, etc.), typically used to establish communication connections between the electronic device and other electronic devices. The user interface may be a display, an input unit (such as a keyboard), or, optionally, a standard wired or wireless interface. Optionally, in some embodiments, the display may be an LED display, a liquid crystal display, a touch-sensitive liquid crystal display, or an OLED (Organic Light-Emitting Diode) touchscreen, etc. The display may also be appropriately referred to as a screen or display unit, used to display information processed in the electronic device and to display a visual user interface.

[0073] Figure 3 Only electronic devices with components are shown; it will be understood by those skilled in the art that... Figure 3The structure shown does not constitute a limitation on the electronic device and may include fewer or more components than shown, or combine certain components, or have different component arrangements. For example, although not shown, the electronic device may also include a power supply (such as a battery) to power the various components. Preferably, the power supply can be logically connected to at least one processor 10 through a power management device, thereby enabling functions such as charging management, discharging management, and power consumption management. The power supply may also include one or more DC or AC power supplies, recharging devices, power fault detection circuits, power converters or inverters, power status indicators, and other arbitrary components. The electronic device may also include various sensors, Bluetooth modules, Wi-Fi modules, etc., which will not be described in detail here.

[0074] It should be understood that the embodiments are for illustrative purposes only and are not limited to this structure in the scope of the patent application.

[0075] Furthermore, if the modules / units integrated in electronic device 1 are implemented as software functional units and sold or used as independent products, they can be stored in a computer-readable storage medium. The computer-readable storage medium can be volatile or non-volatile. For example, a computer-readable medium may include: any entity or device capable of carrying the computer program code, a recording medium, a USB flash drive, a portable hard drive, a magnetic disk, an optical disk, a computer memory, or a read-only memory (ROM).

[0076] In the description of this specification, the references to terms such as "an embodiment," "some embodiments," "example," "specific example," "a implementation," "a preferred implementation," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of the present invention. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described may be combined in any suitable manner in one or more embodiments or examples.

[0077] Although embodiments of the invention have been shown and described, those skilled in the art will understand that various changes, modifications, substitutions and alterations can be made to these embodiments without departing from the principles and spirit of the invention, the scope of which is defined by the claims and their equivalents.

Claims

1. A method for analyzing data lineage graphs based on SQL, characterized in that, include: Collect multiple SQL statements and standardize them. Perform lexical and syntactic analysis on the standardized SQL statements and generate an abstract syntax tree; The abstract syntax tree is traversed to obtain a set of data elements and the relationships between the data elements. The set of data elements includes databases, tables, and fields. Determine whether there are dependencies between data elements based on the relationships between them; Calculate the dependency strength between data elements that have dependencies by analyzing the relationships between data elements; For data elements i and j that have dependencies, data element i depends on data element j, and the dependency strength between data element i and data element j is at least one of the following three factors: the inverse of the dependency path length, the number of common dependencies, and the transformation complexity. Alternatively, the dependency strength between data element i and data element j is the weighted sum of at least two of the following three factors: the inverse of the dependency path length, the number of common dependencies, and the transformation complexity. i and j are both data element indices, and i ≠ j. Wherein, the dependency path length represents the number of transformation steps from data element j to data element i in the SQL query; The number of common dependencies represents the number of data elements in the intersection of the set of data elements needed to directly determine data element j and the set of data elements needed to directly determine data element i in an SQL query; The transformation complexity is calculated as follows: count the number of transformation steps in the transformation process from data element j to data element i, determine the computational difficulty of each transformation step, and sum the computational difficulties of all transformation steps to obtain the transformation complexity from data element j to data element i. When the dependency strength between data element i and data element j is the weighted sum of the inverse of the dependency path length, the number of common dependencies, and the transformation complexity: in, This indicates the strength of the dependency between data element i and data element j. Indicates the first weight. Indicates the length of the dependency path. , Indicates the second weight. Indicates the number of common dependencies. Indicates the third weight. Indicate the transformation complexity; Construct a data lineage graph, in which data elements are graph nodes, and node attributes are database name, table name or field name. Two data elements that have a dependency are connected by an edge, and the attributes of the edge include the dependency strength between the data elements connected at both ends of the edge. The data kinship map is visualized.

2. The SQL-based data lineage graph analysis method as described in claim 1, characterized in that, The edges in the data lineage graph are directed edges, with the direction of the edge pointing from the dependent data element to the dependent data element.

3. The SQL-based data lineage graph analysis method as described in claim 1, characterized in that, Determining the computational difficulty of each conversion step includes: determining the conversion step category, querying the difficulty value corresponding to the conversion step category to obtain the computational difficulty of the conversion step.

4. The SQL-based data lineage graph analysis method as described in claim 1, characterized in that, The properties of an edge also include dependency type, which includes direct dependency and indirect dependency. Direct dependency means that there is only one transformation between the data elements connected at both ends of the edge, while indirect dependency means that there are more than two transformations between the data elements connected at both ends of the edge.

5. A data lineage graph analysis method based on SQL as described in any one of claims 1-4, characterized in that, Standardize SQL statements, including: The SQL statement is matched using a standard template to obtain a matching template, which includes a public template and a dialect-specific template. Trim and replace redundant fields in SQL statements based on matching templates.

6. A data lineage graph analysis device based on SQL, used to implement the data lineage graph analysis method based on SQL as described in any one of claims 1-5, characterized in that, include: The standardization module collects multiple SQL statements and performs standardization processing on them. The abstract syntax tree generation module performs lexical and syntactic analysis on the standardized SQL statements and generates an abstract syntax tree. The data element set and relationship acquisition module traverses the abstract syntax tree to obtain the data element set and the relationship between the data elements. The data element set includes a database, a table, and fields. The dependency determination module determines whether there are dependencies between data elements based on the relationships between them. The dependency strength calculation module calculates the dependency strength between data elements that have dependencies based on the association relationships between data elements. The bloodline graph construction module constructs a data bloodline graph. In the data bloodline graph, data elements are graph nodes. The node attributes are database name, table name, or field name. Two data elements that have a dependency are connected by an edge. The attributes of the edge include the dependency strength between the data elements connected at both ends of the edge. The visualization module displays the kinship map of the data.

7. A computer program product comprising a computer program / instructions, characterized in that, When the computer program / instructions are executed by the processor, they implement the steps of the method described in any one of claims 1-5.

8. An electronic device, characterized in that, The electronic device includes: At least one processor; and, A memory communicatively connected to the at least one processor; wherein the memory stores a computer program executable by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the SQL-based data lineage graph analysis method as described in any one of claims 1 to 5.

Images