Method and apparatus for determining data lineage based on structured query language, and medium

Abstract

This disclosure provides a method, apparatus, and medium for determining data lineage based on Structured Query Language (SCL), relating to the field of computer technology. The method includes: obtaining an abstract syntax tree (AST) corresponding to a SCL statement; the AST is used to represent the SCL statement in a hierarchical tree structure, and includes multiple nodes; determining the data table corresponding to each node in the AST, and obtaining the metadata information of the data table; the metadata information represents the attribute information and field information of the data table; obtaining the field information of each node in the AST, and determining the data lineage information of the SCL statement based on the metadata information and the field information; the field information includes at least one of the node's output field and the node's judgment field; the judgment field represents the field in the judgment condition. This method improves the accuracy of data lineage determination.

Description

Technical Field

[0001] The embodiments of this disclosure relate to the field of computers, and more specifically, the embodiments of this disclosure relate to a method, apparatus, and medium for determining data lineage based on a structured query language. Background Technology

[0002] This section is intended to provide background or context for embodiments of this disclosure. The description herein is not intended to imply that it is prior art simply because it is included in this section.

[0003] Data lineage can be used to represent the source and destination of data, such as tables and fields involved in SQL (Structured Query Language) statements, and is of great significance for analyzing the data generation chain.

[0004] However, SQL statements involve a large number of tables and fields, making it easy to miss data when determining data lineage information, leading to the loss of data lineage for certain fields or tables. Therefore, accurately determining the data lineage of SQL statements is a problem that urgently needs to be solved. Summary of the Invention

[0005] This disclosure provides a method, apparatus, and medium for determining data lineage based on structured query language, in order to improve the accuracy of data lineage determination.

[0006] In a first aspect of this disclosure, a method for determining data lineage based on a structured query language is provided, comprising:

[0007] Obtain the abstract syntax tree corresponding to the statement of the structured query language; wherein the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of a tree structure, and the abstract syntax tree includes at least two nodes;

[0008] The data table corresponding to the node in the abstract syntax tree is determined, and the metadata information of the data table is obtained; wherein, the metadata information is used to represent the attribute information and field information of the data table;

[0009] Obtain the field information of the nodes in the abstract syntax tree, and determine the data lineage information of the statement of the structured query language based on the metadata information and the field information; wherein, the field information includes at least one of the output fields of the node and the judgment fields of the node; the judgment fields are used to represent the fields in the judgment conditions.

[0010] In a second aspect of this disclosure, a data lineage determination apparatus based on a structured query language is provided, comprising:

[0011] The acquisition module is used to acquire the abstract syntax tree corresponding to the statement of the structured query language; wherein the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of tree structure, and the abstract syntax tree includes at least two nodes;

[0012] The first determining module is used to determine the data table corresponding to the node in the abstract syntax tree and obtain the metadata information of the data table; wherein, the metadata information is used to represent the attribute information and field information of the data table;

[0013] The second determining module is used to obtain the field information of the nodes in the abstract syntax tree, and determine the data lineage information of the statement of the structured query language based on the metadata information and the field information; wherein, the field information includes at least one of the output field of the node and the judgment field of the node; the judgment field is used to represent the field in the judgment condition.

[0014] In a third aspect of this disclosure, a computer-readable storage medium is provided that stores computer-executable instructions, which, when executed by a processor, implement the method described in the first aspect.

[0015] In a fourth aspect of this disclosure, a computing device is provided, comprising:

[0016] At least one processor;

[0017] and a memory communicatively connected to the at least one processor;

[0018] The memory stores instructions that can be executed by the at least one processor to cause the computing device to perform the method as described in the first aspect.

[0019] According to the data lineage determination method, apparatus, and medium based on Structured Query Language (SCL) of this disclosure, an abstract syntax tree of an SQL statement can be obtained. The data table involved in the SQL statement can be determined from the nodes of the abstract syntax tree, and the metadata information of the data table can be obtained. The metadata information can represent the attribute information and field information of the data table. When determining data lineage information based on the metadata information, it can ensure that all fields in the data table are considered, avoiding field loss or omission, and improving the accuracy of data lineage determination. Attached Figure Description

[0020] The above and other objects, features, and advantages of this disclosure will become readily apparent from the following detailed description of exemplary embodiments, taken in conjunction with the accompanying drawings. Several embodiments of this disclosure are illustrated in the drawings by way of example and not limitation, in which:

[0021] Figure 1 A schematic diagram illustrating the data lineage determination method of dynamic analysis in the related art of this disclosure is shown.

[0022] Figure 2 A schematic diagram illustrating the process of determining the lineage of fields using static analysis in the related art of this disclosure is shown.

[0023] Figure 3 A schematic diagram illustrating an application scenario according to an embodiment of the present disclosure is provided.

[0024] Figure 4 A schematic flowchart of a data lineage determination method based on structured query language provided according to an embodiment of the present disclosure is shown.

[0025] Figure 5 A schematic diagram of an abstract syntax tree provided according to embodiments of the present disclosure is shown.

[0026] Figure 6 A schematic flowchart of a data lineage determination method based on structured query language provided according to an embodiment of the present disclosure is shown.

[0027] Figure 7 A schematic flowchart of a data lineage determination method based on structured query language provided according to an embodiment of the present disclosure is shown.

[0028] Figure 8 A schematic flowchart of a data lineage determination method based on structured query language provided according to an embodiment of the present disclosure is shown.

[0029] Figure 9 A schematic diagram illustrating a simplified structure of an abstract syntax tree provided according to embodiments of the present disclosure is shown.

[0030] Figure 10 A schematic diagram of a stack structure provided according to an embodiment of the present disclosure is shown.

[0031] Figure 11 A schematic diagram of a program product provided according to an embodiment of the present disclosure is shown.

[0032] Figure 12 A schematic diagram of the structure of a data lineage determination device based on a structured query language according to an embodiment of the present disclosure is shown.

[0033] Figure 13 A schematic diagram of the structure of a computing device provided according to an embodiment of the present disclosure is shown.

[0034] In the accompanying drawings, the same or corresponding reference numerals indicate the same or corresponding parts. Detailed Implementation

[0035] The principles and spirit of this disclosure will now be described with reference to several exemplary embodiments. It should be understood that these embodiments are given merely to enable those skilled in the art to better understand and implement this disclosure, and are not intended to limit the scope of this disclosure in any way. Rather, these embodiments are provided to make this disclosure more thorough and complete, and to fully convey the scope of this disclosure to those skilled in the art.

[0036] Those skilled in the art will recognize that embodiments of this disclosure can be implemented as a system, apparatus, device, method, or computer program product. Therefore, this disclosure can be specifically implemented in the following forms: entirely hardware, entirely software (including firmware, resident software, microcode, etc.), or a combination of hardware and software.

[0037] According to embodiments of this disclosure, a method, apparatus, and medium for determining data lineage based on structured query language are proposed.

[0038] In this article, it is important to understand that the terms used have the following meanings:

[0039] Field lineage: Field lineage refers to the source field and destination field of a field in a data table.

[0040] Table lineage: Table lineage refers to the source table and destination table of a field in a data table.

[0041] Assert lineage: If a field in a conditional statement affects an output field, then the field in the conditional statement has an assertion lineage to the output field.

[0042] Projected lineage: The direct source field of the output field has a projected lineage to the output field.

[0043] Metadata refers to the attribute and field information of a data table, including field names, field types, descriptions of the data table, and whether it is a partitioned table.

[0044] AST (Abstract Syntax Tree): A tree-like representation of a statement generated through lexical and syntactic analysis.

[0045] Antlr4 is an open-source parser generation tool based on Java. It can generate corresponding parsers based on preset grammar files and build abstract syntax trees for specific languages.

[0046] HOOK: is a mechanism for intercepting events, messages, or function calls during processing.

[0047] It should be noted that the user information (including but not limited to user device information, user personal information, etc.) and data (including but not limited to data used for analysis, data stored, data displayed, etc.) involved in this disclosure are all information and data authorized by the user or fully authorized by all parties. Furthermore, the collection, use and processing of the relevant data must comply with the relevant laws, regulations and standards of the relevant countries and regions, and corresponding operation portals are provided for users to choose to authorize or refuse.

[0048] Furthermore, the number of any elements in the accompanying drawings is for illustrative purposes only and not for limitation, and any naming is for distinction only and has no limiting meaning.

[0049] The principles and spirit of this disclosure will be explained in detail below with reference to several representative embodiments. Invention Overview

[0051] The inventors have discovered that data lineage determination methods in related technologies are prone to inaccurate data lineage determination.

[0052] Data lineage analysis for SQL typically involves two steps: generating an abstract syntax tree (AST) and traversing the AST to extract information. Existing data lineage implementation schemes can be broadly categorized into two types: dynamic analysis and static analysis.

[0053] In one example, data lineage is extracted through dynamic analysis. Data lineage can include field lineage and table lineage. The implementation of dynamic analysis for extracting field lineage involves extracting the field lineage during SQL statement execution. For instance, using the Hive engine to execute SQL statements, the field lineage is extracted from the SQL statement based on preset Hive hooks.

[0054] Table lineage extraction typically involves statically traversing the abstract syntax tree to obtain the input and output data tables for each node, thus determining the table lineage. Compared to table lineage extraction, field lineage extraction is more complex, requiring metadata information from the data tables. Furthermore, if subqueries exist in the SQL statement, the transitivity of fields within the subqueries must be considered. A subquery is a query body structure where one query statement is nested within another.

[0055] Refer to the following SQL statement to illustrate the subquery:

[0056] select t1.*,t2.c from(select a,b from t)t1,t2;

[0057] This SQL statement means to query fields a and b from table t, construct a subquery t1, and then query all fields of t1 and field c of t2 from subquery t1 and table t2.

[0058] Figure 1 This is a flowchart illustrating the data lineage determination method based on dynamic analysis in the related technologies of this disclosure. Figure 1 Table lineage can be obtained by directly traversing the abstract syntax tree, while field lineage requires the support of the execution engine. In some SQL dialects, field lineage extraction becomes impossible. For example, traditional relational databases like MySQL and Oracle lack hooks for extracting field lineage, making it impossible to retrieve it and resulting in poor scalability of dynamic analysis methods.

[0059] Furthermore, dynamic analysis for lineage extraction has a lag, meaning it can only be performed during SQL execution. This limits its applicability to scenarios where lineage analysis needs to be done before SQL execution. For example, it cannot be used in scenarios where lineage relationships are required for checkpoint detection in SQL code.

[0060] In one example, the implementation steps of the static analysis method are relatively simple, directly analyzing the abstract syntax tree generated by the SQL statement to obtain the data lineage. The method for obtaining table lineage through static analysis is the same as that through dynamic analysis. Figure 2 This is a flowchart illustrating the field lineage determination method based on static analysis in the related technologies of this disclosure. Figure 2 The static analysis method shown lacks metadata acquisition. Static analysis typically does not acquire metadata, making it impossible to perform lineage inference for SQL statements like `select * from table` that require retrieving all fields from a data table, resulting in the loss of field lineage.

[0061] For subquery structures, accurately analyzing field lineage requires constructing the relationships between the query fields at each level of nesting, which is complex. Current static analysis methods do not offer a clear and explicit solution for this scenario.

[0062] The data lineage determination method disclosed herein can perform static analysis of the abstract syntax tree without the support of an execution engine. Using static analysis as its basic architecture, it solves the problem of heavy reliance on the execution engine in dynamic analysis. Based on static analysis, it obtains metadata information of the data table. By combining the metadata information with the field information of each node in the abstract syntax tree, it addresses the problem of inaccurate lineage inference in static analysis, thereby improving the accuracy of data lineage determination.

[0063] After introducing the basic principles of this disclosure, various non-limiting embodiments of this disclosure will be described in detail below.

[0064] Application Scenarios Overview

[0065] Figure 3 This is a schematic diagram of an application scenario provided in this disclosure. Figure 3 During program execution, when the electronic device reaches the instruction to determine the data lineage of the SQL statement to be analyzed, it determines the abstract syntax tree corresponding to the SQL statement. The electronic device traverses the obtained abstract syntax tree to obtain the metadata and field information of the nodes, and then determines the data lineage information of the SQL statement based on the metadata and field information.

