Method for supporting table and field blood relationship analysis based on Dores SQL (Structured Query Language)

Abstract

The invention discloses a method for supporting table and field blood relationship analysis based on a Dores SQL (Structured Query Language). The method comprises the following steps: S1, capturing the Dores SQL; s2, preprocessing the Dores SQL (Structured Query Language); s3, constructing a Dores SQL (Structured Query Language) L-AST (Accelerated The method comprises the following steps: constructing an AST of an SQL (Structured Query Language) on the basis of a Duid, constructing an entity table root node of the L-AST by using a target table behind an involved INSERT or CREATE, then mounting a whole SELECT block as a first virtual table child node under the root node, extracting entity field information in the SELECT, and then analyzing an FROM to obtain a tree which is the L-AST; and S4, the analysis of the Dores SQL L-AST is carried out. According to the method, the problem of blood relationship acquisition of the Dores SQL can be effectively solved, and the integrity of the obtained blood relationship link is high. And the grammar support for the DSL in the Dores is relatively good.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and particularly relates to a method for supporting table and field lineage parsing based on Doris SQL. Background Art

[0002] With the gradual digital and intelligent transformation of enterprises, data has become the core asset. Enterprises increasingly rely on data to drive decisions, optimize processes, improve user experience, and innovate business. However, in this process, enterprises are facing huge challenges, especially in aspects such as data integration, data management, and data utilization.

[0003] In supporting relevant reports and applications such as company business and finance, the interrelationships between system data have become increasingly complex and data may go through multiple links from collection to final application, including data preprocessing, cleaning, transformation, aggregation, storage, etc., and these links often rely on different tools and technologies. The entire data link from the source end to the application presents a network structure as a whole, bringing huge challenges to the positioning of problems such as data loss, change, or error. Summary of the Invention

[0004] According to an embodiment of the present invention, there is provided a method for supporting table and field lineage parsing based on Doris SQL, including: S1: Capture of Doris SQL; A custom plugin implemented based on the Doris Audit plugin preprocesses each executed SQL in Doris and then sends it to kafka for downstream consumption and parsing; S2: Preprocessing of Doris SQL; Perform format replacement on Doris SQL, convert it into an SQL string for lexical analysis, and classify and label the SQL; S3: Construction of Doris SQL L-AST; Based on Druid, construct the AST of SQL, build the entity table root node of the L-AST for the target table after INSERT or CREATE involved, then mount the entire SELECT block as the first virtual table child node under the root node, extract the entity field information in SELECT, and the tree obtained by parsing FROM is the L-AST; S4: Parsing of Doris SQL L-AST; The parsing traverses in reverse order from the leaf nodes of the tree upwards until the root node rNode.

[0005] Further, the blood relationship SQL in S3 generally involves two major types: CREATE_TABLE_SELECT or INSERT_TABLE_SELECT.

[0006] Further, during the process of parsing the FROM block in S3, if the entity table follows the From block, an entity is constructed and mounted after the first virtual table sub-node. If JOIN or UNION follows the From block, a second virtual table sub-node is constructed and mounted after the first virtual table sub-node.

[0007] Further, if the entity table follows From, the current traversal ends. If JOIN or UNION follows From, the entity table or SELECT block will be parsed in sequence, and then the parsing will be looped until the entity table is traversed and the current loop traversal ends.

[0008] Further, the parsing of the special syntax CREATE_MATERIALIZED_VIEW in Doris is the same as the parsing methods of CREATE_TABLE_SELECT and INSERT_TABLE_SELECT.

[0009] Further, in S4, if there are no entity fields after the INSERT table name, the metadata needs to be extracted first to complete the fields of the target table, and the target table is the table after CREATE or INSERT.

[0010] Further, during the traversal in S4, if the name or alias of the c-column in the sub-node is the same as the name or alias of the p-column in the parent node, it is considered that the c-column and p-column are in a blood relationship, and the traversal is performed layer by layer upwards according to the above logic until the root node rNode.

[0011] Further, the tables and fields in the blood relationship are respectively constructed as TableVertex and ColumnVertex in the graph database NebulaGraph. There are only edges of blood relationship between TableVertexes and between ColumnVertexes, and there are edges of subordination relationship between TableVertex and ColumnVertex.

[0012] Further, for the query of the blood relationship, based on a certain TableVertex or ColumnVertex, the corresponding downstream TableVertex or ColumnVertex can be queried along the edges of the blood relationship according to the incoming edge relationship to obtain the blood relationship.

[0013] A method for supporting table and field lineage parsing based on Doris SQL according to an embodiment of the present invention. This application can effectively solve the problem of lineage acquisition in Doris SQL, and the integrity of the obtained lineage link is high. It has good syntax support for DSL in Doris.

[0014] The present invention not only supports the parsing of lineage SQL but also supports the parsing of metadata SQL to ensure the integrity, real-time nature, and accuracy of downstream lineage data. It can greatly improve the efficiency of business personnel and technical personnel in data tracing, impact analysis, and data system optimization.

[0015] It is to be understood that both the foregoing general description and the following detailed description are exemplary and are intended to provide further explanation of the claimed technology. BRIEF DESCRIPTION OF THE DRAWINGS

[0016] Figure 1 FIG. is a flowchart of a method for supporting table and field lineage parsing based on Doris SQL according to an embodiment of the present invention; Figure 2 FIG. is a parsing process diagram of generating a lineage syntax tree for SQL in a parser of a method for supporting table and field lineage parsing based on Doris SQL according to an embodiment of the present invention. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0017] The following will describe in detail the preferred embodiments of the present invention with reference to the accompanying drawings and further elaborate on the present invention.

