Method, system and equipment for converting natural language into SQL (Structured Query Language) and storage medium

Abstract

The invention discloses a method, a system and equipment for converting a natural language into an SQL (Structured Query Language) and a storage medium. The method comprises the following steps: selecting a relational database of a corresponding field according to a business scene, and reconstructing the database according to a business demand; utilizing a Prompt guide model to generate a sample and extracting a sample template; extracting field values from the reconstructed database to construct a data pool; preparing a data set by using the sample template and the data pool; performing instruction fine tuning on the large model by using a LoRA fine tuning algorithm to obtain a trained LoRA weight, and combining the original large model and the LoRA weight to obtain a fine-tuned model; using the fine-tuned model to generate a corresponding SQL statement according to the natural language query of the user; performing grammar verification and standardization operation on the SQL statement to obtain a final SQL statement; according to the method, the workload of manually annotating data can be greatly reduced, and the accuracy of converting the natural language into the SQL statement is improved.

Description

Technical Field

[0001] The present invention relates to a language-to-SQL method, system, device and storage medium, and in particular to a natural language-to-SQL method, system, device and storage medium, belonging to the technical field of natural language processing. Background Art

[0002] SQL is commonly used to query data stored in relational databases and is widely used in various computer applications. However, it is usually difficult to master SQL and requires long-term learning. Natural language to SQL technology is a technology that converts human natural language into database SQL language. It can lower the threshold for using and accessing databases and significantly improve the level of interaction between users and databases.

[0003] There are three main technical routes for natural language to SQL conversion: pipeline method, machine learning method and large model prompt method. The pipeline method decomposes the task into multiple stages or steps, and designs corresponding algorithms for each step. The advantage of this method is that it does not require a large amount of training data and can more easily combine domain knowledge, but its disadvantage is that it has strict rules for the expression of natural language, and there is an error amplification phenomenon. The error of each step may be amplified by the subsequent steps. The machine learning method is to design an end-to-end model, adjust the model parameters through the loss function and back propagation algorithm until the model converges. Its advantage is that it has good generalization ability, but the disadvantage is that it requires a lot of data labeling work. The large model prompt method is to use super-large-scale parameter models such as GPT4, and simply use prompts to guide the model to generate SQL statements. The advantage of this method is that it does not require data labeling and model training, and has strong generalization ability, but the disadvantage is low accuracy, slow reasoning speed, and high token consumption. Summary of the invention

[0004] Purpose of the invention: The purpose of the present invention is to provide a natural language to SQL conversion method, system, device and storage medium that can improve the conversion accuracy.

[0005] Technical solution: A natural language to SQL conversion method described in the present invention comprises:

[0006] S1: Select a relational database in the corresponding field according to the business scenario and reconstruct the database according to business needs;

[0007] S2: Use Prompt to guide the model to generate samples and extract sample templates; the sample templates include natural language question templates and SQL statement templates;

[0008] S3: Extract field values ​​from the reconstructed database to build a data pool;

[0009] S4: Prepare dataset using sample template and data pool;

[0010] S5: Use the LoRA fine-tuning algorithm to fine-tune the instructions of the generative large model, obtain the trained LoRA weights, and merge the original large model and the LoRA weights to obtain the fine-tuned model;

[0011] S6: Use the fine-tuned model to generate corresponding SQL statements based on the user's natural language query;

[0012] S7: Perform syntax checking and standardization operations on the SQL statement to obtain the final SQL statement.

[0013] Furthermore, in step S1, the database is reconstructed according to business needs, and the reconstruction method is specifically to screen required fields, unify field naming methods and Chinese explanations of remark fields.

[0014] Furthermore, the model in step S2 is a generative large model with hundreds of billions of parameters, and step S2 specifically includes:

[0015] Constructing a Prompt for generating samples, wherein the Prompt for generating samples includes structural information and data samples of each table, and reflects the intention of generating samples through natural language restrictions;

[0016] A prompt for extracting a sample template is constructed, wherein the prompt for extracting a sample template is used to extract a natural language question template and an SQL statement template at the same time.

[0017] Furthermore, the specific method of step S3 is:

[0018] S31: Obtain all tables in the reconstructed database;

[0019] S32, extract all field values ​​of each field in each table;

[0020] S33, deduplicate the field values;

[0021] S34, establishing a corresponding mapping relationship between tables, fields and field values.

[0022] Furthermore, the step S4 is specifically to use the natural language question template and the corresponding SQL statement template, sample the corresponding field value from the data pool according to the preset generation quantity, fill it into the template, form a data sample, add the database relationship model information text to the natural language question in the data sample, and form a data set.