[0066] Exemplary methods

[0067] The following is combined with Figure 3 Application scenarios, refer to Figures 4-10 This document describes a data lineage determination method based on a structured query language according to exemplary embodiments of the present disclosure. It should be noted that the above application scenarios are shown only to facilitate understanding of the spirit and principles of the present disclosure, and the embodiments of the present disclosure are not limited in any way. Rather, the embodiments of the present disclosure can be applied to any applicable scenario.

[0068] Figure 4 This is a flowchart illustrating a data lineage determination method based on Structured Query Language (SCL) provided in an embodiment of this disclosure. As shown in the figure, the method includes the following steps:

[0069] S401. Obtain the abstract syntax tree corresponding to the statement of the structured query language; wherein, the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of tree structure, and the abstract syntax tree includes at least two nodes.

[0070] For example, when determining data lineage information, it is necessary to analyze the AST of the SQL statement. Therefore, the AST corresponding to the SQL statement is obtained. The SQL statement is a statement in a Structured Query Language (SCL) pre-written by the staff. In this embodiment, the SCL statement is referred to as an SQL statement, and the content of the SQL statement is not specifically limited in this embodiment. Each SQL statement can generate a corresponding AST, which can be pre-generated and stored by a computing device. For example, the staff inputs the pre-written SQL statement into the computing device, and the computing device can convert the SQL statement into a tree-structured AST. Alternatively, the staff can pre-draw an AST, and the AST drawn by the staff can be obtained to perform data lineage analysis. In this embodiment, the method of generating the AST is not specifically limited.

[0071] SQL statements are represented using an Abstract Syntax Tree (AST) structure, which has a hierarchical relationship. An AST includes at least two nodes; for example, it may include a root node, multiple intermediate nodes, and multiple leaf nodes. Parent-child relationships exist between nodes at different levels; that is, a higher-level node is the parent of a lower-level node, and a lower-level node is the child of a higher-level node. Nodes in an AST can represent statement blocks in an SQL statement. A statement block refers to a component of an SQL statement, and all nodes in the AST combine to form a complete SQL statement.

[0072] Figure 5 This is a diagram of an abstract syntax tree. Figure 5 The corresponding SQL statement is: `select t1.*, t2.c from (select a, b from t) t1, t2`. An AST (Abstract Syntax Tree) for this SQL statement is constructed using Antlr4, and this AST has a clear hierarchical structure. The root node `statements` represents the entire SQL statement, the `selectClause` node represents the `select t1.*, t2.c` statement block, and `fromClause` represents `from (select a, b from t) t1, t2`. The child nodes of `fromClause` contain `subQuerySource`, representing the subquery structure `(select a, b from t)`, and the `tableSource` node represents table `t2`.

[0073] In this embodiment, obtaining the abstract syntax tree corresponding to the statement in the structured query language includes: obtaining the statement in the structured query language; and constructing the abstract syntax tree corresponding to the statement in the structured query language according to a preset syntax analysis tool.

[0074] Specifically, a pre-written SQL statement can be obtained, and a preset syntax analysis tool can be used to parse the pre-written SQL statement to obtain the individual statement blocks in the SQL statement. These statement blocks are then combined into a tree structure to generate an Abstract Syntax Tree (AST) with hierarchical relationships. For example, Antlr4, a syntax analysis tool that supports syntax analysis for multiple languages, can be used. Based on the preset syntax analysis algorithms in Antlr4, an abstract syntax tree of the SQL statement is constructed. For example, the ALL(*) algorithm can be used as the syntax analysis algorithm. In this embodiment, no specific limitations are made on the syntax analysis tool and the syntax analysis algorithm.

[0075] The advantage of this setup is that, based on the pre-defined syntax analysis tools, an AST can be constructed quickly and efficiently, providing a foundation for subsequent analysis steps and improving the efficiency of determining data lineage.

[0076] S402. Determine the data table corresponding to the node in the abstract syntax tree and obtain the metadata information of the data table; wherein, the metadata information is used to represent the attribute information and field information of the data table.

[0077] For example, an AST includes multiple nodes, and the statement blocks represented by these nodes may or may not contain data tables. For instance, Figure 5 One node in the AST is named "Identifier: t2". This node contains a data table, and the corresponding data table is t2. The task is to determine all the corresponding data tables in the entire AST, that is, to determine whether each node in the AST corresponds to a data table. If so, the data table corresponding to that node is determined; otherwise, the node can be skipped.

[0078] The data table corresponding to a node can be represented by table identifier information such as the table name, which is used to uniquely identify the data table. For example, t2 in "Identifier: t2" is the data table name. By determining the table identifier information such as the table name contained in the node, the data table corresponding to the node can be identified. Each data table has its own metadata information, which refers to the table's attribute information and field information. For example, attributes can be the table type, field types, description of the data table, table headers, etc., while field information can be the field names and the specific data content under each field. All data tables contained in the AST are identified, and the metadata information of each data table is obtained. In this embodiment, the AST can be traversed node by node to ensure that no data table in the AST is missed. For example, preorder or postorder traversal methods can be used for traversal.

[0079] The metadata information of the data tables is pre-stored. For example, the metadata information of all data tables can be pre-stored on a preset server or stored locally. In this embodiment, the storage location of the metadata information is not specifically limited. After determining the data tables in the AST, the metadata information of the data tables can be obtained based on the determined data tables. For example, a network request for metadata retrieval can be sent to a preset server. The network request for metadata retrieval can include table identification information such as the data table name, and the metadata information corresponding to the data table can be retrieved from the preset server. Alternatively, pre-stored metadata information can be retrieved from the local disk. The metadata information can be retrieved in batches after all data tables in the AST are determined, that is, the metadata information of all data tables in the AST can be retrieved at once. Alternatively, the metadata information of each data table can be retrieved once after it is determined.

[0080] S403. Obtain the field information of the nodes in the abstract syntax tree, and determine the data lineage information of the structured query language statement based on the metadata information and the field information; wherein, the field information includes at least one of the node's output fields and the node's judgment fields; the judgment fields are used to represent the fields in the judgment conditions.

[0081] For example, the statement blocks of each node in the AST are determined. Based on the content of the statement blocks, the field information contained in the statement blocks is obtained, that is, the field information corresponding to each node in the AST is obtained. The field information includes at least one of the node's output fields and the node's decision fields. That is, the field information can include either an output field or a decision field, or both. The output field refers to the field output by the node, and the decision field refers to the field in the decision condition of the statement block corresponding to the node. For example, if the statement block of a node is "If condition A is satisfied, then output B", then the output field is B, and the decision field is A. As another example, if the statement block of a node is "output B", then the output field of this node is B, and there is no decision field.

[0082] Collect field information from all nodes and combine it with metadata to determine the data lineage information of the SQL statement. Data lineage information can be field lineage information, table lineage information, or both. Field lineage information represents the source and destination fields of the fields in the tables contained in the SQL statement. Table lineage information represents the source and destination tables of the fields in the tables contained in the SQL statement. For example, a node's field lineage information can represent the mapping relationship between the node's output fields and source fields, and table lineage information can represent the mapping relationship between the node's output fields and source tables.

[0083] An Abstract Syntax Tree (AST) has a hierarchical relationship. Data lineage information can be determined layer by layer from bottom to top based on this hierarchy, ultimately yielding the data lineage information of the top-level node. This top-level node's data lineage information is then used to define the data lineage information for the entire SQL statement. For example, first, based on metadata, determine the source of the output fields of the bottom-level leaf nodes; that is, identify the source field or source table of the leaf node's output fields from the metadata. Establish a mapping relationship between the output fields and source fields, serving as the leaf node's field lineage information. Similarly, establish a mapping relationship between the output fields and source tables, serving as the leaf node's table lineage information, thus obtaining the leaf node's data lineage information. Next, determine the output fields of the leaf node's parent nodes. From the leaf node's data lineage information, determine the source field and source table of that output field. Again, establish mapping relationships between the output field and source fields, and between the output field and source table, obtaining the leaf node's parent node's data lineage information. Continue this process layer by layer upwards until all nodes in the AST have been traversed, yielding the data lineage information of the top-level node. If a node's field information includes a judgment field, a mapping relationship is established between the judgment field and all output fields of that node, serving as part of the node's data lineage information. For example, if a node's judgment field is A and its output fields are B and C, then a mapping relationship can be established between A and B, as well as between A and C.

[0084] In this embodiment, the method further includes: outputting data lineage information from the root node of the abstract syntax tree.

[0085] Specifically, after obtaining the data lineage information of the SQL statement, the data lineage information can be output. This information can be output from the top-level node of the AST. That is, after traversing all the root nodes of the AST, i.e., after accessing the SQL statement at the top-level node, the data lineage information is output from the top-level root node. The data lineage information corresponding to the root node is stored and output as the result of syntax parsing.

[0086] The advantage of this setup is that the final data lineage information output by the root node can cover all statement blocks of the SQL statement, avoiding omissions of data lineage information and improving the accuracy of data lineage determination.

[0087] Understandably, in this embodiment, by obtaining the abstract syntax tree (AST) of the SQL statement, all data tables involved in the SQL statement can be determined from the nodes of the AST, and the metadata information corresponding to each data table can be obtained. The metadata information can represent the attribute and field information of the data table. By determining the field information of each node in the AST and combining the metadata and field information, the data lineage information of the entire AST, i.e., the data lineage information of the SQL statement, can be determined. When determining the data lineage information based on the metadata information, it can be ensured that all fields in the data tables are considered, avoiding field loss or omission, and effectively improving the accuracy of the data lineage information determination.

[0088] Figure 6 This is a flowchart illustrating another data lineage determination method based on structured query language provided in this embodiment of the disclosure.

[0089] In this embodiment, determining the data table corresponding to a node in the abstract syntax tree and obtaining the metadata information of the data table can be refined as follows: if it is determined that a node in the abstract syntax tree corresponds to a data table, then the table identifier information of the data table is obtained from the node in the abstract syntax tree; wherein, in the abstract syntax tree, the node corresponding to the data table is a leaf node; a metadata retrieval instruction is sent to a preset metadata database, and the metadata information of the data table sent by the metadata database is received; wherein, the metadata database is used to store the metadata information of the data table, and the metadata retrieval instruction includes the table identifier information of the data table.

[0090] like Figure 6 As shown, the method includes the following steps:

[0091] S601. Obtain the abstract syntax tree corresponding to the statement of the structured query language; wherein, the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of tree structure, and the abstract syntax tree includes at least two nodes.

[0092] For example, this step can refer to step S401 above, and will not be repeated here.

[0093] S602. If it is determined that a node in the abstract syntax tree corresponds to a data table, then the table identifier information of the data table is obtained from the node in the abstract syntax tree; wherein, in the abstract syntax tree, the node corresponding to the data table is the leaf node.

[0094] For example, nodes in an AST may or may not correspond to data tables. The process involves determining whether each node in the AST corresponds to a data table. If not, metadata information for the node is not retrieved; if so, the table identifier information of the corresponding data table is retrieved from the node. The table identifier information can be information that uniquely identifies the data table, such as the table name. For example, if the statement block corresponding to a node is "t2", then it is determined that the node corresponds to a data table, and the table identifier information is determined to be t2.

[0095] When constructing an Abstract Syntax Tree (AST) based on a pre-defined syntax analysis tool, the tool can map all data tables in an SQL statement to leaf nodes; that is, only leaf nodes may correspond to data tables. In this embodiment, the nodes in the AST that correspond to data tables are leaf nodes, but not all leaf nodes necessarily correspond to data tables. When determining the data table corresponding to a node in the AST, only the leaf nodes of the AST can be traversed to determine whether a leaf node corresponds to a data table. If it does, the table identifier information of the data table is obtained, and then the metadata information of the data table is obtained; otherwise, the metadata information is not obtained.

