Chinese scene-oriented Text2SQL prompt project optimization method

Abstract

The invention provides a Chinese scene-oriented Text2SQL prompt project optimization method, and belongs to the field of natural language processing-semantic analysis, and the method comprises the steps of selecting a training sample for preprocessing through database format conversion; a table and a column suitable for the problem are screened out by applying a mode link; integrating the information and supplementing key knowledge; and SQL generation based on a multi-round self-correction mode. According to the method, the large language model can be guided to understand the Text2SQL task intention in the Chinese scene, the SQL generation process is optimized, and the execution accuracy and execution efficiency of SQL query are improved.

Description

Technical Field

[0001] The present invention belongs to the field of natural language processing-semantic analysis, and relates to a Text2SQL (Text-to-SQL, text converted into SQL statements) prompt engineering (Prompts) optimization method for Chinese scenarios. Background Art

[0002] In today's data-driven era, database systems have become the supporting platform for the core business of various industries and are widely used in finance, medical care, education, retail and other fields. As the amount of data continues to increase, how to efficiently and conveniently obtain the required information from the database has become an important challenge. Traditional database queries usually rely on SQL (Structured Query Language). However, for non-professionals, learning and using SQL is a huge challenge. Users need to have a certain database and programming foundation to effectively write query statements.

[0003] In order to solve this problem, Text2SQL technology came into being. Text2SQL refers to the technology that converts natural language queries into SQL query statements, which aims to enable non-programming professional users to interact with database systems through natural language without having to understand complex SQL syntax. Through this technology, users only need to enter simple Chinese query questions, and the system can automatically generate the corresponding SQL query statements and extract the corresponding results from the database.

[0004] In recent years, with the rapid development of Large Language Model (LLM), Text2SQL technology has made significant progress. Traditional Text2SQL methods rely on rule or template matching, and have limited support for complex statements and multi-table joins. The introduction of large models (such as the GPT series) enables Text2SQL methods to handle more complex query tasks.

[0005] However, existing Text2SQL technologies still face many challenges, including insufficient accuracy of semantic understanding, general support for complex queries, and high deployment and reasoning costs. In particular, there are still many problems in the field of Chinese Text2SQL, including: the existing large language model's ability to process Chinese natural language is not as good as English, resulting in reduced accuracy of Text2SQL based on large language models for long questions and multi-table and multi-column queries; the semantic ambiguity and word order diversity of Chinese make large language models prone to comprehension deviations or loss of key information; the existing large language model-based Text2SQL processing methods for Chinese questions are mostly to translate first and then process, resulting in mismatches between questions and translation results. Therefore, improving the generation quality and efficiency of Chinese Text2SQL has become an urgent problem to be solved in current research.

[0006] Prompts in Text2SQL is a technology that optimizes the generation of SQL queries by large language models. It guides the model to accurately understand natural language queries by designing high-quality prompts, thereby improving the accuracy and efficiency of SQL generation. In the field of Chinese Text2SQL, building targeted prompts is conducive to improving the generation quality and efficiency of large language models in Chinese Text2SQL. Summary of the invention

[0007] The present invention aims to improve the quality and efficiency of Chinese Text2SQL generation, and provides a Text2SQL prompt engineering optimization method for Chinese scenarios, which solves the common error types in the Chinese Text2SQL field, improves the execution accuracy in the Chinese Text2SQL field, improves the execution efficiency, and has good adaptability to different data sets. The method steps of the present invention are as follows: Figure 1 As shown, it includes the following main steps:

[0008] S1. Preprocessing. Collect the information of database and natural language questions required for preprocessing, process the format of database information, and add training samples;

[0009] S2. Schema linking (semantic matching of data and schema). Identify and filter out tables and columns in the database that correspond to the phrases in a given query, and filter out irrelevant noise;

[0010] S3. Key information supplementation and integration. Integrate the input content and generated results, and provide key knowledge to the large language model as a calibration for the current task description;

[0011] S4. Multiple rounds of self-correction. SQL is generated in an iterative manner through rule checking and dynamic adjustment of generation strategies. The following is a detailed description of this method:

[0012] 1. Preprocessing

[0013] Preprocessing lays the foundation for the pattern linking stage in the prompting project by reasonably designing the database format and providing high-quality training samples. The specific implementation steps of preprocessing are as follows:

