SQL statement normalization methods, devices, electronic equipment, and computer storage media

Abstract

This invention relates to data processing and database technology, and provides a method, apparatus, electronic device, and computer storage medium for SQL statement normalization. The method includes: acquiring an SQL statement to be processed, the SQL statement including SQL clauses divided according to preset keywords; extracting logical units from the SQL clauses according to the syntactic structure of the preset keywords, the logical unit being the smallest string with independent semantics; standardizing the logical units to obtain standard strings; and replacing the logical units in the SQL statement with the standard strings to obtain a normalized SQL statement. This invention can generate uniquely normalized SQL statements, improving deduplication accuracy.

Description

Technical Field

[0001] This invention relates to the fields of data processing and database technology, and more specifically, to a method, apparatus, electronic device, and computer storage medium for SQL statement normalization. Background Technology

[0002] In modern information systems, especially in scenarios such as data warehouses, BI (Business Intelligence) platforms, and data analysis platforms, programs or users generate massive amounts of SQL (Structured Query Language) query requests. When managing, optimizing, or analyzing these massive amounts of SQL queries (e.g., identifying query hotspots, implementing query result caching, and detecting duplicate queries), deduplication and classification are the primary steps.

[0003] Current text normalization deduplication methods mostly remain at the string processing level. They perform simple string operations, such as removing extra spaces at the beginning, end, and middle of SQL statements, converting all characters to uniform uppercase or lowercase, and deleting comments. Then, they directly calculate the similarity of the text-normalized SQL statements and perform deduplication based on the similarity calculation results. Although this method is simple to implement and has high performance, its "strong text sensitivity and weak semantic awareness" characteristics result in low deduplication accuracy, which cannot meet the needs of refined operation and optimization. Summary of the Invention

[0004] The purpose of this invention is to provide a method, apparatus, electronic device, and computer storage medium for SQL statement normalization.

[0005] The embodiments of the present invention can be implemented as follows: In a first aspect, the present invention provides a method for SQL statement normalization, the method comprising: Obtain the SQL statement to be processed, the SQL statement including SQL clauses divided according to preset keywords; Based on the grammatical structure of the preset keywords, logical units are extracted from the SQL clauses, where each logical unit is a minimal string with independent semantics. The logical unit is standardized to obtain a standard string; The logical units in the SQL statement are replaced with the standard string to obtain a normalized SQL statement.

[0006] In an optional implementation, the step of extracting the logical units in the SQL clause based on the syntactic structure of the preset keywords includes: Obtain the abstract syntax tree of the SQL statement; Based on the grammatical structure of the preset keywords, the query subtree of the SQL clause is obtained from the abstract syntax tree; Traverse the query subtree and extract the logical units in the SQL clause.

[0007] In an optional implementation, the query subtree includes at least one level, each level corresponds to a syntax logic level, each level includes at least one node, and the string represented by each node belongs to a syntax unit. The step of traversing the query subtree and extracting the logical units in the SQL clause includes: The node at the highest level of the query subtree is taken as the current node; Based on the node type of the current node and the string represented by the current node, extract logical units and / or obtain the next node to be traversed; If a node to be traversed exists, then the node to be traversed is taken as the new current node, and the steps of extracting logical units and / or obtaining the next node to be traversed based on the node type and the string represented by the current node are repeated until all nodes at all levels of the query subtree have been traversed, and all logical units in the SQL clause are obtained.

[0008] In an optional implementation, the step of extracting logical units and / or obtaining the next node to be traversed based on the node type of the current node and the string representing the current node includes: If the current node is a leaf node, then the string represented by the current node is treated as a logical unit, and the next node to be traversed is obtained according to the parent node of the current node. If the current node is not a leaf node and the string represented by the current node is a bracket expression, then the child node of the current node will be the next node to be traversed. If the current node is not a leaf node and the string represented by the current node is a binary logical expression, then extract the logical unit and / or obtain the next node to be traversed based on the node at the level of the current node.

[0009] In an optional implementation, the step of standardizing the logic unit to obtain a standard string includes: If the logical unit is a function alias, a standard function name is generated according to the preset text format, the function identifier and parameters, and the standard function name is used as the standard string; otherwise, the logical unit is converted into the standard string of the preset text format.

[0010] In an optional implementation, the SQL clause contains multiple logical units, and the step of replacing the logical units in the SQL statement with the standard string to obtain a normalized SQL statement includes: Sort the standard strings of all logical units; Replace the logical units in the SQL statement according to the order of the sorted standard strings; The syntax tree of the SQL statement is used to perform a syntax check on the replaced SQL statement; The replaced SQL statement that passes the syntax check is used as the normalized SQL statement.

[0011] In an optional implementation, after the step of replacing the logical units in the SQL statement with the standard string to obtain a normalized SQL statement, the method includes: Obtain preset replacement rules, which include rule expressions and corresponding preset placeholders representing predefined services; The strings in the normalized SQL statement that match the regular expression are replaced with the preset placeholders to obtain the SQL template of the SQL statement, so as to perform deduplication judgment on the SQL statement according to the SQL template.

[0012] Secondly, the present invention provides an SQL statement normalization apparatus, the apparatus comprising: The acquisition module is used to acquire the SQL statement to be processed, the SQL statement including SQL clauses divided according to preset keywords; The extraction module is used to extract logical units from the SQL clause according to the grammatical structure of the preset keywords. The logical unit is the smallest string with independent semantics. The standardization module is used to standardize the logical units to obtain standard strings; The replacement module is used to replace the logical units in the SQL statement with the standard string to obtain a normalized SQL statement.

[0013] Thirdly, the present invention provides an electronic device including a processor and a memory, the memory being used to store a program, and the processor being used to implement the method as described in any of the foregoing embodiments when executing the program.

[0014] Fourthly, the present invention provides a computer storage medium having a computer program stored thereon, which, when executed by a processor, implements the method as described in any of the foregoing embodiments.

[0015] Compared with the prior art, the present invention has the following beneficial effects: This invention extracts the smallest semantically independent logical units from the SQL clauses of a SQL statement based on the syntactic structure of preset keywords. These logical units are then standardized to obtain standard strings. Finally, the standard strings replace the logical units in the SQL statement, resulting in a normalized SQL statement. Because this invention breaks down SQL statements into SQL clauses, standardizes the semantically independent logical units within the clauses, and then replaces the standard strings with the normalized strings, it ensures that logically equivalent but differently written SQL statements can generate unique, deterministic normalized SQL statements. This further improves the accuracy of deduplication when performing deduplication based on the SQL statement. Attached Figure Description

[0016] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of the present invention and should not be regarded as a limitation on the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0017] Figure 1 Example of the SQL statement normalization method provided in this embodiment Figure 1 .

[0018] Figure 2 Example of the SQL statement normalization method provided in this embodiment Figure 2 .

[0019] Figure 3 This is an example of a standardized processing flow for SQL statements provided in this embodiment.

