A data lineage analysis method based on SQL parsing

By traversing the abstract syntax tree of SQL statements layer by layer, establishing the blood relationship between tables and fields and fields, solving the problem that data relationship cannot be displayed in the existing technology, and achieving a comprehensive display and complete data relationship. sex.

CN118916347BActive Publication Date: 2024-12-06SINGULARITY INTELLIGENCE (BEIJING) TECH CO LTD
View PDF 8 Cites 0 Cited by

Patent Information

Application Number
CN202411409168.8
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-10-10
Publication Date
2024-12-06
Estimated Expiration
2044-10-10

AI Technical Summary

Technical Problem

The data blood relationship graph generated based on abstract syntax tree (AST) in the prior art cannot display the relationship between data, especially the relationship between two fields of the same table, and lacks a comprehensive display of the blood relationship between data.

Method used

By traversing the abstract syntax tree of SQL statements layer by layer, establish the table blood relationship between tables and fields and the field blood relationship between fields, including the relationship between descendants and brother nodes, and build a more comprehensive data blood relationship diagram.

Benefits of technology

A comprehensive display of data blood relationships is achieved, which can display more relationships between tables and fields, ensuring that the first table or first field of each blood relationship is the original source of its blood relationship, thereby providing a complete display of data relationships.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN118916347B_ABST
    Figure CN118916347B_ABST
Patent Text Reader

Abstract

The present invention relates to the field of digital data processing technology, and in particular to a data lineage analysis method based on SQL parsing. The method comprises: obtaining AST0; starting from its root node, traversing each node of AST0 layer by layer; if node i,j is the node corresponding to the table, and there is a node corresponding to the table in its descendant nodes and sibling nodes, then the corresponding table blood relationship is established; if node i,j If a node is a node corresponding to a field, and there is a node corresponding to the field in its descendant nodes, sibling nodes, and associated nodes, then the corresponding field lineage relationship is established; the final table lineage relationship set corresponding to the target SQL statement is obtained according to the table lineage relationship set corresponding to AST0; the final field lineage relationship set corresponding to the target SQL statement is obtained according to the field lineage relationship set corresponding to AST0. The present invention can build a more comprehensive data lineage relationship based on AST.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention relates to the technical field of electronic digital data processing, and in particular to a data lineage analysis method based on SQL parsing. Background Art

[0002] Existing technologies and tools have been involved in analyzing data lineage. For example, Apache Atlas is an open source data governance and metadata management platform that is widely used in the big data ecosystem. It provides rich management and query functions for data metadata, including data lineage. Another example is ANTLR (Another Tool for Language Recognition), which is a powerful parser generator that supports the generation of parsers in multiple languages. It can generate an abstract syntax tree (AST) based on SQL syntax, providing a basis for subsequent data lineage analysis. The data lineage graph generated based on AST in the existing technology mainly shows the upstream and downstream relationships between data. For example, a field is the upstream of another field, that is, a field is the data source of another field. However, the data lineage graph generated based on AST in the existing technology does not show the association relationship between data, for example, it does not show the association relationship between two fields in the same table. How to build a more comprehensive data lineage relationship based on AST to show users more relationships between data is an urgent problem to be solved. Summary of the invention

[0003] The purpose of the present invention is to provide a data lineage analysis method based on SQL parsing, so as to construct a more comprehensive data lineage relationship based on AST, so as to show users more relationships between data.

[0004] According to the present invention, a data lineage analysis method based on SQL parsing includes the following steps:

[0005] S100, obtaining an abstract syntax tree AST0 corresponding to the target SQL statement.

[0006] S200, starting from the root node of AST0, traverses each node of AST0 layer by layer. If node i,j is the node corresponding to the table, then enter S300; if node i,j is the node corresponding to the field, then enter S400; node i,j is the jth node of the i-th layer of AST0, i ranges from 1 to n, n is the number of layers of AST0, j ranges from 1 to m(i), m(i) is the number of nodes included in the i-th layer of AST0.

[0007] S300, if node i,jIf there is a node corresponding to the table in the descendant nodes and sibling nodes of i,j Corresponding table and ntab p i,j The table blood relationship between the corresponding tables; ntab p i,j For node i,j The node corresponding to the pth table in the descendant nodes and sibling nodes of , where the value of p ranges from 1 to P(i,j), where P(i,j) is the node i,j The number of nodes corresponding to the table in the descendant nodes and sibling nodes.

[0008] S400, if node i,j If there is a node corresponding to the field in the descendant nodes, sibling nodes, and associated nodes of i,j The corresponding fields are nfie q i,j The field blood relationship between the corresponding fields; nfie q i,j For node i,j The node corresponding to the qth field in the descendant nodes, sibling nodes, and associated nodes of , where the value of q ranges from 1 to Q(i,j), where Q(i,j) is the node i,j The number of nodes corresponding to the fields in the descendant nodes, sibling nodes, and associated nodes; the associated node is node i,j The descendant nodes of the brother node.

