SQL Table Field Analysis Method, Memory, and Device Based on Abstract Syntax Tree

Abstract

The present invention provides a method, a memory, and a device for analyzing SQL table fields based on an abstract syntax tree. The method includes: parsing an initial SQL statement into an abstract syntax tree; performing table field analysis on the abstract syntax tree to obtain the initial SQL statement and the parsing results of the query statements at all levels of the initial SQL statement as the initial parsing results; traversing the initial parsing results, processing wildcards and / or fields lacking table aliases in multi-table associations, and supplementing source fields for the table field parsing results of each clause of the query statements at all levels to obtain the final parsing results. This method can effectively solve the limitations in handling full-field queries and multi-table associations in existing solutions and has universality.

Description

Technical Field

[0001] The present invention belongs to the field of database management, and particularly relates to a method, memory, and device for analyzing SQL table fields based on an abstract syntax tree. Background Art

[0002] In the prior art, there have been some related researches and implementations on the analysis of SQL statement table fields. For example, the table fields in the outermost SELECT clause of a query statement are analyzed, and data dynamic desensitization is performed according to the mapping relationship between the output fields and the source table fields; the target table structure in an insert statement and the table fields in the outermost SELECT clause of a query statement are analyzed to extract data lineage, etc.

[0003] However, the existing methods have many limitations. First, they do not support some common SQL writing methods in production, such as the case of full-field queries. In the existing methods, either the use of wildcards such as "*" to replace all fields in SQL is not allowed, or the wildcards are treated as ordinary fields, resulting in incomplete and inaccurate parsing results. For example, in a multi-table join query statement, in order to identify which table the table fields come from, the existing methods need to standardize the writing of the SQL statement, and the table alias must be explicitly added before the fields, otherwise the correct analysis cannot be performed. Second, the analysis of SQL statement table fields is widely used in scenarios such as data desensitization, SQL optimization, table field extraction, data lineage, and high-frequency table field identification. However, the existing methods only analyze specific categories of SQL statements or the outermost query fields in SQL statements, and do not provide a general analysis method to process the table fields in each level and each clause of SQL statements and establish an association relationship to meet the needs of different downstream tasks, with poor universality and limited application scenarios. Summary of the Invention

[0004] Aiming at the deficiencies of the prior art, the present invention proposes a method, memory, and device for analyzing SQL table fields based on an abstract syntax tree, which can effectively solve the limitations in processing full-field queries and multi-table joins in the existing solutions and has good universality.

[0005] To achieve the above object, on the one hand, the present invention provides a method for analyzing SQL table fields based on an abstract syntax tree, including:

[0006] Step S1, parsing an initial SQL statement into an abstract syntax tree;

[0007] Step S2, performing table field analysis on the abstract syntax tree to obtain the initial SQL statement and the parsing results of each level of query statements of the initial SQL statement as the initial parsing results;

[0008] Step S3: Traverse the initial parsing result, process the wildcards and / or fields lacking table aliases in multi-table associations, and supplement the source fields for the parsing results of the table fields in each clause of each level of query statements to obtain the final parsing result.

[0009] In one embodiment, in step S2, the following sub-steps are included:

[0010] Step S21: Obtain the parent query statement of the initial SQL statement, and this parent query statement is the SELECT statement part in the initial SQL statement;

[0011] Step S22: Convert this parent query statement into a simple query statement, and the simple query statement includes SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY clauses;

[0012] Step S23: Obtain the parsing results of the table fields in each clause of the simple query statement.

[0013] In one embodiment, in step S2, it further includes:

[0014] Create a query record table for each level of query statements to store the parsing results of each level of query statements, and generate a query serial number.

[0015] In one embodiment, in step S2, if this parent query statement is composed of a WITH clause and a first query statement,

[0016] then regard this first query statement as the simple query statement,

[0017] Extract the query part in the WITH clause as the sub-query statement at the first level of this parent query statement, regard the sub-query statement at the first level as a new query statement and re-execute steps S22 - S23 to obtain the parsing result of the sub-query statement at the first level, and link the parsing result of the sub-query statement at the first level as a sub-node to the parsing result of this parent query statement.

[0018] In one embodiment, in step S2, if this parent query statement is a set query statement, regard two or more SQL query statements in this set query statement as the second query statement and re-execute steps S22 - S23 to obtain the parsing result of the second query statement, and link the parsing result of the second query statement as a sub-node to the parsing result of this parent query statement.

[0019] In one embodiment, in step S23, for the SELECT clause, WHERE clause, ORDER BY clause, GROUP BY clause, and HAVING clause, the table field parsing results of each clause are obtained by parsing the query items in each clause.

[0020] In one embodiment, if the query item is a wildcard, the wildcard is treated as an ordinary field.

[0021] In one embodiment, if the query item is a field, the field name, field alias, and table alias of the query item are extracted and constructed into a column record and stored in the table field parsing result of the clause corresponding to the query item;

[0022] If the query item does not have a field alias, the field name is used as the field alias; if the query item does not have a table alias and there is no JOIN clause, the alias of the FROM clause is used as the table alias.

[0023] In one embodiment, if the query item is a complex expression, the field information in the expression is extracted as the table field parsing result of the clause corresponding to the query item.

[0024] In one embodiment, if the query item is a subquery statement, the query item is treated as a subquery statement of the current simple query statement.

[0025] In one embodiment, in step S23, for the FROM clause and JOIN clause,

