SQL (Structured Query Language) statement compliance checking method and device, electronic equipment and storage medium

Abstract

The invention relates to an SQL statement compliance checking method and device, electronic equipment and a storage medium, and the method comprises the steps that a task request is acquired, and the task request comprises a to-be-detected SQL statement and an SQL type of the SQL statement; a grammar parser corresponding to the SQL type is called through a multi-modal parsing engine, grammar parsing is conducted on the SQL statement through the grammar parser, a grammar tree is generated, and the multi-modal parsing engine can dynamically call the grammar parser corresponding to any SQL type; converting the syntax tree into a Query Info object through a syntax extractor in the multi-modal analysis engine, wherein the syntax extractor is used for uniformly converting the syntax trees of different structures into the Query Info object representing SQL statement metadata features; and adopting a plurality of preset standard strategies to detect the Query Info object so as to identify and intercept an irregular SQL statement. According to the method and the device, the SQL statement compliance check efficiency is improved.

Description

Technical Field

[0001] The present application relates to the field of big data technology, and in particular to a method, device, electronic device, and storage medium for checking the compliance of SQL statements. Background Art

[0002] In the field of big data and database technology, SQL (Structured Query Language) is widely used as a core data manipulation language for data query, processing, and management. To ensure data security and operational compliance, SQL statements must be tested for compliance. However, due to the existence of multiple SQL types and dialects, such as HiveSQL, SparkSQL, and PrestoSQL, the testing process faces serious compatibility issues.

[0003] Existing technologies, such as SQLparse, struggle to support syntax features specific to the big data field, such as Hive's lateralview and Presto's unnest. Customized parsing can be achieved through the ANTLR self-developed parser, but this requires continuous tracking of syntax upgrades for versions like Spark 3.0, posing version compatibility risks and high long-term maintenance costs. These issues make it difficult for existing detection solutions to efficiently identify non-compliance in multiple types of SQL statements. Summary of the Invention

[0004] The present application provides a method, device, electronic device and storage medium for checking SQL statement compliance to solve the problem of difficulty in efficiently identifying non-compliant SQL statements of multiple types.

[0005] This application provides a method for checking the compliance of SQL statements, the method comprising:

[0006] Obtaining a task request, wherein the task request includes an SQL statement to be detected and an SQL type of the SQL statement;

[0007] calling a syntax parser corresponding to the SQL type through a multimodal parsing engine, and performing syntax parsing on the SQL statement through the syntax parser to generate a syntax tree, wherein the multimodal parsing engine can dynamically call a syntax parser corresponding to any SQL type;

[0008] Converting the syntax tree into a QueryInfo object by a syntax extractor in the multimodal parsing engine, wherein the syntax extractor is used to uniformly convert syntax trees of different structures into a QueryInfo object representing metadata features of an SQL statement;

[0009] The QueryInfo object is detected using a plurality of preset standard strategies to identify and intercept non-compliant SQL statements.

[0010] Optionally, converting the syntax tree into a QueryInfo object by a syntax extractor in the multimodal parsing engine includes:

[0011] Starting from the root node of the syntax tree, traversing the syntax tree through the syntax extractor in the multimodal parsing engine;

[0012] Iteratively parsing and extracting metadata features of nodes in the syntax tree, wherein the metadata features include table-related information, association conditions, filter conditions, aggregation logic, and partition scan range;

[0013] After the iteration is completed, the metadata features are mapped into unified structured information and stored in a QueryInfo object, generating a standardized representation of the QueryInfo object.

[0014] Optionally, the task request further includes the task submitting user, the task submitting platform, and the task creation time. Before calling the syntax parser corresponding to the SQL type through the multimodal parsing engine, the method further includes:

[0015] The parameter verification engine performs access review of the task request, including the following steps:

[0016] Verify whether the task submitting user has the operation authority for the engine corresponding to the SQL type; verify whether the task submitting platform is in the preset platform whitelist; verify whether the task creation time is earlier than the preset time threshold;

[0017] If the verification results of the above three steps are all true, it is determined that the access review of the task request has passed.

[0018] Optionally, the structured information in the QueryInfo object includes: input table and output table, SQL operation type and query condition set;

[0019] The input table and the output table are used to connect the blood relationship between the upstream and downstream tables in the SQL statement, and the query condition set is used to determine the scanning data range in the partition scanning strategy in combination with the metadata database.

[0020] Optionally, the multiple standardization strategies include: query statement basic feature strategy, table name creation standardization strategy, table creation attribute standardization strategy, large table query orderby strategy, partition scan strategy and parameter setting standardization strategy;

[0021] The query statement basic feature strategy is used to query the infrastructure compliance of the SQL statement;

[0022] The table name creation standardization strategy is used to constrain the naming rules of the table name;

[0023] The table creation attribute standardization strategy is used to standardize the field attributes in the table structure definition;

[0024] The large table query orderby strategy is used to limit the scenarios where orderby operations are performed on large tables;

[0025] The partition scan strategy is used to require query operations on partitioned tables to be filtered by partition keys;

[0026] The parameter setting specification strategy is used to uniformly manage the usage rules of parameters in SQL statements.

[0027] Optionally, in the process of verifying the QueryInfo object using multiple preset standard strategies, the method further includes:

[0028] In response to the received policy configuration adjustment instruction, at least one of the following two operations is performed on at least one existing standard policy in the rule base:

[0029] Dynamically modify the execution parameters of the standard policy, or add a new standard policy to the rule base, wherein the execution parameters include policy threshold, policy activation status and policy priority, and the standard policy takes effect when the configuration is adjusted;

[0030] Through the hot loading mechanism of the rule engine, the standard policies in the rule base are updated in real time;

[0031] The QueryInfo object is verified using the updated canonical strategy set.

[0032] Optionally, the method further includes:

[0033] Obtain the final test results, including SQL statement compliance status, access review results, and optimization suggestions;

[0034] The detection result is encapsulated into a set data format and returned to the preset platform via HTTP response, so that the preset platform triggers a response operation according to the detection result.

[0035] The present application provides a device for checking the compliance of SQL statements, the device comprising:

[0036] An acquisition module, configured to acquire a task request, wherein the task request includes an SQL statement to be detected and an SQL type of the SQL statement;

[0037] a parsing module, configured to call a syntax parser corresponding to the SQL type through a multimodal parsing engine, and perform syntax parsing on the SQL statement through the syntax parser to generate a syntax tree, wherein the multimodal parsing engine can dynamically call a syntax parser corresponding to any SQL type;

[0038] a conversion module, configured to convert the syntax tree into a QueryInfo object through a syntax extractor in the multimodal parsing engine, wherein the syntax extractor is configured to uniformly convert syntax trees of different structures into a QueryInfo object representing metadata features of an SQL statement;

[0039] The detection module is used to detect the QueryInfo object using a plurality of preset standard strategies to identify and intercept non-compliant SQL statements.

[0040] The present application provides an electronic device, comprising: at least one communication interface; at least one bus connected to the at least one communication interface; at least one processor connected to the at least one bus; and at least one memory connected to the at least one bus.

[0041] The present application also provides a computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are used to execute the SQL statement compliance checking method described in any one of the above items of the present application.

[0042] The present application provides a computer program product or computer program, which includes computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the methods provided in the aforementioned aspects of intercepting non-compliant SQL statements or various optional implementations of intercepting non-compliant SQL statements.

[0043] The above technical solution provided by the embodiment of the present application has the following advantages compared with the prior art: each SQL statement has a corresponding SQL type, and the multimodal parsing engine dynamically calls the corresponding syntax parser according to the SQL type. Each syntax parser converts a specific type of SQL statement into an abstract syntax tree to solve the problem of syntax differences between SQL dialects. In the face of structural differences between different syntax trees, the syntax extractor uniformly converts the metadata features in different syntax trees into a standardized QueryInfo object. The QueryInfo object shields the syntax differences between different types of SQL statements and forms a unified detection data model. Finally, the rule engine executes multiple standard strategies based on the QueryInfo object to accurately identify non-compliant SQL statements. The present application dynamically adapts to multiple types of SQL statements through a multimodal parsing engine, avoids developing detection logic separately for each SQL statement, and improves the efficiency of SQL statement compliance checking. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and, together with the description, serve to explain the principles of the present application.

[0045] In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without any creative work.

[0046] One or more embodiments are exemplarily illustrated by pictures in the corresponding drawings. These exemplifications do not constitute limitations on the embodiments. Elements with the same reference numerals in the drawings are represented as similar elements. Unless otherwise stated, the figures in the drawings do not constitute proportional limitations.

[0047] Figure 1 Schematic diagram of the SQL statement compliance checking system provided in an embodiment of the present application;

[0048] Figure 2 A flow chart of a method for checking SQL statement compliance provided in an embodiment of the present application;

[0049] Figure 3 A schematic diagram of a Hive execution log provided in an embodiment of the present application;

[0050] Figure 4 This is an architectural diagram of the SQL statement processing flow provided in the embodiment of the present application;

[0051] Figure 5 A schematic diagram of the structure of an SQL statement compliance checking device provided in an embodiment of the present application;

[0052] Figure 6 A schematic diagram of the structure of an electronic device provided in an embodiment of the present application. DETAILED DESCRIPTION

[0053] To make the purpose, technical solutions, and advantages of the embodiments of this application more clear, the technical solutions in the embodiments of this application will be clearly and completely described below in conjunction with the drawings in the embodiments of this application. Obviously, the described embodiments are part of the embodiments of this application, not all of the embodiments. Based on the embodiments in this application, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of this application.

[0054] The disclosure below provides many different embodiments or examples for implementing different structures of the present application. In order to simplify the disclosure of the present application, the components and settings of specific examples are described below. Of course, these are merely examples and are not intended to limit the present application. In addition, the present application may repeat reference numbers and / or letters in different examples. Such repetition is for the purpose of simplicity and clarity and does not in itself indicate the relationship between the various embodiments and / or settings discussed.

[0055] In order to solve the problem mentioned in the background technology that it is difficult to efficiently identify non-compliant multiple types of SQL statements, the embodiment of the present application converts different SQL statements into a unified QueryInfo object through a multimodal parsing engine, and then performs compliance verification based on multiple standard strategies to achieve efficient verification of multiple types of SQL statements.

[0056] Optionally, in an embodiment of the present application, the above SQL statement compliance checking method can be applied to Figure 1 In the hardware environment composed of the terminal 101 and the server 103 shown in FIG. Figure 1 As shown, server 103 is connected to terminal 101 via a network. A user submits a task request through the big data development platform on terminal 101. Terminal 101 then sends the task request to server 103. The multimodal SQL parsing engine in server 103 implements cross-engine grammar normalization through a grammar adapter pattern and feeds the SQL detection results back to the platform. A database 105 can be provided on the server or independently of the server to provide data storage services for server 103. The aforementioned network includes, but is not limited to, a wide area network, a metropolitan area network, or a local area network. Terminal 101 includes, but is not limited to, PCs, mobile phones, tablet computers, etc.

[0057] The following will be combined with specific implementation methods to provide a detailed description of a SQL statement compliance checking method provided in an embodiment of the present application, taking application to a server as an example. Figure 2 The specific steps are as follows:

[0058] Step 201: Obtain a task request, wherein the task request includes an SQL statement to be detected and an SQL type of the SQL statement;

[0059] Step 202: The multimodal parsing engine calls the parser corresponding to the SQL type, and parses the SQL statement using the parser to generate a syntax tree. The multimodal parsing engine can dynamically call the parser corresponding to any SQL type.

[0060] Step 203: The syntax tree is converted into a QueryInfo object by a syntax extractor in the multimodal parsing engine, wherein the syntax extractor is used to uniformly convert syntax trees of different structures into a QueryInfo object representing metadata features of the SQL statement;

[0061] Step 204: Detect the QueryInfo object using multiple preset standard strategies to identify and intercept non-compliant SQL statements.

[0062] First, some terms used in the embodiments of this application are explained, including the following.

[0063] Task request: A detection instruction initiated by an external system or user, which is the entry point for triggering the detection process.

[0064] SQL statements: involving Hive, Spark, Presto and other big data SQL.