[0009] S500, obtaining a final table lineage relationship set corresponding to the target SQL statement according to the table lineage relationship set corresponding to AST0; the first table of each table lineage relationship in the final table lineage relationship set corresponding to the target SQL statement is not the last table of other table lineage relationships.

[0010] S600, obtain the final field lineage relationship set corresponding to the target SQL statement according to the field lineage relationship set corresponding to AST0; the first field of each field lineage relationship in the final field lineage relationship set corresponding to the target SQL statement is not the last field of other field lineage relationships.

[0011] The present invention has at least the following beneficial effects:

[0012] The present invention obtains the table kinship and field kinship corresponding to the target SQL statement based on the abstract syntax tree AST0 corresponding to the target SQL statement. Specifically, the present invention traverses each node of the AST in a layer-by-layer traversal manner. If the node node i,j For the node corresponding to the table, get the node node i,j The nodes corresponding to the table in the descendant nodes and brother nodes of the node, and establish the tables corresponding to these nodes and nodei,j The table blood relationship between the corresponding tables; if the node node i,j If it is the node corresponding to the field, get the node node i,j The nodes corresponding to the fields in the descendant nodes, sibling nodes, and associated nodes, and establish the fields corresponding to these nodes and node i,j Field lineage relationship between corresponding fields. The table lineage relationship established by the present invention includes not only the lineage relationship between the corresponding node of the table and the table corresponding to its descendant node, but also the lineage relationship between the corresponding node of the table and the table corresponding to its brother node. Therefore, the table lineage relationship established by the present invention is more comprehensive; moreover, the field lineage relationship established by the present invention includes not only the lineage relationship between the corresponding node of the field and the field corresponding to its descendant node, but also the lineage relationship between the corresponding node of the field and the field corresponding to its brother node and associated node. Therefore, the field lineage relationship established by the present invention is more comprehensive; thus, the present invention can construct a more comprehensive data lineage relationship based on AST, and can show users more relationships between tables and between fields. Moreover, the present invention obtains the final table lineage relationship set corresponding to the target SQL statement based on the table lineage relationship set corresponding to AST0, and the first table of each table lineage relationship in the final table lineage relationship set is not the last table of other table lineage relationships, that is, the first table of each table lineage relationship in the final table lineage relationship set is the original source of the last table of the table lineage relationship in the target SQL statement, and can display the complete table relationship to the user; the present invention obtains the final field lineage relationship set corresponding to the target SQL statement based on the field lineage relationship set corresponding to AST0, and the first field of each field lineage relationship in the final field lineage relationship set is not the last field of other field lineage relationships, that is, the first field of each field lineage relationship in the final field lineage relationship set is the original source of the last field of the table lineage relationship in the target SQL statement, and can display the complete field relationship to the user. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0014] Figure 1 A flow chart of a data lineage analysis method based on SQL parsing provided in an embodiment of the present invention. DETAILED DESCRIPTION

[0015] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of the present invention.

[0016] According to the present invention, a data lineage analysis method based on SQL parsing is provided. Figure 1 As shown, the following steps are included:

[0017] S100, obtaining an abstract syntax tree AST0 corresponding to the target SQL statement.

[0018] Those skilled in the art know that any method of parsing SQL statements to obtain an abstract syntax tree in the prior art falls within the protection scope of the present invention. Optionally, the abstract syntax tree AST0 corresponding to the target SQL statement is obtained through a SQL parser, and the SQL parser is ANTLR.

[0019] S200, starting from the root node of AST0, traverses each node of AST0 layer by layer. If node i,j is the node corresponding to the table, then enter S300; if node i,j is the node corresponding to the field, then enter S400; node i,j is the jth node of the i-th layer of AST0, i ranges from 1 to n, n is the number of layers of AST0, j ranges from 1 to m(i), m(i) is the number of nodes included in the i-th layer of AST0.

[0020] In this embodiment, the nodes of the abstract syntax tree generally include multiple types of nodes, for example, nodes corresponding to preset keywords, the preset keywords include from keywords, join keywords, select keywords, update keywords and insert keywords, etc.; for example, nodes corresponding to libraries, that is, the nodes represent a library; for example, nodes corresponding to tables, that is, the nodes represent a table; for example, nodes corresponding to fields, that is, the nodes represent a field.

[0021] As a preferred specific implementation, S200 also includes determining node i,j Is it the node and node corresponding to the table? i,j The process of determining whether it is a node corresponding to the field includes:

[0022] S210, node i,j Matches the preset first keyword set. If node i,j If a keyword in the first keyword set matches successfully, the nodei,j It is not a node corresponding to the table or a node corresponding to the field; otherwise, enter S220; the preset first keyword set includes several keywords.

[0023] In this embodiment, the keywords in the preset first keyword set are keywords obtained based on experience. As a specific implementation, the first keyword set includes a from keyword, a join keyword, a select keyword, an update keyword, an insert keyword, and the like.