[0014] 1.1. Basic input of prompt engineering. Collect natural language questions and their corresponding database information to build the input basis of prompt engineering. Ensure that the Chinese questions are semantically clear and highly consistent with the database structure, so that SQL query statements can be generated more accurately in subsequent processing links.

[0015] 1.2. Database format conversion. Present the database structure in text form to adapt it to the input requirements of the prompt project. Use the structured expression "table (column 1, column 2, ..., column N)" to intuitively describe the database table and its field structure.

[0016] 1.3. Select training samples. In order to provide effective reference samples for the model, select the natural language questions and their corresponding SQL queries that are most similar to the target questions from the training set. The specific steps are as follows:

[0017] (1) Sample retrieval: Natural language questions are converted into vectors using the Word2Vec (Word-to-Vector) method, and the average value of each question vector is calculated.

[0018] (2) Sample screening based on similarity calculation. The similarity between the questions in the training set and the target questions is measured by Euclidean distance, and they are sorted in ascending order of distance. The top k pairs of questions and SQL queries with the highest similarity are selected as training samples.

[0019] 2. Mode Link

[0020] Schema chaining is a crucial step in the Text2SQL task. Its core purpose is to enable the model to accurately find the tables and columns required to generate SQL queries by identifying and associating query patterns in natural language (such as conditional filtering, aggregation, sorting, etc.). In the Chinese scenario, since Chinese natural language processing is more challenging than English, traditional methods (such as simple similarity matching or single schema chaining) often fail to cover key tables and columns, resulting in a decrease in the quality of generated SQL queries. In order to improve the effect of schema chaining, this paper proposes a schema chaining method based on a large language model. By chaining two independent schemas and taking their union, the model's ability to handle complex queries is significantly enhanced. The specific process is as follows:

[0021] 2.1. Constructing pattern link prompt samples. Select j classic samples from the Chinese Text2SQL dataset CSpider training set and construct them into prompt templates for guiding pattern linking. The samples are designed in the Chain-of-Thought (COT) format, and the specific contents include:

[0022] (1) The correspondence between keywords in natural language questions and database table and column names;

[0023] (2) Semantic interpretation of special expressions in Chinese questions.

[0024] 2.2. Preliminary pattern linking based on direct input. In the first pattern linking, the large language model (LLM) is used directly for pattern linking without relying on prompt samples. The input content includes natural language questions, database tables and column information. The large language model generates a set of tables and columns related to the question based on the input. A set of tables and columns is obtained, for example:

[0025] [Table 1. Column 1, Table 1. Column 2, Table 2. Column 1]

[0026] 2.3. Sample-based deep pattern linking. The second pattern linking adds prompt samples based on the first round to enhance the model's understanding of language structure and semantic relationships. The input content includes:

[0027] (1) Natural language questions, database tables and column information;

[0028] (2) Pattern link prompt sample;

[0029] (3) Value samples: Randomly select a number of rows of values ​​from each database table to assist the model in establishing the association between natural language and database content.

[0030] Get a set of tables and columns, for example:

[0031] [Table 1. Column 1, Table 1. Column 3, Table 2. Column 1]

[0032] 2.4. Merge the link results. Merge the tables and columns obtained from the two schema links, and take their union as the schema link output result, such as:

[0033] [Table 1. Column 1, Table 1. Column 2, Table 1. Column 3, Table 2. Column 1]

[0034] 3. Supplement and integrate key information

[0035] After completing preprocessing and pattern linking, the generated input content and output results usually contain a large amount of redundant information. At the same time, due to the semantic ambiguity and diversity of the Chinese Text2SQL task, semantic misunderstandings and translation errors are likely to occur in SQL generation. Therefore, it is necessary to simplify, optimize, and supplement relevant information to improve the generation quality and task efficiency of the model. The specific optimization measures are as follows:

[0036] 3.1. Information integration and simplification. To reduce information redundancy and ensure the accuracy of pattern linking results, relevant content is integrated and streamlined. The specific steps include:

[0037] (1) Simplify the database structure. Based on the pattern linking results, filter out the tables, columns, and data values related to the generation target, and only retain these necessary parts to reduce the complexity of the input information;

