Zero training method for SQL (Structured Query Language) generation based on large language model

Abstract

The invention discloses a zero training method for SQL (Structured Query Language) generation based on a large language model. The zero training method comprises an offline part and an online part, the core technology points include the EMS standard database, data enhancement and model fine tuning, retrieval enhancement generation based on named entity recognition and SQL error correction based on an abstract syntax tree. The invention provides a T2S solution for zero training of numerous EMS industry users; according to the method, a model is finely adjusted by using data of 2-5 users in advance, customers in other numerous industries do not need fine adjustment training, namely zero training, and can deploy the T2S intelligent agent with the accuracy of 99% as long as the solution is accessed, so that the landing cost of the T2S in the EMS industry is greatly reduced, and data analysis becomes a common technology; the system and the method promote employees of more enterprises to use natural languages to directly query a database and automatically and intelligently analyze data, provide quicker, more accurate and more intelligent data analysis basis for decision making of the enterprises, reduce cost and improve efficiency, and promote development of digital economy and entity economy.

Description

Technical Field

[0001] The present invention relates to the technical field of Text To SQL generation (T2S), and in particular to a zero-training method for SQL generation based on a large language model. Background Art

[0002] Traditional SQL query code writing requires users to have knowledge of SQL syntax and be familiar with the structure of the database, which creates a threshold for ordinary users. LLM-based T2S technology greatly simplifies the interaction between users and the database by directly converting users' natural language needs into SQL query statements. LLM-based T2S usually includes several key steps: first, fine-tune the large language model based on the user's data; then, the model receives the user's natural language query (such as "check the power generation of all sites in 2022"); finally, the model generates the corresponding SQL query statement based on the user's natural language query and database DDL.

[0003] In the Energy Management System (EMS) industry, there are two key bottlenecks in using LLM to generate accurate SQL:

[0004] 1. The user's database is scattered and messy, with inconsistent standards. A database may contain thousands of tables. It is difficult for a large language model to accurately understand the relationship between thousands of tables, and the accuracy of generating correct SQL is quite low.

[0005] 2. User queries and databases contain many specific device proper nouns, which the large language model may not have seen, and user queries may contain less precise device proper nouns. The same device may have different names for different users. For example, in the user query "Yesterday's energy consumption of the temperature and humidity sensor in front of the 12_13 passenger elevator on the 2nd floor", "the temperature and humidity sensor in front of the 12_13 passenger elevator on the 2nd floor" is a proper noun. In addition, the EMS industry has some relatively special database organizational structures. The general large language model cannot understand the organizational structure and specific proper nouns in the vertical business field very well, so it is necessary to use the data of this vertical field to fine-tune the large language model. The key problem is that the naming of entities such as equipment and stations of different users is very different. For example, the name of a user's equipment is "No. 1 cooling pump", and the name of another user's equipment is "third floor LED temperature and humidity air sensor". The LLM fine-tuned with the data of a few users does not perform well on the data of the isomorphic EMS standard database of other users.

[0006] There are tens of thousands of EMS users. If we fine-tune LLM for each EMS user using their T2S data, the cost is too high to be suitable for large-scale promotion. Fine-tuning LLM requires a certain amount (not less than one thousand) of high-quality training data, which requires people who are familiar with or even proficient in business knowledge, database structure, and SQL writing. Our internal evaluation takes at least 21 days to construct the data for one user. Fine-tuning and deployment of LLM also requires considerable manpower, material resources, and time costs in AI deep learning. Therefore, we propose a zero-training method for SQL generation based on a large language model. Summary of the invention

[0007] The purpose of the present invention is to provide a zero-training method for SQL generation based on a large language model to solve the existing problems.

[0008] In order to solve the above technical problems, the present invention is achieved through the following technical solutions:

[0009] The present invention is a zero-training method for SQL generation based on a large language model, the method comprising an offline part and an online part;

[0010] The offline workflow is as follows:

[0011] Step 1: Convert the user's original database into the "EMS standard database", i.e., ETL of the database, extraction, transformation, and loading;

[0012] Step 2: Users can enter a custom entity alias in the front-end interface, such as a custom abbreviation for a certain company;

[0013] Step 3, storing the user-defined entity aliases and entities in the standard database into a dedicated entity library;

