A rag-enhanced text-to-sql query method and system for large-scale database environments

Abstract

The application discloses an RAG enhanced Text-to-SQL query method and system for a large-scale database environment, and the method comprises the following steps: constructing a vector database; based on a user's original query, using an LLM to generate a query enhancement description for Schema recall and a query enhancement description for SQL generation; using RAG technology, relying on the constructed vector database, carrying out Schema recall and historical question and answer pair recall operations in double channels in parallel; based on the query enhancement description, the recalled Schema and the historical question and answer pair, using an LLM combined with RAG to generate an SQL query statement, realizing Text-to-SQL conversion; through a database interface, post-processing optimization is carried out on the generated SQL query statement, including syntax verification, performance optimization, error correction, combined with SQL query statement execution and feedback, realizing efficient, accurate and reliable natural language query, and continuous optimization can be carried out by using user feedback.

Description

Technical Field

[0001] This 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 Technology

[0002] Converting natural language into executable SQL queries (Text-to-SQL) has been a long-standing challenge in database applications, aiming to allow users to interact with databases using natural language without needing to master complex SQL syntax. However, due to the inherent ambiguity of natural language and the diversity of database structures, accurately converting natural language queries into SQL queries is not an easy task.

[0003] Early Text-to-SQL systems were typically based on manually defined rules, requiring complex syntactic and semantic analysis of natural language and mapping it to predefined SQL templates. However, these methods relied 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 also exhibiting poor scalability. With the development of machine learning techniques, researchers have attempted to use statistical models to learn the mapping relationship between natural language and SQL. Although such methods reduce the reliance on manual rules, they usually require large amounts of labeled data, and their performance remains unsatisfactory 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. With its powerful semantic understanding capabilities, LLM can effectively handle the ambiguity of natural language. Furthermore, by incorporating external knowledge (such as database schema information, data examples, and historical question-answer pairs), it further improves the accuracy of SQL generation. However, LLM still faces the following key challenges in practical applications:

[0005] I. Schema Management in Large-Scale Databases: In large-scale database environments, the amount of schema information is enormous, and efficiently acquiring and utilizing this information is a challenge. Simple static loading methods easily exceed the context length limit of the LLM, leading to inefficiency, while the lack of a dynamic acquisition mechanism makes it difficult to adapt to changes in the database structure.

[0006] II. Understanding and Transforming Complex Queries: The complexity of natural language queries varies greatly, ranging from simple single-table queries to complex queries involving multi-table joins, nested subqueries, etc. LLM may suffer from a lack of domain knowledge (such as the mapping relationship between business terms and fields, data value constraints), leading to misunderstandings of query intent and misinterpretations of data table fields. Targeted LLM training may require retraining due to schema data changes such as table structure, resulting in a high cost for system use.

[0007] III. Automatic Optimization and Error Correction of Generated SQL: The autoregressive generation characteristics of LLM may produce syntax errors or semantic deviations. Even minor errors can lead to SQL execution failure. Effectively detecting and correcting potential errors in SQL queries generated by LLM to ensure the syntactic correctness and executability of the generated SQL queries is a significant challenge.

[0008] IV. System Controllability and Continuous Improvement: In order to achieve good results in practical applications, the Text-to-SQL system must have the ability to be controlled and continuously improved. This means that the system needs to be able to automatically iterate based on user feedback and continuously improve the accuracy and stability of queries. How to effectively use user feedback to guide the system's learning and improvement is still an urgent problem to be solved. Summary of the Invention

[0009] To address 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, this invention provides a RAG-enhanced Text-to-SQL query method for large-scale database environments. This method utilizes a vector database to achieve dual-path parallel retrieval of database schema information and historical question-answer pairs, and combines query enhancement, SQL generation post-processing optimization, and a feedback closed-loop mechanism to achieve efficient, accurate, and reliable natural language queries, and can be continuously optimized using user feedback. This invention also relates to a RAG-enhanced Text-to-SQL query system for large-scale database environments.

[0010] This invention is achieved through the following technical solution:

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

[0012] The vector database construction steps include: constructing a schema vector database: based on the first Prompt template, generating standardized descriptions for each schema, inputting the generated standardized descriptions into the large language model, outputting a generalized text description of the schema, and then using a vectorization model to convert the generalized 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 each natural language query description entered by the user in the historical records and the corresponding successfully executed SQL query statement, constructing historical question-answer pairs and storing them in the historical question-answer pair vector database.

[0013] Query enhancement steps: Based on the second and third Prompt templates respectively, the natural language query description input by the user is preprocessed to generate two independent enhanced descriptions. Through two independent calls to the large language model, query enhancement descriptions for schema recall and query enhancement descriptions generated for SQL are obtained respectively.

[0014] The dual-path parallel recall process involves using RAG technology and a pre-built vector database to perform schema recall and historical question-answer pair recall operations in parallel. For schema recall, a query description enhanced for schema recall is used as input. RAG technology, combined with a language model to understand the query intent, is used to perform a similarity search in the schema vector database, thereby locating and recalling relevant schema information. For historical question-answer pair recall, a vectorization model is used to convert the user's input natural language query description into a vector representation. RAG technology is then used to search for similar historical question-answer pairs in the historical question-answer pair vector database based on the converted vector representation, thereby locating and recalling historical question-answer pairs.

[0015] SQL query statement generation steps: 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, the large language model combined with the RAG enhancement mechanism is used to generate an SQL query statement suitable for a large-scale database environment according to the fourth Prompt template, thus realizing Text-to-SQL conversion;

[0016] SQL query post-processing optimization steps: Post-processing optimization of the SQL query generated by the large language model is performed 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 generation step to regenerate the SQL query. The optimized SQL query is then verified through the database interface until the SQL query is error-free or the verification count threshold is reached.

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

[0018] Preferably, in the vector database construction step, the standardized descriptions of each generated schema are used to specify the output requirements of the large language model. The standardized descriptions of the schemas include 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 needs and intent of the user query, extracting key elements in the query, enriching the query content based on 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 for the SQL generation task requirements, including processing step requirements and output requirements. 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. The output requirements include output language style requirements, output content restrictions, and output content length restrictions.

[0021] Preferably, in the SQL query generation step, the fourth Prompt template is designed to meet 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; providing a detailed explanation of why the query cannot be generated when the context information is insufficient; providing guidelines for the use of tables and fields; adopting flexible matching strategies when querying text fields; standardizing time-related query logic; correctly understanding and using aggregate functions; comprehensively handling null values ​​in fields; setting clear aliases for result fields; and optimizing the readability and completeness of field values.