[0023] Furthermore, the step S6 specifically comprises forming a prompt by splicing the database relational model information text and the user's natural language question, inputting the fine-tuned model, and generating an SQL statement.

[0024] Furthermore, in step S7, the SQL statement is syntax-checked to check for syntax errors in the SQL statement; the WHERE clause is split according to the logical operators AND and OR, the condition fields and condition values ​​are extracted, the condition values ​​are standardized according to the data types of the condition fields, and the WHERE clauses are merged to obtain the final SQL statement.

[0025] Based on the same inventive concept, the present invention also provides a natural language to SQL system, comprising:

[0026] The database reconstruction module is used to select the relational database of the corresponding field according to the business scenario and reconstruct the database according to business needs;

[0027] A sample template building module is used to generate samples and extract sample templates using the prompt-guided model; the sample templates include natural language question templates and SQL statement templates;

[0028] A data pool construction module is used to extract field values ​​from the reconstructed database to construct a data pool;

[0029] A data set preparation module, used to prepare a data set using a sample template and a data pool;

[0030] The model building module is used to use the LoRA fine-tuning algorithm to fine-tune the instructions of the generative large model, obtain the trained LoRA weights, and merge the original large model and the LoRA weights to obtain the fine-tuned model;

[0031] The conversion module is used to use the fine-tuned model to generate corresponding SQL statements according to the user's natural language query;

[0032] The verification module is used to perform syntax verification and standardization operations on SQL statements to obtain the final SQL statement.

[0033] Based on the same inventive concept, the present invention also provides a computing device, including: one or more processors, one or more memories, and one or more programs, wherein the programs are stored in the memories and configured to be executed by the processors, and when the programs are loaded into the processors, the steps of the natural language to SQL conversion method according to any one of the above items are implemented.

[0034] Based on the same inventive concept, the present invention further provides a storage medium storing a computer program, wherein the computer program includes program instructions, and when the program instructions are executed by a processor, the processor executes the steps of the natural language to SQL conversion method according to any one of the above items.

[0035] Beneficial effects: Compared with the prior art, the present invention proposes a method for constructing a fine-tuning dataset, utilizes a large model, constructs samples and extracts sample templates in a prompt manner, and uses templates to generate datasets, thereby greatly reducing the workload of manually annotating data; uses the LoRA fine-tuning technology to fine-tune the large model, aligns the mapping between natural language and SQL, and significantly improves the accuracy of natural language to SQL statements; uses syntax checking to verify the SQL statements generated by the large model, ensuring the correctness and executability of the SQL statements; and further improves the usability of the generated SQL statements by disassembling the WHERE clause and standardizing conditional value operations. BRIEF DESCRIPTION OF THE DRAWINGS

[0036] Figure 1 is a flow chart of a method according to an embodiment of the present invention;

[0037] Figure 2 Schematic diagram of a reconstructed database according to an embodiment of the present invention. DETAILED DESCRIPTION

[0038] The following is a clear and complete description of the technical solutions in the embodiments of the present application in combination with the accompanying drawings and embodiments. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of this application.

[0039] As attached Figure 1 As shown, the natural language to SQL conversion method of this embodiment includes:

[0040] S1: Select a relational database in the corresponding field according to the business scenario and reconstruct the database according to business needs;

[0041] S2: Use Prompt to guide the model to generate samples and extract sample templates; the sample templates include natural language question templates and SQL statement templates;

[0042] S3: Extract field values ​​from the reconstructed database to build a data pool;

[0043] S4: Prepare dataset using sample template and data pool;

[0044] S5: Use the LoRA fine-tuning algorithm to fine-tune the instructions of the generative large model, obtain the trained LoRA weights, and merge the original large model and the LoRA weights to obtain the fine-tuned model;

[0045] S6: Use the fine-tuned model to generate corresponding SQL statements based on the user's natural language query;

[0046] S7: Perform syntax checking and standardization operations on the SQL statement to obtain the final SQL statement.

[0047] Specifically, in step S1, a relational database, such as a MySQL database, is selected according to the field and specific business. The database contains one or more tables, which can be independent tables or associated with other tables through primary key-foreign keys.

[0048] The database needs to be restructured according to the business scenario, which is reflected in the selection of required fields, unified field naming methods, and Chinese explanations of the remark fields. The restructured database is as follows: Figure 2 shown.

[0049] In step S2, the prompt is a piece of text that guides the large model to generate the expected content. A well-designed prompt can accurately convey the user's needs to the model, prompting the model to produce clear, relevant and high-quality responses. On the contrary, if the prompt is of poor quality, it may cause the model to misunderstand, resulting in the output information being off-topic, weakly relevant or of low quality.