[0014] Step 4: cache and regularly update the schema (organization and structure information of the database) of the standard database;

[0015] Step 5: Put the manually annotated user queries and SQL QA (query-answer) pairs into the vector database;

[0016] Step 6: Perform data augmentation on the manually annotated QA pairs, and then fine-tune the large language model to obtain the fine-tuned T2S model;

[0017] The workflow of the online part is as follows:

[0018] Step 7: Identify keywords in the user query, i.e. named entity recognition;

[0019] Step 8: Entity retrieval: Comprehensively apply the three retrieval methods to search the identified keywords and entity database to match the most similar entity information, that is, the structural information of the entity information in the database; set a certain threshold, and if the similarity is lower than the threshold, it is considered that there is no similar entity information;

[0020] Step 9: The entity information retrieved according to the user query, the standard database schema and the data business logic of the energy management system are integrated into a dynamic context. That is, the context of different user queries is likely to be different.

[0021] Step 10: Input the context and online user query into the fine-tuned large language model to generate SQL;

[0022] Step 11, based on the retrieved entity information and SQL syntax analysis, the generated SQL is checked and corrected;

[0023] Step 12, access the standard database, execute SQL query, and return the query result;

[0024] Step 13: Apply a large language model to the query results to make a natural language summary or draw a picture.

[0025] In one of the embodiments, in step 1, the ever-changing databases of different users are extracted, converted, loaded, and reconstructed into a small number of streamlined standard database tables with the EMS industry background, namely, the "EMS standard database"; the EMS standard database is designed from scratch with a standard database organizational structure based on the actual business needs of multiple EMS users, and the table names, column names, and values ​​are normalized.

[0026] In one embodiment, in step 6, data enhancement and model fine-tuning are used to improve the basic understanding of LLM on EMS industry business knowledge and SQL queries;

[0027] Data enhancement is divided into two steps: the first step is to use LLM to perform semantic generalization and expansion on manually written questions. For one manually written question, four new questions with the same semantics but different expressions are added, and the synonymous questions generated by LLM are manually reviewed. The second step is to use code to generalize and expand precise information, such as dates and entity nouns, according to certain rules and entity information in the database.

[0028] In one of the embodiments, in the steps 7, 8, 9 and 10, firstly, LLM is used to perform named entity recognition and certain post-processing, then search and match with the entity library, and finally the search results are used to enhance the SQL generation capability of LLM, thereby greatly improving LLM's understanding of untrained knowledge, that is, zero training; the key lies in step 8, that is, searching the keywords identified in the user query and the database entity library, and comprehensively applying three text similarity retrieval methods, including character vectors, string edit distance, and semantic vectors, to obtain the most similar entity value in the "EMS standard database" and the database structure of the entity value; the results obtained from the entity retrieval have two uses. On the one hand, the context and user query are input together to the fine-tuned large language model, so that the large language model can link the query with the corresponding most relevant data information and generate accurate SQL; on the other hand, when correcting SQL errors, the retrieved entity information also needs to be used for fuzzy matching and error correction.

[0029] In one of the embodiments, in step 12, an abstract syntax tree is used to parse, check and correct the generated SQL; the SQL generated by LLM is converted into AST, so that the nodes in the SQL statement can be accurately parsed; first, some obvious syntax errors are directly corrected; then the entity information retrieved is used to compare the entity information in the SQL, and if the two are highly similar, the entity retrieved is used to replace the entity in the generated SQL, because the user's natural language query is likely to use simplified or slightly changed entity aliases, which are similar to but different from the entity nouns stored in the database. If the SQL query is to be executed correctly, the entity nouns retrieved from the database are used first.

[0030] The present invention has the following beneficial effects:

[0031] The present invention proposes a zero-training T2S solution for numerous EMS industry users; the model is fine-tuned with data from 2-5 users in advance, and many other industry customers no longer need fine-tuning training, that is, zero training. As long as they access the solution, they can deploy a T2S agent with an accuracy rate of up to 99%, thereby greatly reducing the implementation cost of T2S in the EMS industry, making data analysis a universal technology, and promoting employees of more companies to use natural language to directly query databases and automatically and intelligently analyze data, providing faster, more accurate, and more intelligent data analysis basis for corporate decision-making, reducing costs and increasing efficiency, and promoting the development of the digital economy and the real economy.

