Method for converting natural language of a full link into structured query language
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- BEIJING INST OF TECH
- Filing Date
- 2024-04-11
- Publication Date
- 2026-07-24
Smart Images
Figure CN118227653B_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of data processing technology, and in particular to a method for converting natural language into structured query language across the entire process. Background Technology
[0002] NL2SQL (Natural Language to SQL) is a subtask of semantic parsing, converting natural language into structured query language (SQL). Essentially, it transforms a user's natural language into a semantic representation that a computer can understand and execute. This allows users to interactively access and manipulate data stored in relational databases using query intents expressed in natural language. This technology significantly lowers the technical barrier to database queries, enabling users without SQL knowledge to easily retrieve information from databases.
[0003] The core goal of NL2SQL technology is to accurately translate human natural language query intent into SQL query statements that can be executed by the database. This involves understanding the intent in natural language, identifying relevant data entities (such as table names and field names) and conditions, and constructing the corresponding SQL query structure. NL2SQL technology is widely used in business intelligence, online analytical processing (OLAP), automated report generation, and virtual assistants, improving the efficiency and accuracy of data-driven decision-making while allowing non-technical users to utilize complex database resources.
[0004] The following challenges exist in this task: (1) Language diversity: Natural language has great diversity in expression. The same query intent can be expressed in many different ways, and the SQL corresponding to the same intent may also have multiple ways of expression. (2) Complex queries: For complex queries that contain multiple conditions, nested queries, or aggregate functions, the transformation process is more difficult. (3) Contextual understanding: It is necessary to understand the contextual information in the query, such as time and location, which is crucial for generating accurate SQL statements. (4) Database structure dependency: Query transformation not only requires understanding natural language, but also understanding the structure of a specific database, including table names, field names, and the relationships between them. Sometimes, field names are not actual meanings but abstract meanings, and in this case, mapping relationships also need to be established.
[0005] Currently, many technologies are dedicated to converting natural language into structured query language. These technologies mainly include: (1) Rule-based methods: mapping the structure of natural language and SQL statements by defining a set of conversion rules. This method is simple and intuitive, but has poor flexibility and adaptability. (2) Machine learning-based methods: using machine learning models, especially deep learning models, to achieve conversion by learning a large number of natural language and SQL correspondences. These methods can better handle the diversity and complexity of languages. (3) Hybrid methods: combining rule-based and machine learning techniques to improve the accuracy and adaptability of conversion.
[0006] However, these methods still have some shortcomings and deficiencies: (1) Insufficient understanding depth: Because early methods lacked sufficient contextual understanding and semantic understanding of language, rule-based or simple statistical model-based methods often had limited ability to understand complex natural language queries, especially when dealing with nested queries, complex conditional expressions, and fuzzy language. (2) Weak generalization ability: Even with deep learning methods, the generalization ability of the model depends on the coverage of the training data, and the dataset during model training is limited, making it impossible to cover all possible query types or language usages. Therefore, performance may also decline on unseen queries or data domains. (3) Lack of flexibility and scalability: For new database structures or domain knowledge, traditional methods require a lot of manual rule adjustments or model retraining. These methods usually rely on specific data structures or domain knowledge rather than general language understanding capabilities. Summary of the Invention
[0007] To address the aforementioned issues, this invention provides a method for converting natural language into structured query language across the entire process. By combining the structured knowledge of knowledge graphs with the powerful natural language processing capabilities of open-source large language models, it can better understand the entities in the query and the relationships between them, thereby accurately capturing the user's query intent. Even when faced with complex or ambiguous queries, it can maintain a high level of understanding accuracy, thus achieving efficient conversion of natural language to SQL queries in different scenarios.
[0008] To achieve the above objectives, this invention provides a method for converting natural language into structured query language across the entire process, comprising the following steps:
[0009] S1. Use existing corpora and prompting engineering to pre-train open source large language models;
[0010] S2. Construct an NL2SQL corpus based on knowledge graphs for specific application scenarios, and obtain training and test sets;
[0011] S3. Optimize the model parameters of the open-source large language model using the training and test sets, evaluate the optimized model parameters of the open-source large language model, and select the best parameter combination.
[0012] S4. Input natural language into the best open-source large language model, query and generate SQL statements.
[0013] Preferably, the prompting process described in step S1 is used for problem prompting, which includes generating natural language problem prompts, including database table names, field names and mappings, and a data dictionary.
[0014] Preferably, step S2 specifically includes the following steps;
[0015] S21. Use knowledge graphs to simulate database data in specific application scenarios: Model the data in the database using knowledge graphs, obtain the entities and relationships in the knowledge graphs, and create table structures.
[0016] S22. Fill in the data to simulate the database state in a specific application scenario in the real world;
[0017] S23. Based on the knowledge graph constructed from the database data, generate a corpus containing natural language queries and SQL statements;
[0018] S24. Use knowledge graph path sampling to generate simulated user query data to obtain training and test sets.
[0019] Preferably, step S24 specifically includes the following steps:
[0020] S241. Assume a knowledge graph G = (V, E), where V is the set of entities, E is the set of edges, and edges represent relationships between entities. Let path P in the knowledge graph be a sequence of entities and edges: P = {v1, r1, v2, r2, ..., r...} n-1 ,v n}, where v i ∈V is an entity, r i It is entity v i and v i The relationship between +1 is i∈(1,n), where n is the number of entities in the path;
[0021] S242. Selecting a starting entity: Select at least one starting entity v from the entity set V. start ;
[0022] S243, Generation Path: From each starting entity v start Starting from this point, a path is generated by iteratively selecting edges connecting entities. In this process, at each step i, an edge e∈E is selected, whose connecting entity is v. i and vi +1
[0023] The process continues until the predetermined path length is reached or the set termination condition is met.
[0024] S244. Add constraints: Add constraints during the path generation process to guide the sampling process;
[0025] S245. Generate a set of paths. Each path Obtain data from simulated user queries;
[0026] The constraints in step S244 include one or any combination of relation type constraints, entity type, and path length.
[0027] Preferably, in step S2, automated tools and algorithms are used to generate preliminary language pairs, which are then manually reviewed and adjusted to ensure quality.
[0028] Preferably, in step S3, the evaluation scheme is divided into two parts. The first part is the indicator evaluation, which evaluates the large language model through two indicators: executability rate and execution result accuracy rate. Specifically, the executability rate is used to determine whether the query statement generated by the large language model conforms to the grammatical rules, and the execution result accuracy rate is used to determine whether the execution result of the query statement generated by the large language model is correct.
[0029] The second part is the manual evaluation used in the early iterations: multiple queries of each type were extracted and manually scored;
[0030] After both parts meet the requirements, the best large language model for NL2SQL is selected.
[0031] Preferably, step S4 specifically includes the following steps:
[0032] S41. Integration and Deployment: Integrate the best open-source large language model into the backend system of the target application and complete the deployment;
[0033] S42. Design the user interface and implement chain collaboration between the best open-source large language model and specific application scenarios based on LangChain.
[0034] S43. Build a local vector library;
[0035] S44. Before each query, the question is first matched and recalled using a vector library. Questions that match the training data sentence structure are selected, and SQL is provided to generate a large language model to assist in the generation of the best open-source large language model.
[0036] Preferably, in step S4, the newly input natural language and SQL statements by the user are included in the training set, and the parameters of the best open-source large language model are dynamically adjusted based on the user's query history and feedback.
[0037] The present invention has the following beneficial effects:
[0038] 1. Improved the accuracy of natural language understanding
[0039] By combining knowledge graphs, we can better understand the entities in the query and the relationships between them, thereby accurately capturing the user's query intent and maintaining a high understanding accuracy even when faced with complex or ambiguous queries.
[0040] Meanwhile, the addition of large language models further enhances the understanding of natural language, especially when dealing with queries that are semantically diverse and context-dependent.
[0041] 2. Improved the flexibility and generalization ability of query transformation.
[0042] Generalization ability: Because knowledge graphs provide rich domain knowledge, combined with large language models, they can better generalize to new query expressions. They can work effectively even for new or rare database structures. They can construct datasets from scratch and train models to adapt them to different scenarios.
[0043] Flexibility: It does not rely on hard-coded rules, so it can exhibit greater adaptability and flexibility when encountering new database structures or domain knowledge;
[0044] 3. Enhanced system scalability
[0045] Easy to expand: Through the updating and expansion of the knowledge graph and the continuous optimization of the large language model, the system can easily adapt to new domain knowledge and user query needs without making significant modifications to the underlying architecture;
[0046] Cross-domain application: It is easy to adapt to different databases and business domains, enabling the same system to serve the NL2SQL needs of multiple domains, which greatly improves its practical value;
[0047] 4. Improved user experience
[0048] It reduces the technical barrier for users and improves query efficiency: Users do not need to master SQL to query the database through natural language, which greatly lowers the threshold for database query and makes it easy for non-technical users to obtain the information they need.
[0049] In summary, by combining knowledge graphs and large language models, not only is the accuracy and flexibility of NL2SQL transformation improved, but the system's scalability and generalization capabilities are also enhanced, providing users with more efficient and convenient data query services.
[0050] The technical solution of the present invention will be further described in detail below with reference to the accompanying drawings and embodiments. Attached Figure Description
[0051] Figure 1 This is a flowchart illustrating a method for converting natural language into structured query language across the entire supply chain, as described in this invention.
[0052] Figure 2 This is a flowchart illustrating the query processing of a method for converting natural language into structured query language across the entire supply chain, as described in this invention. Detailed Implementation
[0053] To make the objectives, technical solutions, and advantages disclosed in the embodiments of the present invention clearer, the embodiments of the present invention will be further described in detail below with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are merely illustrative of the embodiments of the present invention and are not intended to limit the embodiments of the present invention. All other embodiments obtained by those skilled in the art based on the embodiments in this application without creative effort are within the scope of protection of this application. Examples of the embodiments are shown in the accompanying drawings, wherein the same or similar reference numerals denote the same or similar elements or elements having the same or similar functions throughout.
[0054] It should be noted that the terms "comprising" and "having" and any variations thereof are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or server that includes a series of steps or units is not necessarily limited to those steps or units that are explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to such process, method, product, or device.
[0055] Similar labels and letters in the following figures indicate similar items; therefore, once an item is defined in one figure, it does not need to be further defined and explained in subsequent figures.
[0056] like Figure 1 and Figure 2 As shown, a method for converting natural language into structured query language across the entire process includes the following steps:
[0057] S1. Use existing corpora and prompting engineering to pre-train open source large language models;
[0058] The prompting process described in step S1 is used for problem prompting. Problem prompting includes generating natural language problem prompts, which include database table names, field names and mappings, and a data dictionary.
[0059] S2. Construct an NL2SQL corpus based on knowledge graphs for specific application scenarios, and obtain training and test sets;
[0060] Step S2 specifically includes the following steps;
[0061] S21. Use knowledge graphs to simulate database data in specific application scenarios: Model the data in the database using knowledge graphs, obtain the entities and relationships in the knowledge graphs, and create table structures.
[0062] S22. Fill in the data to simulate the database state in a specific application scenario in the real world;
[0063] S23. Based on the knowledge graph constructed from the database data, generate a corpus containing natural language queries and SQL statements;
[0064] This process involves automated tools and algorithms to generate initial language pairs, which are then reviewed and refined manually to ensure quality. Examples of automated tools and algorithms in this embodiment are as follows:
[0065] OpenAI Codex: Codex is a powerful programming language model developed by OpenAI that can understand natural language and generate code, including SQL. It can be used to automatically generate preliminary pairings between natural language descriptions and corresponding SQL queries. SQLGen: Certain tools are designed to generate mappings between natural language and SQL statements from existing databases and query logs. These tools can automatically generate query statements based on the database's structure and content, and attempt to generate natural language descriptions for these queries.
[0066] Rule engines and pattern matching algorithms: By defining a set of rules or patterns, these algorithms can identify and extract specific database fragments for updating and expanding knowledge graphs. Slot filling algorithms: First, some language pair templates are written, and preliminary language pairs are generated by filling in the slots. Reinforcement learning algorithms: By defining a reward mechanism, reinforcement learning algorithms can reward users when the generated natural language and SQL sentence pairs are sufficiently close to human-written samples. This method can progressively improve the quality of the generated data, making it more suitable for practical applications. Annotation platforms (such as Label Studio, Doccano): These platforms provide user-friendly interfaces that allow data scientists and annotators to easily review and adjust automatically generated natural language and SQL sentence pairs. Human review can further improve the quality and accuracy of the dataset. Combining automated generation with human review not only greatly improves the efficiency of dataset construction but also ensures the quality of the generated data, laying a solid foundation for training high-performance NL2SQL models.
[0067] S24. Generate simulated user query data using knowledge graph path sampling to obtain training and test sets. The method of knowledge graph path sampling makes the sampled data more realistic.
[0068] Step S24 specifically includes the following steps:
[0069] S241. Assume a knowledge graph G = (V, E), where V is the set of entities and E is the set of edges (relationships), where edges represent relationships between entities. Define a path P in the knowledge graph as a sequence of entities (vertices) and edges: P = {v1, r1, v2, r2, ..., r...} n-1 ,v n}, where v i ∈V is an entity, r i It is entity v i and v i The relationship between +1 is i∈(1,n), where n is the number of entities in the path;
[0070] S242. Selecting a starting entity: Select at least one starting entity v from the entity set V. start ;
[0071] S243, Generation Path: From each starting entity v start Starting from this point, a path is generated by iteratively selecting edges connecting entities. In this process, at each step i, an edge e∈E is selected, whose connecting entity is v. i and v i +1
[0072] The process continues until the predetermined path length is reached or the set termination condition is met.
[0073] S244. Add constraints: Add constraints during the path generation process to guide the sampling process;
[0074] S245. Generate a set of paths. Each path Obtain data from simulated user queries.
[0075] For example, in the task of simulating the conversion of natural language queries into SQL queries, path sampling can be used to generate data simulating user queries in the specific application of the NL2SQL task. For instance, the path = {user, purchase, product, belong to, category} P = {v user, r purchase, v product, r belong to, v category} can be used to generate query statements like "list all users who have purchased products of category X", which can then be converted into corresponding SQL queries. Through the above path-based sampling method, meaningful patterns and relationships can be effectively extracted from knowledge graphs, providing rich and structured training and testing data for deep learning models.
[0076] The constraints in step S244 include one or any combination of relation type constraints, entity type, and path length.
[0077] Preferably, in step S2, automated tools and algorithms are used to generate initial language pairs, which are then manually reviewed and adjusted to ensure quality. Here are some examples of tools and algorithms that can be used for this purpose:
[0078] OpenAI Codex: Codex is a powerful programming language model developed by OpenAI that can understand natural language and generate code, including SQL. It can be used to automatically generate preliminary pairings between natural language descriptions and corresponding SQL queries. SQLGen: Certain tools are designed to generate mappings between natural language and SQL statements from existing databases and query logs. These tools can automatically generate query statements based on the database's structure and content, and attempt to generate natural language descriptions for these queries.
[0079] Rule engines and pattern matching algorithms: By defining a set of rules or patterns, these algorithms can identify and extract specific database fragments for updating and expanding knowledge graphs. Slot filling algorithms: First, some language pair templates are written, and preliminary language pairs are generated by filling in the slots. Reinforcement learning algorithms: By defining a reward mechanism, reinforcement learning algorithms can reward users when the generated natural language and SQL sentence pairs are sufficiently close to human-written samples. This method can progressively improve the quality of the generated data, making it more suitable for practical applications. Annotation platforms (such as Label Studio, Doccano): These platforms provide user-friendly interfaces that allow data scientists and annotators to easily review and adjust automatically generated natural language and SQL sentence pairs. Human review can further improve the quality and accuracy of the dataset. Combining automated generation with human review not only greatly improves the efficiency of dataset construction but also ensures the quality of the generated data, laying a solid foundation for training high-performance NL2SQL models.
[0080] S3. Optimize the model parameters of the open-source large language model using the training and test sets, evaluate the optimized model parameters, select the best parameter combination, and obtain the best open-source large language model.
[0081] In step S3, the evaluation scheme is divided into two parts. The first part is metric evaluation, which uses two metrics to evaluate the large language model: executability and execution result accuracy. Since the goal in the test set is to generate executable query statements, the executability is used to determine whether the query statements generated by the large language model conform to grammatical rules, and the execution result accuracy is used to determine whether the execution result of the query statements generated by the large language model is correct. The second part is manual evaluation used in the early iterations: multiple query statements of each type are extracted and manually scored. After both parts meet the requirements, the best NL2SQL large language model is selected. Furthermore, since there are multiple open-source large language models to choose from and training methods to employ, such as ChatGLM, InternLM, Baichuan, and Qwen, and training methods such as LoRa, P-Tuning, and Freeze training, as well as hyperparameters such as batch training and learning rate, these need to be determined through experiments in specific scenarios.
[0082] In this embodiment, if the accuracy of a certain type of query is found to be lower than expected, additional fine-tuning can be performed using relevant datasets. Incremental learning can also be used, continuously incorporating new user queries and their corresponding SQL statements into the training set over time. This not only helps the model adapt to new query patterns but also enables it to adapt to potentially changing database structures.
[0083] S4. Input natural language into the best open-source large language model, query and generate SQL statements.
[0084] Step S4 specifically includes the following steps:
[0085] S41. Integration and Deployment: Integrate the best open-source large language model into the target application's backend system and complete the deployment, ensuring that the model can access the actual database environment in order to execute the generated SQL statements;
[0086] S42. Design a user interface that allows users to easily input queries, receive query results, and receive feedback when necessary. The interface design should consider user experience, making it easy for non-technical users to use, and should leverage LangChain to achieve chained collaboration between the best open-source large language model and specific application scenarios, thereby enabling more efficient and intelligent information processing and query generation.
[0087] Establish tighter collaborative chains between the various parts of the model. For example, from parsing user queries to querying knowledge graphs and generating SQL statements, each step is closely linked, providing feedback and adjustment information to each other. This enhances the model's understanding of the context, enabling it to adjust its behavior based on the user's query history and the current application scenario.
[0088] S43. Build a local vector library;
[0089] S44. Before each query, the question is first matched and recalled using a vector library. Questions that match the training data sentence structure are selected, and SQL is provided to generate a large language model to assist in the generation of the best open-source large language model.
[0090] In step S4, the new natural language and SQL statements input by the user are included in the training set, and the parameters of the best open-source large language model are dynamically adjusted based on the user's query history and feedback.
[0091] Therefore, this invention adopts the above-mentioned method of converting natural language into structured query language across the entire chain. By combining the structured knowledge of knowledge graphs with the powerful natural language processing capabilities of open-source large language models, it can better understand the entities in the query and the relationships between them, thereby accurately capturing the user's query intent and maintaining a high understanding accuracy even when faced with complex or ambiguous queries.
[0092] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and not to limit them. Although the present invention has been described in detail with reference to preferred embodiments, those skilled in the art should understand that modifications or equivalent substitutions can still be made to the technical solutions of the present invention, and these modifications or equivalent substitutions cannot cause the modified technical solutions to deviate from the spirit and scope of the technical solutions of the present invention.
Claims
1. A method for converting natural language into structured query language across the entire process, characterized in that: Includes the following steps: S1. Use existing corpora and prompting engineering to pre-train open source large language models; S2. Construct an NL2SQL corpus based on knowledge graphs for specific application scenarios, and obtain training and test sets; S3. Optimize the model parameters of the open-source large language model using the training and test sets, evaluate the optimized model parameters of the open-source large language model, select the best parameter combination, and obtain the best NL2SQL large language model. S4. Input natural language into the best open-source large language model, query and generate SQL statements; The prompting process described in step S1 is used for problem prompting. Problem prompting includes generating natural language problem prompts, which include database table names, field names and mappings, and a data dictionary. Step S2 specifically includes the following steps; S21. Use knowledge graphs to simulate database data in specific application scenarios: Model the data in the database using knowledge graphs, obtain the entities and relationships in the knowledge graphs, and create table structures. S22. Fill in the data to simulate the database state in a specific application scenario in the real world; S23. Based on the knowledge graph constructed from the database data, generate a corpus containing natural language queries and SQL statements; S24. Use knowledge graph path sampling to generate simulated user query data to obtain training and test sets.
2. The method for converting natural language into structured query language across the entire process according to claim 1, characterized in that: Step S24 specifically includes the following steps: S241, Hypothetical Knowledge Graph ,in It is a collection of entities. It is a set of edges, where edges represent relationships between entities and define paths in the knowledge graph. A sequence of entities and edges: ,in, It is a physical entity. It is a physical entity and The relationship between them , It is the number of entities in the path; S242. Select starting entity: From entity collection Select at least one starting entity ; S243, Generation Path: From each starting entity Starting from this point, paths are generated by iteratively selecting edges connecting entities. During this process, at each step... Select side Its connection entity is and The process continues until the predetermined path length is reached or the set termination condition is met. S244. Add constraints: Add constraints during the path generation process to guide the sampling process; S245. Generate a set of paths. Each path To obtain data simulating user queries; The constraints in step S244 include one or any combination of relation type constraints, entity type, and path length.
3. The method for converting natural language into structured query language across the entire process according to claim 2, characterized in that: In step S2, automated tools and algorithms are used to generate initial language pairs, which are then reviewed and adjusted manually to ensure quality.
4. The method for converting natural language into structured query language across the entire process according to claim 1, characterized in that: In step S3, the evaluation scheme is divided into two parts. The first part is the indicator evaluation, which evaluates the large language model through two indicators: executability rate and execution result accuracy rate. Specifically, the executability rate is used to determine whether the query statement generated by the large language model conforms to the grammatical rules, and the execution result accuracy rate is used to determine whether the execution result of the query statement generated by the large language model is correct. The second part is the manual evaluation used in the early iterations: multiple queries of each type were extracted and manually scored; After both parts meet the requirements, the best large language model for NL2SQL is selected.
5. The method for converting natural language into structured query language across the entire process according to claim 2 or 3, characterized in that: Step S4 specifically includes the following steps: S41. Integration and Deployment: Integrate the best open-source large language model into the backend system of the target application and complete the deployment; S42. Design the user interface and implement chain collaboration between the best open-source large language model and specific application scenarios based on LangChain. S43. Build a local vector library; S44. Before each query, the question is first matched and recalled using a vector library. Questions that match the training data sentence structure are selected, and SQL is provided to generate a large language model to assist in the generation of the best open-source large language model.
6. The method for converting natural language into structured query language across the entire process according to claim 5, characterized in that: In step S4, the new natural language and SQL statements input by the user are included in the training set, and the parameters of the best open-source large language model are dynamically adjusted based on the user's query history and feedback.