RAG enhanced Text-to-SQL query method and system for large-scale database environment

Abstract

The invention discloses an RAG enhanced Text-to-SQL (Structured Query Language) query method and system for a large-scale database environment. The method comprises the following steps: constructing a vector database; based on the original query of the user, generating query enhancement description aiming at Schema recall and query enhancement description aiming at SQL (Structured Query Language) by utilizing LLM (Logistics Language Model); carrying out Schema recall and historical question and answer pair recall operations in a double-way parallel manner by utilizing an RAG technology and relying on the constructed vector database; based on query enhancement description, recalled Schema and historical question and answer pairs, generating an SQL query statement by using LLM in combination with RAG, and realizing Text-to-SQL conversion; the generated SQL query statement is subjected to post-processing optimization through a database interface, the post-processing optimization comprises grammar verification, performance optimization and error correction, the SQL query statement is combined for execution and feedback, efficient, accurate and reliable natural language query is achieved, and continuous optimization can be conducted through user feedback.

Description

Technical Field

[0001] The present application relates to the fields of software engineering and artificial intelligence technology, and in particular to a RAG-enhanced Text-to-SQL query method and system for large-scale database environments. Background Art

[0002] Converting natural language to executable SQL queries (Text-to-SQL) is a long-standing challenge in database applications. Its goal is to allow users to interact with databases through natural language without having to master the complexities of SQL syntax. However, due to the inherent ambiguity of natural language and the diversity of database structures, accurately converting natural language queries to SQL is not an easy task.

[0003] Early text-to-SQL systems were typically based on manually crafted rules, requiring complex grammatical and semantic analysis of natural language and mapping it to predefined SQL templates. However, these approaches rely on manually written complex semantic mapping rules, making it difficult to effectively handle the flexibility of natural language and the complexity of database structures, and exhibiting poor scalability. With the development of machine learning technology, researchers have attempted to use statistical models to learn the mapping relationship between natural language and SQL. Although these approaches reduce reliance on manual rules, they typically require large amounts of labeled data and still perform poorly when handling complex query scenarios (such as multi-table joins and nested queries).

[0004] In recent years, the emergence of Large Language Models (LLMs) has brought new breakthroughs to the Text-to-SQL field. LLMs, with their powerful semantic understanding capabilities, can effectively handle the ambiguity of natural language. Furthermore, by incorporating external knowledge (such as database schema information, data examples, and historical question-and-answer pairs), they further improve the accuracy of SQL generation. However, LLMs still face the following key challenges in practical applications:

[0005] Schema Management for Large-Scale Databases: Large-scale databases contain enormous amounts of schema information, making efficient access and utilization of this information a challenge. Simple static loading methods easily exceed the context length limit of LLM, resulting in low efficiency. The lack of a dynamic access mechanism makes it difficult to adapt to changes in the database structure.

[0006] 2. Understanding and converting complex queries: The complexity of natural language queries varies greatly, from simple single-table queries to complex query operations involving multi-table joins and nested subqueries. LLM may misunderstand query intent and misinterpret data table fields due to a lack of domain knowledge (such as the mapping relationship between business terms and fields, and data value constraints). Targeted LLM training will require retraining due to schema data transformations such as table structures, making the system more expensive to use.

[0007] 3. Automatic optimization and error correction of generated SQL: The autoregressive generation feature of LLM may produce syntax errors or semantic deviations. Even small errors can cause SQL execution failure. How to effectively detect and correct possible errors in SQL queries generated by LLM to ensure the syntactic correctness and executableness of the generated SQL queries is a major challenge.

[0008] 4. System controllability and continuous improvement: To achieve good results in practical applications, the Text-to-SQL system must be controllable and capable of continuous improvement. This means that the system needs to be able to automatically iterate based on user feedback to continuously improve query accuracy and stability. How to effectively use user feedback to guide system learning and improvement remains an urgent issue. Summary of the Invention

[0009] To address existing issues such as unsatisfactory query results in complex scenarios, low query efficiency, misjudgment of query intent, generated SQL syntax errors or semantic deviations, and inability to effectively leverage user feedback for continuous optimization, the present invention provides a RAG-enhanced Text-to-SQL query method for large-scale database environments. This method utilizes a vector database to implement two-way parallel recall of database schema information and historical question-answer pairs. Combined with query enhancement, SQL generation post-processing optimization, and a feedback closed-loop mechanism, this method achieves efficient, accurate, and reliable natural language querying, and can leverage user feedback for continuous optimization. The present invention also relates to a RAG-enhanced Text-to-SQL query system for large-scale database environments.

[0010] The present invention is achieved through the following technical solutions:

[0011] A RAG-enhanced Text-to-SQL query method for a large-scale database environment includes the following steps:

[0012] Vector database construction steps: including constructing a Schema vector database: based on the first prompt template, generating a standardized description of each Schema, inputting the generated standardized description into the large language model, outputting a summary text description of the Schema, and then using the vectorization model to convert the summary text description of the Schema into a vector representation, and storing it in the Schema vector database; and constructing a historical question-answer pair vector database: based on the natural language query description of each user input in the historical record and the corresponding SQL query statement that was finally successfully executed, constructing historical question-answer pairs, and storing them in the historical question-answer pair vector database;

[0013] Query enhancement step: Based on the second prompt template and the third prompt template, the natural language query description entered by the user is preprocessed to generate two independent enhanced descriptions. Through two independent large language model calls, the query enhanced description for schema recall and the query enhanced description for SQL generation are obtained respectively;

[0014] Two-way parallel recall: Using RAG technology and relying on the constructed vector database, schema recall and historical question-answer pair recall operations are carried out in two parallel ways. For schema recall, the query description enhanced for schema recall is used as input. RAG technology is combined with the language model to understand the query intent and perform similarity search in the schema vector database to locate and recall relevant schema information. For historical question-answer pair recall, the natural language query description entered by the user is converted into a vector representation using a vectorization model. RAG technology is used to retrieve similar historical question-answer pairs in the historical question-answer pair vector database based on the converted vector representation to locate and recall the historical question-answer pairs.

[0015] SQL query statement generation step: Based on the query enhancement description for schema recall, the query enhancement description generated for SQL, the recalled schema information, and the recalled historical question-answer pairs, a large language model is combined with the RAG enhancement mechanism to generate an SQL query statement suitable for a large-scale database environment according to the fourth prompt template, thereby achieving text-to-SQL conversion;

[0016] SQL query statement post-processing optimization step: The SQL query statements generated by the large language model are post-processed and optimized through the database interface, including syntax verification, performance optimization, and error correction. If an error is found, the error information is fed back to the large language model, and the process jumps to the SQL query statement generation step to regenerate the SQL query statement. The optimized SQL query statement is then verified through the database interface until the SQL query statement is error-free or the verification count threshold is reached.

[0017] SQL query statement execution and feedback steps: Execute the optimized SQL query statement and obtain the execution result information; receive user feedback on the execution result. If the feedback is an error result, generate feedback information based on the execution result information and feed it back to the large language model, jump to the SQL query statement generation step, and regenerate the SQL query statement; if the feedback is a successful result, construct historical question and answer pairs based on the natural language query description entered by the user and the optimized SQL query statement, and store them in the historical question and answer pair vector database.

[0018] Preferably, in the vector database construction step, the standardized description of each Schema generated is used to specify the output requirements of the large language model, and the standardized description of the Schema includes several combinations of output content length requirements, output content requirements, output language style requirements and output information source requirements.

[0019] Preferably, in the query enhancement step, based on the second Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for Schema recall; the second Prompt template is designed for the Schema recall task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform Schema recall query enhancement on the natural language query description input by the user by analyzing the core requirements and intentions of the user query, extracting key elements in the query, enriching the query content according to the extracted key elements, and generating enhanced query steps, and the output requirements include output format requirements, output content requirements, and output content length requirements.