[0022] Preferably, in the SQL query post-processing optimization step, the SQL query generated by the large language model is subjected to syntax verification 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 generation step to regenerate the SQL query, 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 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 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 an execution error, feedback information related to diagnostic information is generated based on the execution result information and fed back to the large language model, enabling the large language model to optimize the generation scheme and regenerate the SQL query statement based on the feedback information. If the error result is an empty result, feedback information related to improvement suggestions is generated and fed back to the large language model, enabling the large language model to optimize the generation scheme and regenerate the SQL query statement based on the feedback information. The improvement suggestions include suggestions to verify table selection, suggestions to verify field mapping, suggestions to check field value specifications, suggestions to evaluate query conditions, and suggestions to verify data integrity.

[0025] A RAG-enhanced Text-to-SQL query system for large-scale database environments includes, in sequence, a vector database construction module, a query enhancement module, a dual-path parallel retrieval module, an SQL query statement generation module, an SQL query statement post-processing optimization module, and an SQL query statement execution and feedback module; wherein,

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

[0027] The query enhancement module preprocesses the natural language query description input by the user based on the second Prompt template and the third Prompt template respectively, generating two independent enhanced descriptions. Through two independent calls to the large language model, it obtains the query enhancement description for schema recall and the query enhancement description for SQL generation respectively.

[0028] The dual-path parallel recall module utilizes RAG technology and relies on a pre-built vector database to perform schema recall and historical question-answer pair recall operations in parallel. It includes a schema recall submodule and a historical question-answer pair recall submodule. The schema recall submodule uses query descriptions enhanced for schema recall as input, and leverages RAG technology combined with a language model to understand the query intent, performing similarity searches in the schema vector database to locate and recall relevant schema information. The historical question-answer pair recall submodule uses a vectorization model to convert the user-input natural language query description into a vector representation, and uses RAG technology and based on the converted vector representation, searches for similar historical question-answer pairs in the historical question-answer pair vector database to locate and recall historical question-answer pairs.

[0029] The SQL query statement generation module, 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, utilizes 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 achieving Text-to-SQL conversion;

[0030] The SQL query post-processing optimization module is used to perform post-processing optimization on the SQL query 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 generation module to regenerate the SQL query. The optimized SQL query is then verified through the database interface until the SQL query is error-free or the verification count threshold is reached.

[0031] The SQL query execution and feedback module executes the optimized SQL query and obtains the execution result information; it receives user feedback on the execution result. If the feedback is an error, it generates feedback information based on the execution result information and sends it back to the large language model, then jumps to the SQL query generation module to regenerate the SQL query; if the feedback is a success, it constructs historical question-answer pairs based on the user's input natural language query description and the optimized SQL query, and stores them in the historical question-answer pair vector database.

[0032] Preferably, in the query enhancement module, based on the second Prompt template, the user-input natural language query description 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 user-input natural language query description by analyzing the core needs and intent of the user query, extracting key elements in the query, enriching the query content based on the extracted key elements, and generating enhanced query steps; 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 for the SQL generation task requirements, including processing step requirements and output requirements. The processing step requirements guide the large language model to perform query enhancement for SQL generation by analyzing user intent, extracting key elements from the query, decomposing and reconstructing the query, supplementing key information of the query, preserving the user's original intent, and generating enhanced query steps. The output requirements include output language style requirements, output content restrictions, and output content length restrictions.

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