[0026] If the source item in each clause is a table name, the table name is used to update the table field parsing result of the corresponding clause, including:

[0027] If the table alias in the table field parsing result before the update corresponding to the clause is the same as the alias of the clause, the table name in the source item is used to replace the table alias.

[0028] In one embodiment, for the FROM clause and JOIN clause, if the source item in each clause is a subquery statement, the source item is treated as a subquery statement of the current simple query statement, and the query statement alias of the subquery statement is set as the alias of the clause.

[0029] In one embodiment, step S3 includes the following sub-steps:

[0030] Step S31: Perform a post-order traversal on the initial parsing result. When there is no parsing result of a subquery statement in the current query record table,

[0031] Step S32: Process the wildcards in the table field parsing results of each clause in the current query record table;

[0032] Step S33: Process the missing table aliases in the table field parsing results of each clause in the current query record table;

[0033] Step S34: Supplement source fields for the table field parsing results of each clause in each level of query statements.

[0034] In one embodiment, in step S32,

[0035] In the table field parsing results of each clause, there is a case where the field name is a wildcard,

[0036] Determine whether the FROM clause in the query statement corresponding to the current query record table is a table name;

[0037] If it is a table name, query the table structure information of the current query record table by querying the database metadata, obtain all fields in the table, and construct a column record for each field and add it to the table field parsing results of this clause; delete the column record with the field name as a wildcard from the table field parsing results of this clause;

[0038] If it is not a table name, the FROM clause is a subquery statement. Use all the table field parsing results in the SELECT clause of this query record table to replace this wildcard, and replace the table alias in the table field parsing results with the alias of the FROM clause, and replace the field name with the field alias.

[0039] In one embodiment, if there is a JOIN clause in the query statement corresponding to the current query record table, merge the table field parsing results of the JOIN clause and the FROM clause and then replace the wildcard.

[0040] In one embodiment, in this step S33,

[0041] If there is a table alias in the table field parsing results, the current query statement is a multi-table association statement, and there is no explicitly specified table alias in front of the fields in this query statement, then supplement the missing table alias, including:

[0042] Determine whether the FROM clause and the JOIN clause are table names,

[0043] If they are table names, query the database metadata to obtain all fields in the table as the candidate fields for this clause or the JOIN clause;

[0044] If they are not table names, this clause is a subquery statement. Use all the fields in the SELECT clause of this query record table as the candidate fields for this clause;

[0045] If the field alias of the field to be selected is the same as the field name of the missing table alias, it indicates that the field of the missing table alias is from the clause to which the candidate field belongs, and the alias of this clause is used to supplement the missing table alias.

[0046] In one embodiment, in step S34,

[0047] If the parsing result of the sub-query statement exists in the current query record table, the parsing result of the table fields in the SELECT clause in the parsing result of the sub-query statement is used as the field to be selected;

[0048] Traverse the parsing results of the table fields of each clause in the current query record table in sequence. If the field name in the column record of the parsing result of the table fields is the same as the field alias of the field to be selected, and the table alias is the same as the alias of the sub-query statement to which the field to be selected belongs, then the field to be selected is added to the source field of the column record.

[0049] In one embodiment, after step S3, it further includes:

[0050] Step S4, according to the needs of the downstream task, process the final parsing result to obtain the expected output of the downstream task.

[0051] In one embodiment, the downstream task includes a data masking task. In this step S4, it specifically includes:

[0052] Obtain the output fields in each level of query statements, including:

[0053] Traverse the parsing results of the table fields in the SELECT clause of the query record table corresponding to the parent query statement, and determine the field alias in the corresponding column record as the output field;

[0054] Identify the mapping relationship between each output field and the source table field, including:

[0055] If the source field in the parsing result of the table fields is empty, directly determine the mapping relationship between the corresponding output field and the source table field using the table alias and field name corresponding to this source field;

[0056] If the source field in the parsing result of the table fields is not empty, continue to traverse the parsing result of this source field until the source field of this parsing result is empty, and then determine the mapping relationship between the corresponding output field and the source table field using the field name and table alias in this parsing result.

[0057] On the other hand, the present invention also provides a storage structure for the analysis result of table fields based on a two-way tree. This storage structure includes:

[0058] A query record table, which is used to store query serial numbers, query statement aliases, parsing results of query statements at all levels, and table field parsing results of each clause in query statements at all levels;

[0059] A column record, which is used to store table field parsing results of each clause, including field name, field alias, table alias, source field, and whether it is a final result item;

[0060] The parsing results of query statements at all levels are stored in the structure of the query record table, forming a two-way tree.

[0061] On the other hand, the present invention also provides an SQL table field analysis device based on an abstract syntax tree. The device includes:

[0062] An acquisition module, which is used to acquire an initial SQL statement to be analyzed;

[0063] A parsing module, which is used to parse the initial SQL statement into an abstract syntax tree through a parser;

[0064] A table field analysis module, which is used to perform table field analysis on the abstract syntax tree, obtain the initial SQL statement and parsing results of query statements at all levels of the initial SQL statement as initial parsing results, and store them in a two-way tree structure;

[0065] A metadata module, which is used to traverse the initial parsing results, process wildcards and / or fields lacking table aliases in multi-table associations, and supplement source fields for table field parsing results of each clause in query statements at all levels to obtain final parsing results.

[0066] In an embodiment, the device further includes: a downstream task module, which is used to process the final analysis result according to the needs of downstream tasks to obtain the expected output of downstream tasks.

