Two-stage Text-to-SQL generation method based on VIEW and LLM

Abstract

A two-stage Text-to-SQL generation method based on VIEW and LLM, the first stage: filling the user query request and the description information of the View into the large language model, and the large language model generates the View query statement; the second stage: filling the creation syntax of the View in the preprocessing stage and the View query statement generated in the first stage into the large language model, and the large language model restores the syntax of querying the View to the SQL for querying the original table of the database. A preprocessing stage is also provided: creating a View for the database and obtaining the creation syntax of the View at the same time. The present invention can improve the accuracy of understanding and querying of the large language model, improve response time, and improve processing efficiency.

Description

Technical Field

[0001] The present invention relates to the technical field of converting text into structured query language of database, and in particular to a two-stage Text-to-SQL generation method based on VIEW and LLM. Background Art

[0002] The task of converting text to SQL (also called Text-to-SQL or text2sql) is to convert text into the structured query language (SQL) of the database. 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 Text-to-SQL 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), Text-to-SQL based on LLM has become a widely-watched exploration direction. With its powerful language understanding ability, LLM can achieve good results with prompt words without fine-tuning. Although Text-to-SQL based on large language models has made breakthroughs compared to previous methods, the design pattern of the database itself is based on data preservation considerations rather than facilitating the understanding of large language models, making it difficult for large language models to accurately use database information. For example: foreign keys in a database can maintain data consistency and association between two tables, but after a large language model uses foreign keys to join multiple tables, it may incorrectly match tables and fields during select operations or where operations.

[0005] Therefore, in order to avoid the problem that the large language model misunderstands the database schema and causes the performance of the Text-to-SQL task to deteriorate, it is necessary to provide a two-stage Text-to-SQL generation method based on VIEW and LLM to solve the shortcomings of the existing technology. Summary of the invention

[0006] The purpose of the present invention is to avoid the shortcomings of the prior art and provide a two-stage Text-to-SQL generation method based on VIEW and LLM, which can improve the accuracy of large language model understanding and query and improve processing efficiency.

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

[0008] A two-stage Text-to-SQL generation method based on VIEW and LLM is provided, including:

[0009] Phase 1: Fill the user query request and the View description information into the first prompt, input the first prompt into the large language model, and generate the View query statement by the large language model;

[0010] Phase 2: Fill the View creation syntax in the preprocessing phase and the View query statement generated in the first phase into the second prompt, input the second prompt into the large language model, and the large language model restores the View query syntax to the SQL for querying the original database table.

[0011] Preferably, the above two-stage Text-to-SQL generation method based on VIEW and LLM is further provided with a preprocessing stage before the first stage: creating a View for the database and obtaining the creation syntax of the View.

[0012] Preferably, in the above two-stage Text-to-SQL generation method based on VIEW and LLM, in the preprocessing stage, a View is created for the database so that no join operation is required when querying requirements through the View.

[0013] Preferably, in the above two-stage Text-to-SQL generation method based on VIEW and LLM, in the preprocessing stage, the standard for creating View for the database is to eliminate foreign keys.

[0014] Preferably, in the above-mentioned two-stage Text-to-SQL generation method based on VIEW and LLM, in the first stage, when a user asks a question, the user's question is converted into a vector, and the question vector is used to match "SQL example" and "View description" in the database, and the matched information and user question are filled into the first prompt, and the first prompt is input into the large language model, and the large language model generates a View query statement.

[0015] Preferably, in the above two-stage Text-to-SQL generation method based on VIEW and LLM, the first prompt is set as a format template.

[0016] Preferably, in the above-mentioned two-stage Text-to-SQL generation method based on VIEW and LLM, in the second stage, the View creation syntax in the preprocessing stage and the View query statement generated in the first stage are filled into the second prompt and input into the large language model, and the large language model restores the syntax for querying the View into SQL for querying the original table of the database.

[0017] Preferably, in the above two-stage Text-to-SQL generation method based on VIEW and LLM, in the second stage, the method of restoring the syntax of querying View to SQL for querying the original table of the database by the large language model is: firstly, the original table is schema-linked using the query statement of View and the creation statement of View to determine the table to be queried and the fields in the table;

[0018] Then fill the View query statement, View creation statement, table description after schema linking, and user question into the prompt and input the prompt into the large language model to obtain the SQL for querying the original table of the database.

