Method to reduce complex SQL generated by natural language by building table mode and SQL conversion

Abstract

A method for reducing complex SQL generated by natural language by constructing table schema and SQL conversion, comprising: pre-configuring database schema and processing user requests; pre-configuring database schema. The configuration steps are as follows: 1.1 The user configures the connection information of the database to obtain the read permission of the target database in the database; 1.2 According to the information configured by the user in step 1.1, the table schema information is read from the target database, and two records are randomly read and named as data 1 and data 2 respectively, and stored in the database as a table schema. The system processes the user's request Query in the following steps: 2.1 Retrieve the database schema related to the Query, and retrieve the relevant table schema from the database; 2.2 Retrieve the SQL examples related to the Query; 2.3 Prompt word construction; 2.4 Generate SQL through the LLM model; 2.5 Rewrite SQL; 2.6 SQL execution. It has the characteristics of more accurate complex queries, lower maintenance costs, and shorter time consumption.

Description

Technical Field

[0001] The present invention relates to the technical field of converting natural language into structured query language of database, and in particular to a method for reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion. Background Art

[0002] The task of natural language to structured query language (NL2SQL) is to convert natural language into the database's structured query language (SQL). This technology enables users to interact with the database through natural language without knowing SQL. Due to the diversity of user question expressions, the complexity of database table structure and SQL, generating accurate SQL from natural language questions is a challenging task.

[0003] Traditional NL2SQL systems are usually based on rules and deep learning network methods. Although they have achieved good performance, rule-based methods rely on manual work and are therefore expensive. Traditional neural network-based methods require continuous fine-tuning of models based on business areas, which is very inflexible as databases become increasingly complex.

[0004] In recent years, with the popularity of Large Language Models (LLM), NL2SQL based on LLM has become a widely-watched research direction. With its powerful language understanding ability, LLM can achieve good results with only prompt words without fine-tuning. Although NL2SQL based on large models has made breakthroughs compared to previous methods, it still has the following problems:

[0005] 1. LLM has a weak understanding of database schema: LLM understands the table names, column names and the relationships between various tables in the database, which is the basis for accurately generating SQL queries. However, database schemas are often complex and do not always conform to the most ideal design specifications, and they also vary greatly in different fields. How to accurately represent database schemas is a challenging task.

[0006] 2. The generated complex SQL does not meet expectations: In order to complete the user's query, you may need to use operations such as outer joins, subqueries, and window functions. These types of SQL are usually longer and involve more tables and operation types, so it is more difficult for LLM to accurately generate SQL. At the same time, the number of SQLs containing these types of operations in the data set used by the mainstream large model during the training phase is small, so the mainstream large model is not good at generating complex SQL.

[0007] Therefore, in view of the shortcomings of the prior art, it is necessary to provide a method for reducing the generation of complex SQL from natural language by constructing table schema and SQL conversion to overcome the shortcomings of the prior art. Summary of the invention

[0008] The purpose of the present invention is to avoid the shortcomings of the prior art and provide a method for reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion, which has the characteristics of more accurate complex queries, lower maintenance costs and shorter time consumption.

[0009] The purpose of the present invention is achieved through the following technical measures.

[0010] A method is provided for reducing complex SQL generated by natural language by constructing table schema and SQL conversion, including: pre-configuring database schema and processing user requests;

[0011] Pre-configure the database mode. The specific configuration steps are as follows:

[0012] 1.1 The user configures the database connection information to obtain the read permission of the target database in the database;

[0013] 1.2 According to the information configured by the user in step 1.1, the table schema information is read from the target database, and two records are randomly read and named as data 1 and data 2 respectively. The read table schema information and the read records are stored in the database as table schemas in units of tables; the set of stored table schemas constitutes the database schema;

[0014] The system processes the user's query request in the following steps:

[0015] 2.1 Retrieve the database schema related to the query, specifically by combining vector search and full-text search to retrieve the relevant table schema from the database;

[0016] 2.2 Retrieve SQL examples related to the query, specifically by combining vector search and full-text search to retrieve relevant SQL examples from the database;

[0017] 2.3 Prompt word construction, specifically filling in the query, table mode, and SQL example into the prompt template;