[0067] As can be seen from the above solutions, the advantages of the present invention are:

[0068] The SQL table field analysis method based on an abstract syntax tree disclosed by the present invention generates an abstract syntax tree by parsing SQL, analyzes and processes table fields at each level and each clause in the SQL statement, stores the analysis results in a two-way tree structure, and introduces database metadata to process the situation where the table alias is missing before the field name in full-field queries and multi-table associations. This method can effectively solve the limitations in processing full-field queries and multi-table associations in existing solutions, and is a general analysis method that establishes the association relationship of table fields at each level and each clause in the SQL statement, and can meet the needs of different downstream tasks such as table field extraction, data desensitization, data lineage analysis, and high-frequency table field identification. Description of the Drawings

[0069] Figure 1 It shows the overall flowchart of the SQL table field analysis method based on the abstract syntax tree provided by an embodiment of the present invention;

[0070] Figure 2 It shows Figure 1 the detailed flowchart of step S2 in

[0071] Figure 3 It shows the schematic diagram of the bidirectional tree storage structure;

[0072] Figure 4 It shows Figure 1 the detailed flowchart of step S3 in

[0073] Figure 5 It shows the framework diagram corresponding to the table field analysis device;

[0074] Figure 6 It shows the schematic block diagram of the electronic device.

[0075] Where:

[0076] 200: Table field analysis device

[0077] 210: Acquisition module

[0078] 220: Parsing module

[0079] 230: Table field analysis module

[0080] 240: Metadata module

[0081] 250: Downstream task module

[0082] 300: Electronic device

[0083] 301: Computing unit

[0084] 302: Read-only memory

[0085] 303: Random access memory

[0086] 304: Bus

[0087] 305: I / O interface

[0088] 306: Input unit

[0089] 307: Output unit

[0090] 308: Storage unit

[0091] 309: Communication unit

[0092] S1 - S4, S21 - S23, S31 - S34: Steps. Detailed implementation manners

[0093] To make the above features and effects of the present invention more clearly and understandably described, specific embodiments are hereinafter given and described in detail in conjunction with the accompanying drawings of the specification as follows.

[0094] Please refer to Figures 1-4 as shown Figure 1 which shows a schematic diagram of the overall process of the SQL table field analysis method based on the abstract syntax tree provided by an embodiment of the present invention; Figure 2 which shows Figure 1 the detailed process schematic diagram of step S2 in Figure 3 which shows a schematic diagram of a two-way tree storage structure; Figure 4 which shows Figure 1 the detailed process schematic diagram of step S3 in

[0095] In an embodiment of the present invention, a memory is provided, and the storage structure of the memory includes:

[0096] A query record table for storing query serial numbers, query statement aliases, parsing results of query statements at all levels, and table field parsing results of each clause in query statements at all levels;

[0097] Column records for storing the table field parsing results of each clause, including field names, field aliases, table aliases, source fields, and whether they are final result items. That is, the table field parsing results of each clause are stored in the form of column record structures. And the parsing results of query statements at all levels are stored in the structure of the query record table, forming a two-way tree.

[0098] Based on this storage structure, please refer to Figures 1 to 4 as shown, in an embodiment of the present invention, a SQL table field analysis method based on the abstract syntax tree is further provided, including:

[0099] Step S1, parsing the initial SQL statement into an abstract syntax tree.

[0100] In one embodiment, JSqlParser is used to parse the SQL into an AST (abstract syntax tree). The nodes in the AST represent different parts of the SQL statement, such as SELECT statements, table names, column names, function calls, etc. Through the AST, it is more convenient to analyze, modify, and process SQL statements, such as extracting specific parts, detecting syntax errors, optimizing queries, etc. For other SQL parsing tools, such as ANTLR (ANother Tool for Language Recognition), Apache Calcite, sqlparse, etc., the idea of this solution is also applicable.

[0101] Step S2: Analyze the table fields of the abstract syntax tree to obtain the initial SQL statement and the parsing results of the query statements at all levels of the initial SQL statement as the initial parsing results, and store them in a two-way tree structure.

[0102] In this embodiment, refer to Figure 3 As shown in, the query statements at all levels of the initial SQL statement include the parent query statement obtained by decomposing the initial SQL statement, multiple first-level child query statements obtained by further decomposing the parent query statement, and multiple second-level child query statements obtained by further decomposing each first-level child query statement. Continuing to parse the query statements at each level in this way, the N-level child query statements can be obtained. The parsing results of the query statements at each level are all stored in a query record, that is, the parsing result of the parent query statement is stored in the query record QueryRecord#1, and the parsing results of the multiple first-level child query statements of the parent query statement are stored in the query records QueryRecord#2 to QueryRecord#m. For example, if the first-level child query statement Query1 includes two second-level child query statements Query2 and Query3, the parsing results of these two second-level child query statements are stored in the query records QueryRecord#n to QueryRecord#n + 1. In this way, it progresses layer by layer, where the serial numbers 1 to n + 1 represent the query serial numbers.

[0103] Specifically refer to Figure 2 As shown in, this step S2 specifically includes the following sub-steps:

[0104] Step S21: Obtain the parent query statement of the initial SQL statement, and this parent query statement is the SELECT statement part in the initial SQL statement.

