A SQL element parsing method for executing SQL statements

Abstract

The present invention relates to the technical field of data analysis, and in particular to a SQL element parsing method capable of executing SQL statements. The method comprises decomposing an initial SQL statement into a plurality of word units; generating a parsing tree; identifying a table name and a projection field; and executing a SQL statement query. The present invention automatically converts a natural language question input by a user into an SQL query statement that can be executed in a database through a certain conversion rule, performs word segmentation processing on the SQL statement, decomposes the entire SQL statement into each word unit, automatically extracts key information such as a table name and a field in the SQL statement, and records information on data flow and data table references, parses the SQL query statement input by the user, checks whether the word units are arranged in a correct order and structure according to predefined SQL grammar rules, avoids inputting data with incorrect format, quickly queries and accurately analyzes data, and improves the accuracy of generating SQL query statements by optimizing queries, thereby improving the accuracy and efficiency of queries.

Description

Technical Field

[0001] The present invention relates to the technical field of data analysis, and in particular to a SQL element parsing method for executable SQL statements. Background Art

[0002] In modern data warehouse environments, SQL is the main tool for processing and managing data. SQL tasks in data warehouses often involve complex query, insert, update, and delete operations that affect multiple data tables and fields. Therefore, it is crucial to track data flows and record data table information referenced in SQL tasks. This information can be used for debugging, optimization, and auditing purposes. Traditionally, data flows and data table references are recorded through manual annotations and logging, but this method is inefficient and error-prone. In order to automate and simplify this process, SQL parsing technology can be used to automatically extract key elements in SQL statements and record relevant information.

[0003] The patent document with publication number CN104199831B discloses an information processing method and device, which includes: identifying basic elements in SQL code based on a first strategy; performing combination operations on the basic elements parsed from the SQL code to obtain SQL statements and construct a syntax tree; traversing the SQL statements in the syntax tree, and based on the types of basic elements in the traversed SQL statements and the correspondence between the types of the basic elements and the nodes, constructing nodes corresponding to the basic elements in the traversed SQL statements to obtain an intermediate language description of the syntax tree; based on the intermediate language description of the syntax tree, constructing a data flow graph corresponding to the SQL code; It can be seen that the existing SQL parsing technology lacks automatic extraction of key information such as table names and fields in SQL statements, and records data flow and data table reference information, which makes the possibility of human errors high and the efficiency of parsing SQL statements low. Summary of the invention

[0004] To this end, the present invention provides a sql element parsing method for executable sql statements, which is used to overcome the problem in the prior art of lack of multi-stage calculation of similarity and automatic extraction of the most matching table name, field and other key information in the SQL statement, and recording of data flow and data table reference information, resulting in low accuracy in parsing SQL statements.

[0005] To achieve the above object, the present invention provides a sql element parsing method for executable sql statements, comprising:

[0006] Convert the log data input by the user into several initial SQL statements, decompose any initial SQL statement to obtain several tokens;

[0007] Analyzing whether the order and structural arrangement of the word units are correct according to predefined grammar rules, so as to connect the word units according to the operator priority and grouping symbols in the initial SQL statement to generate a parse tree;

[0008] Accessing a metadata view in a database to extract a table structure set, performing semantic analysis on the parse tree according to the table structure set, identifying a table name and a projection field according to the semantic analysis result, and constructing a SQL query statement according to the identified table name and projection field, wherein the SQL query statement is a target SQL query statement or a fuzzy SQL query statement;

[0009] A query step list is generated according to the SQL query statement to instruct the database to execute the initial SQL query statement and output a query result, and whether to adjust the standard similarity is determined according to the query result.

[0010] Furthermore, the table structure set includes a plurality of table name sets and a plurality of column name sets, and semantic analysis of the parse tree according to the table structure set includes:

[0011] Extracting keywords from the parse tree, and matching the keywords from the parse tree with words in each of the table name sets or words in the column name set;

[0012] The matching similarity, set similarity and association similarity are calculated based on the matching results.