[0065] SQL Type: identifies the dialect or execution engine type of the SQL statement, such as HiveSQL, SparkSQL, or PrestoSQL.

[0066] Multimodal parsing engine: A parsing framework that supports dynamic adaptation to multiple SQL dialects. By registering different syntax parsers, it achieves unified parsing of heterogeneous SQL statements.

[0067] Syntax parser: An existing parsing tool for a specific SQL dialect that converts SQL text into an abstract syntax tree (AST) based on grammar rules.

[0068] Syntax Tree (AST): A tree-structured representation of SQL statements, where each node represents a syntax element and edges represent the hierarchical relationships between elements. The AST structure varies across different dialects.

[0069] Syntax Extractor: The core component of the multimodal parsing engine, which traverses the syntax tree based on the visitor pattern, extracts metadata related to compliance detection, and converts it into a unified QueryInfo object.

[0070] QueryInfo object: A standardized metadata carrier used to uniformly represent the core features of different SQL dialects. This object allows detection logic to be independent of the syntax details of specific SQL dialects.

[0071] Compliance policy: A set of predefined compliance rules used to verify whether SQL statements comply with security, performance, or management regulations.

[0072] In step 201, the server receives a task request from an external platform via a standardized interface. This task request carries two key pieces of information: the SQL statement to be tested and the SQL type identifier for the SQL statement. The SQL type identifies the execution engine that the SQL statement is compatible with, such as the Hive engine, Spark engine, or Presto engine.

[0073] In step 202, after obtaining the SQL type identifier, the multimodal parsing engine dynamically loads a corresponding parser based on the identifier. Each parser is built specifically for the SQL statement syntax rules of a specific execution engine. For example, the HiveSQL parser considers Hive's support for partitioning syntax, such as partitioned by, as well as complex custom function call syntax; the SparkSQL parser must be able to recognize Spark's unique syntax structure for processing distributed data; and the PrestoSQL parser must adapt to Presto's efficient query syntax.

[0074] The parser's working process follows the lexical analysis and syntactic analysis stages in compiler theory. During lexical analysis, it reads the SQL text character by character and breaks it down into tokens based on lexical rules. It then uses recursive descent, the LL (Left-to-Right) algorithm, or the LR (Left-to-Right) algorithm to analyze the structural relationships between these tokens according to grammatical rules, converting the SQL text into a structured abstract syntax tree.

[0075] The syntax tree uses a unified tree structure to represent the logical structure of SQL statements. The nodes of the tree represent different syntax elements, such as the select clause, from clause, where clause, etc. The connection relationship between the nodes reflects the hierarchy and logical relationship between these syntax elements.

[0076] In step 203, the built-in syntax extractor in the multimodal parsing engine (such as SparkSQL Parser, HiveSQL Parser, or PrestoSQL Parser) traverses the generated syntax tree. Since the syntax tree structures generated by different execution engines are different, for example, the node structure of Hive's syntax tree when representing partition-related operations is different from that of the Spark syntax tree, the syntax extractor needs to be able to adapt to various syntax tree node types. The syntax extractor performs specific processing on different types of syntax tree nodes and extracts metadata features related to compliance detection. The syntax extractor then uniformly converts the extracted metadata into standardized QueryInfo objects.

[0077] The QueryInfo object is similar to a container, encapsulating the core features of SQL statements. It shields the syntactic differences between different types of SQL statements. Whether it is HiveSQL, SparkSQL, or PrestoSQL, they are ultimately presented in the unified QueryInfo object format. This provides a unified data model for subsequent standard policy detection, making the detection logic independent of the specific SQL syntax form.

[0078] In step 204, the server uses a rule engine to sequentially apply pre-defined standard policies based on the structured information encapsulated in the QueryInfo object to perform compliance verification. These standard policies cover multiple specific dimensions of detection, such as partition scan policies or large table sorting policies.

[0079] These standardized policies are executed sequentially using a chain of responsibility model. As the structured information of a SQL statement is sequentially checked by each policy, if any policy detects a violation, the system blocks further execution of the SQL statement and returns detailed violation information, informing the user or caller of the specific standardized policy that was violated. This rule-matching mechanism, driven by structured information, accurately detects multiple types of SQL statements, promptly identifying and blocking risky SQL operations.

[0080] Figure 3 This is a diagram of a Hive execution log. The image shows that Hive was selected as the execution engine. The execution result indicates that the task failed (execution refused). The execution log records the time the task started and failed. The failure was caused by an abnormal parameter setting, indicating that the tez.am.vertex.max-task-concurrency parameter was set too high and recommending using the default value. It also indicates that the query statement has the risk of a full table scan and that the partition field filter is missing in the where condition.

[0081] For example, the user enters the SQL statement to be tested on the platform, and the SQL type is identified as the Hive engine, indicating that this SQL statement is to be executed in the Hive environment. The multimodal parsing engine dynamically loads the Hive syntax parser based on the SQL type identification as the Hive engine. The Hive syntax parser performs lexical analysis and syntactic analysis on this SQL statement, converting it into an abstract syntax tree. The syntax extractor then traverses the syntax tree and uniformly converts the extracted meta-feature information into a QueryInfo object. Finally, the rule engine applies the specification strategy in sequence based on the structured information in the QueryInfo object. If the large table partition scanning strategy detects a violation, the system immediately intercepts this SQL statement and returns violation information, prompting the developer that this SQL statement does not reasonably use the partition key when querying the large table, which may result in poor query performance.

[0082] In this application, each SQL statement has a corresponding SQL type. The multimodal parsing engine dynamically calls the corresponding syntax parser according to the SQL type. Each syntax parser converts a specific type of SQL statement into an abstract syntax tree to solve the problem of syntax differences between SQL dialects. In the face of structural differences between different syntax trees, the syntax extractor uniformly converts the metadata features in different syntax trees into a standardized QueryInfo object. The QueryInfo object shields the syntax differences between different types of SQL statements and forms a unified detection data model. Finally, the rule engine executes multiple standard strategies based on the QueryInfo object to accurately identify non-compliant SQL statements. This application dynamically adapts to multiple types of SQL statements through a multimodal parsing engine, avoiding the development of separate detection logic for each SQL statement, thereby improving the efficiency of SQL statement compliance checking.