[0105] The parent query statement refers to the statement that substantially queries the data tables and fields in the database. Specifically, if the initial SQL statement is a SELECT statement, then it itself is the parent query statement; if the initial SQL statement is a CREATE TABLE AS SELECT statement (CTAS), then the parent query statement is the SELECT statement part after AS in the CTAS; if the initial SQL statement is an INSERT INTO SELECT statement, then the query statement is the SELECT part in this statement.

[0106] Step S22: Convert the parent query statement into a simple query statement, and this simple query statement includes SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, and ORDER BY clauses.

[0107] In one embodiment, if the parent query statement consists of a WITH clause and a first query statement, the first query statement is regarded as the simple query statement, that is, the first query statement contains clauses such as SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, and ORDER BY. At the same time, the query part in the WITH clause is extracted as the first-level child query statement of the parent query statement, and steps S22 - S23 are re-executed as a new query statement to obtain the parsing result of the first-level child query statement, and the parsing result of the first-level child query statement is linked as a child node to the parsing result of the parent query statement.

[0108] In one embodiment, if the parent query statement is a set query statement, two or more SQL query statements in the set query statement are re-executed as the second query statement in steps S22 - S23 to obtain the initial parsing result of the second query statement, and the initial parsing result of the second query statement is linked as a child node to the parsing result of the parent query statement.

[0109] Step S23: Obtain the table field parsing result of each clause in the simple query statement.

[0110] For the SELECT clause, WHERE clause, ORDER BY clause, GROUP BY clause, and HAVING clause, the parsing methods of these clauses are the same, and the table field parsing results of each clause are obtained by parsing the query items in each clause. Specifically:

[0111] If the query item is the wildcard "*", the wildcard is treated as an ordinary field. If the query item is a field, the field name is initialized to the field name in the query item, the field alias is initialized to the field alias of the query item, and the field name, field alias, table alias, etc. of the query item are extracted and constructed as a column record and stored in the table field parsing result of the clause corresponding to the query item. In particular, if the query item does not have a field alias, the field name is used as the field alias; if the query item does not have a table alias and there is no JOIN clause, the alias of the FROM clause is used as the table alias; if there is a JOIN clause, the table alias is initialized to an empty string. The source field is initialized to an empty set for storing column record objects, whether it is the final result item is initialized to False. And the current column record ColumnRecord is added to the table field parsing result set of the SELECT clause in the query record table.

[0112] In addition, if the query item is a complex expression, such as a function expression, a conditional expression, etc., then extract the field information in the expression as the table field parsing result of the clause corresponding to the query item. If the query item is a subquery statement, then treat the query item as a subquery statement of the current simple query statement.

[0113] For the FROM clause and the JOIN clause, the parsing methods are the same. Specifically:

[0114] If the source item in each clause is a table name, use the table name to update the table field parsing result of the corresponding clause. That is, if the table alias of the table field parsing result before update corresponding to the clause is the same as the alias of the clause, then replace the table alias with the table name in the source item, and set whether it is the final result item to True. Here, for obtaining the aliases of the FROM clause and the JOIN clause, specifically, if the alias of the FROM clause does not exist, an alias is automatically generated; if there is a JOIN clause, then obtain or automatically generate the alias of each JOIN clause. In one embodiment, the rule for automatically generating the clause alias is clause type + serial number, such as "FROM5", where the serial number is obtained from an incrementing serial number generator.

[0115] If the source item in each clause is a subquery statement, then treat the source item as a subquery statement of the current simple query statement, and set the query statement alias of the subquery statement as the alias of the clause.

[0116] S24. Store the parsing results of each level of query statements, including the table field parsing results of each clause, in the query record, and generate a query serial number.

[0117] Through the above steps S21 - S23, generate the table field parsing results of each clause and the parsing results of each level of query statements. Store the table field parsing results of each clause in the column record ColumnRecord and link them to the parsing results of the corresponding levels of query statements. The parsing results of each level of query statements are stored in query record tables respectively, forming a storage in the form of a two-way tree, as Figure 3 shown, thereby generating the initial parsing result of the initial SQL statement.

[0118] Step S3. Traverse the initial parsing result, process the wildcards and / or the fields lacking table aliases in the multi-table association, and supplement the source fields for the table field parsing results of each clause of each level of query statements to obtain the final parsing result.

[0119] In this embodiment, post-order traversal is used to traverse the initial analysis result. This post-order traversal ensures that the access order of each node in the tree is to access its child nodes first and then the node itself, thereby ensuring that all child nodes of each node have been accessed and processed before the node is accessed. The database metadata refers to the descriptive information about various objects stored in the database. In the embodiment, it specifically refers to the table structure information, and all field names of the specified table are obtained through the table structure information. In the embodiment, the table structure information of a given table is obtained through the DatabaseMetaData interface of JDBC (Java Database Connectivity, a set of APIs for the Java programming language to interact with relational databases). For the method of using the metadata query function provided by the database management system to obtain the table structure information by using metadata query statements and system tables (or views), the idea of this solution is also applicable. Specifically, refer to Figure 4 As shown in, step S3 includes the following sub-steps:

[0120] Step S31: Perform post-order traversal on the initial parsing result, and determine whether there is a parsing result of a sub-query statement in the current query record table. If it exists, repeat steps S31 - SS4 for all parsing results of the sub-query statements. If not, that is, when there is no parsing result of a sub-query statement in the current query record table, directly execute step S32.