[0013] Furthermore, the matching similarity includes column name matching similarity and table name matching similarity, and calculating the matching similarity includes:

[0014] Marking words matching the keyword as matching fields;

[0015] Acquire each of the table name sets with matching field tags as a candidate table name set, or acquire each of the column name sets with matching field tags as a candidate column name set;

[0016] The percentage of the number of matching fields in each candidate table name set to the total number of words in the candidate table name set is calculated respectively to obtain the corresponding table name matching similarity, and the percentage of the number of matching fields in each candidate column name set to the total number of words in the candidate column name set is calculated respectively to obtain the corresponding column name matching similarity.

[0017] Furthermore, the set similarity includes table name set similarity and column name set similarity, and calculating the set similarity includes:

[0018] When there is no table name matching similarity greater than or equal to the standard similarity, or there is no column name matching similarity greater than or equal to the standard similarity, constructing a keyword set according to the keywords of the parse tree;

[0019] The table name set similarity between the keyword set and the candidate table name set is calculated, or the column name set similarity between the keyword set and the candidate column name set is calculated.

[0020] Furthermore, the association similarity includes table name association similarity and column name association similarity, and calculating the association similarity includes:

[0021] When there is no table name set whose similarity is greater than or equal to the standard similarity, or when there is no column name set whose similarity is greater than or equal to the standard similarity, obtaining an associated entity of the keyword in the database, and matching the associated entity with a word in the candidate table name set or with a word in the candidate column name set;

[0022] Calculate the percentage of the sum of the number of corrected table names and the number of matching fields to the total number of words in the candidate table name set to obtain the table name association similarity;

[0023] Or the percentage of the sum of the number of modified table names and the number of matching fields in the total number of words in the candidate column name set is calculated to obtain the column name association similarity.

[0024] Further, identifying the table name and the projection field according to the semantic analysis result includes:

[0025] The table name, projection field, table name to be detected and projection field to be detected of the SQL query statement are selected according to matching similarity, set similarity or association similarity.

[0026] Furthermore, the target SQL query statement is constructed according to the table name and the projection field, and the real-time response duration of executing the target SQL query statement is obtained. When the real-time response duration is greater than the standard response duration, the standard similarity is adjusted to a modified first similarity.

[0027] Further, construct a fuzzy SQL query statement according to the table name to be detected and the projection field to be detected;

[0028] Obtaining the real-time response time of executing the fuzzy SQL query statement;

[0029] When the real-time response time is less than or equal to the standard response time, it is determined that the query result meets expectations, the standard similarity is adjusted to a modified second similarity, and the table name to be detected and the projection field to be detected are stored in the database.

[0030] Furthermore, the real-time response time of executing the fuzzy SQL query statement is obtained. When the real-time response time is longer than the standard response time, it is preliminarily determined that the query result does not meet expectations, and an error prompt is output.

[0031] Further, converting the log data input by the user into the initial SQL statement includes:

[0032] Preprocessing the log data to generate input text;

[0033] The input text is divided into a number of initial SQL statements according to the positions of question marks.

[0034] Compared with the prior art, the beneficial effect of the present invention lies in that the natural language question input by the user is automatically converted into an SQL query statement that can be executed in the database through certain conversion rules, the entire SQL statement is decomposed into individual words by performing word segmentation processing on the SQL statement, key information such as table name and field in the SQL statement is automatically extracted, and information on data flow and data table reference is recorded, the SQL query statement input by the user is parsed, and the words are checked according to predefined SQL grammar rules to see whether they are arranged in the correct order and structure, so as to avoid inputting erroneous data in the format, so as to realize fast query and accurate data analysis, and ensure the real-time and accuracy of the query results by dynamically generating and executing SQL query statements, and improve the accuracy of generated SQL query statements by constructing and optimizing queries, thereby improving the accuracy and efficiency of queries.

[0035] Furthermore, since some questions do not specify all the necessary components of SQL statements, such as table names or projection fields, the keywords in the parse tree are matched with words in the database to predict and construct SQL query statements, thereby ensuring that the queries entered by users are accurately converted into executable SQL queries, thereby improving the applicability of the system.