[0083] As an optional implementation, in step 203, the syntax tree is converted into a QueryInfo object by a syntax extractor in the multimodal parsing engine, including the following content:

[0084] Step S11: Starting from the root node of the syntax tree, the syntax tree is traversed by the syntax extractor in the multimodal parsing engine;

[0085] Step S12: Iteratively parse and extract metadata features of nodes in the syntax tree, where the metadata features include table-related information, association conditions, filter conditions, aggregation logic, and partition scan range;

[0086] Step S13: After the iteration is completed, the metadata features are mapped into unified structured information and stored in the QueryInfo object, generating a standardized representation of the QueryInfo object.

[0087] In step S11, the syntax extractor in the multimodal parsing engine is capable of processing different types of SQL statements. The multimodal parsing engine can dynamically call different syntax parsers to accommodate a variety of SQL dialects and structures. The syntax extractor begins at the root node of the abstract syntax tree and traverses it using a depth-first or breadth-first strategy, ensuring that all nodes in the entire syntax tree are accessed. Different SQL types have different access nodes, such as the TOK_QUERY node in Hive, the SubqueryAlias ​​node in Spark, and the Query node in Presto.

[0088] In step S12, the syntax extractor starts from the root node, traverses the nodes in the syntax tree and iteratively parses the metadata features of each node. The metadata features include the following content.

[0089] Table-related information: For each table reference node, extract the table name, alias, database name, and its corresponding physical storage path (such as the HDFS path in Hive). This information will be used for subsequent query optimization and execution plan generation.

[0090] Join conditions: Traverse the join nodes, extract the join fields and join types (such as INNERjoin, LEFTjoin, etc.), and store this information in the joinConditions field.

[0091] Filter conditions: Traverse the where node, extract the fields, operators, and values ​​in the expression, and store this information in the where Conditions field. These filter conditions can help optimize query performance.

[0092] Aggregation logic: Traverses the group by nodes, extracts aggregate fields and functions (such as count and sum), and stores this information in the aggregation fields. Aggregation logic is an important component of complex queries.

[0093] Partition Scan Range: Traverses the partition fields (e.g., ds = xxx, pt = xxx), extracts partition key-value pairs, and stores them in the partition Filters field. Partition Scan Range helps improve query efficiency.

[0094] In step S13, after completing the traversal and parsing of the syntax tree, the syntax extractor uniformly maps and organizes the extracted metadata features of more than 50 dimensions to form a structured QueryInfo object. Since each metadata feature field has its specific purpose and meaning, this ensures the accuracy and completeness of the metadata features. In addition, the generated structured QueryInfo object contains all the key information of the query, forming a standardized intermediate representation. This standardized representation allows different SQL engines (such as HiveSQL, SparkSQL, and PrestoSQL) to share and reuse the same intermediate representation. This application only extracts multi-dimensional feature data, and there is no need to prioritize the execution plan. The parsing efficiency is doubled, and a response in seconds is achieved.

[0095] The structured information in the QueryInfo object includes: table name, field, statement type, calculation logic, input table and output table, SQL operation type, and query condition set.

[0096] Input and output tables are used to precisely connect the relationships between upstream and downstream tables in SQL statements, creating a visual framework for data flow. By recording data's input and output tables, the system can track where data flows from its original storage location after processing. This not only helps understand the data's entire lifecycle but also provides a crucial basis for data traceability and impact analysis.

[0097] Operation Name records the SQL operation type. Other common operation name types include createtable, altertable_rename, droptable, query, show_createtable, showtables, and setvar (not all types).

[0098] The query condition set records key query conditions such as the where clause and join conditions in the SQL statement. These conditions, combined with the table structure and partition information in the metadata database, can accurately determine the scan data range in the partition scan strategy. The system analyzes the matching relationship between the filter fields in the query conditions and the partition fields to determine whether partition pruning technology can be used to narrow the data scan range. For example, when the query conditions include filter conditions for partition fields, the system can only scan the partition data that meets the conditions, avoiding the performance loss caused by full table scans, improving SQL execution efficiency, and reducing resource consumption.

[0099] In this application, by converting complex syntax trees into structured QueryInfo objects, the system is able to perform standardized processing on different types of SQL statements. This conversion mechanism not only retains the key semantic information in the original SQL query, but also shields the differences between different SQL engines. This application has good versatility and scalability, and can adapt to a variety of SQL types and support the parsing and recognition of multiple SQL dialects. Through the flexible configuration of the syntax extractor, cross-platform and multi-engine consistent processing capabilities are achieved. In addition, the generated QueryInfo object expresses the core metadata features of the SQL query in a unified data structure, providing a standardized data interface for subsequent rule verification.

[0100] As an optional implementation, the task request also includes the task submitting user, the task submitting platform, and the task creation time. After implementing step 201 and before implementing step 202, the device further includes the following content:

[0101] Step S21: Performing access review of the task request through the parameter verification engine, including the following steps:

[0102] Verify whether the user submitting the task has the operation permissions for the SQL engine corresponding to the type; verify whether the task submission platform is in the preset platform whitelist; verify whether the task creation time is earlier than the preset time threshold;

[0103] Step S22: If the verification results of the above three steps are all true, it is determined that the access review of the task request has passed.

[0104] After receiving the task request in step 201, the system uses the parameter verification engine to conduct a strict access review of the task request, thereby building a front-line defense for security detection. The parameter verification engine verifies the task request from three dimensions, including the following.

[0105] User permission verification: Based on a pre-set permission mapping table, the system verifies whether the user submitting the task has the permissions to operate the corresponding SQL engine. For example, only a specific group of data analysts is allowed to execute interactive queries in the Presto engine, while ordinary business personnel do not have this permission. This prevents the risk of data leakage or resource abuse caused by unauthorized operations.

[0106] Platform whitelist verification: The platform identifiers used to submit tasks, such as IP addresses and application IDs, are compared against a pre-defined list of trusted platforms. Only requests from platforms on the whitelist, such as internal enterprise data centers and certified third-party data interfaces, are accepted, effectively blocking malicious requests from unauthorized platforms.