[0035] This invention provides a RAG-enhanced Text-to-SQL query method for large-scale database environments. It is also a Text-to-SQL query method based on Retrieval Enhanced Generation (RAG) and Large Language Model (LLM). It is used to achieve efficient natural language queries in complex database environments by leveraging artificial intelligence technology, and is especially suitable for large-scale, multi-type databases (which have massive amounts of data, diverse data types, and complex data structures and relationships). This method constructs a vector database, including a schema vector database and a historical question-answer pair vector database. Based on the first Prompt template, standardized descriptions of each schema are generated and input into a large language model. The output is a summary text description of the schema. Then, a vectorization model is used to convert the summary text description of the schema into a vector representation and store it in the schema vector database. The standardized schema descriptions generated based on the first Prompt template can guide the large language model to output more accurate summary text descriptions of the schema. The length limit of the output content of the large language model can be added to the first Prompt template to prevent the output summary text description of the schema from being too long. Using the vector database, efficient schema similarity retrieval can be achieved, avoiding the inefficiency and context length limitation problems caused by static loading of schema information, thereby improving query efficiency. Based on each historical record... The user-input natural language query description and the corresponding successfully executed SQL query statement are used to construct historical question-answer pairs and store them in a historical question-answer pair vector database. Storing historical question-answer pairs in the vector database can provide high-quality data support for historical question-answer pair recall, thereby improving the accuracy of SQL query language generated by the large language model. Based on the second and third Prompt templates, corresponding enhanced prompt descriptions for the natural language query description are 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, while the output requirements of the third Prompt template focus on enhancing the query generated by the SQL. The enhanced prompt descriptions generated based on the Prompt templates can guide the large language model to output query enhancement descriptions for schema recall and query enhancement descriptions for SQL generation more accurately and efficiently.Utilizing RAG technology and relying on an existing vector database, schema retrieval and historical question-answering pair retrieval operations are performed in parallel. Based on the query enhancement description for schema retrieval, RAG technology can retrieve relevant schema information from the schema vector database and recall the most relevant schema information to enhance the generation capability of the large language model, thereby improving the quality and accuracy of SQL generation. The dynamic schema retrieval mechanism can adapt to dynamic changes in the database structure without manual configuration updates, and can quickly and accurately locate relevant information from massive amounts of schema information, avoiding the problem of simple static loading methods easily exceeding the LLM context length limit; for historical... Historical question-answer pair retrieval utilizes a vectorization model to convert user-input natural language query descriptions into vector representations. RAG technology is then used to retrieve historical question-answer pairs similar to the converted vector representations from the historical question-answer pair vector database. These pairs can be sorted by similarity and retrieved to enhance the generation capabilities of the large language model, thereby improving the quality and accuracy of SQL generation. This vector database-based similarity search method forms a dynamic acquisition mechanism, enabling the dynamic acquisition of relevant schema information based on user query needs. This adapts to changes in the database structure, rather than using an inflexible static loading method, thus achieving schema management for large-scale databases. Based on the schema... The system generates enhanced SQL query descriptions based on the retrieved query, SQL-generated enhanced SQL descriptions, and related schema information from RAG enhancements, along with several similar historical question-answer pairs. Following the fourth Prompt template, it generates enhanced SQL query descriptions and inputs them into the large language model. This process ultimately produces SQL query statements, achieving text-to-SQL conversion and significantly improving the accuracy and reliability of the generated SQL queries. The fourth Prompt template can be configured with experience to optimize the output requirements for enhanced SQL generation, guiding the large language model to efficiently generate SQL queries and overcoming comprehension biases caused by the lack of domain knowledge in LLM models. The database interface is used to process the large language model's generated SQL query descriptions. The generated SQL query statements undergo post-processing optimization, including syntax validation, performance optimization, and error correction. If errors are found, the error information is fed back to the large language model, and the process jumps back to the SQL query statement generation step to regenerate the SQL query statement. The optimized SQL query statement is then validated through the database interface until the SQL query statement is error-free or the validation count threshold is reached. Through validation, error detection, and optimization, this comprehensive error handling and correction mechanism can identify potential syntax errors, performance bottlenecks, and other issues in the SQL query and take appropriate action to ensure the syntactic correctness and executability of the generated SQL query. This improves the system's robustness and enables it to handle complex query scenarios.The system executes the optimized SQL query, obtains the execution result, and receives user feedback. If the feedback is successful, it constructs historical question-and-answer pairs based on the user's original natural language query description and the optimized SQL query, storing them in a historical question-and-answer pair vector database. This allows for the retrieval of similar historical question-and-answer pairs in subsequent queries. Through continuous user use, the number of historical question-and-answer pairs in the vector database is continuously improved, thereby increasing the recall similarity of similar historical question-and-answer pairs for new queries and improving query accuracy. If the feedback is incorrect, the system generates improvement suggestions based on the execution result and feeds them back to the large language model. This allows the large language model to optimize its generation scheme and regenerate the SQL query based on the feedback. 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. By employing a feedback loop mechanism—including SQL query generation, SQL query optimization, and feedback and optimization from the large language model—successful query cases are used for reinforcement learning, allowing the model to learn the correct processing methods. Error cases are analyzed to identify the causes of errors and correct the model, thus achieving continuous improvement and controllability. This not only increases the success rate of individual queries but also effectively utilizes user feedback to guide the learning and improvement of the large language model, preventing the repetition of similar errors in subsequent queries and achieving continuous optimization. Through the construction of a vector database, enhancements to schema recall, enhancements to SQL generation, schema-based recall, recall based on historical question-answer pairs, SQL query generation, SQL query optimization, and feedback and optimization from 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. Furthermore, continuous optimization can be achieved using user feedback, solving problems such as unsatisfactory query results in complex scenarios, low query efficiency, misjudgment of query intent, generated SQL syntax errors or semantic deviations, and the inability to effectively utilize user feedback for continuous optimization in traditional techniques.

[0036] The standardized description of the schema generated by this invention is used to specify the output requirements of a large language model. The schema includes metadata information such as table structure, field types, constraints, and relationships. The standardized description of the schema includes several combinations such as 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 the requirements (such as the format and length of the output content, whether to use subjective adjectives, whether to list potential use cases, etc.), the large language model can be guided to quickly and accurately generate the text description of the schema.

[0037] This invention, based on a second Prompt template, preprocesses the user-inputted natural language query description to generate an enhanced description for schema recall. The second Prompt template is designed to meet the requirements of schema recall tasks, including processing step requirements and output requirements. The processing step requirements primarily guide the large language model through the following steps to enhance the user-inputted natural language query description for schema recall: 1. Analyze and understand the core needs and intent of the user query; 2. Extract key entities (e.g., ship name, company name, port), attributes (e.g., fuel consumption, voyage, ship type), and conditions (e.g., time range, comparison relationships); 3. Based on the extracted key elements, appropriately add relevant descriptions or synonyms to comprehensively cover the user's needs; 4. Organize the above information into a clear and complete query. Output requirements include at least one of the following: output format restrictions (e.g., fluent Chinese expression, a paragraph, etc.), output content restrictions (e.g., enhancement based only on existing data and information, containing only information related to the user's original intent, etc.), and output content length restrictions (e.g., output word count of 100 to 200 characters, etc.). By specifying processing steps and output requirements, large language models can be guided to quickly and accurately generate query enhancement descriptions for schema recall.

[0038] This invention, based on a third Prompt template, preprocesses the user-input natural language query description to generate an enhanced description for SQL generation. The third Prompt template is designed to meet the requirements of SQL generation tasks, including processing steps and output requirements. The processing steps primarily guide the large language model to enhance the user-input natural language query description for SQL generation through the following steps: 1. Analyze user intent; 2. Extract key elements from the query (including entities, attributes, conditions, constraints, etc.); 3. Decompose complex queries into basic components, clarify the meaning of each component, and then reorganize them; 4. Supplement potentially missing but crucial information as needed (e.g., if the query design time range is not specified, consider a reasonable default time range); 5. Ensure that the user's original intent is not changed during the enhancement process; 6. Integrate the above elements to generate the enhanced query. Output requirements include at least one of the following: output language style requirements (e.g., fluent Chinese expression), output content restrictions (e.g., no SQL syntax or code is required), and output content length restrictions (e.g., output characters are 100 to 200). By specifying processing steps and output requirements, large language models can be guided to quickly and accurately generate query enhancement descriptions for SQL.

[0039] This invention, based on query enhancement descriptions for schema recall, query enhancement descriptions for SQL generation, relevant schema information from recall, and several similar historical question-answer pairs, utilizes a large language model combined with a RAG enhancement mechanism to generate SQL query statements according to the fourth Prompt template. The fourth Prompt template is designed to meet the integration requirements of SQL generation quality and vector database retrieval information in large-scale database environments. Its rules and requirements can be refined through experiments to include detailed settings (such as generating a syntactically correct and valid query, ensuring the executability of the SQL query, providing detailed explanations for why a query cannot be generated when context information is insufficient, guidelines for using tables and fields, employing flexible matching strategies when querying text fields, standardizing time-related query logic, correctly understanding and using aggregate functions, comprehensively handling null values ​​in fields, setting clear aliases for result fields, and optimizing the readability and completeness of field values). This guides the large language model to quickly and accurately generate SQL query statements.

