Text-to-SQL (Structured Query Language) method based on knowledge graph and big language model prompt project
By leveraging knowledge graphs and large language models, we have addressed the accuracy limitations of existing Text-to-SQL methods in multi-domain and multi-table queries, achieving a more efficient and accurate SQL generation process.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-05-30
- Publication Date
- 2026-04-03
AI Technical Summary
Existing Text-to-SQL methods perform poorly in multi-domain and multi-table queries, require large amounts of data for training, and lack accuracy.
We employ a knowledge graph and large language model-based suggestion engineering approach to generate SQL queries through entity linking, semantic completion, pattern linking, problem decomposition, and self-consistency algorithms. We also optimize the SQL generation process by combining a voting mechanism and scoring feedback.
It improves the accuracy and reliability of Text-to-SQL tasks, and enhances the semantic understanding and generation accuracy of natural language questions to SQL queries.
Smart Images
Figure CN121786059A_ABST
Abstract
Description
Technical Field
[0001] This invention relates to the field of large language model technology, and in particular to a Text-to-SQL method based on knowledge graphs and large language model hinting engineering. Background Technology
[0002] In real life, human activities generate massive amounts of data. With the development of modern information technology, more and more industries are using relational databases to store this data, including finance, healthcare, education, and industrial production—all crucial to social development. Compared to traditional paper-based storage, relational databases offer more convenient and faster information retrieval. Querying relational databases is a vital way to obtain this data. Traditional database querying involves users interacting with the database using Structured Query Language (SQL) to retrieve the required data. However, with the continuous development of databases, the amount of data is constantly increasing, the number of tables is growing, and their structures are becoming more complex, with multi-level nesting relationships between tables. Writing correctly executed SQL statements presents a significant challenge in the face of these practical problems. Text-to-SQL refers to converting natural language text (Text) into Structured Query Language (SQL). Traditional Text-to-SQL methods typically use deep learning algorithms; however, deep learning methods require large amounts of data to train models and perform poorly in multi-domain and multi-table queries, requiring further attention and improvement.
[0003] The shortcomings of existing technologies: Using deep learning-related algorithms to convert natural language text (Text) into structured query language (SQL) requires a large amount of data to train the model, and it performs poorly in multi-domain and multi-table queries, requiring more attention and improvement. Summary of the Invention
[0004] This invention provides a Text-to-SQL method based on knowledge graphs and large language model hinting engineering, which can improve the accuracy and reliability of large language model Text-to-SQL tasks.
[0005] To achieve the above objectives, this invention provides a Text-to-SQL method based on knowledge graphs and large language model hinting engineering, which, crucially, includes the following steps:
[0006] Step 1: Problem Collection: The problem collection module collects problem information a and transmits the problem information a to the large language model;
[0007] Step 2: Entity Linking: The knowledge acquisition module in the large language model acquires the question information a, converts the question information a into a natural language question b, and then performs entity linking on the natural language question b through the entity linker to obtain the named entity c in the natural language question b. The entity linker links the named entity c to the knowledge graph.
[0008] Step 3: Semantic completion: The knowledge acquisition module obtains information d associated with the named entity c from the knowledge graph, and concatenates the summary and type knowledge in the associated information d into a description content e of the named entity c, and uses the description content e as part of the final prompt word s;
[0009] To supplement the semantic information of natural language question b, a knowledge acquisition module was designed to perform entity linking on the natural language question b, find the named entities c that appear in the natural language question b, and link them to the knowledge graph to obtain more semantic information about these named entities c.
[0010] Step 4: Pattern Link Generation: The large language model randomly selects N samples from the training set of the Spider dataset, learns the generation process of the sample pattern links in the samples, generates the pattern link corresponding to the natural language question b, and transmits the pattern link to the question decomposition module; N≥2.
[0011] The few-sample prompting method in prompt word engineering is adopted. Several pre-constructed samples are input into the large language model, and after learning, the large language model outputs the pattern link of the natural language question b in the form of the given samples.
[0012] Here, the N samples are pre-constructed examples for the large language model to learn from. An example is shown below:
[0013] Natural Language Problem B is: Output the names of foreign students who are over 18 years old?
[0014] Process: The table in the database corresponding to this problem is a student information table, which may involve columns such as [student.name, student.nationality, student.age].
[0015] Search the database for the corresponding column based on the student's name, nationality, and age.
[0016] An entity is a specific noun involved in the problem, such as a country, while a unit value is a specific value involved, such as 18 years old.
[0017] Pattern Link = [Student Name, Student Country, Student Age, 18]
[0018] Pattern linking is the process of associating information in a natural language problem (b) with tables and columns in a database.
[0019] Based on the above analysis of the problem, the large language model gradually arrives at the final result.
[0020] Step 5: Problem Decomposition: The problem decomposition module constructs decomposition prompt words based on the pattern link. For problems that do not need decomposition, A problems that do not require nested queries are constructed as prompt samples, and the large language model outputs the results. For problems that need decomposition, B problems that require nested queries are constructed as prompt samples, and heuristic prompting methods are used in the prompts to guide the model to break down the problem into sub-problems. Each sub-problem is solved separately, and the final output results are summarized. The output results are used as part of the final prompt word s; A≥1, B≥1.
[0021] For problems requiring decomposition, heuristic hints are used to guide the large language model in breaking the problem down into subproblems. For example, the problem "Find the ID cards of teachers who taught in the fall of 2009 but not in the spring of 2010" can be broken down into two subproblems: Problem 1: "Find the ID cards of teachers who taught in the fall of 2009" and Problem 2: "Find the ID cards of teachers who taught in the spring of 2010". Each subproblem is solved separately, and the results are then aggregated. Detailed decomposition hints enable the language model to explicitly design a plan for solving a given problem, better leveraging the multi-step reasoning capabilities of the large language model.
[0022] The expression for determining whether a problem needs to be decomposed is as follows:
[0023] classification = f prompt (scheamlinks)
[0024] Here, `classification` indicates whether the problem needs to be decomposed, including both "needs decomposition" and "does not need decomposition"; `scheamlinks` represents pattern links, f prompt This represents the defined prompt function. The question decomposition module constructs a decomposition prompt based on the pattern link and prepends the decomposition prompt to the input of the large language model to obtain the answer to whether the question needs to be decomposed.
[0025] A more accurate description of classification is problem classification (problems that need to be decomposed and problems that don't). This step uses a few-shot suggestion method with N samples. Each sample contains the schema links of the sample problem, the analysis process based on the schema links, and the result indicating whether the problem needs to be decomposed. The schema links (scheamlinks) corresponding to the input problem are then input into a large language model, which uses the above analysis process to determine whether the problem needs further decomposition.
[0026] Step 6: Generate SQL query: The large language model generates M SQL candidate statements based on the natural language question b and the final prompt word s, and uses a voting mechanism to obtain the optimal SQL query statement from the SQL candidate statements; M≥2.
[0027] To enable large language models to generate high-quality SQL statements, a self-consistent algorithm is introduced, allowing the large language model to generate multiple SQL statements, thereby improving the reliability of the results.
[0028] The algorithm flow of the self-consistency algorithm is shown in Table 1:
[0029]
[0030] Table 1
[0031] To select the SQL statement with the highest accuracy from multiple candidate answers, a suitable voting mechanism can be chosen, such as the majority voting principle.
[0032] Step 7: Scoring Output: The large language model takes the optimal SQL query statement as part of the input and scores it. When the score is greater than the scoring threshold, the optimal SQL query statement is output; otherwise, the large language model constructs new prompt words based on the feedback information of the evaluation results and the table structure information of the database, prompting the large language model to regenerate the SQL query statement and score it again.
[0033] The feedback information includes error messages. The new prompts contain table structure information and the reasons for SQL generation errors, prompting the large language model to solve or avoid these problems based on the provided table structure information and error reasons, and to regenerate the SQL query statement.
[0034] The table structure information includes, but is not limited to, table name and column names.
[0035] The reasons for the SQL generation error include, but are not limited to, the following:
[0036] 1. Selecting columns not explicitly required in the question; 2. Incorrect use of GROUP BY; 3. Using JOIN to join unnecessary tables; 4. Incorrect use of keywords or operators such as DISTINCT and EXCEPT; 5. Incorrect capitalization of words.
[0037] Among them, GROUP BY, JOIN, DISTINCT, and EXCEPT are keywords or operators in SQL. GROUP BY is the grouping keyword, JOIN is the connection keyword, DISTINCT is the keyword that specifies returning unique distinct values, and EXCEPT is an operator used to return rows that exist in one query result set but not in another query result set.
[0038] The optimal SQL query statement after voting is used as part of the input of the large language model, enabling the AI to evaluate the SQL query statement it generates. The algorithm flow is shown in Table 2.
[0039] The algorithm flow of the self-evaluation algorithm is shown in Table 2:
[0040]
[0041]
[0042] Table 2
[0043] The final SQL query statement is output to the querying user, and it is generally ready to use immediately.
[0044] The above design effectively utilizes the reasoning and learning capabilities of large-scale language models while eliminating errors caused by randomness or uncertainty, thereby improving the overall accuracy of the Text-to-SQL task of generating structured query language from natural language. It provides a comprehensive and detailed method for improving the accuracy of natural language problem b to SQL query.
[0045] Preferably, in step 1, the problem acquisition module is either a text acquisition device or a voice acquisition device.
[0046] Preferably, in step 2, the entity linker is the Dpedia Spotlight named entity recognition system based on Dpedia.
[0047] Based on the above design, Dpedia Spotlight is selected as the entity linker. This entity linker can automatically annotate named entity 'c' in a given text and link it to the Dpedia knowledge graph. Dpedia extracts structured information from Wikipedia and builds a semantic network, enriching the application of Wikipedia. Two types of knowledge about named entity 'c' in Dpedia are selected as external knowledge. These two types of knowledge include summary and type information. The summary describes the named entity 'c', and the type indicates the type to which the named entity 'c' belongs.
[0048] Dpedia is a unique example of a semantic web application. It extracts structured data from Wikipedia entries to enhance Wikipedia's search functionality and links other datasets to Wikipedia. Through this semantic technology, Wikipedia's vast amount of information has been given many innovative and interesting applications, such as mobile versions, map integration, multi-directional search, relationship queries, document categorization and annotation, and more.
[0049] Dpedia Spotlight is a Java-based open-source tool used to identify and link entities in text to Wikipedia and Dpedia.
[0050] Dpedia Spotight helps users quickly identify important entities, such as names, locations, and organizations, in large-scale text and links these entities to relevant Wikipedia or Dpedia pages. Dpedia Spotight can be widely used in fields such as information extraction, knowledge graph construction, and natural language processing.
[0051] Preferably, in step 3, the final prompt word s is set according to the prompt template, which consists of an instruction, prompt content, and a question, separated by a separator.
[0052] A clear and concise prompt template can improve the large language model's ability to understand questions. The template is divided into three parts using separators: instructions, prompt content, and questions. In the instructions section, the role of the large language model is specified, and common SQL generation errors of the large language model are summarized. In the prompt content section, targeted prompts are constructed according to different questions. In the question section, the large language model is prompted to generate SQL.
[0053] Preferably, in step 4, the process of generating the pattern link corresponding to the natural language question b is as follows: the large speech model obtains the corresponding columns and their tables from the given database according to the column names involved in the natural language question b, and then extracts entities and unit values from the natural language question b to form the pattern link corresponding to the natural language question b.
[0054] Preferably, in step 6, the voting mechanism is a majority voting principle. The execution results are obtained by running candidate SQL statements, and the execution results are categorized. The candidate SQL statement with the highest generation rate is selected as the optimal SQL query statement. This process is expressed as follows:
[0055] SQL=result{sql1,sql2,....,sql M}
[0056] Where SQL represents the optimal SQL query statement, sql1, sql2, ..., sql M This represents M candidate SQL statements, and result represents a list used to store the M candidate SQL statements.
[0057] Preferably, in step 7, the large language model sets the scoring standard to 0-10 points based on the question and table result information provided in the final prompt word s. If the optimal SQL query statement is executed incorrectly, the score is lower than 3 points, and a prompt is output: "Syntax error generated"; if the optimal SQL query statement is executed correctly but does not meet the question, the score is lower than 6 points; if the optimal SQL query statement is executed correctly and meets the question, the score is higher than 6 points, and a specific score is given based on the information characteristics of the optimal SQL query statement.
[0058] Preferably, the information features include, but are not limited to, syntactic correctness, the degree to which the execution result meets the requirements of the problem, and readability.
[0059] Preferably, in step 7, the scoring threshold is 6 points.
[0060] The beneficial effects of this invention are: by making full use of knowledge graphs and large language models, the semantics of natural language problem b are enhanced, and the accuracy of SQL query output is improved. Attached Figure Description
[0061] Figure 1 This is a schematic diagram of the process of the present invention. Detailed Implementation
[0062] The present invention will be further described in detail below with reference to the accompanying drawings and specific examples. The following embodiments or drawings are used to illustrate the present invention, but are not intended to limit the scope of the present invention.
[0063] like Figure 1 As shown: A Text-to-SQL method based on knowledge graphs and large language model hints, the key of which includes the following steps:
[0064] Step 1: Problem Collection: The problem collection module collects problem information a and transmits the problem information a to the large language model;
[0065] Step 2: Entity Linking: The knowledge acquisition module in the large language model acquires the question information a, converts the question information a into a natural language question b, and then performs entity linking on the natural language question b through the entity linker to obtain the named entity c in the natural language question b. The entity linker links the named entity c to the knowledge graph.
[0066] The entity linker is the Dpedia Spotlight named entity recognition system based on Dpedia.
[0067] Step 3: Semantic completion: The knowledge acquisition module obtains information d associated with the named entity c from the knowledge graph, and concatenates the summary and type knowledge in the associated information d into a description content e of the named entity c, and uses the description content e as part of the final prompt word s;
[0068] The final prompt word s is set according to the prompt template, which consists of an instruction, prompt content, and a question, separated by a separator. The instruction section assigns a role to the large language model and summarizes common SQL generation errors of the large language model; the prompt content section constructs targeted prompts based on different questions; and the question section prompts the large language model to generate SQL.
[0069] Step 4: Pattern Link Generation: The large language model randomly selects N samples from the training set of the Spider dataset and learns the generation process of the sample pattern links in the samples. The large speech model obtains the corresponding columns and their tables from the given database according to the column names involved in the natural language problem b, and then extracts entities and unit values from the natural language problem b to form the pattern links corresponding to the natural language problem b, and transmits the pattern links to the problem decomposition module.
[0070] Step 5: Problem Decomposition: The problem decomposition module constructs decomposition prompts based on the pattern links. For problems that do not require decomposition, it constructs A types of problems that do not require nested queries as prompt samples, prompting the large language model to output the results. For problems that require decomposition, it constructs B types of problems that require nested queries as prompt samples, and uses heuristic prompting methods to guide the model to break the problem into sub-problems. Each sub-problem is solved separately, and the final output results are summarized. The output results are used as part of the final prompt s.
[0071] The expression for determining whether a problem needs to be decomposed is as follows:
[0072] classification = f prompt (scheamlinks)
[0073] Here, `classification` indicates whether the problem needs to be decomposed, including both "needs decomposition" and "does not need decomposition"; `scheamlinks` represents pattern links, f prompt This indicates the defined prompt function.
[0074] Step 6: Generate SQL query: The large language model generates M SQL candidate statements based on the natural language question b and the final prompt word s, and uses a voting mechanism to obtain the optimal SQL query statement from the SQL candidate statements;
[0075] The voting mechanism is based on the majority voting principle. It involves running candidate SQL statements to obtain execution results, classifying these results, and selecting the candidate SQL statement with the highest generation rate as the optimal SQL query statement. This process is expressed as follows:
[0076] SQL=result{sql1,sql2,....,sql M}
[0077] Where SQL represents the optimal SQL query statement, sql1, sql2, ..., sql M This represents M candidate SQL statements, and result represents a list used to store the M candidate SQL statements.
[0078] Step 7: Scoring Output: The large language model takes the optimal SQL query statement as part of the input and scores it. When the score is greater than the scoring threshold, the optimal SQL query statement is output; otherwise, the large language model constructs new prompt words based on the feedback information of the evaluation results and the table structure information of the database, prompting the large language model to regenerate the SQL query statement and score it again.
[0079] In this embodiment, the problem collection module uses a text collection device, and the scoring threshold is 6 points.
[0080] The large language model sets the scoring criteria to 0-10 points based on the question and table result information provided in the final prompt word s. If the optimal SQL query statement is executed incorrectly, the score is below 3 points, and a prompt: "Syntax error generated" is output. If the optimal SQL query statement is executed correctly but does not meet the question requirements, the score is below 6 points. If the optimal SQL query statement is executed correctly and meets the question requirements, the score is above 6 points, and a specific score is given based on the syntactic correctness of the optimal SQL query statement, the degree to which the execution result meets the question requirements, and the readability.
[0081] The above description is merely a preferred embodiment of the present invention and is not intended to limit the invention. Various modifications and variations can be made to the present invention by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the scope of protection of the present invention.
Claims
1. A Text-to-SQL method based on knowledge graphs and large language model hinting engineering, characterized in that, Includes the following steps: Step 1: Problem Collection: The problem collection module collects problem information a and transmits the problem information a to the large language model; Step 2: Entity Linking: The knowledge acquisition module in the large language model acquires the question information a, converts the question information a into a natural language question b, and then performs entity linking on the natural language question b through the entity linker to obtain the named entity c in the natural language question b. The entity linker links the named entity c to the knowledge graph. Step 3: Semantic completion: The knowledge acquisition module obtains information d associated with the named entity c from the knowledge graph, and concatenates the summary and type knowledge in the associated information d into a description content e of the named entity c, and uses the description content e as part of the final prompt word s; Step 4: Pattern Link Generation: The large language model randomly selects N samples from the training set of the Spider dataset, learns the generation process of the sample pattern links in the samples, generates the pattern links corresponding to the natural language question b, and transmits the pattern links to the question decomposition module. Step 5: Problem Decomposition: The problem decomposition module constructs decomposition prompt words based on the pattern link. For problems that do not need to be decomposed, it constructs A problems that do not require nested queries as prompt samples and prompts the output results of the large language model. For problems that need to be decomposed, construct B nested query problems as hint samples, and use heuristic hint methods in the hints to guide the model to break down the problems into sub-problems; Solve each sub-problem separately, and then summarize the results. The output result is used as part of the final prompt word s; Step 6: Generate SQL query: The large language model generates M SQL candidate statements based on the natural language question b and the final prompt word s, and uses a voting mechanism to obtain the optimal SQL query statement from the SQL candidate statements; Step 7: Scoring Output: The large language model takes the optimal SQL query statement as part of the input and scores it. When the score is greater than the scoring threshold, the optimal SQL query statement is output; otherwise, the large language model constructs new prompt words based on the feedback information of the evaluation results and the table structure information of the database, prompting the large language model to regenerate the SQL query statement and score it again.
2. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering as described in claim 1, characterized in that: In step 1, the problem acquisition module is either a text acquisition device or a voice acquisition device.
3. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering as described in claim 1, characterized in that: In step 2, the entity linker is DpediaSpotlight, a named entity recognition system based on Dpedia.
4. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering as described in claim 1, characterized in that: In step 3, the final prompt word s is set according to the prompt template, which consists of an instruction, prompt content, and a question, separated by a separator.
5. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering as described in claim 1, characterized in that: In step 4, the process of generating the pattern link corresponding to the natural language question b is as follows: the large speech model obtains the corresponding columns and their tables from the given database according to the column names involved in the natural language question b, and then extracts entities and unit values from the natural language question b to form the pattern link corresponding to the natural language question b.
6. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering as described in claim 1, characterized in that: In step 6, the voting mechanism is based on the majority voting principle. The execution results are obtained by running candidate SQL statements, and these results are categorized. The candidate SQL statement with the highest generation rate is selected as the optimal SQL query statement. This process is expressed as follows: SQL=result{sql1,sql2,....,sql M } Where SQL represents the optimal SQL query statement, sql1, sql2, ..., sql M This represents M candidate SQL statements, and result represents a list used to store the M candidate SQL statements.
7. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering according to claim 1, characterized in that: In step 7, the large language model sets the scoring criteria to 0-10 points based on the question and table result information provided in the final prompt word s. If the optimal SQL query statement is executed incorrectly, the score is lower than 3 points, and a prompt is output: "Syntax error generated"; if the optimal SQL query statement is executed correctly but does not meet the question, the score is lower than 6 points; if the optimal SQL query statement is executed correctly and meets the question, the score is higher than 6 points, and a specific score is given based on the information characteristics of the optimal SQL query statement.
8. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering according to claim 7, characterized in that: The information features include, but are not limited to, syntactic correctness, the degree to which the execution result meets the requirements of the question, and readability.
9. The Text-to-SQL method based on knowledge graphs and large language model prompting engineering according to claim 1, characterized in that: In step 7, the scoring threshold is 6 points.