[0018] First, in combination with Figures 1 to 2 A method for supporting table and field lineage parsing based on Doris SQL according to an embodiment of the present invention is described, which improves the timeliness and reliability of the data middle platform in practical applications, reduces the difficulty of problem troubleshooting in complex data links, and further improves the decision-making efficiency and business agility of enterprises.

[0019] As Figures 1 to 2 shown, a method for supporting table and field lineage parsing based on Doris SQL according to an embodiment of the present invention includes: S1: Capture of Doris SQL; A custom plugin implemented based on the Doris Audit plugin preprocesses each executed SQL in Doris and then sends it to kafka for downstream consumption and parsing.

[0020] S2: Preprocessing of Doris SQL; Perform format replacement on Doris SQL, convert it into an SQL string for lexical analysis, and classify and label the SQL into: CREATE_TABLE_LIKE, CREATE_TABLE_SELECT, CREATE_MATERIALIZED_VIEW, DROP_TABLE, DROP_VIEW, DROP_MATERIALIZED_VIEW, INSERT_TABLE_SELECT, ALTER_RENAME_TABLE, ALTER_RENAME_COLUMN, ALTER_ADD_COLUMN, ALTER_DROP_COLUMN and the above categories.

[0021] S3: Construction of Doris SQL L-AST; Lineage SQL generally involves two major types: CREATE_TABLE_SELECT or INSERT_TABLE_SELECT. When constructing the AST of SQL based on Druid, the entity table root node of the L-AST will be constructed for the target table after INSERT or CREATE. Then, the entire SELECT block will be mounted as the first virtual table child node under the root node. The entity field information in the SELECT will be extracted. During the parsing of the FROM block, if the entity table follows the From block, the entity will be constructed and mounted after the first virtual table child node. If JOIN or UNION follows the From block, the second virtual table child node will be constructed and mounted after the first virtual table child node. If the entity table follows From, the current traversal ends. If JOIN or UNION follows From, the entity table or SELECT block will be parsed in sequence, and then the parsing will be looped until the entity table is traversed and the current loop traversal ends. The finally obtained tree is the L-AST. The parsing of the special syntax CREATE_MATERIALIZED_VIEW in Doris is the same as above.

[0022] S4: Parsing of Doris SQL L-AST; If there are no entity fields after the INSERT table name, the metadata needs to be extracted first to complete the fields of the target table, where the target table is the table after CREATE or INSERT. The parsing traverses in reverse order from the leaf nodes of the tree upwards. If the name or alias of the c-column in the child node is the same as the name or alias of the p-column in the parent node, then the c-column and p-column are considered to have a lineage relationship, and the traversal is carried out layer by layer upwards according to the above logic until the root node rNode.

[0023] This application realizes the parsing of Doris SQL, and traverses and generates a lineage syntax tree (L-AST) for lineage parsing based on the abstract syntax tree (AST). It identifies structures such as tables, fields, aliases, subqueries, JOIN conditions, aggregate functions, and calculation logics in SQL.

[0024] It supports syntax extensions unique to Doris SQL, such as materialized views and functions unique to Doris. It can be compatible with diverse syntaxes such as complex nested queries and window functions. It supports multi-level nested parsing of field-level dependencies, providing support for refined data management. Combined with graph database storage, it can make the lineage relationships of complex SQL queries clear at a glance.

[0025] In the lineage relationships, tables and fields respectively construct TableVertex and ColumnVertex in the graph database NebulaGraph. There are only edges of lineage relationships between TableVertices and between ColumnVertices, and there are edges of subordination relationships between TableVertex and ColumnVertex. For the query of lineage relationships, based on a certain TableVertex or ColumnVertex, along the edges of the lineage relationships and following the incoming edge relationships, the corresponding downstream TableVertex or ColumnVertex can be queried to obtain the lineage relationships.