[0036] Furthermore, by associating and identifying aliases, the expression of SQL statements is corrected to extract matching words as table names or projection fields, avoiding missing matching items, thereby improving the accuracy of generating SQL query statements and further improving query efficiency.

[0037] Furthermore, by analyzing the execution of the constructed SQL query statement, the generation method of the SQL query statement is optimized. When it is initially determined that the query result does not meet expectations, the standard similarity is increased to reduce the possibility of falling into the generation of the target SQL query statement and increase the possibility of falling into the generation of the fuzzy SQL query statement, so as to improve the accuracy of selecting table names and projection fields, thereby improving the query quality.

[0038] Furthermore, the actual execution time of the fuzzy SQL query statement is analyzed to determine the accuracy of the generated table name to be detected and the projection field to be detected. If the real-time response time is determined to be less than or equal to the standard response time, it means that the query result meets expectations, indicating that the generated table name to be detected and the projection field to be detected have high accuracy. The table name to be detected and the projection field to be detected are stored in the database as new data. By adaptively reducing the standard similarity, unnecessary strict judgments are reduced, time and computing resources are saved, the entire detection process is made more flexible and efficient, the accuracy of the query results is verified, and the data quality is improved. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] Figure 1 It is a flowchart of a method for parsing SQL elements that can execute SQL statements according to an embodiment of the present invention;

[0040] Figure 2 A logical decision diagram for calculating matching similarity, set similarity and association similarity for an embodiment of the present invention;

[0041] Figure 3 Constructing a logical decision diagram of a SQL query statement for an embodiment of the present invention;

[0042] Figure 4 This is a logical decision diagram for correcting standard similarity according to an embodiment of the present invention. DETAILED DESCRIPTION

[0043] In order to make the objects and advantages of the present invention more clearly understood, the present invention is further described below in conjunction with embodiments; it should be understood that the specific embodiments described herein are only used to explain the present invention and are not used to limit the present invention.

[0044] The preferred embodiments of the present invention are described below with reference to the accompanying drawings. It should be understood by those skilled in the art that these embodiments are only used to explain the technical principles of the present invention and are not intended to limit the protection scope of the present invention.

[0045] It should be noted that, in the description of the present invention, terms such as "up", "down", "left", "right", "inside" and "outside" indicating directions or positional relationships are based on the directions or positional relationships shown in the drawings. This is merely for the convenience of description and does not indicate or imply that the device or element must have a specific orientation, be constructed and operated in a specific orientation. Therefore, it cannot be understood as a limitation on the present invention.

[0046] In addition, it should be noted that in the description of the present invention, unless otherwise clearly specified and limited, the terms "installed", "connected", and "connected" should be understood in a broad sense, for example, it can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For those skilled in the art, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0047] See also Figure 1 As shown, it is a flow chart of a method for parsing an SQL element that can execute an SQL statement according to an embodiment of the present invention. The present invention provides a method for parsing an SQL element that can execute an SQL statement, including:

[0048] Step S1, converting the log data input by the user into a number of initial SQL statements, decomposing any initial SQL statement to obtain a number of word elements, wherein the word elements include keywords, qualifiers, operators and special characters;

[0049] Step S2, analyzing whether the order and structural arrangement of the word units are correct according to predefined grammar rules, so as to connect the word units according to the operator priority and grouping symbols in the initial SQL statement to generate a parse tree;

[0050] Step S3, accessing the metadata view in the database to extract a table structure set, performing semantic analysis on the parse tree according to the table structure set, identifying table names and projection fields according to the semantic analysis results, and constructing a SQL query statement according to the identified table names and projection fields, wherein the SQL query statement is a target SQL query statement or a fuzzy SQL query statement;

[0051] Step S4, generating a query step list according to the SQL query statement to instruct the database to execute the initial SQL statement query and output the query result, and determining whether to adjust the standard similarity according to the query result.