[0024] S220, if node i,j If it is not the root node, get the node i,j The parent node nfat i,j .

[0025] In this embodiment, if node i,j Is the root node, then first i,j Match with the preset table name database. If the match is successful, determine the node i,j is the node corresponding to the table, otherwise, node i,j Match with the preset field name database, if the match is successful, determine the node i,j is the node corresponding to the field, otherwise, determine the node i,j Not the node corresponding to the field.

[0026] S230, if nfat i,j If a match is successful with a keyword in the preset second keyword set, the process proceeds to S240 ; otherwise, the process proceeds to S250 ; the preset second keyword set includes a plurality of keywords, and the preset second keyword set is a subset of the preset first keyword set.

[0027] In this embodiment, the keywords in the preset second keyword set are keywords obtained based on experience, and the probability of table names appearing after these keywords in the SQL statement is greater than the probability of field names appearing. Optionally, the second keyword set includes from keywords and join keywords.

[0028] S240, node i,j Match with the preset table name database. If the match is successful, determine the node i,j It is the node corresponding to the table.

[0029] In this embodiment, if node i,j If the match with the preset table name database fails, the node i,j Match with the preset field name database, if the match is successful, determine the node i,jis the node corresponding to the field, otherwise, judge node i,j Not the node corresponding to the field.

[0030] S250, if nfat i,j If a match is successful with a keyword in the preset third keyword set, enter S260; the preset third keyword set includes several keywords, and the preset third keyword set is a subset of the preset first keyword set; the preset second keyword set does not intersect with the preset third keyword set.

[0031] In this embodiment, if nfat i,j If the match with the keywords in the preset third keyword set fails, the node i,j Match with the preset table name database. If the match is successful, determine the node i,j is the node corresponding to the table, otherwise, node i,j Match with the preset field name database, if the match is successful, determine the node i,j is the node corresponding to the field, otherwise, determine the node i,j Not the node corresponding to the field.

[0032] In this embodiment, the keywords in the preset third keyword set are keywords obtained based on experience, and the probability of field names appearing after these keywords is greater than the probability of table names appearing. Optionally, the third keyword set includes select keywords, update keywords, and insert keywords.

[0033] S260, node i,j Match with the preset field name database, if the match is successful, determine the node i,j It is the node corresponding to the field.

[0034] In this embodiment, if node i,j If the match with the preset field name database fails, the node i,j Match with the preset table name database. If the match is successful, determine the node i,j is the node corresponding to the table, otherwise, judge node i,j Not the node corresponding to the table.

[0035] Based on S210-S260, first determine the node i,j Whether it matches the keywords in the preset first keyword set, if it matches, determine whether the node i,j It is not the node corresponding to the table, nor the node corresponding to the field; if it does not match, and node i,jIf it is not a root node, then according to node i,j The parent node of the node is used to determine whether to i,j Matches the preset table name database or matches the preset field name database; in this embodiment, node i,j When the parent node of node matches the keyword in the second keyword set, node i,j The probability of the node corresponding to the table is greater than node i,j is the probability of the node corresponding to the field. In this case, node i,j Matching with the preset table name database is helpful to quickly determine the node i,j Is it a table name? In this embodiment, node i,j When the parent node of node matches the keyword in the third keyword set, node i,j The probability of the node corresponding to the field is greater than node i,j is the probability of the node corresponding to the table. In this case, node i,j Matching with the preset field name database is helpful for quickly determining the node i,j Whether it is a field name; therefore, based on S210-S260, this embodiment can quickly determine whether the node is a node corresponding to the table or a node corresponding to the field, which can improve efficiency.

[0036] S300, if node i,j If there is a node corresponding to the table in the descendant nodes and sibling nodes of i,j Corresponding table and ntab p i,j The table blood relationship between the corresponding tables; ntab p i,j For node i,j The node corresponding to the pth table in the descendant nodes and sibling nodes of , where the value of p ranges from 1 to P(i,j), where P(i,j) is the node i,j The number of nodes corresponding to the table in the descendant nodes and sibling nodes.

[0037] As a specific implementation, when establishing a table blood relationship between tables, the name of the library to which the table belongs is attached when representing the table. For example, the corresponding library name is used as a prefix of the table name.

[0038] S400, if node i,j If there is a node corresponding to the field in the descendant nodes, sibling nodes, and associated nodes of i,j The corresponding fields are nfie q i,j The field blood relationship between the corresponding fields; nfie q i,jFor node i,j The node corresponding to the qth field in the descendant nodes, sibling nodes, and associated nodes of , where the value of q ranges from 1 to Q(i,j), where Q(i,j) is the node i,j The number of nodes corresponding to the fields in the descendant nodes, sibling nodes, and associated nodes; the associated node is node i,j The descendant nodes of the brother node.