[0038] (2) Update the value samples. Take the intersection of the original value samples and the tables and columns generated by pattern linking, and only retain the content jointly involved by both to ensure the relevance of the input data.

[0039] 3.2. Chinese translation mapping for the database. In the Chinese Text2SQL task, mapping errors often occur due to the mismatch between language expressions and database column names or data values. For example: Problem statement "SELECT weight FROMPetsWHERE petType='dog'ORDER BYpet_ageASC LIMIT 1" Database column name: "dog". Therefore, add the corresponding Chinese translations after each table name, column name, and noun-like value and present them in the database structure.

[0040] 4. Multi-round self-correction

[0041] In the Text2SQL task, the generated SQL statements may be incorrect due to insufficient semantic understanding or improper rule adaptation. To improve the accuracy and robustness of SQL generation, this paper adopts a multi-round self-correction mechanism. By combining large language models and prompt engineering, the generated results are iteratively optimized. This process ensures that the SQL query meets the requirements or is optimized as much as possible before reaching the maximum number of correction times N through rule checking and dynamic adjustment of the generation strategy. The multi-round self-correction method is as Figure 2 shown, and the specific steps are as follows:

[0042] 4.1. Chinese noun replacement. For the generated SQL statements, if they contain Chinese nouns, refer to the database information to map them to the corresponding English names to ensure grammar and execution consistency.

[0043] 4.2. Multi-round correction generation. The generated SQL may lead to empty results or execution errors due to syntax problems or incomplete query logic. To address these problems, a multi-round correction strategy is adopted: using a large language model to reconstruct statements, introducing rule prompts, and optimizing through dynamic generation strategies. The correction process continues to iterate until executable SQL that meets the requirements is generated or the maximum number of corrections N is reached.

[0044] 4.3. Dynamic generation strategy. Considering that the schema link may miss key tables or columns, or destroy the original structural relationship of the database, the following dynamic strategy is adopted to enhance the correction effect:

[0045] (1) Switching the schema linking scheme: If the previous round of execution failed and the schema linking was used, the complete database structure will be used in the new round; otherwise, the schema linking method will be switched.

[0046] (2) Randomly select training samples. In each round of correction, dynamically select different training samples to optimize the prompting project and reduce the deviation that may be caused by a single training sample. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] Figure 1 . Flowchart diagram of a Text2SQL prompt engineering optimization method for Chinese scenarios Figure 2 . Schematic diagram of multi-round self-correction flow chart DETAILED DESCRIPTION

[0048] The hardware environment of the present invention is mainly a PC host with 32GB RAM and 64-bit operating system. The software is implemented on Ubuntu 22.04 platform and developed in Python language.

[0049] The large language model used in the experiment is CodeLlama 13B, the LORA fine-tuning method, and the dataset is CSpider.

[0050] The technical details of the invention will be described more clearly and completely below in conjunction with the accompanying drawings in the examples of the invention.

[0051] like Figure 1 As shown, according to the Text2SQL prompt engineering optimization method for Chinese scenarios described in the present invention, the main steps are divided into four steps: preprocessing, mode linking, key information supplementation and integration, and multiple rounds of self-correction.

[0052] In order to facilitate the description of the structure of each step, the following symbols of key elements are provided: Chinese natural language question (T), task description and specific requirements (Q), pattern link prompt sample (L), database tables and columns (S), database format (M), value sample (V), partial training set sample (E), error sample (W).

[0053] 1. Preprocessing

[0054] (1) Database format conversion. The database structure is presented in text form to adapt it to the input requirements of the prompt project. The structured expression "table (column 1, column 2, ..., column N)" is used to intuitively describe the database table and its field structure.

[0055] An example of this is:

[0056]

[0057] (2) Select training samples. In order to select the sample closest to the target question from the training set, a similarity calculation method is used to retrieve k natural language questions and sort them from high to low in terms of similarity. The specific steps are as follows:

[0058] ① Vectorization problem: Convert the words in Chinese natural language questions into vector set vi through the Word2Vec method.

[0059] ② Calculate the average vector: Take the average of each vector set Vi to generate a single vector representing the entire problem:

[0060]

[0061] Example: For the word vector set of "query student names and grades", assume that the calculated average vector is:

[0062] v 句子 =[0.25,0.35,0.35,...0.41]