[0019] Preferably, in the above two-stage Text-to-SQL generation method based on VIEW and LLM, the second prompt is set as a format template.

[0020] The present invention discloses a two-stage Text-to-SQL generation method based on VIEW and LLM. In the first stage, the user query request and the description information of the View are filled into the large language model, and the large language model generates the View query statement; in the second stage, the creation syntax of the View in the preprocessing stage and the View query statement generated in the first stage are filled into the large language model, and the large language model restores the syntax of querying the View to the SQL for querying the original table of the database. The present application solves the Text-to-SQL problem of complex tables based on LLM, and proposes a SQL generation scheme based on View and LLM. The scheme re-describes the database with the help of View, which reduces the difficulty of the large language model to understand the database model. Then, according to the creation statement of the View, the generated View query statement is converted, and the syntax of querying the View is restored to the SQL for querying the original table of the database by the large language model, avoiding the problem of low query efficiency when relying on View query and possible inconsistency with the direct query database result. It can improve the accuracy of understanding and querying of the large language model, improve response time, and improve processing efficiency. DETAILED DESCRIPTION

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

[0022] Example 1

[0023] This embodiment proposes a solution to solve the complex table Text-to-SQL based on LLM, and proposes a SQL generation solution based on View and LLM. This solution uses View to re-describe the database, reducing the difficulty of the large language model to understand the database model. Then, according to the View creation statement, the generated View query statement is converted, and the large language model restores the syntax of querying View to SQL for querying the original table of the database, avoiding the problem of low query efficiency when relying on View query and possible inconsistency with direct database query results.

[0024] A two-stage Text-to-SQL generation method based on VIEW and LLM in this embodiment includes the following processing stages:

[0025] Preprocessing phase: Create a View for the database and obtain the View creation syntax.

[0026] Phase 1: Fill the user query request and View description information into the first prompt, input the first prompt into the large language model, and generate the View query statement by the large language model.

[0027] Phase 2: Fill the View creation syntax in the preprocessing phase and the View query statement generated in the first phase into the second prompt, input the second prompt into the large language model, and the large language model restores the View query syntax to the SQL for querying the original database table.

[0028] In the preprocessing stage, a View is created for the database, and the creation syntax of the View is obtained. In the preprocessing stage, a View is created for the database so that the join operation is not required when querying the requirements through the View. In the preprocessing stage, the standard for creating a View for the database is to eliminate foreign keys.

[0029] A foreign key is a special field in a relational database that maintains data consistency. Two tables are associated through a foreign key. For example, a school's database contains the following two tables.

[0030] The student table has fields: student ID, major ID, age, name.

[0031] The major table has fields: major ID, major name, and major person in charge.

[0032] At this point, the major ID in the student table is a foreign key, which links the student table to the major table. This design pattern is based on relational databases, but it is not conducive to LLM's understanding of the entire database model because it makes the database more complicated. Views are designed to eliminate foreign keys.

[0033] The two-stage Text-to-SQL generation method based on VIEW and LLM of this embodiment includes: in the first stage, filling the user query request and the description information of the View into the first prompt, inputting the first prompt into the large language model, and generating the View query statement by the large language model.

[0034] In the first stage, when a user asks a question, the user's question is converted into a vector, and the question vector is used to match the "SQL example" and "View description" in the database. The matched information and the user's question are filled into the first prompt and input into the large language model, which generates the View query statement. The first prompt can be set as a format template.

[0035] Database schema description is a key issue for Text-to-SQL. Only after the database schema is accurately described can LLM be generated. When designing a relational database, data consistency must be followed. To ensure data consistency, foreign keys establish reference relationships between tables to ensure that the referenced data is logically consistent. In addition, foreign keys can also ensure data integrity and minimize data redundancy. However, the use of foreign keys increases the difficulty for large language models to understand database schemas. Especially when a query requires multiple tables to be joined, it becomes very necessary to describe the database schema with LLM as the center rather than with the database as the center. In order to build a table suitable for LLM understanding, the related data originally scattered in multiple tables should be aggregated and displayed under the same View without affecting the stored data.

[0036] For example, a school's database contains the following two tables:

[0037] The student table has fields: student ID, major ID, age, name.