[0039] As a specific implementation, when establishing the field blood relationship between fields, the table name and library name to which the field belongs are attached when representing the field. For example, the corresponding library name and table name are used as prefixes of the field name.

[0040] S500, obtaining a final table lineage relationship set corresponding to the target SQL statement according to the table lineage relationship set corresponding to AST0; the first table of each table lineage relationship in the final table lineage relationship set corresponding to the target SQL statement is not the last table of other table lineage relationships.

[0041] As a preferred specific implementation, S500 includes:

[0042] S510, obtain the table blood relationship set trea corresponding to AST0, trea={trea1, trea2,…, trea x ,…,trea y}, trea x is the xth table consanguinity relationship constructed according to AST0, x ranges from 1 to y, and y is the number of table consanguinity relationships constructed according to AST0; x =(tab x,1 ,tab x,2 ), tab x,1 For trea x The first table in the tab x,2 For trea x The tail table in tab x,1 For tab x,2 The data source.

[0043] S520, traverse trea, if trea x Tab x,1 If it is not the last table in the blood relationship of any table in trea, then trea x Append to the first table blood relationship set; otherwise, enter S530; the first table blood relationship set is initialized to an empty set.

[0044] S530, get trea x The corresponding front table blood relationship set tfro x ,tfro x={tfro x,1 ,tfro x,2 ,…,tfro x,c(x) ,…,tfro x,e(x)}, tfro x,c(x) The c(x)th tail table in trea is tab x,1 The table of blood relationship, the value range of c(x) is 1 to e(x), e(x) is the tail table in trea, tab x,1 The table indicates the number of blood relationships.

[0045] S540, will x,1 ,ntre x,2 ,…,ntre x,c(x) ,…,ntre x,e(x)}Append to the first table of blood relationship collection, ntre x,c(x) According to tfro x,c(x) Get the tab x,2 The table of blood relationship, ntre x,c(x) =(tsta x,c(x) ,tab x,1 ,tab x,2 ), tsta x,c(x) For tfro x,c(x) The first table.

[0046] S550, if the first table of each table kinship relationship in the first table kinship relationship set is not the last table of other table kinship relationships in the first table kinship relationship set, the first table kinship relationship set is determined as the final table kinship relationship set; otherwise, the first table kinship relationship set is updated according to the source table kinship relationship corresponding to the table kinship relationship in the first table kinship relationship set, and the updated first table kinship relationship set is determined as the final table kinship relationship set, the source table kinship relationship corresponding to the table kinship relationship is the table kinship relationship whose last table is the first table of the table kinship relationship, and the first table of each table kinship relationship in the updated first table kinship relationship set is not the last table of other table kinship relationships in the updated first table kinship relationship set.

[0047] For example, tfro x,c(x) Also for trea x The corresponding source table correspondence.

[0048] In this embodiment, when there is a table kinship set in the first table kinship set whose first table is the last table of other table kinships in the first table kinship set, the first table kinship set is updated. The purpose of updating the first table kinship is to make the first table of each table kinship in the updated first table kinship set not the last table of other table kinships in the updated first table kinship set. The process of updating the first table kinship set is similar to the process of obtaining the first table kinship set according to the table kinship set trea corresponding to AST0, including the process of determining whether the first table in each table kinship in the first table kinship set is the last table in other table kinships, and the process of obtaining the corresponding table kinship according to the corresponding previous table kinship set when the first table in a table kinship is the last table in other table kinships, which will not be repeated here.

[0049] In the final table lineage relationship set obtained based on S510 - S550 , the first table of each table lineage relationship is the original source of the last table in the target SQL statement, and the table source of the last table in each table lineage relationship in the target SQL statement is fully displayed.

[0050] S600, obtain the final field lineage relationship set corresponding to the target SQL statement according to the field lineage relationship set corresponding to AST0; the first field of each field lineage relationship in the final field lineage relationship set corresponding to the target SQL statement is not the last field of other field lineage relationships.

[0051] As a preferred specific implementation, S600 includes:

[0052] S610, obtain the field blood relationship set drea corresponding to AST0, drea={drea1, drea2,…, drea f ,…,drea g},drea f is the fth field kinship relationship constructed according to AST0, the value range of f is 1 to g, and g is the number of field kinship relationships constructed according to AST0; drea f =(fie f,1 ,fie f,2 ), fie f,1 for drea f The first field in the f,2 for drea f The tail field in fie f,1 for fie f,2 The data source.

[0053] S620, traverse drea, if drea f fief,1 If it is not the last field in the blood relationship of any field in drea, then drea f Append to the first field blood relationship set; otherwise, enter S630; the first field blood relationship set is initialized to an empty set.

[0054] S630, get dream f The corresponding front field blood relationship set dfro f ,dfro f ={dfro f,1 ,dfro f,2 ,…,dfro f,u(f) ,…,dfro f,h(f)}, dfro f,u(f) The u(f)th tail field in drea is fie f,1 The field blood relationship, u(f) ranges from 1 to h(f), h(f) is the tail field in drea, and fie is f,1 The number of field lineages.