[0121] Step S32: Process the wildcards in the table field parsing results of each clause in the current query record table. Specifically, in this step, when there is a field name that is the wildcard "*" in the table field parsing results of each clause, it is further determined whether the FROM clause in the query statement corresponding to the current query record table is a table name. If it is a table name, query the table structure information of the current query record table by querying the database metadata, obtain all fields in the table, and construct a column record for each field and add it to the table field parsing results of this clause. The table alias in the column record is the table name of the FROM clause, and the field name and field alias are the field names obtained through metadata query. Finally, delete the column record with the field name being the wildcard "*" from the table field parsing results of this clause. If it is not a table name, it means that the FROM clause is a sub-query statement. Use all the table field parsing results in the SELECT clause of the query record table corresponding to this sub-query statement to replace this wildcard, replace the table alias in the table field parsing results with the alias of the FROM clause, and replace the field name with the field alias. Particularly, if there is a JOIN clause in the query statement corresponding to the current query record table, merge the table field parsing results of the JOIN clause and the FROM clause and then replace the wildcard.

[0122] In addition, in this step, when there is no case where the field name is the wildcard "*" in the table field parsing result of each clause, step S33 is directly executed.

[0123] Step S33: Process the missing table aliases in the table field parsing results of each clause in the current query record table. In this step, if there is a table alias in the table field parsing result, it means that the current query statement corresponding to the current query record table is a multi-table association statement, and there is no explicitly specified table alias before the fields in this query statement, then the missing table alias is supplemented. Specifically, it is judged whether the FROM clause and the JOIN clause are table names. If they are table names, the table structure information in the current query record table is queried by querying the database metadata, all the fields in the table are obtained, and a column record ColumnRecord is constructed for each field as the candidate field for this clause or the JOIN clause. If they are not table names, this clause is a subquery statement, and all the fields in the SELECT clause of the query record table corresponding to this subquery statement are used as the candidate fields for this clause. In addition, if the field alias of the candidate field is the same as the field name of the missing table alias, it means that the field with the missing table alias comes from the clause to which this candidate field belongs, and the alias of this clause is used to supplement the missing table alias.

[0124] Step S34: Supplement the source fields for the table field parsing results of each clause in each level of query statements. In this step, first, it is judged whether there is a parsing result of a subquery statement in the current query record table QueryRecord#m. If not, the process ends. If there is a parsing result of a subquery statement in the current query record table, the table field parsing result of the SELECT clause in the parsing result of this subquery statement is used as the candidate field for the corresponding subquery statement. Then, the table field parsing results of each clause in the current query record table are traversed in sequence. If the field name in the column record of the table field parsing result is the same as the field alias of the candidate field, and the table alias is the same as the alias of the subquery statement to which the candidate field belongs, then this candidate field is added to the set of source fields of the column record.

[0125] Through the above steps S31 - S34, the final parsing result of the initial SQL statement is generated and stored in the form of a two-way tree.

[0126] Step S4: Process the final parsing result according to the needs of the downstream task to obtain the expected output of the downstream task.

[0127] Downstream tasks include, but are not limited to, table field extraction, data lineage, data masking, identification of high-frequency table fields, etc. This embodiment presents a general SQL table field analysis method, and the final analysis results are stored in a table field analysis result storage structure based on a binary tree, which supports efficient traversal to meet the needs of different downstream tasks. In one embodiment, taking data masking as an example, it illustrates how to use the final analysis results to obtain the expected output of downstream tasks.

[0128] The data masking task needs to identify the mapping relationship between the output fields of the query statement and the database source table fields, that is, the query items in the outermost SELECT clause of the query statement and the corresponding database table field information. It only needs to process the field parsing results of the SELECT clause of the query record table QueryRecord#1 corresponding to the parent query statement.

[0129] Specifically, first, obtain the output field names in each level of the query statement. That is, by traversing the table field parsing results of the SELECT clause of the query record table corresponding to the parent query statement, determine the field alias in the corresponding column record as the output field. Then, further identify the mapping relationship between each output field and the source table field. This mapping relationship can be one-to-one or one-to-many. If the source field in the table field parsing result is empty, directly determine the mapping relationship between the corresponding output field and the source table field using the table alias and field name corresponding to this source field. If the source field in the table field parsing result is not empty, continue to traverse the parsing results of this source field until the source field of the parsing result is empty, and then determine the mapping relationship between the corresponding output field and the source table field using the field name and table alias in this parsing result.

[0130] For example: For the query statement: "select t1.a as f1,t2.b as f2 from table1 join(selectb1as b from table2 union selectb2 as b from table3)as t2 wheretable1.c>1", where:

[0131] Table 1: Table field parsing results of the SELECT clause of the query record table corresponding to the parent query statement

[0132] Table alias Field alias Field name Source field table1 f1 a t2 f2 b b1 and b2 in the subquery

[0133] Table 2: Table field parsing results of the SELECT clause of the query record table corresponding to the child query statement

[0134] Table alias Field alias Field name Source field table2 b b1 table3 b b2