[0020] Preferably, in the query enhancement step, based on the third Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for SQL generation; the third Prompt template is designed according to the SQL generation task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform query enhancement for SQL generation on the natural language query description input by the user by analyzing user intentions, extracting key elements in the query, decomposing and reconstructing the query, supplementing key information of the query, maintaining the user's original intention, and generating enhanced query steps, and the output requirements include output language style requirements, output content restrictions and output content length restrictions.

[0021] Preferably, in the SQL query statement generation step, the fourth prompt template is designed based on the integration requirements of SQL generation quality and vector database retrieval information in a large-scale database environment, including the following rules and requirements: generating a syntactically correct and valid query, ensuring the executability of the SQL query, explaining in detail the reasons why the query cannot be generated when the context information is insufficient, table and field usage guidelines, using flexible matching strategies when querying text fields, normalizing time-related query logic, correctly understanding and using aggregate functions, comprehensively handling the null value of fields, setting clear alias identifiers for result fields, and optimizing the readability and integrity of field values.

[0022] Preferably, in the SQL query statement post-processing optimization step, the SQL query statement generated by the large language model is syntax verified through the database interface. If a syntax error is found, the syntax error information is fed back to the large language model, and the process jumps to the SQL query statement generation step to regenerate the SQL query statement, thereby correcting the error. If there is no execution result within the preset maximum query execution time threshold, a performance optimization suggestion is generated and fed back to the large language model.

[0023] Preferably, in the SQL query statement post-processing optimization step, the performance optimization suggestions include at least one of the following: merging JOIN statements, using LIMIT to limit the result set, optimizing the use of indexes, and limiting the time range.

[0024] Preferably, in the SQL query statement execution and feedback step, the error result includes a syntax error, an execution error and an empty result. If the error result is a syntax error and / or an execution error, feedback information related to the diagnostic information is generated based on the execution result information and fed back to the large language model, so that the large language model optimizes the generation plan based on the feedback information and regenerates the SQL query statement; if the error result is an empty result, feedback information related to improvement suggestions is generated and fed back to the large language model, so that the large language model optimizes the generation plan based on the feedback information and regenerates the SQL query statement, wherein the improvement suggestions include suggestions for verifying table selection, suggestions for verifying field mapping, suggestions for checking field value specifications, suggestions for evaluating query conditions and suggestions for verifying data integrity.

[0025] A RAG-enhanced Text-to-SQL query system for large-scale database environments includes a vector database construction module, a query enhancement module, a two-way parallel recall module, an SQL query statement generation module, an SQL query statement post-processing optimization module, and an SQL query statement execution and feedback module, which are connected in sequence.

[0026] The vector database construction module includes a Schema vector database construction submodule and a historical question-answer pair vector database construction submodule. The Schema vector database construction submodule generates a standardized description of each Schema based on the first Prompt template, inputs the generated standardized description into the large language model, outputs a summary text description of the Schema, and then uses the vectorization model to convert the summary text description of the Schema into a vector representation, and stores it in the Schema vector database; the historical question-answer pair vector database construction submodule constructs historical question-answer pairs based on the natural language query description of each user input in the historical record and the corresponding SQL query statement that was finally successfully executed, and stores them in the historical question-answer pair vector database;

[0027] The query enhancement module pre-processes the natural language query description input by the user based on the second prompt template and the third prompt template, generates two independent enhanced descriptions, and obtains the query enhanced description for schema recall and the query enhanced description for SQL generation respectively through two independent large language model calls;

[0028] The dual-path parallel recall module utilizes RAG technology and relies on the constructed vector database to carry out Schema recall and historical question-answer pair recall operations in dual parallel paths. It includes a Schema recall submodule and a historical question-answer pair recall submodule. The Schema recall submodule uses the query description enhanced for Schema recall as input, utilizes RAG technology combined with the language model's understanding of the query intent, performs a similarity search in the Schema vector database, and then locates and recalls relevant Schema information. The historical question-answer pair recall submodule utilizes a vectorization model to convert the natural language query description input by the user into a vector representation, utilizes RAG technology and, based on the converted vector representation, retrieves similar historical question-answer pairs in the historical question-answer pair vector database, and then locates and recalls the historical question-answer pairs.

[0029] The SQL query statement generation module generates SQL query statements suitable for large-scale database environments based on the query enhancement description for schema recall, the query enhancement description for SQL generation, the recalled schema information, and the recalled historical question-answer pairs, using a large language model combined with a RAG enhancement mechanism, and according to the fourth prompt template, thereby achieving text-to-SQL conversion;

[0030] The SQL query statement post-processing optimization module is used to perform post-processing optimization on the SQL query statement generated by the large language model through the database interface, including syntax verification, performance optimization, and error correction. If an error is found, the error information is fed back to the large language model, and the module jumps to the SQL query statement generation module to regenerate the SQL query statement. The optimized SQL query statement is then verified through the database interface until the SQL query statement is error-free or a verification threshold is reached;

[0031] The SQL query statement execution and feedback module executes the optimized SQL query statement and obtains execution result information; receives user feedback on the execution result. If the feedback is an error result, feedback information is generated based on the execution result information and fed back to the large language model, jumping to the SQL query statement generation module to regenerate the SQL query statement; if the feedback is a successful result, a historical question and answer pair is constructed based on the natural language query description entered by the user and the optimized SQL query statement, and stored in a historical question and answer pair vector database.

[0032] Preferably, in the query enhancement module, based on the second Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for Schema recall; the second Prompt template is designed according to the Schema recall task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform Schema recall query enhancement on the natural language query description input by the user by analyzing the core requirements and intent of the user query, extracting key elements in the query, enriching the query content based on the extracted key elements, and generating an enhanced query step; the output requirements include output format requirements, output content requirements, and output content length requirements;

[0033] Based on the third Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for SQL generation; the third Prompt template is designed according to the SQL generation task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform query enhancement for SQL generation on the natural language query description input by the user by analyzing user intent, extracting key elements in the query, decomposing and reconstructing the query, supplementing key information of the query, maintaining the user's original intent, and generating enhanced query steps, and the output requirements include output language style requirements, output content restrictions, and output content length restrictions.

[0034] The beneficial effects of the present invention are as follows:

[0035] The present invention provides a RAG-enhanced Text-to-SQL query method for large-scale database environments. This method is also based on Retrieval Enhanced Generation (RAG) and a Large Language Model (LLM). It is used to implement efficient natural language queries in complex database environments using artificial intelligence technology. The method is particularly suitable for large-scale multi-type databases (which have massive amounts of data, diverse data types, and complex data structures and relationships). The method constructs a vector database, a Schema vector database and a historical question-answer pair vector database, generates a standardized description of each Schema based on a first Prompt template, inputs the generated standardized description into a large language model, outputs a summary text description of the Schema, and then uses a vectorized model to convert the summary text description of the Schema into a vector representation and store it in the Schema vector database. The standardized description of the Schema generated based on the first Prompt template can guide the large language model to output a summary text description of the Schema more accurately, and the output content length limit of the large language model can be added to the first Prompt template to prevent the summary text description of the output Schema from being lengthy. The vector database can be used to achieve efficient Schema similarity retrieval, avoid the inefficiency and context length limitation problems caused by static loading of Schema information, and thus improve query efficiency; based on each record in the historical record, The natural language query description entered by the user and the corresponding SQL query statement that is finally successfully executed are used to construct historical question-answer pairs and store them in the historical question-answer pair vector database. Storing historical question-answer pairs in the vector database can provide high-quality data support for the recall of historical question-answer pairs, thereby improving the accuracy of the SQL query language generated by the large language model; based on the second Prompt template and the third Prompt template, an enhanced prompt description of the corresponding natural language query description is generated. The two Prompt templates can be configured with output requirements (such as output format restrictions, output content restrictions, output content length restrictions, etc.). The output requirements of the second Prompt template focus on enhancing the recall of the Schema, and the output requirements of the third Prompt template focus on enhancing the query generated by SQL. The enhanced prompt description generated based on the Prompt template can guide the large language model to more accurately and efficiently output the enhanced query description for Schema recall and the enhanced query description for SQL generation;Utilize RAG technology, rely on the constructed vector database, carry out Schema recall and historical question and answer pair recall operations in two parallel ways, based on the query enhancement description for Schema recall, utilize RAG technology to retrieve relevant Schema information in the Schema vector database, and recall the most relevant Schema information, to enhance the generation capability of large language models, thereby improving the quality and accuracy of SQL generation, the dynamic Schema recall mechanism can adapt to the dynamic changes of database structure, without manual configuration update, and can quickly and accurately locate relevant information from massive Schema information, avoiding the problem that simple static loading method easily exceeds the LLM context length limit; for historical Historical question-answer pair recall uses a vectorized model to convert the natural language query description entered by the user into a vector representation. Using RAG technology, historical question-answer pairs similar to the converted vector representation can be retrieved from the historical question-answer pair vector database, and can be sorted by similarity. Several similar historical question-answer pairs can be recalled to enhance the generation capability of the large language model, thereby improving the quality and accuracy of SQL generation. Through this similarity search method based on the vector database, a dynamic acquisition mechanism is formed, which can dynamically obtain relevant Schema information according to the user's query requirements and adapt to changes in the database structure, rather than using a static loading method that lacks flexibility to complete the Schema management of large-scale databases; based on Sche Ma recall query enhancement description, query enhancement description generated for SQL, and the recall-related Schema information enhanced by RAG technology, and several similar historical question-answer pairs recalled, according to the fourth Prompt template, generate enhanced description for SQL generation, and input the large language model, and finally generate SQL query statements to achieve Text-to-SQL conversion, which can significantly improve the accuracy and reliability of the generated SQL query statements. The fourth Prompt template can enhance the output requirements of SQL generation based on experience configuration to guide the large language model to efficiently generate SQL query statements and overcome the understanding deviation caused by LLM's lack of domain knowledge; the large language model is generated through the database interface The generated SQL query statements are post-processed and optimized, including syntax verification, performance optimization, and error correction. If an error is found, the error information is fed back to the large language model, and the process jumps to the SQL query statement generation step to regenerate the SQL query statement. The optimized SQL query statement is then verified through the database interface until the SQL query statement is error-free or the verification count threshold is reached. Through verification, error discovery, and optimization, such a complete error handling and correction mechanism can identify possible syntax errors, performance bottlenecks, and other problems in SQL queries, and take corresponding measures to ensure the grammatical correctness and executableness of the generated SQL queries, thereby improving the robustness of the system and being able to cope with complex query scenarios;Execute the optimized SQL query statement, obtain the execution result information, and then receive user feedback on the execution result. If the feedback is a successful result, construct a historical question and answer pair based on the original natural language query description entered by the user and the optimized SQL query statement, and store it in the historical question and answer pair vector database for recalling similar historical question and answer pairs in the next query. Through continuous use by users, the number of historical question and answer pairs in the vector database can be continuously improved, thereby improving the recall similarity of similar historical question and answer pairs for new queries and improving the accuracy of queries. If the feedback is an incorrect result, generate improvement suggestions based on the execution result information and feed it back to the large language model, so that the large language model can optimize the generation plan based on the feedback information and regenerate the SQL query statement. This refined error feedback mechanism not only improves the success rate of a single query, but also allows the model to learn from errors and avoid repeating similar errors in subsequent queries. Through a closed-loop feedback mechanism involving SQL query generation, SQL query optimization, and feedback and optimization of the large language model, successful query cases are used for reinforcement learning, allowing the model to learn the correct handling methods. Error cases are analyzed to identify the causes and correct the model, thereby achieving continuous improvement and controllability of the solution. This not only improves the success rate of single queries, but also effectively utilizes user feedback to guide the learning and improvement of the large language model, avoiding the repetition of similar errors in subsequent queries and achieving continuous optimization. By building a vector database, enhancing schema recall, enhancing SQL generation, schema recall, recalling historical question-and-answer pairs, generating SQL queries, optimizing SQL queries, and providing feedback and optimization of the vector database and large language model, combined with RAG enhancement technology and a vectorized model, efficient, accurate, and reliable natural language queries can be achieved, and continuous optimization can be achieved using user feedback. This addresses the problems of traditional technologies in complex scenarios, such as unsatisfactory query results, low query efficiency, misjudgment of query intent, syntax errors or semantic deviations in generated SQL, and the inability to effectively utilize user feedback for continuous optimization.

[0036] The standardized description of the Schema generated by the present invention is used to specify the output requirements of the large language model. The Schema includes metadata information such as table structure, field type, constraints, and relationships. The standardized description of the Schema includes several combinations of output content length requirements, output content requirements, output language style requirements, and output information source requirements. By specifying the output requirements of the large language model according to requirements (such as the format and length of the output content, whether to use competent adjectives, whether to list potential use cases, etc.), the large language model can be guided to quickly and accurately generate a text description of the Schema.

[0037] The present invention is based on a second prompt template, which pre-processes the original natural language query description entered by the user to generate an enhanced description for schema recall. The second prompt template is designed for the schema recall task requirements, including processing step requirements, output requirements, etc., wherein the processing step requirements are mainly used to guide the large language model through the following steps to perform schema recall query enhancement on the natural language query description entered by the user: 1. Analyze and understand the core needs and intentions of the user query; 2. Extract key entities (such as ship name, company name, port, etc.), attributes (such as fuel consumption, voyage, ship type, etc.) and conditions (such as time range, comparison relationship, etc.) in the query; 3. Based on the extracted key elements, appropriately add relevant descriptions or synonyms to fully cover the user's needs; 4. Organize the above information into a clear and complete query. The output requirements include at least one of the following: output format restrictions (such as fluent Chinese expression, a paragraph, etc.), output content restrictions (such as only enhancing based on existing data and information, only including information related to the user's original intention, etc.) and output content length restrictions (such as the output word count is 100 to 200 words, etc.). By specifying processing step requirements and output requirements, etc., the large language model can be guided to quickly and accurately generate query-enhanced descriptions for Schema recall.

[0038] The present invention is based on a third prompt template, which pre-processes the original natural language query description input by the user to generate an enhanced description for SQL generation; the third prompt template is designed for the SQL generation task requirements, including processing step requirements, output requirements, etc.; wherein the processing step is mainly used to guide the large language model to enhance the natural language query description input by the user for SQL generation through the following steps: 1. Analyze user intent; 2. Extract key elements in the query (including entities, attributes, conditions, constraints, etc.); 3. Decompose complex queries into basic components, clarify the meaning of each component, and then reorganize; 4. Supplement information that may be missing but is crucial to the query as needed (for example, if the query design time range is not specified, consider defaulting to a reasonable time range); 5. Ensure that the user's original intent is not changed during the enhancement process; 6. Integrate the above elements to generate an enhanced query. The output requirements include at least one of the following: output language style requirements (such as fluent Chinese expression), output content restrictions (such as no need to include any SQL syntax or code), and output content length restrictions (such as the output word count is 100 to 200 words, etc.). By specifying processing step requirements and output requirements, etc., the large language model can be guided to quickly and accurately generate query enhancement descriptions for SQL generation.

[0039] The present invention is based on query enhancement descriptions for schema recall, query enhancement descriptions for SQL generation, recalled related schema information, and recalled several similar historical question-answer pairs, and utilizes a large language model in combination with a RAG enhancement mechanism to generate SQL query statements according to a fourth prompt template. The fourth prompt template is designed for the integration requirements of SQL generation quality and vector database retrieval information in a large-scale database environment. Its rules and requirements can be summarized into detailed settings based on experiments (such as generating a grammatically correct and valid query, ensuring the executable nature of the SQL query, explaining in detail the reasons why the query cannot be generated when context information is insufficient, table and field usage guidelines, adopting flexible matching strategies when querying text fields, normalizing time-related query logic, correctly understanding and using aggregate functions, comprehensively handling null value situations of fields, setting clear alias identifiers for result fields, optimizing the readability and integrity of field values, etc.), so as to guide the large language model to quickly and accurately generate SQL query statements.