[0055] S640, will f,1 ,ndre f,2 ,…,ndre f,u(f) ,…,ndre f,h(f)} Append to the first field blood relationship collection, ndre f,u(f) According to dfro f,u(f) Get the fie f,2 Field kinship, ndre f,u(f) =(sfie f,u(f) ,fie f,1 ,fie f,2 ), sfie f,u(f) for dfro f,u(f) The first field of .

[0056] S650, if the first field of each field kinship in the first field kinship set is not the last field of other field kinships in the first field kinship set, the first field kinship set is determined as the final field kinship set; otherwise, the first field kinship set is updated according to the source field kinship corresponding to the field kinship in the first field kinship set, and the updated first field kinship set is determined as the final field kinship set, the source field kinship corresponding to the field kinship is a field kinship whose last field is the first field of the field kinship, and the first field of each field kinship in the updated first field kinship set is not the last field of other field kinship in the updated first field kinship set.

[0057] For example, dfro f,u(f) Also for drea f The corresponding source field lineage.

[0058] In this embodiment, when there is a first field kinship in the first field kinship set that is the last field of other field kinships in the first field kinship set, the first field kinship set is updated, and the purpose of updating the first field kinship is to make the first field of each field kinship in the updated first field kinship set not the last field of other field kinships in the updated first field kinship set. The process of updating the first field kinship set is similar to the process of obtaining the first field kinship set according to the field kinship set drea corresponding to AST0, including the process of determining whether the first field in each field kinship in the first field kinship set is the last field in other field kinships, and the process of obtaining the corresponding field kinship according to the corresponding previous field kinship set when the first field in a field kinship is the last field in other field kinships, which will not be repeated here.

[0059] In the final field lineage set obtained based on S610-S650, the first field of each field lineage is the original source of the tail field in the target SQL statement, and the field source of the tail field in each field lineage in the target SQL statement is fully displayed.

[0060] This embodiment obtains the table lineage relationship and field lineage relationship corresponding to the target SQL statement based on the abstract syntax tree AST0 corresponding to the target SQL statement. Specifically, this embodiment traverses each node of the AST in a layer-by-layer traversal manner. If the node node i,j For the node corresponding to the table, get the node node i,j The nodes corresponding to the table in the descendant nodes and brother nodes of the node, and establish the tables corresponding to these nodes and node i,j The table blood relationship between the corresponding tables; if the node node i,j If it is the node corresponding to the field, get the node node i,j The nodes corresponding to the fields in the descendant nodes, sibling nodes, and associated nodes, and establish the fields corresponding to these nodes and node i,jField lineage relationship between corresponding fields. The table lineage relationship established in this embodiment includes not only the lineage relationship between the corresponding node of the table and the table corresponding to its descendant node, but also the lineage relationship between the corresponding node of the table and the table corresponding to its sibling node. Therefore, the table lineage relationship established in this embodiment is more comprehensive; moreover, the field lineage relationship established in this embodiment includes not only the lineage relationship between the corresponding node of the field and the field corresponding to its descendant node, but also the lineage relationship between the corresponding node of the field and the field corresponding to its sibling node and associated node. Therefore, the field lineage relationship established in this embodiment is more comprehensive; thus, this embodiment can construct a more comprehensive data lineage relationship based on AST, and can show users more relationships between tables and between fields. Moreover, the present invention obtains the final table lineage relationship set corresponding to the target SQL statement based on the table lineage relationship set corresponding to AST0, and the first table of each table lineage relationship in the final table lineage relationship set is not the last table of other table lineage relationships, that is, the first table of each table lineage relationship in the final table lineage relationship set is the original source of the last table of the table lineage relationship in the target SQL statement, and can display the complete table relationship to the user; the present invention obtains the final field lineage relationship set corresponding to the target SQL statement based on the field lineage relationship set corresponding to AST0, and the first field of each field lineage relationship in the final field lineage relationship set is not the last field of other field lineage relationships, that is, the first field of each field lineage relationship in the final field lineage relationship set is the original source of the last field of the table lineage relationship in the target SQL statement, and can display the complete field relationship to the user.

[0061] As a preferred specific implementation, after S600, the method further includes:

[0062] S700: Obtain a first SQL statement.

[0063] In this embodiment, the first SQL statement is a statement different from the target SQL statement.

[0064] S710: If the result set generated by executing the first SQL statement is the same as the result set generated by executing the target SQL statement, proceed to S720.

[0065] In this embodiment, if the result set generated by executing the first SQL statement is the same as the result set generated by executing the target SQL statement, it means that although the syntax of the first SQL statement and the target SQL statement is different, the execution results are the same, indicating that the first SQL statement and the target SQL statement are SQL statements that can replace each other.