[0038] The major table has fields: major ID, major name, and major person in charge.

[0039] Now, in order to aggregate the data scattered in two tables into one table, we can simplify the SQL generated by LLM. We can use the following View syntax:

[0040] Create View v_student as

[0041] Select

[0042] Student ID,

[0043] Professional ID,

[0044] age,

[0045] name,

[0046] Professional name,

[0047] Professional person in charge

[0048] From Student Table

[0049] Join majortable on majortable.majorID=studenttable.majorID.

[0050] It should be noted that how to create the View syntax depends on how to query in a specific business. The technical means of selecting the View syntax according to different business queries is common knowledge in the art and will not be described in detail here.

[0051] In the two-stage Text-to-SQL generation method based on VIEW and LLM of this embodiment, in the second stage, the creation syntax of the View in the preprocessing stage and the View query statement generated in the first stage are filled into the second prompt and input into the large language model, and the large language model restores the syntax of querying the View into the SQL for querying the original table of the database.

[0052] In the second stage, the big language model restores the syntax of querying the View to the SQL for querying the original table of the database by first using the View query statement and the View creation statement to perform schema linking on the original table to determine the table and fields in the table to be queried;

[0053] Then fill the View query statement, View creation statement, table description after schema linking, and user question into the prompt and input the prompt into the large language model to obtain the SQL for querying the original table of the database.

[0054] The View obtained through the join operation mentioned above simplifies the database structure and expresses the database model in a more concise way. However, the View obtained through the join operation cannot completely replace the table because the View has two disadvantages:

[0055] 1) View is the result of data query. Querying View is a secondary query of the query result, so the efficiency is lower than directly querying the table.

[0056] 2). Using join operation to obtain View is equivalent to performing set operation on multiple tables. Therefore, compared with directly querying the table, the result of querying View will have different data volume. For example, for the following tables student and major, if you use create View v_student as select student.id, student.name, student.score, major.major from student left major on student.major_id=major.id. Then the newly created View v_student cannot accurately answer the question "how many majors are there in total". Therefore, it is necessary to restore the sql querying View to the sql querying the table.

[0057] Table student

[0058]

[0059] Table major

[0060]

[0061] Table View v_student

[0062]

[0063] In order to simplify the database joint query operation while using View, and avoid the problems caused by using View. A solution is proposed to convert the SQL query of View into the query table. This solution first uses the SQL query statement of View and the creation statement of View to schema link the original table to determine the table and fields in the table to be queried. For example, for the question "Please calculate the average credit time of students in different majors", based on the table View v_student, the SQL used is: "SELECT major, AVG(score) AS average_score FROM v_student GROUP BY major;". From this SQL, it can be determined that v_student is used, and from the creation statement of v_student, it can be determined that the fields name, score, major_id in the table student and id and major in the table major are used.

[0064] Then fill the View query statement, View creation statement, table description after schema linking, and user question into the second prompt template below, input the prompt into the big language model, and the big language model will restore the syntax of querying the View to the SQL for querying the original table of the database.

[0065] # target

[0066] According to the “Views schemas”, “View query” and “relative tableschemas”, restore the original table query without explanation

[0067] notice:

[0068] 1. SQL should contain “join” as little as possible.

[0069] 2. table in “Views schemas” should not be contained in output.

[0070] 3. comply with the syntax of {db_type}.

[0071] 4. must avoid ambiguous column name by using table name and columnname in the SQL statement. For example, use `table_name.column_name` instead of just `column_name`.

[0072] # Views schemas

[0073] {Views}

[0074] # View query

[0075] ```sql

[0076] {query_sql}

[0077] ```

[0078] intention: {query}

[0079] # relative table schemas

[0080] {prompt_table}

[0081] #foramt

[0082] ```sql

[0083] {sql}

[0084] ```.

[0085] The present invention provides a two-stage Text-to-SQL generation method based on VIEW and LLM, and proposes a SQL generation solution based on View and LLM based on the solution of solving the Text-to-SQL of complex tables. This solution redescribes the database with the help of View, which reduces the difficulty of the large language model to understand the database mode. Then, according to the creation statement of View, the generated SQL is converted, avoiding the problems of low query efficiency when relying on View query and possible inconsistency with the direct query database result. It can improve the accuracy of understanding and query of the large language model, improve response time, and improve processing efficiency.