[0096] In this embodiment, if it is determined that a node in the abstract syntax tree corresponds to a data table, the table identifier information of the data table is obtained from the node in the abstract syntax tree. This includes: traversing the nodes in the abstract syntax tree; if it is determined that the currently traversed node in the abstract syntax tree corresponds to a data table, the table identifier information of the data table is obtained from the currently traversed node; if it is determined that the traversal of the nodes in the abstract syntax tree is complete, the set of table identifier information is determined; wherein, the set of table identifier information includes the table identifier information of all data tables corresponding to the abstract syntax tree.

[0097] Specifically, the AST is traversed, for example, through preorder or postorder traversal. In this embodiment, the traversal method for this step is not specifically limited. The nodes in the AST are traversed to determine whether each node corresponds to a data table. If it is determined that the currently traversed node does not correspond to a data table, the traversal continues to the next node. If it is determined that the currently traversed node corresponds to a data table, the table identifier information of the data table is obtained from the currently traversed node. For example, the name of the data table can be determined from the statement block of the node.

[0098] Once a data table is identified, its table identifier information can be cached. If the traversal of all nodes in the AST is complete, a set of table identifier information can be obtained. This set can include the table identifier information of all data tables involved in the AST. For example, if the AST involves five data tables t1, t2, t3, t4, and t5, then the set {t1, t2, t3, t4, t5} can be obtained.

[0099] The advantage of this setup is that, in related techniques for field lineage analysis, static analysis fails to acquire metadata information, leading to the loss of field lineage information. This embodiment traverses the AST to avoid missing data tables and improves the accuracy of metadata information determination. Furthermore, by generating a set of table identifier information, it facilitates subsequent batch acquisition of metadata information, improving the efficiency of metadata information determination.

[0100] In this embodiment, traversing the nodes in the abstract syntax tree includes: performing a preorder traversal of the nodes in the abstract syntax tree; if, according to the preset structured query language syntax file, it is determined that there is no data table in the child nodes of the currently traversed node, then the traversal of the child nodes of the currently traversed node is skipped; wherein, the structured query language syntax file is used to represent the statement writing rules of the structured query language.

[0101] Specifically, SQL statements are written according to a predefined syntax file. This syntax file represents the rules for writing SQL statements. In the Abstract Syntax Tree (AST), a node represents a block of statements within an SQL statement; that is, the blocks of statements within nodes in the AST also follow the rules of the predefined syntax file. Based on the predefined syntax file, it can be determined whether the currently traversed node corresponds to a child node containing a data table; that is, it can be determined whether a data table exists among the child nodes of the currently traversed node. For example, if the currently traversed node is "select t1.*,t2.c", then according to the predefined syntax file, it can be determined that this node has child nodes containing t1 and child nodes containing t2.

[0102] A preorder traversal can be performed on the AST, starting from the root node and traversing downwards until the last leaf node is reached. Upon reaching each node, it is determined whether a data table exists among its child nodes. If so, the traversal continues downwards, and when a child node with a data table is encountered, its table identifier information can be obtained. Otherwise, the traversal of all child nodes of the currently visited node is skipped. In other words, if it is determined that none of the child nodes of a node have a corresponding data table, then there is no need to traverse those child nodes.

[0103] The advantage of this setup is that, using preorder traversal, if the currently traversed node explicitly does not contain child nodes with corresponding database entries, then the child nodes of the currently traversed node are not visited. Compared to postorder traversal and other traversal methods, preorder traversal can reduce the number of nodes visited, improve traversal efficiency, and thus improve the efficiency of determining data lineage.

[0104] S603. Send a metadata retrieval instruction to a preset metadata database and receive metadata information of the data table sent by the metadata database; wherein, the metadata database is used to store the metadata information of the data table, and the metadata retrieval instruction includes the table identifier information of the data table.

[0105] For example, after determining that the table identifier information is obtained, a metadata retrieval command can be sent to a preset metadata database. The metadata database can be set up on a preset server or locally; it is a database that stores metadata information, and the metadata retrieval command contains the table identifier information. Upon receiving the metadata retrieval command, the metadata database can search for the metadata information corresponding to the table identifier information in the command and send it. The metadata information sent by the metadata database is then received.

[0106] A metadata retrieval command can be sent once each table identifier is determined. That is, all metadata information corresponding to a data table can be retrieved from the metadata database each time. Once the AST traversal is complete, all metadata information of the AST is obtained.

[0107] In this embodiment, the metadata retrieval instruction includes a set of table identification information. The metadata retrieval instruction is used to retrieve the metadata information of all data tables corresponding to the abstract syntax tree in batches.

[0108] Specifically, metadata information for all tables in the AST can be retrieved at once. First, the AST is traversed, resulting in a set of table identifiers. Then, a metadata retrieval command is sent to the metadata database, which can include the set of table identifiers. The metadata database can then return the metadata information corresponding to each table identifier in the set. In other words, the metadata retrieval command can be used to retrieve metadata information for all corresponding tables in the AST in batches.

[0109] The advantage of this setup is that batch access effectively reduces the overhead of multiple network accesses and data transmissions, improving the efficiency of determining metadata information and thus the efficiency of determining data lineage. Furthermore, in static analysis techniques, the lack of metadata information leads to the loss of field lineage. This embodiment achieves accurate analysis of field lineage by acquiring metadata information, thereby improving the precision of lineage analysis.

[0110] S604. Obtain the field information of the nodes in the abstract syntax tree, and determine the data lineage information of the structured query language statement based on the metadata information and the field information; wherein, the field information includes at least one of the node's output fields and the node's judgment fields; the judgment fields are used to represent the fields in the judgment conditions.

[0111] For example, this step can refer to step S403 above, and will not be repeated here.

[0112] Understandably, in this embodiment, by obtaining the abstract syntax tree (AST) of the SQL statement, all data tables involved in the SQL statement can be determined from the nodes of the AST. By performing a preorder traversal of the AST, the metadata information corresponding to each data table in the AST is obtained, avoiding omissions of metadata information and improving the efficiency and accuracy of metadata information determination. The field information of each node in the AST is determined, and combined with the metadata information and field information, the data lineage information of the entire AST, i.e., the data lineage information of the SQL statement, is determined. When determining the data lineage information based on the metadata information, it can be ensured that all fields in the data tables are considered, avoiding field loss or omission, and effectively improving the accuracy of data lineage information determination.

[0113] Figure 7 This is a flowchart illustrating another data lineage determination method based on structured query language provided in this embodiment of the disclosure.

[0114] In this embodiment, obtaining the field information of a node in the abstract syntax tree can be further refined as follows: performing a post-order traversal of the abstract syntax tree and extracting the field information from the currently traversed node.

[0115] like Figure 7 As shown, the method includes the following steps:

[0116] S701. Obtain the abstract syntax tree corresponding to the statement of the structured query language; wherein, the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of tree structure, and the abstract syntax tree includes at least two nodes.

[0117] For example, this step can refer to step S401 above, and will not be repeated here.

[0118] S702. Determine the data table corresponding to the node in the abstract syntax tree and obtain the metadata information of the data table; wherein, the metadata information is used to represent the attribute information and field information of the data table.

[0119] For example, this step can refer to step S402 above, and will not be repeated here.

[0120] S703. Perform a post-order traversal of the abstract syntax tree and extract field information from the currently traversed node.

[0121] For example, when retrieving metadata information, a preorder traversal of the AST can be performed. When retrieving node field information, a postorder traversal of the AST can be performed. Postorder traversal refers to traversing from the bottommost leaf node of the AST upwards until the root node of the AST is reached.

[0122] For each node encountered during iteration, its field information is extracted. This field information can include at least one of the node's output fields and conditional fields. Field information can be directly obtained from the statement block corresponding to the node. For example, if the statement block for the currently iterated node is "select t1.*, t2.c", where "*" represents all fields, then the output fields in the node's field information can be determined to be all fields in t1 and field c in t2.

[0123] In this embodiment, a post-order traversal approach is adopted. That is, for a given node, its child nodes are traversed first, and then the node itself is traversed. By using post-order traversal, the data lineage information of the node can be inferred after collecting the data lineage information of all its child nodes. Through this bottom-up approach, the lineage relationships of nodes in the AST are constructed layer by layer, and the lineage of the root node is finally inferred, thereby obtaining all the data lineage information of the entire SQL statement and avoiding the omission of data lineage information.

[0124] In this embodiment, the AST is traversed at least twice. The first traversal is performed in step S702 when determining the data table corresponding to the node in the abstract syntax tree. The second traversal is performed in step S703. The first traversal can be a preorder traversal, and the second traversal can be a postorder traversal. The first traversal is used to obtain all data tables in the AST, thereby batch obtaining the metadata information of the data tables and avoiding omissions in field lineage analysis. The second traversal can collect the field lineage information of all child nodes from the lower to the upper layers of the AST, and then infer the field lineage information of the current node by combining it with the metadata information. This achieves recursive upward construction of field lineage, layer by layer, to obtain the field lineage information of the entire SQL statement. That is, the first traversal is used to obtain metadata information, and the second traversal is used to construct the lineage.

[0125] S704. Determine the data lineage information of the Structured Query Language statement based on metadata and field information.

[0126] For example, the field lineage information of the currently traversed node is obtained by combining the field information of the currently traversed node with the metadata information. In this embodiment, table lineage information can be disregarded initially, and the field lineage information of the node can be determined as the data lineage information of the node. In the AST, nodes corresponding to data tables are leaf nodes, that is, nodes corresponding to metadata information are leaf nodes. When determining the field lineage information of a leaf node, if the leaf node has corresponding metadata information, the field lineage information of the leaf node can be determined based on the field information of the leaf node and the corresponding metadata information; if the leaf node does not have corresponding metadata information, the field lineage information of the leaf node can be determined based solely on the field information of the leaf node. When determining the field lineage information of a non-leaf node, it is not necessary to combine the metadata information; instead, the field lineage information of the currently traversed node can be determined by combining the field information of the currently traversed node and the field lineage information of the child nodes of the currently traversed node. That is, metadata information can be used only for determining the field lineage information of leaf nodes.

[0127] By performing a post-order traversal, the field lineage information of each node is determined from bottom to top until the root node is reached, and the field lineage information corresponding to the root node is obtained, which is the data lineage information of the SQL statement.

[0128] In this embodiment, the data lineage information of the Structured Query Language statement is obtained based on metadata information and field information, including: determining the field lineage information of the currently traversed node based on metadata information and field information of the currently traversed node; wherein, the field lineage information is used to indicate the source and destination of fields in the data table, and the field lineage information includes projection edge information and assertion edge information; the projection edge information is used to indicate the mapping relationship between the output field of the node and the source table field, the assertion edge information is used to indicate the mapping relationship between the output field of the node and the judgment field, and the source table field is used to indicate the output field of the leaf node; if it is determined that the post-order traversal of the abstract syntax tree is completed, the field lineage information of the currently traversed node is determined, which is the data lineage information of the Structured Query Language statement.

[0129] Specifically, the process involves determining the field information of the currently traversed node, and then, based on the metadata and the field information of the currently traversed node, determining its field lineage. For example, based on the field information of the currently traversed node, the output fields of the currently traversed node are determined. If the output fields of the currently traversed node originate from a certain data table, then all fields in the metadata of that data table are identified as the source fields of the output fields, and a mapping relationship is established between the output fields and each source field. This mapping relationship is then used to determine the field lineage information of the currently traversed node.

[0130] Field lineage information is used to represent the source and destination of fields in a data table. That is, it can establish a mapping relationship between a field and its source field, or between a field and its destination field, and the established mapping relationship is defined as field lineage information. Field lineage information can include projection edge information and assertion edge information. Projection edge information can be used to represent the mapping relationship between a node's output field and its source table fields, and source table fields can be used to represent the output fields of leaf nodes. That is, for the currently traversed node, the mapping relationship between the node's output field and the output fields of its leaf nodes can be determined. Projection edge information can also represent the mapping relationship between a node's output field and the output fields of its child nodes. Since a post-order traversal of the AST is performed, the mapping relationship between a node's output field and its source table fields can be inferred from the mapping relationship between the node's output field and the output fields of its child nodes.