[0020] Figure 4 This is an example diagram illustrating the SQL statement template processing flow provided in this embodiment.

[0021] Figure 5 This is a block diagram illustrating the SQL statement normalization device provided in this embodiment.

[0022] Figure 6 This is a block diagram of the electronic device provided in this embodiment.

[0023] Icons: 10-Electronic device; 11-Processor; 12-Memory; 13-Bus; 100-SQL statement normalization device; 110-Acquisition module; 120-Extraction module; 130-Standardization module; 140-Replacement module. Detailed Implementation

[0024] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer, the technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. The components of the embodiments of the present invention described and shown in the accompanying drawings can generally be arranged and designed in various different configurations.

[0025] Therefore, the following detailed description of the embodiments of the invention provided in the accompanying drawings is not intended to limit the scope of the claimed invention, but merely to illustrate selected embodiments of the invention. All other embodiments obtained by those skilled in the art based on the embodiments of the invention without inventive effort are within the scope of protection of the invention.

[0026] It should be noted that similar labels and letters in the following figures indicate similar items. Therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures.

[0027] In the description of this invention, it should be noted that if terms such as "upper," "lower," "inner," or "outer" are used to indicate the orientation or positional relationship based on the orientation or positional relationship shown in the accompanying drawings, or the orientation or positional relationship in which the product of this invention is usually placed, they are only for the convenience of describing this invention and simplifying the description, and do not indicate or imply that the device or element referred to must have a specific orientation, or be constructed and operated in a specific orientation, and therefore should not be construed as a limitation of this invention.

[0028] Furthermore, the terms "first" and "second" are used only to distinguish descriptions and should not be interpreted as indicating or implying relative importance.

[0029] It should be noted that, where there is no conflict, the features in the embodiments of the present invention can be combined with each other.

[0030] Existing text normalization deduplication methods typically have the following shortcomings: 1) Structural order sensitivity: SQL syntax allows logically equivalent elements to be written in different orders within multiple clauses. Traditional methods cannot handle this type of situation. For example: The expressions “SELECT column_a, column_b FROM table_x” and “SELECT column_b, column_a FROM table_x” are almost identical in meaning, but the strings are different.

[0031] "WHERE condition1 AND condition2" and "WHERE condition2 AND condition1" are logically equivalent, but the strings are different.

[0032] "GROUP BY field1, field2" and "GROUP BY field2, field1" are logically equivalent, but the strings are different.

[0033] 2) Sensitivity to specific values: Conventional methods treat specific values (constants, literals) in queries as part of the SQL, resulting in SQLs with the same query template being misidentified as different. For example, the following two SQLs have the same intention (querying population data of a certain type in a certain province), but are judged as two independent SQLs due to different province names: WHERE prov_name = 'Beijing' AND popu_type = 4 WHERE prov_name = 'Hebei' AND popu_type = 4 3) Sensitivity to format and aliases: Differences in the writing style of SQL, such as the case of keywords and different function aliases, can also lead to failure in deduplication.

[0034] "SELECT prov_name,city_name FROM wcl_sspop_crm_score_month …" "select prov_name,city_name from wcl_sspop_crm_score_month …" "SELECT SUM(value) as total FROM ..." "SELECT SUM(value) as sales_total FROM ..." These typical defects together result in a very low deduplication rate, with a large number of logically repeated queries being missed, greatly reducing the value of subsequent cache hit rate, performance optimization, behavior analysis, etc.

[0035] In view of this, this embodiment provides a method, device, electronic device and computer storage medium for normalizing SQL statements, aiming to accurately map SQL statements with equivalent group logic or homologous structures to the same unique "normal form" or "fingerprint", regardless of how their writing order, format style, aliases, and specific query values change, so as to achieve high-precision and high-recall SQL deduplication and classification. The following will describe it in detail.

[0036] Please refer to Figure 1 , Figure 1 Example of the SQL statement normalization method provided in this embodiment Figure 1 The method includes the following steps: Step S101: Obtain the SQL statement to be processed. The SQL statement includes SQL clauses divided according to preset keywords.

[0037] In this embodiment, the preset keywords refer to core syntax terms in the SQL language, such as SELECT to specify the fields to be queried, FROM to indicate the table name from which the data comes, WHERE followed by filter conditions, and GROUP BY to indicate the grouping criteria, etc. Each preset keyword is associated with an independent logical function in the SQL statement, namely an SQL clause.

[0038] In this embodiment, since the original SQL statements obtained from different platforms are in different forms, simple text normalization can be performed to simplify subsequent processing. As an implementation method, extra spaces at both ends and in the middle of the SQL statement can be removed, all characters can be converted to lowercase, comments can be deleted, and preset keywords (SELECT, FROM, WHERE, AND, OR, LIKE, ORDER BY, GROUP BY, HAVING, LIMIT, BETWEEN, SUBSTRING, AS, CONCAT, etc.) can be uniformly capitalized.

[0039] Step S102: Extract logical units from the SQL clauses based on the grammatical structure of the preset keywords. Each logical unit is the smallest string with independent semantics.

[0040] In this embodiment, the logic unit includes, but is not limited to, comparison expressions, such as a=1, b>20; IN expressions, such as c IN ('X', 'Y'); LIKE expressions, such as d LIKE '%test%'; BETWEEN expressions; function calls, such as ISNULL(e); and arguments or column names.

[0041] Step S103: Standardize the logic unit to obtain a standard string.

[0042] In this embodiment, corresponding standardization strategies are adopted for different types of logical units. For example, for logical units involving function aliases, such as "SUM(popu_value) AS total_population", the system can automatically generate a unified standard alias according to preset rules, such as renaming it according to the format "sum_popu_value", thereby avoiding formal differences caused by developers using different aliases (such as total, sum_val, result, etc.). For other non-alias-type logical units, they will be uniformly converted to a standardized format, such as converting all to lowercase letters, removing redundant whitespace characters, and standardizing the spaces on both sides of operators. Thus, logical units with the same meaning but different forms will become completely consistent standard strings.

[0043] Step S104: Replace the logical units in the SQL statement with standard strings to obtain a normalized SQL statement.

[0044] In this embodiment, when there is only one logical unit, it can be directly replaced. When the same logical unit appears multiple times in the SQL statement, all of them can be replaced. When there are multiple logical units, they can be sorted according to a preset rule before replacement. The preset rule can be lexicographical order. For example, SQL statement 1 is "SELECT a, b" and SQL statement 2 is "SELECT b, a". After sorting and concatenation, they will both become "SELECT a, b". This ensures that no matter how the order of fields or conditions in the original SQL statement changes, the final result will be consistent.

[0045] The method provided in this embodiment breaks down SQL statements into SQL clauses, standardizes the independent semantic logical units in the SQL clauses, and then replaces the standardized strings in the SQL statements with the standardized strings. This ensures that logically equivalent but differently written SQL statements can generate unique, deterministic, and normalized SQL statements. As a result, the accuracy of deduplication can be improved when deduplicating SQL statements.