[0040] Through specific query enhancement steps, the present invention optimizes schema understanding by enhancing query recall by understanding user query intent, extracting key elements, enriching query content, and generating enhanced queries. Furthermore, query enhancement for SQL generation optimizes understanding of SQL generation by understanding user intent, extracting key elements, decomposing and reconstructing queries, supplementing information, and maintaining the original intent. This helps more accurately grasp the intent of complex user queries. The historical question-answer pair recall operation in the dual-path parallel recall step uses the user's original query as input and retrieves historical question-answer pairs similar to the current query from a vector database, including the user query and the corresponding SQL query. These historical question-answer pairs can provide domain knowledge and correct query examples for the LLM, reducing the misunderstanding of query intent and misinterpretation of data table fields caused by the LLM's lack of domain knowledge. The SQL query statement generation step utilizes LLM using a specific fourth prompt template. This template can contain information such as database version, data table schema and field value samples, SQL generation requirements, table and field usage guidelines, time query logic specifications, aggregate function usage specifications, null value processing specifications, result field alias setting specifications, and field value optimization specifications. This helps LLM combine vector database schema information to accurately convert users' natural language queries into correct SQL queries.

[0041] The present invention can optimize the SQL query statements generated by the large language model, perform syntax verification on the SQL query statements generated by the large language model through the database interface, and if there are no syntax errors, no optimization is required; if a syntax error is found, an error prompt is generated based on the error information responded by the database and fed back to the large language model, so that the large language model can optimize the SQL query statement again based on the error prompt and correct the error; if the SQL query statement is executed in the database exceeding the preset value, optimization suggestions are generated based on the main factors affecting the SQL execution efficiency (such as too many JOIN statements, not using LIMIT to limit the result set, not using indexes, time fields not limiting the range, etc.) and fed back to the large language model to optimize the SQL query statement again. Such a complete error handling and correction mechanism improves the robustness of the system and can cope with more complex query scenarios.

[0042] In the present invention, the error results fed back by users may include syntax errors, execution errors and empty results. If the error result is a syntax error and / or execution error, some prompt words are added based on the SQL execution result information of the database, so that the large language model optimizes the generation plan according to the feedback information and regenerates the SQL; if the error result is an empty result, feedback information related to improvement suggestions (such as verifying table selection, verifying field mapping, checking field value specifications, evaluating query conditions and verifying data integrity, etc.) is generated, so that the large language model optimizes the generation plan according to the feedback information and regenerates the SQL. The multi-level error feedback mechanism is used to continuously optimize the SQL generation quality, which not only improves the success rate of a single query, but also enables the model to learn from errors and avoid repeating similar errors in subsequent queries.

[0043] The present invention also relates to a RAG-enhanced Text-to-SQL query system for a large-scale database environment. The system corresponds to the above-mentioned RAG-enhanced Text-to-SQL query method for a large-scale database environment and can be understood as a system that implements the above-mentioned RAG-enhanced Text-to-SQL query method for a large-scale database environment. The system includes a vector database construction module, a query enhancement module, a two-way parallel recall module, an SQL query statement generation module, an SQL query statement post-processing optimization module, and an SQL query statement execution and feedback module. The modules work together. The two-way parallel recall based on RAG technology can more comprehensively obtain relevant information of the original query. Combined with query enhancement, post-processing optimization, and execution and feedback, the generated SQL query statement is Compared with traditional technologies, it significantly improves the accuracy of SQL queries; improves query accuracy: improves query efficiency: uses a vector database (Schema vector database) to achieve efficient similarity retrieval, avoiding the inefficiency and context length limitation problems caused by static loading of Schema information; enhances system robustness: through the generation of SQL query statements, syntax verification of SQL query statements, and SQL optimization, such a complete error handling and correction mechanism, the robustness of the system is improved and can cope with more complex query scenarios; achieves continuous improvement: the feedback closed-loop mechanism can effectively use user feedback to guide system learning and improvement, and achieve continuous optimization of the system; adapts to changes in database structure: the dynamic Schema recall mechanism can adapt to dynamic changes in database structure without manual configuration updates. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] Figure 1 This is a flow chart of the RAG enhanced Text-to-SQL query method for a large-scale database environment of the present invention.

[0045] Figure 2 This is a schematic diagram of the RAG enhanced Text-to-SQL query method for a large-scale database environment of the present invention.

[0046] Figure 3 This is an example diagram of the preferred specific format of the Schema of the present invention.

[0047] Figure 4 This is an example diagram of the Schema data of the present invention.

[0048] Figure 5 This is a preferred example diagram of the first Prompt template of the present invention.

[0049] Figure 6 This is an example diagram of a preferred second Prompt template of the present invention.

[0050] Figure 7This is an example diagram of a preferred third Prompt template of the present invention.

[0051] Figure 8 This is a preferred example diagram of the first part of the fourth Prompt template of the present invention.

[0052] Figure 9 This is a preferred example diagram of the second part of the fourth Prompt template of the present invention.

[0053] Figure 10 This is a structural diagram of the RAG enhanced Text-to-SQL query system for large-scale database environments of the present invention. DETAILED DESCRIPTION

[0054] The present invention will be described below with reference to the accompanying drawings.

[0055] The present invention discloses a RAG-enhanced Text-to-SQL query method for large-scale database environments. The method aims to solve the problems of unsatisfactory query results, low query efficiency, misjudgment of query intent, generated SQL syntax errors or semantic deviations, and inability to effectively utilize user feedback for continuous optimization in complex scenarios in the existing technology. Based on RAG technology, the method uses a vector database to realize dual-path parallel recall of database schema information and historical question-answer pairs, and combines query enhancement, SQL generation post-processing optimization, and feedback closed-loop mechanism to achieve efficient, accurate, and reliable natural language query, and can utilize user feedback for continuous optimization. The method can be executed by a processor or an electronic device with processing capabilities, such as Figure 1 and Figure 2As shown in the figure, it can be understood as the architectural principle of building a vector database, query enhancement, information retrieval (two-way parallel recall), SQL generation, post-processing, feedback and optimization. First, a Schema vector database and a historical question-answer pair vector database are built. Then, the original query input by the user is enhanced for Schema recall to obtain a query enhancement description for Schema recall, and for SQL generation to obtain a query enhancement description for SQL generation. Then, using RAG technology, relying on the constructed vector database, the Schema recall and historical question-answer pair recall operations are carried out in two parallel ways to recall the relevant Schema information and historical question-answer pairs respectively. Then, LLM is used in combination with the RAG enhancement mechanism and The query enhancement description of the schema recall obtained above, the query enhancement description generated for SQL, the recalled related schema information and the historical question and answer pairs are used to generate SQL query statements suitable for large-scale database environments, realize text-to-SQL conversion, and then perform post-processing optimization on the generated SQL through the database interface. If an error is found, the SQL is optimized according to the error information and then optimized until there is no error or the number of optimizations reaches the upper limit. Finally, the SQL is executed and user feedback is received. If the user feedback is successful, a question and answer pair is generated according to the original query information and SQL and stored in the historical question and answer pair database. If the user feedback shows that the execution fails, the LLM is optimized according to the reason for the failure and the SQL is regenerated. The present invention uses a vector database to achieve efficient similarity retrieval, avoiding the inefficiency and context length limitation problems caused by static loading of schema information. Through a dual-path parallel recall mechanism, query-related information can be fully obtained. Combined with query enhancement and post-processing optimization, the accuracy of SQL queries is significantly improved. The perfect error handling and correction mechanism improves the robustness of the system and can cope with more complex query scenarios. The feedback closed-loop mechanism can effectively use user feedback to guide the learning and improvement of the system, thereby achieving continuous optimization of the system. Specifically, the method includes the following steps:

[0056] 1. Vector database construction steps: including building a Schema vector database: based on the first Prompt template, generating a standardized description of each Schema, and inputting the generated standardized description into the large language model, outputting a summary text description of the Schema, and then using the vectorization model to convert the summary text description of the Schema into a vector representation, and storing it in the Schema vector database; and building a historical question and answer pair vector database: based on the natural language query description of each user input in the historical record and the corresponding SQL query statement that was finally successfully executed, building historical question and answer pairs, and storing them in the historical question and answer pair vector database.

[0057] like Figure 3As shown, the original Schema information of the embodiment of the present invention is stored in a structured format and contains rich semantic information, such as field descriptions (such as Figure 3 Field 1 description, unit description, business meaning supplement, etc. in Table 3), data examples (such as the sample data section of the table in Table 3), and primary key information (such as the primary key field description and primary key design description in Table 3), etc. Figure 4 The Schema storage information of the database table sdc_lcb.teyun_2023_ship is shown, which records the key characteristics and attribute data of the ship, including ship identification information, physical parameters, technical specifications and operational information, which are used for fleet management, asset evaluation and compliance supervision. In order to achieve accurate Schema recall, each Schema will be converted into a vector representation and stored in the vector database: First, based on the first prompt template (such as Figure 5 As shown in the figure, the original Schema information is filled into the template to generate a standardized description of the Schema; the generated standardized description is then input into the LLM, and the LLM generates a summary text description of the Schema based on the guidance information in the standardized description; finally, a vectorization model (such as the Sentence-BERT model) is used to convert the summary text description of the Schema into a vector representation and store it in the Schema vector database.

[0058] For example, Figure 5 The first prompt template (i.e., the Schema prompt template) shown here uses schema information to replace {Table_Schema} in the template to generate a standardized description of the schema. The present invention does not limit the specific format of the first prompt template. Output requirements can include several combinations of format, content, language style, and information source. Format can include output content length requirements, content indicates output content requirements, language style indicates output language style requirements, and information source indicates output information source requirements.

[0059] For example, Figure 4 The schema information of the database table sdc_lcb.teyun_2023_ship shown and Figure 5Taking the first prompt template shown as an example, LLM outputs the following information: "The sdc_lcb.teyun_2023_ship table is a comprehensive ship information database with 49 fields covering the ship's identification information, physical characteristics, technical parameters and operational details. The table uses mmsi as the primary key and records basic information such as ship name, IMO number, call sign, as well as physical characteristics such as gross tonnage, deadweight tonnage, and size. The table uses a multi-level classification system to describe the ship type and provides detailed main and auxiliary engine information. Some fields include both Chinese and English versions, which enhances the usability of the data. This data can be used for various purposes such as fleet management, market analysis, performance evaluation, and compliance inspection. Through this table, users can perform various types of data exploration and analysis, such as ship details query, fleet composition analysis, technical parameter comparison, and ship age analysis. It is worth noting that some fields may have null values or special values, which may require additional processing when used."

[0060] 2. Query enhancement step: Based on the second and third prompt templates, the natural language query description entered by the user is preprocessed to generate two independent enhanced descriptions. Through two independent large language model calls, the query enhanced description for schema recall and the query enhanced description for SQL generation are obtained respectively, to improve the accuracy of subsequent schema recall and SQL generation.

[0061] Query enhancement for Schema recall: The embodiment of the present invention can pre-process the natural language query description input by the user to generate an enhanced description for Schema recall based on the second Prompt template (i.e., the query enhancement Prompt template for Schema recall). The second Prompt template is designed for the Schema recall task requirements and includes: processing step requirements, output requirements, etc.; wherein, the processing step requirements are used to guide the LLM to perform Schema recall query enhancement on the natural language query description input by the user by analyzing the core requirements and intentions of the user query, extracting key elements in the query, enriching the query content according to the extracted key elements, and generating enhanced query steps. The output requirements include several combinations of output format requirements, output content requirements, and output content length requirements. The query enhancement step for Schema recall aims to optimize the understanding of the Schema. Its input is the user's original query, and its output is an enhanced query that is easier to Schema recall.

[0062] For example, Figure 6The second Prompt template shown uses the natural language query description input by the user to replace {User_Query} in the template to generate an enhanced description for Schema recall. The present invention does not limit the specific format of the second Prompt template. The processing steps include understanding the intention, extracting key elements, enriching the query content and generating enhanced queries. Understanding the intention means analyzing the core needs and intentions of the user query, extracting key elements means extracting key elements in the query, enriching the query content means enriching the query content according to the extracted key elements, and generating an enhanced query means generating an enhanced query. Figure 6 As shown, the output requirements include format, length, content, etc., where format represents the output format requirement, length represents the output content length requirement, and content represents the output content requirement.

[0063] For example, taking "Query all ports visited by the PRESIDENT KENNEDY in the past 12 months and calculate the berthing time at each port" as the user's original query example, the output query enhancement description for Schema recall is: "Please provide all port call information of the ship named 'PRESIDENT KENNEDY' in the past 12 months, including the specific name, arrival time and departure time of each port. In addition, please calculate the actual berthing time (in hours) of the ship at each port of call. If possible, please also list the order of each port of call to understand the ship's sailing route. In addition, if there are any abnormally long berthing times (for example, more than 7 days), please mark them specifically and explain the possible reasons (such as maintenance, waiting for cargo, etc.). This information will help analyze the operational efficiency and port usage of the PRESIDENT KENNEDY ship."

[0064] Query enhancement for SQL generation: The embodiment of the present invention can pre-process the natural language query description input by the user based on the third Prompt template (i.e., the query enhancement Prompt template for SQL generation) to generate an enhanced description for SQL generation. The third Prompt template is designed for the SQL generation task requirements and includes: processing step requirements, output requirements, etc.; wherein, the processing step requirements are used to guide the LLM to perform query enhancement for SQL generation on the natural language query description input by the user by analyzing the user's intention, extracting key elements in the query, decomposing and reconstructing the query, supplementing the key information of the query, maintaining the user's original intention, and generating enhanced query steps. The output requirements include several combinations of output language style requirements, output content restrictions, and output content length restrictions. The query enhancement step for SQL generation is intended to optimize the understanding of SQL generation and locate named entities. Its input is the user's original query, and its output is an enhanced query that is easier to generate SQL.

[0065] For example, Figure 7 The third Prompt template shown uses the natural language query description input by the user to replace {User_Query} in the template to generate an enhanced description for SQL generation. The present invention does not limit the specific format of the third Prompt template. The processing steps may include understanding user intention, extracting key elements, decomposing and reconstructing the query, supplementing information, maintaining the original intention, and generating an enhanced query. Understanding intention means analyzing user intention, extracting key elements means extracting key elements in the query, supplementing information means supplementing key information of the query, and maintaining original intention means maintaining the original intention. Figure 7 As shown, the output language style requirements include: enhanced queries should use natural, fluent Chinese expressions. Output content restrictions include: no SQL syntax or code is required, only natural language queries are required; do not reference specific database tables or field names, but use common domain terminology; ensure that the enhanced query helps the LLM accurately understand and generate the corresponding SQL query; and only return the enhanced query.

[0066] For example, taking "What is the annual fuel consumption of COSCO Pacific" as the user's original query example, the output query enhancement description generated for SQL is: "Please query the total fuel consumption of the ship named 'COSCO Pacific' in the past year."

[0067] 3. Two-way parallel recall steps: Using RAG technology and relying on the constructed vector database, schema recall and historical question and answer pair recall operations are carried out in two parallel ways. For schema recall, the query description enhanced for schema recall is used as input. RAG technology is combined with the language model to understand the query intent, and a similarity search is performed in the schema vector database to locate and recall relevant schema information. For historical question and answer pair recall, the natural language query description entered by the user is converted into a vector representation using a vectorized model. RAG technology is used to retrieve similar historical question and answer pairs in the historical question and answer pair vector database based on the converted vector representation to locate and recall historical question and answer pairs.

[0068] Preferably, the vectorized model can be an Embedding model.

[0069] The embodiment of the present invention can use the query enhancement description for schema recall obtained by the above method, and use RAG technology combined with the language model to understand the query intent, to retrieve relevant schema information in the schema vector database, such as table name, field name, data type, field description, field sample value, etc., and then locate and recall the most relevant schema information to enhance the LLM generation capability, thereby improving the quality and accuracy of SQL generation. The dynamic schema recall mechanism can adapt to dynamic changes in the database structure without the need for manual configuration updates. Using RAG technology and based on the converted vector representation, similar historical question and answer pairs, such as user queries and corresponding SQL queries, are retrieved in the historical question and answer pair vector database, and the similarity of several retrieved similar historical question and answer pairs is sorted from high to low. A preset historical question and answer pair recall threshold (such as 3, 4, 5, etc.) is selected from the sorted historical question and answer pairs from front to back for recall, thereby improving the quality and accuracy of SQL generation.

[0070] For example, taking "Query the ports of call and berthing time of PRESIDENT KENNEDY in the past 12 months" as the user's original query example, the original queries among the five most relevant historical question-answer pairs are: "Query the most recent anchorage time of COSCO Pacific", "Count the number of loading and unloading times of COSCO Pacific in the past three months and the corresponding ports", "Query the sailing time of COSCO Pacific's last voyage", "Help me check the current status of ships currently sailing from Yangshan Port to Long Beach Port", and "How long was COSCO Pacific's last voyage in port?"

[0071] Fourth, SQL query generation: Based on the query enhancement description for schema recall, the query enhancement description for SQL generation, the recalled schema information, and the recalled historical question-answer pairs, a large language model combined with the RAG enhancement mechanism is used to generate SQL query statements suitable for large-scale database environments according to the fourth prompt template, achieving text-to-SQL conversion. This step is the core part of generating high-quality SQL.

[0072] This embodiment of the present invention replaces {User_Query}, {Table_Schema}, and {NL_QL_Pairs} in the fourth prompt template (i.e., the SQL generation prompt template) with an enhanced query description for SQL generation, relevant schema information enhanced for recall using RAG technology, and several similar historical question-answer pairs. This generates an enhanced generation description that can be used to guide the LLM to enhance the generation of SQL query statements. This enhanced generation description is then input into the LLM, ultimately outputting the SQL query statement. This significantly improves the accuracy and reliability of the generated SQL query statements. The fourth prompt template can be configured based on experience to enhance the output requirements for SQL generation, guiding the LLM to efficiently generate SQL query statements.

[0073] For example, Figure 8 and Figure 9 A preferred fourth Prompt template is shown. The present invention does not limit the specific format of the fourth Prompt template, and may include data tables and field value samples, requirements, NL-SQL pair samples, response formats, and questions, etc., wherein the data tables and field value samples represent relevant Schema information for enhanced recall using RAG technology, the requirements represent output rules and requirements for guiding LLM (including: generating a syntactically correct and valid query, ensuring the executableness of SQL queries, explaining in detail why queries cannot be generated when context information is insufficient, table and field usage guidelines, adopting flexible matching strategies when querying text fields, normalizing time-related query logic, correctly understanding and using aggregate functions, comprehensively handling null values of fields, setting clear alias identifiers for result fields, optimizing the readability and integrity of field values, etc.), the NL-SQL pair samples represent several similar historical question-answer pairs for enhanced recall using RAG technology, and the questions represent enhanced descriptions of queries generated for SQL.

[0074] 5. SQL query statement post-processing optimization step: The SQL query statements generated by the large language model are post-processed and optimized through the database interface, including syntax verification, performance optimization, and error correction. Among them, syntax verification: checks whether the syntax of the SQL query is correct; performance optimization: optimizes the SQL query, such as adding indexes, rewriting queries, etc., to improve query efficiency; error correction: if there are errors in the SQL query, try to automatically correct the errors; if an error is found, the error information is fed back to the large language model, and the process jumps to the SQL query statement generation step to regenerate the SQL query statement. The optimized SQL query statement is then verified through the database interface until the SQL query statement has no errors or the verification times threshold is reached.

[0075] The embodiment of the present invention can optimize the SQL query statements generated by the LLM. Through the database interface, the SQL query statements generated by the LLM are post-processed and optimized (including syntax verification, performance optimization, error correction, etc.). If a syntax error is found, an error prompt is generated based on the error information responded by the database and fed back to the LLM. The system then jumps to the SQL query statement generation step and regenerates the SQL query statement, thereby achieving error correction. If the SQL query statement execution in the database exceeds a preset value (such as 10s, 11s, or 12s), the database-side query operation is actively canceled to avoid resource waste. Performance optimization suggestions are generated based on the main factors affecting SQL execution efficiency (such as too many JOIN statements, failure to use LIMIT to restrict the result set, failure to use indexes, and unrestricted time fields). The suggestions are fed back to the LLM, and the system jumps to the SQL query statement generation step and regenerates the SQL query statement until a verification threshold (such as 4, 5, or 6 times) is reached, thereby achieving performance optimization. This comprehensive error handling and correction mechanism improves the robustness of the system and can cope with more complex query scenarios.

[0076] 6. SQL query statement execution and feedback step: Execute the optimized SQL query statement and obtain the execution result information; receive user feedback on the execution result. If the feedback is an error result, generate feedback information based on the execution result information and feed it back to the large language model, jump to the SQL query statement generation step, and regenerate the SQL query statement; if the feedback is a successful result, construct historical question and answer pairs based on the natural language query description entered by the user and the optimized SQL query statement, and store them in the historical question and answer pair vector database.

[0077] After executing an optimized SQL query, the embodiment of the present invention stores the executed SQL query and the execution results (including successful and error results), and then receives user feedback on the execution results. User feedback includes both good and bad cases. Good cases are used to reinforce learning, while bad cases are used to correct errors in the LLM large language model. If the user reports a successful execution, the optimized SQL query is accurate. The original natural language query description entered by the user and the optimized SQL query are then constructed into a historical question-answer pair and stored in a database of historical question-answer pair vectors. If the user reports an execution error or syntax error, diagnostic feedback (i.e., LLM guidance information including the execution result) is generated based on the stored execution result information and fed back to the LLM. The LLM then optimizes the generated solution based on the feedback and returns to the SQL query generation step for regeneration and optimization. If the user reports an empty execution result, feedback with improvement suggestions (such as verifying table selection, verifying field mappings, checking field value specifications, evaluating query conditions, and verifying data integrity) is generated and fed back to the LLM. The LLM then optimizes the generated solution based on the feedback and returns to the SQL query generation step for regeneration and optimization. This multi-level error feedback mechanism continuously optimizes SQL generation quality, not only improving the success rate of individual queries but also enabling the LLM to learn from its errors and avoid repeating similar mistakes in subsequent queries.

[0078] Based on the same inventive concept, one or more embodiments of this specification also provide a RAG-enhanced Text-to-SQL query system for a large-scale database environment. Since the principles of the problems solved by the RAG-enhanced Text-to-SQL query system for a large-scale database environment are similar to those of the aforementioned RAG-enhanced Text-to-SQL query method for a large-scale database environment, the implementation of the RAG-enhanced Text-to-SQL query system for a large-scale database environment can refer to the aforementioned implementation of the RAG-enhanced Text-to-SQL query method for a large-scale database environment, and the repeated parts will not be repeated.

[0079] Figure 10 This is a structural diagram of a RAG enhanced Text-to-SQL query system for a large-scale database environment provided in one or more embodiments of this specification. Figure 10 As shown, the system includes a vector database construction module 101, a query enhancement module 102, a dual-path parallel recall module 103, an SQL query statement generation module 104, an SQL query statement post-processing optimization module 105, and an SQL query statement execution and feedback module 106, which are connected in sequence.

[0080] The vector database construction module 101 includes constructing a Schema vector database: based on the first Prompt template, generating a standardized description of each Schema, inputting the generated standardized description into the large language model, outputting a general text description of the Schema, and then using the vectorization model to convert the general text description of the Schema into a vector representation, and storing it in the Schema vector database; and constructing a historical question and answer pair vector database: based on the natural language query description of each user input in the historical record and the corresponding SQL query statement that was finally successfully executed, constructing a historical question and answer pair, and storing it in the historical question and answer pair vector database.

[0081] The query enhancement module 102 preprocesses the natural language query description input by the user based on the second prompt template and the third prompt template, generates two independent enhanced descriptions, and obtains the query enhanced description for schema recall and the query enhanced description for SQL generation through two independent large language model calls.

[0082] The dual-path parallel recall module 103 uses RAG technology and relies on the constructed vector database to perform dual-path parallel Schema recall and historical question and answer pair recall operations. For Schema recall, the query description enhanced for Schema recall is used as input, and RAG technology is combined with the language model to understand the query intent, and a similarity search is performed in the Schema vector database to locate and recall relevant Schema information; for historical question and answer pair recall, the natural language query description input by the user is converted into a vector representation using a vectorization model, and RAG technology is used to retrieve similar historical question and answer pairs in the historical question and answer pair vector database based on the converted vector representation to locate and recall the historical question and answer pairs.

[0083] The SQL query statement generation module 104, based on the query enhancement description for schema recall, the query enhancement description for SQL generation, the recalled schema information and the recalled historical question and answer pairs, uses a large language model combined with the RAG enhancement mechanism to generate SQL query statements suitable for large-scale database environments according to the fourth prompt template, thereby realizing text-to-SQL conversion.

[0084] The SQL query statement post-processing optimization module 105 is used to perform post-processing optimization on the SQL query statements generated by the large language model through the database interface, including syntax verification, performance optimization, and error correction. If an error is found, the error information is fed back to the large language model, and the process jumps to the SQL query statement generation step to regenerate the SQL query statement. The optimized SQL query statement is then verified through the database interface until the SQL query statement is error-free or the verification count threshold is reached.

[0085] The SQL query statement execution and feedback module 106 is used to execute the optimized SQL query statement and obtain execution result information; receive user feedback on the execution result. If the feedback is an error result, feedback information is generated based on the execution result information and fed back to the large language model, jumping to the SQL query statement generation step to regenerate the SQL query statement; if the feedback is a successful result, historical question and answer pairs are constructed based on the natural language query description entered by the user and the optimized SQL query statement, and stored in the historical question and answer pair vector database.

[0086] Furthermore, in the query enhancement module 102, based on the second Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for Schema recall. The second Prompt template is designed according to the requirements of the Schema recall task and includes processing step requirements and output requirements. The processing step requirements are used to guide the large language model to perform Schema recall query enhancement on the natural language query description input by the user by analyzing the core requirements and intent of the user query, extracting key elements in the query, enriching the query content based on the extracted key elements, and generating an enhanced query step. The output requirements include output format requirements, output content requirements, and output content length requirements.

[0087] Based on the third Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for SQL generation; the third Prompt template is designed according to the SQL generation task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform query enhancement for SQL generation on the natural language query description input by the user by analyzing user intent, extracting key elements in the query, decomposing and reconstructing the query, supplementing key information of the query, maintaining the user's original intent, and generating enhanced query steps, and the output requirements include output language style requirements, output content restrictions, and output content length restrictions.

[0088] The present invention provides a RAG-enhanced Text-to-SQL query system for large-scale database environments. The system utilizes a vector database to achieve efficient similarity retrieval, avoiding the inefficiency and context length limitations caused by static loading of schema information. The historical question-answer pair vector database can improve the similarity of recalled historical question-answer pairs, thereby improving query accuracy. The dual-path parallel recall mechanism of the dual-path parallel recall module enables more comprehensive acquisition of relevant information. The dynamic schema recall mechanism can adapt to dynamic changes in database structure without the need for manual configuration updates. The combination of query enhancement and post-processing optimization of SQL query statements significantly improves the accuracy of SQL queries. The post-processing optimization of SQL query statements adopts a multi-level verification, correction, and optimization mechanism to ensure that the generated SQL statements are not only correct and executable but also have good performance. The entire post-processing optimization process is based on a retry mechanism, allowing multiple generation-verification-correction-optimization cycles within a preset number of attempts, each time improving query quality and performance based on the previous failure experience. The system stores the original user SQL queries that ultimately executed successfully and the optimized SQL information in a database of historical question-answer pair vectors. These historical question-answer pairs are then incorporated into the LLM's prompts as sample learning examples in the next query, improving the accuracy of SQL generation. Through structured prompt templates, successful cases are formatted into standardized example pairs, enabling the LLM to clearly understand the mapping between question patterns and SQL solutions. The system also explicitly records recalled similar questions, facilitating debugging and analyzing the system's retrieval performance. This learning mechanism based on successful cases greatly enhances the LLM's understanding of domain-specific query patterns, enabling it to better handle new, similar queries. This closed-loop feedback mechanism, encompassing SQL query generation, optimization, and LLM feedback and optimization, not only improves the success rate of individual queries but also effectively leverages user feedback to guide LLM's learning and improvement, avoiding similar errors in subsequent queries and achieving continuous optimization. Furthermore, a comprehensive error handling and correction mechanism enhances the system's robustness, enabling it to cope with complex query scenarios.

[0089] It should be noted that the specific embodiments described above can enable those skilled in the art to more fully understand the present invention, but do not limit the present invention in any way. Therefore, although this specification has described the present invention in detail with reference to the drawings and embodiments, those skilled in the art should understand that the present invention can still be modified or replaced with equivalents. In short, all technical solutions and improvements that do not depart from the spirit and scope of the present invention should be included in the scope of protection of the patent for the present invention.

Claims

1. A RAG enhanced Text-to-SQL query method for large-scale database environments, characterized in that: The following steps are involved: Vector database construction steps: including constructing a Schema vector database: based on the first prompt template, generating a standardized description of each Schema, inputting the generated standardized description into the large language model, outputting a summary text description of the Schema, and then using the vectorization model to convert the summary text description of the Schema into a vector representation, and storing it in the Schema vector database; and constructing a historical question-answer pair vector database: based on the natural language query description of each user input in the historical record and the corresponding SQL query statement that was finally successfully executed, constructing historical question-answer pairs, and storing them in the historical question-answer pair vector database; Query enhancement step: Based on the second prompt template and the third prompt template, the natural language query description entered by the user is preprocessed to generate two independent enhanced descriptions. Through two independent large language model calls, the query enhanced description for schema recall and the query enhanced description for SQL generation are obtained respectively; Two-way parallel recall: Using RAG technology and relying on the constructed vector database, schema recall and historical question-answer pair recall operations are carried out in two parallel ways. For schema recall, the query description enhanced for schema recall is used as input. RAG technology is combined with the language model to understand the query intent and perform similarity search in the schema vector database to locate and recall relevant schema information. For historical question-answer pair recall, the natural language query description entered by the user is converted into a vector representation using a vectorization model. RAG technology is used to retrieve similar historical question-answer pairs in the historical question-answer pair vector database based on the converted vector representation to locate and recall the historical question-answer pairs. SQL query statement generation step: Based on the query enhancement description for schema recall, the query enhancement description generated for SQL, the recalled schema information, and the recalled historical question-answer pairs, a large language model is combined with the RAG enhancement mechanism to generate an SQL query statement suitable for a large-scale database environment according to the fourth prompt template, thereby achieving text-to-SQL conversion; SQL query statement post-processing optimization step: The SQL query statements generated by the large language model are post-processed and optimized through the database interface, including syntax verification, performance optimization, and error correction. If an error is found, the error information is fed back to the large language model, and the process jumps to the SQL query statement generation step to regenerate the SQL query statement. The optimized SQL query statement is then verified through the database interface until the SQL query statement is error-free or the verification count threshold is reached. SQL query statement execution and feedback step: Execute the optimized SQL query statement to obtain execution result information; receive user feedback on the execution result. If the feedback is an error result, generate feedback information based on the execution result information, feed it back to the large language model, jump to the SQL query statement generation step, and regenerate the SQL query statement; If the feedback is a successful result, a historical question-answer pair is constructed based on the natural language query description entered by the user and the optimized SQL query statement, and stored in the historical question-answer pair vector database.

2. The method according to claim 1, characterized in that In the vector database construction step, the standardized description of each Schema generated is used to specify the output requirements of the large language model. The standardized description of the Schema includes several combinations of output content length requirements, output content requirements, output language style requirements, and output information source requirements.

3. The method according to claim 1, characterized in that In the query enhancement step, based on the second Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for Schema recall; the second Prompt template is designed according to the Schema recall task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform Schema recall query enhancement on the natural language query description input by the user by analyzing the core requirements and intentions of the user query, extracting key elements in the query, enriching the query content according to the extracted key elements, and generating enhanced query steps; the output requirements include output format requirements, output content requirements, and output content length requirements.

4. The method according to claim 1, wherein In the query enhancement step, based on the third Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for SQL generation; the third Prompt template is designed according to the SQL generation task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform query enhancement on the natural language query description input by the user by analyzing user intent, extracting key elements in the query, decomposing and reconstructing the query, supplementing key information of the query, maintaining the user's original intent, and generating an enhanced query step, and the output requirements include output language style requirements, output content restrictions, and output content length restrictions.

5. The method according to any one of claims 1 to 4, characterized in that In the SQL query statement generation step, the fourth prompt template is designed to meet the requirements of integrating SQL generation quality and vector database retrieval information in a large-scale database environment, including the following rules and requirements: generating a syntactically correct and valid query, ensuring the executable nature of the SQL query, providing a detailed explanation of the reasons why the query cannot be generated when context information is insufficient, providing a guide for using tables and fields, adopting a flexible matching strategy when querying text fields, standardizing time-related query logic, correctly understanding and using aggregate functions, comprehensively handling null value situations in fields, setting clear alias identifiers for result fields, and optimizing the readability and integrity of field values.

6. The method according to any one of claims 1 to 4, characterized in that In the SQL query statement post-processing optimization step, the SQL query statement generated by the large language model is syntax-verified through the database interface. If a syntax error is found, the syntax error information is fed back to the large language model, and the process jumps to the SQL query statement generation step to regenerate the SQL query statement, thereby correcting the error. If no execution result is obtained within the preset maximum query execution time threshold, a performance optimization suggestion is generated and fed back to the large language model.

7. The method according to claim 6, characterized in that In the SQL query statement post-processing optimization step, the performance optimization suggestions include at least one of the following: merging JOIN statements, using LIMIT to limit the result set, optimizing the use of indexes, and limiting the time range.

8. The method according to any one of claims 1 to 4, characterized in that In the SQL query statement execution and feedback step, the error results include syntax errors, execution errors and empty results. If the error result is a syntax error and / or execution error, feedback information related to the diagnostic information is generated based on the execution result information and fed back to the large language model, so that the large language model optimizes the generation plan based on the feedback information and regenerates the SQL query statement; if the error result is an empty result, feedback information related to improvement suggestions is generated and fed back to the large language model, so that the large language model optimizes the generation plan based on the feedback information and regenerates the SQL query statement, wherein the improvement suggestions include suggestions for verifying table selection, suggestions for verifying field mapping, suggestions for checking field value specifications, suggestions for evaluating query conditions and suggestions for verifying data integrity.

9. A RAG enhanced Text-to-SQL query system for large-scale database environments, characterized in that: It includes a vector database construction module, a query enhancement module, a dual-path parallel recall module, an SQL query statement generation module, an SQL query statement post-processing optimization module and an SQL query statement execution and feedback module connected in sequence; among them, The vector database construction module includes a Schema vector database construction submodule and a historical question-answer pair vector database construction submodule. The Schema vector database construction submodule generates a standardized description of each Schema based on the first Prompt template, inputs the generated standardized description into the large language model, outputs a summary text description of the Schema, and then uses the vectorization model to convert the summary text description of the Schema into a vector representation, and stores it in the Schema vector database; the historical question-answer pair vector database construction submodule constructs historical question-answer pairs based on the natural language query description of each user input in the historical record and the corresponding SQL query statement that was finally successfully executed, and stores them in the historical question-answer pair vector database; The query enhancement module pre-processes the natural language query description input by the user based on the second prompt template and the third prompt template, generates two independent enhanced descriptions, and obtains the query enhanced description for schema recall and the query enhanced description for SQL generation respectively through two independent large language model calls; The dual-path parallel recall module utilizes RAG technology and relies on the constructed vector database to carry out Schema recall and historical question-answer pair recall operations in dual parallel paths. It includes a Schema recall submodule and a historical question-answer pair recall submodule. The Schema recall submodule uses the query description enhanced for Schema recall as input, utilizes RAG technology combined with the language model's understanding of the query intent, performs a similarity search in the Schema vector database, and then locates and recalls relevant Schema information. The historical question-answer pair recall submodule utilizes a vectorization model to convert the natural language query description input by the user into a vector representation, utilizes RAG technology and, based on the converted vector representation, retrieves similar historical question-answer pairs in the historical question-answer pair vector database, and then locates and recalls the historical question-answer pairs. The SQL query statement generation module generates SQL query statements suitable for large-scale database environments based on the query enhancement description for schema recall, the query enhancement description for SQL generation, the recalled schema information, and the recalled historical question-answer pairs, using a large language model combined with a RAG enhancement mechanism, and according to the fourth prompt template, thereby achieving text-to-SQL conversion; The SQL query statement post-processing optimization module is used to perform post-processing optimization on the SQL query statement generated by the large language model through the database interface, including syntax verification, performance optimization, and error correction. If an error is found, the error information is fed back to the large language model, and the module jumps to the SQL query statement generation module to regenerate the SQL query statement. The optimized SQL query statement is then verified through the database interface until the SQL query statement is error-free or a verification threshold is reached; The SQL query statement execution and feedback module executes the optimized SQL query statement and obtains execution result information; receives user feedback on the execution result. If the feedback is an error result, feedback information is generated based on the execution result information and fed back to the large language model, jumping to the SQL query statement generation module to regenerate the SQL query statement; if the feedback is a successful result, a historical question and answer pair is constructed based on the natural language query description entered by the user and the optimized SQL query statement, and stored in a historical question and answer pair vector database.

10. The system according to claim 9, characterized in that In the query enhancement module, based on the second Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for Schema recall. The second Prompt template is designed according to the requirements of the Schema recall task, including processing step requirements and output requirements. The processing step requirements are used to guide the large language model to perform Schema recall query enhancement on the natural language query description input by the user by analyzing the core requirements and intent of the user query, extracting key elements in the query, enriching the query content based on the extracted key elements, and generating an enhanced query step. The output requirements include output format requirements, output content requirements, and output content length requirements. Based on the third Prompt template, the natural language query description input by the user is preprocessed to generate an enhanced description for SQL generation; the third Prompt template is designed according to the SQL generation task requirements, including processing step requirements and output requirements, wherein the processing step requirements are used to guide the large language model to perform query enhancement for SQL generation on the natural language query description input by the user by analyzing user intent, extracting key elements in the query, decomposing and reconstructing the query, supplementing key information of the query, maintaining the user's original intent, and generating enhanced query steps, and the output requirements include output language style requirements, output content restrictions, and output content length restrictions.

Images