[0018] 2.4 Generate SQL through LLM model;

[0019] 2.5 Rewrite SQL to simplify the original complex SQL operation into single-table operation SQL;

[0020] 2.6 SQL execution.

[0021] Preferably, in the above method of reducing the generation of complex SQL from natural language by constructing table schema and SQL conversion, the table schema information in step 1.2 is table name, table description, schema name, field name, field type, and field description.

[0022] Preferably, the above method for reducing complex SQL generated from natural language by constructing table schema and SQL conversion further includes step 1.3 of modifying the read database schema to obtain a modified database schema and a corresponding table schema.

[0023] Preferably, in the above method of reducing complex SQL generated from natural language by constructing table schema and SQL conversion, in step 1.3, the read database schema is modified, specifically, the table schema that will not be used in NL2SQL is deleted and the fields that are not used in the table schema are deleted.

[0024] Preferably, in the above method of reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion, the table of the database mode contains a table name, a table description and a field declaration in csv format; the field declaration in csv format contains 5 fields, namely field name, data type, description, data 1 and data 2.

[0025] Preferably, the above method of reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion builds a pseudo table on a database mode based on the CSV format, and simplifies the query operation originally involving multiple tables into a single table operation through the pseudo table.

[0026] Preferably, the above method of reducing complex SQL generated from natural language by constructing table schema and SQL conversion obtains a pseudo table by logically merging fields of two tables, and the schema of the logically merged tables is called a pseudo table schema.

[0027] Preferably, in the above method of reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion, the pseudo table mode is used in the SQL generated by the large model according to the user's Query, and the SQL is rewritten into the SQL for operating the original table according to the rules.

[0028] Preferably, in the above method of reducing the generation of complex SQL from natural language by constructing table schema and SQL conversion, in step 1.3, the read database schema is modified according to actual business needs to obtain a modified standard database schema and a corresponding standard table schema.

[0029] The method of reducing the generation of complex SQL by natural language through constructing table mode and SQL conversion of the present invention comprises: pre-configuring database mode and processing user's request; pre-configuring database mode, the specific configuration steps are as follows: 1.1 user configures database connection information to obtain the read permission of the target database in the database; 1.2 according to the information configured by the user in step 1.1, reads table mode information from the target database, and randomly reads two records and names them as data 1 and data 2 respectively, and stores the read table mode information and the read records in the database as table mode in units of tables; the set of stored table modes constitutes the database mode; the system processes the user's request Query, the specific steps are as follows: 2.1 retrieves the database mode related to the Query, specifically through the combination of vector retrieval and full-text retrieval, retrieves the relevant table mode from the database; 2.2 retrieves the SQL example related to the Query, specifically through the combination of vector retrieval and full-text retrieval, retrieves the relevant SQL example from the database; 2.3 prompt word construction, specifically filling the Query, table mode, and SQL example into the prompt template; 2.4 Generate SQL through LLM model; 2.5 Rewrite SQL to simplify the original complex SQL operation into single table operation SQL; 2.6 SQL execution.

[0030] Compared with other existing technical solutions, the present invention has the following advantages:

[0031] 1. Complex queries are more accurate. The pseudo-table mode and SQL rewriting technology pioneered by the present invention can effectively reduce the complexity of the SQL that LLM needs to generate for complex queries, reduce the probability that the generated SQL cannot be executed, and reduce the time required for complete SQL generation. On the other hand, the present invention describes the table mode based on csv, so that in addition to the field name, field type, and field remarks that can also be included in other work, the table mode can also contain additional table records, so that the model can learn the specific storage format of the records in the table.

[0032] 2. Lower maintenance cost. The present invention is based on csv to organize the table mode. Compared with other NL2SQL methods that describe the table mode based on database table creation statements, the present invention is easier to be maintained by non-technical personnel.

[0033] 3. Shorter time consumption: The time consumption of NL2SQL mainly lies in the number of tokens required to generate for large models. SQL rewriting technology makes it possible to complete operations that originally required the generation of long SQL statements by generating shorter SQL statements, thus greatly reducing time consumption. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] The present invention is further described with reference to the accompanying drawings, but the contents in the accompanying drawings do not constitute any limitation to the present invention.