[0063] 3③Similarity calculation: Based on the average vectors of the two questions, use the Euclidean distance to calculate their similarity. The smaller the distance, the higher the similarity. Distance calculation formula:

[0064]

[0065] 2. Mode Link

[0066] (1) Construct pattern link samples. Select j classic samples from the Cspider training set and construct them into samples that guide pattern links according to the thinking chain format. Gradually replace the table names, column names, special Chinese expressions, numbers, etc. mentioned in the question.

[0067] The table, column, and value in the database correspond to each other. An example of a sample is as follows:

[0068] Q: "In which year did the weight of the automobile produced not less than 3000 and not more than 4000?"

[0069] A: Let's think about it step by step. In the question "Which year did the automobile produced weigh not less than 3000 and not more than 4000?"

[0070] In the question, it is mentioned:

[0071] "Which year", so we need column = [cars_data.Year]

[0072] "Cars produced", so we need columns = [car_makers.Maker][model_list.Maker]

[0073] "Weight", so we need column = [car_data.Weight]

[0074] Based on the columns and tables, we need foreign key = [car_makers.Maker = model_list.Maker]

[0075] Therefore, the result of pattern linking is expressed as =

[0076] [cars_data.Year,car_makers.Maker,model_list.Maker,car_data.Weight]

[0077] (2) Preliminary model linking based on direct input. The input of the first model linking is the Chinese natural language question (T), task description and specific requirements (Q), and database tables and columns (S). The input is provided to the large language model for processing (f LLM ), get a set of tables and columns (S fsl ), which is specifically formalized as:

[0078] S fsl =f LLM (T,Q,S)

[0079] The example presented to the prompt project is as follows:

[0080]

[0081] (3) Sample-based deep model linking. The input of the second model linking is the Chinese natural language question (T), task description and specific requirements (Q), database tables and columns (S), value samples (V) and model linking prompt samples (L), which are provided to the large language model for processing (f LLM ), get a set of tables and columns (S esl ), which is specifically formalized as:

[0082] S esl =f LLM(T,Q,S,V,L)

[0083] The example presented to the prompt project is as follows:

[0084]

[0085] (4) Merge the link results. Merge the link results. Merge the two schema links to get the sum of the tables and columns (S fsl and S esl ), and take their union as the mode link output result (S sl ), which is formalized as:

[0086] S sl =S fsl ∪S esl

[0087] 3. Supplement and integrate key information

[0088] (1) Information integration simplification. The tables and columns (S sl ) Construct a new problem database format (M') according to the way the database format is constructed in the preprocessing. Record the new tables and columns generated by the schema link (S sl ), a small number of training samples (E) remain unchanged, and the value samples (V) are simplified to the intersection (V') of the value samples in the tables and columns obtained by linking the original samples and patterns:

[0089] V'=V∩S sl