[0066] In this embodiment, if the result set generated by executing the first SQL statement is different from the result set generated by executing the target SQL statement, S720 to S740 are not executed.

[0067] S720, obtain the final table lineage relationship set ftab1 and the final field table lineage relationship set fbie1 corresponding to the first SQL statement; the first table of each table lineage relationship in ftab1 is not the last table of other table lineage relationships in fbie1, and the first field of each field lineage relationship in fbie1 is not the last field of other field lineage relationships in fbie1.

[0068] In this embodiment, the process of obtaining the final table lineage relationship set corresponding to the first SQL statement is similar to the process of obtaining the final table lineage relationship set corresponding to the target SQL statement, and will not be repeated here.

[0069] In this embodiment, the process of obtaining the final field lineage relationship set corresponding to the first SQL statement is similar to the process of obtaining the final field lineage relationship set corresponding to the target SQL statement, and will not be repeated here.

[0070] S730, determine the priority wei1 of the first SQL statement according to ftab1 and flie1.

[0071] As a preferred specific implementation, S730 includes:

[0072] S731, obtain the maximum length Len1 of the blood relationship table in ftab1.

[0073] In this embodiment, ftab1 includes several table kinship relations, and the length of each table kinship relation is the number of tables corresponding to it. For example, if a table kinship relation is used to represent the kinship relation between two tables, then the length of the table kinship relation is 2. Specifically, table kinship relation (tab x,1 ,tab x,2 ) is 2; a table of blood relations is used to represent the blood relations between three tables, so the length of the blood relations of the table is 3. Specifically, (tsta x,c(x) ,tab x,1 ,tab x,2 ) has a length of 3.

[0074] S732, obtain the number of tables tnum1 existing in the table blood relationship in ftab1.

[0075] In this embodiment, all tables existing in the table blood relationship in ftab1 are formed into a table set, and then the table set is subjected to the first deduplication process, and the number of tables in the table set after the first deduplication process is determined as tnum1. The first deduplication process refers to filtering out repeated tables, and only one table remains after filtering out.

[0076] S733, obtain the number dnum1 of the first fields existing in the field blood relationship in fie1.

[0077] In this embodiment, the first fields existing in all field blood relations in fie1 constitute a field set, and then the field set is subjected to the second deduplication process, and the number of fields in the field set after the second deduplication process is determined as dnum1. The second deduplication process refers to filtering out repeated fields, and only one field remains after filtering out.

[0078] S734, get wei1, wei1=1-w1×Len 1,gui -w2×tnum 1,gui -w3×dnum 1,gui , w1, w2 and w3 are the preset first weight, second weight and third weight respectively, w1+w2+w3=1, Len 1,gui 、tnum 1,gui and dnum 1,gui They are the normalized values ​​of Len1, tnum1 and dnum1 respectively.

[0079] In this embodiment, w1, w2 and w3 are empirical values, and optionally, w1=w2=w3=1 / 3.

[0080] Those skilled in the art are aware that any normalization processing method in the prior art falls within the protection scope of the present invention.

[0081] In this embodiment, the number of first fields existing in the field lineage relationship in the final table lineage relationship set ftab1 corresponding to the first SQL statement, the number of tables existing in the table lineage relationship in ftab1, and the number of first fields existing in the field lineage relationship in the final field lineage relationship set fie1 corresponding to the first SQL statement can represent the number of tables called during the execution of the first SQL statement and the complexity of the grammatical structure of the first SQL statement; the larger wei1 is, the fewer the number of tables called during the execution of the first SQL statement and the lower the complexity of the grammatical structure of the first SQL statement, and the better the first SQL statement is.

[0082] S740: If wei1 is greater than the priority wei0 of the target SQL statement, output preset information for indicating that the priority of the target SQL statement is lower than the priority of the first SQL statement.

[0083] In this embodiment, if wei1 is greater than the priority wei0 of the target SQL statement, it means that the first SQL statement calls fewer tables and has a simpler syntax structure than the target SQL statement when the execution results are the same, and the corresponding execution speed is faster and occupies fewer resources. It is a better SQL statement than the target SQL statement.

[0084] In this embodiment, the method for obtaining the priority wei0 of the target SQL statement is similar to the method for obtaining the priority wei1 of the first SQL statement, which will not be repeated here.

[0085] In this embodiment, if wei1 is less than the priority wei0 of the target SQL statement, preset information for indicating that the priority of the first SQL statement is lower than the priority of the target SQL statement is output.

[0086] This embodiment can output preset information to prompt SQL statements with the same execution results, different syntax and corresponding lower priority, so that users can optimize the SQL statements with lower priority to improve the efficiency of SQL statement execution and reduce resource usage.

[0087] Although some specific embodiments of the present invention have been described in detail by way of example, it will be appreciated by those skilled in the art that the above examples are for illustration only and are not intended to limit the scope of the present invention. It will also be appreciated by those skilled in the art that various modifications may be made to the embodiments without departing from the scope and spirit of the present invention. The scope of the present invention is defined by the appended claims.

