Method and system for converting natural language into SQL (Structured Query Language) statement
By obtaining user questions and performing entity recognition in NL2SQL technology, using the generative large language model and Faiss vectorized search tool for two-stage conversion, the problem of insufficient understanding of language understanding and complex SQL logical relationships in the existing technology is solved. , realizes high-quality SQL statement conversion, and improves NL2SQL's accuracy and business adaptability.
Patent Information
- Application Number
- CN202411405510.7
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-10-10
- Publication Date
- 2024-11-08
- Estimated Expiration
- 2044-10-10
AI Technical Summary
The existing NL2SQL technology has insufficient language understanding accuracy and complex SQL logical relationship understanding, and does not support diverse SQL, which has failed to effectively solve the problem of high-quality conversion.
By obtaining user questions and performing entity recognition, we determine the query library table for preset fields, and use the generative large language model to perform two-stage conversion on the preset JSON structure of SQL intermediate semantic representation, first converting it to SQL intermediate semantic representation, and then converting it to Convert to executable SQL statements, combined with Faiss vectorized search tool and auto-program generator for error checking and correction.
It improves the accuracy and business adaptability of NL2SQL, realizes high-quality natural language to SQL statement conversion, and enhances the structured processing capabilities of user question information.
Smart Images

Figure CN118916381A_ABST
Abstract
Description
Technical Field
[0001] The present application relates to the technical field of natural language processing, and in particular to a method and system for converting natural language into SQL statements. Background Art
[0002] NL2SQL (Natural Language to SQL) technology is a technology that converts natural language into SQL query statements. This technology has many application scenarios, such as database query, data analysis, and business intelligence, especially in the financial field. Although NL2SQL technology has great potential in improving the convenience of database query, it still faces some challenges and difficulties in actual development and application, such as the accuracy of language understanding, misunderstanding of complex SQL logical relationships, and insufficient support for diverse SQL.
[0003] Currently, no effective solution has been proposed for the problem of how to perform high-quality conversion of NL2SQL in related technologies. Summary of the invention
[0004] The embodiments of the present application provide a method and system for converting natural language to SQL statements, so as to at least solve the problem of how to perform high-quality NL2SQL conversion in the related art.
[0005] In a first aspect, an embodiment of the present application provides a method for converting a natural language into an SQL statement, the method comprising: Acquire a user question and perform entity recognition to obtain a named entity of the user question, wherein the user question is a natural language question involving a preset field; Determine a query table for the user's question from the business table of the preset field; Based on the preset JSON structure of the SQL intermediate semantic representation, the user question is converted into the SQL intermediate semantic representation through a generative large language model based on the named entities and query library tables of the user question; Based on the SQL intermediate semantic representation and the user question, the SQL intermediate semantic representation is converted into an executable SQL statement through the generative large language model.
[0006] In some embodiments, determining the query library table of the user question from the business library table of the preset field includes: The query library table of the user question is determined from the business library table of the preset field through the Faiss vectorized search tool.
[0007] In some embodiments, the query library table of the user question is determined from the business library table of the preset field by using the Faiss vectorized search tool, including: Obtain a business library table of a preset domain, and convert the information contained in the business library table based on a JSON string to obtain a library table JSON description document; Vectorize the library table JSON description document and the user question to obtain a Faiss vector; The similarity between corresponding Faiss vectors is calculated by a Faiss vectorization search tool, and a query library table of the user question is determined from the business library table of the preset field.
[0008] In some embodiments, before converting the user question into an SQL intermediate semantic representation by the generative large language model, the method includes: A preset JSON structure of an SQL intermediate semantic representation is defined, wherein the SQL intermediate semantic representation includes a basic SQL intermediate semantic representation and a complex SQL intermediate semantic representation.
[0009] In some embodiments, the preset JSON structure defining the SQL intermediate semantic representation includes: Define a preset JSON structure for basic SQL intermediate semantic representation, wherein the preset JSON structure includes tables, fields, conditions and limits, wherein the tables represent the name of the table to be queried involved in the user's question, the fields represent the fields to be queried involved in the user's question, the conditions represent the screening conditions involved in the user's question, and the limits represent the query result restrictions of the user's question; A preset JSON structure for complex SQL intermediate semantic representation is defined, wherein the preset JSON structure includes sqls and rels, the sqls represents all basic SQL intermediate semantic representations involved in the user's question, and the rels represents the relationship between the SQL intermediate semantic representations in sqls.
[0010] In some embodiments, based on the preset JSON structure of the SQL intermediate semantic representation, based on the named entities and query library tables of the user question, converting the user question into the SQL intermediate semantic representation through a generative large language model includes: Constructing a first prompt word template based on a preset JSON structure represented by SQL intermediate semantics, the named entities of the user's question, and a query library table; The first prompt word template is input into a trained generative large language model, and the user question is converted into an SQL intermediate semantic representation through the generative large language model.
[0011] In some embodiments, based on the SQL intermediate semantic representation and the user question, converting the SQL intermediate semantic representation into an executable SQL statement through the generative large language model includes: Based on the SQL intermediate semantic representation and the user question, construct a second prompt word template; The second prompt word template is input into a trained generative large language model, and the SQL intermediate semantic representation is converted into an executable SQL statement through the generative large language model.
[0012] In some embodiments, after converting the SQL intermediate semantic representation into an executable SQL statement through the generative large language model based on the SQL intermediate semantic representation and the user question, the method includes: A first proxy role and a second proxy role may be created through an automatic program generator AutoGen, wherein the first proxy role is used to perform error checking and error information output on the executable SQL statement, and the second proxy role is used to perform error correction on the executable SQL statement.
[0013] In some of the embodiments, the preset field is the field of financial futures.
[0014] In a second aspect, an embodiment of the present application provides a system for converting natural language into SQL statements, the system being used to execute the method described in any one of the first aspects above, the system comprising an acquisition module, a retrieval module and a conversion module; The acquisition module is used to acquire a user question and perform entity recognition to obtain a named entity of the user question, wherein the user question is a natural language question involving a preset field; The retrieval module is used to determine the query library table of the user question from the business library table of the preset field; The conversion module is used to convert the user question into the SQL intermediate semantic representation through a generative large language model based on the preset JSON structure of the SQL intermediate semantic representation and the named entities and query library tables of the user question; The conversion module is used to convert the SQL intermediate semantic representation into an executable SQL statement through the generative large language model according to the SQL intermediate semantic representation and the user question.
[0015] Compared with the related art, the embodiment of the present application provides a method and system for converting natural language into SQL statements, wherein the method obtains a user question and performs entity recognition to obtain a named entity of the user question, wherein the user question is a natural language question involving a preset field; a query library table of the user question is determined from a business library table of the preset field; based on a preset JSON structure of the SQL intermediate semantic representation, the user question is converted into an SQL intermediate semantic representation through a generative large language model based on the named entity and query library table of the user question; based on the SQL intermediate semantic representation and the user question, the SQL intermediate semantic representation is converted into an executable SQL statement through a generative large language model, thereby realizing two-stage SQL statement generation, firstly converting the user question into an SQL intermediate semantic representation based on a preset JSON structure, and then converting it into an executable SQL statement, which can effectively structure the information in the user question, thereby ultimately improving the accuracy and business adaptability of NL2SQL, and solving the problem of how to perform high-quality conversion of NL2SQL. BRIEF DESCRIPTION OF THE DRAWINGS
[0016] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings: Figure 1 is a flowchart of the steps of the method for converting natural language to SQL statements according to an embodiment of the present application; Figure 2 is a structural block diagram of a natural language to SQL statement system according to an embodiment of the present application; Figure 3 It is a schematic diagram of the internal structure of an electronic device according to an embodiment of the present application. DETAILED DESCRIPTION
[0017] In order to make the purpose, technical solutions and advantages of the present application clearer, the present application is described and illustrated below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. Based on the embodiments provided in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of the present application.
[0018] Obviously, the drawings described below are only some examples or embodiments of the present application. For ordinary technicians in this field, the present application can also be applied to other similar scenarios based on these drawings without creative work. In addition, it can also be understood that although the efforts made in this development process may be complicated and lengthy, for ordinary technicians in this field related to the content disclosed in this application, some changes in design, manufacturing or production based on the technical content disclosed in this application are just conventional technical means, and should not be understood as insufficient content disclosed in this application.
[0019] Reference to "embodiments" in this application means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those of ordinary skill in the art that the embodiments described in this application may be combined with other embodiments without conflict.
[0020] Unless otherwise defined, the technical terms or scientific terms involved in this application should be understood by people with ordinary skills in the technical field to which this application belongs. The words "one", "a", "a", "the" and the like involved in this application do not indicate a quantity limitation, and may indicate the singular or plural. The terms "include", "comprise", "have" and any of their variations involved in this application are intended to cover non-exclusive inclusions; for example, a process, method, system, product or device that includes a series of steps or modules (units) is not limited to the listed steps or units, but may also include steps or units that are not listed, or may also include other steps or units inherent to these processes, methods, products or devices. The words "connect", "connected", "coupled" and the like involved in this application are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. The "multiple" involved in this application refers to two or more. "And / or" describes the association relationship of associated objects, indicating that there may be three relationships, for example, "A and / or B" can mean: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the objects before and after are in an "or" relationship. The terms "first", "second", "third", etc. involved in this application are only used to distinguish similar objects and do not represent a specific ordering of the objects.
[0021] The present application embodiment provides a method for converting natural language into SQL statements. Figure 1 is a flowchart of the steps of the method for converting natural language to SQL statements according to an embodiment of the present application, such as Figure 1As shown, the method comprises the following steps: Step S102, obtaining a user question and performing entity recognition to obtain a named entity of the user question, wherein the user question is a natural language question involving a preset field; It should be noted that the preset field is preferably the financial futures field. The universal information extraction model UIE (Universal Information Extraction Model) used for question entity recognition in step S102 is fine-tuned and trained using financial futures data, so that it can automatically extract information such as entities, events and relationships in the financial futures field from the text. The named entity types in the financial futures field generally include futures subject entities (such as futures sectors, varieties, contracts, exchanges, etc.), futures indicator term entities (such as settlement price, opening price, trading volume, transaction amount, warehouse receipt quantity, planting area, operating rate, etc.), and also include basic entities such as date, country / region, value, financial institution, etc.
[0022] Furthermore, the extraction of events is similar to the above-mentioned named entity extraction, the difference is that the text of the event is longer and more inclined to the sentence level, but the internal recognition principle is exactly the same, both of which classify and recognize text strings of sequential nature. Therefore, based on UIE's powerful unified modeling capabilities and few-sample learning transfer capabilities, a small number of sample examples are annotated for various event types in the financial futures field. In order to more comprehensively cover financial futures events, examples of various events can be sorted out from the aspects of varieties, industries / sectors, the entire market, and macroeconomics. In terms of varieties, the main events are futures price increases, variety demand reductions, and variety operating rate increases and decreases. Industry / sector events mainly include collective energy price increases, tight meat supply, etc., and macro events include war conflicts, extreme weather such as floods / droughts, trade frictions, and industrial support.
[0023] Through the above method, various entities and event types in the financial futures field are comprehensively sorted out, and text examples are annotated. Through fine-tuning training with UIE's few-sample learning mechanism, the system can effectively identify various events and provide a solid foundation for NL2SQL's subsequent understanding and processing.
[0024] Step S104, determining a query table of the user's question from the business table of the preset field; Step S104 specifically includes the following steps: Step S1041, obtaining a business library table of a preset domain, and converting the information contained in the business library table based on a JSON string to obtain a library table JSON description document; It should be noted that the library table name, library table description, library table field information, etc. included in the business library are carefully sorted and converted based on the JSON string to obtain the library table JSON description document, as shown in Table 1. Table 1 "Library table name": "dw_fut_agmt" "Table Description": "Futures contract code information table, mainly storing futures contract name, contract code, trading market, contract quantity multiplier, delivery year, delivery month and other information" "Library table field information": [ ["agmt_cd", "AGMT", 'Contract code', 'such as i2405, a2407, etc.'][" agmt_nam", "AGMT", 'Contract name', 'such as copper 2405, iron ore 2408']["product_cd", "PRODUCT", 'Product code', 'cu, a, etc.'][" product_ nam","PRODUCT", 'Product code', 'Copper, soybean, etc.'][" exch_cd", "ORG_EXCH", 'Exchange code', 'such as F1, F2, etc.'][" exch_ nam", "ORG_EXCH", 'Exchange name', 'such as Guangzhou Stock Exchange, etc.'][" agmt_mul", "NUM", 'Contract quantity multiplier', 'such as 1000, etc.']["dely_year", "DATE YEAY", 'Delivery year', 'xxxx, such as 2024'][" dely_ mon", "DATE MON", 'Delivery month', '1-12'][" dely_typ ", "varchar", 'Delivery method', '']] Step S1042, vectorizing the library table JSON description document and the user question to obtain a Faiss vector; It should be noted that the library table JSON description document obtained by the above conversion is converted into a fixed-size vector. Generally, models such as Word2Vec, Bert, TF-IDF, etc. are used to achieve this. In order to express semantics, this paper uses the RoBerta-wwm-ext model, which performs well in Chinese semantic representation. It combines the advantages of the whole word masking technology Whole WordMasking (WWM) and the RoBerta model. The corpus information of financial futures is used to fine-tune the RoBerta-wwm-ext basic model for futures finance and transfer learning. In the pre-training stage, the WWM strategy is used to mask the training expectations, and the Next Sentence Prediction (NSP) task is cancelled, and the number of training steps is appropriately extended. By fine-tuning the trained model, the library table JSON description document can be effectively converted into a vector of length 768. The user's question and the entity category information identified from the question are reassembled into a new question, and the RoBerta-wwm-ext model is also used to convert it into a 768-dimensional semantic vector.
[0025] Furthermore, Faiss provides a variety of index types, including Flat (brute force search), IVF (inverted file), PQ (product quantization), etc. In view of the fact that there is a certain correspondence between entities and database table fields in user questions, some limited-range terms (such as daily and monthly lines) in user queries have a certain correspondence with database table names, and the overall database table scale is relatively limited (generally ranging from dozens to hundreds of tables), the IndexFlatL2 index structure is used to construct the Faiss index of database table information. The IndexFlatL2 index structure uses the Euclidean distance (L2 norm) as a metric to measure the similarity between vectors, which can effectively capture the semantic association information between questions and database tables.
[0026] Step S1043: The similarity between corresponding Faiss vectors is calculated by using the Faiss vectorization search tool, and a query library table of the user's question is determined from the business library table of the preset field.
[0027] It should be noted that the semantic similarity calculation is performed through the Faiss index to find the K items of data that are most similar to the query vector. Since the number of database tables involved in a general query generally does not exceed 10, K is set to 10. In this way, at most 10 database tables that are potentially related to the user's question are determined.
[0028] After determining the query tables, it is necessary to further perform deep semantic calculations based on the table JSON description documents of these query tables and the user's questions to determine whether these query tables are clearly related to the current question. The specific process is mainly to fine-tune the training of the Bert classification model based on the Transformer structure, which can effectively capture long-distance dependencies, thereby realizing the deep logical association judgment between questions and table information. A SoftMax layer is applied to the hidden layer output of the Bert model to judge the relevance of user queries and tables, and a small amount of sample data with fine annotations in the form of <question, table description, yes / no> is used for fine-tuning, which can achieve good results in the task of precise screening of tables.
[0029] Step S106, based on the preset JSON structure of the SQL intermediate semantic representation, based on the named entities and query library tables of the user question, the user question is converted into an SQL intermediate semantic representation through a generative large language model; wherein the SQL intermediate semantic representation includes a basic SQL intermediate semantic representation and a complex SQL intermediate semantic representation.
[0030] Step S106 specifically includes the following steps: Step S1061, define a preset JSON structure for basic SQL intermediate semantic representation, wherein the preset JSON structure includes tables, fields, conditions and limits, tables represent the name of the library table to be queried involved in the user's question, fields represent the fields to be queried involved in the user's question, conditions represent the screening conditions involved in the user's question, and limits represent the query result limitation of the user's question.
[0031] It should be noted that tables is in the form of array [], and the internal elements are in strings, for example: ["Table 5: futures_options_realtime", "Table 3: dwd_main_agmt_latest"], the SQL intermediate semantics library table information, in addition to the library table name, may also retain the library table description field information. In addition, in order to increase support for the subsequent complex SQL intermediate semantics representation, the internal elements of tabels[] also need to support subqueries represented by "$ SQL".
[0032] fields is in the form of an array [], with internal element strings. Each field needs to reflect the table name to support fields with duplicate names in multiple tables, for example: ["Table 5.futu_code Contract Code", "Table 5.lastprice Latest Price"]. Due to the existence of some complex elements, such as average, maximum, minimum, and quantity, AVG (field name) is used to represent the average, MAX (field name) the maximum, MIN (field name) the minimum, and COUNT (field name) is used to represent the number of records queried.
[0033] Conditions is in the form of an array []. Each condition is represented by a triple such as (field name, operator, value). The field name in the triple condition needs to come from fields. The operation symbols of the condition triple mainly include greater than >, equal to =, not equal to !=, less than <, greater than or equal to >=, less than or equal to <=, not greater than !>, not less than !<, contain, in, start, end, non-empty !null and other operations. The value of the condition triple mainly includes a specific string value, and also a reference value represented by the field filed. For example: [("Table 5.futu_code Contract Code", "Equal to =", "Copper 2305")], [("Table 5.futu_code Contract Code", "Equal to =", "$Table 6.futu_code Contract Code")]. Similar to the tables part, in order to increase the support for the complex SQL intermediate semantic representation later, the triple "value" of the internal element of conditions [] also needs to support the query of the subquery represented by "$ SQL".
[0034] limits exists in the form of an array []. Each field consists of three parts: (constraint field name, constraint type, constraint value). The constraint field name can be empty. The constraint types mainly include: LIMIT for limiting the number of entries, ascending order ASC, descending order DESC, group clustering GROUP, group filtering GROUP_HAVING, etc. The constraint value can also be empty. In addition, the constraint value also supports deterministic fixed values and SQL and field reference values. For example, "price from high to low top 10" can be expressed as ("Table 5.price price", DESC, 10).
[0035] Step S1062, defining a preset JSON structure of complex SQL intermediate semantic representation, wherein the preset JSON structure includes sqls and rels, sqls represents all basic SQL intermediate semantic representations involved in the user's question, and rels represents the relationship between the SQL intermediate semantic representations in sqls.
[0036] It should be noted that the preset JSON structure for defining complex SQL intermediate semantic representation is: {"sqls":[SQL intermediate semantic list], "rels":[($ SQL-head, $ SQL- end, relation)]} sqls represents all the basic SQL intermediate semantic representations involved in the user's question, and is stored in a list []. Each object in the list is an SQL intermediate semantic query. rels represents the relationship between the basic SQL intermediate semantic representations in sqls, and is also represented in the form of a triple, namely (SQL-head, SQL-end, relationship type). SQL-head and SQL-end refer to the specific SQL subscript positions in sqls. The relationship types are mainly divided into the following categories: WHERE_EQ scalar subquery. This type is used when the SQL-end returns a single value, and the SQL-head is used in the WHERE clause to use the SQL-end result.
[0037] FROM_SQL Row subquery. This type is used when the SQL-end returns multiple columns of a single row, while the SQL-head is usually used in the SELECT list.
[0038] WHERE_IN table subquery. This type is used when the SQL-end returns multiple rows, while the SQL-head is usually used in the FROM clause or with the IN operator.
[0039] HAVING_IN group filtering is only used in conjunction with group filtering GROUP HAVING, that is, after the aggregation query, use the nested query to further filter the group results.
[0040] Step S1063, constructing a first prompt word template based on the preset JSON structure represented by the SQL intermediate semantics, the named entities of the user's question, and the query library table; It should be noted that the entire first prompt word template contains four parts: translation function role definition (that is, clearly telling the generative large language model what needs to be done), structured basic knowledge of the financial futures industry (mainly involving the names and codes of the financial futures sectors, varieties, contracts and other types of entities), query library table field information (mainly inputting the query library table fields and other information at the user query understanding level above into the large model), the preset JSON structure of the SQL intermediate semantic representation and the translation example of the user question to the SQL intermediate semantic representation.
[0041] Step S1064: input the first prompt word template into the trained generative large language model, and convert the user question into an SQL intermediate semantic representation through the generative large language model.
[0042] It should be noted that the production-type large language model used in step S1064 is the ChatGLM model. Specifically, the ChatGLM-6B model with 6.2 billion parameters is used for fine-tuning training, so that it can convert user questions into SQL intermediate semantics with high accuracy. First, prepare a training data set of {questions, SQL intermediate semantic representation}. The questions basically cover the field queries of the main database tables, and the question forms need to involve: single table field queries, connection queries, subqueries and other forms of user queries. In addition, in order to train the large model's ability to reject recognition answers, it is also necessary to prepare a batch of rejection queries, that is, for a given database table, ask questions that are not related to this time, and prepare rejection answers such as "cannot answer". In this way, the model can deeply understand the logical relationship between questions, database table fields and SQL intermediate semantics. Secondly, set the parameters in the fine-tuning training process, such as learning rate, batch size, number of training rounds, etc., and use fine-tuning techniques such as LoRA for fine-tuning training, and finally save the checkpoint model at an iteration frequency of every 1,000 times, so that the model has high-quality translation and conversion capabilities. Furthermore, since ChatGLM is a generative model, its generated text may contain some other non-SQL semantic strings, so the output text needs to be extracted to obtain a higher-quality JSON text string with SQL intermediate semantics.
[0043] Step S108: Based on the SQL intermediate semantic representation and the user question, the SQL intermediate semantic representation is converted into an executable SQL statement through a generative large language model.
[0044] Step S108 specifically includes the following steps: Step S1081, constructing a second prompt word template based on the SQL intermediate semantic representation and the user's question; It should be noted that the conversion of executable SQL statements in step S108 and the conversion of SQL intermediate semantic representation in the above step S106 both use the ChatGLM model and are related to the query of financial futures industry database tables, but there are differences in the prompt word commands. Therefore, a second prompt word template suitable for the conversion of executable SQL statements is constructed in step S1081.
[0045] Step S1082: input the second prompt word template into the trained generative large language model, and convert the SQL intermediate semantic representation into an executable SQL statement through the generative large language model.
[0046] It should be noted that the conversion of executable SQL statements in step S1082 also requires fine-tuning and training of the ChatGLM model, and the preparation of training data in the form of <question, SQL intermediate semantics, executable SQL> triples. In order to improve the conversion ability of the model, try to cover the query library tables of financial futures, and cover representative complex nested subquery training examples such as WHERE_IN, WHERE_EQ, FROM_SQL, HAVING_IN, etc. In addition, it is also necessary to add some non-convertible training examples to allow the model to learn to reject conversion capabilities. Similarly, fine-tuning techniques such as LoRA are used for fine-tuning training, and finally the checkpoint model is saved at a frequency of every 1,000 iterations.
[0047] Through steps S102 to S108 in the embodiment of the present application, two-stage SQL statement generation is implemented. First, the user question is converted into an SQL intermediate semantic representation based on a preset JSON structure, and then converted into an executable SQL statement. The information in the user question can be effectively structured, thereby ultimately improving the accuracy and business adaptability of NL2SQL, and solving the problem of how to perform high-quality conversion of NL2SQL.
[0048] It should be noted that the steps shown in the above process or the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.
[0049] In some embodiments, after, in step S108, the SQL intermediate semantic representation is converted into an executable SQL statement by a generative large language model based on the SQL intermediate semantic representation and the user question, the method further includes: The first proxy role and the second proxy role are created by the automatic program generator AutoGen, wherein the first proxy role is used to perform error checking and error information output on the executable SQL statement, and the second proxy role is used to correct errors on the executable SQL statement.
[0050] It should be noted that since the executable SQL statement generated in step S108 may have errors in the conversion of library tables or field names, or illegal SQL syntax, etc., it is necessary to check and correct the generated SQL statement according to the library tables and field names. A multi-role SQL error checking and correction workflow based on AutoGen (The Automated Program Generator) is adopted here. Autogen provides customizable and conversational agents, making full use of the capabilities of large models (such as GPT-4, GLM, etc.), and can realize automated chat between multiple Agent agents (Agent can be regarded as a robot that can use prompt words to enable the LLM large language model to complete specific task capabilities. It is based on the specific capability output of the LLM large language model, so it becomes an agent).
[0051] In general, the SQL check and error correction can be seen as two conversational robots based on large language models automatically checking the initial executable SQL statements, and then continuously adjusting and improving the final executable SQL statements that meet the SQL syntax rules based on the question, database table, intermediate semantics and other information. Two large language model agent roles can be defined: one is the error checking agent responsible for checking whether there are illegal places in the SQL, and the other is the agent that continuously adjusts the final executable SQL statement rewriting agent based on the error checking results. The error checking agent continuously determines whether there is an error message in the current latest SQL based on information such as the database table name, database table field and SQL version. If there is an error message, the SQL rewriting robot is called to rewrite the SQL and check again. If there is no SQL syntax error, the SQL rewriting agent is not called, and the final SQL is directly returned.
[0052] The present application embodiment provides a system for converting natural language into SQL statements. Figure 2 is a structural block diagram of a natural language to SQL statement system according to an embodiment of the present application, such as Figure 2 As shown, the system includes an acquisition module, a retrieval module and a conversion module; An acquisition module is used to acquire user questions and perform entity recognition to obtain named entities of the user questions, wherein the user questions are natural language questions involving a preset field; A search module, used to determine a query table of a user's question from a business table of a preset field; A conversion module, which is used to convert the user's question into the SQL intermediate semantic representation through a generative large language model based on the preset JSON structure of the SQL intermediate semantic representation and the named entities and query library tables of the user's question; The conversion module is used to convert the SQL intermediate semantic representation into an executable SQL statement through a generative large language model according to the SQL intermediate semantic representation and the user question.
[0053] Through the acquisition module, retrieval module and conversion module in the embodiment of the present application, two-stage SQL statement generation is implemented. First, the user question is converted into an SQL intermediate semantic representation based on a preset JSON structure, and then converted into an executable SQL statement. The information in the user question can be effectively structured, thereby ultimately improving the accuracy and business adaptability of NL2SQL, and solving the problem of how to perform high-quality conversion of NL2SQL.
[0054] It should be noted that the above modules can be functional modules or program modules, and can be implemented by software or hardware. For modules implemented by hardware, the above modules can be located in the same processor; or the above modules can be located in different processors in any combination.
[0055] This embodiment further provides an electronic device, including a memory and a processor, wherein a computer program is stored in the memory, and the processor is configured to run the computer program to execute the steps in any one of the above method embodiments.
[0056] Optionally, the electronic device may further include a transmission device and an input / output device, wherein the transmission device is connected to the processor, and the input / output device is connected to the processor.
[0057] It should be noted that the specific examples in this embodiment can refer to the examples described in the above embodiments and optional implementation modes, and this embodiment will not be described in detail here.
[0058] In addition, in combination with the method of converting natural language to SQL statements in the above embodiments, the present application embodiment can provide a storage medium for implementation. The storage medium stores a computer program; when the computer program is executed by a processor, any one of the methods of converting natural language to SQL statements in the above embodiments is implemented.
[0059] In one embodiment, a computer device is provided, which may be a terminal. The computer device includes a processor, a memory, a network interface, a display screen, and an input device connected via a system bus. The processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system and a computer program. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The network interface of the computer device is used to communicate with an external terminal via a network connection. When the computer program is executed by the processor, a method for converting a natural language to an SQL statement is implemented. The display screen of the computer device may be a liquid crystal display screen or an electronic ink display screen, and the input device of the computer device may be a touch layer covering the display screen, or a key, trackball, or touchpad provided on the housing of the computer device, or an external keyboard, touchpad, or mouse, etc.
[0060] In one embodiment, Figure 3 is a schematic diagram of the internal structure of an electronic device according to an embodiment of the present application, such as Figure 3 As shown, an electronic device is provided, which may be a server, and its internal structure diagram may be as shown in Figure 3 As shown. The electronic device includes a processor, a network interface, an internal memory and a non-volatile memory connected through an internal bus, wherein the non-volatile memory stores an operating system, a computer program and a database. The processor is used to provide computing and control capabilities, the network interface is used to communicate with an external terminal through a network connection, the internal memory is used to provide an environment for the operation of the operating system and the computer program, the computer program is executed by the processor to implement a method for converting a natural language to an SQL statement, and the database is used to store data.
[0061] Those skilled in the art will understand that Figure 3 The structure shown in the figure is merely a block diagram of a partial structure related to the scheme of the present application, and does not constitute a limitation on the electronic device to which the scheme of the present application is applied. The specific electronic device may include more or fewer components than shown in the figure, or combine certain components, or have a different arrangement of components.
[0062] Those of ordinary skill in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application may include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM), etc.
[0063] Those skilled in the art should understand that the technical features of the above-described embodiments may be arbitrarily combined. To make the description concise, not all possible combinations of the technical features in the above-described embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.
[0064] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.
Claims
1. A method for converting natural language into SQL statements, characterized in that: The method comprises: Acquire a user question and perform entity recognition to obtain a named entity of the user question, wherein the user question is a natural language question involving a preset field; Determine a query table for the user's question from the business table of the preset field; Based on the preset JSON structure of the SQL intermediate semantic representation, the user question is converted into the SQL intermediate semantic representation through a generative large language model based on the named entities and query library tables of the user question; Based on the SQL intermediate semantic representation and the user question, the SQL intermediate semantic representation is converted into an executable SQL statement through the generative large language model.
2. The method according to claim 1, characterized in that Determining the query table of the user question from the business table of the preset field includes: The query library table of the user question is determined from the business library table of the preset field through the Faiss vectorized search tool.
3. The method according to claim 2, characterized in that By using the Faiss vectorized search tool, the query library table of the user question is determined from the business library table of the preset field, including: Obtain a business library table of a preset domain, and convert the information contained in the business library table based on a JSON string to obtain a library table JSON description document; Vectorize the library table JSON description document and the user question to obtain a Faiss vector; The similarity between corresponding Faiss vectors is calculated by a Faiss vectorization search tool, and a query library table of the user question is determined from the business library table of the preset field.
4. The method according to claim 1, characterized in that: Before converting the user question into an SQL intermediate semantic representation by using the generative large language model, the method includes: A preset JSON structure of an SQL intermediate semantic representation is defined, wherein the SQL intermediate semantic representation includes a basic SQL intermediate semantic representation and a complex SQL intermediate semantic representation.
5. The method according to claim 4, characterized in that The preset JSON structures that define the SQL intermediate semantic representation include: Define a preset JSON structure for basic SQL intermediate semantic representation, wherein the preset JSON structure includes tables, fields, conditions and limits, wherein the tables represent the name of the table to be queried involved in the user's question, the fields represent the fields to be queried involved in the user's question, the conditions represent the screening conditions involved in the user's question, and the limits represent the query result restrictions of the user's question; A preset JSON structure for complex SQL intermediate semantic representation is defined, wherein the preset JSON structure includes sqls and rels, the sqls represents all basic SQL intermediate semantic representations involved in the user's question, and the rels represents the relationship between the SQL intermediate semantic representations in sqls.
6. The method according to claim 1, characterized in that Based on the preset JSON structure of the SQL intermediate semantic representation, based on the named entities and query library tables of the user question, converting the user question into the SQL intermediate semantic representation through a generative large language model includes: Constructing a first prompt word template based on a preset JSON structure represented by SQL intermediate semantics, the named entities of the user's question, and a query library table; The first prompt word template is input into a trained generative large language model, and the user question is converted into an SQL intermediate semantic representation through the generative large language model.
7. The method according to claim 1, characterized in that Based on the SQL intermediate semantic representation and the user question, converting the SQL intermediate semantic representation into an executable SQL statement through the generative large language model includes: Based on the SQL intermediate semantic representation and the user question, construct a second prompt word template; The second prompt word template is input into a trained generative large language model, and the SQL intermediate semantic representation is converted into an executable SQL statement through the generative large language model.
8. The method according to claim 1, characterized in that After converting the SQL intermediate semantic representation into an executable SQL statement through the generative large language model based on the SQL intermediate semantic representation and the user question, the method includes: A first proxy role and a second proxy role may be created through an automatic program generator AutoGen, wherein the first proxy role is used to perform error checking and error information output on the executable SQL statement, and the second proxy role is used to perform error correction on the executable SQL statement.
9. The method according to any one of claims 1 to 8, characterized in that: The preset field is the financial futures field.
10. A system for converting natural language to SQL statements, characterized in that: The system is used to execute the method described in any one of claims 1 to 9, and the system includes an acquisition module, a retrieval module and a conversion module; The acquisition module is used to acquire a user question and perform entity recognition to obtain a named entity of the user question, wherein the user question is a natural language question involving a preset field; The retrieval module is used to determine the query library table of the user question from the business library table of the preset field; The conversion module is used to convert the user question into the SQL intermediate semantic representation through a generative large language model based on the preset JSON structure of the SQL intermediate semantic representation and the named entities and query library tables of the user question; The conversion module is used to convert the SQL intermediate semantic representation into an executable SQL statement through the generative large language model according to the SQL intermediate semantic representation and the user question.
Citation Information
Patent Citations
Data file analysis processing method and device based on similar SQL (Structured Query Language) and electronic equipment
CN113190573A
SQL (Structured Query Language) statement generation method and system, medium and equipment
CN117251469A
Relationship perception production data query method and device, equipment, medium and product
CN118349569A
Method for scalable, fast normalization of XML documents for insertion of data into a relational database
US20050097128A1
Cited By
Futures content generation method and system based on large language model
CN119248909A
A futures content generation method and system based on a large language model
CN119248909B
Method for converting natural language into SQL (Structured Query Language) statement
CN119884167A
Data query analysis method and system based on LLM and semantic model
CN120030045A
Visual data query method based on natural language and configured vector library
CN120162346A