[0135] Specifically, by traversing the parsing results of the table fields in the SELECT clause of the query record table corresponding to the parent query statement, two output fields f1 and f2 corresponding to the field aliases are obtained. For the output field f1, if the source field is empty in the table field parsing results, then this table field itself is the source table field, and the mapping relationship between the corresponding output field f1 and the source table field is directly determined using the table alias and field name corresponding to this source field (for example, t1.a as f1 can directly obtain the result: f1->table1.a, that is, the f1 field in the output result comes from the a field of the source table table1). For the output field f2, if the source field is not empty in the parsing results of the table fields in the SELECT clause of the query record table corresponding to the parent query statement, then continue to traverse the parsing results of this source field until the source field in the parsing results of the table fields in the SELECT clause of the query record table corresponding to the corresponding subquery statement is empty, and then use the field name and table alias in this parsing result to determine the mapping relationship between the corresponding output field f2 and the source table field. (For example, for t2.b as f2, it is necessary to continue to traverse the parsing results of b1 and b2 in the subquery statement, and finally obtain the result f2->table2.b1,table3.b2, that is, the f2 field in the output result is composed of the b1 field of the source table table2 and the b2 field of the table3). In the current example, the final result is:

[0136] f1->table1.a

[0137] f2->table2.b1,table3.b2

[0138] In addition, in one embodiment, a table field extraction task is also provided. Specifically, table field extraction refers to obtaining all the tables and fields used in the initial SQL statement, traversing the final parsing results in any way, and by analyzing the parsing results of each clause field in the query record table, if there is a source field in this field parsing result, it is ignored; otherwise, this source field is added to the final parsing result. In the above example, the final result is: table1.a,table1.c,table2.b1,table3.b2.

[0139] This embodiment is only to illustrate that the SQL table field analysis method based on the abstract syntax tree in the present invention is a general and extensible method, not limited to the applications of the above two types of embodiments, and can be applied to the analysis methods of multiple tasks. Moreover, by using the storage structure of the table field analysis results based on the bidirectional tree, the analysis results can be efficiently processed to obtain the expected output of the downstream tasks.

[0140] In summary, the SQL table field analysis method based on the abstract syntax tree provided by the present invention parses the SQL to generate an abstract syntax tree, analyzes and processes the table fields at each level and in each clause of the SQL statement, stores the analysis results in a two-way tree structure, and introduces database metadata to handle the situation where the table alias is missing before the field name in full field queries and multi-table associations. This method can effectively solve the limitations in handling full field queries and multi-table associations in existing solutions, and is a general analysis method that establishes the association relationships of table fields at each level and in each clause of the SQL statement, and can meet the needs of different downstream tasks such as table field extraction, data desensitization, data lineage analysis, and identification of high-frequency table fields.

[0141] In addition, corresponding to the foregoing embodiments of the SQL table field analysis method based on the abstract syntax tree, the present invention also provides an embodiment of an SQL table field analysis device based on the abstract syntax tree.

[0142] Referring to Figure 5 , an SQL table field analysis device 200 provided by an embodiment of the present invention includes:

[0143] An acquisition module 210, configured to acquire an initial SQL statement to be analyzed.

[0144] A parsing module 220, configured to parse the initial SQL statement into an abstract syntax tree through a parser.

[0145] A table field analysis module 230, configured to perform table field analysis on the abstract syntax tree, obtain the initial SQL statement and the parsing results of each level of query statements of the initial SQL statement as initial parsing results, and store them in a two-way tree structure.

[0146] A metadata module 240, configured to traverse the initial parsing results, process wildcards and / or fields missing table aliases in multi-table associations, and supplement source fields for the table field parsing results of each clause of each level of query statements to obtain final parsing results.

[0147] A downstream task module 250, configured to process the final analysis results according to the needs of downstream tasks to obtain the expected output of the downstream tasks.

[0148] Those skilled in the art can clearly understand that for the convenience and brevity of description, the specific working processes of the described modules can refer to the corresponding processes in the foregoing method embodiments, and will not be elaborated herein.

[0149] According to an embodiment of the present invention, the present invention also provides an electronic device.

[0150] Figure 6FIG. 0 shows a schematic block diagram of an electronic device 300 that can be used to implement an embodiment of the present invention. The electronic device is intended to represent various forms of digital computers, such as, for example, laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device can also represent various forms of mobile devices, such as, for example, personal digital processors, cellular telephones, smart phones, wearable devices, and other similar computing devices. The components shown in the present invention, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the present invention described and / or claimed in the present invention.

[0151] The electronic device 300 includes a computing unit 301 that can perform various appropriate actions and processes according to a computer program stored in a read-only memory (ROM) 302 or a computer program loaded from a storage unit 308 into a random access memory (RAM) 303. In the RAM 303, various programs and data required for the operation of the electronic device 300 can also be stored. The computing unit 301, the ROM 302, and the RAM 303 are connected to each other via a bus 304. An input / output (I / O) interface 305 is also connected to the bus 304.

[0152] A plurality of components in the electronic device 300 are connected to the I / O interface 305, including: an input unit 306, such as a keyboard, a mouse, etc.; an output unit 307, such as various types of displays, speakers, etc.; a storage unit 308, such as a magnetic disk, an optical disk, etc.; and a communication unit 309, such as a network card, a modem, a wireless communication transceiver, etc. The communication unit 309 allows the device 300 to exchange information / data with other devices via a computer network such as the Internet and / or various telecommunication networks.

[0153] The computing unit 301 may be various general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of the computing unit 301 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various dedicated artificial intelligence (AI) computing chips, various computing units running machine learning model algorithms, a digital signal processor (DSP), and any suitable processor, controller, microcontroller, etc. The computing unit 301 executes the various methods and processes described above, such as steps S1 to S4. For example, in some embodiments, steps S1 to S4 may be implemented as a computer software program that is tangibly contained in a machine-readable medium, such as the storage unit 308. In some embodiments, part or all of the computer program may be loaded and / or installed onto the device 300 via the ROM 302 and / or the communication unit 309. When the computer program is loaded into the RAM 303 and executed by the computing unit 301, one or more steps of the methods S1 to S4 described above may be executed. Alternatively, in other embodiments, the computing unit 301 may be configured to execute steps S1 to S4 in any other suitable manner (e.g., by means of firmware).