[0131] Assertion edge information can be used to represent the mapping relationship between the output field and the judgment field of a node. If the statement block of the currently traversed node does not contain a judgment condition, that is, it does not contain a judgment field, then the field lineage information of the currently traversed node does not contain assertion edge information.

[0132] If it is determined that the AST traversal is not complete, the traversal continues upwards, constructing the field lineage information of each node in the AST layer by layer based on the field information of the node and the field lineage information of its child nodes. If it is determined that the post-order traversal of the AST is complete, that is, the currently traversed node is the root node of the AST, the field lineage information of the root node is determined, and the field lineage information of the root node is used as the data lineage information of the SQL statement.

[0133] The advantage of this setup is that it collects the field lineage information of child nodes and, combined with metadata, infers the field lineage information of the currently traversed node. Through this bottom-up approach, the data lineage information of nodes in the AST is built layer by layer, ultimately inferring the data lineage information of the root node, thus obtaining the data lineage information of the entire SQL statement. This post-order traversal method, based on static analysis, does not require waiting until the SQL statement is executed to extract data lineage information, nor does it require the support of an execution engine, offering high scalability and versatility. Combined with metadata, field lineage information can be accurately inferred during static analysis. The bottom-up field lineage construction method covers all SQL scenarios, avoiding omissions of data lineage information and improving the efficiency and accuracy of data lineage determination.

[0134] This embodiment employs static analysis, which does not rely on the execution engine; the execution engine is used in dynamic analysis. Dynamic analysis refers to lineage analysis being performed only when the SQL statement is executed, making it inherently delayed. Furthermore, the execution engine has limitations on the language type of the SQL statement, making lineage analysis impossible for certain SQL dialects. For example, traditional relational databases like MySQL and Oracle cannot extract field lineage, resulting in poor scalability. Therefore, this embodiment boasts high scalability and versatility, making it widely applicable.

[0135] In this embodiment, after determining the field lineage information of the currently traversed node if the post-order traversal of the abstract syntax tree is completed, the method further includes: performing a preset traversal process on the abstract syntax tree to determine the table lineage information of the traversed node when the preset traversal process is completed; wherein, the preset traversal process is either a pre-order traversal process or a post-order traversal process, and the table lineage information is used to represent the source table and destination table of the field; based on the table lineage information and the field lineage information, the data lineage information of the structured query language statement is determined.

[0136] Specifically, the data lineage information may include table lineage information, which can be used to represent the source table and destination table of a field. When determining the table lineage information, the AST can be traversed in a preset manner. In this embodiment, the preset method is not specifically limited; for example, the preset traversal process can be either a preorder traversal or a postorder traversal.

[0137] During the preset traversal process, the table lineage information of each node is determined upon traversal. If the traversal is not complete, it continues until the last node is reached. If the preset traversal is complete, the table lineage information of the last traversed node is determined and used as the table lineage information for the entire SQL statement. Table lineage information can be obtained during static traversal of the AST. For example, the source table of fields can be directly obtained from the statement block of a node, thus obtaining the node's table lineage information. In this embodiment, the method for obtaining table lineage information is not specifically described.

[0138] After obtaining the field lineage information and table lineage information of the SQL statement, these two information are used together as the data lineage information of the SQL statement. That is, the data lineage information includes both field lineage information and table lineage information. In this embodiment, the order in which the table lineage information and field lineage information are obtained is not limited.

[0139] The advantage of this setup is that by traversing the AST, the table lineage information of the SQL statement can be obtained, thereby improving the data lineage information of the SQL statement and increasing the accuracy of the determination of the data lineage information.

[0140] In this embodiment, the statement type of the currently traversed node is a data table. Based on the metadata information and the field information of the currently traversed node, the field lineage information of the currently traversed node is determined, including: based on the metadata information of the data table in the currently traversed node, all fields in the data table in the currently traversed node are determined as metadata fields; based on the output fields in the field information of the currently traversed node, the mapping relationship between the metadata fields and the output fields of the currently traversed node is obtained, and the mapping relationship between the metadata fields and the output fields of the currently traversed node is determined as the projection edge information of the currently traversed node.

[0141] Specifically, the statement types of nodes in an AST include data tables and subqueries. A statement type of data table means that the statement content in the statement block corresponding to the node is a specific data table; a statement type of subquery means that the statement content in the statement block corresponding to the node is a subquery structure, which refers to a structure in which one query statement is nested within another query statement.

[0142] If the statement type of the currently traversed node is a data table, then the metadata information of the corresponding data table is determined. Field lineage information includes projection edge information and assertion edge information. When determining the projection edge information of the currently traversed node, the metadata information of the data table in the currently traversed node and the field information of the currently traversed node can be combined to determine the projection edge information.

[0143] The specific process for determining the projection edge information of a data table type node can be as follows: First, determine the metadata information of the data table in the currently traversed node, and identify all fields in this metadata information as metadata fields. Next, determine the output fields in the field information of the currently traversed node, and establish a mapping relationship between the metadata fields and the output fields, indicating that the output fields of the currently traversed node originate from the metadata fields. A mapping relationship can be established between each metadata field and each output field. This mapping relationship between the metadata fields and the output fields is determined as the projection edge information of the currently traversed node. In this embodiment, a node with a statement type of data table can be a leaf node; that is, when the currently traversed node is a leaf node, the projection edge information of the currently traversed leaf node can be determined based on the metadata information and the field information of the currently traversed leaf node. In actual ASTs, data table type nodes do not contain judgment conditions; that is, only the projection edge information needs to be determined for data table type nodes, without assertion edge information.

[0144] The advantage of this setup is that, for nodes of the data table type, the projection edge information of the node can be determined by combining metadata and field information. By combining metadata information, omissions of fields in the data table can be avoided, thus improving the accuracy of determining the projection edge information.

[0145] In this embodiment, the statement type of the currently traversed node is a subquery. Based on the metadata information and the field information of the currently traversed node, the field lineage information of the currently traversed node is determined, including: determining the child node of the currently traversed node as the current child node, obtaining the field lineage information of the current child node, and obtaining the field information of the currently traversed node; determining the field lineage information of the currently traversed node based on the field information of the currently traversed node and the field lineage information of the current child node; if it is determined that the post-order traversal of the abstract syntax tree is not complete, then the parent node of the currently traversed node is determined, and the parent node of the currently traversed node is determined as the current parent node; obtaining the field information of the current parent node, and determining the field lineage information of the current parent node based on the field lineage information of the currently traversed node and the field information of the current parent node, until the post-order traversal of the abstract syntax tree is completed.

[0146] Specifically, for nodes of the subquery type, since the AST is traversed in post-order, when determining the field lineage information of the currently traversed node, the field lineage information of the child nodes of the currently traversed node must first be determined. The child nodes of the currently traversed node are then identified as the current child nodes. The field lineage information of the current child nodes and the field information of the currently traversed node are then retrieved.

[0147] Based on the field information of the currently traversed node and the field lineage information of the current child node, determine the field lineage information of the currently traversed node. For example, the output field of the currently traversed node can be determined based on its field information. Establish a mapping relationship between the output field of the currently traversed node and the output field of the current child node. Then, based on the projection edge information in the field lineage information of the current child node, determine the mapping relationship between the output field of the current child node and the source table fields. Based on the mapping relationship between the output field of the currently traversed node and the output field of the current child node, and the mapping relationship between the output field of the current child node and the source table fields, obtain the mapping relationship between the output field of the currently traversed node and the source table nodes. This mapping relationship between the output field of the currently traversed node and the source table nodes can be determined as the projection edge information of the currently traversed node. If the currently traversed node does not contain a condition, its projected edge information can be used as its field lineage information. If the currently traversed node contains a condition, a mapping relationship between the conditional field and the output field can be established based on the conditional field and output field in the field information, which can then be used as the assertion edge information of the currently traversed node. The projected edge information and assertion edge information of the currently traversed node are then used as the field lineage information of the currently traversed node.

[0148] If it is determined that the post-order traversal of the abstract syntax tree is not complete, then the parent node of the currently traversed node is determined, and the traversal continues to the parent node of the currently traversed node. The parent node of the currently traversed node can be determined as the current parent node. The field information of the current parent node is obtained, and based on the field lineage information of the currently traversed node and the field information of the current parent node, the field lineage information of the current parent node is determined, until the post-order traversal of the abstract syntax tree is completed. That is, traversing to the topmost parent node, which is the root node of the AST, and obtaining the field lineage information of the root node.

[0149] The advantage of this setup is that, for nodes of subquery type, field lineage information is constructed layer by layer upwards based on the field information of the currently traversed node and the field lineage information of the current child node, until the AST traversal is complete. Subquery lineage analysis is quite complex, requiring accurate analysis of the relationships between the nested query fields at each level. This embodiment, through bottom-up traversal, can analyze all nested statements in the subquery structure, handling complex subquery scenarios and ensuring that no data lineage information is omitted.

[0150] In this embodiment, the field lineage information of the currently traversed node is determined based on the field information of the currently traversed node and the field lineage information of the current child node. This includes: if the field information of the currently traversed node contains a preset field identifier, then all the projection edge information in the field lineage information of the current child node is determined as the projection edge information of the currently traversed node.

[0151] Specifically, the field information of a node can be determined based on the content of its statement block. The field information corresponding to a subquery-type node can contain preset field identifiers, specific fields, or function expressions. Different processing methods can be used to determine the field lineage information of a node depending on the different content contained in its statement block.

[0152] The default field identifier can be "*", where "*" represents all fields. That is, to determine the projection edge information corresponding to all output fields of the current child node, the projection edge information of all output fields of the current child node is used as the projection edge information of the currently traversed node.

[0153] In other words, if the field information of a subquery-type node contains a preset field identifier, then all projection edge information of the current child node is aggregated and used as the projection edge information of the currently traversed node. For example, if the statement block of a subquery-type node is represented as `select t1.*from t2`, where "*" is a preset field identifier, then all projection edge information of the fields in t2 can be aggregated and used as the projection edge information of the fields in t1. The projection edge information of the fields in t1 is then the projection edge information of this subquery-type node.

[0154] The advantage of this setting is that the processing method for the preset field identifier is the simplest, directly aggregating all the projection edge information of the current child node, improving the efficiency of determining the projection edge information, and realizing targeted processing for different sub-query nodes.

[0155] In this embodiment, the field lineage information of the currently traversed node is determined based on the field information of the currently traversed node and the field lineage information of the current child node. This includes: if the field information of the currently traversed node contains a database table name and a field name corresponding to the database table name, then the field lineage information corresponding to the database table name is determined from the field lineage information of the current child node, and the field lineage information corresponding to the database table name is determined as the target lineage information; the projection edge information of the currently traversed node is determined based on the projection edge information corresponding to the field name corresponding to the database table name in the target lineage information.

[0156] Specifically, for subquery-type nodes, the field information is further divided into two cases: one where the field information contains both the database / table name and the corresponding field name, and the other where it only contains the field name. The database / table name refers to the name of the data table, and the field name is the name of the specific field. The field name corresponding to the database / table name refers to the name of the field in the data table corresponding to the database / table name.

[0157] If the field information of the currently traversed node contains a database table name and its corresponding field names, then the field lineage information of the current child node is determined. From the field lineage information of the current child node, the field lineage information corresponding to the database table name is determined. The field information of the current child node can contain multiple database table names, and each database table name corresponds to its own field lineage information. For example, if the field information of the current child node includes t1 and t2, then the field lineage information corresponding to t1 and the field lineage information corresponding to t2 of the current child node can be determined. The output fields in the field lineage information corresponding to the database table name are the fields in the data table corresponding to the database table name.

[0158] From the lineage information of the current child node, determine the lineage information corresponding to the table name of the currently traversed node, and identify that lineage information as the target lineage information. For example, if the lineage information of the current child node includes the lineage information corresponding to t1 and the lineage information corresponding to t2, and the table name of the currently traversed node is t1, then the lineage information corresponding to t1 can be identified as the target lineage information.