[0086] Example 2

[0087] The two-stage Text-to-SQL generation method based on VIEW and LLM of this embodiment has the same other features as those of Embodiment 1, except that the method of this embodiment also has the following features: The first prompt is set as a format template. The second prompt is also set as a format template.

[0088] The prompt template used in the first stage is the first prompt, which is used to generate SQL for querying the view based on the user query and the view description. The view description is the variable relevant_db_schema. The first prompt is set as a format template. This embodiment provides one of the format templates:

[0089] # target

[0090] You are an experienced database administrator, please answer "question" by sql with no explanation.

[0091] notice:

[0092] 1.Do not alias the output fields

[0093] 2. must avoid ambiguous column name by using table name and columnname in the SOLstatement. For example, use table name,column name insteadofjust column name.

[0094] 3. refer to "relevant db schema"

[0095] # relevant db schemarelevant db schema)

[0096] # question{query}

[0097] The prompt template used in stage 2 is the second prompt, which is used to restore the SQL querying the view in stage 1 (i.e. the variable dummy_sql) to the SQL querying the original table. The view_schemas below is the syntax for creating the view, and table_schemas is the table description of the database.

[0098] The second prompt is set as a format template. This embodiment provides one of the format templates:

[0099] # target

[0100] According to the "views schemas", "view query" and "relevant tableschemas", restore the sql in "view query" to one used the original tablequery without explanation

[0101] notice:

[0102] 1. SQL should contain "join" as little as possible.

[0103] 2. table in "views schemas" should not be contained in output.

[0104] 3. comply with the syntax of {db_type}.

[0105] 4. must avoid ambiguous column name by using table name and columnname in the SOLstatement. For example, use table name,column name instead ofjust column name

[0106] # view schemasview schemas!

[0107] # view queryQuestion: {query}sql{dummy sql}, 1

[0108] # relevant table schemasftable schemas!

[0109] #foramt

[0110] 11sql

[0111] {sql}11.

[0112] The present invention provides a two-stage Text-to-SQL generation method based on VIEW and LLM, and proposes a SQL generation solution based on View and LLM based on the solution of solving the Text-to-SQL of complex tables. This solution redescribes the database with the help of View, which reduces the difficulty of the large language model to understand the database mode. Then, according to the creation statement of View, the generated SQL is converted, avoiding the problems of low query efficiency when relying on View query and possible inconsistency with the direct query database result. It can improve the accuracy of understanding and query of the large language model, improve response time, and improve processing efficiency.

[0113] 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 two-stage Text-to-SQL generation method based on VIEW and LLM, characterized in that: include: Preprocessing stage: Create a View for the database and obtain the View creation syntax; The standard for creating views for the database in the preprocessing phase is to eliminate foreign keys so that no join operation is required when querying requirements through the views; Phase 1: Fill the user query request and the View description information into the first prompt, input the first prompt into the large language model, and generate the View query statement by the large language model; Phase 2: Fill the View creation syntax in the preprocessing phase and the View query statement generated in the first phase into the second prompt, input the second prompt into the large language model, and the large language model restores the View query syntax to the SQL for querying the original database table; In the first stage, when a user asks a question, the user's question is converted into a vector, and the question vector is used to match the "SQL example" and "View description" in the database. The matched information and the user's question are filled into the first prompt, and the first prompt is input into the large language model, which generates a View query statement. In the second stage, the View creation syntax in the preprocessing stage and the View query statement generated in the first stage are filled into the second prompt and input into the large language model. The large language model restores the View query syntax to the SQL for querying the original database table. In the second stage, the big language model restores the syntax of querying the View to the SQL for querying the original table of the database by first linking the original table architecture with the View query statement and the View creation statement to determine the table and fields in the table to be queried; Then fill the View query statement, View creation statement, table description after schema linking, and user question into the prompt and input the prompt into the large language model to obtain the SQL for querying the original table of the database.

2. The two-stage Text-to-SQL generation method based on VIEW and LLM according to claim 1, characterized in that: The first prompt is set to the format template.

3. The two-stage Text-to-SQL generation method based on VIEW and LLM according to claim 1, characterized in that: The second prompt is set as a format template.

Images