Private field Text-to-SQL (Structured Query Language) method and system based on large language model

Abstract

The invention discloses a private field Text-to-SQL (Structured Query Language) method and system based on a large language model, and relates to a natural language processing technology, and the method comprises the following steps: obtaining an original query statement; based on the first Prompt for extracting the query statement, key semantic components in the original query statement are extracted by using the first large model, and semantic enhancement is carried out; according to a semantic enhancement result, based on a preset second Prompt for questioning and answering, utilizing a second large model to generate a first question and answer pair of the SQL; according to a semantic enhancement result, based on a preset third Prompt for questioning and answering, generating a second question and answer pair containing query local information by using the first large model; and training the third model based on the first question-answer pair and the second question-answer pair as a training set. According to the method, the advantages of the large language model in the Text-to-SQL task are efficiently transmitted to the small model applied to the private field through knowledge distillation, so that the efficient, low-cost and high-performance private field Text-to-SQL model is constructed.

Description

Technical Field

[0001] The present application relates to the field of natural language processing technology, and in particular to a private domain Text-to-SQL method and system based on a large language model. Background Art

[0002] Text-to-SQL is an important task in Natural Language Processing (NLP) that aims to convert natural language queries into SQL queries that can be executed on a database. The goal of this task is to bridge the gap between human language understanding and database queries, making interaction with databases more intuitive and efficient. By converting natural language into SQL queries, Text-to-SQL systems enable users to interact with databases without having to have expertise in the SQL query language, thus enabling easy access to data analysis and manipulation.

[0003] With the rapid development of deep learning technology, the Text-to-SQL task has transformed from early rule-based methods to current machine learning-based methods. In particular, large language models, such as Transformer and its variants, have demonstrated powerful capabilities in Text-to-SQL tasks. These models are able to process complex natural language queries and accurately convert them into corresponding SQL statements. However, these technologies still face many challenges in practical applications. For example, when processing sensitive data, it is crucial to ensure user privacy and data security. Appropriate security measures need to be taken during model training and inference to prevent data leakage. Secondly, large language models usually require a lot of computing resources for training and inference. This can lead to huge resource consumption, especially in resource-limited environments.

[0004] In the existing Text-to-SQL technology, although the methods based on large language models have made significant progress, there are still many defects and shortcomings. First, the data in the private domain is often highly professional and sensitive, which requires the model to be trained under the premise of ensuring data security, which undoubtedly increases the difficulty of data collection and labeling. Secondly, the training and deployment of large language models are expensive and require huge computing resources and storage space, which is unaffordable for enterprises or research institutions with limited resources. Furthermore, although large models perform well on general data sets, they often require fine-tuning and optimization to achieve optimal performance, which further increases the complexity of model application.

[0005] Regarding the Text-to-SQL task itself, like other natural language processing tasks, large model technology also has problems such as excessive consumption of computing resources and overly large models in Text2SQL. Secondly, the Text2SQL task has high requirements for the accuracy of SQL generation, and the SQL syntax of different databases varies significantly. Different databases often have unique SQL syntax specifications, which makes it particularly difficult to generate correct SQL. In addition, databases often have problems such as scarce corpus or involve sensitive information, so there is a lack of efficient and low-cost model training solutions for private domain databases. Although open source models usually require prompt engineering to adapt to databases in specific fields, overly redundant prompts will increase the model's computational workload, consume more resources, and even affect the performance of the model. Summary of the invention

[0006] The embodiments of the present application provide a private domain Text-to-SQL method and system based on a large language model, which efficiently transfers the advantages of the large language model in Text-to-SQL tasks to a small model of private domain applications through knowledge distillation, thereby building an efficient, low-cost, and high-performance private domain Text-to-SQL model.

[0007] The present application embodiment provides a private domain Text-to-SQL method based on a large language model, including:

[0008] Obtaining an original query statement, where the query statement is a natural language statement;

[0009] Based on the first prompt of the extracted query sentence, the key semantic components in the original query sentence are extracted using the first large model, and semantic enhancement is performed, wherein the first large model is a general large model;

[0010] According to the result of semantic enhancement, based on a second prompt preset for question and answer, a first question and answer pair of SQL is generated by using a second large model, wherein the second large model is a SQL large model; and