[0090] (2) Database Chinese translation mapping. Add Chinese translation (α) after the English table name, column name and noun property value (M') presented in the new problem database format. tra ) to get (M tra ). It can be formalized as:

[0091] M tra =α tra (M')

[0092] Among them, M tra An example is as follows:

[0093]

[0094] 4. Multiple rounds of self-correction

[0095] The steps to implement the multi-round self-correction algorithm are as follows:

[0096] Algorithm input: Chinese natural language question (T), task description and specific requirements (Q), tables and columns obtained by pattern linking (S sl ), database tables and columns (S), supplemented database format (Mtra ), value samples (V), value samples obtained by pattern linking (V'), and partial training set samples (E)

[0097] The algorithm generates intermediate results: error samples (W), initial SQL generation (SQL1), SQL generation in round i (SQL i )

[0098] Algorithm output SQL

[0099] The algorithm steps are as follows:

[0100] (1) The algorithm input information is passed through a large language model (γ LLM ) to generate preliminary SQL. This process can be visualized as follows:

[0102] SQL1 = γ LLM (T,Q,S sl ,M tra ,V',E)

[0103] (2) Chinese noun replacement. For the generated SQL i If it contains Chinese nouns, you need to refer to the database information to map them to the corresponding English names SQL i ', and make SQL i =SQL i ';

[0104] (3) Empty results and execution errors are fixed. SQL i Empty results or execution errors may occur due to syntax problems or incomplete query logic. To address these issues, the following multiple rounds of correction strategies are adopted:

[0105] ①Fixed empty results and execution errors. For the generated SQL i ,If it contains empty query results or execution errors, use the large language model to reconstruct the statement or introduce rule prompts for optimization;

[0106] ② The correction process continues to iterate until an executable SQL statement that meets the requirements is generated i Or when the maximum number of corrections N is reached, it is used as the output SQL.

[0107] ③ Dynamic generation. The following dynamic generation strategies are used to enhance the correction effect:

[0108] a. Switch mode link scheme. If the last round of SQL i-1 If the execution fails and the schema link is used, then in the next SQL i In the new round, the complete database structure is used instead; otherwise, the new round switches to the mode linking solution.

[0109] b. Randomly select training samples. Dynamically select different training samples for optimization prompt engineering to reduce the deviation that may be caused by a single training sample.

[0110] The use of pattern chaining and inapplicability patterns is achieved through a large language model (γ LLM ) Link to generate SQL i The process can be visualized as:

[0111] SQL i =γ LLM (T,Q,S sl ,M tra ,V',E,W i-1 )

[0112] SQL i =γ LLM (T,Q,S,M tra ,V,E,W i-1 )

[0113] The pseudo code for multiple rounds of self-correction is as follows:

[0114]

[0115]

Claims

1. A Text2SQL prompt engineering optimization method for Chinese scenarios, characterized by The implementation steps are: (1) In the pre-training stage, natural language questions and data sets are used as basic inputs, the database format is converted into text form, and training samples are selected; (2) Using the pattern linking method based on the large language model to select tables and columns that are suitable for the problem; (3) Integrate the information and supplement the Chinese translation mapping of the database; (4) Based on multiple rounds of self-correction mechanism, SQL is generated and iteratively optimized.

2. The Text2SQL prompt engineering optimization method for Chinese scenarios according to claim 1 is characterized in that In the preprocessing stage, this method: (1) Describe the database table and its field structure in the text form of "table (column 1, column 2, ..., column N)"; (2) Randomly select some natural language questions from the training data set, and use the similarity calculation method based on Euclidean distance to select the k questions that are most similar to the target question as training samples by similarity sorting.

3. The Text2SQL prompt engineering optimization method for Chinese scenarios according to claim 1 is characterized in that This method applies pattern chaining based on a large language model: (1) constructing a pattern link prompt sample, wherein the sample includes a correspondence between a natural language question and a database table name and column name and a semantic interpretation of a special Chinese expression; (2) Use a large language model for preliminary model linking, where the input content of the preliminary model linking includes natural language questions, database table and column information, task descriptions, and specific requirements; (3) Use a large language model for deep pattern linking. The input content of deep pattern linking includes natural language questions, database table and column information, task description and specific requirements, pattern linking prompt samples and value samples; (4) The preliminary pattern linking and deep pattern linking results are combined to form the final pattern linking result.

4. The Text2SQL prompt engineering optimization method for Chinese scenarios according to claims 1-3 is characterized in that This method is to supplement and integrate the information: (1) Update the value sample by taking the intersection of the original value sample and the table and column generated by the pattern link; (2) Perform Chinese translation mapping on the database, and add corresponding Chinese translations to the values ​​of noun properties in each table name, column name, and value sample obtained by pattern linking.

5. The Text2SQL prompt engineering optimization method for Chinese scenarios according to claim 1 is characterized in that This method designs a multi-round self-correction algorithm, and the algorithm implementation steps are as follows: (1) Use the large language model to generate preliminary SQL for input information, where the input information is Chinese natural language questions, task descriptions and specific requirements, updated database information, updated value samples, and training samples; (2) Map the Chinese nouns contained in the SQL to the corresponding English nouns in the database; (3) For empty queries and execution errors in the SQL generation results, multiple rounds of corrections are performed until the correct SQL is generated or the maximum number of iterations N is reached. The input content of each round of correction is adjusted according to the dynamic generation strategy.

6. The Text2SQL prompt engineering optimization method for Chinese scenarios according to claim 5 is characterized in that This method is used in the dynamic generation strategy: (1) Based on whether the previous round of SQL generation used schema chaining, the opposite schema chaining scheme is selected in this round; (2) Randomly select training samples; (3) The error cause of the previous round of SQL generation is used as the input of this round.

Images