Method for generating statement from natural language to SQL (Structured Query Language) based on large language model agent

Abstract

The invention discloses a method for generating statements from natural languages to SQL (Structured Query Language) based on a large language model agent. The method comprises the following steps of: constructing an LLM (Logical Language Model) guide table mode index for a target database; obtaining a user natural language question and extracting a keyword; searching in an LLM guidance table mode index according to the keyword, and screening related data tables; splicing the table mode information and the keywords of the related data table into a user natural language problem, and constructing an enhanced cue word; based on the large language model intelligent agent, generating SQL statements according to the enhanced cue words and verifying the SQL statements, if the SQL statements succeed in verification, obtaining the required SQL statements, and if the SQL statements fail in verification, performing iterative closed-loop correction on the generated SQL statements through an SQL statement closed-loop correction workflow; if the corrected SQL statement is successfully verified, the required SQL statement is obtained, and if the corrected SQL statement fails and the number of times of correction reaches a threshold value, the suggestions that the natural language problem of the user has defects and is modified are output. According to the method, the accuracy and success rate of SQL statement generation can be improved.

Description

TECHNICAL FIELD

[0001] The application belongs to the field of database query and natural language processing, and particularly relates to a natural language to SQL sentence generation method based on a large language model agent. BACKGROUND

[0002] Relational databases are the core systems for storing and managing structured data today. However, obtaining information from a database usually requires users to master Structured Query Language (SQL), which constitutes a significant usage barrier for users without a technical background. In order to lower the threshold for data access, the academic and industrial communities have been researching techniques for converting natural language into SQL sentences (Text-to-SQL).

[0003] Early Text-to-SQL methods mostly rely on complex semantic parsing rules or matching based on specific templates, and such methods have poor generalization ability and are difficult to adapt to different database schemas and variable language expressions. With the development of deep learning, neural network-based models have improved the accuracy of conversion to some extent, but still require a large amount of labeled data for training, and the effect is still not ideal for complex or ambiguous queries. In recent years, pre-training models represented by large language models (LLM) have made breakthroughs in natural language understanding and generation. Therefore, applying LLM to the Text-to-SQL task has become a research direction. SUMMARY

[0004] In order to solve the problems in the prior art, the application provides a natural language to SQL sentence generation method based on a large language model agent, which constructs an LLM guide label and an LLM guide table schema index, and iteratively corrects the generated SQL sentence through a SQL sentence closed-loop correction workflow based on a large language model agent, thereby enhancing the reliability, robustness and practicality of the LLM-based Text-to-SQL method.

[0005] In order to achieve the above purpose, the application provides a natural language to SQL sentence generation method based on a large language model agent, which includes the following steps: S1, constructing an LLM guide table schema index for a target database; S2, obtaining a user natural language question and extracting keywords in the user natural language question; S3, retrieving related data tables in the LLM guide table schema index according to the keywords; S4, integrating table schema information of the filtered data tables and the keywords into context information; S5, concatenating the context information with the user natural language question to construct an enhanced prompt word; S6, based on the large language model agent, generating an SQL statement according to the enhanced prompt word: S7, executing and verifying the generated SQL statement in a sandbox environment, and if the verification is passed, obtaining the required SQL statement, otherwise iteratively correcting the generated SQL statement through a SQL statement closed loop correction workflow and recording the correction times; S8, judging whether the correction times have reached a threshold value, if yes, integrating all failed SQL statements and system error information, and making the large language model output the defects and modification suggestions of the user natural language question.

[0006] Further, the S1, constructing an LLM guide table mode index for the target database, comprises the steps of: S11, obtaining each data table in the target database; S12, generating an LLM guide label describing the content of the data table according to the data table and the upstream and downstream relationship; S13, using an embedding model to vectorize each data table and the LLM guide label of the data table and construct an LLM guide table mode index.

[0007] Further, the LLM guide label comprises table basic information, table structure information, table life information, table permission information, table special value description information and table upstream and downstream relationship information.

[0008] Further, the table basic information comprises table name, table business name, table function description, table data source, table responsible person, table creation time and table last update time; the table structure information comprises field name, field data type and field description; the table life information comprises table data update cycle, table data retention policy and table access permission.

[0009] Further, the LLM guide label further comprises table typical use case, which is used to help the LLM associate the data table with a specific analysis task.

[0010] Further, the LLM guide table mode index comprises table name, field name and LLM guide label, which is used to quickly locate the related data table.

[0011] Further, the table mode information in S4 is information about the screened data table intercepted in the LLM guide table mode index.