[0159] Determine the field name corresponding to the database table name in the field information of the currently traversed node. Based on the projection edge information corresponding to the field name corresponding to the database table name in the target lineage information, determine the projection edge information of the currently traversed node. That is, determine the mapping relationship matching the field name corresponding to the database table name from the projection edge information of the target lineage information. Determine the source table field in the mapping relationship matching the field name corresponding to the database table name, and construct the mapping relationship between the source table field and the field name corresponding to the database table name, as the projection edge information of the currently traversed node.

[0160] The advantage of this setting is that, for specific fields, if there is a database table name, the system first searches for the target lineage information that matches the database table name, and then finds the mapping relationship that matches the field name from the target lineage information, thereby achieving accurate determination of the projection edge information.

[0161] In this embodiment, the field lineage information of the currently traversed node is determined based on the field information of the currently traversed node and the field lineage information of the current child node. This includes: if the field information of the currently traversed node contains only the field name, then the projection edge information corresponding to the field name is determined from the field lineage information of the current child node; and the projection edge information of the currently traversed node is determined based on the projection edge information corresponding to the field name in the field lineage information of the current child node.

[0162] Specifically, if the field information of the currently traversed node does not contain a database table name, but only a field name, then there is no need to perform database table name matching. Traverse the field lineage information of the current child node to determine the projection edge information that matches the field name.

[0163] Based on the lineage information of the current child node, the projection edge information of the currently traversed node is determined by matching the field name with the projection edge information. This can be done by first identifying the source table fields in the projection edge information, and then mapping these source table fields to the fields in the field names of the currently traversed node, thus constructing the projection edge information of the currently traversed node.

[0164] The advantage of this setup is that for subquery nodes that only have field names, only one match is needed, without needing to distinguish between different data tables. This improves the efficiency of determining projection edge information and enables targeted processing for different situations.

[0165] In this embodiment, the field lineage information of the currently traversed node is determined based on the field information of the currently traversed node and the field lineage information of the current child node. This includes: if the field information of the currently traversed node contains a function expression, then determining the field name in the function expression; determining the projection edge information corresponding to the field name in the function expression from the field lineage information of the current child node; and determining the projection edge information of the currently traversed node based on the projection edge information corresponding to the field name in the function expression from the field lineage information of the current child node.

[0166] Specifically, if the field information of the currently traversed node contains a function expression, then the field names in the function expression are determined. For example, if the function expression is func(a, b) as c, then the field names in the function expression are a and b. Here, c is a preset alias, meaning that the projection edge information corresponding to a and b is used as the projection edge information of c.

[0167] Determine the field lineage information of the current child node, and traverse all the projected edge information of 'a' and 'b' in the field lineage information. That is, determine the projected edge information corresponding to the field names in the function expression from the field lineage information of the current child node. Based on the projected edge information corresponding to the field names in the function expression from the field lineage information of the current child node, establish the projected edge information of 'a' and 'b' of the currently traversed node, as the projected edge information of the currently traversed node. For example, we can determine the source table field in the projected edge information of 'a' in the field lineage information of the current child node, establish the mapping relationship between 'a' of the currently traversed node and the source table field, as the projected edge information of 'a' of the currently traversed node. We can use a preset alias as the key to store the projected edge information corresponding to the field names in the function expression as the projected edge information of the alias. The projected edge information of the alias is then determined as the projected edge information of the currently traversed node.

[0168] The advantage of this setting is that, for the case of function expressions, it can traverse all field lineage information of the current child node, avoid missing projection edge information, improve the efficiency of determining field lineage information, and achieve targeted processing for different situations.

[0169] The lineage construction of subquery nodes is quite complex. Subquery nodes include three types: field identifiers, specific fields, and function expressions. By processing different types in a targeted manner, we can deal with complex subquery scenarios, improve the accuracy of determining data lineage, and broaden the scope of application.

[0170] In this embodiment, the field lineage information of the currently traversed node is determined based on the metadata information and the field information of the currently traversed node, including: if the field information of the currently traversed node includes a judgment field, then the output field in the field information of the currently traversed node is determined; a mapping relationship between the judgment field and the output field is constructed as the assertion edge information of the currently traversed node.

[0171] Specifically, if the field information of the currently traversed node does not include a judgment field, then the projection edge information of the currently traversed node is determined based on the metadata information and the field information of the currently traversed node, and used as the field lineage information. If the field information of the currently traversed node includes a judgment field, then the assertion edge information of the currently traversed node also needs to be determined. The assertion edge information and the projection edge information are used together as the field lineage information of the currently traversed node.

[0172] When determining assertion edge information, the output fields and judgment fields in the field information of the currently traversed node are determined. A mapping relationship is constructed between each judgment field and each output field, serving as the assertion edge information for the currently traversed node. For example, if the judgment condition of the currently traversed node is "if h and k, then output m", then a mapping relationship between h and m, and a mapping relationship between k and m, can be established. These mapping relationships between h and m, and between k and m, are then determined as the assertion edge information for the currently traversed node.

[0173] The advantage of this setup is that the construction of assertion edge information is relatively simple, and all output fields corresponding to the node are mapped to the judgment fields in the judgment conditions, thereby improving the determination efficiency of assertion edge information.

[0174] In this embodiment, the method further includes: generating structured data of the currently traversed node based on the projection edge information and assertion edge information of the currently traversed node, and storing the structured data; wherein the structured data is a preset data structure, and the structured data includes the projection edge information and assertion edge information of the currently traversed node, as well as the field information, node type and node name of the currently traversed node.

[0175] Specifically, a pre-defined data structure can be used to represent the determined lineage information as structured data. Based on the projection edge information and assertion edge information of the currently traversed node, structured data of the currently traversed node is generated according to the pre-defined data structure and stored. The structured data may include the projection edge information and assertion edge information of the currently traversed node, as well as field information, node type, and node name of the currently traversed node. In this embodiment, the pre-defined data structure can be in tabular form. Table 1 is a schematic table of structured data.

[0176] Table 1. Schematic diagram of structured data

[0177]

[0178] The projection edge information and assertion edge information may include a set of output fields and a set of source fields.

[0179] The advantage of this setup is that, based on the preset data structure, the lineage information in the field can be represented and stored in a structured manner, making it easier for staff to view and analyze it later, and improving the efficiency of SQL statement analysis.

[0180] Understandably, in this embodiment, by obtaining the abstract syntax tree (AST) of the SQL statement, the data tables involved in the SQL statement are determined from the nodes of the AST, thus obtaining the metadata information of the data tables. Metadata information can represent the attribute and field information of the data tables. When determining data lineage information based on metadata information, it ensures that all fields in the data tables are considered, avoiding field loss or omission. Post-order traversal of the AST is performed, achieving a static analysis-based architecture that does not rely on the support of an execution engine. This bottom-up lineage construction approach can handle complex subquery scenarios, covering all SQL scenarios, ensuring no lineage information is omitted, and improving the accuracy of data lineage determination.

[0181] Figure 8 This is a flowchart illustrating another data lineage determination method based on structured query language provided in this embodiment of the disclosure.

[0182] In this embodiment, the data lineage information of the Structured Query Language (SCL) statement is obtained based on metadata and field information. This can be further refined as follows: the field lineage information of the currently traversed node is determined based on metadata and field information of the currently traversed node. The field lineage information represents the source and destination of fields in the data table, and includes projection edge information and assertion edge information. Projection edge information represents the mapping relationship between the node's output field and the source table field, while assertion edge information represents the mapping relationship between the node's output field and the judgment field. The source table field represents the output field of the leaf node. If the post-order traversal of the abstract syntax tree is determined to be complete, the field lineage information of the currently traversed node is determined, which is the data lineage information of the SCL statement.

[0183] like Figure 8 As shown, the method includes the following steps:

[0184] S801. Obtain the abstract syntax tree corresponding to the statement of the structured query language; wherein, the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of tree structure, and the abstract syntax tree includes at least two nodes.

[0185] For example, this step can refer to step S401 above, and will not be repeated here.

[0186] S802. Determine the data table corresponding to the node in the abstract syntax tree and obtain the metadata information of the data table; wherein, the metadata information is used to represent the attribute information and field information of the data table.

[0187] For example, this step can refer to step S402 above, and will not be repeated here.

[0188] S803, Obtain field information of nodes in the abstract syntax tree.

[0189] For example, this step can refer to step S403 above, and will not be repeated here.

[0190] S804. Based on the metadata information and the field information of the currently traversed node, determine the lineage information of the field of the currently traversed node.

[0191] For example, a post-order traversal of the nodes in the AST is performed. That is, for each node in the AST, its child nodes need to be traversed first. Only after the field lineage information of all the child nodes of the node is determined can the field lineage information of the node itself be determined. Starting from the leaf nodes, the field lineage information of the leaf nodes can be determined first based on the metadata information and the field information of the leaf nodes. Then, the field information of the parent nodes of the leaf nodes is determined, and the field lineage information of the parent nodes of the leaf nodes is determined based on the field information of the parent nodes and the field lineage information of the leaf nodes. In this embodiment, the field lineage information of each node can be stored after it is determined. For example, it can be stored in a queue, a database, or a stack structure.

[0192] In this embodiment, the method further includes: determining the parent node of the currently traversed node as the current parent node, and determining all child nodes of the current parent node as child nodes to be aggregated; wherein the child nodes to be aggregated include the currently traversed node; storing the field lineage information of the currently traversed node at the top position of a preset stack structure; if the next traversed node is a child node to be aggregated, determining the field lineage information of the child node to be aggregated, and storing the field lineage information of the child node to be aggregated at the top position of a preset stack structure.

[0193] Specifically, the parent node of the currently traversed node is determined as the current parent node. The child nodes of the current parent node are then determined, and all child nodes of the current parent node are identified as the child nodes to be aggregated. Since the currently traversed node is also a child node of the current parent node, the child nodes to be aggregated can include the currently traversed node.

[0194] A stack structure is pre-set to store the field lineage information of the currently traversed node. In this embodiment, a last-in-first-out (LIFO) stack structure is used. After obtaining the field lineage information of the currently traversed node, it can be stored at the top position of the pre-set stack structure. It is then determined whether the next traversed node is a child node to be aggregated, i.e., whether it is another child node of the current parent node. If so, it is determined that the field lineage information of the current parent node's child nodes has not yet been fully determined, and the determination of the field lineage information of the next traversed node continues. That is, the field lineage information of the child node to be aggregated continues to be determined, and this field lineage information is stored at the top position of the pre-set stack structure. In other words, the field lineage information originally at the top of the stack is pushed down, and the field lineage information at the top position of the stack is updated.

[0195] Figure 9 This is a simplified diagram of the abstract syntax tree. Figure 9 In this process, the currently traversed node is the first leaf node, and the current parent node is the first intermediate node. After obtaining the lineage information of the child segments of the first leaf node, the lineage information of the second leaf node is determined. Figure 10 This is a schematic diagram of a stack structure. Figure 10 In this process, the lineage information of the first leaf node is first stored in a stack structure. Then, the lineage information of the second leaf node is determined and placed at the top of the stack, while the lineage information of the first leaf node is stored at the bottom of the stack.

[0196] The advantage of this setup is that by setting up a stack structure, the field lineage of nodes at the same level can be stored, making it easier to aggregate the field lineage information of nodes at the same level into the field lineage information of the parent node, thus realizing the bottom-up construction of the field lineage of the AST.

[0197] In this embodiment, the method further includes: if the next node traversed is not the child node to be aggregated, then the field lineage information at the top of the stack is taken out from the stack structure and used as the first field lineage information; if there is no field lineage information of the child node to be aggregated in the stack structure, then the first field lineage information is determined as the field lineage information of the current parent node.

[0198] Specifically, after storing the lineage information of the currently traversed node at the top of a pre-defined stack structure, if it is determined that the next traversed node is not a child node to be aggregated, then the lineage information of all child nodes of the current parent node can be considered determined. The lineage information of all child nodes of the current parent node is stored in the stack structure, thus determining the lineage information of the current parent node. Each time the lineage information of a parent node is determined, the stack structure is cleared, and the determined lineage information of the parent node is stored as a new child node to be aggregated in the stack structure. Therefore, when it is determined that the next traversed node is not a child node to be aggregated, the stack structure only contains the lineage information of each child node to be aggregated of the current parent node.