[0011] According to the result of semantic enhancement, based on the third prompt preset for question answering, the first large model is used to generate a second question-answer pair containing local information of the query;

[0012] Based on the first question-answer pair and the second question-answer pair as a training set, training the third largest model, wherein the third largest model is a large model adapted to the private domain;

[0013] After training, the third largest model is used to generate the required private domain SQL statements based on the input text.

[0014] Optionally, based on extracting the first prompt of the query statement, extracting key semantic components in the original query statement using the first large model includes:

[0015] A first prompt for extracting a query statement is preconfigured, wherein the first prompt includes instruction information indicating that the first large model returns a format of key semantic components.

[0016] Optional semantic enhancements include:

[0017] Performing one or more of the following operations on the extracted key semantic components: synonym replacement, phrase expansion, and sentence transformation;

[0018] generating an enhanced query statement semantically related to the original query after the operation; and,

[0019] Generates contextual query statements that are unrelated to the original query semantics.

[0020] Optionally, based on the second prompt preset for question and answer, the second largest model is used to generate SQL question and answer data pairs including:

[0021] Pre-configure the second prompt;

[0022] The second prompt and the context-dependent query according to the enhanced query statement are input into the second large model to generate a SQL question-answer data pair.

[0023] Optionally, according to the result of semantic enhancement, based on the third prompt preset for question and answer, using the first large model to generate a second question and answer pair containing local query information includes:

[0024] Pre-configured third prompt for question and answer;

[0025] The third prompt and the context-independent query according to the context query statement are input into the first large model to generate a second question and answer containing local query information.

[0026] Optionally, based on the enhanced query statements and the generated SQL question-answer data pairs as a training set, the third largest model is trained including:

[0027] Using the training set, supervised fine-tuning training is performed on the third largest model, where the goal of supervised fine-tuning is to minimize the following empirical loss:

[0028]

[0029] Among them, L is the loss function used to quantify the difference between generated queries and real queries, and M^* is the trained lightweight model.

[0030] An embodiment of the present application also proposes a private domain Text-to-SQL system based on a large language model, including a processor and a memory, wherein a computer program is stored on the memory, and when the computer program is executed by the processor, the steps of the private domain Text-to-SQL method based on a large language model as described above are implemented.

[0031] The embodiments of the present application efficiently transfer the advantages of a large language model in Text-to-SQL tasks to a small model for private domain applications through knowledge distillation, thereby building an efficient, low-cost, and high-performance private domain Text-to-SQL model.

[0032] The above description is only an overview of the technical solution of the present application. In order to more clearly understand the technical means of the present application, it can be implemented in accordance with the contents of the specification. In order to make the above and other purposes, features and advantages of the present application more obvious and easy to understand, the specific implementation methods of the present application are listed below. BRIEF DESCRIPTION OF THE DRAWINGS

[0033] Various other advantages and benefits will become apparent to those of ordinary skill in the art by reading the detailed description of the preferred embodiments below. The accompanying drawings are only for the purpose of illustrating the preferred embodiments and are not to be considered as limiting the present application. Also, the same reference symbols are used throughout the accompanying drawings to represent the same components. In the accompanying drawings:

[0034] Figure 1 The basic process diagram of the private domain Text-to-SQL method based on the large language model in the embodiment of the present application is shown;

[0035] Figure 2 The overall architecture of the private domain Text-to-SQL method based on a large language model according to an embodiment of the present application is shown;

[0036] Figure 3 The following is a schematic diagram of the context semantics-related enhancement process of the private domain Text-to-SQL method based on a large language model according to an embodiment of the present application;

[0037] Figure 4 The following is a schematic diagram of the context-independent enhancement process of the private domain Text-to-SQL method based on a large language model according to an embodiment of the present application;

[0038] Figure 5 This is a schematic diagram of the SQL big model knowledge distillation process of the private domain Text-to-SQL method based on a big language model in an embodiment of the present application;

[0039] Figure 6 The general large model knowledge distillation process of the private domain Text-to-SQL method based on a large language model in an embodiment of the present application is schematically illustrated. DETAILED DESCRIPTION