[0012] Further, the S7 iteratively corrects the generated SQL statement through a SQL statement closed loop correction workflow, comprising: P1, capturing error information returned by the target database; P3, splicing the error information with the enhanced prompt word; P4, the large language model intelligent agent corrects the SQL statement according to the spliced error information and the enhanced prompt word; P5, performing verification on the corrected SQL statement, and if the verification is passed, the required SQL statement is obtained, otherwise step P1 is executed; Further, in the iterative closed-loop correction process, the error information can be iteratively spliced to enable the large model to obtain all error processes to facilitate the correction of the SQL statement.

[0013] The beneficial effects of the present application are: 1. The present application provides a large language model with rich information far beyond traditional methods through the "LLM guide table schema index" context retrieval mechanism, significantly improving accuracy; 2. The present application uses the innovative "execution verification and iterative closed-loop correction" mechanism to enable the system to have self-diagnosis and repair capabilities. When the first generated SQL statement is incorrect, the system will not fail directly, but can use the precise feedback of the database for autonomous learning and correction, forming an intelligent trial-and-error and iterative process, greatly enhancing the robustness of the system; 3. The present application integrates table retrieval, SQL statement generation, execution verification, error correction, and answer synthesis into an organic whole, realizing end-to-end conversion from natural language questions to natural language answers, and achieving high automation and intelligence: 4. The method framework of the present application is flexible, supports access to different types of large language models and various databases that follow the SQL standard, and through preprocessing and vectorization indexing of data, it can be easily expanded to support more databases and data tables, and has good scalability. BRIEF DESCRIPTION OF DRAWINGS

[0014] Figure 1 The flowchart of the natural language to SQL statement generation method based on the large language model intelligent agent of the present application. DETAILED DESCRIPTION

[0015] To make the purpose, technical scheme and advantages of the present application clearer, the following will be further described in conjunction with the drawings and examples.

[0016] As shown in the accompanying Figure 1 The present application provides a natural language to SQL statement generation method based on a large language model intelligent agent, which includes the following steps: S1, constructing an LLM guide table schema index for the target database, specifically including the following steps: S11, obtaining each data table in the target database; Read each data table from the target database, and further, if data needs to be read from various data sources, automatically create the corresponding table structure in the database.

[0017] S12, generating LLM guide labels describing the content of the data table according to the data table and upstream and downstream relationships; To assist subsequent LLM semantic retrieval, a text type LLM guide label describing the content of each data table is generated. The LLM guide label includes table basic information, table structure information, table life information, table permission information, table special value explanation information, table upstream and downstream relationship information, and table typical use cases.

[0018] Among them, the table basic information includes the table name (the actual name in the database, such as dim_user_info), the table business name (the business name easier to understand, such as "user dimension table"), the table function description (explain in simple language what this table is for, what core business entity it stores, and its role in business process or data analysis. For example: "Store all registered users' basic static information for user portrait analysis and circle selection."), the table data source (explain where the data comes from. Is it from a business database (such as MySQL), a log file, a third-party API, or processed from other data tables (ETL process). This is crucial for data tracing and problem troubleshooting.), the table responsible person (including the business responsible person and the technical responsible person. Business responsible person: the person responsible for the business meaning in the table. Technical responsible person: the person responsible for the technical implementation, performance and maintenance of the table. When users have questions about the data, they know who to ask.), the table creation time (the time when the table is first created), and the table last update time (the time when the table structure or data is last modified); The table structure information includes the field name (the name of the column, such as user_id, register_date), the field data type (the data type of the column, which determines how the data is stored and calculated), and the field description (a detailed explanation of the meaning of the field. This is the key to preventing data misreading. For example, the is_vip field needs to be clear whether it refers to "whether the current is a member" or "whether it has ever been a member"). The table life information includes the table data update cycle (the data update cycle, such as "real-time", "every hour", "every morning at 5 o'clock". This determines the timeliness of the data), the table data retention policy (how long will the data be retained? Will it be archived or deleted? For example: "Data is retained for 365 days, then it will be migrated to cold storage."), and the table access permission (a brief description of the access level of the table, such as "public", "limited to certain teams", "contains sensitive information, needs to apply for permission").

[0019] The table special value explanation information is used to explain the non-standard or special business meaning values that appear in some columns of the table. For example, 1 represents male and 2 represents female; the table upstream and downstream relationship information is used to describe the "blood relationship" of the current table in the entire data link, and the role is "navigation" and "tracing".

[0020] Furthermore, the LLM guidance label also includes typical use cases for tables (i.e., examples of what aspects of analysis they are typically used for), which helps the LLM associate data tables with specific analytical tasks, providing context for the LLM to understand business intent and improve the success rate of generating correct, efficient, and business-logical SQL statements.