[0035] Figure 1 It is a flow chart of a method of reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion according to the present invention. DETAILED DESCRIPTION

[0036] The present invention will be further described with reference to the following examples.

[0037] Example 1

[0038] A method for reducing complex SQL generated by natural language by building table mode and SQL conversion includes: pre-configuring database mode and processing user requests.

[0039] Pre-configure the database mode. The specific configuration steps are as follows:

[0040] 1.1 The user configures the database connection information to obtain the read permission of the target database in the database;

[0041] 1.2 According to the information configured by the user in step 1.1, the table schema information is read from the target database, and two records are randomly read and named as data 1 and data 2 respectively. The read table schema information and the read records are stored in the database as table schemas in units of tables; the collection of stored table schemas constitutes the database schema.

[0042] The table schema information includes table name, table description, schema name, field name, field type, and field description.

[0043] The system processes the user's query request, such as Figure 1 As shown, the specific steps are as follows:

[0044] 2.1 Retrieve the database schema related to the query, specifically by combining vector search and full-text search to retrieve the relevant table schema from the database;

[0045] 2.2 Retrieve SQL examples related to the query, specifically by combining vector search and full-text search to retrieve relevant SQL examples from the database;

[0046] 2.3 Prompt word construction, specifically filling in the query, table mode, and SQL example into the prompt template;

[0047] 2.4 Generate SQL through LLM model;

[0048] 2.5 Rewrite SQL to simplify the original complex SQL operation into single-table operation SQL;

[0049] 2.6 SQL execution.

[0050] Database schema description is a key issue for NL2SQL. Only after the database schema is accurately described can LLM generate accurate SQL. In the past, table schemas were represented by table creation statements, such as "CREATE TABLE V_ATN_Record (name nvarchar(255), employee_id int, attendance_date date, show_temperature varchar(255), attendance_time time, organ_name varchar(255));". Although this method helps the model understand the table structure, it has three obvious disadvantages:

[0051] First: The format of the data stored in the table cannot be represented. This makes it difficult for LLM to generate SQL statements based on the specific data storage format. For example, for a field storing the name of an administrative district, the actual stored value may be "Guangzhou" or "Guangzhou City". For a field storing "date", the actual stored value may be "2024-08-20" or "2024 / 08 / 20 / 000000".

[0052] Second: LLM may not understand the purpose and usage of the table. In previous work, the usage of the table was prompted to LLM through SQL examples, but this method may cause the table and SQL examples to not correspond.

[0053] Third: High maintenance cost. The table creation statement is not suitable for non-professionals to maintain.

[0054] In order to overcome the above problems, this application decomposes the representation of the database model into three parts: table name, table description and field declaration in csv format. The field declaration in csv format contains 5 fields, namely field name, data type, description, data 1 and data 2, where data 1 and data 2 are records randomly sampled from the table, used to indicate the storage format of the large model data.

[0055] This technology reduces the generation of complex SQL from natural language by building table models and SQL conversion. On a database model based on the CSV format, a pseudo table is built to simplify query operations that originally involved multiple tables into single-table operations.

[0056] Preferably, the above method of reducing complex SQL generated from natural language by constructing table schema and SQL conversion obtains a pseudo table by logically merging fields of two tables, and the schema of the logically merged tables is called a pseudo table schema.

[0057] A pseudo table is an artificially constructed table proposed on a database model based on the CSV format, which is designed to help simplify query operations that originally involve multiple tables into single-table operations. For example, a pseudo table model can be created based on the employee table and the supplementary punch-in table.

[0058] The characteristic of pseudo table mode is that the information density is higher, and there is no need to consider the corresponding relationship with the database table. For example, for the query requirement "Who is late this week", you can add a row "late, bool, indicating whether the employee is late on that day, 1 is late, 0 is not late" in the pseudo table, without considering whether the late field actually exists in the real database. When the SQL generated by LLM uses this field, you can clearly know the user's question intention, and extract and rewrite the SQL parameters.

[0059] The method of reducing the generation of complex SQL from natural language by building table mode and SQL conversion, the pseudo table mode is used in the SQL generated by the large model according to the user's query, and the SQL is rewritten into the SQL operating the original table according to the rules.