[0052] In this embodiment, the preprocessing of the log data includes converting the image data in the log data into text data, converting the audio data into text data, and then merging the audio data with the original text data to obtain the input text. The input text is formatted and segmented to process the data in segments to improve the query efficiency. The entire initial SQL statement input is decomposed into individual words. For example, the initial SQL statement input: "SELECT name FROM employeeWHERE age>30" will be decomposed into SELECT, name, FROM, employee, WHERE, age, 30. Each token has a specific type, such as keyword, identifier, operator or value. The decomposed initial SQL statement is parsed to ensure that the decomposed token combination conforms to the grammatical rules of the SQL language. The predefined SQL grammar rules are used to check whether the tokens are arranged in the correct order and structure. For example, the SELECT keyword should be followed by one or more field names, and the FROM keyword should be followed by a table name, etc. If the SQL statement does not conform to the grammatical rules, the parser will throw an error. In the process of parsing the initial SQL statement, the relevant tokens are connected to build a parse tree. The parse tree is built according to the operator priority and grouping symbols in the SQL statement. It is a hierarchical structure that reflects the execution order of the SQL statement.

[0053] By automatically converting the natural language questions input by the user into SQL query statements that can be executed in the database through certain conversion rules, the entire SQL statement is decomposed into individual words through word segmentation, and the most matching table name, field and other key information in the SQL statement is automatically extracted through multi-stage similarity calculation and automatic extraction, and the data flow and data table reference information are recorded. The SQL query statement input by the user is parsed, and the word elements are checked according to the predefined SQL grammar rules to see whether they are arranged in the correct order and structure to avoid inputting erroneous data in the format, so as to achieve fast query and accurate data analysis, and ensure the real-time and accuracy of the query results by dynamically generating and executing SQL query statements, and improve the accuracy of generated SQL query statements by constructing and optimizing queries, thereby improving the accuracy and efficiency of queries.

[0054] Specifically, converting the log data entered by the user into the initial SQL statement includes:

[0055] Preprocessing the log data to generate input text;

[0056] The input text is divided into a number of initial SQL statements according to the positions of question marks.

[0057] The method is simple and direct, by identifying question marks in the input text and segmenting the text into different question sentences according to the positions of the question marks.

[0058] Specifically, the table structure set includes a plurality of table name sets, a plurality of column name sets and data types, and the semantic analysis of the parse tree according to the table structure set includes:

[0059] Extracting keywords from the parse tree, and matching the keywords from the parse tree with words in each of the table name sets or words in the column name set;

[0060] The matching similarity, set similarity and association similarity are calculated based on the matching results.

[0061] Since some questions do not specify all the necessary components of SQL statements, such as table names or projection fields, the keywords in the parse tree are matched with words in the database to predict and build SQL query statements, thereby ensuring that the queries entered by users are accurately converted into executable SQL queries, thereby improving the applicability of the system.

[0062] See also Figure 2 As shown, it is a logical decision diagram for calculating matching similarity, set similarity and association similarity in an embodiment of the present invention;

[0063] Specifically, the words matching the keyword are marked as matching fields, and each of the table name sets with matching field marks is obtained as a candidate table name set, or each of the column name sets with matching field marks is obtained as a candidate column name set, and the percentage of the number of matching fields in each of the candidate table name sets to the total number of words in the candidate table name set is calculated to obtain the corresponding table name matching similarity, and the percentage of the number of matching fields in each of the candidate column name sets to the total number of words in the candidate column name set is calculated to obtain the corresponding column name matching similarity, the matching similarity includes the column name matching similarity and the table name matching similarity, and the standard similarity is compared with the table name matching similarity or the column name matching similarity.

[0064] If there is a table name matching similarity greater than or equal to the standard similarity, select the candidate table name set with the largest table name matching similarity as the table name of the target SQL query statement;

[0065] If there is a column name matching similarity greater than or equal to the standard similarity, select the candidate column name set with the largest column name matching similarity as the projection field of the target SQL query statement;