[0050] This embodiment uses the Tongyi Qianwen 2.5 model to construct a prompt for generating samples, which contains the structural information and data samples of each table for accurately generating samples. A prompt for extracting sample templates is constructed to simultaneously extract natural language question templates and SQL statement templates.

[0051] The prompt for generating the sample is as follows:

[0052] Prompt:

[0053] You are a sample generation assistant. Please generate 2 sets of natural language questions and corresponding SQL statements based on the information and examples in the following table, and give the results directly.

[0054] Rooms table

[0055] [RoomID (primary key)—room unique identifier, RoomType—room type (double room, family room, twin room, etc.), Rate—price, Availability—status (available, reserved, occupied, cleaned, etc.)]

[0056] Guests table

[0057]

GuestID(PK)—customer unique identifier, Name—name, Contact—contact information

[0058] Bookings table

[0059] [BookingID (PK)—booking unique identifier, GuestID (FK)—customer ID, RoomID (FK)—room ID, CheckInDate—check-in date, CheckOutDate—check-out date, Status—booking status (pending check-in, checked-in, canceled, checked-out, etc.)]

[0060] Rooms Table Example

[0061] RoomID&RoomType&Rate&Availability

[0062] 8001 & King Bed Room & 299 & Available

[0063] 8002&Double Bed Room&299&Book

[0064] 8003 & Family Room & 399 & In residence

[0065] 8004&Twin Room&499&Cleaning

[0066] Guests table example

[0067] GuestID&Name&Contract

[0068] HZ24071401&Name1&158XXXXXXXX

[0069] HZ24071411&Name2&134XXXXXXXX

[0070] HZ24071419&Name3&131XXXXXXXX

[0071] HZ24071319&Name4&152XXXXXXXX

[0072] Bookings table example

[0073] BookingID&GuestID&RoomID&CheckInDate&CheckOutDate&Status

[0074] 1000101&HZ24071401&8003&2024-07-14 14:34:56&2024-07-15 12:14:56&Checked out

[0075] 1000102&HZ24071411&8001&2024-07-14 15:00:56&&Already checked in

[0076] 1000103&HZ24071419&8002&2024-07-14 14:56:23&&Waiting for check-in

[0077] 1000104&HZ24071319&8017&&&Cancelled

[0078] Output the results directly in the following format:

[0079] Question: xxx

[0080] SQL: xxx

[0081] Question: xxx

[0082] SQL: xxx

[0083] Output:

[0084] Question: Query all available double-bed rooms priced below 400 yuan.

[0085] SQL: SELECT * FROM Rooms WHERE RoomType = 'Double Bed Room' AND Rate < 400 AND Availability = 'Available';

[0086] Problem: Find all customers who have booked a room but have not checked in yet and their booking details.

[0087] SQL: SELECT Guests.Name,Guests.Contact,Bookings.CheckInDate,Bookings.CheckOutDate

[0088] FROM Guests

[0089] JOIN Bookings ON Guests.GuestID=Bookings.GuestID

[0090] WHERE Bookings.Status = 'Available'.

[0091] The prompt for extracting the sample template is as follows:

[0092] Prompt:

[0093] You are a template extraction assistant. Please extract the template based on the following natural language question and the corresponding SQL statement (the question template can be further abstracted to adapt to a wider range of situations), and please give the result directly.

[0094] Example

[0095] Question: Query all available double-bed rooms priced below 400 yuan.

[0096] SQL: SELECT * FROM Rooms WHERE RoomType = 'Double Bed Room' AND Rate < 400 AND Availability = 'Available';

[0097] Question template: Query all rooms of %RoomType% with a price lower than %Rate% and current availability %Availability%.

[0098] SQL template: SELECT*FROM Rooms WHERE RoomType='%RoomType%'AND Rate<%Rate%AND Availability=%Availability%';

[0099] Problem: Find all customers who have booked a room but have not checked in yet and their booking details.

[0100] SQL: SELECT Guests.Name,Guests.Contact,Bookings.CheckInDate,Bookings.CheckOutDate

[0101] FROM Guests

[0102] JOIN Bookings ON Guests.GuestID=Bookings.GuestID

[0103] WHERE Bookings.Status = 'Awaiting Check-in';

[0104] Output:

[0105] Question template: Find all customers with %Status% rooms and their booking details.

[0106] SQL template: SELECT Guests.Name,Guests.Contact,Bookings.CheckInDate,Bookings.CheckOutDate

[0107] FROM Guests