[0046] In an optional implementation, to reduce the processing complexity caused by syntax structure analysis, this embodiment provides an implementation method for extracting logical units from SQL clauses using an abstract syntax tree: First, obtain the abstract syntax tree of the SQL statement; In this embodiment, the Abstract Syntax Tree (AST) is a tree-like representation of the source code's syntactic structure. As one implementation, the Java net.sf.jsqlparser library can be used to convert SQL statements into a structured Java object, i.e., an AST, which precisely expresses the syntactic structure of the SQL statement. For example, a SELECT clause node in an SQL statement will contain child nodes such as selectItems, fromItem, and where. The AST includes a root node and at least one level below the root node. Each level corresponds to a syntactic logic level, and each level includes at least one node. The string represented by each node belongs to a syntactic unit.

[0047] Secondly, based on the grammatical structure of the preset keywords, the query subtree of the SQL clause is obtained from the abstract syntax tree; In this embodiment, based on the grammatical structure represented by the preset keyword, the corresponding subtree structure, i.e., the query subtree, is located in the abstract syntax tree. For example, when processing the filtering conditions in the WHERE clause, the system will find the subtree guided by the WHERE keyword in the abstract syntax tree. This subtree contains all node information related to the filtering conditions, such as field comparisons, IN lists, and compound conditions connected by logical operators. In other words, the query subtree refers to the part of the tree structure separated from the entire abstract syntax tree that corresponds to a specific SQL clause (such as WHERE or SELECT) related to a certain preset keyword.

[0048] It should be noted that since the query subtree is part of the abstract syntax tree, the query subtree also includes an initial node (equivalent to the root node of the abstract syntax tree) and at least one level below the initial node. Each level corresponds to a syntactic logic level, and each level includes at least one node. The string represented by each node belongs to a syntactic unit.

[0049] Finally, traverse the query subtree to extract the logical units in the SQL clause.

[0050] In this embodiment, the traversal method can be based on a depth-first or breadth-first traversal strategy. By traversing each node in the query subtree, each level can be expanded and processed according to the syntactic logic hierarchy, and finally all logical units included in the SQL clause can be obtained.

[0051] In an optional implementation, one way to extract logical units from SQL clauses by traversing the query subtree is as follows: First, the node at the highest level of the query subtree is taken as the current node; In this embodiment, the highest-level node is usually the starting node equivalent to the root node of the abstract syntax tree in the query subtree. The children of the highest-level node are located in the next level below the highest level. For any node in either the abstract syntax tree or the query subtree, if there is a parent node, the parent node is located in the level above the level where the node is located. If there are child nodes, the child nodes are located in the level below the level where the node is located. Other nodes at the same level as this node are called the sibling nodes of this node.

[0052] Secondly, according to the node type of the current node and the string represented by the current node, extract logical units and / or obtain the next node to be traversed. In this embodiment, the node types include leaf nodes and non-leaf nodes. If the current node is a leaf node, that is, it has no child nodes and the string it represents is an indivisible comparison expression, for example, "city_name = 'Beijing' " or "date_dt>202401", then it is regarded as a complete logical unit. If it is a non-leaf node, the processing strategy needs to be determined according to the different strings represented by the current node.

[0053] Finally, if there is a node to be traversed, the node to be traversed is used as the new current node, and the steps of extracting logical units and / or obtaining the next node to be traversed according to the node type of the current node and the string represented by the current node are repeated until all nodes at all levels of the query subtree are traversed, and all logical units in the SQL clause are obtained.

[0054] In this embodiment, if there is a node to be traversed, it means that the traversal of the query subtree has not been completed, and there are nodes in the query subtree that have not been traversed. Therefore, it is necessary to continue traversing. Otherwise, it means that the traversal of the query subtree is completed.

[0055] In an alternative embodiment, in order to improve the traversal efficiency, this embodiment performs targeted processing for strings representing different types. One implementation is as follows: If the current node is a leaf node, the string represented by the current node is used as a logical unit, and the next node to be traversed is obtained according to the parent node of the current node. In this embodiment, since the leaf node has no child nodes, the traversal continues to backtrack to the parent node of the current node to avoid missing the case where the current node has sibling nodes.

[0056] If the current node is a non-leaf node and the string represented by the current node is a parenthesis expression, the child nodes of the current node are used as the next node to be traversed. In this embodiment, a bracketed expression refers to a logical condition enclosed in parentheses, such as "(prov_name = 'Hebei' AND city_name = 'Shijiazhuang')". Syntactically, it participates in higher-level logical operations as a whole. Since the content within the brackets may contain deeper structures, it is necessary to delve deeper into its next level of child nodes.

[0057] If the current node is not a leaf node and the string represented by the current node is a binary logical expression, then extract the logical unit and / or obtain the next node to be traversed based on the node at the current node's level.

[0058] In this embodiment, a binary logical expression refers to a conditional combination formed by connecting two operands with AND or OR, such as "A AND B" or "X OR Y". For this case, a recursive flattening strategy is adopted: first, the operator type (AND or OR) of the current expression is identified; then, all child nodes under that expression are collected uniformly. Specifically, if a branch is still a chain structure connected by the same operator (such as "A AND B AND C"), it is broken down into three independent items; if a branch is itself a composite structure at different logical levels (such as an OR expression within parentheses), it is standardized as a whole before being sorted. Finally, these internally processed sub-expressions are recombined to ensure that logically equivalent different expressions can generate consistent results. Thus, both basic units can be identified, and complex logical hierarchical relationships can be maintained.

[0059] One specific implementation method is as follows: Iterate through each target node in the hierarchy of the current node. For each target node encountered, if it meets a preset condition, then if the target node has a child node of a leaf node, treat the string represented by the leaf node of the target node as a logical unit. The preset condition is that the string represented by the target node is a binary logical expression and the logical operator of the target node is the same as the logical operator of the current node. Iterate through all the child nodes of the target node and all nodes in all levels below the child node until each logical unit related to each child node of the target node is obtained. Treat the parent node of the parent node of the current node as the node to be traversed.

[0060] In this embodiment, the target node is the node at the same level as the current node, including the current node and its sibling nodes. Since every target node and all nodes in all levels below it have been traversed, to avoid missing other nodes in the query subtree, the parent node of the current node's parent node is taken as the node to be traversed.

[0061] In an optional embodiment, in order to uniform the text differences caused by developers arbitrarily naming aliases when using the same function, this embodiment also provides an implementation method for standardizing a logical unit such as a function alias: If the logical unit is a function alias, generate a standard function name according to a preset text format, the identifier of the function, and the parameters, and use the standard function name as the standard string; otherwise, convert the logical unit into a standard string in the preset text format.