[0066] If there is no table name matching similarity greater than or equal to the standard similarity, or there is no column name matching similarity greater than or equal to the standard similarity, a keyword set is constructed according to the keywords of the parse tree, and the table name set similarity of the keyword set and the candidate table name set is calculated, or the column name set similarity of the keyword set and the candidate column name set is calculated, where the set similarity includes the table name set similarity and the column name set similarity, and the standard similarity is compared with each set similarity,

[0067] If there is a table name set whose similarity is greater than or equal to the standard similarity, select the candidate table name set with the largest set similarity as the table name of the target SQL query statement;

[0068] If there is a column name set whose similarity is greater than or equal to the standard similarity, select the candidate column name set with the largest set similarity as the projection field of the target SQL query statement;

[0069] If there is no table name set whose similarity is greater than or equal to the standard similarity, or there is no column name set whose similarity is greater than or equal to the standard similarity, obtain the associated entity of the keyword in the database, match the associated entity with the words in the candidate table name set, match the associated entity with the words in the candidate column name set, calculate the percentage of the sum of the number of corrected table names and the number of matched fields in the total number of words in the candidate table name set, obtain the table name associated similarity, calculate the percentage of the sum of the number of corrected table names and the number of matched fields in the total number of words in the candidate column name set, obtain the column name associated similarity, compare the standard similarity with each associated similarity,

[0070] If there is a table name association similarity greater than or equal to the standard similarity, then select a candidate table name set with the largest table name association similarity, modify the words in the candidate table name set that match the associated entity to the associated entity, and use the modified table name set after replacement as the table name of the target SQL query statement;

[0071] If there is a column name association similarity greater than or equal to the standard similarity, then select a candidate column name set with the largest column name association similarity, modify the words in the candidate column name set that match the associated entity to the associated entity, and use the replaced modified column name set as the projection field of the target SQL query statement;

[0072] If there is no table name with an association similarity greater than or equal to the standard similarity, or there is no column name with an association similarity greater than or equal to the standard similarity, the largest candidate table name set among the table name matching similarity, the table name set similarity, and the table name association similarity is selected as the table name to be detected, and the largest candidate column name set among the column name matching similarity, the column name set similarity, and the column name association similarity is selected as the projection field to be detected.

[0073] The standard similarity set in this embodiment is set between 60% and 90%, and is selected and adjusted according to the size and characteristics of the data actually processed; the matching similarity is the degree of matching between the tables and fields involved and the parse tree, the table name matching similarity indicates the percentage of the keywords involved in the parse tree in the table name set to the total number of words in the table, the column name matching similarity indicates the percentage of the keywords involved in the parse tree in the column name set to the total number of fields in the column name set, and the most matching table name and projection field are selected by calculating the matching similarity, the set similarity is the similarity between the keyword set and a set, and the set is composed of several column name sets and table name sets, and the association similarity is the degree of matching between the sum of the keywords and synonyms in the parse tree and the parse tree, by first calculating the table involved and the parse tree The degree of match is to select the table with the greatest degree of match as the table name. However, due to differences in context, incomplete keywords in the parse tree, or the use of synonyms, the matching similarity is low. In this case, by calculating the set similarity, the overlap degree of the two sets is analyzed in connection with the context to improve the accuracy of selecting table names and projection fields. At the same time, when the set similarity is not high, that is, it does not meet the standard, the synonyms of the keywords in the parse tree are identified and the similarity is recalculated to effectively select the accuracy of table names and projection fields. The most matching table name and projection field are selected through multi-stage similarity calculation to improve query accuracy and efficiency. For example, the keywords in the parse tree ["user", login, data], the standard similarity is 0.7,

[0074] Table name set 1: ["user", login, "info"],

[0075] Table name set 2: ["customer","profile",data],

[0076] Table name set 3: ["user","profile","data"],

[0077] Calculate matching similarity:

[0078] Table name set 1: Keyword matching ["user", login], the number of matches is 2, the total number is 3, and the matching similarity is 2 / 3≈0.67;

[0079] Table name set 2: keyword matches ["data"], the number of matches is 1, the total number is 3, and the matching similarity is 1 / 3≈0.33;

