Method and equipment for improving Text-to-SQL (Structured Query Language) conversion accuracy, medium and program product

Abstract

The invention provides a method and equipment for improving Text-to-SQL (Structured Query Language) conversion accuracy, a medium and a program product. The method comprises the following steps: connecting a target database to obtain DDL (Double Data Language) information of all tables in the target database; extracting effective information of all tables in the database based on the acquired data to establish a table association relation graph, and performing near-synonym expansion on DDL information; when a user inputs a natural language question, an entity contained in the question is extracted and combined with the question to perform RAG retrieval, reachability analysis is performed based on a table association relation graph, and DDL information is used as a context to be transmitted to a large model after a correlation table is complemented, so that the large model generates an SQL (Structured Query Language); performing correctness verification on the generated SQL by using the table association relation graph; if the verification is passed, the generated SQL is returned to the user, and if the verification is not passed, automatic correction is tried, and the corrected SQL is returned to the user. According to the method and the device, the SQL generation accuracy can be improved.

Description

Technical Field

[0001] The present invention relates to the technical field of Text-to-SQL conversion, and in particular to a method, device, medium and program product for improving the accuracy of Text-to-SQL conversion. Background Art

[0002] In recent years, with the development of big data and artificial intelligence, large language models have been widely used, and the technology of converting natural language text into structured query language (Text-to-SQL, Text2SQL for short) using large language models has become increasingly important. It is widely used in intelligent database query, business intelligence and other fields to help users express query requirements through natural language without having to master complex SQL syntax.

[0003] At present, most Text2SQL systems based on general large language models perform well when processing single tables, but face challenges when processing multi-table queries. Although using general large language models for Text2SQL conversion avoids the cost of training special domain models and has good flexibility and scalability, it also increases the possibility that the model will be affected by the noise of its own training data when generating SQL, and the model will lack some necessary domain knowledge. These problems are particularly obvious when querying multiple tables. Compared with single-table queries, multi-table queries need to consider the relationship between tables, connection conditions, and complex query logic, which increases the amount of information that the model needs to process and the difficulty of the task, resulting in a low accuracy rate of multi-table Text2SQL systems and poor quality of generated SQL statements, which is specifically manifested in the generated field names or table names being false, or the table connection relationship in the JOIN statement being wrong, etc. Summary of the invention

[0004] The present invention aims to provide a method, device, medium and program product for improving the accuracy of Text-to-SQL conversion, so as to solve the problems that the accuracy of a multi-table Text2SQL system is low and the quality of the generated SQL statements is poor, which is specifically manifested in that the generated field names or table names are false, or the table connection relationship in the JOIN statement is wrong.

[0005] The present invention provides a method for improving the accuracy of Text-to-SQL conversion, comprising the following steps:

[0006] S1. Connect to the target database to obtain DDL information of all tables in the target database, including comment information of tables and fields;

[0007] S2. Extract valid information of all tables in the database based on the acquired data to establish a table association diagram, and perform synonym expansion on the remark information of tables and fields in the DDL information and store it in the vector database;

[0008] S3. When the user inputs a natural language question, the entities contained in the question are extracted, and RAG search is performed together with the question. A reachability analysis is performed based on the table association graph. After the relevant tables are supplemented, the DDL information is passed to the big model as context to generate SQL statements.

[0009] S4. Use the table association diagram to check the correctness of the generated SQL statement, including table association verification, field name verification, and table name verification;

[0010] S5. If the verification passes, the generated SQL statement is returned to the user. If not, automatic correction is attempted and the corrected SQL statement is returned to the user.

[0011] In some embodiments, step S1 includes the following sub-steps:

[0012] S11, knowing that the target database is a relational database MySQL, obtaining the connection parameters of the target database and establishing a connection;

[0013] S22. After the connection is successfully established, query the DDL information of all tables in the mode through query statements.

[0014] In some embodiments, in step S2, connection information including table name, table field name, and field foreign key is extracted from the DDL information of each table according to SQL syntax rules to form a table association relationship diagram.

[0015] In some embodiments, in step S2, the table association relationship diagram is defined as S = {T1, T2, ..., Ti, E} when stored, where Ti represents the i-th table, E represents the primary and foreign key relationship between each table, and the database table Ti contains columns Ci = {Ci1, ..., Cij}, where Cij represents the j-th field of the i-th table.

[0016] In some embodiments, if the table is not created in a standardized manner and the connection information between tables is not strictly specified, manual information completion is used.

[0017] In some embodiments, in step S3, performing reachability analysis based on the table association relationship graph includes:

[0018] Abstract the table association graph into an undirected graph:

[0019] The reachability between all vertex pairs, i.e., all tables, is determined by identifying the connected components of the wireless graph.

[0020] In some embodiments, step S4 includes the following sub-steps:

[0021] S41, extracting table names, field names and association relationships involved in the SQL statement;

[0022] S42, first perform field name verification and table name verification, and after all field names and table names are verified, perform table association verification.

[0023] The present invention also provides an electronic device, comprising:

[0024] at least one processor; and a memory communicatively coupled to the at least one processor;

[0025] The memory stores instructions that can be executed by the at least one processor, and the at least one processor executes the instructions stored in the memory, so that the at least one processor performs the method.

[0026] The present invention also provides a computer-readable storage medium, wherein the computer-readable storage medium is used to store instructions, and when the instructions are executed, the above method is implemented.

[0027] The present invention also provides a computer program product, which, when called by a computer, enables the computer to execute the above method.

[0028] In summary, due to the adoption of the above technical solution, the beneficial effects of the present invention are:

[0029] The existing solutions that avoid fine-tuning and do multi-table Text2SQL based on a general large language model are mostly to improve the generation accuracy by storing table DDL information and preset question-answer pairs in a vector database. When a user asks a question, the RAG technology is used to directly match the question vector, retrieve relevant information, and generate SQL without correctness verification based on the database structure. Although these methods are convenient and fast, the disadvantage is that they sacrifice a certain matching effect. Because the preset questions are not enough to cover all the questions asked by the user, and the user does not bring specific field names and table names when asking questions, such as the example question: What courses did Xiao Ming choose? If the question is not in the preset question, the RAG search will not associate Xiao Ming with the name in the DDL information, which may cause the retrieved table DDL to be incomplete or incorrect. In the present invention, adding entity extraction and synonym expansion can associate Xiao Ming-character-student table. Further, the table association relationship accessibility analysis can associate the student table-course table-student course selection table, avoiding the omission of context information transmitted to the large model. Finally, SQL verification based on the table association diagram of the generated SQL also greatly weakens the influence of the large model illusion and improves the accuracy of the generated SQL BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 The present invention is a flowchart of a method for improving the accuracy of Text-to-SQL conversion in an embodiment of the present invention.

[0031] Figure 2 The figure is a schematic diagram of a multi-table query example in an embodiment of the present invention.

[0032] Figure 3 This is an example diagram showing the DDL information of all tables in the query mode through a query statement in an embodiment of the present invention.

[0033] Figure 4 This is a schematic diagram of an example of a table association relationship diagram in an embodiment of the present invention.

[0034] Figure 5 A schematic diagram of the structure of an electronic device provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0035] In order to make the purpose, technical solutions and advantages of the embodiments of the present invention clearer, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Generally, the components of the embodiments of the present invention described and shown in the drawings here can be arranged and designed in various different configurations.

[0036] Therefore, the following detailed description of the embodiments of the present invention provided in the accompanying drawings is not intended to limit the scope of the invention claimed for protection, but merely represents selected embodiments of the present invention. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0037] Example

[0038] like Figure 1 As shown, this embodiment proposes a method for improving the accuracy of Text-to-SQL conversion, comprising the following steps:

[0039] S1. Connect to the target database to obtain DDL (Data Definition Language) information of all tables in the target database, including the remark information of the tables and fields;

[0040] S2. Extract valid information of all tables in the database based on the acquired data to establish a table association diagram, and perform synonym expansion on the remark information of tables and fields in the DDL information and store it in the vector database;

[0041] S3. When the user inputs a natural language question, the entities contained in the question are extracted, and RAG search is performed together with the question. A reachability analysis is performed based on the table association graph. After the relevant tables are supplemented, the DDL information is passed to the big model as context to generate SQL statements.

[0042] S4. Use the table association diagram to check the correctness of the generated SQL statement, including table association verification, field name verification, and table name verification;

[0043] S5. If the verification passes, the generated SQL statement is returned to the user. If not, automatic correction is attempted and the corrected SQL statement is returned to the user.

[0044] In order to make the above-mentioned objectives, technical solutions and advantages of the present invention more clearly understood, the present invention is further described in detail below with reference to the accompanying drawings.

[0045] by Figure 2 The multi-table query shown is used as an application case to illustrate a method for improving the accuracy of Text-to-SQL conversion provided by the present invention, and the case includes the following steps:

[0046] S1. Connect to the target database to obtain the DDL information of all tables in the target database, including the comment information of tables and fields. Specifically, it includes the following sub-steps:

[0047] S11, knowing that the target database is a relational database MySQL, obtaining the connection parameters of the target database and establishing a connection;