[0062] In this embodiment, by adopting targeted processing strategies for function aliases and other logical units respectively, accurate normalization of various expressions in the SQL statement is achieved, effectively improving the accuracy and robustness of SQL normalization. No matter how developers name aliases or adjust the format, they can ultimately be correctly grouped into a unified standard form.

[0063] In an optional embodiment, when there are multiple logical units in the SQL clause, in order to avoid text differences caused by different arrangement orders of logical units in the SQL statement, and at the same time to avoid syntax errors in the replaced SQL statement caused by replacement errors, this embodiment provides an implementation method for replacing the logical units in the SQL statement with standard strings to obtain a normalized SQL statement: First, sort the standard strings of all logical units; In this embodiment, after obtaining the standard string of each logical unit, these standard strings can be put into a list and arranged according to a preset sorting rule. A commonly used sorting method is the lexicographical order, that is, arranged in alphabetical order like looking up a dictionary. For example, "city_name = 'Beijing'" is arranged before "prov_name = 'Hebei'" because 'c' is before 'p'.

[0064] It should be noted that when there are multiple logical units, the multiple logical units may be located in different layers of the abstract syntax tree. During sorting, the logical units in the same layer need to be sorted uniformly.

[0065] Second, replace the logical units in the SQL statement in the order of the sorted standard strings; In this embodiment, the order of the original logical units can be replaced one by one with the sorted new sequence based on the structural position of each node in the Abstract Syntax Tree (AST). For example, if the WHERE condition of the original SQL statement is "date_dt>202401 AND city_name = 'Beijing'", and the normalized sorted order should be "city_name = 'Beijing' AND date_dt>202401", then the order of these two condition nodes will be adjusted, and the corresponding SQL string will be regenerated.

[0066] Third, use the abstract syntax tree of the SQL statement to perform syntax checks on the replaced SQL statement; Finally, the replaced SQL statement that passes the syntax check is used as the normalized SQL statement.

[0067] In an optional implementation, to more accurately capture the essential query intent of the SQL statement, this embodiment also provides an implementation method for obtaining an SQL template based on a normalized SQL statement: First, obtain the preset replacement rules, which include rule expressions and corresponding preset placeholders representing predefined services; In this embodiment, the preset replacement rules can be a pre-configured set of rule matching-replacement pairs, typically existing in the form of a two-dimensional array or a rule list. Each rule contains three parts: first, a rule expression, which uses regular expression syntax to describe the SQL fragment pattern to be matched, such as "city_name\\s*=\\s*'[^']*'" used to identify conditions where the city name is equal to any city; second, a corresponding preset placeholder, such as "{address_city}", which is a standard marker with a clear business meaning; and third, the business semantic label represented by the placeholder. These rules cover common variable field types, such as province, city, date, and numerical range.

[0068] Secondly, the strings that match the regular expression in the normalized SQL statement are replaced with preset placeholders to obtain the SQL template of the SQL statement, so as to perform deduplication judgment on the SQL statement based on the SQL template.

[0069] In this embodiment, by traversing the preset replacement rules in sequence and performing a one-by-one matching operation on the currently processed normalized SQL statement. Once it is found that a certain segment of text conforms to the pattern of a certain rule expression, it is immediately replaced with the corresponding preset placeholder. For example, if a certain SQL contains "city_name = 'Beijing City'", by matching the first city name rule, this segment is identified and replaced with "ssng_city_code IN ({address_city})"; similarly, "date_dt>202407" will be replaced with "date_dt>{date_dt}". This process is carried out in a chain, and the replacement result of the previous rule may be used as the input for the next rule to continue participating in the matching until all applicable rules are processed. The above replacement is also called slot replacement, that is, the process of replacing the specific query value of the mutable field in the SQL statement with a predefined placeholder (i.e., "slot"), aiming to eliminate the text differences caused by different specific query values. For example, if the mutable field is a geographical name field, the current query value is 'Beijing City', and it is replaced with the placeholder ${address_city}. When the current query value is updated to 'Shanghai City', it is still replaced with the placeholder ${address_city}, that is, although the query values are different, the replaced placeholders are the same. Thus, the text differences caused by different specific query values are eliminated. Different mutable fields can be located at different placeholders for distinction. For example, if the mutable field is a date field, its current query value is 202406, and it is replaced with the placeholder ${date_dt}, and if the mutable field is a numerical field, its current query value is 50000, and it is replaced with the placeholder ${popu_value}.

[0070] This embodiment also presents a processing method for deduplicating SQL statements using SQL templates: First, obtain the SQL templates of the two SQL statements respectively; Second, calculate the characteristic values of each SQL template respectively; In this embodiment, the feature value of the SQL template, also known as the SQL fingerprint, is a hash value or unique string that uniquely identifies a class of SQL statements with the same structure and similar semantics after a series of standardization and abstraction processes. As one implementation, the feature value of the SQL template can be converted into a fixed-length integer or string unique identifier using an efficient hash algorithm, such as the hashCode method in Java or the MD5 algorithm. Since the same input will inevitably produce the same output, if two SQL templates are completely identical, their corresponding feature values ​​will definitely be the same; conversely, if there are any character-level differences between the templates, the feature values ​​will usually be different. This mechanism simplifies the complex operation of character-by-character comparison into a lightweight numerical comparison, greatly reducing computational overhead.

[0071] Finally, the semantic logic of the two SQL statements is determined based on the two feature values.

[0072] In this embodiment, if two feature values ​​are equal, it means that the two SQL templates are completely identical in structure and abstract parameter layout, indicating that they express the same query intent and belong to the same category of duplicate or highly similar queries; if they are not equal, it means that their core structures are different and they do not belong to the same category. Of course, there is also a very small probability that hash collisions may occur, that is, different templates generate the same feature values, but this situation can be avoided by secondary verification (such as exact string comparison).

[0073] In this embodiment, as an implementation method, the entire SQL statement deduplication process can be divided into three main stages: the first stage: SQL statement normalization, where the input is the original SQL statement and the output is a normalized SQL statement; the second stage: SQL statement templateization, where the input is the normalized SQL statement and the output is an SQL template; and the third stage: generating SQL fingerprints and deduplication classification.

[0074] Please refer to Figure 2 , Figure 2 Example of the SQL statement normalization method provided in this embodiment Figure 2 , Figure 2 In this process, a batch of raw SQL statements are input. For each SQL statement, it goes through the first stage and the second stage in sequence to obtain the SQL template corresponding to each raw SQL statement. Since typical keywords in SQL statements usually include SELECT, WHERE, GROUP BY, HAVING, and ORDER BY, this embodiment provides a... Figure 2 The specific implementation method of standardizing SQL statements in Chinese.