[0080] Table name set 3: Keyword match ["user"], number of matches is 1, total number is 3, and the matching similarity is 1 / 3 = 0.33;

[0081] Since the matching similarity of all table name sets is less than 0.7, we need to calculate the set similarity:

[0082] Table name set 1: The intersection element is ["user", "login"],

[0083] The union elements are ["user", "login", data, login],

[0084] Jaccard similarity is 2 / 4 = 0.5;

[0085] Table name set 2: The intersection element is ["data"],

[0086] The union elements are ["user", login, data],

[0087] Jaccard similarity is 1 / 3≈0.33;

[0088] Table name set 3: The intersection element is ["user"],

[0089] The union elements are ["user", login, data],

[0090] Jaccard similarity is 1 / 3 = 0.33,

[0091] Since the set similarity of all table name sets is less than 0.7, it is necessary to calculate the association similarity;

[0092] Related word set: ["user","login","auth","access","customer"]

[0093] Table name set 1: The matching keywords and aliases are ["user", "login"], and the association similarity is 2 / 3≈0.67;

[0094] Table name set 2: The matching keywords and aliases are ["login", "data", "customer"], and the association similarity is 3 / 3 = 1.0;

[0095] Table name set 3: The matching keywords and aliases are ["user", "data"], and the association similarity is 2 / 3≈0.67;

[0096] The table name set with the largest association similarity is selected as the table name, so table name set 2 is selected. A SQL query statement is generated based on the selected table name set 2, and the accuracy of the query result is checked. If the accuracy is insufficient, the standard similarity is adjusted and recalculated.

[0097] In this embodiment, the Jaccard similarity index is used to calculate the table name set similarity of the keyword set and the candidate table name set to measure the degree of vocabulary overlap between the two texts to characterize the degree of relevance between the two texts. The corresponding table name set similarity and column name set similarity are obtained by calculating the number of elements in the intersection of the two sets minus the number of elements in the union. Any keyword set includes all the keywords of the corresponding parse tree, and each table name set represents part of the elements in the database. The candidate table name set constitutes all the vocabulary related to the parse tree in the database, and its elements include table names and corresponding aliases.

[0098] By performing text matching between the keywords in the parse tree and the words in the database, the matching items with the keywords in each set in the database are first determined as matching fields, and the matching degree between the set and the keyword is determined according to the proportion of the matching fields. The higher the proportion of the matching fields, the more words in the set match the keywords, and the corresponding elements in the set are selected as the table name. The method is simple and effective.

[0099] By comparing the Jaccard distance between the question keyword set and the vocabulary set related to each table name, the table name that best matches the question intent is selected, and the same is true for the column name. The projection field in the query statement is determined based on the column name to generate an accurate SQL query statement.

[0100] The associated entity is an alias for the keyword. Since table names and projection fields usually have aliases, the aliases are identified through association and the expression of SQL statements is corrected to extract matching words as table names or projection fields to avoid missing matching items, thereby improving the accuracy of generated SQL query statements and further improving query efficiency.

[0101] See also Figure 3 As shown, it is a logical decision diagram for constructing a SQL query statement in an embodiment of the present invention;

[0102] Specifically, identifying the table name and the projection field according to the semantic analysis result includes:

[0103] Select the table name, projection field, table name to be detected and projection field to be detected of the SQL query statement according to matching similarity, set similarity or association similarity;

[0104] Construct a target SQL query statement according to the table name and projection field;

[0105] Construct a fuzzy SQL query statement according to the table name to be detected and the projection field to be detected.

[0106] See also Figure 4 As shown, it is a logical decision diagram for correcting the standard similarity according to an embodiment of the present invention;

[0107] Specifically, the real-time response time of executing the target SQL query statement is obtained, and the real-time response time is determined according to the standard response time.

[0108] If the real-time response time is less than or equal to the standard response time, the query result is determined to be in line with expectations;

[0109] If the real-time response time is longer than the standard response time, it is preliminarily determined that the query result does not meet expectations, and the standard similarity is adjusted to a modified first similarity;