[0108] JOIN Bookings ON Guests.GuestID=Bookings.GuestID

[0109] WHERE Bookings.Status='%Status%'.

[0110] In step S3, the role of the data pool is to provide real field values ​​when preparing the data set. It comes from the field values ​​in the database. The specific construction steps are as follows:

[0111] S31, obtain all tables in the database;

[0112] S32, extract all field values ​​of each field in each table;

[0113] S33, deduplicate the field values;

[0114] S34, establish a mapping relationship between table, field and field value.

[0115] The final data pool data structure is a multi-layer nested dictionary format.

[0116] In step S4, the natural language question template and the corresponding SQL statement template generated in step S2 are used to randomly sample the corresponding field values ​​from the data pool constructed in step S3 and fill them into the template according to the set generation quantity to form a data sample. At the same time, the database relational model information text prefix is ​​added to the natural language question in the sample to form the final Alpaca style dataset.

[0117] An example of database relationship schema information text is as follows:

[0118] Rooms table [RoomID(PK), RoomType, Rate, Availability]

[0119] Guests table [GuestID(PK), Name, Contact]

[0120] Bookings table [BookingID(PK), GuestID(FK), RoomID(FK), CheckInDate, CheckOutDate, Status]

[0121] Question: {Question}

[0122] The final data sample is as follows:

[0123] {

[0124] "instruction": "Rooms table [RoomID (PK), RoomType, Rate, Availability] \nGuests table [GuestID (PK), Name, Contact] \nBookings table [BookingID (PK), GuestID (FK), RoomID (FK), CheckInDate, CheckOutDate, Status] \nQuestion: Query the information of all king-size rooms with a price lower than 400 yuan and currently available.",

[0125] "input":"",

[0126] "output":"SELECT*FROM Rooms WHERE RoomType='King bed room'AND Rate<40

[0127] 0AND Availability='Available';"

[0128] }

[0129] In step S5, the fine-tuned base model uses the Qwen2-7B-Instruct model with a smaller number of parameters, and uses the LoRA fine-tuning algorithm encapsulated in the open source Peft library to fine-tune the instructions of the large model. During fine-tuning, the main parameters of the base model are frozen, and a low-rank bypass matrix is ​​added to q_proj, k_proj, v_proj, and o_proj in the model Attention structure, where the rank size is set to 32. After training is completed, the LoRA weights and base model are merged to improve the inference speed.

[0130] In step S6, the big model inference generates SQL statements: the user inputs a natural language question. In order to maintain the consistency of the input data pattern for reasoning and fine-tuning, it is necessary to splice the database relational model information text and the user's natural language question to form a prompt, which is then input into the big model with the merged weights. The big model inference generates SQL statements.

[0131] The input to the large model prompt example is as follows:

[0132] Rooms table [RoomID (PK), RoomType, Rate, Availability]\nGuests table [GuestID (PK), Name, Contact]\nBookings table [BookingID (PK), GuestID (FK), RoomID (FK), CheckInDate, CheckOutDate, Status]\nQuestion: Query the information of all king-size rooms with a price lower than 400 yuan and currently available.

[0133] In step S7, syntax checking and standardization operations are performed on the SQL statement to obtain a final SQL statement.

[0134] Syntax checking includes: using regular expressions or SQL executors to determine whether there are syntax errors in SQL statements;

[0135] The standardized operation steps are as follows:

[0136] S71, splitting the WHERE clause according to the logical operators (AND and OR) to extract the condition fields and condition values;

[0137] S72, standardize the condition value according to the data type of the condition field. For example, if the data type of the amount is integer, "10,000" must be standardized to "10000".

[0138] S73, finally merging the split WHERE clauses to obtain a final SQL statement.

[0139] Based on the same inventive concept, this embodiment also provides a natural language to SQL conversion system, including:

[0140] The database reconstruction module is used to select the relational database of the corresponding field according to the business scenario and reconstruct the database according to business needs;

[0141] A sample template building module is used to generate samples and extract sample templates using the prompt-guided model; the sample templates include natural language question templates and SQL statement templates;

[0142] A data pool construction module is used to extract field values ​​from the reconstructed database to construct a data pool;

[0143] A data set preparation module, used to prepare a data set using a sample template and a data pool;

[0144] The model building module is used to use the LoRA fine-tuning algorithm to fine-tune the instructions of the generative large model, obtain the trained LoRA weights, and merge the original large model and the LoRA weights to obtain the fine-tuned model;

[0145] The conversion module is used to use the fine-tuned model to generate corresponding SQL statements according to the user's natural language query;

[0146] The verification module is used to perform syntax verification and standardization operations on SQL statements to obtain the final SQL statement.