[0040] This invention employs specific query enhancement steps. For schema-based query enhancement, it improves schema understanding by understanding user query intent, extracting key elements, enriching query content, and generating enhanced queries. For SQL-generated query enhancement, it improves SQL understanding by understanding user intent, extracting key elements, decomposing and reconstructing queries, supplementing information while maintaining the original intent, and locating named entities. This helps to more accurately grasp the intent of complex user queries. The historical question-answer pair retrieval operation in the dual-path parallel retrieval step uses the user's original query as input and retrieves historical question-answer pairs similar to the current query from a vector database, containing both the user's query and the corresponding SQL query. These historical question-answer pairs provide LLMs with domain knowledge and correct query examples, reducing misunderstandings of query intent and misinterpretations of data table fields caused by the LLM's lack of domain knowledge. The SQL query generation process utilizes a specific fourth Prompt template in the LLM, which includes information such as database version, table schema and field value examples, SQL generation requirements, table and field usage guidelines, time query logic specifications, aggregate function usage specifications, null value handling specifications, result field aliasing specifications, and field value optimization specifications. This helps the LLM accurately convert the user's natural language query into a correct SQL query by combining the vector database schema information.

[0041] This invention optimizes SQL queries generated by a large language model. It performs syntax validation on the generated SQL queries through a database interface; if no syntax errors are found, no optimization is needed. If syntax errors are found, an error message is generated based on the database response and fed back to the large language model, allowing it to further optimize the SQL query and correct the errors. If the SQL query execution exceeds a preset value in the database, optimization suggestions are generated based on the main factors affecting SQL execution efficiency (such as too many JOIN statements, failure to use LIMIT to limit the result set, lack of indexes, and unrestricted time field ranges) and fed back to the large language model for further optimization. This comprehensive error handling and correction mechanism improves the system's robustness and enables it to handle more complex query scenarios.

[0042] In this invention, user feedback errors can include syntax errors, execution errors, and empty results. If the error is a syntax error and / or an execution error, prompt words are added to the SQL execution result information in the database, enabling the large language model to optimize the generation scheme and regenerate the SQL based on the feedback information. If the error 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) is generated, enabling the large language model to optimize the generation scheme and regenerate the SQL based on the feedback information. This multi-level error feedback mechanism continuously optimizes the quality of SQL generation, not only improving the success rate of a single query but also allowing the model to learn from errors and avoid repeating similar errors in subsequent queries.

[0043] This invention also relates to a RAG-enhanced Text-to-SQL query system for large-scale database environments. This system corresponds to the aforementioned RAG-enhanced Text-to-SQL query method for large-scale database environments. It can be understood as a system that implements the aforementioned RAG-enhanced Text-to-SQL query method for large-scale database environments. The system 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. These modules work collaboratively, utilizing RAG technology for dual-path parallel recall to more comprehensively obtain relevant information from the original query. Combined with query enhancement, post-processing optimization, and execution and feedback, the generated SQL query statement... Compared to traditional technologies, it significantly improves the accuracy of SQL queries; enhances query accuracy; improves query efficiency: utilizes a vector database (Schema vector database) to achieve efficient similarity retrieval, avoiding the inefficiencies and context length limitations caused by statically loading schema information; enhances system robustness: through a comprehensive error handling and correction mechanism including SQL query generation, SQL query syntax validation, and SQL optimization, the system's robustness is improved, enabling it to handle more complex query scenarios; achieves continuous improvement: the feedback loop mechanism effectively utilizes user feedback to guide system learning and improvement, achieving continuous system optimization; adapts to database structure changes: the dynamic schema recall mechanism can adapt to dynamic changes in the database structure without requiring manual configuration updates. Attached Figure Description

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

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

[0046] Figure 3 This is an example diagram showing a preferred schematic format for the present invention.

[0047] Figure 4 This is a schematic data example diagram of the present invention.

[0048] Figure 5 This is an example diagram of a preferred 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 according to the present invention. Detailed Implementation

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

[0055] This invention discloses a RAG-enhanced Text-to-SQL query method for large-scale database environments. It aims to address the problems of existing technologies, such as unsatisfactory query performance in complex scenarios, low query efficiency, misjudgment of query intent, generated SQL syntax errors or semantic deviations, and inability to effectively utilize user feedback for continuous optimization. Based on RAG technology, it achieves efficient, accurate, and reliable natural language querying by utilizing a vector database to implement dual-path parallel retrieval of database schema information and historical question-answer pairs. Combined with query enhancement, SQL generation post-processing optimization, and a feedback closed-loop mechanism, it can continuously optimize using user feedback. This method can be executed by a processor or a processing-capable electronic device, such as... Figure 1 and Figure 2As shown, this can be understood as an architecture principle encompassing the construction of a vector database, query enhancement, information retrieval (dual-path parallel recall), SQL generation, post-processing, feedback, and optimization. First, a schema vector database and a historical question-and-answer pair vector database are constructed. Then, the user-input raw query is enhanced for schema recall, resulting in a query enhancement description for schema recall, and enhancements are made for SQL generation, resulting in a query enhancement description for SQL generation. Next, using RAG technology and relying on the constructed vector database, schema recall and historical question-and-answer pair recall operations are performed in parallel, respectively recalling relevant schema information and historical question-and-answer pairs. Finally, LLM is used in conjunction with the RAG enhancement mechanism... The above-obtained schema-recall query enhancement description, SQL-generated query enhancement description, relevant schema information, and historical question-and-answer pairs are used to generate SQL query statements suitable for large-scale database environments, achieving text-to-SQL conversion. Then, the generated SQL is post-processed and optimized through a database interface. If errors are found, the SQL is optimized based on the error information, and optimization continues until there are no errors or the maximum number of optimizations is reached. Finally, the SQL is executed and user feedback is received. If the user feedback indicates successful execution, question-and-answer pairs are generated based on the original query information and SQL and stored in the historical question-and-answer pair database. If the user feedback indicates execution failure, the LLM is optimized based on the reason for the failure, and the SQL is regenerated. This invention utilizes a vector database to achieve efficient similarity retrieval, avoiding the inefficiency and context length limitations caused by statically loading schema information. Through a dual-path parallel recall mechanism, comprehensive query-related information can be obtained. Combined with query enhancement and post-processing optimization, the accuracy of SQL queries is significantly improved. The robust error handling and correction mechanism enhances the system's robustness, enabling it to handle more complex query scenarios. The feedback loop mechanism effectively utilizes user feedback to guide system learning and improvement, achieving continuous system optimization. Specifically, the method includes the following steps:

[0056] I. Vector Database Construction Steps: This includes constructing a Schema vector database: Based on the first Prompt template, generating standardized descriptions for each Schema, inputting the generated standardized descriptions into the large language model, outputting a generalized text description of the Schema, and then using a vectorization model to convert the generalized 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 descriptions entered by each user in the historical records and the corresponding successfully executed SQL query statements, constructing historical question-answer pairs and storing them in the historical question-answer pair vector database.

[0057] like Figure 3As shown, the original schema information in this embodiment of the invention is stored in a structured format and contains rich semantic information, such as field descriptions (e.g., ...). Figure 3 The data includes field 1 descriptions, unit descriptions, supplementary business meanings, etc.; data examples (such as the sample data section in Table 3); and primary key information (such as the primary key field descriptions and primary key design descriptions in Table 3). Figure 4 The schema storage information of the database table sdc_lcb.teyun_2023_ship is displayed, recording key characteristics and attribute data of the vessel, including vessel identification information, physical parameters, technical specifications, and operational information, used for fleet management, asset valuation, and compliance supervision. To achieve accurate schema recall, each schema is converted into a vector representation and stored in a vector database: firstly, based on the first Prompt template (such as...). Figure 5 As shown, the original schema information is filled into the template to generate a standardized description of the schema; then the generated standardized description is input into the LLM, and the LLM generates a general textual description of the schema based on the guiding information in the standardized description; finally, a vectorization model (such as the Sentence-BERT model) is used to convert the general textual description of the schema into a vector representation and store it in the schema vector database.

[0058] For example, such as Figure 5 The first Prompt template (i.e., Schema Prompt template) shown can generate a standardized description of the schema by replacing {Table_Schema} in the template with schema information. This invention does not limit the specific format of the first Prompt template; the output requirements may include several combinations of format, content, language style, information source, etc., where the format may include output content length requirements, the content indicates output content requirements, the language style indicates output language style requirements, and the information source indicates output information source requirements.

[0059] For example, with Figure 4 The schema information of the database table sdc_lcb.teyun_2023_ship shown is as follows: Figure 5Taking the first Prompt template as an example, the LLM output is as follows: "The sdc_lcb.teyun_2023_ship table is a comprehensive ship information database containing 49 fields, covering ship 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, and physical characteristics such as gross tonnage, deadweight tonnage, and dimensions. The table uses a multi-level classification system to describe ship types and provides detailed main and auxiliary engine information. Some fields are available in both Chinese and English, enhancing data usability. This data can be used for various purposes such as fleet management, market analysis, performance evaluation, and compliance checks. Through this table, users can perform various types of data exploration and analysis, such as ship detail queries, fleet composition analysis, technical parameter comparisons, and ship age analysis. It is worth noting that some fields may contain null or special values, which may require additional handling during use."

[0060] II. Query Enhancement Steps: Based on the second and third Prompt templates respectively, the natural language query description input by the user is preprocessed to generate two independent enhanced descriptions. Through two independent calls to the large language model, query enhanced descriptions for schema recall and SQL generation are obtained respectively, so as to improve the accuracy of subsequent schema recall and SQL generation.

[0061] Query enhancement for schema recall: This embodiment of the invention can preprocess the user-input natural language query description based on a second Prompt template (i.e., a schema recall query enhancement Prompt template) to generate an enhanced description for schema recall. The second Prompt template is designed to meet the requirements of schema recall tasks, including processing step requirements and output requirements. The processing step requirements guide the LLM to perform schema recall query enhancement on the user-input natural language query description by analyzing the core needs and intent of the user query, extracting key elements from the query, enriching the query content based on 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 steps for schema recall aim to optimize the understanding of the schema; the input is the user's original query, and the output is the enhanced query that is easier to recall based on schema.

[0062] For example, such as Figure 6The second Prompt template shown can generate an enhanced description for schema recall by replacing {User_Query} in the template with a natural language query description input by the user. This invention does not limit the specific format of the second Prompt template. The processing steps include understanding intent, extracting key elements, enriching query content, and generating enhanced queries. Understanding intent means analyzing the core needs and intent of the user query; extracting key elements means extracting key elements from the query; enriching query content means enriching the query content based on the extracted key elements; and generating enhanced queries means generating the enhanced query. Figure 6 As shown, the output requirements include format, length, and content, where format indicates the output format requirements, length indicates the output content length requirements, and content indicates the output content requirements.

[0063] For example, using the user's original query "Query all ports the PRESIDENT KENNEDY vessel has called at in the past 12 months and calculate the berthing time at each port," the output query enhancement description for schema recall is: "Please provide information on all ports called by the vessel 'PRESIDENT KENNEDY' in the past 12 months, including the specific name of each port, arrival time, and departure time. Additionally, please calculate the actual berthing time (in hours) for the vessel at each port. If possible, please also list the order of calls at each port to understand the vessel's route. Furthermore, please specifically mark any unusually long berthing periods (e.g., exceeding 7 days) and explain the possible reasons (e.g., maintenance, waiting for cargo, etc.). This information will help analyze the operational efficiency and port utilization of the PRESIDENT KENNEDY vessel."

[0064] For query enhancement in SQL generation: This embodiment of the invention can preprocess the user-input natural language query description based on a third Prompt template (i.e., the SQL generation query enhancement Prompt template) to generate an enhanced description for SQL generation. The third Prompt template is designed to meet the requirements of SQL generation tasks and includes processing step requirements and output requirements. The processing step requirements guide the LLM to perform query enhancement for SQL generation by analyzing user intent, extracting key elements from the query, decomposing and reconstructing the query, supplementing key information in the query, preserving the user's original intent, 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 steps for SQL generation aim to optimize the understanding of SQL generation, locate named entities, and take the user's original query as input, outputting an enhanced query that is easier to generate using SQL.