[0032] Of course, any product implementing the present invention does not necessarily need to achieve all of the advantages described above at the same time. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings required for describing the embodiments will be briefly introduced below. Obviously, the accompanying drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other accompanying drawings can be obtained based on these accompanying drawings without paying creative work.

[0034] Figure 1 A schematic diagram of the overall architecture of a zero-training method for SQL generation based on a large language model;

[0035] Figure 2 This is a schematic diagram of an actual example from user query to SQL in the present invention;

[0036] Figure 3 for Figure 2 The query result display diagram. DETAILED DESCRIPTION

[0037] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. 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.

[0038] In the description of the present invention, it should be understood that the terms "upper", "middle", "outer", "inner" and the like indicating directions or positional relationships are only for the convenience of describing the present invention and simplifying the description, rather than indicating or implying that the components or elements referred to must have a specific direction, be constructed and operated in a specific direction, and therefore should not be understood as limiting the present invention.

[0039] In the description of the present invention, it should be noted that, unless otherwise clearly specified and limited, the terms "installed", "provided with", "connected", etc. should be understood in a broad sense. For example, "connected" can be a fixed connection, a detachable connection, or an integral connection; it can be a mechanical connection or an electrical connection; it can be a direct connection, or it can be indirectly connected through an intermediate medium, or it can be the internal communication of two components. For ordinary technicians in this field, the specific meanings of the above terms in the present invention can be understood according to specific circumstances.

[0040] See also Figure 1-3 As shown, the present invention is a zero-training method for SQL generation based on a large language model, and the method includes an offline part and an online part;

[0041] The offline workflow is as follows:

[0042] Step 1: Convert the user's original database into the "EMS standard database", i.e. ETL of the database, extract, transform and load;

[0043] Step 2: Users can enter a custom entity alias in the front-end interface, such as a custom abbreviation for a certain company;

[0044] Step 3, storing the user-defined entity aliases and entities in the standard database into a dedicated entity library;

[0045] Step 4: cache and regularly update the schema (organization and structure information of the database) of the standard database;

[0046] Step 5: Put the manually annotated user queries and SQL QA (query-answer) pairs into the vector database;

[0047] Step 6: Perform data augmentation on the manually annotated QA pairs, and then fine-tune the large language model to obtain the fine-tuned T2S model;

[0048] The workflow of the online part is as follows:

[0049] Step 7: Identify keywords in the user query, i.e. named entity recognition;

[0050] Step 8: Entity retrieval: Comprehensively apply the three retrieval methods to search the identified keywords and entity database to match the most similar entity information, that is, the structural information of the entity information in the database; set a certain threshold, and if the similarity is lower than the threshold, it is considered that there is no similar entity information;

[0051] Step 9: The entity information retrieved according to the user query, the standard database schema and the data business logic of the energy management system are integrated into a dynamic context. That is, the context of different user queries is likely to be different.

[0052] Step 10: Input the context and online user query into the fine-tuned large language model to generate SQL;

[0053] Step 11, based on the retrieved entity information and SQL syntax analysis, the generated SQL is checked and corrected, such as Figure 2 ;

[0054] Step 12, access the standard database, execute SQL query, and return the query result;

[0055] Step 13: Apply the large language model to the query results to make a natural language summary or draw a picture, such as Figure 3 .

[0056] Furthermore, in step 1, the ever-changing databases of different users are extracted, converted, loaded, and reconstructed into a small number of streamlined standard database tables with EMS industry background, namely the "EMS standard database";

[0057] The EMS standard database is designed from scratch based on the actual business needs of multiple EMS users. It also standardizes table names, column names, and values. For example, thousands of data tables of a certain customer are efficiently converted into a few tables, which greatly improves the efficiency and quality of data management and provides a solid foundation for understanding and generating SQL for large language models.

[0058] Furthermore, in step 6, data enhancement and model fine-tuning are used to improve LLM’s basic understanding of EMS industry business knowledge and SQL queries;

[0059] Data enhancement is divided into two steps: the first step is to use LLM to perform semantic generalization and expansion on manually written questions. For one manually written question, four new questions with the same semantics but different expressions are added, and the synonymous questions generated by LLM are manually reviewed. The second step is to use code to generalize and expand precise information, such as dates and entity nouns, according to certain rules and entity information in the database.