[0110] Wherein, Ab1′=Ab×[1+(Ts−Tb) / Ts], Ab1′ is the modified first similarity, Ab is the standard similarity, Tb is the standard response time, and Ts is the real-time response time.

[0111] The standard response time set in this embodiment is the standard time for executing SQL query statements, which is affected by the actual amount of data and increases with the increase of data volume. It is also related to the load of the database system. Generally, it is set between a few milliseconds and a few minutes, and is adjusted according to the actual execution environment.

[0112] By analyzing the execution of the constructed target SQL query statement, the generation method of SQL query statement is optimized. When it is initially determined that the query result does not meet expectations, the standard similarity is increased to reduce the possibility of falling into the generation of target SQL query statement and increase the possibility of falling into the generation of fuzzy SQL query statement, so as to improve the accuracy of selecting table names and projection fields, thereby improving query quality.

[0113] By analyzing the actual execution time of the SQL query statement, we can determine whether the query result meets expectations and optimize the generation method of the SQL query statement to improve the query quality.

[0114] Specifically, the real-time response time of executing the fuzzy SQL query statement is obtained, and the real-time response time is determined according to the standard response time.

[0115] If the real-time response time is less than or equal to the standard response time, it is determined that the query result meets expectations, the standard similarity is adjusted to a modified second similarity, and the table name to be detected and the projection field to be detected are stored in the database;

[0116] If the real-time response time is longer than the standard response time, it is preliminarily determined that the query result does not meet expectations, an error prompt is output, and the table name to be detected and the projection field to be detected are not stored;

[0117] Wherein, Ab2′=Ab×[1-(Tb-Ts) / Tb], Ab2′ is the modified second similarity, Ab is the standard similarity, Tb is the standard response time, and Ts is the real-time response time.

[0118] The actual execution time of the fuzzy SQL query statement is analyzed to determine the accuracy of the generated table name to be detected and the projection field to be detected. If the real-time response time is less than or equal to the standard response time, it means that the query result meets expectations, indicating that the generated table name to be detected and the projection field to be detected are highly accurate. The table name to be detected and the projection field to be detected are stored in the database as new data. By adaptively reducing the standard similarity, unnecessary strict judgments are reduced, time and computing resources are saved, the entire detection process is made more flexible and efficient, and the accuracy of the query results is verified to improve data quality.

[0119] Specifically, generating a SQL query statement according to the column type includes:

[0120] The column type is identified and marked, and a query method is determined according to the marking result, wherein the query method includes grouping query, sorting query and table joining query.

[0121] The sql data source types supported in this embodiment are: Oracle, Mysql, Click House, Postgresql, sql Server, Hive. The parsing method is encapsulated into a jar. When using it, you only need to reference the jar. It contains detailed method comments on the core method of parsing sql, which is easy to use and extensible.

[0122] So far, the technical solutions of the present invention have been described in conjunction with the preferred embodiments shown in the accompanying drawings. However, it is easy for those skilled in the art to understand that the protection scope of the present invention is obviously not limited to these specific embodiments. Without departing from the principle of the present invention, those skilled in the art can make equivalent changes or substitutions to the relevant technical features, and the technical solutions after these changes or substitutions will fall within the protection scope of the present invention.

[0123] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for parsing sql elements that can execute sql statements, characterized in that: include, Convert the log data input by the user into several initial SQL statements, decompose any initial SQL statement and obtain several tokens; Among them, through multi-stage similarity calculation and automatic extraction of the most matching table name and field key information in the SQL statement, and recording the data flow and data table reference information; Analyzing whether the order and structural arrangement of the word units are correct according to predefined grammar rules, so as to connect the word units according to the operator priority and grouping symbols in the initial SQL statement to generate a parse tree; Accessing a metadata view in a database to extract a table structure set, performing semantic analysis on the parse tree according to the table structure set, identifying a table name and a projection field according to the semantic analysis result, and constructing a SQL query statement according to the identified table name and projection field, wherein the SQL query statement is a target SQL query statement or a fuzzy SQL query statement; A query step list is generated according to the SQL query statement to instruct the database to execute the initial SQL query statement and output a query result, and whether to adjust the standard similarity is determined according to the query result.