[0021] S13. Using the embedding model, vectorize each data table and its LLM guidance label, and construct an LLM guidance table schema index.

[0022] Using an embedding model (for example, the open-source BGE model), vectorize two types of information: each data table and its LLM guidance label, and construct an LLM guidance table schema index. The LLM guidance table schema index contains table names, field names, and LLM guidance labels, used for quickly locating relevant data tables.

[0023] S2. Obtain the user's natural language question and extract keywords from the user's natural language question; Obtain the user's natural language question. For example, when the user inputs the natural language question "I want to know how many XXX brand products Xiao Zhao has sold since August 2024?", extract the keywords in the user's natural language question; for example, the keywords include "person: Xiao Zhao, verb: sell, date: August 2024, brand: XXX brand".

[0024] S3. Search the LLM guidance table schema index based on keywords and filter out relevant data tables; Based on the keywords of the user's natural language question, a search is performed in the LLM guidance table schema index to select the most likely relevant data tables.

[0025] S4. Integrate the table schema information and keywords of the filtered data tables into contextual information; Information about the most likely relevant data tables selected from the LLM guidance table schema index is extracted to form table schema information. In other words, table schema information is the information about the selected data tables extracted from the LLM guidance table schema index.

[0026] The table schema information of the selected data tables is integrated with the keywords of the user's natural language questions to form a detailed contextual information material.

[0027] S5. Combine contextual information with the user's natural language question to construct enhanced prompt words; The contextual information integrated in the previous step is combined with the user's natural language question to construct a rich and enhanced prompt word.

[0028] S6. Based on a large language model agent, generate SQL statements according to enhanced prompts: The enhanced prompts are submitted to a large language model agent (such as the Qwen-coder series) specifically designed for writing code, instructing it to generate SQL statements.

[0029] S7. Execute and verify the generated SQL statement in the sandbox environment. If the verification is successful, the required SQL statement is obtained. Otherwise, iterative closed-loop correction of the generated SQL statement is performed through the SQL statement closed-loop correction workflow and the number of corrections is recorded. Before verification, a sandbox containing the database needs to be built. The generated SQL statements are executed and verified in a secure and controlled environment to prevent data corruption (e.g., due to malicious programs). The sandbox execution environment is the cornerstone of verification, ensuring that the system can safely, quickly, and repeatedly execute and verify SQL without worrying about damaging the database. Successful verification yields the required SQL statements.

[0030] If the verification fails, it means that the generated SQL statement is incorrect and needs to be iteratively corrected through the SQL statement closed-loop correction workflow.

[0031] The generated SQL statements are iteratively corrected using a closed-loop SQL statement correction workflow, including the following steps: P1. Capture error messages returned by the target database during execution; P3. Combine the error message with the enhanced prompt; P4. Instruct the large language model agent to correct the SQL statement based on the concatenated error information and enhancement prompts; P5. Execute and verify the corrected SQL statement. If the verification is successful, the required SQL statement will be obtained; otherwise, execute step P1 and record the number of corrections.

[0032] Furthermore, during the iterative closed-loop correction process, error information can be iteratively pieced together to enable the large model to acquire all error processes (including syntax errors, logical deviations, and unexpected results) to facilitate the correction of SQL statements.

[0033] S8. Determine if the number of corrections has reached the threshold. If so, integrate all failed SQL statements and system error messages, and have the large language model output the defects in the user's natural language problem and modification suggestions.

[0034] In the iterative closed-loop correction process of S7, if the corrected SQL statement is successfully executed and verified, the required SQL statement is obtained. If the execution and verification fail and the number of corrections has reached the threshold, all failed SQL statements and system error messages are integrated, and the large language model outputs the defects in the user's natural language problem and modification suggestions.

[0035] In other words, after generating the SQL statement, it needs to be executed in the target database. If execution fails, the system will capture the error information returned by the database. Then, the system will create a "retry prompt" containing the error information, requesting the large language model to correct the SQL statement based on the reason for the failure. This closed-loop process of "generation-verification-correction" will iterate until the generated SQL statement executes successfully or a preset threshold (retry limit) is reached. Setting a maximum retry limit can prevent infinite loops in certain extreme cases.

[0036] The S8 process includes an intelligent exception handling procedure that is triggered when an SQL statement fails to execute multiple times and reaches the retry limit. First, the system integrates the SQL statements from the multiple failures with the system's error messages, requesting syntax hints and modification suggestions from the large language model. Next, the system will initiate exception handling and provide the user with the "SQL statement - error message - modification suggestions." If the user wishes to modify the statement and run it themselves, the system will provide a modification interface; otherwise, the system will terminate the process.