[0199] Retrieve the lineage information from the top of the stack structure, using it as the first lineage information. Check if any other lineage information exists in the stack structure. If not, it is assumed that the current parent node has only one child node. The lineage information of the current parent node can be determined based on the first lineage information and the current parent node's lineage information. Remove the first lineage information from the stack structure, and store the current parent node's lineage information at the top of the stack structure.

[0200] The advantage of this setup is that it determines the field lineage information of the current parent node based on the field lineage information in the stack structure, thereby enabling orderly management of the field lineage information and improving the efficiency and accuracy of determining the field lineage information of the current parent node.

[0201] In this embodiment, the method further includes: if it is determined that there is field lineage information of the child node to be aggregated in the stack structure, then the second field lineage information following the first field lineage information is taken out from the stack structure; the first field lineage information and the second field lineage information are merged to generate the third field lineage information; if there is no field lineage information of the child node to be aggregated in the stack structure, then the field lineage information of the current parent node is determined according to the third field lineage information; if there is still field lineage information of the child node to be aggregated in the stack structure, then the fourth field lineage information following the second field lineage information is taken out from the stack structure, and the third field lineage information and the fourth field lineage information are merged, until there is no field lineage information of the child node to be aggregated in the stack structure.

[0202] Specifically, after retrieving the first field lineage information, if it is determined that there are still lineage information fields for child nodes to be aggregated in the stack structure, then the second field lineage information, which follows the first field lineage information, is retrieved from the stack structure; that is, the lineage information at the new top position of the stack is retrieved. The first and second field lineage information are then merged to generate the third field lineage information. For example, the union of the mapping relationships between the first and second field lineage information can be used to determine the third field lineage information.

[0203] Next, check if there is still field lineage information of child nodes to be aggregated in the stack structure. If not, the field lineage information of the current parent node can be determined based on the third field lineage information and the field information of the current parent node.

[0204] If the stack structure still contains lineage information for child nodes to be aggregated, then the fourth lineage information, following the second lineage information, is retrieved from the stack structure; that is, the lineage information at the new top position of the stack is retrieved. The third and fourth lineage information are merged to obtain the fifth lineage information. If the stack structure does not contain lineage information for child nodes to be aggregated, the lineage information of the current parent node can be determined based on the fifth lineage information and the lineage information of the current parent node. If lineage information still exists, the lineage information at the new top position of the stack is retrieved and merged until the stack structure no longer contains lineage information for child nodes to be aggregated.

[0205] The advantage of this setup is that by iteratively obtaining the lineage information of the top position of the stack, the lineage information of all child nodes of the current parent node can be merged, avoiding the omission of the lineage information of child nodes, improving the accuracy of determining the lineage information of the current parent node, and realizing recursive upward construction of the lineage information layer by layer.

[0206] S805. If it is determined that the post-order traversal of the abstract syntax tree is complete, then the field lineage information of the currently traversed node is determined, which is the data lineage information of the structured query language statement.

[0207] For example, if it is determined that the post-order traversal of the abstract syntax tree is complete, then the field lineage information at the top of the stack structure is the field lineage information of the top-level node in the AST. The table lineage information of the AST and the field lineage information of the top-level node in the AST are used to determine the data lineage information of the SQL statement.

[0208] Understandably, in this embodiment, the abstract syntax tree of the SQL statement is obtained, and the data table involved in the SQL statement is determined from the nodes of the abstract syntax tree, thus obtaining the metadata information of the data table. The metadata information can represent the attribute and field information of the data table. When determining data lineage information based on the metadata information, it can be ensured that all fields in the data table are considered. By setting a last-in-first-out (LIFO) stack structure, the loss or omission of field lineage information of child nodes can be avoided, improving the accuracy of data lineage determination.

[0209] Exemplary media

[0210] After introducing the methods of exemplary embodiments of this disclosure, the following references are made. Figure 11The storage medium of the exemplary embodiments of this disclosure will be described.

[0211] refer to Figure 11 As shown, the storage medium 1100 stores a program product for implementing the above-described method according to an embodiment of the present disclosure. This program product may be a portable compact disc read-only memory (CD-ROM) and includes program code, and can run on a terminal device, such as a personal computer. However, the program product of the present disclosure is not limited thereto.

[0212] The program product may employ any combination of one or more readable media. A readable medium may be a readable signal medium or a readable storage medium. A readable storage medium may be, for example, but not limited to, an electrical, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or any combination thereof. More specific examples of readable storage media (a non-exhaustive list) include: an electrical connection having one or more wires, a portable disk, a hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination thereof.

[0213] A readable signal medium may include a data signal propagated in baseband or as part of a carrier wave, carrying readable program code. This propagated data signal may take various forms, including but not limited to electromagnetic signals, optical signals, or any suitable combination thereof. A readable signal medium may also be any readable medium other than a readable storage medium.

[0214] Program code for performing the operations disclosed herein can be written in any combination of one or more programming languages, including object-oriented programming languages ​​such as Java and C++, and conventional procedural programming languages ​​such as C or similar languages. The program code can execute entirely on the user's computing device, partially on the user's computing device, partially on a remote computing device, or entirely on a remote computing device or server. In cases involving remote computing devices, the remote computing devices can be connected to the user's computing device via any type of network, including a local area network (LAN) or a wide area network (WAN).

[0215] Exemplary device

[0216] Having introduced the medium of exemplary embodiments of this disclosure, the following references are made to... Figure 12The data lineage determination apparatus 1200 based on structured query language according to an exemplary embodiment of the present disclosure is described. It is used to implement the method in any of the above method embodiments. Its implementation principle and technical effect are similar, and will not be repeated here.

[0217] like Figure 12 As shown in the diagram, this embodiment provides a structural schematic of a data lineage determination device based on structured query language, including:

[0218] The acquisition module 1201 is used to acquire the abstract syntax tree corresponding to the statement of the structured query language; wherein the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of a tree structure, and the abstract syntax tree includes at least two nodes;

[0219] The first determining module 1202 is used to determine the data table corresponding to the node in the abstract syntax tree and obtain the metadata information of the data table; wherein, the metadata information is used to represent the attribute information and field information of the data table;

[0220] The second determining module 1203 is used to obtain field information of nodes in the abstract syntax tree, and determine the data lineage information of the structured query language statement based on the metadata information and the field information; wherein, the field information includes at least one of the node's output field and the node's judgment field; the judgment field is used to represent the field in the judgment condition.

[0221] In one embodiment of this disclosure, the first determining module 1202 includes:

[0222] The acquisition unit is used to acquire table identification information of a data table from a node in the abstract syntax tree if it is determined that a node in the abstract syntax tree corresponds to a data table; wherein, in the abstract syntax tree, the node corresponding to the data table is a leaf node.

[0223] A receiving unit is configured to send a metadata retrieval instruction to a preset metadata database and receive metadata information of the data table sent by the metadata database; wherein the metadata database is used to store the metadata information of the data table, and the metadata retrieval instruction includes the table identifier information of the data table.

[0224] In one embodiment of this disclosure, the acquisition unit includes:

[0225] The traversal subunit is used to traverse the nodes in the abstract syntax tree. If it is determined that the currently traversed node in the abstract syntax tree corresponds to a data table, the table identifier information of the data table is obtained from the currently traversed node.

[0226] The set-determining subunit is used to determine the set of table identification information if it is determined that the node traversal of the abstract syntax tree is complete; wherein, the set of table identification information includes the table identification information of all data tables corresponding to the abstract syntax tree.

[0227] In one embodiment of this disclosure, traversing the sub-units is specifically used for:

[0228] The abstract syntax tree is subjected to a preorder traversal of nodes. If, according to the preset structured query language syntax file, it is determined that there is no data table in the child nodes of the currently traversed node, the traversal of the child nodes of the currently traversed node is skipped. The structured query language syntax file is used to represent the statement writing rules of the structured query language.

[0229] In one embodiment of this disclosure, the metadata retrieval instruction includes a set of table identification information, and the metadata retrieval instruction is used to retrieve the metadata information of all data tables corresponding to the abstract syntax tree in batches.

[0230] In one embodiment of this disclosure, it further includes:

[0231] The output module is used to output the data lineage information from the root node of the abstract syntax tree.

[0232] In one embodiment of this disclosure, the second determining module 1203 includes:

[0233] The post-order traversal unit is used to perform post-order traversal processing on the abstract syntax tree and extract field information from the currently traversed node.

[0234] In one embodiment of this disclosure, the second determining module 1203 includes:

[0235] The first determining unit is configured to determine the field lineage information of the currently traversed node based on the metadata information and the field information of the currently traversed node; wherein the field lineage information is used to represent the source and destination of the field in the data table, and the field lineage information includes projection edge information and assertion edge information; the projection edge information is used to represent the mapping relationship between the output field of the node and the source table field, the assertion edge information is used to represent the mapping relationship between the output field of the node and the judgment field, and the source table field is used to represent the output field of the leaf node;

[0236] The second determining unit is used to determine the field lineage information of the currently traversed node as the data lineage information of the statement in the structured query language if it is determined that the post-order traversal of the abstract syntax tree is completed.

[0237] In one embodiment of this disclosure, it further includes:

[0238] The third determining module is used to determine the table lineage information of the currently traversed node after determining the field lineage information of the abstract syntax tree if the post-order traversal of the abstract syntax tree is completed; and to determine the table lineage information of the traversed node when the pre-order traversal is completed; wherein the pre-order traversal is either a pre-order traversal or a post-order traversal, and the table lineage information is used to represent the source table and destination table of the field.

[0239] The fourth determining module is used to determine the data lineage information of the structured query language statement based on the table lineage information and the field lineage information.

[0240] In one embodiment of this disclosure, the statement type of the currently traversed node is a data table;

[0241] The first defined unit includes:

[0242] The first determining subunit is used to determine all fields in the data table of the currently traversed node as metadata fields based on the metadata information of the data table in the currently traversed node.

[0243] The second determining subunit is used to obtain the mapping relationship between the metadata field and the output field of the currently traversed node based on the output field in the field information of the currently traversed node, and to determine the mapping relationship between the metadata field and the output field of the currently traversed node as the projection edge information of the currently traversed node.

[0244] In one embodiment of this disclosure, the statement type of the currently traversed node is a subquery;

[0245] The first defined unit includes:

[0246] The third determining subunit is used to determine the child node of the currently traversed node as the current child node, obtain the lineage information of the current child node, and obtain the field information of the currently traversed node.

[0247] The fourth determining subunit is used to determine the field lineage information of the currently traversed node based on the field information of the currently traversed node and the field lineage information of the current child node.

[0248] The fifth determining subunit is used to determine the parent node of the currently traversed node if it is determined that the post-order traversal of the abstract syntax tree is not completed, and to determine the parent node of the currently traversed node as the current parent node.

[0249] The sixth determining subunit is used to obtain the field information of the current parent node, and determine the field lineage information of the current parent node based on the field lineage information of the currently traversed node and the field information of the current parent node, until the post-order traversal of the abstract syntax tree is completed.

[0250] In one embodiment of this disclosure, the fourth determining subunit is specifically used for:

[0251] If the field information of the currently traversed node contains a preset field identifier, then all the projected edge information in the lineage information of the current child node will be determined as the projected edge information of the currently traversed node.

[0252] In one embodiment of this disclosure, the fourth determining subunit is specifically used for:

[0253] If the field information of the currently traversed node contains a database table name and a field name corresponding to the database table name, then the field lineage information corresponding to the database table name is determined from the field lineage information of the current child node, and the field lineage information corresponding to the database table name is determined as the target lineage information.

[0254] Based on the projection edge information corresponding to the field name corresponding to the database table name in the target lineage information, the projection edge information of the currently traversed node is determined.

[0255] In one embodiment of this disclosure, the fourth determining subunit is specifically used for:

[0256] If the field information of the currently traversed node contains only the field name, then the projection edge information corresponding to the field name is determined from the lineage information of the current child node.

[0257] Based on the projection edge information corresponding to the field name in the lineage information of the current child node, the projection edge information of the currently traversed node is determined.