[0107] Timeliness check: Compare the task creation time with the time threshold set by the system. If the task creation time is earlier than the preset threshold (for example, more than 24 hours), the request is considered expired, avoiding potential risks caused by task backlogs or replay attacks.

[0108] The system comprehensively assesses the results of the three aforementioned checks. Only when the user permission check, platform whitelist check, and timeliness check all pass will the system deem the task request's access approval successful and allow it to proceed to the subsequent syntax parsing phase. If any of these checks fail, the system immediately terminates the request and returns a clear error message to the requester, such as "Insufficient Permissions," "Unauthorized Platform," or "Request Timed Out."

[0109] This application uses a triple-check mechanism to effectively filter out requests from unauthorized users, illegal platforms, and expired tasks, preventing security risks such as SQL injection and unauthorized access from being detected, thereby improving system security. Furthermore, by preemptively filtering out invalid requests, the multimodal parsing engine is prevented from performing meaningless parsing and detection of illegal tasks, reducing system resource consumption and improving overall processing efficiency.

[0110] As an optional implementation method, multiple standardization strategies include: query statement basic feature strategy, table name creation standardization strategy, table creation attribute standardization strategy, large table query orderby strategy, partition scan strategy and parameter setting standardization strategy.

[0111] The basic query feature strategy focuses on the basic structural compliance of SQL statements, ensuring the fundamental logical correctness of SQL statements by checking whether the statements contain required syntax elements, whether keywords are used correctly, and whether the clause order complies with the specifications. For example, the statement length limit is: ≤ 5000 words for a single SQL statement, the number of input tables is: ≤ 20 tables referenced in a single query, the join complexity is: ≤ 20 join operations, and the aggregation complexity is: ≤ 20 group by operations.

[0112] Create a table name specification policy to constrain table name naming rules. Specific rules may include limiting table name length, prohibiting the use of special characters, and requiring table names to contain business identifiers or data type identifiers. For example, in Presto, limit table creation to databases prefixed with dwd, dws, or ods_.

[0113] Table attribute standardization strategies are used to standardize field attributes in table structure definitions, including data types, field lengths, primary key constraints, and index settings. This unified attribute standard prevents data storage anomalies and query performance issues caused by non-standard field definitions. For example, non-Hive external tables are prohibited from using LOCATION to specify paths, and orc.compress = 'snappy' (snappy should be capitalized).

[0114] The large table query orderby strategy is used to limit scenarios where orderby operations are performed on large tables. Performing orderby operations on tables with large data volumes may lead to performance bottlenecks, and this strategy is used to limit or optimize such scenarios. Compliance is determined by evaluating factors such as the table's data size, whether the orderby field has an index, and the resource consumption of the sorting operation. If a high-risk operation is detected (such as sorting the entire table of a large, unindexed table), the system will intercept it and provide optimization suggestions, such as adding indexes or adjusting the sorting logic, to avoid cluster resource exhaustion caused by inefficient sorting. For example, the Presto engine prohibits executing orderby without filtering conditions on tables larger than 10GB.

[0115] The partition scan strategy requires queries on partitioned tables to be filtered by the partition key. When processing partitioned tables, this strategy requires queries to be filtered by the partition key to narrow the data scan range and improve query efficiency. The system analyzes the matching relationship between the fields in the query criteria and the table's partition key to determine whether the partition pruning conditions are met. If the query does not use the partition key filter, an interception will be triggered and optimization prompts will be prompted to ensure that data queries fully utilize the performance advantages of partitioned tables. For example, a large table with a top partition has a partition day limit.

[0116] The parameter setting standardization policy is used to uniformly manage parameter usage rules in SQL statements, including parameter naming conventions, value ranges, and default values. Standardized parameter settings avoid execution errors and security risks caused by inconsistent parameter usage. Specifically, this policy prohibits users from arbitrarily setting the tez.am.vertex.max-task-concurrency and hive.tez.container.size parameters, which could lead to excessive task resource consumption.

[0117] This application uses multiple strategies to work together to intercept potential errors before SQL statements are executed, avoiding problems such as task failure and data loss caused by non-compliant statements, and improving system stability and reliability.

[0118] As an optional implementation, the process of verifying the QueryInfo object using multiple preset standard strategies also includes the following:

[0119] Step S31: In response to the received policy configuration adjustment instruction, perform at least one of the following two operations on at least one existing standard policy in the rule base:

[0120] Step S32: Dynamically modify the execution parameters of the standard policy, or add a new standard policy to the rule base, wherein the execution parameters include the policy threshold, policy activation status, and policy priority. The standard policy takes effect when the configuration is adjusted;

[0121] Step S33: Using the hot loading mechanism of the rule engine, the specification policy in the rule base is updated in real time;

[0122] Step S34: Use the updated standard policy set to verify the QueryInfo object.

[0123] As business needs and security regulations continue to change, in order to keep the SQL compliance detection mechanism flexible and adaptable, the embodiments of the present application provide a dynamic policy configuration adjustment function, including the following content.

[0124] In step S31, the system continuously monitors policy configuration adjustment instructions from the management side. These instructions can be triggered by data administrators, security managers, or automated operation and maintenance tools, and support visual interface operations or API interface calls to ensure that policy adjustment operations are convenient and efficient.

[0125] In step S32, the system implements hot loading of policies through the SPI mechanism, and defines interfaces (such as Standard PolicyDao) to enable each policy to independently process QueryInfo objects. When adjusting policies, the rule base is dynamically updated by modifying configuration files or injecting parameters. Specifically, the system dynamically modifies or expands the standard policies in the rule base according to the adjustment instructions. For existing standard policies, its execution parameters can be flexibly adjusted. 1. Adjust the policy threshold, for example, change the "large table" judgment threshold in the large table query orderby strategy from 5 million records to 10 million records; 2. Change the policy enablement status, such as temporarily disabling the creation of table name standardization strategies to support the urgent table creation needs of special projects; 3. Modify the policy priority to ensure that key strategies (such as partition scan strategies) are executed before secondary strategies. The above strategies independently process QueryInfo objects and generate detection tags.