[0037] Furthermore, once the SQL statement is successfully executed (execution verification successful) and the data results are obtained, the user's original question, the successful SQL statement, and the retrieved data results are submitted to the large language model. The large language model is responsible for synthesizing and refining this structured information to generate a fluent and easy-to-understand natural language answer, which is then presented to the user.

[0038] Here is a simple application example: For example, there is a data form with the table name "QCXSSJ" and column names including XSPP, XSJG, RQ, JHJG, CPXH, and XSRY.

[0039] Step 1: Data preprocessing and index building. This requires generating an "LLM guide label table schema index" containing table names, column names, and LLM guide labels. LLM guide labels can be manually written, generated by the large model, or a combination of both. These LLM guide labels will serve as direct input to the subsequent large language model, used for intent recognition (i.e., determining whether the table is needed for subsequent data queries based on the user's question) and directly for generating SQL statements.

[0040] The LLM guidance label information includes a description of the table's purpose and a description of each column. The LLM guidance label table schema index contains both text and vector parts. The text part is as follows: There is a car sales table named "QCXSSJ". This table records all historical car sales data for the 4S store. All queries related to the sales volume of a car brand on a specific date can use this table (equivalent to a typical table use case). The column names in the table include: XSPP (string type), sales brand, including major well-known car brands; XSJG (floating-point type), sales price, ranging from 200,000 to 10,000,000; RQ (date type), sales date; JHJG (floating-point type), purchase price, ranging from 200,000 to 10,000,000; CPXH (string type), product model, a unique identifier for the product; XSRY (string type), salesperson, referring to store employees. The vector part is generated from the text part and the vector model. The text part is input into the vector model, and the output is a 1024-dimensional vector with a shape similar to: [0.23123, 0.213213, 0.458748, .....] Step 2: Receive the query request and perform multi-level contextual retrieval. When a user enters the natural language question, "I want to know how many XXX brand products Xiao Zhao has sold since August 2024?", the system needs to initiate a service to respond to the request.

[0041] Based on the user's natural language question and the index established in the first step, the system determines that the question requires a summary answer based on information from the "Automobile Sales Data" table. The system extracts the table schema information of the "Automobile Sales Data" table (including table name, column names, column definitions, LLM guidance labels, etc.). Simultaneously, the system extracted keywords from the user's question, including: Person: Xiao Zhao, Verb: Sell, Date: August 2024, Brand: XXX Brand. Finally, the table schema information, keyword information (including values ​​and types), and the user's question were integrated into a complete contextual document. An example of this context is as follows: A user asked: I want to know how many XXX brand products Xiao Zhao sold since August 2024? I will refer to the information in table QCXSSJ to generate the answer.

[0042] The following is the table schema information for this table: Car sales chart, chart name: QCXSSJ Column names include: XSPP string type, sales brands, including major well-known car brands. XSJG is a floating-point number type; the selling price ranges from 200,000 to 10,000,000. RQ date type, sales date, JHJG is a floating-point number type; the purchase price ranges from 200,000 to 10,000,000. CPXH, a string type, represents the product model, which is a unique identifier for the product. The string type is XSRY, representing a salesperson or store employee. The keywords in the user's question are: Time: August 2024; Brand: XXX brand; Verb: sell; Personnel: Xiao Zhao.

[0043] Please answer: I want to know how many XXX brand products Xiao Zhao sold since August 2024? Step 3: Context-enhanced SQL statement generation and loop closure correction The contextual information from step two is used as input to the large model, prompting the large model to generate the corresponding SQL statement and execute it. If execution is successful, it will return {“Product Quantity”:100}. If execution fails, it will continuously correct itself based on the error message and retry. If the number of retries exceeds the predetermined number, it will enter the exception handling section.

[0044] Step 4: Natural Language Answer Synthesis The user's question, SQL statement, and the successful return result from step three are submitted as input to the large model. The large model summarizes and answers: "Since August 2024, Xiao Zhao has sold a total of 100 products of brand XXX."

[0045] This invention provides a natural language to SQL statement generation method capable of accurately understanding database structure, perceiving data content, and possessing closed-loop self-correction capabilities, thereby significantly improving the accuracy of SQL statement generation and the overall availability of the system. This method constructs a multi-stage workflow with closed-loop feedback and retry capabilities. In summary, the method of this invention is mainly reflected in the following aspects: Multi-level contextual information retrieval: Upon receiving a user's natural language query, this method does not directly throw the problem at a large language model, but first performs contextual information retrieval. By constructing a table schema retrieval, based on the semantics of the user query, and through vector similarity calculation, it retrieves several candidate data tables most relevant to the query from multiple table schemas and their tag information in the database. Context-enhanced SQL statement generation: This involves combining the user's original query and the relevant table schema information retrieved in the first step into a rich, enhanced suggestion. This enhanced suggestion is then submitted to the large language model agent, instructing it to generate a preliminary SQL query statement. Execution verification and iterative closed-loop correction: This involves executing and verifying the SQL statements generated by the large language model, and iteratively correcting them based on the results. The specific correction process includes: SQL statement execution attempt: attempting to execute the SQL statement in the target database; closed-loop correction: if execution fails, the system automatically captures the error information returned by the database. Then, the original query, table schema, failed SQL statement, and specific error information are combined to construct a new "retry prompt," which is submitted again to the large language model, requesting it to correct the SQL statement based on the error information. This correction process can be repeated until the SQL statement executes successfully or the preset maximum number of retries is reached. Natural Language Answer Synthesis: After the SQL statement is successfully executed, the query results, along with the user's original question and the successfully executed SQL statement, are submitted to the large language model agent. The agent then synthesizes and refines the structured data results and generates a fluent and easy-to-understand natural language answer to return to the user.

[0046] This invention aims to address the technical problems of existing Text-to-SQL technologies based on large language models, such as inaccurate understanding of database schemas, insufficient perception of data content, and a lack of automatic error correction mechanisms when SQL statement generation is flawed, resulting in poor system robustness. This invention strives to provide a natural language to SQL statement generation method that can accurately understand database structure, perceive data content, and possess closed-loop self-correction capabilities, thereby significantly improving the accuracy of SQL statement generation and the overall usability of the system.

[0047] The above embodiments are merely preferred embodiments of the present invention and are not intended to limit the scope of protection of the present invention. Any changes made based on the design principles of the present invention, or any non-creative modifications, should fall within the scope of the present invention.

Claims

1. A method for generating natural language to SQL statements based on a large language model intelligent agent, characterized in that, It includes the following steps: S1. Construct an LLM guidance table schema index for the target database; S2. Obtain the user's natural language question and extract keywords from the user's natural language question; S3. Search the LLM guidance table schema index based on keywords and filter out relevant data tables; S4. Integrate the table schema information and keywords of the filtered data tables into contextual information; S5. Combine contextual information with the user's natural language question to construct enhanced prompt words; S6. Based on a large language model agent, generate SQL statements according to enhanced prompts: S7. Execute and verify the generated SQL statement in the sandbox environment. If the verification is successful, the required SQL statement is obtained. Otherwise, iteratively close-loop correct the generated SQL statement through the SQL statement closed-loop correction workflow and record the number of corrections. S8. Determine if the number of corrections has reached the threshold. If so, integrate all failed SQL statements and system error messages, and have the large language model output the defects in the user's natural language problem and modification suggestions.

2. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 1, characterized in that, S1, constructing an LLM-guided table schema index for the target database, includes the following steps: S11. Obtain the data tables in the target database; S12. Generate LLM guidance tags describing the content of the data table based on the data table and upstream and downstream relationships; S13. Using the embedding model, vectorize each data table and its LLM guidance label, and construct an LLM guidance table schema index.

3. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 2, characterized in that, The LLM guidance tags include basic table information, table structure information, table life information, table permission information, special value descriptions, and upstream and downstream relationship information.

4. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 3, characterized in that, The basic information of the table includes the table name, table business name, table function description, table data source, table owner, table creation time, and table last update time; the table structure information includes field names, field data types, and field descriptions; the table lifecycle information includes the table data update cycle, table data retention policy, and table access permissions.

5. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 3, characterized in that, The LLM guidance labels also include table typical use cases to help LLMs associate data tables with specific analysis tasks.

6. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 2, characterized in that, The LLM guidance table schema index contains table name, field name, and LLM guidance label, which is used to quickly locate the relevant data table.

7. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 2, characterized in that, The table schema information in S4 is information about the filtered data tables extracted from the LLM-guided table schema index.

8. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 1, characterized in that, The S7 process iteratively corrects the generated SQL statements using a closed-loop SQL statement correction workflow, including: P1. Capture error messages returned by the target database; P3. Combine the error message with the enhanced prompt; P4. Instruct the large language model agent to correct the SQL statement based on the concatenated error information and enhancement prompts; P5. Execute and verify the revised SQL statement. If the verification is successful, the required SQL statement will be obtained; otherwise, proceed to step P1.

9. The method for generating natural language to SQL statements based on a large language model intelligent agent according to claim 8, characterized in that, During the iterative closed-loop correction process, error information can be iteratively pieced together to enable the large model to capture all error processes, which is beneficial for the correction of SQL statements.

Images