[0048] S22. After successfully establishing the connection, query the DDL information of all tables in the mode through query statements, such as Figure 3 shown.

[0049] S2. Extract valid information of all tables in the database based on the acquired data to establish a table association diagram, and perform synonym expansion on the remark information of tables and fields in the DDL information and store it in the vector database. This includes the following sub-steps:

[0050] S21. From the DDL information of each table, according to the SQL syntax rules, the table name, table field name, field foreign key and other connection information are extracted to form a table association diagram, such as Figure 4 As shown, the table association diagram is defined as S = {T1, T2, ..., Ti, E} when stored, where Ti represents the ith table, E represents the primary and foreign key relationship between the tables, and the database table Ti contains columns Ci = {Ci1, ..., Cij}, where Cij represents the jth field of the ith table. It should be noted that if the table is not built in a standardized manner and the connection information between tables is not strictly specified, it is recommended to manually complete the information. An accurate and complete table association diagram is very important for the accuracy of Text2SQL.

[0051] S22. The remark information in the DDL information is expanded by synonyms and stored in the vector database. For example, for a name, the synonym expansion information includes name, student name, person, etc. Another example is a student course selection table, and the synonym expansion information is a student course schedule, etc. The synonym expansion can improve the accuracy of the subsequent RAG search.

[0052] S3: When the user inputs a natural language question, extract the entities contained in the question, perform RAG search together with the question, and perform reachability analysis based on the table association diagram. After supplementing the relevant tables, pass the DDL information as context to the big model to generate SQL statements. The specific steps include:

[0053] S31. After the user enters a natural language question, the big model summarizes and extracts the entities involved in the question, such as Figure 2 As shown in the figure, the question is: What courses did Xiao Ming take? The entities [Person] and [Course] are extracted. Based on the DDL information after synonym expansion, RAG search finds three related tables: student table, course table, and student course selection table. This is an ideal situation, and all are hit directly.

[0054] S32. In non-ideal situations, for example, only two related tables, the student table and the course table, may be found. In this case, the reachability analysis of the table association graph is very important. In graph theory, reachability refers to the ease of getting from one vertex to another in a graph. The table association graph can be abstracted as an undirected graph. The reachability between all vertex pairs, that is, all tables, can be determined by identifying the connected components of the graph. Commonly used algorithms include Floyd-Warshall, Kameda, Thorup, etc. Generally speaking, in the Text2SQL scenario, there is only one or a few pieces of data to be queried, and a breadth-first search can be used directly. Perform a breadth-first search on the course table and the student table. They can also reach the student course selection table. Then, the DDL information for adding the student course selection table is passed to the large model as context for reference. Following the principle, the context information can be more, but it cannot be missed.

[0055] S33. Build the DDL information of all the tables found above into the prompt passed to the big model as the context knowledge for the big model to answer, and let it generate SQL statements. In the example scenario, the SQL statement generated by the big model is: SELECT s.name, c.course_name FROM students s JOIN enrollments e ON s.student_id=e.student_id JOIN courses c ON e.course_id=c.course_id WHERE s.name='Xiaoming';

[0056] S4. Use the table association diagram to check the correctness of the generated SQL statement, including table association verification, field name verification and table name verification. Specifically, the following steps are included:

[0057] S41. There are two methods to extract the table names, field names and relationships involved in the SQL statement. One is to match and extract based on the SQL syntax, and the other is to use a large model to extract. The current large language model extraction effect is very good. This case uses the prompt word project to allow the large model to extract. The results of the SQL table name and related field name extraction are: [students.name, students.student_id, enrollments.student_id, enrollments.course_id, courses.course_id, courses.course_name], and the result of the relationship extraction is: [enrollments.student_id = students.student_id, enrollments.course_id = courses.course_id].

[0058] S42. First, perform field name verification and table name verification. For example, students.name. In the table association diagram, you can find that the students table (student table) has a field name name (name), and the field verification of this table will pass. After verifying all field names and table names, perform table association verification. For example, enrollments.student_id = students.student_id. In the table association diagram, you can find that the enrollments table (student course selection table) is connected to the student_id (student number) of the students table (student table), and the verification passes.

[0059] S5. If the verification passes, the generated SQL statement is returned to the user. If not, automatic correction is attempted and the corrected SQL statement is returned to the user. The specific steps include:

[0060] S51. The verification in the case is passed, so the generated SQL statement will be directly returned to the user.