[0065] For example, such as Figure 7 The third Prompt template shown generates an enhanced description for SQL by replacing {User_Query} in the template with a natural language query description input by the user. This invention does not limit the specific format of the third Prompt template; the processing steps may include understanding user intent, extracting key elements, decomposing and reconstructing the query, supplementing information, preserving the original intent, and generating the enhanced query. Understanding intent means analyzing user intent, extracting key elements means extracting key elements from the query, supplementing information means supplementing the query with key information, and preserving the original intent means preserving the original intent. Figure 7 As shown, the output language style requirements include: the enhanced query should use natural and fluent Chinese expressions. Output content restrictions include: no SQL syntax or code is required, only the natural language query should be provided; do not reference specific database table or field names, but common domain terminology can be used; ensure the enhanced query helps the LLM accurately understand and generate the corresponding SQL query; only return the enhanced query.

[0066] For example, using "What is COSCO Pacific's annual fuel consumption?" as the user's original query sample, the output SQL-generated query enhancement description is: "Please query the total fuel consumption of the vessel named 'COSCO Pacific' over the past year."

[0067] III. Parallel Dual-Path Recall Steps: Utilizing RAG technology and relying on the established vector database, schema recall and historical question-answer pair recall operations are performed in parallel. For schema recall, query descriptions enhanced for schema recall are used as input. RAG technology, combined with language model understanding of query intent, is used to perform similarity searches in the schema vector database, thereby locating and recalling relevant schema information. For historical question-answer pair recall, a vectorization model is used to convert the user-input natural language query description into a vector representation. RAG technology is then used to search for similar historical question-answer pairs in the historical question-answer pair vector database based on the converted vector representation, thereby locating and recalling historical question-answer pairs.

[0068] Preferably, the vectorization model can be an embedding model.

[0069] This invention, based on the schema-enhanced description obtained by the above method, utilizes RAG technology combined with a language model to understand query intent. It retrieves relevant schema information, such as table names, field names, data types, field descriptions, and sample field values, from the schema vector database. This allows for the location and retrieval of the most relevant schema information, enhancing the LLM generation capability and improving the quality and accuracy of SQL generation. The dynamic schema retrieval mechanism adapts to dynamic changes in the database structure without requiring manual configuration updates. Furthermore, using RAG technology and based on the transformed vector representation, it retrieves similar historical question-and-answer pairs, such as user queries and their corresponding SQL queries, from the historical question-and-answer pair vector database. These pairs are then sorted from highest to lowest similarity. From the sorted historical question-and-answer pairs, a preset recall threshold (e.g., 3, 4, 5, etc.) is selected for recall, further improving the quality and accuracy of SQL generation.

[0070] For example, using the user's original query "Query the ports of call and berthing time of the PRESIDENT KENNEDY vessel in the past 12 months", the original queries in the five most relevant historical question-and-answer pairs recalled are: "Query the most recent anchorage duration of COSCO Pacific", "Statistics on the number of loading and unloading operations of COSCO Pacific in the past three months and the corresponding ports", "Query the sailing time of the previous leg of COSCO Pacific", "Please check the current status of the vessel currently sailing from Yangshan Port to Long Beach Port", and "What was the port time of the previous leg of COSCO Pacific?"

[0071] IV. SQL Query Generation Steps: Based on the query enhancement descriptions for schema recall, the query enhancement descriptions for SQL generation, the recalled schema information, and the recalled historical question-answer pairs, the 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 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 description of the generated SQL query, relevant schema information enhanced using RAG technology, and several similar historical question-answer pairs. This results in an enhanced generation description that can guide the LLM to generate enhanced 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 statement. The fourth Prompt template can be configured empirically to enhance the output requirements of SQL generation, guiding the LLM to efficiently generate SQL query statements.

[0073] For example, such as Figure 8 and Figure 9 The present invention provides a preferred fourth Prompt template, but does not limit the specific format of the fourth Prompt template. It may include data tables and their field value examples, requirements, NL-SQL pair examples, response formats, and questions. The data tables and their field value examples represent the relevant schema information for enhanced recall using RAG technology. The requirements represent the output rules and requirements for guiding LLM (including: generating a syntactically correct and valid query, ensuring the executability of the SQL query, providing a detailed explanation of why the query cannot be generated when the context information is insufficient, guidelines for using tables and fields, adopting flexible matching strategies when querying text fields, standardizing time-related query logic, correctly understanding and using aggregate functions, comprehensively handling null values ​​in fields, setting clear aliases for result fields, and optimizing the readability and completeness of field values). The NL-SQL pair examples represent several similar historical question-and-answer pairs for enhanced recall using RAG technology. The questions represent enhanced descriptions of the SQL-generated query.

[0074] V. SQL Query Post-processing Optimization Steps: Post-processing optimization of the SQL query statements generated by the large language model is performed through the database interface. This includes syntax validation, performance optimization, and error correction. Specifically: Syntax validation checks the correctness of the SQL query syntax; Performance optimization optimizes the SQL query, such as adding indexes or rewriting the query to improve query efficiency; Error correction attempts to automatically correct errors if they exist in the SQL query. If an error is found, the error information is fed back to the large language model, and the process returns to the SQL query generation step to regenerate the SQL query statement. The optimized SQL query statement is then validated through the database interface until it is error-free or the validation count threshold is reached.

[0075] This invention can optimize SQL queries generated by an LLM (Large Query Management) system. Post-processing optimization (including syntax validation, performance optimization, and error correction) is performed on the LLM-generated SQL queries through a database interface. If a syntax error is found, an error message is generated based on the database response and fed back to the LLM. The system then jumps to the SQL query generation step to regenerate the SQL query, thus correcting the error. If the SQL query executes in the database for more than a preset time (e.g., 10s, 11s, or 12s), the database query operation is actively canceled to avoid resource waste. Performance optimization suggestions are generated based on the main factors affecting SQL execution efficiency (e.g., too many JOIN statements, failure to use LIMIT to limit the result set, lack of indexes, and unrestricted time field ranges), and fed back to the LLM. The system then jumps to the SQL query generation step to regenerate the SQL query until the validation threshold (e.g., 4, 5, or 6 times) is reached, thus optimizing performance. This comprehensive error handling and correction mechanism improves the system's robustness and enables it to handle more complex query scenarios.

[0076] VI. SQL Query Execution and Feedback Steps: Execute the optimized SQL query and obtain the execution result information; receive user feedback on the execution result. If the feedback is an error, generate feedback information based on the execution result information and feed it back to the large language model, then jump to the SQL query generation step to regenerate the SQL query; if the feedback is a success, construct historical question-answer pairs based on the user's input natural language query description and the optimized SQL query, and store them in the historical question-answer pair vector database.