[0040] The exemplary embodiments of the present disclosure will be described in more detail below with reference to the accompanying drawings. Although the exemplary embodiments of the present disclosure are shown in the accompanying drawings, it should be understood that the present disclosure can be implemented in various forms and should not be limited by the embodiments set forth herein. On the contrary, these embodiments are provided to enable a more thorough understanding of the present disclosure and to fully convey the scope of the present disclosure to those skilled in the art.

[0041] The present application embodiment provides a private domain Text-to-SQL method based on a large language model, such as Figure 1 , Figure 2 As shown, the following steps are included:

[0042] In step S101, an original query statement is obtained, where the query statement is a natural language statement.

[0043] In step S102, based on the first prompt of the extracted query, the first large model is used to extract the key semantic components in the original query, and semantic enhancement is performed, such as Figure 2 As shown, the first large model is a general large model. In some embodiments, this step can be enhanced based on semantic components. In the embodiments of the present application, the semantic component enhancement aims to improve the understanding ability of the subsequent model to the query statement by extracting and enhancing the semantic components (semantic components) in the original query statement.

[0044] In the specific example, the semantic component enhancement mainly enhances the key semantic components from two dimensions. In this application, the key semantic components are specifically subjected to context-related synonym replacement, phrase expansion, and sentence transformation to generate context-dependent queries related to the original query semantics for SQL large model distillation. Each semantic component is further enhanced in a context-independent manner to generate a context-dependent query that is independent of the original query semantics for general large model distillation.

[0045] In step S103, according to the result of semantic enhancement, based on the second prompt preset for question and answer, the second large model is used to generate the first question and answer pair of SQL, wherein the second large model is a SQL large model. Figure 2As shown, the first large model is used to extract the key semantic components in the original query statement and perform semantic enhancement. In the specific example, the result of the semantic enhancement includes an enhanced query statement context-dependent query related to the original query semantics and a query statement context-independent query that is unrelated to the context semantics. The generated context-dependent query is used for distillation of the SQL large model to generate [context-dependent query, SQL]. Based on the second prompt preset for question and answer, the second large model is used to generate SQL question and answer data pairs. Figure 2 As shown, the second large model is a SQL large model.

[0046] In step S104, according to the result of semantic enhancement, based on the third prompt preset for question and answer, a second question and answer pair containing local information of the query is generated using the first large model. Figure 2 As shown, in parallel with the second largest model, the generated context-independent query will be used for the general large model distillation to generate [context-independent query, answer].

[0047] In step S105, based on the first question-answer pair and the second question-answer pair as training sets, the third largest model is trained, wherein the third largest model is a large model adapted to the private domain. In a specific example, after the third largest model is fine-tuned and trained according to the enhanced query statements and the generated SQL question-answer data pairs, a large model with high-quality data training parameters of moderate scale and high efficiency is obtained.

[0048] In step S106, after training, the third largest model is used to generate the required private domain SQL statements according to the input text.

[0049] The method of this application not only ensures the efficiency of the model when executing private domain Text-to-SQL tasks, but also significantly reduces the cost of computing and deployment, and provides a highly cost-effective solution for natural language to structured query language conversion tasks in specific scenarios.

[0050] In some embodiments, extracting the first prompt of the query statement and extracting the key semantic components in the original query statement using the first large model includes:

[0051] A first prompt for extracting a query statement is preconfigured, wherein the first prompt contains instruction information indicating that the first large model returns a format of key semantic components. In a specific example, the process of extracting key semantic components from the original query statement is as follows:

[0052] The original query statement is input, and then the Prompt project of the first model is used to extract the key semantic components in the query statement. In some specific examples of this application, the following Prompt is designed to extract the key semantic components in the query statement. The Prompt is as follows:

[0053] Please extract key semantic components from the following query statement, including entities, attributes, and operations. Please return the results in the following format:

[0054] Entity:[Entity1,Entity2,...]

[0055] Attributes: [attribute1, attribute2, ...]

[0056] Operation: [Operation 1, Operation 2, ...]

[0057] Example: Find the names and departments of all employees who joined in 2025.

[0058] Semantic component results:

[0059] Entity: [employee]

[0060] Attributes: [year of birth, name, department]

[0061] Operation: [Find out that the year of employment is 2025]