[0126] The system also supports adding standardized policies to the rule base. For example, when introducing new data security requirements, you can quickly add a "sensitive field desensitization policy." These configuration adjustments take effect immediately without restarting the system, minimizing the impact on normal detection processes. This application provides a policy development SDK to support the rapid integration of custom policies.

[0127] In step S33, the system leverages the hot-loading mechanism of the rule engine to enable real-time updates of the rule base, enabling plug-and-play policies. Through event monitoring and version comparison, once a policy adjustment is detected, the updated policy is immediately loaded into memory, replacing the original policy version, ensuring that subsequent detections use the latest rules.

[0128] In step S34, the rule engine performs compliance checks on the QueryInfo object based on the updated set of standard policies. Both newly added policies and those with adjusted parameters can be immediately applied to SQL statement detection, ensuring that the detection results meet the latest business and security requirements.

[0129] This application supports dynamic modification and addition of new regulatory policies, enabling the system to quickly respond to scenarios such as changes in business needs and updates to security regulations. Policy adjustment configurations take effect immediately and the rule base is updated through a hot loading mechanism to avoid affecting the SQL detection process due to system restart or service interruption, ensuring the continuity of data operations and improving operation and maintenance efficiency. By adjusting policy thresholds, enabling status and priority, detection rules can be customized for different business scenarios to achieve precise allocation of resources. There is no need to modify the code and redeploy for each policy adjustment, reducing development and operation and maintenance costs, while avoiding compliance risks caused by policy lags and reducing potential losses.

[0130] As an optional implementation method, after obtaining the final test results, which include the SQL statement compliance status, access review results and optimization suggestions, the test results are encapsulated into a set data format and returned to the preset platform via HTTP response, so that the preset platform triggers a response operation based on the test results.

[0131] After the system completes the SQL statement access review and policy verification, it enters the test result feedback phase. The test result generation includes the following content.

[0132] Access review detection results: Integrate the access review results of steps S21 to S22, clearly mark whether the task request has passed the user authority, platform whitelist and timeliness verification, and return the results of access review success or access review failure (for example, the user does not have Hive engine operation permissions) to the terminal platform.

[0133] Detection results of strategy verification: 1. Compliance status determination: The rule engine generates compliance, violation, or partial compliance status based on the results of the standard strategy verification. If the SQL statement violates any standard strategy (such as large table query without using partition key), it is marked as a violation and an interception report is generated. The interception report records the specific violation strategy name (such as partition scan strategy) and the reason (such as the partition key filter condition is not included). 2. Optimization suggestion generation: Specific improvement plans are provided for violations. For example, if the large table query orderby strategy is triggered, it is recommended to "add an index to the sorting field or limit the number of rows in the result set"; if the partition scan strategy is triggered, it is recommended to "add a partition key filter condition in the where clause."

[0134] The system encapsulates the test results in a pre-set data format (such as JSON Schema) and returns them to a pre-set platform, such as the DS (Data Studio Platform) platform, via HTTP. The pre-set platform automatically triggers response actions based on the test results. If the status is a violation, the task is intercepted for manual review, and a work order is generated based on optimization recommendations, which is automatically assigned to a developer for statement optimization. If the status is compliant, the task is submitted to the cluster computing engine for subsequent execution.

[0135] The big data specification detection system in this application embodiment is built on the SpringBoot framework and realizes the automatic detection of heterogeneous SQL tasks through layered architecture and modular design. The following is a technical description of the core process.

[0136] Step 1: Receive a task request.

[0137] The system receives task requests from the DS platform through a standardized interface, which includes: the SQL statement to be tested, the SQL type identifier, the task submitting user, the task submission platform, and the task creation time.

[0138] Step 2: Admission review.

[0139] The parameter validation engine performs three checks: user permission verification, platform whitelist verification, and timeliness verification. If all three checks pass, the subsequent process proceeds; otherwise, the request is intercepted and an error code is returned.

[0140] Step 3: Syntax parsing and syntax tree generation.

[0141] The multimodal parsing engine dynamically loads the corresponding parser based on the SQL type, and uses the parser to convert SQL statements into an abstract syntax tree. This application supports parsing in a metadata-free environment, meaning that even when table metadata doesn't exist (e.g., the table hasn't been created), the parser can still independently parse DDL (Data Definition Language) statements at the syntax level, verifying only the correctness of the statement's syntax structure without relying on existing table information in the database.

[0142] Step 4: Convert the syntax tree to a QueryInfo object.

[0143] The syntax extractor uses the visitor pattern to traverse the syntax tree, extract multi-dimensional metadata features and uniformly convert them into standardized QueryInfo objects. The structured information of the QueryInfo object includes: input table and output table, SQL operation type, query condition set, etc.

[0144] Step 5: The rule engine performs policy verification.

[0145] Based on the QueryInfo object, the rule engine applies pre-defined standardized policies sequentially through a chain of responsibility model. These policies include: basic query feature policies, partition scan policies, large table query order by policies, and parameter setting standardized policies. If any policy detects a violation, the SQL statement is immediately intercepted and the violation details are recorded.

[0146] Step 6: Policy configuration hot update.

[0147] The system supports dynamic adjustment of policy parameters or addition of new policies through the management end; the hot loading mechanism updates the rule base in real time to ensure that the adjusted policies take effect immediately without restarting the system.

[0148] Step 7: Results integration and packaging.

[0149] The system generates test results that include compliance status, access review results, and optimization recommendations. These results are packaged in a standardized format and returned via HTTP responses to a pre-defined platform, which automatically executes subsequent actions based on the results.

[0150] Figure 4 This is an architectural diagram of the SQL statement processing flow. Figure 4 This section mainly describes the process from task submission to SQL statement compliance testing. The overall process is as follows:

[0151] Task submission: Users submit tasks to the DS platform, and the DS platform passes the tasks to the system by calling the SQL interface.

[0152] Parameter verification: After a task enters the system, it is first verified by the parameter verification engine to determine the SQL statement type, which may be SparkSQL, HiveSQL, or PrestoSQL.