Claims

1. A data lineage analysis method based on SQL parsing, characterized in that: The following steps are involved: S100, obtaining the abstract syntax tree AST0 corresponding to the target SQL statement; S200, starting from the root node of AST0, traverses each node of AST0 layer by layer. If node i,j is the node corresponding to the table, then enter S300; if node i,j is the node corresponding to the field, then enter S400; node i,j is the jth node of the i-th layer of AST0, i ranges from 1 to n, n is the number of layers of AST0, j ranges from 1 to m(i), m(i) is the number of nodes included in the i-th layer of AST0; S300, if node i,j If there is a node corresponding to the table in the descendant nodes and sibling nodes of i,j Corresponding table and ntab p i,j The table blood relationship between the corresponding tables; ntab p i,j For node i,j The node corresponding to the pth table in the descendant nodes and sibling nodes of , where the value of p ranges from 1 to P(i,j), where P(i,j) is the node i,j The number of nodes corresponding to the table in the descendant nodes and sibling nodes; S400, if node i,j If there is a node corresponding to the field in the descendant nodes, sibling nodes, and associated nodes of i,j The corresponding fields are the same as nfie q i,j The field blood relationship between the corresponding fields; nfie q i,j For node i,j The node corresponding to the qth field in the descendant nodes, sibling nodes, and associated nodes of , where the value of q ranges from 1 to Q(i,j), where Q(i,j) is the node i,j The number of nodes corresponding to the fields in the descendant nodes, sibling nodes, and associated nodes; the associated node is node i,j The descendant nodes of the brother nodes; S500, obtaining a final table lineage relationship set corresponding to the target SQL statement according to the table lineage relationship set corresponding to AST0; The first table of each table blood relationship in the final table blood relationship set corresponding to the target SQL statement is not the last table of other table blood relationships; S600, obtaining a final field lineage relationship set corresponding to the target SQL statement according to the field lineage relationship set corresponding to AST0; The first field of each field lineage relationship in the final field lineage relationship set corresponding to the target SQL statement is not the last field of other field lineage relationships.

2. The data lineage analysis method based on SQL parsing according to claim 1 is characterized in that: S500 includes: S510, obtain the table blood relationship set trea corresponding to AST0, trea={trea1, trea2,…, trea x ,…,trea y }, trea x is the xth table consanguinity relationship constructed according to AST0, x ranges from 1 to y, and y is the number of table consanguinity relationships constructed according to AST0; x =(tab x,1 ,tab x,2 ), tab x,1 For trea x The first table in the tab x,2 For trea x The tail table in tab x,1 For tab x,2 The source of data; S520, traverse trea, if trea x Tab x,1 If it is not the last table in the blood relationship of any table in trea, then trea x Append to the first table blood relationship set; otherwise, enter S530; the first table blood relationship set is initialized to an empty set; S530, get trea x The corresponding front table blood relationship set tfro x ,tfro x ={tfro x,1 ,tfro x,2 ,…,tfro x,c(x) ,…,tfro x,e(x) }, tfro x,c(x) The c(x)th tail table in trea is tab x,1 The table of blood relationship, the value range of c(x) is 1 to e(x), e(x) is the tail table in trea, tab x,1 The number of blood relations; S540, will x,1 ,ntre x,2 ,…,ntre x,c(x) ,…,ntre x,e(x) }Append to the first table of blood relationship collection, ntre x,c(x) According to tfro x,c(x) Get the tab x,2 The table of blood relationship, ntre x,c(x) =(tsta x,c(x) ,tab x,1 ,tab x,2 ), tsta x,c(x) For tfro x,c(x) The first table of S550, if the first table of each table kinship relationship in the first table kinship relationship set is not the last table of other table kinship relationships in the first table kinship relationship set, the first table kinship relationship set is determined as the final table kinship relationship set; otherwise, the first table kinship relationship set is updated according to the source table kinship relationship corresponding to the table kinship relationship in the first table kinship relationship set, and the updated first table kinship relationship set is determined as the final table kinship relationship set, the source table kinship relationship corresponding to the table kinship relationship is the table kinship relationship whose last table is the first table of the table kinship relationship, and the first table of each table kinship relationship in the updated first table kinship relationship set is not the last table of other table kinship relationships in the updated first table kinship relationship set.