[0154] The various embodiments of the systems and techniques described above in the present invention can be implemented in digital electronic circuit systems, integrated circuit systems, field-programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard products (ASSPs), system-on-chip systems (SOCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include: being implemented in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a special or general-purpose programmable processor, and can receive data and instructions from a storage system, at least one input device, and at least one output device, and transmit the data and instructions to the storage system, the at least one input device, and the at least one output device.

[0155] The program code for implementing the methods of the present invention can be written in any combination of one or more programming languages. These program codes can be provided to the processor or controller of a general-purpose computer, a special-purpose computer, or other programmable data processing devices, such that when the program codes are executed by the processor or controller, the functions / operations specified in the flowcharts and / or block diagrams are implemented. The program codes can be executed entirely on the machine, partially on the machine, as an independent software package partially on the machine and partially on a remote machine, or entirely on a remote machine or server.

[0156] In the context of the present invention, a machine-readable medium can be a tangible medium that can contain or store a program for use by or in connection with an instruction execution system, apparatus, or device. A machine-readable medium can be a machine-readable signal medium or a machine-readable storage medium. A machine-readable medium can include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatuses, or devices, or any suitable combination of the foregoing. More specific examples of a machine-readable storage medium would include an electrical connection based on one or more wires, a portable computer diskette, a hard disk, a random access memory (RAM), a read-only memory (ROM), an erasable programmable read-only memory (EPROM or Flash memory), an optical fiber, a portable compact disc read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

[0157] To provide for interaction with a user, the systems and techniques described herein can be implemented on a computer having: a display device (e.g., a CRT (cathode ray tube) or LCD (liquid crystal display) monitor) for displaying information to the user; and a keyboard and a pointing device (e.g., a mouse or a trackball) by which the user can provide input to the computer. Other kinds of devices can also be used to provide for interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including acoustic, speech, or tactile input).

[0158] The systems and techniques described herein can be implemented in a computing system that includes back-end components (e.g., as a data server), or a computing system that includes middleware components (e.g., an application server), or a computing system that includes front-end components (e.g., a user computer having a graphical user interface or a web browser through which the user can interact with an implementation of the systems and techniques described herein), or a computing system that includes any combination of such back-end components, middleware components, or front-end components. The components of the system can be interconnected by any form or medium of digital data communication (e.g., a communication network). Examples of communication networks include: a local area network (LAN), a wide area network (WAN), and the Internet.

[0159] A computer system can include a client and a server. The client and the server are generally remote from each other and typically interact through a communication network. The client-server relationship is created by computer programs running on the respective computers and having a client-server relationship to each other. The server can be a cloud server, a server of a distributed system, or a server incorporating a blockchain.

[0160] It should be understood that the various forms of processes shown above can be used, with steps reordered, added or deleted. For example, the steps described in the present invention can be executed in parallel, sequentially or in different orders, as long as the desired results of the technical solution of the present invention can be achieved, and the present invention is not limited herein.

[0161] The above specific embodiments do not constitute a limitation on the protection scope of the present invention. Those skilled in the art should understand that various modifications, combinations, sub - combinations and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions and improvements made within the spirit and principle of the present invention shall be included within the protection scope of the present invention.

Claims

1. A method for analyzing SQL table fields based on an abstract syntax tree, characterized in that, Comprising: Step S1: Parse the initial SQL statement into an abstract syntax tree; Step S2: Perform table field analysis on the abstract syntax tree to obtain the initial SQL statement and the parsing results of each level of query statements of the initial SQL statement as the initial parsing results, including: Step S21: Obtain the parent query statement of the initial SQL statement; Step S22: Convert the parent query statement into a simple query statement of a clause; Step S23: Obtain the table field parsing results of each clause in the simple query statement; Step S3: Perform a post-order traversal on the initial parsing results, Delete or replace the wildcards in the table field parsing results of each clause in the current query record table, Supplement the missing table aliases in the table field parsing results of each clause in the current query record table with the aliases of the clauses to which the candidate fields belong, When there are parsing results of subquery statements in the current query record table, supplement the source fields for the table field parsing results of each clause of each subquery statement to obtain the final parsing results.

2. The method according to claim 1, wherein The parent query statement is the SELECT statement part in the initial SQL statement; The simple query statement includes SELECT, FROM, WHERE, JOIN, GROUP BY, HAVING, ORDER BY clauses.

3. The method according to claim 2, wherein Step S2 further includes: Create a query record table for each level of query statement to store the parsing results of each level of query statement and generate a query serial number.

4. The method according to claim 2 or 3, characterized in that, If the parent query statement consists of a WITH clause and a first query statement, Then regard the first query statement as the simple query statement, Extract the query part in the WITH clause as the subquery statement of the first level of the parent query statement, take the subquery statement of the first level as a new query statement and re-execute steps S22 to S23 to obtain the parsing results of the subquery statement of the first level, and link the parsing results of the subquery statement of the first level as child nodes to the parsing results of the parent query statement.