[0061] S52. If the name in the generated SQL is changed to student_name, the verification will fail at the table name and field name stage, because the students table does not have the field student_name. At this time, the error reason [the students table does not have the field student_name] and the DDL information of the related table, i.e. the students table, will be passed to the large model to automatically correct it and regenerate SQL until it passes the verification.

[0062] Based on the same technical concept, the embodiment of the present application also provides an electronic device, which can implement the method flow of improving the accuracy of Text-to-SQL conversion provided by the above embodiment of the present application. In one embodiment, the electronic device can be a server, or a terminal device or other electronic device. Figure 5 As shown, the electronic device may include:

[0063] At least one processor, and a memory connected to the at least one processor. The specific connection medium between the processor and the memory is not limited in the embodiment of the present application. Figure 5 The example in this article is that the processor and memory are connected through a bus. Figure 5 The connections between other components are shown in bold lines, which are only for illustration and are not intended to be limiting. The bus can be divided into address bus, data bus, control bus, etc. Figure 5 In the diagram, only one thick line is used, but this does not mean that there is only one bus or only one type of bus. Alternatively, a processor can also be called a controller, and there is no limitation on the name.

[0064] In an embodiment of the present application, the memory stores instructions that can be executed by at least one processor, and the at least one processor can execute a method for improving the accuracy of Text-to-SQL conversion discussed above by executing the instructions stored in the memory. The processor can implement Figure 5 The functions of each module in the device shown.

[0065] Among them, the processor is the control center of the device, which can use various interfaces and lines to connect the various parts of the entire control device, and monitor the device as a whole by running or executing instructions stored in the memory and calling data stored in the memory, various functions of the device and processing data.

[0066] In an optional design, the processor may include one or more processing units, and the processor may integrate an application processor and a modem processor, wherein the application processor mainly processes an operating system, a user interface, and application programs, and the modem processor mainly processes wireless communications. It is understandable that the modem processor may not be integrated into the processor. In some embodiments, the processor and the memory may be implemented on the same chip, and in some embodiments, they may also be implemented separately on separate chips.

[0067] The processor may be a general-purpose processor, such as a CPU, a digital signal processor, an application-specific integrated circuit, a field programmable gate array or other programmable logic device, a discrete gate or transistor logic device, or a discrete hardware component, which may implement or execute the methods, steps, and logic block diagrams disclosed in the embodiments of the present application. A general-purpose processor may be a microprocessor or any conventional processor, etc. The steps of a method for improving the accuracy of Text-to-SQL conversion disclosed in the embodiments of the present application may be directly embodied as being executed by a hardware processor, or may be executed by a combination of hardware and software modules in the processor.

[0068] As a non-volatile computer-readable storage medium, the memory can be used to store non-volatile software programs, non-volatile computer executable programs and modules. The memory can include at least one type of storage medium, for example, it can include flash memory, hard disk, multimedia card, card-type memory, random access memory (Random Access s Memory, RAM), static random access memory (Static Random Access Memory, SRAM), programmable read-only memory (Programmable Read Only Memory, PROM), read-only memory (Read Only Memory, ROM), electrically erasable programmable read-only memory (Electrically Erasable Programmable Read-Only Memory, EEPROM), magnetic storage, disk, optical disk, etc. The memory is any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto. The memory in the embodiment of the present application can also be a circuit or any other device that can realize a storage function, for storing program instructions and / or data.

[0069] By designing and programming the processor, the code corresponding to the method for improving the accuracy of Text-to-SQL conversion introduced in the above embodiment can be fixed into the chip, so that the chip can perform the steps of the method for improving the accuracy of Text-to-SQL conversion in the above embodiment when running. How to design and program the processor is a technology well known to those skilled in the art and will not be described in detail here.

[0070] Based on the same inventive concept, an embodiment of the present application further provides a storage medium storing computer instructions. When the computer instructions are executed on a computer, the computer executes a method for improving the accuracy of Text-to-SQL conversion discussed above.

[0071] In some optional embodiments, the present application also provides various aspects of a method for improving Text-to-SQL conversion accuracy, which can also be implemented in the form of a program product, which includes program code. When the program product is run on an apparatus, the program code is used to enable the control device to execute the steps of a method for improving Text-to-SQL conversion accuracy according to various exemplary embodiments of the present application described above in this specification.

[0072] It should be noted that, although several units or subunits of the device are mentioned in the above detailed description, this division is merely exemplary and not mandatory. In fact, according to the embodiments of the present application, the features and functions of two or more units described above can be embodied in one unit. Conversely, the features and functions of a unit described above can be further divided into multiple units to be embodied. In addition, although the operation of the method of the present application is described in a specific order in the accompanying drawings, this does not require or imply that these operations must be performed in this specific order, or that all the operations shown must be performed to achieve the desired result. Additionally or alternatively, some steps can be omitted, multiple steps can be combined into one step for execution, and / or one step can be decomposed into multiple steps for execution.