3. The data lineage analysis method based on SQL parsing according to claim 1 is characterized in that: S600 includes: S610, obtain the field blood relationship set drea corresponding to AST0, drea={drea1, drea2,…, drea f ,…,drea g },drea f is the fth field kinship relationship constructed according to AST0, the value range of f is 1 to g, and g is the number of field kinship relationships constructed according to AST0; drea f =(fie f,1 ,fie f,2 ), fie f,1 for drea f The first field in the f,2 for drea f The tail field in fie f,1 for fie f,2 The source of data; S620, traverse drea, if drea f fie f,1 If it is not the last field in the blood relationship of any field in drea, then drea f Append to the first field blood relationship set; otherwise, enter S630; the first field blood relationship set is initialized to an empty set; S630, get dream f The corresponding front field blood relationship set dfro f ,dfro f ={dfro f,1 ,dfro f,2 ,…,dfro f,u(f) ,…,dfro f,h(f) }, dfro f,u(f) The u(f)th tail field in drea is fie f,1 The field blood relationship, u(f) ranges from 1 to h(f), h(f) is the tail field in drea, and fie is f,1 The field is the number of kinship relations; S640, will f,1 ,ndre f,2 ,…,ndre f,u(f) ,…,ndre f,h(f) } Append to the first field blood relationship collection, ndre f,u(f) According to dfro f,u(f) Get the fie f,2 Field kinship, ndre f,u(f) =(sfie f,u(f) ,fie f,1 ,fie f,2 ), sfie f,u(f) for dfro f,u(f) The first field of S650, if the first field of each field kinship in the first field kinship set is not the last field of other field kinships in the first field kinship set, the first field kinship set is determined as the final field kinship set; otherwise, the first field kinship set is updated according to the source field kinship corresponding to the field kinship in the first field kinship set, and the updated first field kinship set is determined as the final field kinship set, the source field kinship corresponding to the field kinship is a field kinship whose last field is the first field of the field kinship, and the first field of each field kinship in the updated first field kinship set is not the last field of other field kinship in the updated first field kinship set.

4. The data lineage analysis method based on SQL parsing according to claim 1 is characterized in that: After S600, the method further includes: S700, obtaining a first SQL statement; S710, if the result set generated by executing the first SQL statement is the same as the result set generated by executing the target SQL statement, proceed to S720; S720, obtaining the final table kinship set ftab1 and the final field table kinship set fie1 corresponding to the first SQL statement; the first table of each table kinship in ftab1 is not the last table of other table kinship in ftab1, and the first field of each field kinship in fie1 is not the last field of other field kinship in fie1; S730, determining the priority wei1 of the first SQL statement according to ftab1 and flie1; S740: If wei1 is greater than the priority wei0 of the target SQL statement, output preset information for indicating that the priority of the target SQL statement is lower than the priority of the first SQL statement.

5. The data lineage analysis method based on SQL parsing according to claim 4 is characterized in that: S730 includes: S731, obtain the maximum length Len1 of the blood relationship table in ftab1; S732, obtaining the number of tables tnum1 existing in the table blood relationship in ftab1; S733, obtaining the number dnum1 of the first fields in the field kinship relationship in fie1; S734, get wei1, wei1=1-w1×Len 1,gui -w2×tnum 1,gui -w3×dnum 1,gui , w1, w2 and w3 are the preset first weight, second weight and third weight respectively, w1+w2+w3=1, Len 1,gui 、tnum 1,gui and dnum 1,gui They are the normalized values ​​of Len1, tnum1 and dnum1 respectively.

6. The data lineage analysis method based on SQL parsing according to claim 1 is characterized in that: S200 also includes judging node i,j Is it the node and node corresponding to the table? i,j The process of determining whether it is a node corresponding to the field includes: S210, node i,j Matches the preset first keyword set. If node i,j If a keyword in the first keyword set matches successfully, the node i,j It is not a node corresponding to a table or a node corresponding to a field; otherwise, the process proceeds to S220; the preset first keyword set includes a plurality of keywords; S220, if node i,j If it is not the root node, get the node i,j The parent node nfat i,j ; S230, if nfat i,j If a keyword in the preset second keyword set is successfully matched, then the process proceeds to S240; otherwise, the process proceeds to S250; the preset second keyword set includes a plurality of keywords, and the preset second keyword set is a subset of the preset first keyword set; S240, node i,j Match with the preset table name database. If the match is successful, determine the node i,j is the node corresponding to the table; S250, if nfat i,j If a match is successfully made with a keyword in the preset third keyword set, the process proceeds to S260; the preset third keyword set includes a plurality of keywords, and the preset third keyword set is a subset of the preset first keyword set; the preset second keyword set does not intersect with the preset third keyword set; S260, node i,j Match with the preset field name database, if the match is successful, determine the node i,j It is the node corresponding to the field.

7. The data lineage analysis method based on SQL parsing according to claim 1 is characterized in that: In S100, an abstract syntax tree AST0 corresponding to the target SQL statement is obtained through a SQL parser.

Citation Information

Patent Citations

  • Blood relationship extraction method and device and electronic equipment

    CN114265945A

  • SQL script-based blood relationship analysis method and device, equipment and storage medium

    CN115408399A

  • Data analysis-based data blood relationship graph construction method and related equipment

    CN116186174A

  • Method and device for generating consanguinity of field-level data of data table

    CN116955498A

  • Data consanguinity traceability method based on data entity

    CN117235037A