[0060] Furthermore, in steps 7, 8, 9, and 10, first use LLM to perform named entity recognition and do some post-processing, then perform search matching with the entity library, and finally use the search results to enhance LLM's SQL generation ability, thereby greatly improving LLM's understanding of untrained knowledge, that is, zero training. The key lies in step 8, that is, searching the keywords identified in the user query and the database entity library. We comprehensively apply three text similarity retrieval methods, including character vectors, string edit distance, and semantic vectors. The most similar entity value in the "EMS standard database" and the database structure of the entity value are obtained. For example, the query "Yesterday's energy consumption of the No. 4 screw machine host in the large machine room" obtains the result of the search: {'asset_device':{'name':['Large machine room No. 4 screw machine host]}}, where asset_device is the table name and name is the column name. The results of entity retrieval have two uses. On the one hand, they are used as context and input into the fine-tuned large language model together with the user query, so that the large language model can connect the query with the most relevant data information and generate accurate SQL. On the other hand, when correcting SQL errors, the retrieved entity information is also needed for fuzzy matching and error correction.

[0061] Furthermore, in step 12, an Abstract Syntax Tree (AST) is used to parse, check and correct the generated SQL; the SQL generated by LLM is converted into AST, so that the nodes in the SQL statement can be accurately parsed; first, some obvious syntax errors are directly corrected; then the entity information retrieved is used to compare the entity information in the SQL. If the two are highly similar, the entity retrieved is used to replace the entity in the generated SQL, because the user's natural language query is likely to use simplified or slightly changed entity aliases, which are similar to but different from the entity nouns stored in the database. If the SQL query is to be executed correctly, the entity nouns retrieved from the database are used first.

[0062] It should be further explained that the present invention avoids fine-tuning the T2S large language model one by one for numerous customers in the Energy Management System (EMS) industry.

[0063] Experiment: Using exactly the same input and 1500 test sets, the experimental results are shown in the following table;

[0064] Table 1 shows the accuracy of different models and different solutions on the T2S test set of the EMS industry constructed by the present invention.

[0065]

[0066] Experimental results:

[0067] 1) The accuracy of SQL generated by the un-fine-tuned base LLM on the EMS industry data is only 0.02, which shows that the database of the EMS industry has its own specific organizational structure and specific terms. The un-fine-tuned base LLM cannot understand and generate most of the T2S tasks in this field.

[0068] 2) The performance of the fine-tuned LLM on the test dataset of the homogeneous “EMS Standard Database” of unknown new users in the EMS industry is quite low, with a precision of only 0.760 given the huge differences in the naming of entities such as equipment and stations among different users.

[0069] 3) The fine-tuned LLM, combined with RAG based on database named entity recognition retrieval, performed quite poorly on the test data set of the homogeneous "EMS standard database" of unknown new users in the EMS industry, with an accuracy of 0.991. That is, for unknown new users in the EMS industry, this solution can be deployed with zero training, that is, to perform the T2S task with high accuracy.

[0070] The present invention proposes a zero-training T2S solution for many EMS industry users. The model is fine-tuned with data from 2-5 users in advance, and many other industry customers no longer need fine-tuning training, that is, zero training. As long as they access the solution, they can deploy T2S agents with an accuracy rate of up to 99%, thereby greatly reducing the implementation cost of T2S in the EMS industry, making data analysis a universal technology, and promoting employees of more companies to use natural language to directly query databases and automatically analyze data intelligently, providing faster, more accurate, and more intelligent data analysis basis for corporate decision-making, reducing costs and increasing efficiency, and promoting the development of the digital economy and the real economy.

[0071] In the description of this specification, the description with reference to the terms "one embodiment", "example", "specific example", etc. means that the specific features, structures, materials or characteristics described in conjunction with the embodiment or example are included in at least one embodiment or example of the present invention. In this specification, the schematic representation of the above terms does not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials or characteristics described can be combined in any one or more embodiments or examples in a suitable manner.

[0072] The preferred embodiments of the present invention disclosed above are only used to help explain the present invention. The preferred embodiments do not describe all the details in detail, nor do they limit the invention to the specific implementation methods described. Obviously, many modifications and changes can be made according to the content of this specification. This specification selects and specifically describes these embodiments in order to better explain the principles and practical applications of the present invention, so that those skilled in the art can understand and use the present invention well. The present invention is limited only by the claims and their full scope and equivalents.