[0077] In this embodiment of the invention, after executing the optimized SQL query, the executed SQL query and execution results (including success and error results) are stored, and then user feedback on the execution results is received. User feedback includes good cases and bad cases. Good cases are used for reinforcement learning, and bad cases are used to correct errors in the LLM large language model. If the user reports successful execution, it indicates that the optimized SQL query is accurate. The original natural language query description input by the user and the optimized SQL query are combined to form a historical question-and-answer pair, which is stored in the historical question-and-answer pair vector database. If the user reports a database execution error or syntax error, feedback information related to diagnostic information (i.e., LLM guidance information including the execution result) is generated based on the stored execution result information and fed back to the LLM. This allows the LLM to optimize the generation plan based on the feedback information and return to the SQL query generation step for regeneration and optimization. If the user reports an empty database execution result, feedback information related to improvement suggestions is generated (such as suggestions to verify table selection, verify field mapping, check field value specifications, evaluate query conditions, and verify data integrity), and fed back to the LLM. This allows the LLM to optimize the generation plan based on the feedback information and return to the SQL query generation step for regeneration and optimization. This multi-layered error feedback mechanism continuously optimizes the quality of SQL generation, not only improving the success rate of a single query but also allowing the LLM to learn from errors and avoid repeating similar errors 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 large-scale database environments. Since the principle of solving the problem by the RAG-enhanced Text-to-SQL query system for large-scale database environments is similar to that of the aforementioned RAG-enhanced Text-to-SQL query method for large-scale database environments, the implementation of the RAG-enhanced Text-to-SQL query system for large-scale database environments can refer to the aforementioned implementation of the RAG-enhanced Text-to-SQL query method for large-scale database environments, and the repeated parts will not be described again.

[0079] Figure 10 This document provides an architecture diagram of a RAG-enhanced Text-to-SQL query system for large-scale database environments, as illustrated 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 standardized descriptions of each schema, inputting the generated standardized descriptions into the large language model, outputting a generalized text description of the schema, and then using a vectorization model to convert the generalized 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 each natural language query description entered by the user in the historical records and the corresponding successfully executed SQL query statement, constructing historical question-answer pairs and storing them in the historical question-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 respectively, generating two independent enhanced descriptions. Through two independent calls to the large language model, it obtains the query enhancement description for schema recall and the query enhancement description for SQL generation respectively.

[0082] The dual-path parallel recall module 103 utilizes RAG technology and relies on a pre-built vector database to perform schema recall and historical question-answer pair recall operations in parallel. For schema recall, the query description enhanced for schema recall is used as input. RAG technology, combined with the understanding of query intent by a language model, is used to perform similarity search in the schema vector database, thereby locating and recalling relevant schema information. For historical question-answer pair recall, a vectorization model is used to convert the user's input natural language query description into a vector representation. RAG technology is used and based on the converted vector representation, similar historical question-answer pairs are retrieved in the historical question-answer pair vector database, thereby locating and recalling historical question-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-answer pairs, utilizes 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 achieving Text-to-SQL conversion.

[0084] The SQL query 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 execution and feedback module 106 is used to execute the optimized SQL query 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 generation step, and regenerate the SQL query; if the feedback is a success result, construct historical question-answer pairs based on the natural language query description input by the user and the optimized SQL query, and store them in the historical question-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 for the schema recall task requirements, 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 needs and intent of the user query, extracting key elements in the query, enriching the query content based on the extracted key elements, and generating enhanced query steps. 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 for the SQL generation task requirements, including processing step requirements and output requirements. The processing step requirements guide the large language model to perform query enhancement for SQL generation by analyzing user intent, extracting key elements from the query, decomposing and reconstructing the query, supplementing key information of the query, preserving the user's original intent, and generating enhanced query steps. The output requirements include output language style requirements, output content restrictions, and output content length restrictions.

[0088] This invention provides a RAG-enhanced Text-to-SQL query system for large-scale database environments. This system utilizes a vector database to achieve efficient similarity retrieval, avoiding the inefficiencies and context length limitations caused by statically loading schema information. Furthermore, the historical question-answer pair vector database can improve the similarity of recalled historical question-answer pairs, thereby enhancing query accuracy. Through a dual-parallel recall mechanism using a dual-path parallel recall module, more comprehensive relevant information can be obtained. The dynamic schema recall mechanism can adapt to dynamic changes in the database structure without requiring manual configuration updates. Combined with query enhancement and post-processing optimization of SQL query statements, the accuracy of SQL queries is significantly improved. The post-processing optimization of SQL query statements employs 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 loops within a preset number of attempts, each improving query quality and performance based on the experience of previous failures. The system stores the successfully executed original user SQL queries and optimized SQL information in a historical question-and-answer pair vector database. These historical question-and-answer pairs are then integrated into the LLM's prompts as sample learning examples in subsequent queries to improve 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 relationship between problem patterns and SQL solutions. The system also explicitly records recalled similar questions, facilitating debugging and analysis of the system's retrieval performance. This successful case-based learning mechanism greatly enhances the LLM's understanding of domain-specific query patterns, enabling it to better handle new similar queries. This feedback loop mechanism—from SQL query generation and optimization to LLM feedback and optimization—not only improves the success rate of individual queries but also effectively utilizes user feedback to guide the LLM's learning and improvement, preventing the repetition of similar errors in subsequent queries and achieving continuous optimization. Furthermore, the robust error handling and correction mechanism enhances the system's robustness, enabling it to handle complex query scenarios.

[0089] It should be noted that the specific embodiments described above 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 the present invention has been described in detail with reference to the accompanying drawings and embodiments, those skilled in the art should understand that modifications or equivalent substitutions can still be made to the present invention. In short, all technical solutions and improvements that do not depart from the spirit and scope of the present invention should be covered within the protection scope of the present invention patent.

Claims