[0075] Please refer to Figure 3 , Figure 3 This is an example of the normalization process for SQL statements provided in this embodiment. Figure 3 In this process, the received raw SQL statement string is first simply formatted. The formatted SQL string is then parsed into an abstract syntax tree (Abstract Syntax Tree). If parsing is successful, the SELECT, WHERE, GROUP BY, HAVING, and ORDER BY clauses are parsed and processed sequentially from the Abstract Syntax Tree, ultimately generating a normalized SQL statement. If parsing fails, the raw SQL statement string is returned or an exception is output. These typical clauses will be introduced one by one below.

[0076] For the SELECT clause, the main processing includes the following: (1) Same alias: Traverse all query items. If a query item is a function call (e.g., SUM(popu_value) AS total_population), a deterministic alias (e.g., sum_popu_value) will be automatically generated based on the function name (SUM) and its parameter (popu_value). This solves the text discrepancies caused by developers arbitrarily naming aliases.

[0077] (2) Sequential Sort: After processing all query items, they are placed into a list and sorted lexicographically according to the string representation of each item (normalized alias or column name). Aliases are used first, and column name strings are used if no alias exists. Finally, the original query item list in the AST is replaced with the sorted list. This ensures that SELECT a, b and SELECT b, a will produce the same normalized result.

[0078] (3) Alias ​​replacement: If the alias in the SELECT part is referenced in the string part after FROM, it needs to be replaced by the newly generated unified alias in a loop to ensure that all subsequent uses are unified aliases.

[0079] (4) Parsing and verification: The new SQL after unification, sorting and replacement of aliases is parsed again by the abstract syntax tree to ensure that the new SQL syntax is correct.

[0080] The complexity of the WHERE / HAVING clause lies in: mixed logical operators (AND and OR coexist, with AND having higher default precedence than OR); altering precedence using parentheses (the order of operations can be forcibly changed using parentheses); and composition (conditions can be nested infinitely, such as "A AND (B OR (C AND D))"). The main approach is to recursively descent the abstract syntax tree (AST) of the WHERE / HAVING clause, decomposing the complex conditional expression into indivisible minimum logical units. These units are then lexicographically ordered at the same logical level (connected by the same logical operators AND / OR), and recombined to ensure that logically equivalent but differently written expressions generate unique, deterministic, standardized strings. The processing steps include: (1) Recursive datum identification: If the current expression is the smallest indivisible logical unit, it is directly converted to a normalized string and returned. These units include: comparison expressions (e.g., a = 1, b>20), IN expressions (e.g., c IN ('X', 'Y')), LIKE expressions (e.g., d LIKE '%test%'), BETWEEN expressions, function calls (e.g., ISNULL(e)), literals, or column names.

[0081] (2) Handling bracket expressions: If the current expression is of type parentheses, it means that it is a subexpression enclosed in parentheses. The specific processing method could be: Extract the subexpression inner_expr within the parentheses; The processOpSortExpression(inner_expr) is recursively called to obtain the standardized string standardized_inner_str inside it. The function of processOpSortExpression(inner_expr) is to decompose the input expression into the smallest indivisible logical units through recursion, and sort the logical units at the same logical level in lexicographical order.

[0082] The result is then wrapped in parentheses and returned as "( " + standardized_inner_str + " )". This preserves the user-specified priority structure.

[0083] (3) Processing of logical binary expressions (AND / OR): If the current expression is a binary expression connected by AND or OR (e.g., left_expr AND right_expr), the processing steps include: (1) Identify the operator at the current level: determine whether it is AND or OR.

[0084] (2) Flatten and collect operands: (2.1) Create an empty list operands_list.

[0085] (2.2) The function collectOperands(expr, current_operator, operands_list) is used to recursively examine the current expression expr and perform the following processing: (2.2.1) If the operator of expr is the same as the current_operator of the current level (for example, encountering another AND while processing an AND chain), then its left and right subexpressions are further broken up, and collectOperands is recursively called on them respectively. This achieves "flattening", breaking a=1 AND b=2 AND c=3 into three independent units: a=1, b=2, and c=3.

[0086] (2.2.2) If the operator of expr is different from that of current_operator (for example, encountering an OR expression when processing an AND chain), or if expr is an indivisible unit or a bracketed expression, then a logical boundary is considered to have been encountered. In this case, the main function processOpSortExpression(expr) is recursively called on the entire expr to normalize it as a whole and add it to operands_list.

[0087] (3) Sort the conditions at the same level: After the above processing, the current operands_list contains all the condition strings that have been internally standardized under the current logical level. The list is then sorted lexicographically.

[0088] When sorting based on criteria at the same level, because the structures are similar and have the same weight, to avoid nesting, only the first key field (the field to the left of the first equal sign) is used for sorting. For example: When popu_type = 5 is compared with date_dt = 202407, the sorting fields are popu_type and date_dt. Comparing (city_name = 'Tianjin City' AND prov_name = 'Tianjin City') with (city_name = 'Beijing City' AND prov_name = 'Beijing City'), the sorting words are city_name and city_name, and the order of city_name and prov_name within the sorting is already completed in the previous child level sorting with the same conditions.

[0089] (4) Reorganization of the expression tree: An expression tree is a subtree within the abstract syntax tree corresponding to the current expression. All strings in the sorted operands_list are concatenated using the operators ("AND" or "OR") at the current level, returning the final combined string. This process guarantees that any order of equivalent condition combinations will ultimately produce the exact same expression tree and string.

[0090] For GROUP BY / ORDER BY clauses, the processing includes sorting and parsing validation, as detailed in the sorting and parsing validation process described in the SELECT clause above.

[0091] Finally, all processed clauses are re-transformed to obtain a normalized SQL statement.

[0092] Furthermore, this embodiment also provides Figure 2 The specific implementation of SQL statement templates involves replacing the specific query values ​​in a normalized SQL statement with predefined placeholders (slots) that have business meaning, thus obtaining the SQL template. Please refer to [link / reference]. Figure 4 , Figure 4 This is an example diagram illustrating the SQL statement template processing flow provided in this embodiment. Figure 4 This includes the following processing: (1) Rule-driven replacement mechanism: First, a two-dimensional array replacementRules is defined. This array constitutes a set of rule engines. Each rule is an independent replacement unit, defining "what to find" and "what to replace".