[0060] When LLM generates complex SQL, it is very easy to generate SQL that does not conform to the syntax, or conforms to the syntax but the query results are not what the user needs, due to insufficient model capabilities such as model hallucination. SQL rewriting aims to reduce the difficulty of LLM generating SQL and simplify operations that can only be completed by complex SQL into shorter single-table operation SQL.

[0061] After using SQL rewriting technology, LLM only needs to generate simple SQL, and the code logic will restore the SQL. Therefore, LLM needs fewer TOKENs to generate complete SQL, which speeds up the system's response and reduces the cost of TOKEN generation.

[0062] For example, the table schema of two tables is required to use the JOIN syntax, but if the fields of the two tables are logically merged in advance, the JOIN operation can be omitted. We call the schema of the logically merged tables a pseudo table schema.

[0063] If the pseudo table mode is used in the SQL generated by the large model based on the user's query, the SQL will be rewritten into SQL that operates the original table according to the rules.

[0064] Compared with other existing technical solutions, the method of this embodiment of reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion has the following advantages:

[0065] 1. Complex queries are more accurate. The pseudo-table mode and SQL rewriting technology pioneered by the present invention can effectively reduce the complexity of the SQL that LLM needs to generate for complex queries, reduce the probability that the generated SQL cannot be executed, and reduce the time required for complete SQL generation. On the other hand, the present invention describes the table mode based on csv, so that in addition to the field name, field type, and field remarks that can also be included in other work, the table mode can also contain additional table records, so that the model can learn the specific storage format of the records in the table.

[0066] 2. Lower maintenance cost. The present invention is based on csv to organize the table mode. Compared with other NL2SQL methods that describe the table mode based on database table creation statements, the present invention is easier to be maintained by non-technical personnel.

[0067] 3. Shorter time consumption: The time consumption of NL2SQL mainly lies in the number of tokens required to generate for large models. SQL rewriting technology makes it possible to complete operations that originally required the generation of long SQL statements by generating shorter SQL statements, thus greatly reducing time consumption.

[0068] Example 2

[0069] A method for reducing complex SQL generated by natural language by constructing table mode and SQL conversion, the other features are the same as those of embodiment 1, except that: it also has the following features.

[0070] The method of reducing complex SQL generated by natural language by constructing table schema and SQL conversion in this example also includes step 1.3 of modifying the read database schema to obtain a modified database schema and a corresponding table schema. In step 1.3, the read database schema is modified, specifically deleting the table schema that will not be used in NL2SQL and deleting the unused fields in the table schema.

[0071] Specifically, the read database schema can be modified according to actual business needs to obtain a modified standard database schema and a corresponding standard table schema.

[0072] Through step 1.3, a more suitable database model can be modified according to the user's specific query situation, which can further improve the accuracy of the query, reduce maintenance costs and time consumption.

[0073] Example 3

[0074] This embodiment proposes a solution based on LLM to solve the complex table NL2SQL, proposes a table model representation method that can provide richer information for large models, and proposes a pseudo table model and SQL rewriting solution based on this method to alleviate the problem that LLM is difficult to correctly generate SQL when performing complex queries on multiple tables.

[0075] This embodiment has the following functions:

[0076] Database schema creation and SQL rewriting.

[0077] Database schema generation: describes the schema of the table in the database. The representation of the database schema is decomposed into three parts: table name, table description and field declaration in csv format. The field declaration in csv format contains 5 fields, namely field name, data type, description, data 1, and data 2. Data 1 and data 2 are records randomly sampled from the database, which are used to indicate the storage format of the large model data.

[0078] SQL rewriting: Convert the simple SQL output by LLM to query the pseudo table mode into SQL for querying the real table. Store the fields in multiple tables required for complex queries in a table that does not actually exist in the database. When LLM generates a query for the data in this table, it is rewritten to query the original table.

[0079] This embodiment takes the SQL Service database storing attendance punch-in data as an example to introduce the NL2SQL function of this patent application. Before using the NL2SQL function, it is necessary to configure the database mode.

[0080] The configuration steps are as follows:

[0081] The user configures the database connection information to obtain the read permission for the target database in the database.