[0258] In one embodiment of this disclosure, the fourth determining subunit is specifically used for:

[0259] If the field information of the currently traversed node contains a function expression, then determine the field name in the function expression;

[0260] From the lineage information of the current child node, determine the projection edge information corresponding to the field name in the function expression;

[0261] Based on the projection edge information corresponding to the field name in the function expression in the lineage information of the current child node, the projection edge information of the currently traversed node is determined.

[0262] In one embodiment of this disclosure, the first determining unit is specifically used for:

[0263] If the field information of the currently traversed node includes a judgment field, then the output field in the field information of the currently traversed node is determined.

[0264] Construct a mapping relationship between the judgment field and the output field, which serves as the assertion edge information of the currently traversed node.

[0265] In one embodiment of this disclosure, it further includes:

[0266] The storage module is used to generate structured data of the currently traversed node based on the projection edge information and assertion edge information of the currently traversed node, and to store the structured data; wherein the structured data is a preset data structure, and the structured data includes the projection edge information and assertion edge information of the currently traversed node, as well as the field information, node type and node name of the currently traversed node.

[0267] In one embodiment of this disclosure, it further includes:

[0268] The child node determination module is used to determine the parent node of the currently traversed node as the current parent node, and to determine all child nodes of the current parent node as child nodes to be aggregated; wherein, the child nodes to be aggregated include the currently traversed node;

[0269] The first storage module is used to store the lineage information of the currently traversed node at the top position of the stack in a preset stack structure.

[0270] The second storage module is used to determine the lineage information of the child node to be aggregated if the next node traversed is the child node to be aggregated, and store the lineage information of the child node to be aggregated at the top position of the stack in the preset stack structure.

[0271] In one embodiment of this disclosure, it further includes:

[0272] The first extraction module is used to extract the lineage information of the top position of the stack structure as the first lineage information if the next node traversed is not the child node to be aggregated.

[0273] The information determination module is used to determine the field lineage information of the current parent node based on the first field lineage information if the field lineage information of the child node to be aggregated does not exist in the stack structure.

[0274] In one embodiment of this disclosure, it further includes:

[0275] The second retrieval module is used to retrieve the second field lineage information that is ranked after the first field lineage information from the stack structure if it is determined that the field lineage information of the child node to be aggregated exists in the stack structure.

[0276] The merging module is used to merge the first field of bloodline information and the second field of bloodline information to generate the third field of bloodline information;

[0277] The lineage determination module is used to determine the lineage information of the current parent node based on the third lineage information if the lineage information of the child node to be aggregated does not exist in the stack structure.

[0278] The repetitive execution module is used to, if the field lineage information of the child node to be aggregated still exists in the stack structure, continue to retrieve the fourth field lineage information after the second field lineage information from the stack structure, merge the third field lineage information with the fourth field lineage information, until the field lineage information of the child node to be aggregated no longer exists in the stack structure.

[0279] In one embodiment of this disclosure, the acquisition module 1201 is specifically used for:

[0280] Obtain the statement from the Structured Query Language;

[0281] Based on a preset syntax analysis tool, an abstract syntax tree corresponding to the statements of the structured query language is constructed.

[0282] Exemplary computing device

[0283] Having described the methods, media, and apparatus of exemplary embodiments of this disclosure, the following references... Figure 13 A computing device according to an exemplary embodiment of the present disclosure will be described.

[0284] Figure 13 The computing device 1300 shown is merely an example and should not be construed as limiting the functionality and scope of use of the embodiments disclosed herein.

[0285] like Figure 13 As shown, the computing device 1300 is presented in the form of a general-purpose computing device. The components of the computing device 1300 may include, but are not limited to: at least one processing unit 1301, at least one storage unit 1302, and a bus 1303 connecting different system components (including the processing unit 1301 and the storage unit 1302). The at least one storage unit 402 stores computer-executable instructions; the at least one processing unit 401 includes a processor that executes the computer-executable instructions to implement the methods described above.

[0286] Bus 1303 includes a data bus, a control bus, and an address bus.

[0287] Storage unit 1302 may include readable media in the form of volatile memory, such as random access memory (RAM) 13021 and / or cache memory 13022, and may further include readable media in the form of non-volatile memory, such as read-only memory (ROM) 13023.

[0288] Storage unit 1302 may also include a program / utility 13025 having a set (at least one) of program modules 13024, such program modules 13024 including but not limited to: operating system, one or more application programs, other program modules and program data, each or some combination of these examples may include an implementation of a network environment.

[0289] The computing device 1300 can also communicate with one or more external devices 1304 (e.g., keyboard, pointing device, etc.). This communication can be performed via the input / output (I / O) interface 1305. Furthermore, the computing device 1300 can also communicate with one or more networks (e.g., local area network (LAN), wide area network (WAN), and / or public networks, such as the Internet) via a network adapter 1306. Figure 13 As shown, network adapter 1306 communicates with other modules of computing device 1300 via bus 1303. It should be understood that, although not shown in the figure, other hardware and / or software modules may be used in conjunction with computing device 1300, including but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, and data backup storage systems.

[0290] It should be noted that although several units / modules or sub-units / modules of the data lineage determination device based on Structured Query Language have been mentioned in the detailed description above, this division is merely exemplary and not mandatory. In fact, according to embodiments of this disclosure, the features and functions of two or more units / modules described above can be embodied in one unit / module. Conversely, the features and functions of one unit / module described above can be further divided and embodied by multiple units / modules.

[0291] Furthermore, although the operations of the methods disclosed herein are described in a specific order in the accompanying drawings, this does not require or imply that these operations must be performed in that specific order, or that all of the operations shown must be performed to achieve the desired result. Additionally or alternatively, certain steps may be omitted, multiple steps may be combined into one step, and / or one step may be broken down into multiple steps.

[0292] While the spirit and principles of this disclosure have been described with reference to several specific embodiments, it should be understood that this disclosure is not limited to the disclosed specific embodiments, and the division of aspects does not imply that features in these aspects cannot be combined for benefit; such division is merely for convenience of expression. This disclosure is intended to cover various modifications and equivalent arrangements included within the spirit and scope of the appended claims.

Claims

1. A method for determining data lineage based on Structured Query Language, comprising: Obtain the abstract syntax tree corresponding to the statement of the structured query language; wherein the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of a tree structure, and the abstract syntax tree includes at least two nodes; The data table corresponding to the node in the abstract syntax tree is determined, and the metadata information of the data table is obtained; wherein, the metadata information is used to represent the attribute information and field information of the data table; Obtain the field information of the nodes in the abstract syntax tree, and determine the data lineage information of the statement in the structured query language based on the metadata information and the field information; wherein, the field information includes at least one of the node's output field and the node's judgment field; the judgment field is used to represent the field in the judgment condition; Obtaining field information of nodes in the abstract syntax tree includes: The abstract syntax tree is subjected to a post-order traversal to extract field information from the currently traversed node; Based on the metadata information and the field information, the data lineage information of the statements in the structured query language is obtained, including: Based on the metadata information and the field information of the currently traversed node, the field lineage information of the currently traversed node is determined; wherein, the field lineage information is used to indicate the source and destination of the field in the data table, and the field lineage information includes projection edge information and assertion edge information; the projection edge information is used to indicate the mapping relationship between the node's output field and the source table field, the assertion edge information is used to indicate the mapping relationship between the node's output field and the judgment field, and the source table field is used to indicate the output field of the leaf node; If it is determined that the post-order traversal of the abstract syntax tree is complete, then the field lineage information of the currently traversed node is determined to be the data lineage information of the statement in the structured query language.

2. The method according to claim 1, determining the data table corresponding to the node in the abstract syntax tree and obtaining the metadata information of the data table, includes: If it is determined that a node in the abstract syntax tree corresponds to a data table, then the table identifier information of the data table is obtained from the node in the abstract syntax tree; wherein, in the abstract syntax tree, the node corresponding to the data table is a leaf node; A metadata retrieval instruction is sent to a preset metadata database, and the metadata information of the data table sent by the metadata database is received; wherein, the metadata database is used to store the metadata information of the data table, and the metadata retrieval instruction includes the table identifier information of the data table.

3. The method according to claim 2, wherein if it is determined that a node in the abstract syntax tree corresponds to a data table, then the table identifier information of the data table is obtained from the node in the abstract syntax tree, including: Traverse the nodes in the abstract syntax tree. If it is determined that the currently traversed node in the abstract syntax tree corresponds to a data table, then obtain the table identifier information of the data table from the currently traversed node. If it is determined that the node traversal of the abstract syntax tree is complete, then the set of table identification information is determined; wherein, the set of table identification information includes the table identification information of all data tables corresponding to the abstract syntax tree.

4. The method according to claim 3, traversing the nodes in the abstract syntax tree, includes: The abstract syntax tree is subjected to a preorder traversal of nodes. If, according to the preset structured query language syntax file, it is determined that there is no data table in the child nodes of the currently traversed node, the traversal of the child nodes of the currently traversed node is skipped. The structured query language syntax file is used to represent the statement writing rules of the structured query language.

5. The method according to claim 3, wherein the metadata retrieval instruction includes a set of table identification information, and the metadata retrieval instruction is used to batch retrieve metadata information of all data tables corresponding to the abstract syntax tree.

6. The method according to claim 1, further comprising: The data lineage information is output from the root node of the abstract syntax tree.

7. The method according to claim 1, further comprising, after determining the field lineage information of the currently traversed node if it is determined that the post-order traversal of the abstract syntax tree is complete: The abstract syntax tree is traversed in a preset manner to determine the table lineage information of the traversed nodes when the preset traversal is completed; wherein the preset traversal is either a preorder traversal or a postorder traversal, and the table lineage information is used to represent the source table and destination table of the field. Based on the table lineage information and the field lineage information, determine the data lineage information of the statement in the structured query language.

8. The method according to claim 1, wherein the statement type of the currently traversed node is a data table; Based on the metadata information and the field information of the currently traversed node, determine the lineage information of the currently traversed node, including: Based on the metadata information of the data table in the currently traversed node, all fields in the data table in the currently traversed node are identified as metadata fields; Based on the output fields in the field information of the currently traversed node, the mapping relationship between the metadata field and the output fields of the currently traversed node is obtained, and the mapping relationship between the metadata field and the output fields of the currently traversed node is determined as the projection edge information of the currently traversed node.

9. The method according to claim 1, wherein the statement type of the currently traversed node is a subquery; Based on the metadata information and the field information of the currently traversed node, determine the lineage information of the currently traversed node, including: Determine the child node of the currently traversed node as the current child node, obtain the lineage information of the current child node, and obtain the field information of the currently traversed node; Based on the field information of the currently traversed node and the field lineage information of the current child node, determine the field lineage information of the currently traversed node; If it is determined that the post-order traversal of the abstract syntax tree is not complete, then the parent node of the currently traversed node is determined, and the parent node of the currently traversed node is determined as the current parent node. Obtain the field information of the current parent node, and determine the field lineage information of the current parent node based on the field lineage information of the currently traversed node and the field information of the current parent node, until the post-order traversal of the abstract syntax tree is completed.

10. The method according to claim 9, wherein determining the field lineage information of the currently traversed node based on the field information of the currently traversed node and the field lineage information of the currently traversed child node includes: If the field information of the currently traversed node contains a preset field identifier, then all the projected edge information in the lineage information of the current child node will be determined as the projected edge information of the currently traversed node.

11. The method according to claim 9, wherein determining the field lineage information of the currently traversed node based on the field information of the currently traversed node and the field lineage information of the currently traversed child node includes: If the field information of the currently traversed node contains a database table name and a field name corresponding to the database table name, then the field lineage information corresponding to the database table name is determined from the field lineage information of the current child node, and the field lineage information corresponding to the database table name is determined as the target lineage information. Based on the projection edge information corresponding to the field name corresponding to the table name in the target lineage information, the projection edge information of the currently traversed node is determined.

12. The method according to claim 11, wherein determining the field lineage information of the currently traversed node based on the field information of the currently traversed node and the field lineage information of the currently traversed child node comprises: If the field information of the currently traversed node contains only the field name, then the projection edge information corresponding to the field name is determined from the lineage information of the current child node. Based on the projection edge information corresponding to the field name in the lineage information of the current child node, the projection edge information of the currently traversed node is determined.