[0093] The following is an example of a list of replacement rules: / / Replace province { "prov_name\\s*=\\s*'[^']*'", "ssng_province_code IN (\\${address_province})", "address_province"}, / / Replace city { "city_name\\s*=\\s*'[^']*\\s*'", "ssng_city_code IN (\\${address_city})", "address_city"}, / / Replace district / county { "zone_name\\s*=\\s*'[^']*'", "ssng_zone_code IN (\\${address_county})", "address_county"}, / / Replace date { "date_dt\\s*(>=|<=|>|<|=|!=)\\s*\\d{6}", "date_dt = \\${date_dt}","date_dt"}, / / Matching various comparison operators { "(?i)(popu_value|total_population)\\s*(>=|<=|>|<|=|!=)\\s*\\d+(\\.\\d+)?", "$1 $2 \\${popu_value}", "popu_value"}, / / Matches the format xxx IN (...) { "(?i)(\\w+)\\s+IN\\s*\\(\\s*\\d+(\\s*,\\s*\\d+)*\\s*\\)", "$1 IN(\\${date_dt_1}, \\${date_dt_2})", "date_dt_1, date_dt_2"}, / / Match BETWEEN x AND y format { "(?i)(\\w+)\\s+BETWEEN\\s+(\\d+)\\s+AND\\s+(\\d+)", "$1 BETWEEN \\${value_1} AND \\${value_2}", "value_1,value_2"} In the example diagram above, `replacementRules[0]` corresponds to a regular expression, which defines the pattern of the SQL condition fragment to be matched. For example, `prov_name\\s*=\\s*'[^']*'` is used to match conditions like `prov_name = 'any province'`. `replacementRules[1]` corresponds to a replacement template, which defines the new string to be replaced after a successful match. It contains slots, such as `${address_province}`. These slots are standardized placeholders that represent a type of business data. `replacementRules[2]` corresponds to a slot name, which records the slot name used in this replacement and can be used for subsequent analysis.

[0094] (2) Iterative processing: Iterate through each rule in replacementRules in sequence. For each rule, it performs a match and replacement operation once on the current (which may have been modified by the previous rule) processedSql string. This chained processing method allows for certain dependencies or sequential relationships between rules.

[0095] (3) Pattern matching and replacement: The regular expression replacementRules[0] in the rule can be compiled into a pattern object using Java syntax, and then the matched object is replaced according to the replaceAll(replacementRules[1]) rule. If there are multiple places in an SQL that match the rule, replaceAll will replace them all at once.

[0096] (4) Abstraction effect: After processing by all rules, all the variable and specific query values ​​in the original SQL are abstracted into unified slots with business meaning. For example, whether querying Beijing or Hebei, the final template is ${address_province}, thus smoothing out the text differences caused by different specific query regions.

[0097] After obtaining the SQL templates for all the original SQL statements, deduplication is performed on all SQL templates. For example, given n rows of data, each row represents a single original SQL statement. To compare one SQL statement with any other, the total number of comparisons required to complete all comparisons is equal to the total number of combinations of randomly selecting two SQL statements from the n rows. The average time complexity of the entire grouping phase is O(n²), and when n becomes very large, the number of comparisons is approximately half that of n².

[0098] For large datasets, the number of comparisons is significant. A better approach is to first calculate the characteristic values ​​(the simplest being hashCode). Data with the same characteristic value (i.e., a hash collision) is then compared string by string. This method is much more efficient, with an average time complexity of O(n), and the number of comparisons is far less than [the required number of comparisons]. .

[0099] To this end, an SQL fingerprint is generated for each SQL template. A mapping table is used to record each SQL fingerprint and its corresponding list of original SQL statements. Each entry in the mapping table corresponds to an SQL fingerprint and its corresponding list of original SQL statements. The components of each entry in the mapping table include: Key: SQL fingerprint (Int); Value: List, which stores relevant information about all the original SQL statements that generated that fingerprint (such as the SQL identifier and the original SQL string).

[0100] Specifically, for each original SQL statement, its feature value is calculated based on its SQL template and algorithms such as HashCode and MD5. This generates a unique and deterministic SQL fingerprint for the same SQL template. Then, deduplication and classification are performed. If a given SQL fingerprint does not exist in the mapping table, it is added to the mapping table, and its corresponding original SQL statement is added to the corresponding list of original SQL statements. Finally, deduplication is achieved by analyzing the mapping table. Specifically, the mapping table is traversed. If the length of the list of original SQL statements corresponding to a certain SQL fingerprint is greater than 1, it means that multiple original SQL statements have generated the same SQL fingerprint. To avoid hash collisions (different SQL templates corresponding to the same SQL fingerprint), multiple SQL templates for the same SQL fingerprint can be compared at the string level. If multiple SQL templates are completely identical, it means that the multiple original SQL statements corresponding to that SQL fingerprint are logically identical duplicate SQL statements. Otherwise, it means a hash collision has occurred. In this case, the truly duplicate SQL statements can be determined based on the string-level comparison.

[0101] Based on the above Figures 2-4 This embodiment uses a specific, non-standardized SQL statement as an example to introduce the complete process.

[0102] Original SQL statement: SELECT prov_name, city_name, SUM(popu_value) AS total_day_population From wcl_sspop_crm_score_month WHERE date_dt = 202407 and region_lv=2 and popu_type = 5 AND ((prov_name = 'Beijing' AND city_name = 'Beijing') or (prov_name = 'Tianjin' AND city_name = 'Tianjin')) group by prov_name,city_name order by total_day_population desc。

[0103] The first stage: The SQL normalization process: 1) Uniformly format the SQL: SELECT prov_name, city_name, sum(popu_value) AS total_day_population FROM wcl_sspop_crm_score_month WHERE date_dt = 202407 AND region_lv = 2 AND popu_type = 5 AND ((prov_name = 'Beijing' AND city_name = 'Beijing') OR (prov_name = 'Tianjin' AND city_name = 'Tianjin')) GROUP BY prov_name, city_name ORDER BY total_day_population DESC。

[0104] 2) Parse it into an AST.

[0105] 3) SELECT clause processing: prov_name, city_name, and SUM(popu_value) are sorted lexicographically. The alias of the SUM function is normalized to sum_popu_value according to the rules.

[0106] 4) Reconstruct the SQL according to SELECT: SELECT city_name, prov_name, SUM(popu_value) AS sum_popu_value FROM wcl_sspop_crm_score_month WHERE date_dt = 202407 AND region_lv = 2 AND popu_type = 5 AND ((prov_name = 'Beijing' AND city_name = 'Beijing') OR (prov_name = 'Tianjin' AND city_name = 'Tianjin')) GROUP BY prov_name, city_name ORDER BY sum_popu_value DESC.

[0107] 5) Hierarchical processing of the WHERE clause: The innermost prov_name = 'Beijing' and city_name = 'Beijing' are sorted lexicographically according to the field names prov_name and city_name. The next level (city_name = 'Beijing' AND prov_name = 'Beijing'), (city_name = 'Tianjin' AND prov_name = 'Tianjin') is sorted lexicographically by the first key, that is, the city_name and city_name field names on both sides of OR. The next level date_dt = 202407, region_lv = 2, popu_type = 5, ((city_name = 'Beijing' AND prov_name = 'Beijing') OR (city_name = 'Tianjin' AND prov_name = 'Tianjin')) are rearranged according to the lexicographical order of the field names date_dt, region_lv, popu_type, city_name.

[0108] Final result: ((city_name = 'Beijing' AND prov_name = 'Beijing') OR (city_name = 'Tianjin' AND prov_name = 'Tianjin')) AND date_dt = 202407 AND popu_type = 5 AND region_lv = 2.