[0026] Above, with reference to Figures 1 to 2 A method for supporting table and field lineage parsing based on Doris SQL according to an embodiment of the present invention is described, which can clearly show the lineage relationships in the Doris SQL query process, including: the dependency relationships between data tables, the source and change paths of fields, and the relationships between the calculation logics involved in the query and the output results. It supports tracing back from the result data to the source of its original data, providing the ability to trace the origin of data problems. When the data structure changes (such as the addition, modification, or deletion of table fields), it can automatically analyze the scope of its impact on upstream and downstream tasks: determine which tables or fields will be affected by the change. Evaluate the impact of the change on BI reports, ETL processes, etc. Ensure the accuracy and consistency of data. Quickly locate the root cause of abnormal fields.

[0027] It can effectively solve the problem of lineage acquisition for Doris SQL, and the integrity of the obtained lineage link is high. It has good support for the syntax of DSL in Doris (such as the syntax of materialized views).

[0028] The present invention not only supports the parsing of lineage SQL but also supports the parsing of metadata SQL (such as DROP TABLE and ALTER TABLE SQL) to ensure the integrity, real-time performance, and accuracy of downstream lineage data. It can significantly improve the efficiency of business personnel and technical personnel in data tracing, impact analysis, and data system optimization and other tasks.

[0029] It should be noted that in this specification, the terms "comprising", "including" or any other variant thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device comprising a series of elements not only includes those elements but also includes other elements not expressly listed, or further includes elements inherent to such process, method, article or device. Without further limitation, an element defined by the statement "comprising..." does not exclude the presence of additional identical elements in the process, method, article or device comprising said element.

[0030] Although the content of the present invention has been introduced in detail through the above preferred embodiments, it should be recognized that the above description should not be construed as a limitation of the present invention. After those skilled in the art have read the above content, various modifications and alternatives to the present invention will be obvious. Therefore, the protection scope of the present invention should be defined by the appended claims.

Claims

1. A method for supporting table and field lineage analysis based on Doris SQL, characterized in that: include: S1: Capture of Doris SQL; The custom plug-in implemented based on the Doris Audit plug-in pre-processes each SQL executed in Doris and then sends it to Kafka for downstream consumption and analysis; S2: Doris SQL preprocessing; Replace the format of Doris SQL, convert it into an SQL string for lexical analysis, and classify and mark the SQL; S3: Construction of Doris SQL L-AST; Build SQL AST based on Druid, build the entity table root node of L-AST for the target table after INSERT or CREATE involved, then mount the entire SELECT block as the first virtual table child node under the root node, extract the entity field information in SELECT, and then parse FROM to get the L-AST tree; S4: Parsing of Doris SQL L-AST; The parsing uses a reverse traversal from the leaf nodes of the tree upwards until the root node rNode.

2. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 1, characterized in that: The bloodline SQL in S3 generally involves two types: CREATE_TABLE_SELECT or INSERT_TABLE_SELECT.

3. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 2, characterized in that: During the parsing of the FROM block in S3, if the From block is followed by an entity table, an entity is constructed and mounted after the first virtual table child node; if the From block is followed by JOIN or UNION, a second virtual table child node is constructed and mounted after the first virtual table child node.

4. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 3, characterized in that: If From is followed by an entity table, the traversal ends. If From is followed by JOIN or UNION, the entity table or SELECT block will be parsed in turn, and then the parsing will be repeated in a loop until the entity table is traversed, and the traversal ends.

5. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 4, characterized in that: The parsing method of the special syntax CREATE_MATERIALIZED_VIEW in Doris is the same as that of CREATE_TABLE_SELECT and INSERT_TABLE_SELECT.

6. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 1, characterized in that: In S4, if there is no entity field after the INSERT table name, it is necessary to first extract metadata to complete the fields of the target table, and the target table is the table after CREATE or INSERT.

7. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 1, characterized in that: During the traversal process of S4, if the name or alias of c-column in the child node is consistent with the name or alias of p-column in the parent node, it is considered that c-column and p-column are related by blood, and the above logic is traversed layer by layer until the root node rNode is reached.

8. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 1, characterized in that: The tables and fields in the blood relationship are constructed as TableVertex and ColumnVertex in the graph library NebulaGraph respectively. There are only blood relationship edges between TableVertex and ColumnVertex, and there are subordinate relationship edges between TableVertex and ColumnVertex.

9. A method for supporting table and field lineage analysis based on Doris SQL as claimed in claim 8, characterized in that: The query of blood relationship can be based on a certain TableVertex or ColumnVertex, and the corresponding TableVertex or ColumnVertex downstream can be queried along the edge of the blood relationship and the input edge relationship to obtain the blood relationship.

Images