13. The method according to claim 9, wherein determining the field lineage information of the currently traversed node based on the field information of the currently traversed node and the field lineage information of the currently traversed child node comprises: If the field information of the currently traversed node contains a function expression, then determine the field name in the function expression; From the lineage information of the current child node, determine the projection edge information corresponding to the field name in the function expression; Based on the projection edge information corresponding to the field name in the function expression in the lineage information of the current child node, the projection edge information of the currently traversed node is determined.

14. The method according to any one of claims 8-13, wherein determining the lineage information of the currently traversed node based on the metadata information and the field information of the currently traversed node includes: If the field information of the currently traversed node includes a judgment field, then the output field in the field information of the currently traversed node is determined. Construct a mapping relationship between the judgment field and the output field, which serves as the assertion edge information of the currently traversed node.

15. The method of claim 14, further comprising: Based on the projection edge information and assertion edge information of the currently traversed node, structured data of the currently traversed node is generated and stored; wherein, the structured data is a preset data structure, and the structured data includes the projection edge information and assertion edge information of the currently traversed node, as well as the field information, node type and node name of the currently traversed node.

16. The method according to claim 1, further comprising: The parent node of the currently traversed node is determined as the current parent node, and all child nodes of the current parent node are determined as child nodes to be aggregated; wherein, the child nodes to be aggregated include the currently traversed node; The lineage information of the currently traversed node is stored at the top of the stack in a preset stack structure. If the next node encountered during traversal is the child node to be aggregated, then the lineage information of the child node to be aggregated is determined, and the lineage information of the child node to be aggregated is stored at the top position of the preset stack structure.

17. The method of claim 16, further comprising: If the next node traversed is not the child node to be aggregated, then the lineage information at the top of the stack is taken out from the stack structure and used as the first lineage information. If the lineage information of the child node to be aggregated does not exist in the stack structure, the lineage information of the current parent node is determined based on the first lineage information.

18. The method of claim 17, further comprising: If it is determined that the lineage information of the child node to be aggregated exists in the stack structure, then the second lineage information following the first lineage information is retrieved from the stack structure. The first field of bloodline information and the second field of bloodline information are merged to generate the third field of bloodline information; If the lineage information of the child node to be aggregated does not exist in the stack structure, then the lineage information of the current parent node is determined based on the third lineage information. If the lineage information of the child node to be aggregated still exists in the stack structure, then the fourth lineage information following the second lineage information is taken out from the stack structure, and the third lineage information is merged with the fourth lineage information, until the lineage information of the child node to be aggregated no longer exists in the stack structure.

19. The method according to claim 1, wherein obtaining the abstract syntax tree corresponding to a statement in a structured query language comprises: Obtain the statement from the Structured Query Language; Based on a preset syntax analysis tool, an abstract syntax tree corresponding to the statements of the structured query language is constructed.

20. A data lineage determination device based on Structured Query Language, comprising: The acquisition module is used to acquire the abstract syntax tree corresponding to the statement of the structured query language; wherein the abstract syntax tree is used to represent the statement of the structured query language in a hierarchical relationship of tree structure, and the abstract syntax tree includes at least two nodes; The first determining module is used to determine the data table corresponding to the node in the abstract syntax tree and obtain the metadata information of the data table; wherein, the metadata information is used to represent the attribute information and field information of the data table; The second determining module is used to obtain field information of nodes in the abstract syntax tree, and determine the data lineage information of the structured query language statement based on the metadata information and the field information; wherein, the field information includes at least one of the node's output field and the node's judgment field; the judgment field is used to represent the field in the judgment condition; The second determining module includes: The post-order traversal unit is used to perform post-order traversal processing on the abstract syntax tree and extract field information from the currently traversed node. The first determining unit is configured to determine the field lineage information of the currently traversed node based on the metadata information and the field information of the currently traversed node; wherein the field lineage information is used to represent the source and destination of the field in the data table, and the field lineage information includes projection edge information and assertion edge information; the projection edge information is used to represent the mapping relationship between the output field of the node and the source table field, the assertion edge information is used to represent the mapping relationship between the output field of the node and the judgment field, and the source table field is used to represent the output field of the leaf node; The second determining unit is used to determine the field lineage information of the currently traversed node as the data lineage information of the statement in the structured query language if it is determined that the post-order traversal of the abstract syntax tree is completed.

21. The apparatus according to claim 20, wherein the first determining module comprises: The acquisition unit is used to acquire table identification information of a data table from a node in the abstract syntax tree if it is determined that a node in the abstract syntax tree corresponds to a data table; wherein, in the abstract syntax tree, the node corresponding to the data table is a leaf node. A receiving unit is configured to send a metadata retrieval instruction to a preset metadata database and receive metadata information of the data table sent by the metadata database; wherein the metadata database is used to store the metadata information of the data table, and the metadata retrieval instruction includes the table identifier information of the data table.

22. The apparatus according to claim 21, wherein the acquiring unit comprises: The traversal subunit is used to traverse the nodes in the abstract syntax tree. If it is determined that the currently traversed node in the abstract syntax tree corresponds to a data table, the table identifier information of the data table is obtained from the currently traversed node. The set-determining subunit is used to determine the set of table identification information if it is determined that the node traversal of the abstract syntax tree is complete; wherein, the set of table identification information includes the table identification information of all data tables corresponding to the abstract syntax tree.

23. The apparatus according to claim 22, wherein the traversal subunit is specifically used for: The abstract syntax tree is subjected to a preorder traversal of its nodes. If, according to a preset structured query language syntax file, it is determined that the child nodes of the currently traversed node do not contain a data table, then the traversal of the child nodes of the currently traversed node is skipped; wherein, The syntax file of the Structured Query Language is used to represent the statement writing rules of the Structured Query Language.

24. The apparatus according to claim 22, wherein the metadata acquisition instruction includes a set of table identification information, and the metadata acquisition instruction is used to acquire the metadata information of all data tables corresponding to the abstract syntax tree in batches.

25. The apparatus of claim 20, further comprising: The output module is used to output the data lineage information from the root node of the abstract syntax tree.

26. The apparatus of claim 20, further comprising: The third determining module is used to determine the table lineage information of the currently traversed node after determining the field lineage information of the abstract syntax tree if the post-order traversal of the abstract syntax tree is completed; and to determine the table lineage information of the traversed node when the pre-order traversal is completed; wherein the pre-order traversal is either a pre-order traversal or a post-order traversal, and the table lineage information is used to represent the source table and destination table of the field. The fourth determining module is used to determine the data lineage information of the structured query language statement based on the table lineage information and the field lineage information.

27. The apparatus according to claim 20, wherein the statement type of the currently traversed node is a data table; The first determining unit includes: The first determining subunit is used to determine all fields in the data table of the currently traversed node as metadata fields based on the metadata information of the data table in the currently traversed node. The second determining subunit is used to obtain the mapping relationship between the metadata field and the output field of the currently traversed node based on the output field in the field information of the currently traversed node, and to determine the mapping relationship between the metadata field and the output field of the currently traversed node as the projection edge information of the currently traversed node.

28. The apparatus according to claim 20, wherein the statement type of the currently traversed node is a subquery; The first determining unit includes: The third determining subunit is used to determine the child node of the currently traversed node as the current child node, obtain the lineage information of the current child node, and obtain the field information of the currently traversed node. The fourth determining subunit is used to determine the field lineage information of the currently traversed node based on the field information of the currently traversed node and the field lineage information of the current child node. The fifth determining subunit is used to determine the parent node of the currently traversed node if it is determined that the post-order traversal of the abstract syntax tree is not completed, and to determine the parent node of the currently traversed node as the current parent node. The sixth determining subunit is used to obtain the field information of the current parent node, and determine the field lineage information of the current parent node based on the field lineage information of the currently traversed node and the field information of the current parent node, until the post-order traversal of the abstract syntax tree is completed.

29. The apparatus according to claim 28, wherein the fourth determining subunit is specifically configured to: If the field information of the currently traversed node contains a preset field identifier, then all the projected edge information in the lineage information of the current child node will be determined as the projected edge information of the currently traversed node.

30. The apparatus according to claim 28, wherein the fourth determining subunit is specifically configured to: If the field information of the currently traversed node contains a database table name and a field name corresponding to the database table name, then the field lineage information corresponding to the database table name is determined from the field lineage information of the current child node, and the field lineage information corresponding to the database table name is determined as the target lineage information. Based on the projection edge information corresponding to the field name corresponding to the table name in the target lineage information, the projection edge information of the currently traversed node is determined.

31. The apparatus according to claim 30, wherein the fourth determining subunit is specifically used for: If the field information of the currently traversed node contains only the field name, then the projection edge information corresponding to the field name is determined from the lineage information of the current child node. Based on the projection edge information corresponding to the field name in the lineage information of the current child node, the projection edge information of the currently traversed node is determined.

32. The apparatus according to claim 28, wherein the fourth determining subunit is specifically used for: If the field information of the currently traversed node contains a function expression, then determine the field name in the function expression; From the lineage information of the current child node, determine the projection edge information corresponding to the field name in the function expression; Based on the projection edge information corresponding to the field name in the function expression in the lineage information of the current child node, the projection edge information of the currently traversed node is determined.

33. The apparatus according to any one of claims 27-32, wherein the first determining unit is specifically configured to: If the field information of the currently traversed node includes a judgment field, then the output field in the field information of the currently traversed node is determined. Construct a mapping relationship between the judgment field and the output field, which serves as the assertion edge information of the currently traversed node.

34. The apparatus of claim 33, further comprising: The storage module is used to generate structured data of the currently traversed node based on the projection edge information and assertion edge information of the currently traversed node, and to store the structured data; wherein the structured data is a preset data structure, and the structured data includes the projection edge information and assertion edge information of the currently traversed node, as well as the field information, node type and node name of the currently traversed node.

35. The apparatus of claim 20, further comprising: The child node determination module is used to determine the parent node of the currently traversed node as the current parent node, and to determine all child nodes of the current parent node as child nodes to be aggregated; wherein, the child nodes to be aggregated include the currently traversed node; The first storage module is used to store the lineage information of the currently traversed node at the top position of the stack in a preset stack structure. The second storage module is used to determine the lineage information of the child node to be aggregated if the next node traversed is the child node to be aggregated, and store the lineage information of the child node to be aggregated at the top position of the stack in the preset stack structure.

36. The apparatus of claim 35, further comprising: The first extraction module is used to extract the lineage information of the top position of the stack structure as the first lineage information if the next node traversed is not the child node to be aggregated. The information determination module is used to determine the field lineage information of the current parent node based on the first field lineage information if the field lineage information of the child node to be aggregated does not exist in the stack structure.

37. The apparatus of claim 36, further comprising: The second retrieval module is used to retrieve the second field lineage information that is ranked after the first field lineage information from the stack structure if it is determined that the field lineage information of the child node to be aggregated exists in the stack structure. The merging module is used to merge the first field of bloodline information and the second field of bloodline information to generate the third field of bloodline information; The lineage determination module is used to determine the lineage information of the current parent node based on the third lineage information if the lineage information of the child node to be aggregated does not exist in the stack structure. The repetitive execution module is used to, if the field lineage information of the child node to be aggregated still exists in the stack structure, continue to retrieve the fourth field lineage information after the second field lineage information from the stack structure, merge the third field lineage information with the fourth field lineage information, until the field lineage information of the child node to be aggregated no longer exists in the stack structure.

38. The apparatus according to claim 20, wherein the acquisition module is specifically used for: Obtain the statement from the Structured Query Language; Based on a preset syntax analysis tool, an abstract syntax tree corresponding to the statements of the structured query language is constructed.

39. A computer-readable storage medium storing computer-executable instructions that, when executed by a processor, implement the method as described in any one of claims 1 to 19.

40. A computing device, comprising: At least one processor; and a memory communicatively connected to the at least one processor; The memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, cause the computing device to perform the method as described in any one of claims 1 to 19.

Images