[0109] 6) GROUP BY clause, sorted alphabetically for prov_name and city_name as city_name, prov_name.

[0110] 7) ORDER BY clause processing: total_day_population has been replaced by sum_popu_value in the SELECT clause, and it is updated synchronously here; Get the normalized SQL statement: SELECT city_name, prov_name, SUM(popu_value) AS sum_popu_value FROM wcl_sspop_crm_score_month WHERE ((city_name = 'Beijing' AND prov_name = 'Beijing') OR (city_name = 'Tianjin' AND prov_name = 'Tianjin')) AND date_dt = 202407 AND popu_type = 5 AND region_lv = 2 GROUP BY city_name, prov_name ORDER BY sum_popu_value DESC.

[0111] Second stage: The process of SQL templating: For the normalized SQL statement, perform the following processing: 1) Traversal rule, city_name = 'Beijing' matches the rule city_name\\s*=\\s*'[^']*\\s*', and this part is replaced with ssng_city_code IN (\\${address_city}).

[0112] 2) Similarly, complete the replacement of prov_name = 'Beijing' with ssng_province_code IN (\\${address_province}), date_dt = 202407 with date_dt = ${date_dt}, and other slot replacements.

[0113] 3) Other conditions (such as popu_type = 5) have no matching items in the current rule set and remain unchanged.

[0114] The resulting SQL template is: SELECT city_name, prov_name, SUM(popu_value) AS sum_popu_value FROM wcl_sspop_crm_score_month WHERE ((ssng_city_code IN (${address_city}) AND ssng_province_code IN (${address_province})) OR (ssng_city_code IN (${address_city}) AND ssng_province_code IN (${address_province}))) AND date_dt = ${date_dt} AND popu_type = 5 AND region_lv = 2GROUP BY city_name, prov_name ORDER BY sum_popu_value DESC.

[0115] Phase 3: Generating SQL fingerprints and deduplication / classification For SQL templates, perform the following processing: 1) Call the HashCode algorithm to calculate an integer value, for example: 293365043.

[0116] 2) Store the original SQL information (ID and original SQL text) into the list with key 293365043 in fingerprintMap.

[0117] Suppose another SQL is received: select city_name, prov_name, sum(popu_value) assum_popu_value from wcl_sspop_crm_score_month where ((city_name = 'Beijing' and prov_name = 'Beijing') or (city_name = 'Tianjin' and prov_name = 'Tianjin')) and date_dt = 202507 and popu_type = 5 and region_lv = 2 group by city_name,prov_name order by sum_popu_value desc. After going through the exact same process as in this embodiment, it will generate exactly the same SQL template and thus calculate the exact same fingerprint 293365043. Then, the information of this SQL will be added to the same list in the fingerprintMap. During the final analysis, it will be found that there are two elements in this list. After a secondary comparison and a character-by-character comparison of the strings, it will be found that every character of these two SQL templates is the same, thus successfully identifying these two seemingly different but logically identical SQLs as duplicate queries.

[0118] To execute the corresponding steps in the above embodiment and each possible implementation manner, an implementation manner of an SQL statement normalization device 100 will be given below. Please refer to Figure 5 , Figure 5 This is a block diagram of the SQL statement normalization device provided in this embodiment. It should be noted that for the SQL statement normalization device 100 provided by the present invention, its basic principle and the resulting technical effects are the same as those of the corresponding above embodiment. For the sake of brief description, this embodiment does not mention some points.

[0119] The SQL statement normalization device 100 includes an acquisition module 110, an extraction module 120, a normalization module 130, and a replacement module 140.

[0120] The acquisition module 110 is configured to acquire an SQL statement to be processed, where the SQL statement includes SQL clauses divided according to a preset keyword; The extraction module 120 is configured to extract logical units from the SQL clauses according to the syntax structure of the preset keyword, where the logical unit is the smallest string with independent semantics; The normalization module 130 is configured to perform normalization processing on the logical unit to obtain a standard string; The replacement module 140 is used to replace the logical units in the SQL statement with the standard string to obtain a normalized SQL statement.

[0121] In an optional implementation, the extraction module 120 is specifically used for: Obtain the abstract syntax tree of the SQL statement; Based on the grammatical structure of the preset keywords, retrieve the query subtree of the SQL clause from the abstract syntax tree; Traverse the query subtree and extract the logical units in the SQL clause.

[0122] In an optional implementation, the query subtree includes at least one level, each level corresponds to a syntax logic level, each level includes at least one node, and the string represented by each node belongs to a syntax unit. Extraction module 120 is specifically used to traverse the query subtree and extract logical units from the SQL clause. It is also specifically used for: The node at the highest level of the queried subtree is taken as the current node; Based on the node type of the current node and the string representing the current node, extract the logical unit and / or obtain the next node to be traversed; If there is a node to be traversed, then the node to be traversed is taken as the new current node, and the steps of extracting logical units and / or obtaining the next node to be traversed based on the node type and the string represented by the current node are repeated until all nodes at all levels of the query subtree have been traversed and all logical units in the SQL clause are obtained.

[0123] In an optional implementation, the extraction module 120 is specifically used to extract logical units and / or obtain the next node to be traversed based on the node type and the string represented by the current node. If the current node is a leaf node, the string represented by the current node is treated as a logical unit, and the next node to be traversed is obtained based on the parent node of the current node. If the current node is not a leaf node and the string represented by the current node is a parenthesis expression, then the child nodes of the current node will be the next nodes to be traversed. If the current node is not a leaf node and the string represented by the current node is a binary logical expression, then extract the logical unit and / or obtain the next node to be traversed based on the node at the current node's level.

[0124] In an optional implementation, the standardization module 130 is specifically used for: If the logic unit is a function alias, a standard function name is generated based on the preset text format, the function identifier, and the parameters, and the standard function name is used as the standard string; otherwise, the logic unit is converted into a standard string in the preset text format.

[0125] In an optional implementation, there are multiple logical units in the SQL clause, and the replacement module 140 is specifically used for: Sort the standard strings of all logical units; Replace logical units in the SQL statement according to the order of the sorted standard strings; The syntax tree of the SQL statement is used to perform syntax checks on the replaced SQL statement; The replaced SQL statement that passes the syntax check is used as the normalized SQL statement.

[0126] In an optional implementation, the replacement module 140 is further configured to: Obtain the preset replacement rules, which include rule expressions and corresponding preset placeholders representing predefined services; The strings that match the regular expression in the normalized SQL statement are replaced with preset placeholders to obtain the SQL template of the SQL statement, so as to perform deduplication judgment on the SQL statement based on the SQL template.

[0127] This invention also provides a block diagram of an electronic device 10, which implements the SQL statement normalization method of the aforementioned embodiments. Please refer to... Figure 6 , Figure 6 This is a block diagram of the electronic device 10 provided in this embodiment. The electronic device 10 includes a processor 11, a memory 12 and a bus 13. The processor 11 and the memory 12 are connected through the bus 13.