1. A RAG-enhanced Text-to-SQL query method for large-scale database environments, characterized in that, Includes the following steps: The vector database construction steps include: constructing a schema vector database: based on the first Prompt template, generating standardized descriptions for each schema, inputting the generated standardized descriptions into the large language model, outputting a generalized text description of the schema, and then using a vectorization model to convert the generalized 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 each natural language query description entered by the user in the historical records and the corresponding successfully executed SQL query statement, constructing historical question-answer pairs and storing them in the historical question-answer pair vector database. Query enhancement steps: Based on the second and third Prompt templates respectively, the natural language query description input by the user is preprocessed to generate two independent enhanced descriptions. Through two independent calls to the large language model, query enhancement descriptions for schema recall and query enhancement descriptions generated for SQL are obtained respectively. The dual-path parallel recall process involves two parallel steps: Schema recall and historical question-answer pair recall, utilizing RAG technology and a pre-built vector database. For schema recall, the query augmentation description is used as input, and RAG technology, combined with a language model to understand the query intent, is used to perform a similarity search in the schema vector database to locate and recall relevant schema information. For historical question-answer pair recall, a vectorization model is used to convert the user's natural language query description into a vector representation. RAG technology is then used to search for similar historical question-answer pairs in the historical question-answer pair vector database based on the converted vector representation, thereby locating and recalling historical question-answer pairs. SQL query statement generation steps: 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, the large language model combined with the RAG enhancement mechanism is used to generate an SQL query statement suitable for a large-scale database environment according to the fourth Prompt template, thus realizing Text-to-SQL conversion; SQL query post-processing optimization steps: Post-processing optimization of the SQL query generated by the large language model is performed 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 generation step to regenerate the SQL query. The optimized SQL query is then verified through the database interface until the SQL query is error-free or the verification count threshold is reached. SQL query execution and feedback steps: Execute the optimized SQL query and obtain the execution result information; Receive user feedback on the execution result. If the feedback is an error, generate feedback information based on the execution result information and feed it back to the large language model. Then, jump to the SQL query generation step to regenerate the SQL query. If the feedback is a success result, then based on the natural language query description entered by the user and the optimized SQL query statement, a historical question-answer pair is constructed 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 descriptions of each generated schema are used to specify the output requirements of the large language model. The standardized descriptions of the schema include 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 for the schema recall task requirements, 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 needs and intent of the user query, extracting key elements in the query, enriching the query content based on 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, characterized in that, 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 for the SQL generation task requirements, including processing step requirements and output requirements. The processing step requirements 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 enhanced query steps. 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, The SQL query generation step is based on a fourth Prompt template designed to meet the integration requirements of SQL generation quality and vector database retrieval information in a large-scale database environment. This includes the following rules and requirements: generating a syntactically correct and valid query; ensuring the executability of the SQL query; providing detailed explanations of why a query cannot be generated when context information is insufficient; providing guidelines for table and field usage; employing flexible matching strategies when querying text fields; standardizing time-related query logic; correctly understanding and using aggregate functions; comprehensively handling null values ​​in fields; setting clear aliases for result fields; and optimizing the readability and completeness of field values.

6. The method according to any one of claims 1 to 4, characterized in that, In the SQL query post-processing optimization step, the SQL query generated by the large language model is syntax-validated 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 generation step to regenerate the SQL query, 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.

7. The method according to claim 6, characterized in that, In the SQL query post-processing optimization steps, 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 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 an execution error, feedback information related to diagnostic information is generated based on the execution result information and fed back to the large language model, enabling the large language model to optimize the generation scheme and regenerate the SQL query statement based on the feedback information. If the error result is an empty result, feedback information related to improvement suggestions is generated and fed back to the large language model, enabling the large language model to optimize the generation scheme and regenerate the SQL query statement based on the feedback information. The improvement suggestions include suggestions to verify table selection, suggestions to verify field mapping, suggestions to check field value specifications, suggestions to evaluate query conditions, and suggestions to verify data integrity.

9. A RAG-enhanced Text-to-SQL query system for large-scale database environments, characterized in that, It includes, in sequence, a vector database construction module, a query enhancement module, a dual-path parallel retrieval module, an SQL query statement generation module, an SQL query statement post-processing optimization module, and an SQL query statement execution and feedback module; among which, The vector database construction module includes a schema vector database construction submodule and a historical question-and-answer pair vector database construction submodule. The schema vector database construction submodule generates standardized descriptions of each schema based on the first Prompt template, inputs the generated standardized descriptions into the large language model, outputs a generalized text description of the schema, and then uses a vectorization model to convert the generalized text description of the schema into a vector representation and stores it in the schema vector database. The historical question-and-answer pair vector database construction submodule constructs historical question-and-answer pairs based on each natural language query description entered by the user in the historical records and the corresponding successfully executed SQL query statement, and stores them in the historical question-and-answer pair vector database. The query enhancement module preprocesses the natural language query description input by the user based on the second Prompt template and the third Prompt template respectively, generating two independent enhanced descriptions. Through two independent calls to the large language model, it obtains the query enhancement description for schema recall and the query enhancement description for SQL generation respectively. The dual-path parallel recall module utilizes RAG technology and relies on a pre-built vector database to perform schema recall and historical question-answer pair recall operations in parallel. It includes a schema recall submodule and a historical question-answer pair recall submodule. The schema recall submodule uses an augmented query description for schema recall as input, and leverages RAG technology combined with a language model to understand the query intent, performing a similarity search in the schema vector database to locate and recall relevant schema information. The historical question-answer pair recall submodule uses a vectorization model to convert the user-input natural language query description into a vector representation, and uses RAG technology and based on the converted vector representation, searches for similar historical question-answer pairs in the historical question-answer pair vector database to locate and recall historical question-answer pairs. The SQL query statement generation module, 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, utilizes 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 achieving Text-to-SQL conversion; The SQL query post-processing optimization module is used to perform post-processing optimization on the SQL query 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 generation module to regenerate the SQL query. The optimized SQL query is then verified through the database interface until the SQL query is error-free or the verification count threshold is reached. The SQL query execution and feedback module executes the optimized SQL query and obtains the execution result information; it receives user feedback on the execution result. If the feedback is an error, it generates feedback information based on the execution result information and sends it back to the large language model, then jumps to the SQL query generation module to regenerate the SQL query; if the feedback is a success, it constructs historical question-answer pairs based on the user's input natural language query description and the optimized SQL query, and stores them in the historical question-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 for the schema recall task requirements, 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 needs and intent of the user query, extracting key elements in the query, enriching the query content based on the extracted key elements, and generating enhanced query steps. 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 for the SQL generation task requirements, including processing step requirements and output requirements. The processing step requirements guide the large language model to perform query enhancement for SQL generation by analyzing user intent, extracting key elements from the query, decomposing and reconstructing the query, supplementing key information of the query, maintaining the user's original intent, and generating enhanced query steps. The output requirements include output language style requirements, output content restrictions, and output content length restrictions.

Images