[0062] Finally, the first large model is used to output the extracted key semantic components.

[0063] Compared with traditional named entity recognition algorithms, the method of the present application can not only extract entity components in the query, but also significantly improve the accuracy when identifying sensitive information interfered by noise.

[0064] In some embodiments, performing semantic enhancement includes: context semantically relevant enhancement and context semantically irrelevant enhancement.

[0065] Contextual semantic enhancements, such as Figure 3 As shown, it includes: performing one or more of the following operations on the extracted key semantic components: synonym replacement, phrase expansion and sentence transformation;

[0066] After the operation, enhanced query statements related to the original query semantics are generated, and the generated semantically relevant query statements are used for knowledge distillation of subsequent SQL large models.

[0067] Context-independent enhancements include, for example, Figure 4 As shown in the figure, a query that is independent of the original query semantics is generated, each key semantic information is explained, and the generated context query statement that is independent of the context semantics is used for the subsequent knowledge distillation of the general large model.

[0068] Further embodiments of the present application introduce knowledge distillation of SQL big model and general big model, context-dependent query generated by semantic component enhancement is used for distillation of SQL big model to generate [context-dependent query, SQL]; context-independent query generated by semantic component enhancement will be used for distillation of general big model to generate [context-independent query, answer], and the data pairs generated finally are used for subsequent low-resource supervised fine-tuning. In some embodiments, such as Figure 5 As shown, based on the second prompt preset for question and answer, the second largest model is used to generate SQL question and answer data pairs including:

[0069] The second prompt is pre-configured, and in a specific example, a prompt with stronger robustness may be designed.

[0070] The second prompt and the context-dependent query according to the enhanced query statement are input into the second large model to generate SQL question-answer data pairs. In a specific example, the designed robust prompt is combined with the context-dependent query and sent into the selected SQL large model to generate accurate and executable SQL.

[0071] The SQL big model needs to have a large parameter scale and SQL generation capabilities, and focuses on converting the complete context content into executable SQL statements. When selecting a SQL big model, for example, it can be a code big model such as SQLCoder, StarCoder, Codestral, etc. with excellent performance in the field of SQL generation. When selecting a SQL big model, it can be evaluated and selected according to the actual business scenario, which is specifically limited in this application.

[0072] The large model is used for distillation: the model can handle more complex and subtle semantic information, and is used to distill the general large model's ability to understand informal and colloquial language, providing SFT data pairs containing query global information for the subsequent SFT process. In some specific embodiments, such as Figure 6 As shown, according to the result of semantic enhancement, based on the third prompt preset for question answering, the second question-answer pair containing the query local information is generated by using the first large model, including:

[0073] Pre-configure the third prompt for question and answer;

[0074] The third prompt and the context-independent query according to the context query statement are input into the first large model to generate a second question and answer pair containing local information of the query. Specifically, the third prompt for question and answer is combined with the context-independent query and sent into the selected general large model to generate a question and answer pair containing local information of the query.

[0075] The main advantage of the general large model is the depth and breadth of natural language understanding, and it also has a large parameter scale. When selecting a general large model, for example, it can be a large model such as Qwen and Mixtral that have demonstrated significant versatility and efficiency in multiple fields. When selecting a suitable general large model, it can be determined based on the actual evaluation results, taking into account the performance, applicability and efficiency of the model, and this application does not make specific restrictions.

[0076] After the distillation of the SQL large model and the distillation of the general large model, the embodiment of the present application further integrates the high-quality data obtained by collaborative distillation to train a large model with moderate parameter scale and high efficiency. In this way, not only the efficiency of the model in performing private domain Text-to-SQL tasks is guaranteed, but also the cost of calculation and deployment is significantly reduced, providing a solution with extremely high performance-price ratio for the conversion task from natural language to structured query language in specific scenarios. In a specific example, data that conforms to real business scenarios and has privacy protection is obtained according to this method, and a lightweight model is trained with these data to verify the effectiveness of the method of this application. In the embodiment of the present application, Qwen2-1.5B is selected as the basic model M, and the training data set T generated by the knowledge distillation method, where T = {q_i, s_i, D_i}, q_i represents a natural language problem, and s_i is the corresponding SQL query on the database D_i. The goal of supervised fine-tuning is to minimize the following empirical loss, where L is a loss function used to quantify the difference between generated queries and real queries, and M^* is a trained lightweight model. In some embodiments, using the enhanced query statements and the generated SQL question-answer data pairs as a training set to perform training on the third model includes:

[0077] Using the training set, supervised fine-tuning training is performed on the third largest model, where the goal of supervised fine-tuning is to minimize the following empirical loss:

[0078]

[0079] Among them, L is the loss function used to quantify the difference between generated queries and real queries, and M^* is the trained lightweight model.

[0080] In the specific example, the application example of this application constructs a Text-to-SQL Test set that conforms to the real scenario, aiming to comprehensively evaluate and verify the effectiveness and practical application value of the method proposed in this application in the Text-to-SQL task.

[0081] This test set relies on the table structure information of the TPC-H standard database to ensure the authenticity and complexity of the data. This example also designs and generates a series of SQL query statements based on the common syntax specifications and habits in the actual SQL generation process. The generated queries cover a wide range of colloquial expressions and also include SQL syntax formats in private domains to simulate the diversity and challenges in a real development environment.

[0082] Here are some examples of typical scenarios in the test set:

[0083] Supports multi-source heterogeneous data query. The data source must be included before the table name, for example: mysql.Table or dfs.`Table.csv`.

[0084] Date functions with specific syntax. For example, the current date is represented as CURRENT_DATE

[0085] Specific data types and formats. For example: UNIX_TIMESTAMP('2024-04-01 10:00:00','yyyy-MM-dd HH:mm:ss') converts a date string to a timestamp.

[0086] Search for colloquial and proper nouns

[0087] According to the method proposed in this application, 50,000 Text-to-SQL data and general data were distilled. The supervised fine-tuning experiment was completed on a single A100. In all fine-tuning experiments, this example uses a unified dataset, SequenceLength is set to 2048, the micro-batch size is 8, and the global batch size is 128. The Adam optimizer was selected for the optimization process, in which the initial learning rate was set to 2e-4, and the minimum learning rate was adjusted to 1e-5.

[0088] During the fine-tuning phase, we used Qlora, an efficient fine-tuning model strategy, and ensured that the fine-tuning parameter ratio was always maintained at 2.52%. The entire fine-tuning process went through three cycles of iterative training to ensure full optimization of the model performance.

[0089] Example Results

[0090] In this section, in order to verify whether the capabilities of the SQL large model and the general large model are distilled into a lightweight small model, this example selects Qwen2-72B as the general large model, Codestral-22B as the SQL large model, and Qwen2-1.5B as the lightweight target model. As shown in the following table,

[0091]

[0092]

[0093] This example uses the general big model and the SQL big model as the baseline and verifies them in a test set of a text-to-SQL scenario in actual application. The test set meets the text-to-SQL tasks in real application scenarios.

[0094] As shown in the table above, among the baseline models, Baseline-Codestral-22B performs best with an accuracy of 80.4%, which is attributed to its excellent code generation ability. In contrast, the Baseline-Qwen2-1.5B model, which has not been fine-tuned by the method of this application, has an accuracy of only 42.4%, which is poor.

[0095] The KD-Qwen2-1.5B model trained using the method proposed in this application has an accuracy rate of 93.7%, which not only meets the industry's needs for low cost, high accuracy, and private deployment, but also significantly improves its performance by 51.3% compared to the Baseline-Qwen2-1.5B model. This significant improvement fully demonstrates the effectiveness of the large-model knowledge distillation method proposed in this application in dealing with private domain Text-to-SQL tasks.

[0096] The embodiment of the present application uses knowledge distillation to efficiently transfer the advantages of large language models in Text-to-SQL tasks to small models for private domain applications, thereby building an efficient, low-cost, and high-performance private domain Text-to-SQL model. This not only helps to promote the further development of Text-to-SQL technology, but also provides new ideas and methods for the application of large language models in more practical scenarios.

[0097] An embodiment of the present application also proposes a private domain Text-to-SQL system based on a large language model, including a processor and a memory, wherein a computer program is stored on the memory, and when the computer program is executed by the processor, the steps of the private domain Text-to-SQL method based on a large language model as described above are implemented.