5. The method according to claim 2 or 3, characterized in that, If the parent query statement is a set query statement, take two or more SQL query statements in the set query statement as the second query statement and re-execute steps S22 to S23 to obtain the parsing results of the second query statement, and link the parsing results of the second query statement as child nodes to the parsing results of the parent query statement.

6. The method according to claim 2 or 3, characterized in that, In step S23, For each of the SELECT clause, WHERE clause, ORDER BY clause, GROUP BY clause, and HAVING clause, obtain the table field parsing results of each clause by parsing the query items in each clause.

7. The method according to claim 6, characterized in that, If the query item is a wildcard, treat the wildcard as an ordinary field.

8. The method according to claim 6, characterized in that, If the query item is a field, extract the field name, field alias, and table alias of the query item, and construct them into column records to be stored in the table field parsing result of the clause corresponding to the query item. If the query item does not have a field alias, use the field name as the field alias; if the query item does not have a table alias and there is no JOIN clause, use the alias of the FROM clause as the table alias.

9. The method according to claim 6, wherein If the query item is a complex expression, extract the field information in the expression as the table field parsing result of the clause corresponding to the query item. Or If the query item is a subquery statement, treat the query item as a subquery statement of the current simple query statement.

10. The method according to claim 2 or 3, characterized in that, In step S23, For the FROM clause and JOIN clause, If the source item in each clause is a table name, use the table name to update the table field parsing result of the corresponding clause, including: If the table alias in the table field parsing result before the update corresponding to the clause is the same as the alias of the clause, use the table name in the source item to replace the table alias. Or If the source item in each clause is a subquery statement, treat the source item as a subquery statement of the current simple query statement, and set the query statement alias of the subquery statement as the alias of the clause.

11. The method according to claim 1, wherein When there is a field name that is a wildcard in the table field parsing result of each clause, Judge whether the FROM clause in the query statement corresponding to the current query record table is a table name; If it is a table name, query the table structure information of the current query record table by querying the database metadata, obtain all the fields in the table, and construct column records for each field and add them to the table field parsing result of the clause. Delete the column record with the field name as the wildcard from the table field parsing result of the clause. If it is not a table name, the FROM clause is a subquery statement, use all the table field parsing results in the SELECT clause of the query record table to replace the wildcard, and replace the table alias in the table field parsing result with the alias of the FROM clause, and replace the field name with the field alias.

12. The method according to claim 11, wherein If there is a JOIN clause in the query statement corresponding to the current query record table, merge the table field parsing result of the JOIN clause and the table field parsing result of the FROM clause and then replace the wildcard.

13. The method according to claim 1, wherein Judge whether the FROM clause and the JOIN clause are table names, If they are table names, obtain all the fields in the table by querying the database metadata as the candidate fields of the clause or the JOIN clause; If they are not table names, the clause is a subquery statement, and use all the fields in the SELECT clause of the query record table as the candidate fields of the clause; If the field alias of the candidate field is the same as the field name of the missing table alias, it means that the field with the missing table alias comes from the clause to which the candidate field belongs, and use the alias of the clause to supplement the missing table alias.

14. The method according to claim 1, wherein if there is a parsing result of a sub-query statement in the current query record table, the parsing result of the table fields in the SELECT clause in the parsing result of the sub-query statement is used as the candidate field; traverse the parsing results of the table fields of each clause in the current query record table in sequence. If the field name in the column record of the parsing result of the table fields is the same as the field alias of the candidate field, and the table alias is the same as the alias of the sub-query statement to which the candidate field belongs, then add the candidate field to the source field of the column record.

15. The method according to claim 1, characterized in that, After step S3, it further includes: Step S4. According to the requirements of the downstream task, process the final parsing result to obtain the expected output of the downstream task.

16. The method according to claim 15, wherein The downstream task includes a data masking task. In this step S4, it specifically includes: Obtain the output fields in each level of query statements, including: Traverse the parsing results of the table fields in the SELECT clause of the query record table corresponding to the parent query statement, and determine the field alias in the corresponding column record as the output field; Identify the mapping relationship between each output field and the source table field, including: If the source field in the parsing result of the table fields is empty, directly determine the mapping relationship between the corresponding output field and the source table field using the table alias and field name corresponding to the source field; If the source field in the parsing result of the table fields is not empty, continue to traverse the parsing result of the source field until the source field of the parsing result is empty, and then determine the mapping relationship between the corresponding output field and the source table field using the field name and table alias in the parsing result.

17. A SQL table field analysis device based on an abstract syntax tree, characterized in that, The device includes: An acquisition module, configured to acquire an initial SQL statement to be analyzed; A parsing module, configured to parse the initial SQL statement into an abstract syntax tree through a parser; A table field analysis module, configured to perform table field analysis on the abstract syntax tree to obtain the initial SQL statement and the parsing results of each level of query statements of the initial SQL statement as the initial parsing result, and store them in a two-way tree structure, including: obtaining the parent query statement of the initial SQL statement, converting the parent query statement into a simple query statement of clauses, and obtaining the parsing results of the table fields of each clause in the simple query statement; A metadata module, configured to perform a post-order traversal on the initial parsing result, and delete or replace wildcards in the parsing results of the table fields of each clause in the current query record table; Supplement the missing table alias in the parsing results of the table fields of each clause in the current query record table with the alias of the clause to which the candidate field belongs; And when there is a parsing result of a sub-query statement in the current query record table, supplement the source field for the parsing results of the table fields of each clause of each sub-query statement.

Images