[0073] Those skilled in the art will appreciate that the embodiments of the present application may be provided as methods, systems, or computer program products. Therefore, the present application may adopt the form of a complete hardware embodiment, a complete software embodiment, or an embodiment in combination with software and hardware. Moreover, the present application may adopt the form of a computer program product implemented in one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) that include computer-usable program code.

[0074] The present application is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to the embodiments of the present application. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of the processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to generate a server, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the process in the flowchart. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0075] Program code for performing the operations of the present application may be written using any combination of one or more programming languages, including object-oriented programming languages ​​such as Java, C++, etc., and conventional procedural programming languages ​​such as "C" or similar programming languages. The program code may be executed entirely on the user computing device, partially on the user device, as a stand-alone software package, partially on the user computing device and partially on a remote computing device, or entirely on a remote computing device or server.

[0076] Where a remote computing device is involved, the remote computing device may be connected to the user computing device through any type of network, including a local area network (LAN) or a wide area network (WAN), or may be connected to an external computing device (e.g., via the Internet using an Internet service provider).

[0077] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture comprising an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0078] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0079] The above description is only a preferred embodiment of the present invention and is not intended to limit the present invention. For those skilled in the art, the present invention may have various modifications and variations. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present invention shall be included in the protection scope of the present invention.

Claims

1. A method for improving the accuracy of Text-to-SQL conversion, characterized in that: The steps include: S1. Connect to the target database to obtain DDL information of all tables in the target database, including comment information of tables and fields; S2. Extract valid information of all tables in the database based on the acquired data to establish a table association diagram, and perform synonym expansion on the remark information of tables and fields in the DDL information and store it in the vector database; S3. When the user inputs a natural language question, the entities contained in the question are extracted, and RAG search is performed together with the question. A reachability analysis is performed based on the table association graph. After the relevant tables are supplemented, the DDL information is passed to the big model as context to generate SQL statements. S4. Use the table association diagram to check the correctness of the generated SQL statement, including table association verification, field name verification, and table name verification; S5. If the verification passes, the generated SQL statement is returned to the user. If not, automatic correction is attempted and the corrected SQL statement is returned to the user.

2. The method for improving the accuracy of Text-to-SQL conversion according to claim 1, characterized in that: Step S1 includes the following sub-steps: S11, knowing that the target database is a relational database MySQL, obtaining the connection parameters of the target database and establishing a connection; S22. After the connection is successfully established, query the DDL information of all tables in the mode through query statements.

3. The method for improving the accuracy of Text-to-SQL conversion according to claim 1, characterized in that: In step S2, connection information including table name, table field name, and field foreign key is extracted from the DDL information of each table according to SQL syntax rules to form a table association relationship diagram.

4. The method for improving the accuracy of Text-to-SQL conversion according to claim 3, characterized in that: In step S2, when the table association relationship diagram is stored, it is defined as S = {T1, T2, ..., Ti, E}, where Ti represents the i-th table, E represents the primary and foreign key relationship between the tables, and the database table Ti contains columns Ci = {Ci1, ..., Cij}, where Cij represents the j-th field of the i-th table.

5. The method for improving the accuracy of Text-to-SQL conversion according to claim 3, characterized in that: If the table is not created in a standardized manner and the connection information between tables is not strictly specified, manual information completion is required.

6. The method for improving the accuracy of Text-to-SQL conversion according to claim 1, characterized in that: In step S3, the reachability analysis based on the table association graph includes: Abstract the table association graph into an undirected graph: The reachability between all vertex pairs, i.e., all tables, is determined by identifying the connected components of the wireless graph.

7. The method for improving the accuracy of Text-to-SQL conversion according to claim 1, characterized in that: Step S4 includes the following sub-steps: S41, extracting table names, field names and association relationships involved in the SQL statement; S42, first perform field name verification and table name verification, and after all field names and table names are verified, perform table association verification.

8. An electronic device, characterized in that: include: at least one processor; and a memory communicatively coupled to the at least one processor; The memory stores instructions that can be executed by the at least one processor, and the at least one processor executes the method as described in any one of claims 1 to 7 by executing the instructions stored in the memory.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium is used to store instructions, and when the instructions are executed, the method according to any one of claims 1 to 7 is implemented.

10. A computer program product, characterized in that When the computer program product is called by a computer, the computer is caused to execute the method according to any one of claims 1 to 7.

Images