[0098] It should be noted that in the various embodiments of the present application, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also includes other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, an element defined by the sentence "includes a ..." does not exclude the presence of other identical elements in the process, method, article or device including the element.

[0099] The serial numbers of the above-mentioned embodiments of the present application are for description only and do not represent the advantages or disadvantages of the embodiments.

[0100] Through the description of the above implementation methods, those skilled in the art can clearly understand that the above-mentioned embodiment methods can be implemented by means of software plus a necessary general hardware platform, and of course by hardware, but in many cases the former is a better implementation method. Based on such an understanding, the technical solution of the present application, or the part that contributes to the prior art, can be embodied in the form of a software product, which is stored in a storage medium (such as ROM / RAM, a magnetic disk, or an optical disk), and includes a number of instructions for a terminal (which can be a mobile phone, a computer, a server, an air conditioner, or a network device, etc.) to execute the methods described in each embodiment of the present application.

[0101] The embodiments of the present application are described above in conjunction with the accompanying drawings, but the present application is not limited to the above-mentioned specific implementation methods. The above-mentioned specific implementation methods are merely illustrative and not restrictive. Under the guidance of the present application, ordinary technicians in this field can also make many forms without departing from the purpose of the present application and the scope of protection of the claims, all of which are within the protection of the present application.

Claims

1. A private domain Text-to-SQL method based on a large language model, characterized in that: include: Obtaining an original query statement, where the query statement is a natural language statement; Based on the first prompt of the extracted query sentence, the key semantic components in the original query sentence are extracted using the first large model, and semantic enhancement is performed, wherein the first large model is a general large model; According to the result of semantic enhancement, based on a second prompt preset for question and answer, a first question and answer pair of SQL is generated by using a second large model, wherein the second large model is a large SQL model; as well as According to the result of semantic enhancement, based on the third prompt preset for question answering, the first large model is used to generate a second question-answer pair containing local information of the query; Based on the first question-answer pair and the second question-answer pair as a training set, training the third largest model, wherein the third largest model is a large model adapted to the private domain; After training, the third largest model is used to generate the required private domain SQL statements based on the input text.

2. The private domain Text-to-SQL method based on a large language model as claimed in claim 1, characterized in that: Based on the first prompt of the extracted query, the key semantic components in the original query are extracted using the first large model, including: A first prompt for extracting a query statement is preconfigured, wherein the first prompt includes instruction information indicating that the first large model returns a format of key semantic components.

3. The private domain Text-to-SQL method based on a large language model as claimed in claim 2, characterized in that: Semantic enhancement includes: Performing one or more of the following operations on the extracted key semantic components: synonym replacement, phrase expansion, and sentence transformation; generating an enhanced query statement semantically related to the original query after the operation; and, Generates contextual query statements that are unrelated to the original query semantics.

4. The private domain Text-to-SQL method based on a large language model as claimed in claim 3, characterized in that: Based on the second prompt preset for question and answer, the second largest model is used to generate SQL question and answer data pairs including: Pre-configure the second prompt; The second prompt and the context-dependent query according to the enhanced query statement are input into the second large model to generate a SQL question-answer data pair.

5. The private domain Text-to-SQL method based on a large language model as claimed in claim 4, characterized in that: According to the result of semantic enhancement, based on the third prompt preset for question answering, the second question-answer pair containing the local information of the query is generated by using the first large model, including: Pre-configured third prompt for question and answer; The third prompt and the context-independent query according to the context query statement are input into the first large model to generate a second question and answer containing local query information.

6. The private domain Text-to-SQL method based on a large language model as claimed in claim 5, characterized in that: Based on the enhanced query statements and the generated SQL question-answer data pairs as training sets, the third largest model is trained including: Using the training set, supervised fine-tuning training is performed on the third largest model, where the goal of supervised fine-tuning is to minimize the following empirical loss: Among them, L is the loss function used to quantify the difference between generated queries and real queries, and M^* is the trained lightweight model.

7. A private domain Text-to-SQL system based on a large language model, characterized in that: The method comprises a processor and a memory, wherein a computer program is stored in the memory, and when the computer program is executed by the processor, the steps of the private domain Text-to-SQL method based on a large language model as described in any one of claims 1 to 6 are implemented.

Images