[0153] Syntax parsing: Depending on the SQL statement type, the corresponding syntax parser is used to process it, that is, the SparkSQLParse syntax parser processes SparkSQL, the HiveSQL Parse syntax parser processes HiveSQL, and the PrestoSQLParse syntax parser processes PrestoSQL, generating the corresponding syntax tree.

[0154] Sentence information extraction and conversion: After grammatical parsing, the sentence information is extracted by the grammar extractor and uniformly converted into a QueryInfo object, which integrates the metadata related to the sentence.

[0155] Policy detection: The QueryInfo object is passed to the policy detection module, which contains a series of standard policies (such as Rule 1, Rule 2, Rule 3, Rule 4, etc.) for compliance detection of SQL statements.

[0156] Result feedback: intercepts non-compliant SQL statements and returns the results of whether the SQL statements comply with the specifications to the DS platform.

[0157] The architecture in this application's embodiments supports horizontal expansion of multiple SQL dialects and flexible iterative detection strategies by decoupling parsing, rules, and response modules, providing standardized access governance capabilities for big data development. By integrating native parsers for multiple engines, the architecture directly reuses the official parsers for Hive, Spark, and Presto, achieving full syntax compatibility and stripping out unnecessary steps like execution plan optimization (only extracting multidimensional query metadata features without optimizing execution plans), doubling parsing efficiency.

[0158] Based on the same technical concept, this application provides a SQL statement compliance checking device, such as Figure 5 As shown, the device includes:

[0159] An acquisition module 501 is configured to acquire a task request, wherein the task request includes an SQL statement to be detected and an SQL type of the SQL statement;

[0160] Parsing module 502, configured to call a parser corresponding to an SQL type through a multimodal parsing engine, and parse the SQL statement through the parser to generate a syntax tree, wherein the multimodal parsing engine can dynamically call a parser corresponding to any SQL type;

[0161] A conversion module 503 is configured to convert the syntax tree into a QueryInfo object through a syntax extractor in the multimodal parsing engine, wherein the syntax extractor is configured to uniformly convert syntax trees of different structures into a QueryInfo object representing metadata features of an SQL statement;

[0162] The detection module 504 is used to detect the QueryInfo object using a plurality of preset standard strategies to identify and intercept non-compliant SQL statements.

[0163] Optionally, the conversion module 503 is configured to:

[0164] Starting from the root node of the syntax tree, the syntax tree is traversed through the syntax extractor in the multimodal parsing engine;

[0165] Iteratively parses and extracts metadata features of nodes in the syntax tree, including table-related information, join conditions, filter conditions, aggregation logic, and partition scan ranges.

[0166] After the iteration is completed, the metadata features are mapped into unified structured information and stored in the QueryInfo object, generating a standardized representation of the QueryInfo object.

[0167] Optionally, the task request also includes the task submitting user, the task submitting platform, and the task creation time. The device is further configured to:

[0168] The parameter verification engine performs access review of task requests, including the following steps:

[0169] Verify whether the user submitting the task has the operation permissions for the SQL engine corresponding to the type; verify whether the task submission platform is in the preset platform whitelist; verify whether the task creation time is earlier than the preset time threshold;

[0170] If the verification results of the above three steps are all true, the access review of the task request is determined to be passed.

[0171] Optionally, the structured information in the QueryInfo object includes: input table and output table, SQL operation type, and query condition set;

[0172] The input table and the output table are used to connect the blood relationship between the upstream and downstream tables in the SQL statement, and the query condition set is used to determine the scanning data range in the partition scanning strategy in combination with the metadata database.

[0173] Optionally, multiple standardization strategies include: query statement basic feature strategy, table name creation standardization strategy, table creation attribute standardization strategy, large table query orderby strategy, partition scan strategy, and parameter setting standardization strategy;

[0174] Among them, the query statement basic feature strategy is used to query the infrastructure compliance of SQL statements;

[0175] Create a table name specification strategy to constrain the naming rules of table names;

[0176] The table attribute specification strategy is used to standardize the field attributes in the table structure definition;

[0177] The large table query orderby strategy is used to limit the scenarios where orderby operations are performed on large tables;

[0178] The partition scan strategy is used to require query operations on partitioned tables to be filtered by partition keys;

[0179] Parameter setting specification strategy is used to uniformly manage the usage rules of parameters in SQL statements.

[0180] Optionally, the device is further used to:

[0181] In response to the received policy configuration adjustment instruction, at least one of the following two operations is performed on at least one existing standard policy in the rule base:

[0182] Dynamically modify the execution parameters of a standard policy or add a new standard policy to the rule base. The execution parameters include policy threshold, policy activation status, and policy priority. The standard policy takes effect when the configuration is adjusted.

[0183] Through the hot loading mechanism of the rule engine, the standard policies in the rule base are updated in real time;

[0184] Use the updated canonical strategy set to validate the QueryInfo object.

[0185] Optionally, the device is further used to:

[0186] Obtain the final test results, which include SQL statement compliance status, access review results, and optimization suggestions;

[0187] The detection results are encapsulated into a set data format and returned to the preset platform via HTTP response, so that the preset platform triggers a response operation based on the detection results.

[0188] like Figure 6 As shown, an embodiment of the present application provides an electronic device, including a processor 601, a communication interface 602, a memory 603 and a communication bus 604, wherein the processor 601, the communication interface 602, and the memory 603 communicate with each other through the communication bus 604.

[0189] The memory 603 is used to store computer programs.

[0190] In one embodiment of the present application, the processor 601 is configured to implement the SQL statement compliance checking method provided by any one of the aforementioned method embodiments when executing the program stored in the memory 603 .

[0191] An embodiment of the present application further provides a computer-readable storage medium having a computer program stored thereon. When the computer program is executed by a processor, the steps of the SQL statement compliance checking method provided in any of the aforementioned method embodiments are implemented.

[0192] The present application provides a computer program product or computer program, which includes computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the methods provided in the aforementioned aspects of intercepting non-compliant SQL statements or various optional implementations of intercepting non-compliant SQL statements.

[0193] The device embodiments described above are merely illustrative. The units described as separate components may or may not be physically separate, and the components shown as units may or may not be physical units, that is, they may be located in one place or distributed across multiple network units. Some or all of the modules may be selected based on actual needs to achieve the objectives of this embodiment.