[0128] Processor 11 can be an integrated circuit chip with signal processing capabilities. In implementation, each step of the SQL statement normalization method described above can be completed by the integrated logic circuitry in the hardware of processor 11 or by software instructions. Processor 11 can be a general-purpose processor, including CPU (Central Processing Unit), NP (Network Processor), GPU (Graphics Processing Unit), etc.; it can also be DSP (Digital Signal Processor), ASIC (Application Specific Integrated Circuit), FPGA (Field Programmable Logic Gate Array), or other programmable logic devices, discrete gate or transistor logic devices, or discrete hardware components.

[0129] The memory 12 is used to store a program that implements the SQL statement normalization method. This program can be a software function module stored in the memory 12 in the form of software or firmware or embedded in the OS (Operating System) of the electronic device 10.

[0130] After receiving the execution instruction, the processor 11 executes the program to implement the SQL statement normalization method of the aforementioned embodiment.

[0131] This embodiment provides a computer storage medium on which a computer program is stored. When the computer program is executed by a processor, it implements the SQL statement normalization method in this embodiment.

[0132] In summary, embodiments of the present invention provide a method, apparatus, electronic device, and computer storage medium for SQL statement normalization. The method includes: acquiring an SQL statement to be processed, the SQL statement including SQL clauses divided according to preset keywords; extracting logical units from the SQL clauses according to the syntactic structure of the preset keywords, the logical unit being the smallest string with independent semantics; standardizing the logical units to obtain standard strings; and replacing the logical units in the SQL statement with the standard strings to obtain a normalized SQL statement. Compared with the prior art, this embodiment has at least the following advantages: (1) It splits the SQL statement into SQL clauses, then standardizes the logical units of independent semantics in the SQL clauses, and then replaces the standardized strings in the SQL statement with the standardized strings, thereby ensuring that logically equivalent but differently written SQL statements can generate unique, deterministic, and normalized SQL statements, thereby improving the accuracy of deduplication when deduplicating based on the SQL statement; (2) It introduces deep syntax analysis based on AST, which fundamentally eliminates the sensitivity to text order and format; (3) On the AST, for the elements in the "unordered set" such as the SELECT clause, the WHERE / HAVING condition set, and the GROUP BY / ORDER BY field set, deterministic sorting rules (such as lexicographical order) are forcibly applied to ensure that logically equivalent structures can always generate completely identical text representations; (4) Through predefined rules, it identifies and extracts volatile values ​​(such as place names, IDs, dates, and numerical values) in the SQL, and replaces them with unified placeholders (slots). This process elevates a "specific query" to a "query template", accurately capturing the essential intent of the query; (5) After the "structural determinism" and "intent abstraction" processes are processed by the above in-depth analysis, the normalized SQL template string is generated by applying a hash algorithm to generate its unique fingerprint, which serves as the final basis for its classification.

[0133] The above descriptions are merely various embodiments of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.

Claims

1. A method for normalizing SQL statements, characterized in that, The method includes: Obtain the SQL statement to be processed, the SQL statement including SQL clauses divided according to preset keywords; Based on the grammatical structure of the preset keywords, logical units are extracted from the SQL clauses, where each logical unit is a minimal string with independent semantics. The logical unit is standardized to obtain a standard string; The logical units in the SQL statement are replaced with the standard string to obtain a normalized SQL statement.

2. The method according to claim 1, characterized in that, The step of extracting logical units from the SQL clause based on the syntactic structure of the preset keywords includes: Obtain the abstract syntax tree of the SQL statement; Based on the grammatical structure of the preset keywords, the query subtree of the SQL clause is obtained from the abstract syntax tree; Traverse the query subtree and extract the logical units in the SQL clause.

3. The method according to claim 2, characterized in that, The query subtree includes at least one level, each level corresponds to a syntax logic level, each level includes at least one node, and the string represented by each node belongs to a syntax unit. The step of traversing the query subtree and extracting the logical units in the SQL clause includes: The node at the highest level of the query subtree is taken as the current node; Based on the node type of the current node and the string represented by the current node, extract logical units and / or obtain the next node to be traversed; If a node to be traversed exists, then the node to be traversed is taken as the new current node, and the steps of extracting logical units and / or obtaining the next node to be traversed based on the node type and the string represented by the current node are repeated until all nodes at all levels of the query subtree have been traversed, and all logical units in the SQL clause are obtained.

4. The method according to claim 3, characterized in that, The steps of extracting logical units and / or obtaining the next node to be traversed based on the node type of the current node and the string represented by the current node include: If the current node is a leaf node, then the string represented by the current node is treated as a logical unit, and the next node to be traversed is obtained according to the parent node of the current node. If the current node is not a leaf node and the string represented by the current node is a bracket expression, then the child node of the current node will be the next node to be traversed. If the current node is not a leaf node and the string represented by the current node is a binary logical expression, then extract the logical unit and / or obtain the next node to be traversed based on the node at the level of the current node.

5. The method according to claim 1, characterized in that, The step of standardizing the logical unit to obtain a standard string includes: If the logical unit is a function alias, a standard function name is generated according to the preset text format, the function identifier and parameters, and the standard function name is used as the standard string; otherwise, the logical unit is converted into the standard string of the preset text format.

6. The method according to claim 1, characterized in that, The SQL clause contains multiple logical units. The step of replacing the logical units in the SQL statement with the standard string to obtain a normalized SQL statement includes: Sort the standard strings of all logical units; Replace the logical units in the SQL statement according to the order of the sorted standard strings; The syntax tree of the SQL statement is used to perform a syntax check on the replaced SQL statement; The replaced SQL statement that passes the syntax check is used as the normalized SQL statement.

7. The method according to claim 1, characterized in that, After the step of replacing the logical units in the SQL statement with the standard string to obtain the normalized SQL statement, the following steps are included: Obtain preset replacement rules, which include rule expressions and corresponding preset placeholders representing predefined services; The strings in the normalized SQL statement that match the regular expression are replaced with the preset placeholders to obtain the SQL template of the SQL statement, so as to perform deduplication judgment on the SQL statement according to the SQL template.

8. A SQL statement normalization device, characterized in that, The device includes: The acquisition module is used to acquire the SQL statement to be processed, the SQL statement including SQL clauses divided according to preset keywords; The extraction module is used to extract logical units from the SQL clause according to the grammatical structure of the preset keywords. The logical unit is the smallest string with independent semantics. The standardization module is used to standardize the logical units to obtain standard strings; The replacement module is used to replace the logical units in the SQL statement with the standard string to obtain a normalized SQL statement.

9. An electronic device, characterized in that, It includes a processor and a memory, the memory being used to store a program, and the processor being used to implement the method as described in any one of claims 1-7 when executing the program.

10. A computer storage medium, characterized in that, It stores a computer program that, when executed by a processor, implements the method as described in any one of claims 1-7.

Images