[0082] According to the configuration provided by the user, the table name, table description, schema name, field name, field type, and field description are read from the target database. At the same time, two records are randomly read and this information is stored in the database as a table schema in units of tables.

[0083] According to actual business needs, modify the mapping of the read database schema, such as deleting table schemas that will not be used in NL2SQL and fields that are not used in the table schema. The modification can remove fields that users do not care about, such as the update time of the field. As for which fields users do not care about, it can be determined in combination with specific business.

[0084] The configured database mode is shown in Table 1 below:

[0085] Table 1 Table schema read from the target database

[0086]

[0087] The system processes the user's query request as follows: Figure 1 As shown, the specific steps are as follows:

[0088] 2.1 Retrieve the database schema related to the query, specifically by combining vector search and full-text search to retrieve the relevant table schema from the database;

[0089] 2.2 Retrieve SQL examples related to the query, specifically by combining vector search and full-text search to retrieve relevant SQL examples from the database;

[0090] 2.3 Prompt word construction, specifically filling in the query, table mode, and SQL example into the prompt template;

[0091] 2.4 Generate SQL through LLM model;

[0092] 2.5 Rewrite SQL to simplify the original complex SQL operation into single-table operation SQL;

[0093] 2.6 SQL execution.

[0094] In this application, the representation of the database schema is decomposed into three parts: table name, table description and field declaration in csv format. The field declaration in csv format contains 5 fields, namely field name, data type, description, data 1 and data 2. Data 1 and data 2 are records randomly sampled from the table, which are used to indicate the storage format of the large model data.

[0095] A representative database schema hint is shown in Table 2:

[0096] Table 2. Table schema description in csv format

[0097]

[0098] This technology reduces the generation of complex SQL from natural language by building table models and SQL conversion. On a database model based on the CSV format, a pseudo table is built to simplify query operations that originally involved multiple tables into single-table operations.

[0099] A method for reducing complex SQL generated by natural language by constructing table schema and SQL conversion. Fields of two tables are logically merged to obtain a pseudo table. The schema of the logically merged tables is called a pseudo table schema.

[0100] For example, based on the employee table in Table 3 and the punch-in table in Table 4, a pseudo table schema such as Table 5 can be created.

[0101] Table 3 Employee table schema

[0102]

[0103] Table 4: Table schema of the punch-in record table

[0104]

[0105] Table 5 Pseudo table mode description

[0106]

[0107] The characteristic of pseudo table mode is that the information density is higher, and there is no need to consider the corresponding relationship with the database table. For example, for the query requirement "Who is late this week", you can add a row "late, bool, indicating whether the employee is late on that day, 1 is late, 0 is not late" in the pseudo table, without considering whether the late field actually exists in the real database. When the SQL generated by LLM uses this field, you can clearly know the user's question intention, and extract and rewrite the SQL parameters.

[0108] When LLM generates complex SQL, it is very easy to generate SQL that does not conform to the syntax, or conforms to the syntax but the query results are not what the user needs, due to insufficient model capabilities such as model hallucination. SQL rewriting aims to reduce the difficulty of LLM generating SQL and simplify operations that can only be completed by complex SQL into shorter single-table operation SQL.

[0109] After using SQL rewriting technology, LLM only needs to generate simple SQL, and the code logic will restore the SQL. Therefore, LLM needs fewer TOKENs to generate complete SQL, which speeds up the system's response and reduces the cost of TOKEN generation.

[0110] For example, the table schema of two tables is required to use the JOIN syntax, but if the fields of the two tables are logically merged in advance, the JOIN operation can be omitted. The schema of the logically merged tables is called a pseudo table schema.

[0111] A common method to merge the fields of two tables to obtain a pseudo table schema is to collect the SQLs generated by the user's query, and then see which join operations are included in these SQLs. After knowing the common join operations, manually create a pseudo table after joining.

[0112] If the pseudo table mode is used in the SQL generated by the large model based on the user's query, the SQL will be rewritten into SQL that operates the original table according to the rules.

[0113] For example, when the following SQL query is performed on the pseudo table mode shown in Table 6, it will be converted into the original query shown in Table 7.

[0114] Table 6

[0115]

[0116] Table 7

[0117]

