A method, device, and storage medium for generating SQL based on natural language.
By using a natural language-based SQL generation method and leveraging cascading verification and automatic repair mechanisms, the problem of low-quality AI-generated SQL was solved, achieving efficient and standardized SQL generation and improving data development efficiency and reliability.
Patent Information
- Application Number
- CN202610972230.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2026-07-01
- Publication Date
- 2026-11-17
AI Technical Summary
Existing AI-generated SQL suffers from quality issues such as syntax errors, non-standard naming, or incomplete structure, resulting in low data development efficiency and poor reliability.
The SQL generation method based on natural language is adopted. Through cascading verification processing in short-circuit mode, syntax verification, naming convention verification, structural convention verification, hierarchical compliance verification, and data flow verification are performed in sequence. In short-circuit mode, if any stage of verification fails, the subsequent verification will be terminated, and repair and repeated verification will be performed when verification fails, until it passes or reaches the preset threshold.
It significantly improves the quality of generated SQL, ensuring the standardization, architectural consistency, and long-term maintainability of output SQL, thereby improving data development efficiency and reliability.
Smart Images

Figure CN123115176A_ABST
Abstract
Description
Technical Field
[0001] This application relates to the field of artificial intelligence technology, and in particular to a method, device and storage medium for generating SQL based on natural language. Background Technology
[0002] With the rapid development of big data technology and the deepening of enterprise digital transformation, data warehouses have become the core infrastructure for enterprise data asset management and business decision support. Structured Query Language (SCL), as the standard language for data warehouse development, is widely used throughout the entire process of data extraction, transformation, loading, data modeling, and data analysis. However, the traditional manual SQL development model relies heavily on the professional skills and experience of developers, resulting in low development efficiency, long development cycles, and a high risk of errors, making it difficult to meet the ever-increasing data processing needs of enterprises. In recent years, AI (Artificial Intelligence) SQL generation technology based on large language models has gradually become the mainstream tool for data warehouse development due to its high efficiency and ease of use. It can significantly improve data development efficiency and meet the growing needs of digital transformation for data asset management.
[0003] However, existing AI-generated SQL commonly suffers from quality issues such as syntax errors, non-standard naming, or incomplete structure. These problems significantly reduce data development efficiency and data reliability. Therefore, current AI-generated SQL methods generally produce SQL of poor quality. Summary of the Invention This application provides a method, device, and storage medium for generating SQL based on natural language, which can solve the technical problem of low quality of generated SQL. It obtains the verification result through cascading verification in short-circuit mode, and performs repair and repeated verification when verification fails, which significantly improves the quality of the final output target SQL.
[0004] In a first aspect, embodiments of this application provide a natural language-based SQL generation method, comprising: Receive demand information in natural language form, and intelligently generate the initial SQL based on the demand information and the artificial intelligence agent; The initial SQL is subjected to cascading validation in short-circuit mode to obtain the validation result. The cascading validation process involves sequentially executing syntax validation, naming convention validation, structural convention validation, hierarchical compliance validation, and data flow validation. The short-circuit mode terminates the subsequent validation process if any stage of the validation fails. If the verification result is a failure, the initial SQL is repaired according to the verification result and the short-circuit mode cascading verification is performed again. If the verification passes within the preset threshold number of repairs, the corresponding SQL that passes the verification is output as the target SQL. If the verification still fails even after the number of repairs reaches the preset threshold, a diagnostic report is output. If the verification result is successful, the initial SQL will be output as the target SQL.
[0005] Furthermore, the initial SQL is subjected to short-circuit mode cascading validation to obtain the validation results, including: The initial SQL is subjected to hierarchical recognition processing to obtain the target level of the initial SQL in the preset data warehouse hierarchy. The hierarchical recognition processing adopts a three-level cascaded recognition strategy of table name prefix matching, database name matching, and SQL statement feature inference. The data warehouse hierarchy includes operational data storage layer, data detail layer, data summary layer, application data service layer, and dimension layer. The initial SQL is subjected to statement type identification to obtain the target statement type, which includes data definition type and data operation type; Based on the target level and target statement type, load a subset of target rules from the preset constraint rule set. The subset of target rules includes common rules and level-specific rules. The initial SQL is subjected to short-circuit mode cascading verification based on the constraint rules in the target rule subset to obtain the verification result.
[0006] Furthermore, the initial SQL is subjected to short-circuit mode cascading validation to obtain the validation results, including: The initial SQL is parsed into an abstract syntax tree. If the parsing is successful, the syntax validation passes. If the parsing fails, the validation result of the cascading validation process is validation failure. If the syntax check passes, the abstract syntax tree is checked for naming conventions according to the naming convention rules in the target rule subset to obtain the naming convention check result. If the naming convention validation result is successful, the abstract syntax tree is validated according to the structure convention rules in the target rule subset to obtain the structure convention validation result; if the naming convention validation result is unsuccessful, the cascade validation process will also fail. If the structural specification verification result is "passed", the abstract syntax tree is subjected to hierarchical compliance verification based on the hierarchical compliance rules in the target rule subset to obtain the hierarchical compliance verification result; if the structural specification verification result is "failed", the cascade verification processing result is "failed". If the hierarchical compliance verification result is "verification passed", the abstract syntax tree is verified for data flow according to the data flow rules in the target rule subset to obtain the data flow verification result; if the hierarchical compliance verification result is "verification failed", the cascade verification process will also result in "verification failed". If the data flow verification result is successful, the cascade verification process will also pass; if the data flow verification result is unsuccessful, the cascade verification process will fail.
[0007] Furthermore, the abstract syntax tree is subjected to naming convention validation based on the naming convention rules in the target rule subset to obtain the naming convention validation result, including: Extract the target identifier from the parsed abstract syntax tree, where the target identifier includes the target table name and field names; Obtain the prefix and suffix of the target table name, perform matching and verification based on the prefix of the target table name with the target level, and perform legality verification based on the suffix of the target table name with the preset set of legal suffixes corresponding to the target level, and obtain the table name verification result; Perform snake-shaped naming format validation and database reserved word validation on each field name in the target identifier to obtain the field validation results; When the target layer is the application data service layer, obtain the character length of the target table name, and perform an upper limit threshold check on the character length to obtain the length check result; When the target layer is not the application data service layer, the length verification result is "verification passed". If the table name validation result, field validation result, and length validation result all pass, the naming convention validation result also passes.
[0008] Furthermore, the abstract syntax tree is subjected to structure specification verification based on the structure specification rules in the target rule subset to obtain the structure specification verification result, including: Extract table creation statement information from the abstract syntax tree. The table creation statement information includes one or more of the following: field definition information, partition field information, table attribute information, lifecycle configuration parameters, and primary key definition information. Based on the structural specification rules in the target rule subset, each item in the table creation statement information is checked for structural specification. The structural specification check includes the verification of field definition information comments, the verification of partition field information format, the verification of the integrity of table attribute information, the verification of the matching between lifecycle configuration parameters and the target level, and the verification of primary key configuration based on primary key definition information when the target level is the application data service layer or dimension layer. If the verification result of each item in the table creation statement is passed, the structure specification verification result is also passed.
[0009] Furthermore, based on the hierarchical compliance rules in the target rule subset, the abstract syntax tree is subjected to hierarchical compliance verification to obtain the hierarchical compliance verification results, including: Hierarchical feature information is extracted from the abstract syntax tree based on the target statement type. When the target statement type is a data definition type, the hierarchical feature information includes write operation keywords and SQL header comments. When the target statement type is a data operation type, the hierarchical feature information includes write operation keywords, date variables, SQL header comments, and aggregate function call information. Based on the hierarchical compliance rules in the target rule subset, each item in the hierarchical feature information is subjected to corresponding hierarchical compliance verification. The hierarchical compliance verification includes matching verification of the write operation keyword with the target level, format verification of date variables, integrity verification of SQL header comments, and function usage verification of aggregate function call information. If the verification result of each item in the hierarchical feature information is successful, the hierarchical compliance verification result is successful.
[0010] Furthermore, based on the data flow rules in the target rule subset, the abstract syntax tree is validated for data flow direction to obtain the data flow validation results, including: Retrieve all source table information from the abstract syntax tree; Construct a data dependency graph based on information from all source tables; The data flow verification results are obtained by performing data flow verification on the data dependency graph based on the data flow rules in the target rule subset. The data flow verification includes source table level legality verification, cross-product domain read legality verification, cross-level read annotation integrity verification, and circular dependency verification.
[0011] Furthermore, based on the verification results, the initial SQL is repaired and re-processed with short-circuit mode cascading verification, including: Obtain the list of issues from the verification results, classify each issue in the issue list according to a preset category, and obtain the corresponding repair category for each issue; Based on the repair category, perform the corresponding modification operation on the initial SQL to obtain the repaired first SQL; Re-execute the cascading verification process in short-circuit mode on the first SQL statement to obtain the re-verification result; If the re-validation result is successful, the first SQL will be output as the target SQL. If the re-verification result is a verification failure and the number of repair attempts has not reached the preset threshold, the first SQL will continue to be repaired until the verification passes or the number of repair attempts reaches the preset threshold, at which point a diagnostic report will be output.
[0012] In a second aspect, embodiments of this application provide a natural language-based SQL generation device, comprising: Memory and one or more processors; Memory, used to store one or more programs; When one or more programs are executed by one or more processors, the one or more processors implement the natural language-based SQL generation method as described in the first aspect.
[0013] In a third aspect, embodiments of this application provide a storage medium for storing computer-executable instructions, which, when executed by a computer processor, are used to perform the natural language-based SQL generation method as described in the first aspect.
[0014] This application embodiment receives requirement information in natural language form when generating SQL, and performs intelligent generation processing based on the requirement information and the artificial intelligence agent to obtain initial SQL; performs short-circuit cascading verification processing on the initial SQL to obtain verification results, wherein the cascading verification processing sequentially executes syntax verification, naming convention verification, structural convention verification, layer compliance verification, and data flow verification, and the short-circuit mode terminates the subsequent verification processing if any stage of verification fails; if the verification result is a verification failure, the initial SQL is repaired according to the verification result and short-circuit cascading verification processing is performed again. If the verification passes within a preset threshold number of repairs, the corresponding verified SQL is output as the target SQL, or if the verification still fails after the preset threshold number of repairs is reached, a diagnostic report is output; if the verification result is a verification pass, the initial SQL is output as the target SQL. By employing the aforementioned technical methods, a five-level progressive cascading verification system—encompassing syntax, naming conventions, structural standards, layered compliance, and data flow—is implemented, achieving comprehensive quality control from basic semantics to data warehouse framework design. This avoids the technical problem of low-quality SQL generated from natural language. This embodiment, through short-circuit mode cascading verification, effectively intercepts numerous hidden quality defects in SQL generated by AI agents, significantly improving the quality of the final target SQL. Furthermore, a closed-loop iterative mechanism of verification, repair, and re-verification automatically corrects problems and completes the entire verification process, achieving automatic convergence of SQL quality. This improves error location efficiency while ensuring the standardization, architectural consistency, and long-term maintainability of the output target SQL, further enhancing the quality of the final target SQL.
[0015] The beneficial effects of the natural language-based SQL generation device and storage medium provided above can be referenced in relation to the beneficial effects of the natural language-based SQL generation method. Attached Figure Description
[0016] Figure 1 This is a flowchart of a natural language-based SQL generation method provided in an embodiment of this application; Figure 2 This is a flowchart of another SQL generation method based on natural language provided in an embodiment of this application; Figure 3 This is a flowchart of another SQL generation method based on natural language provided in the embodiments of this application; Figure 4 This is a flowchart of another SQL generation method based on natural language provided in an embodiment of this application; Figure 5 This is a schematic diagram of the structure of a natural language-based SQL generation device provided in an embodiment of this application. Detailed Implementation
[0017] To make the objectives, technical solutions, and advantages of this application clearer, specific embodiments of this application will be described in further detail below with reference to the accompanying drawings. It should be understood that the specific embodiments described herein are merely for explaining this application and not for limiting it. It should also be noted that, for ease of description, only the parts relevant to this application are shown in the drawings, not all of them. Before discussing exemplary embodiments in more detail, it should be mentioned that some exemplary embodiments are described as processes or methods depicted as flowcharts. Although the flowcharts describe operations (or steps) as sequential processes, many of these operations can be performed in parallel, concurrently, or simultaneously. Furthermore, the order of the operations can be rearranged. The process can be terminated when its operation is completed, but may also have additional steps not included in the drawings. The process can correspond to a method, function, procedure, subroutine, subprogram, etc.
[0018] Existing AI-generated SQL commonly suffers from quality issues such as syntax errors, non-standard naming, and incomplete structure, which significantly reduce data development efficiency and data reliability. To address these problems, existing technologies primarily employ three solutions: The first is a semantic integrity constraint scheme, extending traditional database integrity constraints to semantic query scenarios. It supports three types of constraints: grounding, correctness, and exclusion, and uses both post-validation and proactive constraint execution strategies. However, its constraint objects are limited to query output results, not involving the standardization validation of table creation statements and data processing task statements. Furthermore, it lacks warehouse layer awareness capabilities, and constraint rules must be defined manually one by one, not automatically generated from existing specification documents. The second solution is a general large-scale model output security framework, which defines output format constraints and content validation rules... The first approach is to perform text-level validation on the model output, but it lacks sentence parsing and semantic understanding capabilities, data warehouse domain knowledge, and awareness of hierarchical specifications. When validation fails, it only supports rejection or retry, and does not support automatic diagnosis, location, and repair. The second approach is a constrained statement generation method, which is based on retrieval-enhanced generation technology and improves the generation quality through structure retrieval and example injection. However, it is geared towards generating query statements for specific vertical domains and does not involve table building and data processing scenarios in multi-level data warehouse systems. The scope of constraints is limited to structure matching and does not cover data warehouse (i.e., data warehouse) specific constraints such as naming conventions, lifecycle configuration, and data flow.
[0019] The aforementioned existing technologies are all single-point solutions. When using only post-event validation, the model generates statements freely under unconstrained conditions, resulting in uncontrollable generation quality, a high validation failure rate, and the inability to pinpoint and fix root causes after detecting problems. Secondly, when using only pre-event constraints, although a reference context is provided, the model may still ignore constraints within that context and cannot cover boundary conditions requiring a global view for validation. Alternatively, when using only automatic repair, the lack of a layered validation chain for problem location leads to uncertain repair directions, potentially worsening the problem. Therefore, existing intelligent SQL generation methods generally produce SQL with quality issues such as syntax errors, non-standard naming, or incomplete structure. Furthermore, existing solutions only reject or issue alerts after detecting problems, significantly reducing data development efficiency and data reliability. Consequently, existing intelligent SQL generation methods generally have poor generation quality.
[0020] Based on this, the present application provides a natural language-based SQL generation method, device, and storage medium, which aims to receive demand information in natural language form when generating SQL, and perform intelligent generation processing based on the demand information and artificial intelligence agent to obtain initial SQL; perform short-circuit mode cascading verification processing on the initial SQL to obtain verification results, wherein the cascading verification processing sequentially executes syntax verification, naming convention verification, structural convention verification, layer compliance verification, and data flow verification, and the short-circuit mode terminates the subsequent verification processing when any stage of verification fails; if the verification result is a verification failure, the initial SQL is repaired according to the verification result and the short-circuit mode cascading verification processing is performed again. If the verification passes within a preset threshold number of repairs, the corresponding verified SQL is output as the target SQL, or if the verification still fails even after the preset threshold number of repairs is reached, a diagnostic report is output; if the verification result is a verification pass, the initial SQL is output as the target SQL. By employing the aforementioned technical means, and through a five-level progressive cascading verification system encompassing syntax, naming conventions, structural specifications, layered compliance, and data flow, comprehensive quality control is achieved, extending from basic semantics to data warehouse framework design. Compared to existing post-event detection and alerting methods, this embodiment, through short-circuit mode cascading verification, can effectively intercept a large number of hidden quality defects in SQL generated by AI agents, significantly improving the quality of the final target SQL. Furthermore, a closed-loop iterative mechanism of verification, repair, and re-verification automatically corrects problems and completes the entire process verification, achieving automatic convergence of SQL quality. Compared to existing methods that merely reject or alert after detecting problems, this embodiment improves error location efficiency while ensuring the standardization, architectural consistency, and long-term maintainability of the output target SQL, further enhancing the quality of the final target SQL.
[0021] Figure 1 This is a flowchart illustrating a natural language-based SQL generation method provided in this application embodiment. The natural language-based SQL generation method provided in this embodiment can be executed by a natural language-based SQL generation device. This device can be implemented through software and / or hardware. The natural language-based SQL generation device can consist of two or more physical entities, or it can consist of a single physical entity. Generally, the natural language-based SQL generation device can be an electronic device, such as a PC (personal computer), interactive whiteboard, tablet computer, smart TV, learning machine, or mobile phone.
[0022] The following description uses a PC as the primary entity executing a natural language-based SQL generation method. (Refer to...) Figure 1 The natural language-based SQL generation method specifically includes: S11. Receive the requirement information in natural language form, and intelligently generate the initial SQL based on the requirement information and the artificial intelligence agent.
[0023] In one embodiment, during intelligent SQL generation, the system first receives requirement information in natural language form and then performs intelligent generation processing based on this requirement information and the AI agent to obtain initial SQL. The natural language requirement information refers to the user's requirements expressed in everyday Chinese, including requests for data querying, data processing, or table structure creation. The AI agent is an AI Agent (Artificial Intelligence Agent, an intelligent agent based on a large language model). The AI agent is a code generation agent based on a large language model that, after receiving the natural language requirement information, outputs a structured query language statement, i.e., SQL, through context understanding and code generation capabilities. Intelligent generation processing refers to the process of receiving the user's data development requirements expressed in natural language form and converting the natural language into SQL (Structured Query Language) through the AI agent. For example, if the user inputs the requirement information "Create a user login event table in the DWD layer (Data Warehouse Detail) based on the user login log table in the OWS (Operational Data Store) layer," the system sends this requirement information to the AI agent, which generates initial SQL based on its pre-trained knowledge and the received requirement information.
[0024] As described above, the initial SQL is generated through intelligent processing, realizing the automatic conversion from natural language to machine-executable statements. This lowers the technical threshold for data development, enabling non-professional developers to complete the initial construction of data development tasks through natural language descriptions. Compared with the traditional manual SQL writing mode, this embodiment can quickly generate basic initial SQL, significantly improving the efficiency of data development.
[0025] S12. Perform short-circuit mode cascading verification on the initial SQL to obtain the verification result. The cascading verification process involves sequentially executing syntax verification, naming convention verification, structural convention verification, hierarchical compliance verification, and data flow verification. In short-circuit mode, the subsequent verification process is terminated if any stage of verification fails.
[0026] After generating the initial SQL in S11, a short-circuit cascading validation process is performed on the initial SQL to obtain the validation result. This cascading validation process sequentially executes syntax validation, naming convention validation, structural convention validation, hierarchical compliance validation, and data flow validation. The short-circuit mode terminates subsequent validation processes if any stage of validation fails, avoiding invalid validation based on already determined non-compliance. Cascading validation can be understood as a mechanism that executes multi-stage (or multi-level) validations sequentially according to a preset dependency order, with the validation result of the previous stage providing the basis for the next stage. The cascading validation provided in this embodiment includes five stages in sequence: syntax validation, naming convention validation, structural convention validation, hierarchical compliance validation, and data flow validation. The validation process includes several key components: syntax validation, naming convention validation, structural validation, layer compliance validation, and data flow validation. Syntax validation verifies whether the syntax of the statement conforms to database execution standards; naming convention validation verifies whether identifiers such as table names and field names conform to preset naming rules; structural convention validation verifies whether the structural elements of the table creation statement, such as field definitions, partition settings, and table attributes, are complete and compliant; layer compliance validation verifies whether the statement conforms to the development constraints of the corresponding layer in the data warehouse; and data flow validation verifies whether the dependencies between source tables in the statement conform to the data warehouse architecture's flow control rules. For example, during cascading validation, syntax validation is performed first, parsing the initial SQL into an abstract syntax tree. If parsing fails, the syntax validation is directly deemed to have failed, triggering a short-circuit mode to terminate all subsequent validation steps; if parsing succeeds, the syntax validation is deemed to have passed. If the syntax check passes, the naming convention check is executed, extracting all table names and field names from the statement and matching them against the preset naming rule library. If the check fails, the short-circuit mode is triggered to terminate the subsequent structure specification check, layer compliance check, and data flow check. If the naming convention check passes, the structure specification check, layer compliance check, and data flow check are executed sequentially. Each check passes before proceeding to the next check. If any check fails, the subsequent check process is immediately terminated. Finally, the check results for the corresponding check stage are output.
[0027] In one embodiment, a five-stage cascading validation is initiated on the initial SQL generated in S11. First, a syntax check is performed, parsing the statement text into an abstract syntax tree (i.e., a tree-like representation of the syntax structure) to check if it conforms to the basic syntax rules of the SQL language. If the syntax check passes, a naming convention check is performed. For example, the naming convention check can first identify the target table name as "Data Details Layer_User Login," and check the prefix and suffix according to the naming rules of the data details layer. If an error is found in the prefix or suffix, the naming convention check fails, triggering a short-circuit mode and terminating the execution of subsequent layered compliance checks and data flow checks. For instance, during the naming convention check, it is found that the table name lacks a valid suffix. For example, data details layer table names must end with one of six suffixes: "Event," "Session," "Lifecycle," "Snapshot," "Details," and "History." Because the table name lacks a valid suffix, it is determined to be a serious error, triggering a short-circuit mode and terminating the execution of subsequent layered compliance checks and data flow checks. The output check result includes a problem description: "Naming rule violation: Table name lacks a valid suffix." After naming convention validation, if the naming convention validation passes, structural convention validation is performed. If the structural convention validation fails, the subsequent layered compliance validation and data flow validation are terminated. If the structural convention validation passes, layered compliance validation is performed. If the layered compliance validation fails, the subsequent data flow validation is terminated; if the layered compliance validation passes, data flow validation is performed. If the data flow validation passes, the overall validation result is "valid." If any of the aforementioned syntax validation, naming convention validation, structural convention validation, layered compliance validation, and data flow validation fails, the overall validation result is "validation failed."
[0028] The above-mentioned design, through syntax, naming conventions, structural conventions, layered compliance, and the dependency order of data flow, ensures that low-level problems (such as naming errors) will not lead to a chain of false alarms in high-level checks. At the same time, the short-circuit mechanism avoids consuming unnecessary computing resources on statements that have been determined to be unqualified, thus significantly improving the efficiency of checks.
[0029] It should be noted that the short-circuit mode operates between each verification stage, and within each stage, all sub-item issues of that stage can continue to be collected in order to provide a complete list of issues in a single round of repair.
[0030] S13. If the verification result is a verification failure, repair the initial SQL according to the verification result and re-perform the cascading verification process in short-circuit mode. If the verification passes within the preset threshold number of repairs, the corresponding SQL that passes the verification will be output as the target SQL. If the verification still fails after the number of repairs reaches the preset threshold, a diagnostic report will be output.
[0031] After obtaining the verification result through the short-circuit mode cascading verification process in S12, if the verification result is a failure, the initial SQL is repaired based on the verification result, and the short-circuit mode cascading verification process is repeated. If the verification passes within a preset threshold number of repair attempts, the corresponding successful SQL is output as the target SQL; otherwise, if the verification fails even after reaching the preset threshold number of repair attempts, a diagnostic report is output. Here, repair processing refers to the process of automatically diagnosing the root cause of the problem and generating a repair plan based on the verification report when the verification fails. The preset threshold refers to the maximum number of repair attempts allowed by the system, which is set to three in this embodiment. The diagnostic report is a document summarizing all failed verification issues, their locations, and repair suggestions. For example, after obtaining the verification result in S12, if the verification result is a failure, the system first extracts the specific problem items recorded in the verification result, matches the corresponding repair logic to different types of specific problem items, and performs the corresponding repair processing on the initial SQL, such as correcting syntax spelling errors, adjusting the naming format of identifiers, or completing missing table attribute configurations. After completing the repair operation, the repaired SQL is re-executed with the complete short-circuit mode cascading verification process to obtain the re-verification result. If the re-verification passes, the repaired and verified SQL will be output as the target SQL. If the re-verification still fails, and the cumulative number of repairs has not yet reached the preset threshold, the next round of repair and re-verification will continue based on the latest verification result until the verification passes or the cumulative number of repairs reaches the preset threshold. When the number of repairs reaches the preset threshold (e.g., three times), if the corresponding SQL still fails verification, automatic repair will stop, and a diagnostic report containing all problem details and optimization suggestions will be output for manual intervention and targeted processing.
[0032] In one embodiment, after obtaining the verification result in S12, if the verification result is a failure, the first round of repair processing is initiated. First, the problem is diagnosed and classified; for example, the problem is that the table name lacks a valid suffix. Since the missing suffix is a semantically understandable issue, the login behavior needs to be modeled as an "event" based on business semantics. Therefore, the error context, data detail layer naming rules, and related table structures can be sent to the large language model, which recommends the suffix "event," thus automatically correcting the table name to "Data Detail Layer_User Login_Event." Subsequently, the "User" field is standardized and mapped to "User Identifier" according to the field mapping rules. Simultaneously, missing lifecycle table attribute parameters are supplemented, i.e., lifecycle management is enabled, for example, setting the expiration time to 360 days. Finally, the large language model generates Chinese annotations for the four fields. After the repair is completed, a complete five-stage short-circuit mode cascading verification can be re-executed. If the syntax verification, naming convention verification, structural convention verification, layered compliance verification, and data flow verification all pass, the overall verification result becomes "verification passed."
[0033] It should be noted that if the verification still fails after the first round of modifications, a second round of modifications will be performed. If the verification still fails after the second round of modifications, a third round of modifications will be performed. If the verification still fails after the third round of modifications, the number of repair attempts has reached a preset threshold, and a diagnostic report will be output for manual modification.
[0034] The above-mentioned closed-loop mechanism of automatic repair and re-verification can automatically correct most common compliance issues and generate compliant statements without manual intervention, significantly improving overall development efficiency. In addition, setting a threshold for the number of repairs can prevent the system from falling into an invalid loop, and the output of diagnostic reports can provide clear problem guidance for manual processing, ensuring automation efficiency while maintaining the final quality bottom line.
[0035] S14. If the verification result is successful, output the initial SQL as the target SQL.
[0036] After obtaining the verification result in S12, if the verification result is successful, the initial SQL is output as the target SQL. For example, when the verification result is successful, the target statement output step is executed. This step is the normal exit point of the entire cascading verification process, indicating that the initial statement has passed all levels of verification and meets all quality requirements. For example, when the initial SQL passes all verifications sequentially—syntax verification, naming convention verification, structural convention verification, layered compliance verification, and data flow verification—and the final verification result is successful, it can be determined that the initial SQL conforms to the various development specifications and architectural requirements of the preset data warehouse. No additional repair processing is required, and the initial SQL is directly output as the target SQL for subsequent data development or task scheduling processes. In this embodiment, for generated statements (i.e., initial SQL) that meet the quality standards and comply with all specifications, direct output without additional processing ensures the efficiency of generating compliant statements. It also ensures, through a mechanism, that all final output statements have undergone complete, multi-dimensional verification, significantly improving the quality of the final output target SQL.
[0037] As described above, when generating SQL based on natural language, the system receives requirement information in natural language form and performs intelligent generation processing based on the requirement information and the AI agent to obtain initial SQL. The initial SQL undergoes short-circuit cascading verification to obtain verification results. This cascading verification process sequentially executes syntax verification, naming convention verification, structural convention verification, layered compliance verification, and data flow verification. In short-circuit mode, subsequent verification processes are terminated if any stage of verification fails. If the verification result is a failure, the initial SQL is repaired based on the verification result, and short-circuit cascading verification is performed again. If the verification passes within a preset threshold of repair attempts, the corresponding successful SQL is output as the target SQL. Alternatively, if verification fails even after reaching the preset threshold of repair attempts, a diagnostic report is output. If the verification result is a pass, the initial SQL is output as the target SQL. By employing the aforementioned technical means, and through a five-level progressive cascading verification system encompassing syntax, naming conventions, structural specifications, layered compliance, and data flow, comprehensive quality control is achieved, extending from basic semantics to data warehouse framework design. Compared to existing post-event detection and alerting methods, this embodiment, through short-circuit mode cascading verification, can effectively intercept a large number of hidden quality defects in SQL generated by AI agents, significantly improving the quality of the final target SQL. Furthermore, a closed-loop iterative mechanism of verification, repair, and re-verification automatically corrects problems and completes the entire process verification, achieving automatic convergence of SQL quality. Compared to existing methods that merely reject or alert after detecting problems, this embodiment improves error location efficiency while ensuring the standardization, architectural consistency, and long-term maintainability of the output target SQL, further enhancing the quality of the final target SQL.
[0038] In one embodiment, this application provides a natural language-based SQL generation method, specifically including: receiving requirement information in natural language form, and performing intelligent generation processing based on the requirement information and an artificial intelligence agent to obtain initial SQL. The initial SQL undergoes short-circuit mode cascading verification processing to obtain a verification result. The cascading verification processing sequentially executes syntax verification, naming convention verification, structural convention verification, layered compliance verification, and data flow verification. In short-circuit mode, subsequent verification processing is terminated if any stage of verification fails. If the verification result is a failure, the initial SQL is repaired based on the verification result, and short-circuit mode cascading verification processing is performed again. If the verification passes within a preset threshold number of repair attempts, the corresponding verified SQL is output as the target SQL; otherwise, if verification fails even after the preset threshold number of repair attempts is reached, a diagnostic report is output. If the verification result is a pass, the initial SQL is output as the target SQL. The natural language-based SQL generation method provided in this application can be applied to data warehouses and other SQL generation application scenarios, such as business intelligence query systems, software development code generation, database migration script generation, cloud resource configuration file generation, financial report structured data generation, medical data standardization, and IoT device instruction generation.
[0039] Figure 2 This is a flowchart of another SQL generation method based on natural language provided in the embodiments of this application, referred to... Figure 2 In the cascading validation process of short-circuit mode, this natural language-based SQL generation method specifically includes: S121. Perform hierarchical recognition processing on the initial SQL to obtain the target level of the initial SQL in the preset data warehouse hierarchy. The hierarchical recognition processing adopts a three-level cascaded recognition strategy of table name prefix matching, database name matching, and SQL statement feature inference. The data warehouse hierarchy includes the operational data storage layer, data detail layer, data summary layer, application data service layer, and dimension layer.
[0040] After obtaining the initial SQL in S11, a hierarchical identification process is performed on the initial SQL to determine its target level within the preset data warehouse hierarchy. Hierarchical identification refers to the process of automatically determining the data warehouse level to which the structured query language (i.e., the initial SQL) belongs through a preset multi-level matching strategy. The data warehouse hierarchy includes the ODS (Operational Data Store), DWD (Data WarehouseDetail), DWS (Data Warehouse Service), ADS (Application Data Service), and DIM (Dimension). The three-level cascading identification strategy refers to the execution of three identification methods sequentially from highest to lowest priority; if the current level cannot be determined, it automatically degrades to the next lower level. For example, during the hierarchical identification process, the first level of table name prefix matching is initiated. This extracts the table name prefix of the target table in the statement (i.e., the initial SQL) and matches it one by one with the standard prefix mapping tables for each level in the preset data warehouse hierarchy. For instance, if a matching table name prefix is found, the target level is directly determined to be the data detail layer, and this level of identification is complete. If the table name prefix cannot be effectively matched, the second level of database name matching is initiated. This extracts the database name to which the table belongs in the statement (i.e., the initial SQL) and matches it with the preset hierarchical and database mapping relationship. For instance, if a matching database is found to be a dedicated database for the data aggregation layer, the target level is directly determined to be the data aggregation layer. If the database name still cannot be matched, the third level of SQL statement feature inference is initiated. This involves comprehensively judging the statement by analyzing its syntax features, such as the use of aggregate functions, write operation types, and partition field configurations. For example, if the statement contains multi-dimensional aggregation logic, it is determined to be the data aggregation layer, and the accurate target level result is finally output.
[0041] In one embodiment, assume the initial SQL generated based on the AI agent is "CREATE TABLE dwd_db.dwd_user_login(user STRING,logtime BIGINT,platform STRING,ip STRING)USINGiceberg PARTITIONED BY(dt STRING)TBLPROPERTIES ('format-version'='2')". During hierarchical identification, the first level of table name prefix matching is initiated, extracting the target table name "dwd_user_login" from the table creation statement. The prefix "dwd_" is found to conform to the naming convention of the data detail layer, thus directly identifying the target layer as the data detail layer. If the table name prefix is ambiguous or missing, the second level of database name matching is initiated, checking if the qualified database name "dwd_db" contains the identifier "dwd", which also identifies it as the data detail layer. If the first two levels fail to identify the data, the process proceeds to the third level, SQL statement feature inference. Based on the "PARTITIONED BY" partition definition and the "INSERT OVERWRITE" write characteristics, the data is inferred to be either a data detail layer or a data summary layer. It should be noted that in the aforementioned embodiment, if the table name prefix matching at the first level is successful, then no further downgrade is necessary.
[0042] The above-mentioned three-level cascaded identification strategy achieves automatic and accurate identification of levels, avoiding the tedious operation of manually specifying levels. At the same time, it ensures that even if the table name prefix is missing or the database name is not standardized, the target level can still be inferred through the characteristics of the SQL statement, which significantly improves the robustness and coverage of level identification.
[0043] S122. Perform statement type identification on the initial SQL to obtain the target statement type, where the target statement type includes data definition type and data operation type.
[0044] After generating the initial SQL in S11, the initial SQL undergoes statement type identification to determine the target statement type. Statement type identification refers to analyzing the syntax of the SQL (Structured Query Language) to determine whether it belongs to DDL (Data Definition Language) or DML (Data Manipulation Language). Data definition types refer to statement types used to create, modify, or delete table structures and other data objects, while data manipulation types refer to statement types used to write, query, or process data in a table. For example, during statement type identification, the first keyword of the statement (i.e., the initial SQL) is extracted and matched with core operators. If the first keyword of the statement (i.e., the initial SQL) is a keyword related to structural operations such as creating, modifying, or deleting a table, it is determined to be a data definition type; if the first keyword of the statement (i.e., the initial SQL) is a keyword related to data processing such as data insertion, data query, or data update, it is determined to be a data manipulation type. The corresponding target statement type can be obtained through the aforementioned statement type identification.
[0045] In one embodiment, the initial SQL is parsed to identify that the statement begins with the keyword "CREATE TABLE", which is a table creation statement. Therefore, the target statement type is determined to be a data definition type. If the statement begins with "INSERTOVERWRITE" or "MERGE INTO", it is determined to be a data operation type.
[0046] As mentioned above, since the validation rules applicable to data definition types and data operation types are significantly different, for example, data definition types need to validate the integrity of the table structure and lifecycle configuration, while data operation types need to validate the compliance of the writing method and the legality of the data flow. Therefore, accurate statement type identification provides a key filtering dimension for subsequent rule loading, and type differentiation effectively avoids invalid validation caused by rule misloading, significantly improving the targeting and accuracy of subsequent validation processes.
[0047] S123. Based on the target level and target statement type, load a subset of target rules from the preset constraint rule set, wherein the subset of target rules includes public rules and level-specific rules.
[0048] After determining the target level in S121 and obtaining the target statement type in S122, a subset of target rules is loaded from a preset constraint rule set. This subset includes common rules and level-specific rules. The constraint rule set refers to the pre-configured full set of all validation rules covering all data warehouse levels and statement types. The target rule subset refers to the set of validation rules selected from the full rule set that applies only to the current target level and statement type. Common rules are general validation rules that must be followed by all levels and statement types, while level-specific rules are customized validation rules that only apply to a specific data warehouse level. During execution, using the previously obtained target level and statement type as filtering conditions, common rules applicable to all scenarios are first filtered from the full constraint rule set. Then, level-specific rules corresponding to the target level are filtered, while rule items not applicable to the current target statement type are removed. Finally, all the filtered rules are combined into a target rule subset, which is loaded into the validation execution engine for invocation.
[0049] In one embodiment, assuming that the target level is identified as the data detail layer in S121 and the target statement type is identified in S122 as the data definition type, rules are filtered from a preset set of constraint rules. First, 23 common rules are loaded, covering general constraints such as serpentine naming checks in naming conventions and field comment integrity checks in structural specifications. Then, 28 data detail layer-specific rules are loaded, including naming rules requiring table names to end with one of "Event," "Session," "Lifecycle," "Snapshot," "Detail," or "History," structural rules requiring a lifecycle configuration of 360 days, and field standardization mapping rules. Since the statement type is a data definition type, rules are further filtered to identify target objects as table names, field names, field comments, and table attributes, ultimately forming a target rule subset containing 51 rules.
[0050] As described above, by using the method of loading a subset of target rules on demand, it is not necessary to load the full set of rules for each verification. This reduces memory usage and computational overhead for rule matching, improves verification execution efficiency, avoids interference from irrelevant rules, and ensures the adaptability of verification results to the current business scenario.
[0051] S124. Perform short-circuit mode cascading verification on the initial SQL according to the constraint rules in the target rule subset to obtain the verification result.
[0052] After loading the target rule subset in S123, the initial SQL is subjected to short-circuit cascading validation based on the constraint rules in the target rule subset to obtain the validation result. The short-circuit cascading validation process refers to executing multiple validation stages sequentially in a preset order. If any stage fails, all subsequent validation processes are immediately terminated. This validation process sequentially includes five stages: syntax validation, naming convention validation, structural convention validation, hierarchical compliance validation, and data flow validation. For example, during the cascading validation process, based on the loaded target rule subset, each validation stage can be started sequentially in a preset progressive order. First, syntax validation is executed, parsing the initial SQL (i.e., the structured query statement) into an abstract syntax tree. If parsing fails, the validation is directly deemed a failure, and all subsequent validation processes are terminated; if parsing succeeds, the syntax validation passes. If the syntax validation passes, naming convention validation, structural convention validation, hierarchical compliance validation, and data flow validation are executed sequentially. Each stage passes before proceeding to the next stage. If any stage fails, all subsequent validation steps are immediately terminated, and the validation result for the corresponding stage is finally output.
[0053] For example, using the target rule subset loaded by S123, the initial SQL is processed through cascading validation in short-circuit mode. First, syntax validation is performed, parsing the statement (i.e., the initial SQL) into an abstract syntax tree to check bracket matching and keyword validity. If parsing is successful, the syntax validation passes. Next, naming convention validation is performed, calling the "data detail layer table name suffix validation" rule in the target rule subset. The table name "dwd_user_login" is found to lack a valid suffix, triggering a critical error. According to the short-circuit mode mechanism, subsequent validations are immediately terminated, and the output validation result includes a description of the problem: "Naming convention violation: table name lacks a valid suffix; expected suffix is one of event, session, lifecycle, snapshot, detail, or history." If the naming convention validation passes, the process continues with structural compliance validation (checking the completeness of field comments, partition field format, and table attribute completeness), hierarchical compliance validation (checking the write method and hierarchical matching), and data flow validation (checking the validity of the source table hierarchy).
[0054] The above-mentioned combination of cascading verification based on dependency relationships with short-circuit execution mechanism can comprehensively cover quality issues from basic syntax to architectural design, effectively intercept hidden defects such as syntactically correct but architecturally non-compliant ones, and terminate invalid calculations in a timely manner when basic errors are detected, avoiding chain false alarms caused by basic errors, making error location more accurate and significantly improving verification efficiency.
[0055] Figure 3 This is a flowchart of another SQL generation method based on natural language provided in the embodiments of this application, referred to... Figure 3When performing cascading validation in short-circuit mode, this natural language-based SQL generation method specifically includes: S1240. Parse the initial SQL into an abstract syntax tree. If the parsing is successful, the syntax verification passes. If the parsing fails, the verification result of the cascading verification process is verification failure.
[0056] During the cascading validation process in short-circuit mode (S12 or S124) mentioned above, syntax validation is performed first. First, the initial SQL is parsed into an abstract syntax tree (AST). If parsing is successful, the syntax validation passes; if parsing fails, the syntax validation fails, meaning the cascading validation process fails. The abstract syntax tree is a tree-like structured representation of a structured query statement broken down according to syntax rules. Each node represents a syntactic unit in the statement, such as table creation statements, field definitions, and table attribute configurations, facilitating subsequent structured analysis and precise positioning by the rule engine. Syntax validation is the verification step that checks whether the statement conforms to the database standard syntax specification; it is the foundation of all validation steps. For example, the database's syntax parser can be invoked. The initial SQL (structured query statement) is input into the parser for parsing, attempting to generate the corresponding abstract syntax tree. If parsing is successful and a complete abstract syntax tree is generated, the syntax validation is considered successful, and subsequent validation steps can continue. If parsing fails, it indicates that the statement has basic grammatical problems such as misspelled keywords, incomplete grammatical structure, or mismatched symbols. In this case, the entire cascading validation process is judged to have failed, and no further validation steps will be executed.
[0057] In one embodiment, the initial SQL (Structured Query Language) is input into the parser, which attempts to transform it into an abstract syntax tree. The parser scans the statement word by word for keywords, identifiers, parentheses, commas, and other syntactic elements. If the statement is identified as a table creation statement, it includes a table name node "dwd_user_login", a field list node (containing four field definitions), a partition definition node, and a table attribute node. If the parentheses are matched completely and the keywords are valid, the parsing is successful, the syntax validation passes, and the resulting abstract syntax tree serves as the base input for subsequent validation stages. If mismatched parentheses or misspelled keywords are found during parsing, the parsing fails, the cascading validation process terminates directly, and a validation failure result is output.
[0058] As described above, by using syntax verification as the first checkpoint in cascading verification, it can immediately intercept completely unusable erroneous statements, ensuring that all subsequent verification steps are based on grammatically correct statements. This avoids invalid verification at its source and quickly locates the most basic syntax problems, significantly improving the efficiency of problem diagnosis.
[0059] S1241. If the syntax check passes, perform a naming convention check on the abstract syntax tree according to the naming convention rules in the target rule subset to obtain the naming convention check result.
[0060] After obtaining the syntax verification result in S1240, if the syntax verification passes, the abstract syntax tree is subjected to naming convention verification according to the naming convention rules in the target rule subset to obtain the naming convention verification result. Naming convention verification refers to the process of checking whether identifiers such as table names and field names in the statement conform to the preset data warehouse naming conventions according to the naming rules corresponding to the target level. Naming convention rules are a set of rules in the constraint rule set that specify requirements for prefixes, suffixes, and naming styles of identifier formats. For example, assuming the syntax verification passes, all identifiers are extracted from the generated abstract syntax tree, including the target table name, source table name, and all field names. Then, each identifier is verified against the naming convention rules in the target rule subset, sequentially verifying whether the table name prefix matches the data warehouse level, whether the table name suffix conforms to the corresponding scenario's specifications, whether the field names use a uniform serpentine naming format, and whether any database reserved words have been misused. If all identifiers meet the specification requirements, the naming convention verification is considered passed, and the process continues to the next verification stage. If any identifier fails to conform to the specification, the naming convention verification is deemed a failure, immediately triggering a short-circuit mechanism to terminate subsequent structural specification verification, hierarchical compliance verification, and data flow verification, directly outputting the result of cascading verification failure. This embodiment further standardizes statement naming on the basis of syntactic correctness, resolving the issue of inconsistent naming styles when AI-generated statements, and improving SQL readability and maintainability. Simultaneously, relying on the short-circuit mechanism, subsequent verifications are stopped promptly upon detecting naming issues, reducing system computational overhead and allowing users to focus on resolving fundamental naming problems first, avoiding interference from subsequent error messages.
[0061] In one embodiment, when performing naming convention verification, the target identifier can be extracted from the parsed abstract syntax tree, where the target identifier includes the target table name and field names. The prefix and suffix of the target table name are obtained, and a matching verification is performed based on the prefix against the target level, and a validity verification is performed based on the suffix against a preset set of valid suffixes corresponding to the target level, resulting in a table name verification result. Each field name in the target identifier undergoes serpentine naming format verification and database reserved word verification, resulting in a field verification result. If the target level is the application data service layer, the character length of the target table name is obtained, and an upper limit threshold verification is performed on the character length to obtain a length verification result. If the target level is not the application data service layer, the length verification result is considered passed. If the table name verification result, field verification result, and length verification result are all passed, the naming convention verification result is considered passed.
[0062] In one embodiment, the abstract syntax tree obtained from the syntax verification stage is first traversed to extract target identifiers. The target identifiers include target table names and field names. For example, the extracted target table name is "dwd_user_login", and the field name set contains four elements: "user", "logtime", "platform", and "ip". These target identifiers are the basic objects for subsequent naming rule verification. After extracting the target identifier, the prefix "dwd_" and suffix "login" of the target table name "dwd_user_login" are obtained. The prefix of the target table name is matched and validated against the target level. Since the target level has been identified as the data detail level, and the prefix "dwd_" is the standard level identifier for the data detail level, the prefix matching validation passes. At the same time, the suffix of the target table name is validated against the preset set of valid suffixes corresponding to the target level. The set of valid suffixes corresponding to the data detail level includes six types: "event", "session", "lifecycle", "snapshot", "details", and "history". The suffix "login" is not in this set, so the suffix validity validation fails, and the table name validation result is validation failure. While performing prefix and suffix validation on the target table name, each field name in the target identifier also underwent snake-shaped naming format validation and database reserved word validation. The snake-shaped naming format validation requires field names to consist of lowercase letters, numbers, and underscores and not begin with a number. "user", "logtime", "platform", and "ip" all conformed to this format, therefore the snake-shaped naming format validation passed. The database reserved word validation checked whether the field names were Structured Query Language keywords or system reserved words. All four field names were found to be non-reserved words, therefore the database reserved word validation passed. Combining the results of these two sub-validations, the field validation result was determined to be valid. Furthermore, since the target layer is the data detail layer rather than the application data service layer, the character length upper limit threshold validation was skipped, and the length validation result was directly determined to be valid. This is because the application data service layer has a special restriction on table name length not exceeding fifty characters, while other layers do not have this constraint. Finally, the system makes a judgment based on the combined results of table name verification, field verification, and length verification. Since the table name verification result is a failure, even though the field verification and length verification results are both successful, the naming convention verification result is still a failure. This result triggers the short-circuit mode, causing the cascading verification process to terminate at the naming convention stage and no longer proceed to the subsequent structure convention verification.In this embodiment, prefix matching enables secondary confirmation of the target level, preventing subsequent rule misloading due to incorrect level identification. Furthermore, suffix validity checks ensure that table names accurately reflect the data modeling type; for example, "event" represents a single event record, and "session" represents multi-stage state accumulation with start and end times, allowing users (such as data developers) to intuitively understand the data semantics through table names. Moreover, snake-shaped naming format checks and database reserved word checks unify field naming styles and avoid conflicts with database system keywords, improving the readability and portability of the code (i.e., SQL). Additionally, special length limit checks at the application data service layer prevent downstream system compatibility issues caused by excessively long table names, such as relational database table name length limits and report system field truncation. This automated multi-dimensional naming specification verification transforms naming checks, originally reliant on manual code review, into a standardized process executed automatically by machines. This significantly reduces manual review costs while ensuring naming compliance, thereby significantly improving verification efficiency.
[0063] S1242. If the naming convention verification result is successful, perform a structure convention verification on the abstract syntax tree according to the structure convention rules in the target rule subset to obtain the structure convention verification result.
[0064] After obtaining the naming convention verification result in S1241, if the naming convention verification result is successful, the abstract syntax tree is subjected to structure convention verification according to the structure convention rules in the target rule subset to obtain the structure convention verification result. Structure convention verification refers to the process of checking whether the table structure elements in the data definition type statement are complete and compliant, including the completeness of field comments, the correctness of partition field format, the completeness of table attribute parameters, and the correctness of lifecycle configuration. Structure convention rules are a set of rules in the constraint rule set that specify the existence and format requirements of table structure elements. For example, assuming the naming convention verification is successful, all structure element information corresponding to the table creation statement is extracted from the abstract syntax tree, including field definition content, partition field settings, table attribute configuration, lifecycle parameters, and primary key definitions. Then, each item is verified against the structure convention rules in the target rule subset. For example, it verifies whether all fields have added business comments, whether the format and hierarchy of partition fields match, whether the basic table attributes are configured completely, whether the lifecycle duration meets the management requirements of the current level, and whether the tables in the application data service layer and dimension layer are configured with primary keys. If all structural elements meet the specifications, the structural specification verification is considered passed, and the process proceeds to the next verification stage. If any structural element fails to meet the specifications, the structural specification verification is considered to have failed, triggering a short-circuit mechanism to terminate subsequent layered compliance verification and data flow verification, directly outputting the result of cascading verification failure. This embodiment ensures the rationality and completeness of statements at the table structure design level, preventing design flaws such as missing comments, unreasonable partitioning design, and incorrect lifecycle configuration from entering the production environment, thus reducing the cost of subsequent data governance and performance optimization. Furthermore, the application of the short-circuit mechanism avoids invalid calculations and false alarms caused by continuing subsequent verifications when structural defects exist, thereby improving verification efficiency.
[0065] In one embodiment, during structural specification verification, table creation statement information can be extracted from the abstract syntax tree. This information includes one or more of the following: field definition information, partition field information, table attribute information, lifecycle configuration parameters, and primary key definition information. Each item in the table creation statement information is then verified for structural specification according to the structural specification rules in the target rule subset. This verification includes checking the comments of field definition information, the format of partition field information, the completeness of table attribute information, lifecycle configuration parameters, the matching of the target level, and, when the target level is the application data service layer or dimension layer, the primary key configuration based on the primary key definition information. If the verification result for each item in the table creation statement information passes, the structural specification verification result is considered passed.
[0066] In one embodiment, when performing structural specification verification, the table creation statement information is first extracted from the abstract syntax tree. This extraction process is implemented by traversing each node of the abstract syntax tree, specifically including extracting field definition information from the field list node, extracting partition field information from the partition definition node, extracting table attribute information from the table attribute node, extracting lifecycle configuration parameters from the lifecycle configuration sub-node, and extracting primary key definition information from the primary key definition node when the target level is the application data service layer or dimension layer. For the data detail layer table creation statement in this embodiment, the extraction mainly involves the first three items and the lifecycle configuration parameters.
[0067] When extracting field definition information, assuming four field nodes are identified: "user STRING", "logtime BIGINT", "platform STRING", and "ip STRING", each field node contains a field name, data type, and optional comment sub-nodes, it is found that none of these four fields contain comment sub-nodes, therefore the comment content in the field definition information is empty. When extracting partition field information, assuming the partition definition is identified as "dt STRING", which conforms to the partition field format required by the data detail layer. When extracting table attribute information, assuming the table attribute node contains a configuration item "format-version='2'", but the lifecycle configuration parameter is missing. When extracting the lifecycle configuration parameter, it is found that this parameter is completely missing. After completing the information extraction, the above information is checked item by item according to the structural specification rules in the target rule subset. First, the comment verification of the field definition information is initiated. According to the requirement in the structural specification rule that "all fields in the data detail layer must contain Chinese comments", the comment sub-nodes of the four fields are checked, and it is found that the fields "user", "logtime", "platform", and "ip" are all missing comments, therefore the comment verification fails. Following the logic of item-by-item verification, since the field definition information comment verification failed, the remaining items were checked to collect a complete list of issues. Next, the format verification of the partition field information was checked, confirming that "dt STRING" met the format requirement of "partition field must be a date string type and the comment must be the partition date," thus passing this step. Next, the integrity verification of the table attribute information was checked. According to the data detail layer table attribute rules, table attributes must include eleven mandatory parameters, such as format version, compression encoding, lifecycle enable flag, and lifecycle expiration time. Currently, only the format version parameter is present, so the integrity verification failed. Finally, the matching verification of the lifecycle configuration parameters with the target layer was checked. According to the data detail layer rules, the lifecycle configuration parameter must be "lifecycle management enabled and expiration time is 360 days," which is currently completely missing, so the matching verification failed.
[0068] During the above verification process, since the field definition information annotation verification, table attribute information integrity verification, and lifecycle configuration parameter matching verification in the table creation statement information all failed, not every information verification result was a pass. Therefore, the structure specification verification result was determined to be a verification failure. This result triggered the short-circuit mode of cascading verification, terminating the execution of subsequent layered compliance verification and data flow verification, and summarizing all the collected issues and outputting them to the feedback repair engine for automatic repair processing.
[0069] As described above, by extracting the various elements of the table creation statement from the abstract syntax tree in a structured manner and performing targeted structural specification checks on each item, fine-grained quality control of data definition type statements is achieved, avoiding the problems of missed detections and false alarms caused by traditional text-level regular expression matching. At the same time, through multi-dimensional item-by-item checks such as field comments, partition format, table attribute integrity, lifecycle matching, and primary key configuration, the integrity and compliance of the table structure at each level of the data warehouse are ensured. This ensures that the table creation statement meets the preset data warehouse development specifications before being put into production, significantly reducing the risk of data processing anomalies and data reliability issues caused by structural deficiencies or configuration errors.
[0070] S1243. If the naming convention verification result is a failure, the cascading verification process will also fail.
[0071] After obtaining the naming convention verification result in S1241, if the naming convention verification result fails, the cascading verification process will also fail. For example, assuming the syntax verification passes, all identifiers are extracted from the generated abstract syntax tree, including the target table name, source table name, and all field names. Then, each identifier is checked against the naming convention rules in the target rule subset, sequentially verifying whether the table name prefix matches the data warehouse hierarchy, whether the table name suffix conforms to the corresponding scenario's specifications, whether the field names use a unified serpentine naming format, and whether any database reserved words have been misused. If any identifier does not conform to the specifications, the naming convention verification is deemed to have failed, immediately triggering a short-circuit mechanism to terminate subsequent structural specification verification, layered compliance verification, and data flow verification, directly outputting the cascading verification failure result.
[0072] In one embodiment, based on successful syntax validation, naming convention rules are extracted from the target rule subset. The table name nodes and field name nodes in the abstract syntax tree are traversed and checked. First, the prefix "dwd_" of the table name "dwd_user_login" is checked to ensure it meets the data detail layer prefix requirements. Then, the suffix is checked, revealing that the table name ends in "login," which is not among the six legal suffixes allowed by the data detail layer (event, session, lifecycle, snapshot, detail, and history). Therefore, the naming convention validation result is a failure. According to the short-circuit mode mechanism, this result is directly used as the final result of the cascading validation process, terminating the execution of subsequent structural specification validation, hierarchical compliance validation, and data flow validation. In this embodiment, since the table name prefix and suffix directly determine the target level of the statement, an incorrect level determination will cause subsequent structural specification and hierarchical compliance validations to load the wrong rule subset, generating a large number of false positives. Therefore, this embodiment sets the naming convention validation to be executed immediately after syntax validation to reduce false positives. Furthermore, the application of short-circuit mode at this stage avoids invalid subsequent validations, significantly improving validation efficiency.
[0073] S1244. If the structural specification verification result is that the verification is passed, perform layered compliance verification on the abstract syntax tree according to the layered compliance rules in the target rule subset to obtain the layered compliance verification result.
[0074] After obtaining the structural specification verification result in S1242, if the structural specification verification result is passed, the abstract syntax tree is subjected to layered compliance verification according to the layered compliance rules in the target rule subset to obtain the layered compliance verification result. Layered compliance verification refers to the process of checking whether the operation method, aggregation logic, and comment header of a statement conform to the development specification of the target level. Layered compliance rules are a set of rules in the constraint rule set that specify requirements for writing methods, variable formats, and the use of aggregation functions at a specific level. For example, assuming the structural specification verification is passed, based on the type of the current statement (i.e., the target statement type obtained above, Data Definition Type (DDL) or Data Operation Type (DML)), the corresponding layered feature information is extracted from the abstract syntax tree. For DDL statements, write operation keywords and header comment information are extracted; for DML statements, date variables and aggregation function call information are additionally extracted. Then, each item in the layered compliance rules in the target rule subset is verified to confirm whether the write operation permission matches the current level, whether the date variable format conforms to the unified specification, whether the business comments in the statement header are complete, and whether the aggregation logic conforms to the processing positioning of the current level. If all layered features meet the specification requirements, the layered compliance check is deemed passed, and the system proceeds to the final check stage (i.e., data flow verification). If any feature fails to meet the specification, the layered compliance check is deemed to have failed, triggering a short-circuit mechanism to terminate subsequent data flow verifications and directly outputting the result of cascading verification failure. This approach, from an architectural perspective, ensures that the generated statements conform to the design principles of layered decoupling in the data warehouse, avoiding issues such as confused layered responsibilities or cross-layer violations, preventing the long-term accumulation of unmaintainable data swamps, and ensuring the long-term stability of the data warehouse architecture. Simultaneously, the short-circuit mechanism further reduces the scope of invalid checks, improving overall verification efficiency.
[0075] In one implementation, assuming all fields in S1242 have been annotated and table attributes have been configured with lifecycle parameters, and the structural specification verification passes, the hierarchical compliance verification continues. Hierarchical compliance rules are extracted from the target rule subset, and the abstract syntax tree is checked. Since this statement is a data definition type (i.e., the SQL provided in this embodiment is a table creation statement), the hierarchical compliance verification mainly checks whether the write method configuration in the table attributes matches the data detail layer, i.e., whether the data detail layer uses partition overwrite write method, and whether there is necessary comment header information. After checking, the statement meets the hierarchical compliance requirements of the data detail layer; therefore, the hierarchical compliance verification result is "verified passed," and the process continues to the next stage, data flow verification. Because compliance checks such as write method and aggregation logic require complete table structure information—for example, the existence of partition fields determines whether partition overwrite write method is feasible—this embodiment sets the hierarchical compliance verification to be executed after the structural specification verification. This ensures that the statement is not only structurally complete but also conforms to the development paradigm of the target level in terms of operational logic, preventing data processing anomalies caused by incorrect hierarchical operation methods.
[0076] In one embodiment, during hierarchical compliance verification, hierarchical feature information is first extracted from the abstract syntax tree based on the target statement type. When the target statement type is a data definition type, the hierarchical feature information includes write operation keywords and SQL header comments. When the target statement type is a data operation type, the hierarchical feature information includes write operation keywords, date variables, SQL header comments, and aggregate function call information. Each item in the hierarchical feature information is then subjected to corresponding hierarchical compliance verification according to the hierarchical compliance rules in the target rule subset. This hierarchical compliance verification includes matching the write operation keywords with the target level, format verification of date variables, integrity verification of SQL header comments, and function usage verification of aggregate function call information. If the verification result for each item in the hierarchical feature information passes, the hierarchical compliance verification result is considered passed.
[0077] For example, during hierarchical compliance verification, assuming the SQL to be verified (the initial SQL or the repaired SQL) is a table creation statement, such as "CREATE TABLE dwd_db.dwd_user_login_event(user_id STRINGCOMMENT'User ID',logtime BIGINT COMMENT'Log Timestamp',platform STRING COMMENT'Platform',ip STRING COMMENT'Network Address')USING iceberg PARTITIONED BY(dt STRINGCOMMENT'Partition Date')TBLPROPERTIES('format-version'='2','smart-optimizer.lifecycle.enable'='enable','smart-optimizer.lifecycle.expiration'='360')", extracting hierarchical feature information from this statement reveals that the write operation keyword is "CREATETABLE", and the Structured Query Language header comment is empty, meaning the statement does not contain header information such as author, creation date, and description. Subsequently, layered compliance checks are performed on each of the aforementioned feature information items according to the layered compliance rules in the target rule subset. In the check for matching write operation keywords with the target level, the system checks whether "CREATE TABLE" meets the write operation requirements of the data detail layer. Since the data definition type statement in the data detail layer allows the use of table creation operations, this check passes. In the check for the completeness of the Structured Query Language (SCL) header comments, the system checks whether the comment header contains required elements such as author, creation date, description, and update record. It is found that the statement lacks a SCL header comment, so this check fails. Because layered compliance checks require each item in the layered feature information to pass the check for overall passability, the layered compliance check result in this embodiment is a failure.
[0078] In one embodiment, assuming the target statement type in another scenario is a data operation type, the statement is: "INSERT OVERWRITE dws_db.dws_user_activity_daily PARTITION(dt) SELECT COUNT(DISTINCT user_id)AS active_user_cnt,'yyyy MM dd 1d′ AS dt FROMdwd d b.dwd u serl ogin e The command `vent WHERE dt=′{yyyy-MM-dd-1d}'GROUP BY platform` will extract the write operation keyword "INSERT OVERWRITE", the date variable "yyyy-MM-dd-1d", the Structured Query Language header comment (assuming it exists), and the aggregate function call information "COUNT(DISTINCTuser)" from the abstract syntax tree. i d) Then, each item is verified: the write operation keyword "INSERTOVERWRITE" meets the partition coverage write requirements of the data summary layer; the date variable format "{yyyy-MM-dd-1d}" conforms to the preset date variable template; the structured query language header comments are complete and contain all required elements; the aggregate function call information uses a valid aggregate function and the dimension field is included in the grouping clause. If all four verifications pass, the layered compliance verification result for this scenario is "verified." In this embodiment, layered feature information is extracted based on the differences in target statement types, achieving precise adaptation of verification elements and avoiding problems such as invalid verification of date variables and aggregate functions in data definition type statements, and omission of write methods and aggregation logic in data operation type statements. At the same time, a mechanism combining item-by-item verification and overall judgment is adopted to ensure that the statement meets the target level requirements in all dimensions such as write operation, variable format, comment completeness, and aggregation specifications. Any non-compliance triggers overall failure, preventing the situation where partial compliance masks overall risk, and significantly improving the strictness of the execution of data warehouse layered development specifications and the reliability of data processing logic.
[0079] S1245. If the structural specification verification result is a verification failure, the cascade verification processing result will also be a verification failure.
[0080] After obtaining the structural specification verification result in S1242, if the structural specification verification result fails, the cascading verification process also fails. For example, assuming the table name in S1241 has been corrected to "dwd_user_login_event" and the naming specification verification passes, the structural specification verification continues. Structural specification rules are extracted from the target rule subset, and the field list nodes in the abstract syntax tree are checked. It is found that the fields "user", "logtime", "platform", and "ip" are missing comment nodes. Simultaneously, the table attribute nodes lack lifecycle configuration parameters. Therefore, the structural specification verification result is a failure, and this result is directly used as the final result of the cascading verification process, terminating the execution of subsequent hierarchical compliance verification and data flow verification. In this embodiment, since the verification of structural elements such as field comments and table attributes requires loading the corresponding structural rules based on the correct hierarchical identification result (e.g., the data detail layer requires a lifecycle of 360 days, while the operational data storage layer requires lifecycle to be disabled), the structural specification verification is set to be executed after the naming specification verification. Furthermore, the application of short-circuit mode at this stage ensures that incomplete statements do not enter more complex verification stages such as data flow, significantly reducing invalid calculations.
[0081] S1246. If the hierarchical compliance verification result is "verification passed", perform data flow verification on the abstract syntax tree according to the data flow rules in the target rule subset to obtain the data flow verification result.
[0082] After obtaining the hierarchical compliance verification result in S1244, if the hierarchical compliance verification result is passed, the abstract syntax tree is subjected to data flow verification according to the data flow rules in the target rule subset to obtain the data flow verification result. Data flow verification refers to the process of checking the hierarchical legality, cross-domain compliance, and circular dependency constraints of data source tables in data operation type statements. Data flow rules are a set of rules in the constraint rule set that address source table reference relationships. For example, during data flow verification, all source table information can be obtained from the abstract syntax tree; a data dependency graph can be constructed based on all source table information; and data flow verification can be performed on the data dependency graph according to the data flow rules in the target rule subset to obtain the data flow verification result. The data flow verification includes source table hierarchical legality verification, cross-product domain read legality verification, cross-layer read annotation integrity verification, and circular dependency verification.
[0083] In one embodiment, assuming the hierarchical compliance verification passes, all source table information involved in the statement is extracted from the abstract syntax tree. Based on the hierarchy of the source tables and their respective business domains, corresponding data dependencies are constructed. Then, each data flow rule in the target rule subset is checked against the rules to verify whether the source table hierarchy conforms to the downward reading specification, whether there are any illegal cross-product domain reads, whether compliant cross-layer reads have been properly annotated, and whether there are circular dependencies in the data dependency chain. If all data flows meet the specification requirements, the data flow verification is deemed to have passed, and the final result of the entire cascading verification process is a successful verification. If any item does not conform to the specification, the data flow verification is deemed to have failed, and the final result of the entire cascading verification process is a failed verification. This embodiment, as the last line of quality defense, ensures clear data lineage and compliant flow at the data link level, avoiding inconsistencies in data definitions and insufficient compliance caused by chaotic data links. Furthermore, a multi-level progressive verification system achieves full-dimensional quality control from basic syntax to architectural flow. Combined with a short-circuit execution mechanism that is effective throughout the process, it significantly improves verification execution efficiency and the accuracy of problem location while ensuring comprehensive verification.
[0084] In one embodiment, based on the successful hierarchical compliance verification, data flow rules are extracted from the target rule subset. The source table reference nodes in the abstract syntax tree are traversed and checked. Since the statements provided in this embodiment are data definition types (i.e., table creation statements), and do not involve data source table references, the data flow verification is automatically skipped or considered successful. The final data flow verification result is successful, and this result serves as the final result of the cascading verification process; that is, the cascading verification process result is successful. In this embodiment, data flow verification is set as the final stage of cascading verification, specifically controlling the compliance of data operation type statements. For example, the data summary layer is only allowed to read the data detail layer and dimension layer, and direct reading of the operation data storage layer is prohibited. For data definition type statements, the verification logic is automatically adapted. Furthermore, the complete execution of the five-stage cascading verification ensures that the statements pass quality control in five dimensions: syntax, naming, structure, hierarchical compliance, and data flow. The output target structured query language can be directly used in the production environment, avoiding data development rework and data reliability risks caused by omissions in a single dimension.
[0085] S1247. If the hierarchical compliance verification result is a verification failure, the cascading verification processing result will also be a verification failure.
[0086] After obtaining the layered compliance verification result in the aforementioned S1244, if the layered compliance verification result fails, the cascading verification processing result will also fail. For example, assuming the structural specification verification passes, based on the type of the current statement (i.e., the target statement type obtained above, Data Definition Language (DDL) or Data Operation Language (DML)), the corresponding layered feature information is extracted from the abstract syntax tree. For DDL statements, write operation keywords and header comment information are extracted; for DML statements, date variables and aggregate function call information are additionally extracted. Then, each layered compliance rule in the target rule subset is checked against the rules to verify whether the write operation permission matches the current level, whether the date variable format conforms to the unified specification, whether the business comments at the statement header are complete, and whether the aggregation logic conforms to the processing positioning of the current level. If any item fails to meet the specification, the layered compliance verification is deemed to have failed, triggering a short-circuit mechanism to terminate subsequent data flow verification, and directly outputting the cascading verification failure result.
[0087] S1248. If the data flow verification result is successful, the cascading verification processing result is also successful.
[0088] After obtaining the data flow verification result in S1246, if the data flow verification result is successful, the cascading verification process also passes. For example, assuming the hierarchical compliance verification passes, all source table information involved in the statement is extracted from the abstract syntax tree. Based on the hierarchy of the source tables and their respective business domains, corresponding data dependencies are constructed. Then, each data flow rule in the target rule subset is checked against the rules to verify whether the source table hierarchy conforms to the downward reading specification, whether there are any illegal cross-product domain reads, whether compliant cross-layer reads have been properly annotated, and whether there are any circular dependencies in the data dependency chain. If all data flows meet the specification requirements, the data flow verification is deemed successful, and the final result of the entire cascading verification process is successful. This embodiment, as the last line of quality defense, ensures clear data lineage and compliant data flow at the data link level, avoiding issues such as inconsistent data definitions and insufficient compliance caused by chaotic data links. In addition, a multi-level progressive verification system achieves full-dimensional quality control from basic syntax to architectural flow. Combined with a short-circuit execution mechanism that is effective throughout the process, it significantly improves verification execution efficiency and the accuracy of problem location while ensuring the comprehensiveness of verification.
[0089] S1249. If the data flow verification result is a verification failure, the cascading verification process will also fail.
[0090] After obtaining the data flow verification result in S1246, if the data flow verification result fails, the cascading verification process will also fail. For example, assuming the layered compliance verification passes, all source table information involved in the statement is extracted from the abstract syntax tree. Based on the hierarchy of the source tables and their respective business domains, corresponding data dependencies are constructed. Then, each data flow rule in the target rule subset is checked against the rules to verify whether the source table hierarchy conforms to the downward reading specification, whether there are any illegal cross-product domain reads, whether compliant cross-layer reads have been properly annotated, and whether there are circular dependencies in the data dependency chain. If any item fails to meet the specification, the data flow verification is deemed to have failed, and the final result of the entire cascading verification process is a verification failure. This embodiment, as the last line of quality defense, ensures clear data lineage and compliant data flow at the data link level, avoiding issues such as inconsistent data definitions and insufficient compliance caused by chaotic data links. In addition, a multi-level progressive verification system achieves full-dimensional quality control from basic syntax to architectural flow. Combined with a short-circuit execution mechanism that is effective throughout the process, it significantly improves verification execution efficiency and the accuracy of problem location while ensuring the comprehensiveness of verification.
[0091] Figure 4 This is a flowchart of another SQL generation method based on natural language provided in the embodiments of this application, referred to... Figure 4 During the repair process, this natural language-based SQL generation method specifically includes: S131. Obtain the problem list in the verification results, classify each problem in the problem list according to the preset category, and obtain the repair category corresponding to each problem.
[0092] After obtaining the verification result in S12, if the verification fails, the first step is to retrieve the problem list from the verification result. Each problem in the problem list is then categorized according to a preset category to obtain the corresponding repair category. Problem categorization refers to receiving the problem list output by the cascaded verification and assigning each problem to a specific repair category based on preset repair difficulty and automation levels. Preset categories include deterministic automatic repair categories (which can be directly modified without semantic understanding), large language model-assisted repair categories (which require semantic inference using an AI agent), and categories requiring manual intervention (involving business semantic judgment and unable to be automatically decided). For example, if the verification result output by S12 fails, a complete problem list is extracted from the failure result. Assume the problem list contains five problems: "Table name lacks a valid suffix," "Field 'user' lacks a comment," "Field 'logtime' lacks a comment," "Field 'platform' lacks a comment," and "Field 'ip' lacks a comment." To address the aforementioned issues, each problem can be categorized sequentially. First, the issue of "table names lacking valid suffixes" is identified. This requires determining the corresponding modeling type based on the business semantics of the login behavior, such as event, session, or lifecycle. It falls under the category of semantic understanding-based issues and is classified as a large language model-assisted repair category. Next, the issue of missing comments for four fields is identified. This requires generating Chinese comments that conform to the business meaning and also falls under the large language model-assisted repair category. Since this embodiment does not involve deterministic operations such as name prefix replacement or table attribute key-value completion, nor does it involve scenarios requiring manual judgment such as modeling type selection or cross-layer dependency approval, no deterministic automatic repair category or manual intervention category is generated. After categorization, each problem can be labeled with a corresponding repair strategy identifier.
[0093] The above-mentioned diagnostic classification enables precise matching of repair strategies, avoiding the directional deviation caused by a one-size-fits-all approach to repair. It ensures that deterministic problems are repaired directly and quickly, semantic problems are repaired intelligently with the help of artificial intelligence, and business problems are judged by humans, thereby improving the accuracy and controllability of repair.
[0094] S132. Perform the corresponding modification operation on the initial SQL according to the repair category to obtain the repaired first SQL.
[0095] After determining the repair category for each issue in S131, the initial SQL is modified according to the repair category to obtain the repaired first SQL. Repair execution refers to the process of calling the corresponding repair engine to perform modification operations on the initial SQL based on the repair category determined in S131. The first SQL refers to the intermediate result statement obtained after one round of repair. For example, repair operations can be executed sequentially according to a sorting strategy from high to low severity level or from low to high stage priority. First, the "table name lacks a valid suffix" issue in the large language model-assisted repair category is addressed. The error context (such as the table name "user_login", the business description "user login", or the data detail layer naming rules) is sent to the large language model. Based on semantic inference, the large language model determines that the login behavior belongs to a single event record and recommends the suffix "event", thus automatically correcting the table name to "dwd_user_login_event". Next, to address the issue of missing comments for the four fields, the field names, field types, and business context are sent to the large language model. The large language model then generates corresponding Chinese comments: “user identifier”, “logtime timestamp”, “platform”, and “network address”. This automatically corrects the field definitions to “user_id STRING COMMENT 'user identifier'”, “logtime BIGINT COMMENT 'logtime timestamp'”, “platform STRING COMMENT 'platform'”, and “ip STRING COMMENT 'network address'”. Simultaneously, the system corrects “user” to “user_id” according to the field standardization mapping rules. Finally, it adds table attribute parameters “'smart-optimizer.lifecycle.enable'='enable'” and “'smart-optimizer.lifecycle.expiration'='360'” according to the data detail layer lifecycle configuration rules.After all repair operations are completed, the first SQL statement after repair is: "CREATE TABLE dwd_db.dwd_user_login_event (dt STRING COMMENT'Partition Date',user_id STRING COMMENT'User Identifier',logtime BIGINT COMMENT'Log Timestamp',platformSTRING COMMENT'Platform',ip STRING COMMENT'Network Address')USING iceberg PARTITIONED BY(dt)TBLPROPERTIES('format-version'='2','smart-optimizer.lifecycle.enable'='enable','smart-optimizer.lifecycle.expiration'='360')".
[0096] As described above, through category-driven repair execution, the system automates the handling of various issues such as naming correction, field standardization, annotation generation, and configuration injection. A single round of repair can cover most common issues, compressing the original manual modification process to a second-level automatic completion, significantly improving data development efficiency.
[0097] S133. Re-execute the cascading verification process of the short-circuit mode on the first SQL to obtain the re-verification result.
[0098] After obtaining the first SQL in S132, the short-circuit mode cascading verification process is re-executed on the first SQL to obtain a re-verification result. Re-verification refers to performing the short-circuit mode cascading verification process on the repaired first SQL again to verify the repair effect and identify any new or residual problems. For example, the specific verification process for re-short-circuit mode cascading verification of the first SQL generated in S132 is the same as in the previous embodiment and will not be repeated here. If syntax verification, naming convention verification, structural convention verification, layered compliance verification, and data flow verification all pass, the re-verification result is output as verification passed. If any verification fails, short-circuit mode is triggered, terminating subsequent verifications and outputting the re-verification result as verification failed.
[0099] The above-mentioned re-validation ensures that no new syntax errors or specification violations were introduced during the repair operation, while also verifying the completeness and correctness of the repair, providing a reliable basis for subsequent output decisions. Furthermore, if new issues are introduced during the repair process, such as naming conflicts caused by field standardization, re-validation can promptly identify them and proceed to the next round of repair, preventing any issues from being overlooked.
[0100] S134. If the re-verification result is successful, output the first SQL as the target SQL.
[0101] After obtaining the re-verification result in S133, if the re-verification result is successful, the first SQL is output as the target SQL. Since the re-verification result obtained in S133 is successful, and the current repair count is one, which has not reached the preset threshold (such as three times), the first SQL can be directly output as the target SQL for the user to determine whether to deploy in the production environment.
[0102] S135. If the re-verification result is a verification failure and the number of repairs has not reached the preset threshold, continue to repair the first SQL until the verification passes or the number of repairs reaches the preset threshold, at which point a diagnostic report will be output.
[0103] After obtaining the re-verification result in S133, if the re-verification result is a verification failure and the number of repair attempts has not reached the preset threshold, the first SQL statement continues to be repaired until the verification passes or the number of repair attempts reaches the preset threshold, at which point a diagnostic report is output. The preset threshold refers to the maximum number of repair attempts allowed by the system; in this embodiment, it is set to three. The diagnostic report is a detailed document containing a problem description, repair suggestions, and manual processing guidance, output when automatic repair fails to make the statement compliant within a specified time. For example, assuming that the verification result is still not a failure after the aforementioned S133 re-verification (e.g., the suffix recommended by the large language model does not conform to the actual business), a second round of repair processing will be executed, re-classifying, repairing, and verifying until the verification passes or the number of repair attempts reaches three. If the verification still fails after three rounds of repair, automatic repair is abandoned, and a diagnostic report containing a complete list of problems, a record of each round of repair attempts, suggested manual repair directions, and the source of the corresponding specification document is output.
[0104] The above-mentioned branch decision-making mechanism, which outputs upon successful verification, iterates upon failure, and reports upon reaching a threshold, achieves a balance between quality and efficiency. It ensures automatic release of high-confidence repairs, avoids resource waste caused by infinite loop repairs through preset thresholds, and provides complete contextual information for manual intervention in the diagnostic report, ensuring that no problem is missed and that the problem is traceable.
[0105] In one embodiment, before receiving the requirement information in natural language form in S11, the method further includes: automatically parsing the natural language specification document into an executable set of constraint rules using a specification parsing engine. For example, firstly, a user-maintained natural language specification document is read. This document is written in markup language format and contains four structured expressions: tables, code blocks, enumeration lists, and natural language paragraphs. The document type (including naming conventions, table creation conventions, data processing conventions, and hierarchical development guidelines, etc.) is identified through a document scanning step, and the document's title hierarchy, tables, code blocks, and list structures are extracted. Subsequently, multiple parsers are used in parallel processing: a table parser extracts structured data such as hierarchical prefixes and suffixes from the markup language tables; a code block extractor extracts regular expressions and structured query language templates; a list parser extracts enumeration values such as sets of valid suffixes; for natural language paragraphs that cannot be matched by templates, a large language model can be used to extract structured rule descriptions. After extraction, the structured data is matched against predefined rule templates, automatically populating parameters such as rule identifier, applicable level, validation object, rule type, severity level, and matching mode. This generates a set of constraint rules expressed in a declarative rule definition language. Each rule includes at least a rule identifier, applicable level, validation object, rule type, severity level, and matching mode. Finally, conflict detection is performed between the newly generated rules and existing rules, employing a conflict resolution strategy that prioritizes level-specific rules. The correctness of the rules is verified through pattern structure validation and regression testing. Through this specification parsing engine, this embodiment realizes the core concept of "specification as a guardrail," enabling existing natural language specification documents to be automatically converted into executable constraint rules without manual coding. The rule set can be automatically updated synchronously after the specification document is updated, significantly reducing the cost and threshold of rule maintenance.
[0106] In one embodiment, before receiving the demand information in natural language form in S11, the method further includes: constraining the SQL generation process of the large language model with a step-by-step workflow instruction sequence through a generation controller. For example, before the AI agent generates the structured query language, the corresponding generation workflow is dynamically loaded according to the task type (including data detail layer table creation, data summary layer table creation, application data service layer table creation, dimension layer table creation, or data processing task), constraining the generation process of the large language model with a step-by-step instruction sequence. The generation workflow includes at least modeling type selection, field normalization mapping, template constraints, and a pre-generation validation checklist. Taking data detail layer table creation as an example, the workflow first instructs the AI agent to identify the product domain and business domain, then selects one of six modeling suffixes (including event type, session type, lifecycle type, snapshot type, detail type, and history type) based on data characteristics, then performs field normalization mapping (e.g., unifying the identifier field to string type, unifying the boolean field to integer type 0 or 1, or unifying the time field to millisecond-level timestamps), and then fills in the table name, field list, and table attributes according to a predefined table creation template. At the end of the generation process, the workflow forces the AI agent to verify each item against a checklist (including whether the product domain has been specified, whether the table name format is correct, whether the modeling suffix is one of the six types, whether the identifier field uses a string type, whether the boolean field uses an integer type, whether the partition field exists, whether all fields have Chinese comments, and whether the table attributes contain all required parameters, etc.). If any item on the checklist is not met, the generation controller will stop the generation and require the AI agent to correct it before continuing. For complex indicator development requirements, the generation controller also loads a full-process workflow that includes ten stages: requirement registration, data exploration, requirement review, architecture design, development at each layer, code review, scheduling and deployment, and data verification. During the architecture design stage, it enforces a necessity analysis of the data aggregation layer. For example, it determines whether to retain the data aggregation layer based on four judgment conditions: whether multiple application data service layers are reused, whether multi-dimensional cross-analysis is needed, indicator complexity, and whether it is reused across tasks. This decision-making process must be explicitly executed and recorded. For application data service layer table creation, the generation controller forces the simultaneous generation of two table creation statements: one for the data lake and one for the analytical database, and verifies that the primary key definitions of the two are completely consistent. Through this generation controller, this embodiment achieves pre-constraints, that is, it guides the large language model to follow the specifications at the source of SQL generation, rather than fixing it after generation, thereby significantly reducing the output of non-compliant SQL.
[0107] In one embodiment, before receiving the requirement information in natural language form in S11, the method further includes: automatically injecting domain context according to the task target level and type via a context injector. For example, before the AI agent generates structured query language, relevant context information is automatically selected and injected into the AI agent's prompts based on the target level, product domain, and task type of the current development task. Content can be injected in descending order of priority. For example, the first priority is the hierarchical specification, including the naming rules, table templates, data processing templates, and special constraints for that level; this part is mandatory and not subject to pruning. The second priority is the related table structure, including the source table structure definition and the target table structure definition (if it already exists). A relevance score can be calculated based on field name overlap, weight within the same business domain, and historical reference frequency, and the results are selected in descending order of score. The third priority is the domain context, including a business terminology dictionary, requirement design documents, and processing examples of similar tables. The fourth priority is the change context, including the differences between the original structure definition and the target structure definition, a list of downstream dependent tables, and historical change records. A token budget management strategy can also be adopted, allocating the total reserved tokens proportionally to content of different priorities. For example, hierarchical specifications might account for approximately 40%, table structures approximately 30%, examples approximately 20%, and terminology dictionaries approximately 10%. When content of a certain priority exceeds the budget, it can be truncated from the lowest relevance end after being sorted by relevance score, with the highest priority content not participating in the pruning, to ensure the complete injection of necessary constraint information. Through this context injector, this embodiment enables the AI agent to have sufficient domain knowledge when generating SQL, allowing it to directly generate code that conforms to specifications, achieving a prevention-than-repair effect.
[0108] In one embodiment, after obtaining the verification result by performing short-circuit mode cascading verification on the initial SQL in S12, the method further includes: performing confidence assessment and rollback mechanism through the feedback repair engine. For example, during the feedback repair process, a confidence score is calculated for each repair operation, and the output method of the repair result is determined based on the confidence score. The confidence score is calculated based on the product of the baseline score of the repair category, the historical repair success rate, and the edit distance ratio. For example, the baseline score for the deterministic automatic repair category is 1.0, the baseline score for the large language model-assisted repair category is between 0.7 and 0.9, and the baseline score for the manual intervention category is 0.3. The confidence score for each repair operation can be obtained by multiplying the baseline score by the historical repair success rate of the category, then multiplying by 1 and subtracting the edit distance ratio (the edit distance ratio refers to the degree of character change in the structured query language before and after repair). When the confidence score is greater than or equal to 0.8, the repair operation is automatically accepted; when the confidence score is between 0.6 and 0.8, the repair is accepted but marked as "recommended for review"; when the confidence score is below 0.6, even if the repaired structured query language passes cascading validation, the system still marks it as "requires manual review" and does not directly use it as the final output. Furthermore, a repair rollback mechanism is supported: when subsequent rounds of repair operations cause a recurrence of issues repaired in previous rounds, the system automatically rolls back to the state of the previous round and selects an alternative repair strategy to retry. Through this confidence assessment and rollback mechanism, this embodiment can effectively avoid the implicit risk of passing validation but having semantic errors, while preventing mutual interference between multiple rounds of repair, thus improving the security and reliability of automatic repair.
[0109] In one embodiment, before outputting the target SQL, the method further includes: performing an incremental update mechanism through a specification parsing engine. For example, the specification parsing engine provided in this embodiment also supports incremental update functionality. First, a version fingerprint is calculated on the specification document (e.g., calculating the document's hash value or generating a version identifier based on the modification timestamp). By comparing the current version fingerprint with the version fingerprint recorded during the last parsing, changes to the document are detected. After detecting changes, structured extraction and rule generation are performed only on the changed parts, generating incremental rule patches. These incremental patches include newly added rules, modified rules, and obsolete rule markers. Before applying the incremental patches to the existing rule set, regression verification can be performed, for example, testing the updated rule set using an existing production structured query language file to detect false positives (i.e., SQL that was originally compliant is incorrectly marked as non-compliant by the new rules). If the regression verification passes, the new rule is activated; if the regression verification fails, the new rule is marked as "pending manual confirmation," and the user (e.g., a data warehouse developer) reviews and decides whether to activate it. Through this incremental update mechanism, this embodiment achieves rapid synchronization of the rule set after the specification document is changed, avoiding the overhead of re-parsing the entire document every time it is updated. At the same time, regression verification ensures the quality of rule updates and prevents newly introduced rules from misjudging existing compliant SQL.
[0110] In one embodiment, in complex indicator development scenarios, the generation controller provided in this embodiment can load a full-process workflow containing multiple (e.g., ten) stages, from requirement registration, data exploration, and requirement review, to architecture design, detailed data layer development, summary data layer development, application data service layer development, and code review, up to scheduling, deployment, and data verification. During the architecture design stage, a necessity analysis of the summary data layer is enforced. Based on four criteria—multi-application data service layer reuse, multi-dimensional cross-referencing, indicator complexity, and cross-task reuse—it is determined whether to retain the data summary layer. When all four criteria are negative or simple, the process is streamlined, skipping the data summary layer, and the application data service layer reads directly from the detailed data layer. This embodiment, through mandatory architecture design decisions and process streamlining mechanisms, avoids unnecessary intermediate layer redundancy, reduces data link complexity and storage resource waste, while ensuring the standardization and traceability of the complex indicator development process.
[0111] In one embodiment, during table creation at the application data service layer, the workflow mandates the simultaneous generation of two table creation statements: one for the data lake computing engine and one for the relational database. Furthermore, the primary key definitions in both must be completely identical. The system uses constraint rules to verify whether the primary key configuration in the data lake computing engine table attributes matches the field names and order declared in the relational database primary key. If a violation occurs, generation is prevented, and a message indicating a primary key inconsistency is displayed. This embodiment, through consistency verification of dual table creation, ensures synchronization between the offline data warehouse and the online application database, avoiding data synchronization failures or query result deviations caused by inconsistent primary key definitions, and guaranteeing data consistency across storage engines.
[0112] In one embodiment, when system resources are sufficient, naming convention verification and structural convention verification can be started simultaneously. Utilizing the multi-node traversal capability of the abstract syntax tree, table name prefixes and suffixes, field names and comments, partition formats, and table attribute integrity are checked simultaneously. If a critical error occurs in either parallel stage, the remaining verification tasks of the other stage are canceled. The results of the two stages are merged to determine whether to proceed to the next stage. This embodiment significantly reduces time overhead by executing naming and structural verification in parallel, while maintaining the rapid error response capability of short-circuit mode, thus improving the throughput performance of large-scale batch verification while ensuring verification quality.
[0113] In one embodiment, in the data detail layer table creation workflow, the modeling type selection step automatically matches valid suffixes based on data characteristics using a decision tree. For example, a single event record with no state change matches the "Event" suffix; a record with start and end times and multi-stage state accumulation matches the "Session" suffix; the entire lifecycle state transition of an entity matches the "Lifecycle" suffix; fixed-period sampling statistics match the "Snapshot" suffix; multi-source merging and field flattening for detail retention match the "Detail" suffix; and tracing historical attribute changes matches the "History" suffix. This embodiment, by replacing free inference with a structured decision tree, significantly improves the accuracy of modeling type selection, avoids modeling type mismatches caused by model illusion, and ensures the semantic consistency and maintainability of the data detail layer tables.
[0114] This application provides a natural language-based SQL generation device, referring to... Figure 5 The natural language-based SQL generation device includes a processor 31, a memory 32, a communication module 33, an input device 34, and an output device 35. The number of processors and the number of memories in the natural language-based SQL generation device can be one or more. The processor, memory, communication module, input device, and output device of the natural language-based SQL generation device can be connected via a bus or other means.
[0115] The memory 32, as a computer-readable storage medium, can be used to store software programs, computer-executable programs, and modules, such as program instructions / modules corresponding to the SQL generation method based on natural language described in any embodiment of this application. The memory may primarily include a program storage area and a data storage area. The program storage area may store the operating system and at least one application program required for a function; the data storage area may store data created based on the use of the device, etc. Furthermore, the memory may include high-speed random access memory and non-volatile memory, such as at least one disk storage device, flash memory device, or other non-volatile solid-state storage device. In some instances, the memory may further include memory remotely located relative to the processor, and these remote memories can be connected to the device via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.
[0116] The communication module 33 is used for data transmission.
[0117] The processor 31 executes various functional applications and data processing of the device by running software programs, instructions and modules stored in the memory, thereby realizing the above-mentioned SQL generation method based on natural language.
[0118] Input device 34 can be used to receive input digital or character information, and to generate key signal inputs related to user settings and function control of the device. Output device 35 may include display devices such as a display screen.
[0119] The natural language-based SQL generation device provided above can be used to execute the natural language-based SQL generation method provided in the above embodiments, and has corresponding functions and beneficial effects.
[0120] This application embodiment also provides a storage medium for storing computer-executable instructions. When executed by a computer processor, the computer-executable instructions are used to execute a natural language-based SQL generation method. This natural language-based SQL generation method includes: receiving requirement information in natural language form and performing intelligent generation processing based on the requirement information and an artificial intelligence agent to obtain initial SQL; performing short-circuit mode cascading verification processing on the initial SQL to obtain a verification result, wherein the cascading verification processing sequentially executes syntax verification, naming convention verification, structural convention verification, hierarchical compliance verification, and data flow verification, and the short-circuit mode terminates subsequent verification processing if any stage of verification fails; if the verification result is a verification failure, repairing the initial SQL based on the verification result and re-performing the short-circuit mode cascading verification processing; if the verification passes within a preset threshold number of repair attempts, the corresponding verified SQL is output as the target SQL; or if the verification fails even after the preset threshold number of repair attempts is reached, a diagnostic report is output; if the verification result is a verification pass, the initial SQL is output as the target SQL.
[0121] Storage medium – any type of memory device or storage device. The term “storage medium” is intended to include: mounting media, such as CD-ROM, floppy disk, or magnetic tape devices; computer system memory or random access memory, such as DRAM, DDR RAM, SRAM, EDO RAM, Rambus RAM, etc.; non-volatile memory, such as flash memory, magnetic media (e.g., hard disk or optical storage); registers or other similar types of memory elements, etc. Storage medium may also include other types of memory or combinations thereof. Furthermore, storage medium may reside in a first computer system in which the program is executed, or it may reside in a different second computer system connected to the first computer system via a network (such as the Internet). The second computer system can provide program instructions to the first computer for execution. The term “storage medium” can include two or more storage media residing in different locations (e.g., in different computer systems connected via a network). Storage medium may store program instructions (e.g., specifically implemented as a computer program) executable by one or more processors.
[0122] Of course, the computer-executable instructions provided in the embodiments of this application are not limited to the SQL generation method based on natural language as described above, but can also execute related operations in the SQL generation method based on natural language provided in any embodiment of this application.
[0123] The above embodiments provide a natural language-based SQL generation device and storage medium that can execute the natural language-based SQL generation method provided in any embodiment of this application. For technical details not described in detail in the above embodiments, please refer to the natural language-based SQL generation method provided in any embodiment of this application.
[0124] The above description is merely a preferred embodiment and the technical principles employed in this application. This application is not limited to the specific embodiments described herein, and various obvious changes, readjustments, and substitutions that can be made by those skilled in the art will not depart from the scope of protection of this application. Therefore, although this application has been described in detail through the above embodiments, this application is not limited to the above embodiments, and may include many other equivalent embodiments without departing from the concept of this application. The scope of this application is determined by the scope of the claims.
Claims
1. A method for generating SQL based on natural language, characterized in that, include: Receive demand information in natural language form, and generate initial SQL based on the demand information and the artificial intelligence agent; The initial SQL is subjected to cascading validation in short-circuit mode to obtain the validation result. The cascading validation process involves sequentially executing syntax validation, naming convention validation, structural convention validation, hierarchical compliance validation, and data flow validation. The short-circuit mode terminates the subsequent validation process if any stage of validation fails. If the verification result is a verification failure, the initial SQL is repaired according to the verification result and the short-circuit mode cascading verification is performed again. If the verification passes within the preset threshold number of repairs, the corresponding SQL that passes the verification is output as the target SQL. If the verification still fails even after the number of repairs reaches the preset threshold, a diagnostic report is output. If the verification result is successful, the initial SQL will be output as the target SQL.
2. The method according to claim 1, characterized in that, The step of performing short-circuit mode cascading validation on the initial SQL to obtain the validation result includes: The initial SQL is subjected to hierarchical recognition processing to obtain the target level of the initial SQL in the preset data warehouse hierarchy. The hierarchical recognition processing adopts a three-level cascaded recognition strategy of table name prefix matching, database name matching and SQL statement feature inference. The data warehouse hierarchy includes an operational data storage layer, a data detail layer, a data summary layer, an application data service layer and a dimension layer. The initial SQL is subjected to statement type identification to obtain the target statement type, which includes data definition type and data operation type; Based on the target level and the target statement type, a subset of target rules is loaded from a preset set of constraint rules. The subset of target rules includes public rules and level-specific rules. The initial SQL is subjected to short-circuit mode cascading verification processing based on the constraint rules in the target rule subset to obtain the verification result.
3. The method according to claim 2, characterized in that, The step of performing short-circuit mode cascading validation on the initial SQL to obtain the validation result includes: The initial SQL is parsed into an abstract syntax tree. If the parsing is successful, the syntax verification passes. If the parsing fails, the verification result of the cascading verification process is verification failure. If the syntax check passes, the abstract syntax tree is subjected to a naming convention check based on the naming convention rules in the target rule subset to obtain the naming convention check result; If the naming convention verification result is successful, the abstract syntax tree is subjected to a structure convention verification based on the structure convention rules in the target rule subset to obtain a structure convention verification result; if the naming convention verification result is unsuccessful, the verification result of the cascading verification process is unsuccessful. If the structural specification verification result is successful, the abstract syntax tree is subjected to hierarchical compliance verification according to the hierarchical compliance rules in the target rule subset to obtain the hierarchical compliance verification result; if the structural specification verification result is unsuccessful, the verification result of the cascading verification process is unsuccessful. If the hierarchical compliance verification result is successful, the abstract syntax tree is subjected to data flow verification according to the data flow rules in the target rule subset to obtain the data flow verification result; if the hierarchical compliance verification result is unsuccessful, the cascading verification process results in verification failure. If the data flow verification result is successful, the cascade verification process is considered successful; if the data flow verification result is unsuccessful, the cascade verification process is considered unsuccessful.
4. The method according to claim 3, characterized in that, The step of performing naming convention verification on the abstract syntax tree according to the naming convention rules in the target rule subset to obtain the naming convention verification result includes: Extract the target identifier from the parsed abstract syntax tree, the target identifier including the target table name and field name; Obtain the prefix and suffix of the target table name, perform a matching verification based on the prefix of the target table name and the target level, and perform a legality verification based on the suffix of the target table name and the preset set of legal suffixes corresponding to the target level to obtain the table name verification result; Perform snake-shaped naming format verification and database reserved word verification on each field name in the target identifier to obtain the field verification results; When the target layer is the application data service layer, the character length of the target table name is obtained, and the character length is checked against the upper limit threshold to obtain the length check result. If the target layer is not the application data service layer, the length verification result is that the verification passed. If the table name verification result, the field verification result, and the length verification result are all passed, the naming convention verification result is also passed.
5. The method according to claim 3, characterized in that, The step of performing structural specification verification on the abstract syntax tree based on the structural specification rules in the target rule subset to obtain the structural specification verification result includes: The table creation statement information is extracted from the abstract syntax tree. The table creation statement information includes one or more of the following: field definition information, partition field information, table attribute information, lifecycle configuration parameters, and primary key definition information. According to the structural specification rules in the target rule subset, each item in the table creation statement information is checked for structural specification. The structural specification check includes the annotation check of field definition information, the format check of partition field information, the integrity check of table attribute information, the matching check of lifecycle configuration parameters with the target level, and the primary key configuration check based on primary key definition information when the target level is the application data service layer or dimension layer. If the verification result of each item in the table creation statement information is successful, the structure specification verification result is successful.
6. The method according to claim 3, characterized in that, The step of performing hierarchical compliance verification on the abstract syntax tree based on the hierarchical compliance rules in the target rule subset to obtain the hierarchical compliance verification result includes: Hierarchical feature information is extracted from the abstract syntax tree according to the target statement type. When the target statement type is a data definition type, the hierarchical feature information includes write operation keywords and SQL header comments. When the target statement type is a data operation type, the hierarchical feature information includes write operation keywords, date variables, SQL header comments, and aggregate function call information. According to the hierarchical compliance rules in the target rule subset, each item in the hierarchical feature information is subjected to corresponding hierarchical compliance verification. The hierarchical compliance verification includes matching verification of the write operation keyword with the target level, format verification of date variables, integrity verification of SQL header comments, and function usage verification of aggregate function call information. If the verification result of each item in the hierarchical feature information is successful, the hierarchical compliance verification result is successful.
7. The method according to claim 3, characterized in that, The step of performing data flow verification on the abstract syntax tree based on the data flow rules in the target rule subset to obtain the data flow verification result includes: Retrieve all source table information from the abstract syntax tree; Construct a data dependency graph based on all the source table information; The data flow verification results are obtained by performing data flow verification on the data dependency graph according to the data flow rules in the target rule subset. The data flow verification includes source table level legality verification, cross-product domain read legality verification, cross-level read annotation integrity verification, and circular dependency verification.
8. The method according to claim 1, characterized in that, Based on the verification results, the initial SQL is repaired and re-verified using short-circuit mode, including: Obtain the problem list from the verification results, classify each problem in the problem list according to a preset category, and obtain the repair category corresponding to each problem; According to the repair category, perform the corresponding modification operation on the initial SQL to obtain the repaired first SQL; The first SQL statement is re-executed using the short-circuit mode cascading verification process to obtain the re-verification result; If the re-verification result is successful, the first SQL will be output as the target SQL. If the re-verification result is a verification failure and the number of repairs has not reached the preset threshold, the first SQL will continue to be repaired until the verification passes or the number of repairs reaches the preset threshold, at which point a diagnostic report will be output.
9. A SQL generation device based on natural language, characterized in that, include: Memory and one or more processors; The memory is used to store one or more programs; When the one or more programs are executed by the one or more processors, the one or more processors implement the method as described in any one of claims 1-8.
10. A storage medium for storing computer-executable instructions, characterized in that, The computer-executable instructions, when executed by a processor, are used to perform the method as described in any one of claims 1-8.