2. The SQL element parsing method for executable SQL statements according to claim 1, characterized in that: The table structure set includes a plurality of table name sets and a plurality of column name sets, and semantic analysis of the parse tree according to the table structure set includes: Extracting keywords from the parse tree, and matching the keywords from the parse tree with words in each of the table name sets or words in the column name set; The matching similarity, set similarity and association similarity are calculated based on the matching results.

3. The SQL element parsing method for executable SQL statements according to claim 2, characterized in that: The matching similarity includes column name matching similarity and table name matching similarity. Calculating the matching similarity includes: Marking words matching the keyword as matching fields; Acquire each of the table name sets with matching field tags as a candidate table name set, or acquire each of the column name sets with matching field tags as a candidate column name set; The percentage of the number of matching fields in each candidate table name set to the total number of words in the candidate table name set is calculated respectively to obtain the corresponding table name matching similarity, and the percentage of the number of matching fields in each candidate column name set to the total number of words in the candidate column name set is calculated respectively to obtain the corresponding column name matching similarity.

4. The SQL element parsing method for executable SQL statements according to claim 2, characterized in that: The set similarity includes table name set similarity and column name set similarity. Calculating the set similarity includes: When there is no table name matching similarity greater than or equal to the standard similarity, or there is no column name matching similarity greater than or equal to the standard similarity, constructing a keyword set according to the keywords of the parse tree; The table name set similarity between the keyword set and the candidate table name set is calculated, or the column name set similarity between the keyword set and the candidate column name set is calculated.

5. The SQL element parsing method for executable SQL statements according to claim 2, characterized in that: The association similarity includes table name association similarity and column name association similarity. Calculating the association similarity includes: When there is no table name set with a similarity greater than or equal to the standard similarity, or when there is no column name set with a similarity greater than or equal to the standard similarity, obtaining an associated entity of the keyword in the database, and matching the associated entity with a word in a candidate table name set or with a word in a candidate column name set; Calculate the percentage of the sum of the number of corrected table names and the number of matching fields to the total number of words in the candidate table name set to obtain the table name association similarity; Or the percentage of the sum of the number of modified table names and the number of matching fields in the total number of words in the candidate column name set is calculated to obtain the column name association similarity.

6. The method for parsing sql elements of an executable sql statement according to claim 1, characterized in that: Identifying the table name and projection field according to the semantic analysis result includes: The table name, projection field, table name to be detected and projection field to be detected of the SQL query statement are selected according to matching similarity, set similarity or association similarity.

7. The method for parsing SQL elements of an executable SQL statement according to claim 6, characterized in that: The target SQL query statement is constructed according to the table name and the projection field, and a real-time response duration of executing the target SQL query statement is obtained. When the real-time response duration is greater than the standard response duration, the standard similarity is adjusted to a modified first similarity.

8. The SQL element parsing method for executable SQL statements according to claim 6, characterized in that: Construct a fuzzy SQL query statement according to the table name to be detected and the projection field to be detected; Obtaining the real-time response time of executing the fuzzy SQL query statement; When the real-time response time is less than or equal to the standard response time, it is determined that the query result meets expectations, the standard similarity is adjusted to a modified second similarity, and the table name to be detected and the projection field to be detected are stored in the database.

9. The SQL element parsing method for executable SQL statements according to claim 6, characterized in that: The real-time response time of executing the fuzzy SQL query statement is obtained. When the real-time response time is longer than the standard response time, it is preliminarily determined that the query result does not meet expectations and an error prompt is output.

10. The method for parsing SQL elements of an executable SQL statement according to claim 1, characterized in that: Converting the user input log data into the initial SQL statement includes: Preprocessing the log data to generate input text; The input text is divided into a number of initial SQL statements according to the positions of question marks.

Images