[0118] The prefix "E_" stands for "Employee.", "R_" stands for "Record.", and "R_E" stands for "from Recordjoin Employee". This way of writing simplifies the original "table name.field name" expression into field names, preventing the model from mistaking the relationship between tables and fields, and restoring it when rewriting SQL.

[0119] The method of reducing the generation of complex SQL from natural language by constructing table mode and SQL conversion in this implementation has the following advantages compared with other existing technical solutions:

[0120] 1. Complex queries are more accurate. The pseudo-table mode and SQL rewriting technology pioneered by the present invention can effectively reduce the complexity of the SQL that LLM needs to generate for complex queries, reduce the probability that the generated SQL cannot be executed, and reduce the time required for complete SQL generation. On the other hand, the present invention describes the table mode based on csv, so that in addition to the field name, field type, and field remarks that can also be included in other work, the table mode can also contain additional table records, so that the model can learn the specific storage format of the records in the table.

[0121] 2. Lower maintenance cost. The present invention is based on csv to organize the table mode. Compared with other NL2SQL methods that describe the table mode based on database table creation statements, the present invention is easier to be maintained by non-technical personnel.

[0122] 3. Shorter time consumption: The time consumption of NL2SQL mainly lies in the number of tokens required to generate for large models. SQL rewriting technology makes it possible to complete operations that originally required the generation of long SQL statements by generating shorter SQL statements, thus greatly reducing time consumption.

[0123] Finally, it should be noted that the above embodiments are only used to illustrate the technical solution of the present invention rather than to limit the scope of protection of the present invention. Although the present invention has been described in detail with reference to the preferred embodiments, those skilled in the art should understand that the technical solution of the present invention can be modified or replaced by equivalents without departing from the essence and scope of the technical solution of the present invention.

Claims

1. A method for reducing complex SQL generated by natural language by constructing table schema and SQL conversion, characterized by comprising: pre-configuring database schema and processing user requests; Pre-configure the database mode. The specific configuration steps are as follows: 1.1 The user configures the database connection information to obtain the read permission of the target database in the database; 1.2 According to the information configured by the user in step 1.1, the table schema information is read from the target database, and two records are randomly read and named as data 1 and data 2 respectively. The read table schema information and the read records are stored in the database as table schemas in units of tables; the set of stored table schemas constitutes the database schema; The system processes the user's query request in the following steps: 2.1 Retrieve the database schema related to the query, specifically by combining vector search and full-text search to retrieve the relevant table schema from the database; 2.2 Retrieve SQL examples related to the query, specifically by combining vector search and full-text search to retrieve relevant SQL examples from the database; 2.3 Prompt word construction, specifically filling in the query, table mode, and SQL example into the prompt template; 2.4 Generate SQL through LLM model; 2.5 Rewrite SQL to simplify the original complex SQL operations into single-table operation SQL; 2.6 SQL execution; The table of the database model contains table name, table description and field declaration in csv format; the field declaration in csv format contains 5 fields, namely field name, data type, description, data 1 and data 2; In the database mode based on CSV format, a pseudo table is constructed to simplify the query operation originally involving multiple tables into a single table operation; A pseudo table is obtained by logically merging the fields of two tables. The mode of the logically merged tables is called the pseudo table mode. If the pseudo table mode is used in the SQL generated by the large model based on the user's query, the SQL is rewritten into the SQL that operates the original table according to the rules.

2. The method for reducing the generation of complex SQL from natural language by constructing table schema and SQL conversion according to claim 1 is characterized in that: the table schema information in step 1.2 is table name, table description, schema name, field name, field type, and field description.

3. The method for reducing complex SQL generated by natural language by constructing table schema and SQL conversion according to claim 2, characterized in that: It also includes step 1.3 of modifying the read database schema to obtain a modified database schema and a corresponding table schema.

4. The method for reducing complex SQL generated by natural language by constructing table schema and SQL conversion according to claim 3, characterized in that: In step 1.3, modify the read database schema, specifically delete the table schema that will not be used in NL2SQL and delete the fields that are not used in the table schema.

5. The method for reducing complex SQL generated by natural language by constructing table schema and SQL conversion according to claim 4, characterized in that: In step 1.3, the read database schema is modified according to actual business needs to obtain a modified standard database schema and a corresponding standard table schema.

Images