[0147] Based on the same inventive concept, this embodiment also provides a computing device, including: one or more processors, one or more memories, and one or more programs, wherein the programs are stored in the memories and configured to be executed by the processors, and when the programs are loaded into the processors, the steps of the natural language to SQL conversion method described in any one of the above items are implemented.

[0148] Based on the same inventive concept, this embodiment further provides a storage medium, wherein the storage medium stores a computer program, wherein the computer program includes program instructions, and when the program instructions are executed by a processor, the processor executes the steps of the natural language to SQL conversion method according to any one of the above items.

Claims

1. A natural language to SQL conversion method, characterized in that: include: S1: Select a relational database in the corresponding field according to the business scenario and reconstruct the database according to business needs; S2: Use Prompt to guide the model to generate samples and extract sample templates; the sample templates include natural language question templates and SQL statement templates; S3: Extract field values ​​from the reconstructed database to build a data pool; S4: Prepare dataset using sample template and data pool; S5: Use the LoRA fine-tuning algorithm to fine-tune the instructions of the generative large model, obtain the trained LoRA weights, and merge the original large model and the LoRA weights to obtain the fine-tuned model; S6: Use the fine-tuned model to generate corresponding SQL statements based on the user's natural language query; S7: Perform syntax checking and standardization operations on the SQL statement to obtain the final SQL statement.

2. The natural language to SQL conversion method according to claim 1, characterized in that: In step S1, the database is reconstructed according to business needs. The specific reconstruction method is to screen the required fields, unify the field naming method and the Chinese explanation of the remark field.

3. The natural language to SQL conversion method according to claim 1, characterized in that: The model in step S2 is a generative large model with hundreds of billions of parameters, and step S2 specifically includes: Constructing a Prompt for generating samples, wherein the Prompt for generating samples includes structural information and data samples of each table, and reflects the intention of generating samples through natural language restrictions; A prompt for extracting a sample template is constructed, wherein the prompt for extracting a sample template is used to extract a natural language question template and an SQL statement template at the same time.

4. The natural language to SQL conversion method according to claim 1, characterized in that: The specific method of step S3 is: S31: Obtain all tables in the reconstructed database; S32, extract all field values ​​of each field in each table; S33, deduplicate the field values; S34, establishing a corresponding mapping relationship between tables, fields and field values.

5. The natural language to SQL conversion method according to claim 1, characterized in that: Specifically, the step S4 uses a natural language question template and a corresponding SQL statement template, samples corresponding field values ​​from a data pool according to a preset generation quantity, fills them into the template, forms a data sample, and adds database relational model information text to the natural language question in the data sample to form a data set.

6. The natural language to SQL conversion method according to claim 1, characterized in that: Specifically, step S6 forms a prompt by splicing the database relational model information text and the user's natural language question, inputs the fine-tuned model, and generates an SQL statement.

7. The natural language to SQL conversion method according to claim 1, characterized in that: In step S7, the SQL statement is syntax-checked to check for syntax errors in the SQL statement; the WHERE clause is split according to the logical operators AND and OR, the condition fields and condition values ​​are extracted, the condition values ​​are standardized according to the data types of the condition fields, and the WHERE clauses are merged to obtain the final SQL statement.

8. A natural language to SQL system, characterized in that: include: The database reconstruction module is used to select the relational database of the corresponding field according to the business scenario and reconstruct the database according to business needs; A sample template building module is used to generate samples and extract sample templates using the prompt-guided model; the sample templates include natural language question templates and SQL statement templates; A data pool construction module is used to extract field values ​​from the reconstructed database to construct a data pool; A data set preparation module, used to prepare a data set using a sample template and a data pool; The model building module is used to use the LoRA fine-tuning algorithm to fine-tune the instructions of the generative large model, obtain the trained LoRA weights, and merge the original large model and the LoRA weights to obtain the fine-tuned model; The conversion module is used to use the fine-tuned model to generate corresponding SQL statements according to the user's natural language query; The verification module is used to perform syntax verification and standardization operations on SQL statements to obtain the final SQL statement.

9. A computing device, characterized in that include: One or more processors, one or more memories, and one or more programs, wherein the programs are stored in the memories and configured to be executed by the processors, and when the programs are loaded into the processors, the steps of the natural language to SQL conversion method according to any one of claims 1 to 7 are implemented.

10. A storage medium, characterized in that: The storage medium stores a computer program, wherein the computer program includes program instructions, and when the program instructions are executed by a processor, the processor executes the steps of the natural language to SQL conversion method according to any one of claims 1 to 7.

Images