[0194] Through the description of the above embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus a general hardware platform, or of course, by hardware. Based on this understanding, the above technical solution, in essence, or the part that contributes to the relevant technology, can be embodied in the form of a software product. The computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., and includes a number of instructions for enabling a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or certain parts of the embodiment.

[0195] It should be understood that the terms used herein are for the purpose of describing specific example embodiments only and are not intended to be limiting. Unless the context clearly indicates otherwise, the singular forms "one", "an" and "said" as used herein may also be meant to include plural forms. The terms "comprise", "include", "contain" and "have" are inclusive and therefore specify the presence of stated features, steps, operations, elements and / or parts, but do not exclude the presence or addition of one or more other features, steps, operations, elements, parts, and / or combinations thereof. The method steps, processes, and operations described herein are not to be construed as necessarily requiring them to be performed in the specific order described or illustrated, unless the order of execution is clearly indicated. It should also be understood that additional or alternative steps may be used.

[0196] The foregoing is merely a list of specific embodiments of the present application, intended to enable those skilled in the art to understand or implement the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be implemented in other embodiments without departing from the spirit or scope of the present application. Therefore, the present application is not limited to the embodiments shown herein, but is intended to conform to the broadest scope consistent with the principles and novel features of the present application.

Claims

1. A method for checking SQL statement compliance, characterized in that: The method comprises: Obtaining a task request, wherein the task request includes an SQL statement to be detected and an SQL type of the SQL statement; calling a syntax parser corresponding to the SQL type through a multimodal parsing engine, and performing syntax parsing on the SQL statement through the syntax parser to generate a syntax tree, wherein the multimodal parsing engine can dynamically call a syntax parser corresponding to any SQL type; Converting the syntax tree into a QueryInfo object by a syntax extractor in the multimodal parsing engine, wherein the syntax extractor is used to uniformly convert syntax trees of different structures into a QueryInfo object representing metadata features of an SQL statement; The QueryInfo object is detected using a plurality of preset standard strategies to identify and intercept non-compliant SQL statements.

2. The method according to claim 1, characterized in that Converting the syntax tree into a QueryInfo object by a syntax extractor in the multimodal parsing engine includes: Starting from the root node of the syntax tree, traversing the syntax tree through the syntax extractor in the multimodal parsing engine; Iteratively parsing and extracting metadata features of nodes in the syntax tree, wherein the metadata features include table-related information, association conditions, filter conditions, aggregation logic, and partition scan range; After the iteration is completed, the metadata features are mapped into unified structured information and stored in a QueryInfo object, generating a standardized representation of the QueryInfo object.

3. The method according to claim 1, characterized in that The task request also includes the task submitting user, the task submitting platform, and the task creation time. Before calling the syntax parser corresponding to the SQL type through the multimodal parsing engine, the method further includes: The parameter verification engine performs access review of the task request, including the following steps: Verify whether the task submitting user has the operation authority for the engine corresponding to the SQL type; verify whether the task submitting platform is in the preset platform whitelist; verify whether the task creation time is earlier than the preset time threshold; If the verification results of the above three steps are all true, it is determined that the access review of the task request has passed.

4. The method according to claim 1, wherein The structured information in the QueryInfo object includes: input table and output table, SQL operation type and query condition set; The input table and the output table are used to connect the blood relationship between the upstream and downstream tables in the SQL statement, and the query condition set is used to determine the scanning data range in the partition scanning strategy in combination with the metadata database.

5. The method according to claim 1, wherein The multiple standardization strategies include: query statement basic feature strategy, table name creation standardization strategy, table attribute standardization strategy, large table query orderby strategy, partition scan strategy and parameter setting standardization strategy; The query statement basic feature strategy is used to query the infrastructure compliance of the SQL statement; The table name creation standardization strategy is used to constrain the naming rules of the table name; The table creation attribute standardization strategy is used to standardize the field attributes in the table structure definition; The large table query orderby strategy is used to limit the scenarios where orderby operations are performed on large tables; The partition scan strategy is used to require query operations on partitioned tables to be filtered by partition keys; The parameter setting specification strategy is used to uniformly manage the usage rules of parameters in SQL statements.

6. The method according to claim 1, characterized in that In the process of verifying the QueryInfo object using multiple preset standard strategies, the method further includes: In response to the received policy configuration adjustment instruction, at least one of the following two operations is performed on at least one existing standard policy in the rule base: Dynamically modify the execution parameters of the standard policy, or add a new standard policy to the rule base, wherein the execution parameters include policy threshold, policy activation status and policy priority, and the standard policy takes effect when the configuration is adjusted; Through the hot loading mechanism of the rule engine, the standard policies in the rule base are updated in real time; The QueryInfo object is verified using the updated canonical strategy set.

7. The method according to claim 1, characterized in that The method further comprises: Obtain the final test results, including SQL statement compliance status, access review results, and optimization suggestions; The detection result is encapsulated into a set data format and returned to the preset platform via HTTP response, so that the preset platform triggers a response operation according to the detection result.

8. A device for checking SQL statement compliance, characterized in that: The device comprises: An acquisition module, configured to acquire a task request, wherein the task request includes an SQL statement to be detected and an SQL type of the SQL statement; a parsing module, configured to call a syntax parser corresponding to the SQL type through a multimodal parsing engine, and perform syntax parsing on the SQL statement through the syntax parser to generate a syntax tree, wherein the multimodal parsing engine can dynamically call a syntax parser corresponding to any SQL type; a conversion module, configured to convert the syntax tree into a QueryInfo object through a syntax extractor in the multimodal parsing engine, wherein the syntax extractor is configured to uniformly convert syntax trees of different structures into a QueryInfo object representing metadata features of an SQL statement; The detection module is used to detect the QueryInfo object using a plurality of preset standard strategies to identify and intercept non-compliant SQL statements.

9. An electronic device, characterized in that: It includes a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus; Memory for storing computer programs; A processor, configured to implement the method according to any one of claims 1 to 7 when executing a program stored in a memory.

10. A computer-readable storage medium, characterized in that The computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method according to any one of claims 1 to 7 is implemented.

Images