Claims

1. A zero-training method for SQL generation based on a large language model, characterized by: The method comprises an offline part and an online part; The offline workflow is as follows: Step 1: Convert the user's original database into the "EMS standard database", i.e., ETL of the database, extraction, conversion, and loading; Step 2: The user can enter a custom entity alias in the front-end interface; Step 3, storing the user-defined entity aliases and entities in the standard database into a dedicated entity library; Step 4: cache and regularly update the schema (organization and structure information of the database) of the standard database; Step 5: Put the manually annotated user queries and SQL QA (query-answer) pairs into the vector database; Step 6: Perform data augmentation on the manually annotated QA pairs, and then fine-tune the large language model to obtain a fine-tuned T2S model. The workflow of the online part is as follows: Step 7: Identify keywords in the user query, i.e. named entity recognition; Step 8: Entity retrieval: Comprehensively apply the three retrieval methods to search the identified keywords and entity database to match the most similar entity information, that is, the structural information of the entity information in the database; set a certain threshold, and if the similarity is lower than the threshold, it is considered that there is no similar entity information; Step 9: The entity information retrieved according to the user query, the standard database schema and the data business logic of the energy management system are integrated into a dynamic context. That is, the context of different user queries is likely to be different. Step 10: Input the context and online user query into the fine-tuned large language model to generate SQL; Step 11, based on the retrieved entity information and SQL syntax analysis, the generated SQL is checked and corrected; Step 12, access the standard database, execute SQL query, and return the query result; Step 13: Apply a large language model to the query results to make a natural language summary or draw a picture.

2. The zero-training method for SQL generation based on a large language model according to claim 1, characterized in that: In step 1, the ever-changing databases of different users are extracted, converted, loaded, and reconstructed into a small number of streamlined standard database tables with the EMS industry background, namely the "EMS standard database"; the EMS standard database is designed from scratch with a standard database organization structure based on the actual business needs of multiple EMS users, and the table names, column names and values ​​are standardized.

3. The zero-training method for SQL generation based on a large language model according to claim 1, characterized in that: In step 6, data enhancement and model fine-tuning are used to improve LLM's basic understanding of EMS industry business knowledge and SQL queries; Data enhancement is divided into two steps: the first step is to use LLM to perform semantic generalization and expansion on manually written questions. For one manually written question, four new questions with the same semantics but different expressions are added, and the synonymous questions generated by LLM are manually reviewed. The second step is to use code to generalize and expand precise information, such as dates and entity nouns, according to certain rules and entity information in the database.

4. The zero-training method for SQL generation based on a large language model according to claim 1, characterized in that: In step 7, step 8, step 9 and step 10, firstly, LLM is used to perform named entity recognition and certain post-processing, then search and match with the entity library, and finally the search results are used to enhance the SQL generation capability of LLM, thereby greatly improving LLM's understanding of untrained knowledge, that is, zero training; The key lies in step 8, which is to search the keywords identified in the user query and the database entity library, and comprehensively apply three text similarity retrieval methods, including character vector, string edit distance, and semantic vector, to obtain the most similar entity value in the "EMS standard database" and the database structure of the entity value; the results of entity retrieval have two uses. On the one hand, they are used as context and user queries together to input into the fine-tuned large language model, so that the large language model can link the query with the corresponding most relevant data information and generate accurate SQL; on the other hand, when correcting SQL errors, the retrieved entity information is also needed for fuzzy matching and error correction.

5. The zero-training method for SQL generation based on a large language model according to claim 1, characterized in that: In step 12, the generated SQL is parsed, checked and corrected using an abstract syntax tree; Convert the SQL generated by LLM into AST so that the nodes in the SQL statement can be accurately parsed; first directly correct some obvious syntax errors; then use the retrieved entity information to compare the entity information in the SQL. If the two are highly similar, use the retrieved entity to replace the entity in the generated SQL, because the user's natural language query is likely to use simplified or slightly changed entity aliases, which are similar to but different from the entity nouns stored in the database. If you want to execute the SQL query correctly, give priority to using the entity nouns retrieved from the database.

Images