Large nl2SQL model training method based on error correction and preference optimization

Abstract

The present application relates to the technical field of natural language processing, and provides a large NL2SQL model training method based on error correction and preference optimization. The method comprises: inputting a data set adapted to a database into an open-source large model, verifying generated data, and collecting erroneous data that fails verification (S1); correcting the erroneous data by means of a closed-source large model to obtain corrected data (S2); associating the erroneous data and the corrected data with query requirements to form training samples (S3); and using a direct preference optimization strategy to perform supervised fine-tuning on the open-source large model by using the training samples (S4).

Description

A training method for large NL2SQL models based on error correction and preference optimization

[0001] Cross-references to related applications

[0002] This application claims priority to Chinese Patent Application No. 202510305859.1, filed on March 14, 2025, entitled “A Training Method for Large NL2SQL Models Based on Error Correction and Preference Optimization”, the entire contents of which are incorporated herein by reference. Technical Field

[0003] This application belongs to the field of natural language processing technology, specifically relating to a training method for a large NL2SQL model based on error correction and preference optimization. Background Technology

[0004] Natural Language to SQL (NL2SQL) technology is an important research direction in the fields of database querying and business intelligence (BI), aiming to lower the data analysis threshold for non-technical users through natural language interaction. In recent years, with the rapid development of Large Language Models (LLMs), closed-source models (such as GPT-4 (Generative Pre-trained Transformer 4)) have demonstrated high accuracy in NL2SQL tasks, but their application is limited by insufficient openness, privacy risks, and high costs. In contrast, open-source large models (such as qwen-14b) offer advantages in transparency and cost, but they have significant drawbacks in generating SQL (Structured Query Language), such as high error rates, weak instruction understanding capabilities, and poor adaptability to specific database syntax.

[0005] Current technical solutions primarily optimize the NL2SQL task in the following ways: Relying on high-performance closed-source models to generate SQL, but this requires calls through third-party APIs (Application Programming Interfaces), posing data security risks and making customization difficult. Fine-tuning open-source models with domain-specific data, but this is limited by insufficient high-quality labeled data and high computational resource requirements. Combining closed-source and open-source models to generate data, but lacking a systematic error correction mechanism, leads to unstable training data quality.

[0006] Open-source models often generate SQL containing syntactic or semantic errors, requiring manual correction and resulting in inefficiency. Models struggle to accurately parse complex queries, leading to generated SQL that deviates from user intent. General training data does not cover the syntactic characteristics of different databases (such as PostgreSQL and MySQL), resulting in low compatibility of generated results. Furthermore, existing methods rely on manually labeled or unoptimized synthetic data, making them difficult to scale and exhibiting weak generalization ability. Summary of the Invention

[0007] This application provides a training method for large NL2SQL models based on error correction and preference optimization to address the technical problems of high SQL error rate, weak instruction following ability, and difficulty in adapting to specific database syntax rules when open-source large language models perform NL2SQL tasks.

[0008] The technical solution adopted in this application is as follows:

[0009] This application provides a method for training a large NL2SQL model based on error correction and preference optimization, including:

[0010] The dataset adapted to the database is input into the open-source large model, and the generated data is validated. Error data that fails to be validated is collected.

[0011] The erroneous data is corrected by using a closed-source large model to obtain corrected data;

[0012] Associate erroneous and corrected data with query requirements to form training samples;

[0013] We employ a direct preference optimization strategy and use training samples to perform supervised fine-tuning on a large open-source model.

[0014] According to some embodiments of this application, after employing a direct preference optimization strategy and using training samples to perform supervised fine-tuning on a large open-source model, the method further includes:

[0015] The fine-tuned open-source model was evaluated, specifically as follows:

[0016] Set evaluation metrics, including accuracy, F1 score, query execution success rate, and efficiency;

[0017] Use an independent test set to evaluate the model's generalization ability;

[0018] Test in real-world user environments, perform error analysis and collect user feedback to identify the shortcomings of the fine-tuned open-source model in specific scenarios or SQL types.

[0019] Develop an iterative improvement plan based on the evaluation results, including expanding the training dataset, adjusting the algorithm, or making targeted fine-tuning.

[0020] According to some embodiments of this application, the specific method for obtaining the dataset of the adaptive database is as follows:

[0021] Extract data from the selected base data source dataset;

[0022] The extracted content is translated into Chinese using a closed-source large language model, while keeping the English SQL keywords, the structure, format, values, and dates of the SQL statements unchanged.

[0023] Using a closed-source large language model, the translated table creation SQL is corrected into SQL database format, and query SQL that conforms to SQL syntax is generated;

[0024] Perform consistency checks, SQL validity verification, and SQL compatibility testing;

[0025] The processed dataset is divided into training and test sets according to the original proportions, while maintaining the original complexity distribution.

[0026] According to some embodiments of this application, the generated data is verified, and erroneous data that fails verification is collected, specifically as follows:

[0027] Generate SQL queries using open-source large models. Inputs include: natural language questions, table structure information, and target database type.

[0028] The generated SQL statement is validated using the SQL executor.

[0029] For SQL statements that fail to execute, record their unique identifier and specific error information.

[0030] According to some embodiments of this application, erroneous data is corrected using a closed-source large model to obtain corrected data, specifically as follows:

[0031] Select and configure a closed-source large model;

[0032] For each erroneous SQL sample, construct input information containing the following elements: the original natural language query, the erroneous SQL statement, the error message, the relevant database structure information, and the explicit instructions;

[0033] The input information is provided as context to the closed-source model to extract the corrected SQL statement;

[0034] Use PostgreSQL's parser for syntax checking;

[0035] A sample of randomly selected correction results was reviewed, and complex or critical cases were reviewed by experts.

[0036] The original erroneous SQL, the corrected SQL, and related metadata are stored in a dedicated database.

[0037] According to some embodiments of this application, error data and correction data are associated with query requirements to form training samples, specifically:

[0038] Extract the original erroneous SQL and the corresponding corrective SQL from the database;

[0039] Each pair of SQL statements is associated with the original natural language query to form a complete training sample;

[0040] Remove duplicate samples, convert all samples to a consistent JSON format, and add metadata tags to each sample;

[0041] The constructed dataset is divided into training set, validation set and test set in a ratio of 8:1:1.

[0042] According to some embodiments of this application, a direct preference optimization strategy is adopted to perform supervised fine-tuning of a large open-source model using training samples, specifically as follows:

[0043] For each training sample, the correct SQL is selected as the preferred choice, and the incorrect SQL is selected as the non-preferred choice.

[0044] Construct model inputs, including instructions, natural language queries, and database structure information;

[0045] The generation probabilities of preferred and unpreferred choices are calculated using the target model and the reference model, respectively.

[0046] The loss is calculated based on the objective function optimized by direct preferences, and the model parameters are updated through backpropagation.

[0047] A computer program product containing instructions that, when run on a device, causes the device to execute steps in implementing a large NL2SQL model training method based on error correction and preference optimization.

[0048] A non-volatile readable storage medium storing a program that, when executed by a processor, implements steps in a large NL2SQL model training method based on error correction and preference optimization.

[0049] An electronic device includes a memory, a processor, and a program stored in the memory and executable on the processor, wherein the processor executes the program to implement steps in a large NL2SQL model training method based on error correction and preference optimization.

[0050] Due to the adoption of the above technical solution, the beneficial effects achieved by this application are as follows:

[0051] This application systematically collects erroneous SQL samples by inputting a dataset adapted to the database into an open-source large model and verifying the generated results. This allows for the precise identification of defects in the model's syntax, semantics, and database adaptability, providing high-quality negative samples for subsequent corrections.

[0052] This application utilizes a high-performance closed-source large model (such as GLM-4-0520) to correct erroneous SQL statements, and combines this with a low temperature value configuration (such as 0.3) to ensure that the corrected SQL statements strictly conform to the target database syntax rules, significantly improving data accuracy and consistency.

[0053] This application strongly correlates erroneous SQL, corrected SQL, and the original natural language query to form a "input-error output-correct output" triple training sample, enabling the model to learn the mapping relationship from natural language to SQL and enhance its ability to understand and execute instructions.

[0054] This application employs the DPO (Direct Preference Optimization) strategy to perform supervised fine-tuning of the open-source model. By comparing the generation probabilities of positive and negative samples, the model preferences are directly optimized, reducing the probability of generating erroneous SQL queries and improving the adaptability to complex queries and database characteristics.

[0055] This technical solution addresses the core shortcomings of open-source large models in NL2SQL tasks through a closed-loop data generation-correction-training mechanism. The combination of error correction and DPO fine-tuning improves SQL generation accuracy by over 30% (based on actual test data). The design of training samples for related natural language queries increases the model's success rate in parsing complex commands by 25%. Customized training data for different database syntax characteristics ensures SQL compatibility with mainstream databases (such as PostgreSQL and MySQL). Automated correction and preference optimization strategies reduce manual intervention by 80% and lower training costs by 40%. Attached Figure Description

[0056] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0057] Figure 1 is a flowchart illustrating a method for training a large NL2SQL model based on error correction and preference optimization provided in an embodiment of this application. Detailed Implementation

[0058] To more clearly illustrate the overall concept of this application, a detailed explanation is provided below with reference to the accompanying drawings.

[0059] Many specific details are set forth in the following description to provide a thorough understanding of this application. However, this application may also be implemented in other ways different from those described herein. Therefore, the scope of protection of this application is not limited to the specific embodiments disclosed below. It should be noted that, unless otherwise specified, the embodiments of this application and the features thereof can be combined with each other.

[0060] In this application, unless otherwise expressly specified and limited, the "above" or "below" of the second feature can mean that the first and second features are in direct contact, or that the first and second features are in indirect contact through an intermediate medium. In the description of this specification, references to terms such as "an embodiment," "some embodiments," "example," "specific example," or "some examples," etc., indicate that a specific feature, structure, material, or characteristic described in connection with that embodiment or example is included in at least one embodiment or example of this application. In this specification, the illustrative expressions of the above terms do not necessarily refer to the same embodiment or example. Furthermore, the specific features, structures, materials, or characteristics described can be combined in any suitable manner in one or more embodiments or examples.

[0061] As shown in Figure 1, a method for training a large NL2SQL model based on error correction and preference optimization includes:

[0062] The dataset adapted to the database is input into the open-source large model, and the generated data is validated. Error data that fails to be validated is collected.

[0063] Specifically, firstly, database-adapted datasets refer to datasets that have been processed to conform to the syntax rules of a specific database (such as Oracle, MySQL, PostgreSQL, etc.). These datasets contain table structure information, natural language queries, and corresponding SQL statements.

[0064] Then, this adapted data is input into the open-source large model, with the aim of enabling the model to generate corresponding SQL queries. The input information in this process includes, but is not limited to: natural language queries (i.e., what the user wants to query), table structure information (such as table names, column names, data types, and constraints), and the target database type (explicitly specifying which database's SQL statements to generate, such as PostgreSQL).

[0065] The next step is to validate the generated SQL statements. This step is accomplished by designing an SQL executor that can execute the generated SQL statements in an isolated environment and record the execution results. Regardless of whether the SQL execution is successful, its results are recorded in detail. Specifically, for SQL statements that fail to execute, the system records their unique identifiers and specific error messages. This is done to collect SQL statements that failed to execute due to syntax or logical errors as a basis for subsequent improvements.

[0066] Finally, collecting the error data from the validation failures means filtering out all SQL queries that failed to execute correctly and their related information (such as unique identifiers and error details) from the above process, and then organizing and archiving this error data. This error data will be used in subsequent steps to correct and optimize the model, using a closed-source large model to correct it, thereby forming high-quality training samples and further improving the accuracy and reliability of the open-source large model in generating SQL queries.

[0067] For example, suppose we are using a dataset that is already adapted for PostgreSQL. This dataset contains natural language queries, table structure information (such as table names, column names, data types, etc.), and the target database type (PostgreSQL). For example, let's extract a record from this dataset:

[0068] Natural Language Question: "Please list the names and start dates of all employees who are over 30 years old."

[0069] Table structure information: It contains a table named employees (one of the tables in the database, used to store basic information and work-related data of employees within the organization), which has fields such as name, age, and hire_date.

[0070] Target database type: PostgreSQL

[0071] Model generates SQL statements

[0072] The above information is provided as input to a large open-source model (such as qwen-14b), which then generates the corresponding SQL query statement based on this information. Assume the model generates the following SQL statement:

[0073] SELECT name,hire_date FROM employees WHERE age>30;

[0074] SQL executor verification

[0075] Design an SQL executor to execute this SQL statement in an isolated environment. In this example, if the SQL statement is correct, it will return a successful result; however, if an error occurs, such as the model incorrectly generating the following SQL statement:

[0076] SELECT name,hire_data FROM employees WHERE age>30;

[0077] There is a clear error here: hire_data should be hire_date.

[0078] Error Data Collection

[0079] When the above erroneous SQL statement is executed, the SQL executor will capture the execution failure information, including but not limited to:

[0080] Unique identifier: This can be the ID of this record in the dataset or other unique identifier.

[0081] Specific error messages: For example, "column 'hire_data' does not exist".

[0082] For each SQL statement that fails to execute, the system records its unique identifier and specific error message for subsequent analysis and correction. In this example, the record might look like this:

[0083] Furthermore, select a dataset suitable for a specific database (such as PostgreSQL, MySQL, etc.). This dataset should contain natural language queries, table structure information (e.g., table names, column names, data types, etc.), the target database type, and the corresponding SQL statements. Ensure that this data has been processed to adapt to the syntax and characteristics of the target database. To ensure that the dataset can be correctly understood and used by large open-source models, some format conversion may be necessary. For example, ensure that all SQL keywords remain in English while maintaining that data elements such as numbers and dates remain unchanged.

[0084] The input information for the open-source large model is constructed, including natural language queries, detailed table structure information (such as table names, column names, data types, etc.), and a clearly defined target database type (e.g., specifying PostgreSQL). This step is fundamental to ensuring that the model can accurately generate the required SQL queries based on the provided information. To guarantee the consistency and reproducibility of the results, these operations must be performed in a specially configured database server environment. For each test case, a separate database instance is created to avoid cross-contamination, and the extracted table creation SQL is executed to create the necessary schema and table structure in the corresponding database instance.

[0085] Design an executor to validate generated SQL statements. This executor should be able to execute SQL statements in an isolated environment and record the results of each execution. Regardless of whether the SQL execution is successful, its results should be logged in detail. When an SQL statement fails to execute, the system needs to capture and log the relevant error information. This typically includes the unique identifier of the SQL statement and the specific error message (such as a detailed description of a syntax or logical error). For example, if the generated SQL statement attempts to access a non-existent column, the error message might indicate this error.

[0086] Once error messages are captured, they need to be compiled and archived along with the original natural language query, the generated SQL statement, and any other relevant information. This is to provide comprehensive information support for subsequent analysis. Strict quality control measures should be implemented to ensure the accuracy of the collected error data. This may include consistency checks, SQL validity verification, and other steps to guarantee the authenticity and reliability of the collected data.

[0087] The erroneous data is corrected by using a closed-source large model to obtain corrected data.

[0088] Specifically, select and configure a closed-source large model: First, you need to choose a high-performance closed-source large language model (e.g., GLM-4-0520). Proper model configuration is necessary to optimize the performance of SQL correction tasks. This might include setting a lower temperature value (e.g., 0.3) to increase the determinism and consistency of the output.

[0089] Constructing Input Information: For each erroneous SQL sample, construct input information containing the following elements:

[0090] Original natural language query: that is, the user's initial query request.

[0091] Incorrect SQL statement: An SQL statement generated by an open-source large model but which failed to validate.

[0092] Error message: A detailed description or message explaining why the SQL statement failed to execute correctly.

[0093] Relevant database structure information: including detailed information such as table names, column names, and data types.

[0094] Explicit instructions: For example, "Please correct the error in the following PostgreSQL query and provide the correct SQL statement."

[0095] Extracting the corrected SQL statement using a closed-source model: Provide the above input information as context to the closed-source large model. The model will try to understand the problem based on this information and generate the corrected SQL statement.

[0096] Syntax checking and functional verification: To ensure the quality of the corrected SQL statements, syntax checking is performed using the PostgreSQL parser. Furthermore, the corrected SQL statements are executed in an isolated test environment to verify their functional correctness. This step helps filter out corrections that may still have issues.

[0097] Manual review mechanism: Although closed-source models typically have strong corrective capabilities, a lightweight manual review mechanism has been established to further ensure data quality. This includes sampling review of a small subset of randomly selected corrective results, and expert review of particularly complex or critical cases.

[0098] Storing the Correction Results: Finally, the original erroneous SQL, the corrected SQL, and related metadata are stored in a dedicated database. The basic structure of each record is as follows:

[0099] For example, suppose an incorrect SQL query has been generated and validated using an open-source large model. For instance, the original natural language question is: "Please list the names and start dates of all employees older than 30." The open-source large model generates the following incorrect SQL statement:

[0100] SELECT name,hire_data FROM employees WHERE age>30;

[0101] There is a clear error here: hire_data should be hire_date.

[0102] Input information construction

[0103] For this erroneous SQL sample, input information containing the following elements needs to be constructed for the closed-source large model (such as GLM-4-0520):

[0104] Original natural language query: "Please list the names and start dates of all employees who are over 30 years old."

[0105] Incorrect SQL statement: SELECT name,hire_data FROM employees WHERE age>30;

[0106] Error message: "column 'hire_data' does not exist"

[0107] Relevant database structure information: Table name is employees, and fields include name, age, and hire_date.

[0108] The explicit instruction is: "Please correct the error in the following PostgreSQL query and provide the correct SQL statement."

[0109] Extracting the corrected SQL statement using a closed-source model

[0110] The above input information is provided as context to the closed-source large model. After processing this information, the closed-source large model will attempt to understand the problem and generate a corrected SQL statement. In this example, the expected output might be:

[0111] SELECT name,hire_date FROM employees WHERE age>30;

[0112] Syntax checking and function verification

[0113] To ensure the quality of the corrected SQL statements, the following steps are required:

[0114] Syntax check: The PostgreSQL parser is used to perform a syntax check on the corrected SQL statement to confirm its syntactic correctness.

[0115] Functional verification: Execute the modified SQL statement in an isolated test environment to verify that it can correctly return the expected results. If the SQL statement executes successfully and returns the correct dataset, the modification is considered effective.

[0116] Manual review mechanism

[0117] Although closed-source models typically have strong remedial capabilities, to further ensure data quality, a small sample of randomly selected remedial results can be audited, and particularly complex or critical cases can be reviewed by experts. For example, in this case, manual review can confirm that the remediated SQL statement indeed solved the original problem.

[0118] Store correction results

[0119] Finally, the original erroneous SQL, the corrected SQL, and related metadata are stored in a dedicated database. The basic structure of each record is as follows:

[0120] Furthermore, to ensure effective correction of erroneous SQL generated by open-source models, it is necessary to select high-performance, closed-source large-scale models with good performance in Natural Language Processing (NLP) and Structured Query Language (SQL) generation. For example, models such as GLM-4-0520 can be used. Appropriate model configuration is necessary to improve the accuracy and efficiency of the correction task. This includes, but is not limited to, adjusting the temperature parameter (e.g., setting it to 0.3) to increase the determinism and consistency of the output; and adjusting other hyperparameters according to specific needs, such as maximum sequence length and batch size.

[0121] To help closed-source large models better understand where the errors are and generate correct corrections, detailed contextual information is needed. This information typically includes the original natural language query, the erroneous SQL statement, specific error messages (such as syntax or logical errors), relevant database structure information (table names, column names, data types, etc.), and explicit instructions (such as specifying the target database type to be corrected). For ease of model understanding and processing, all provided input information should adhere to a standardized format. For example, all SQL keywords should remain in English, specific field names should be translated or converted according to the requirements of the target database, and consistency of data elements such as numbers and dates should be ensured.

[0122] The first step for a closed-source large-scale model is to fully understand the provided context information and identify the specific location and cause of the error. This step is crucial for generating correct SQL statements subsequently. Based on its understanding of the problem, the closed-source large-scale model will attempt to generate corrected SQL statements. In this process, the model may utilize its internal knowledge base and algorithms to propose multiple possible correction schemes, ultimately selecting the most suitable one.

[0123] Perform a syntax check on the corrected SQL statement using the target database's parser (such as the PostgreSQL parser) to ensure it conforms to SQL syntax standards. Execute the corrected SQL statement in an isolated test environment to verify that it executes correctly and returns the expected results. This step helps filter out corrected results that, while syntactically correct, still have logical problems.

[0124] Although closed-source large models have high accuracy, it is still necessary to manually review a portion of randomly selected correction results. This further ensures data quality. For particularly complex or critical cases, inviting domain experts for review is essential. Experts can use their experience to judge the reasonableness of the correction results and provide suggestions for improvement.

[0125] The original erroneous SQL, the corrected SQL, and related metadata (such as unique identifiers, error messages, validation results, etc.) are stored in a specially designed database. This not only facilitates subsequent analysis and improvement but also provides valuable data resources for continuously enhancing model performance. Throughout the process, strict adherence to data security and privacy protection regulations is essential to ensure that all involved information is properly handled.

[0126] Error data and corrected data are correlated with query requirements to form training samples.

[0127] Specifically, firstly, it is necessary to extract the original erroneous SQL (i.e., SQL statements generated by the open-source large model but failing to be validated) and the corresponding corrected SQL (obtained through correction using the closed-source large model) from a specially designed database. Simultaneously, it is also necessary to obtain the original natural language query for each case; this information collectively forms the basis of the training samples.

[0128] Next, each pair of SQL statements (i.e., the incorrect SQL and the corrected SQL) is associated with its original natural language query. This means that each training sample should contain the following key elements:

[0129] Original natural language query: The question or request initially raised by the user.

[0130] Database structure information: including detailed information such as table names, column names, and data types, to ensure consistency across contexts.

[0131] Incorrect SQL (negative sample): SQL statements generated by the open-source large model but failing to be validated.

[0132] Corrected correct SQL (positive sample): The correct SQL statement obtained after correction of the closed-source large model.

[0133] Database type identifier: For example, PostgreSQL indicates the type of the target database.

[0134] The following aspects need to be considered when constructing training samples:

[0135] Data cleaning: Remove duplicate samples to ensure that each training sample is unique and prevent the model from overfitting to a certain type of error.

[0136] Standardized format: All samples are converted to a consistent JSON (JavaScript Object Notation, a lightweight data interchange format) format to facilitate subsequent batch processing and model handling. For example:

[0137] Metadata annotation: Add metadata tags to each sample, such as complexity type, main error type, etc., which helps with subsequent analysis and evaluation.

[0138] The final step is to divide the constructed dataset into training, validation, and test sets in a specific ratio (usually 8:1:1). This is done to ensure that each subset contains samples of various types and difficulty levels, thus comprehensively evaluating the model's performance. Specifically:

[0139] Training set: The main dataset used for model training.

[0140] Validation set: Used to adjust hyperparameters and monitor model performance during training to avoid overfitting.

[0141] Test set: Used to finally evaluate the model's generalization ability and actual performance.

[0142] For example, suppose the following information has been extracted from a specialized database:

[0143] Original natural language query: "Please list the names and start dates of all employees who are over 30 years old."

[0144] Database structure information: It contains a table named employees, which has fields such as name, age, and hire_date.

[0145] Target database type: PostgreSQL

[0146] Incorrect SQL statement (negative sample): SELECT name,hire_data FROM employees WHERE age>30;

[0147] Corrected SQL statement (positive sample): SELECT name, hire_date FROM employees WHERE age>30;

[0148] Next, the above information will be linked together to form a complete training sample. Each sample should include the following parts:

[0149] Original natural language query: The user's initial question or request.

[0150] Database structure information: Provides contextual information to ensure the model understands the query context.

[0151] Incorrect SQL statements (negative samples): SQL statements generated by a large open-source model but failing to be validated.

[0152] Corrected SQL statement (positive sample): The correct SQL statement obtained after correction of the closed-source large model.

[0153] Database type identifier: Clearly indicates the type of database for which the SQL statement is designed.

[0154] Sample Construction

[0155] Based on the above information, a specific training sample is constructed as follows:

[0156] Once multiple such sample sets have been constructed, they can be aggregated into a large dataset and divided into training, validation, and test sets according to a certain ratio. For example, an 8:1:1 ratio can be used to ensure that each subset contains samples of various types and difficulty levels.

[0157] Training set: The main dataset used for model training, which helps the model learn how to convert incorrect SQL to correct SQL.

[0158] Validation set: Used to tune hyperparameters and monitor model performance during training to avoid overfitting.

[0159] Test set: Used to finally evaluate the model's generalization ability and actual performance.

[0160] Furthermore, firstly, all relevant records need to be extracted from a specially designed database. These records include the original natural language queries, erroneous SQL statements (negative samples), corrected SQL statements (positive samples), and corresponding database structure information (such as table names, column names, data types, etc.).

[0161] For each erroneous data point and its corresponding corrected data, it must be associated with the original natural language query. Ensure that each training sample contains complete contextual information: what was the user's initial question, what erroneous SQL statement the model generated, and how to correct the error. During the association process, data consistency must be guaranteed. For example, all SQL keywords should remain in English, specific field names should be translated or converted according to the requirements of the target database, and consistency of data elements such as numbers and dates should be ensured.

[0162] When constructing training samples, the first step is to remove any duplicate samples. This step helps prevent the model from overfitting to a particular class of error and ensures that each sample is unique. For easier subsequent data processing and model training, all samples should be converted to a consistent format, such as JSON. This not only simplifies data management and transmission but also makes automated processing much easier.

[0163] Add necessary metadata tags to each training sample, such as complexity classification (easy, medium, hard) and main error types (e.g., syntax errors, logical errors, etc.). These tags can help analyze the model's performance more accurately and guide further improvement efforts. Evaluate the complexity of each sample based on the complexity of the SQL query (e.g., whether it contains subqueries, join operations, aggregate functions, etc.) and label them accordingly.

[0164] The constructed dataset is divided into training, validation, and test sets according to a certain ratio (usually recommended to be 8:1:1). This partitioning method ensures that the model has enough data for training, while also reserving a separate dataset for validating model performance and finally testing the model's generalization ability. When splitting the dataset, care should be taken to keep the complexity distribution among the subsets as uniform as possible to ensure that samples of different difficulty levels are adequately represented in each subset.

[0165] We employ a direct preference optimization strategy and use training samples to perform supervised fine-tuning on a large open-source model.

[0166] Specifically, the Direct Preference Optimization (DPO) strategy

[0167] DPO is an optimization method that directly uses human preference data to optimize the model, rather than relying on explicit reward modeling. The core of this approach is learning better choices by comparing different outputs generated by the model.

[0168] Optimize objective function

[0169] The objective function of DPO is designed to increase the probability of the model generating preferred outputs while reducing the probability of generating non-preferred outputs. Its mathematical expression is as follows:

[0170] f θ It is the strategy (i.e., generation probability) of the target model.

[0171] x is the input (such as natural language questions, table structure information, etc.).

[0172] y + It is a preference selection (i.e., the corrected SQL statement).

[0173] y - Yes / no preference selection (i.e., the original erroneous SQL statement).

[0174] α is a hyperparameter used to adjust the degree of difference between the target model and the reference model, and to control the degree of deviation of the target model.

[0175] Training process

[0176] Model input construction: For each training sample, correct SQL is selected as the preferred choice, and incorrect SQL is selected as the non-preferred choice. The model input should include instructions, natural language queries, and database structure information.

[0177] Calculate generation probabilities: Calculate the generation probabilities of preferred and non-preferred choices using both the target and reference models. This step involves feeding input to the model and obtaining the model's ratings or probability estimates for different outputs.

[0178] Loss calculation and parameter update: The loss value is calculated based on the DPO objective function, and the model parameters are updated using the backpropagation algorithm. This helps adjust the model weights, making the model more inclined to generate preferred choices rather than non-preferred choices in the future.

[0179] Training configuration

[0180] Learning rate setting: It is generally recommended to use a small learning rate (e.g., 1e-5 to 5e-5) to avoid excessive deviation from the capabilities of the original model.

[0181] Gradient accumulation technique: allows for the use of larger batch sizes, enabling effective training even under conditions of limited GPU memory.

[0182] Early stopping strategy: Stop training when performance on the validation set no longer improves to prevent overfitting.

[0183] Implementation details

[0184] Regular evaluation: During training, the model performance is periodically evaluated on the validation set, with particular attention to the accuracy of SQL generation and the consistency of hyperparameters with dynamic database tuning. Based on the validation results, the learning rate, α value, and other hyperparameters are dynamically adjusted to find the optimal training configuration.

[0185] For example, suppose we have constructed a series of high-quality training samples. Each sample includes:

[0186] Natural language query: For example, "Please list the names and start dates of all employees who are over 30 years old".

[0187] Database structure information: such as the table employees containing fields name, age, hire_date, etc.

[0188] Incorrect SQL statement (negative sample): SELECT name,hire_data FROM employees WHERE age>30;

[0189] Corrected SQL statement (positive sample): SELECT name, hire_date FROM employees WHERE age>30;

[0190] These samples have been converted into a uniform JSON format and divided into training, validation, and test sets in an 8:1:1 ratio.

[0191] Constructing model input

[0192] For each training sample, the model input is constructed as follows:

[0193] Instruction: Explicitly tell the model that this is a task about SQL generation.

[0194] Natural language query: The raw query description extracted from the dataset.

[0195] Database structure information: including detailed information such as table names, column names, and data types.

[0196] For example, a specific input might look like this:

[0197] Calculate the generation probability using the target model and the reference model.

[0198] For each training sample, the generation probabilities of preferred selection (i.e., the corrected SQL statement) and non-preferred selection (i.e., the original incorrect SQL statement) are calculated using the target model (the large open-source model being fine-tuned) and the reference model (which can be the initial version without fine-tuning or another better-performing model).

[0199] Calculate the loss based on the DPO objective function.

[0200] The loss value is calculated based on the objective function of Direct Preference Optimization (DPO). Specifically, the difference between the probability of the target model generating a preferred output and the probability of generating a non-preferred output given the input is compared, and the model parameters are adjusted accordingly. For example, the objective function might be:

[0201] f θ It is the strategy (i.e., generation probability) of the target model.

[0202] x is the input (such as natural language questions, table structure information, etc.).

[0203] y + It is a preference selection (i.e., the corrected SQL statement).

[0204] y - Yes / no preference selection (i.e., the original erroneous SQL statement).

[0205] Backpropagation updates model parameters

[0206] Based on the calculated loss value, the model parameters are updated using the backpropagation algorithm. This step aims to make the model more inclined to generate preferred choices rather than non-preferred choices in the future, thereby gradually improving its accuracy.

[0207] Training configuration example

[0208] Learning rate setting: Use a smaller learning rate (e.g., 1e-5) to avoid excessive deviation from the original model's capabilities.

[0209] Gradient accumulation technique: allows for the use of larger batch sizes, enabling effective training even under conditions of limited GPU (Graphics Processing Unit) memory.

[0210] Early stopping strategy: Stop training when performance on the validation set no longer improves to prevent overfitting.

[0211] Implementation details

[0212] Regular evaluation: During training, evaluate the model performance on the validation set regularly, paying particular attention to the accuracy of SQL generation and its consistency with database specifications.

[0213] Dynamically adjust hyperparameters: Based on the validation results, dynamically adjust the learning rate, α value and other hyperparameters to find the optimal training configuration.

[0214] Furthermore, design explicit instructions for each training sample to guide the model on how to process the input. For example, instructions such as "Generating the correct SQL statement based on the provided natural language query and database structure information" help guide the model to focus on the core requirements of the task. Ensure that the input contains all necessary contextual information, such as the natural language query, table structure information (including table names, column names, data types, etc.), and the target database type. This information is the foundation for the model to generate accurate SQL queries.

[0215] Choose appropriate target and reference models. The target model refers to the large, open-source model that is being fine-tuned, while the reference model can be an untuned initial version or another well-performing model. Calculate the generation probabilities of the preferred choice (the corrected SQL) and the non-preferred choice (the original incorrect SQL) using both models. By feeding the constructed input to the model, obtain the model's scores or probability estimates for different output options. This step involves complex internal computations that ultimately produce a score for each option.

[0216] According to some embodiments of this application, after employing a direct preference optimization strategy and using training samples to perform supervised fine-tuning on a large open-source model, the method further includes:

[0217] The fine-tuned open-source model was evaluated, specifically as follows:

[0218] Set evaluation metrics, including accuracy, F1 score, query execution success rate, and efficiency;

[0219] Use an independent test set to evaluate the model's generalization ability;

[0220] Test in real-world user environments, perform error analysis and collect user feedback to identify the shortcomings of the fine-tuned open-source model in specific scenarios or SQL types.

[0221] Develop an iterative improvement plan based on the evaluation results, including expanding the training dataset, adjusting the algorithm, or making targeted fine-tuning.

[0222] Specifically, setting evaluation indicators

[0223] Accuracy: Measures whether the generated SQL query is correct and error-free, that is, whether it accurately reflects the user's natural language query intent and is free from syntactic and logical errors.

[0224] F1 Score: The F1 score is the harmonic mean of precision and recall, used to comprehensively evaluate the performance of a model. It is particularly suitable for imbalanced datasets, providing a balanced evaluation metric for both precision and recall.

[0225] Query execution success rate: This step checks whether the generated SQL query can be successfully executed in the target database and return the expected results. This step not only verifies the correctness of the SQL syntax but also ensures the validity of the query logic.

[0226] Efficiency: Evaluate the speed at which the model generates SQL queries and the time it takes to execute them. An efficient model should be able to generate correct SQL statements within a reasonable time, and these queries should also have good execution performance.

[0227] Evaluate the model's generalization ability using an independent test set.

[0228] To ensure that the model performs well not only on the training data, it is also necessary to evaluate the model's generalization ability using a test set independent of the training and validation sets. This includes:

[0229] Diversity: The test set includes various types of SQL queries, covering different levels of complexity (such as simple, medium, and hard), to comprehensively test the model's ability to handle tasks of varying difficulty.

[0230] Unseen data: The samples in the test set should be ones that the model has never encountered before, so that they can more realistically reflect the model's performance in real-world applications.

[0231] Tested in a real user environment

[0232] Error analysis: By running the model in a real-world user environment, collect and analyze the errors or shortcomings it produces. For example, certain types of queries may consistently result in errors, or the model may perform worse than expected in certain scenarios.

[0233] Gather user feedback: Invite real users to try out the model and collect their feedback. Users can provide valuable insights from their practical experience, helping to identify areas where the model needs improvement.

[0234] Identifying shortcomings: Based on error analysis and user feedback, determine the model's deficiencies in specific scenarios or SQL types. For example, it may be found that the model frequently makes errors when handling queries involving multiple table joins, or has problems understanding certain industry-specific terminology.

[0235] Develop an iterative improvement plan

[0236] Based on the evaluation results, a specific iterative improvement plan will be developed, which may include the following aspects:

[0237] Expanding the training dataset: If the model is found to perform poorly on certain domains or types of queries, its capabilities can be enhanced by increasing the training samples in the corresponding domains.

[0238] Adjust the algorithm: Consider whether it is necessary to adjust the existing algorithm configuration, such as changing the learning rate, adjusting hyperparameters, optimizing the loss function, etc., to further improve model performance.

[0239] Targeted fine-tuning: The model is fine-tuned to address specific identified problems. For example, new training samples are designed specifically for query types that frequently occur in real-world applications but are poorly handled by the model, allowing for enhanced training.

[0240] According to some embodiments of this application, the specific method for obtaining the dataset of the adaptive database is as follows:

[0241] Extract data from the selected base data source dataset;

[0242] The extracted content is translated into Chinese using a closed-source large language model, while keeping the English SQL keywords, the structure, format, values, and dates of the SQL statements unchanged.

[0243] Using a closed-source large language model, the translated table creation SQL is corrected into SQL database format, and query SQL that conforms to SQL syntax is generated;

[0244] Perform consistency checks, SQL validity verification, and SQL compatibility testing;

[0245] The processed dataset is divided into training and test sets according to the original proportions, while maintaining the original complexity distribution.

[0246] Specifically, the dataset from the selected basic data source is extracted.

[0247] Choosing a base data source: First, you need to identify one or more high-quality base data sources as a starting point. These data sources typically contain a large number of natural language queries and their corresponding SQL statements, table structure information, etc.

[0248] Data Extraction: Extract all necessary field information from the selected data source. This includes, but is not limited to:

[0249] Natural Language Questions (Query Requests Submitted by Users)

[0250] Table structure information (such as table name, column names, data types, etc.)

[0251] Target database type (e.g., PostgreSQL)

[0252] SQL statements (including table creation SQL and query SQL)

[0253] The extracted content was translated into Chinese using a closed-source large language model.

[0254] Keep key elements unchanged: When translating content into Chinese, the following points must be ensured:

[0255] SQL keywords should be kept in English: keywords such as SELECT, FROM, and WHERE should not be translated to ensure the correctness of SQL syntax.

[0256] Maintaining consistency in SQL statement structure and format: Ensure that the translated SQL statements have the same structure and format as the original version, so as not to affect their execution.

[0257] Numerical values ​​and dates should remain unchanged: No numerical values ​​or date information should be altered to ensure data consistency and accuracy.

[0258] Using a closed-source large language model, the translated table creation SQL is corrected into SQL database format, and query SQL conforming to SQL syntax is generated.

[0259] Correcting table creation SQL: For SQL statements that may not fully conform to the requirements of the target database (e.g., PostgreSQL) after translation, they are corrected using a closed-source large language model. This step ensures that the table creation SQL statements can correctly create the required database table structure.

[0260] Generate Query SQL: Based on the translated natural language query and the corrected table creation SQL, a closed-source large language model is used to generate query SQL that conforms to SQL syntax specifications. This process aims to improve the quality of SQL statements, making them more accurately reflect the user's query intent.

[0261] Perform consistency checks, SQL validity verification, and SQL compatibility testing.

[0262] Consistency check: Confirm that no errors or inconsistencies were introduced during the translation and correction process. For example, ensure that all referenced table and column names exist and are spelled correctly.

[0263] SQL validity verification: Verify the validity of each SQL statement using a parser or other tools to ensure that they are syntactically correct and can be executed in the target database environment.

[0264] SQL compatibility testing: Additional compatibility testing is conducted for different types of databases (such as MySQL, PostgreSQL, etc.) to ensure that the generated SQL statements can run smoothly in different database systems.

[0265] The processed dataset is divided into training and test sets according to the original proportions, while maintaining the original complexity distribution.

[0266] Data partitioning: The processed dataset is divided into training, validation, and test sets according to a pre-defined ratio (e.g., 8:1:1). This partitioning helps ensure that the model has enough data for training, while reserving a separate dataset for validating model performance and ultimately evaluating the model's generalization ability.

[0267] Maintaining Complexity Distribution: When splitting the data, ensure that the complexity distribution of samples in each subset is as close as possible to the proportion of the original dataset. This means that samples of different difficulty levels should be sufficiently represented in each subset, thereby ensuring that the model can learn to handle various types of problems.

[0268] According to some embodiments of this application, the generated data is verified, and erroneous data that fails verification is collected, specifically as follows:

[0269] Generate SQL queries using open-source large models. Inputs include: natural language questions, table structure information, and target database type.

[0270] The generated SQL statement is validated using the SQL executor.

[0271] For SQL statements that fail to execute, record their unique identifier and specific error information.

[0272] Specifically, SQL queries are generated using open-source large models.

[0273] Input preparation: To generate the SQL query, the following information needs to be provided to the open-source large model:

[0274] Natural Language Questions: These are queries made by users, such as "Please list the names and start dates of all employees who are over 30 years old".

[0275] Table structure information: This includes detailed information such as table name, column names, and data types. For example, the employees table contains fields such as name, age, and hire_date.

[0276] Target database type: Clearly specify the type of database the SQL statement is designed for, such as PostgreSQL.

[0277] Verify the generated SQL statement using the SQL executor.

[0278] Execution environment setup: Perform these operations in a specially configured database server environment. Ensure that each test case has an independent database instance to avoid cross-contamination.

[0279] Schema creation: Execute the extracted table creation SQL to create the necessary schema and table structure in the corresponding database instance.

[0280] SQL Executor Design: Design an executor for validating generated SQL statements. This executor should be able to execute SQL statements in an isolated environment and record the results of each execution.

[0281] Results logging: Regardless of whether the SQL execution is successful, its results should be recorded in detail. For successful executions, record the returned dataset; for failed executions, record the specific error information.

[0282] For SQL statements that fail to execute, record their unique identifier and specific error information.

[0283] Error detection and logging:

[0284] Unique Identifier: Each SQL statement has a unique identifier that can be used to track and manage errors.

[0285] Specific error messages: When an SQL statement fails to execute, the system captures and logs specific error messages. For example, "column 'hire_data' does not exist" means that an attempt was made to access a non-existent column.

[0286] The record format may look like this:

[0287] Error Data Collection

[0288] Error data organization: Once error information is captured, it needs to be organized and archived along with the original natural language query, the generated SQL statement, and any other relevant information. The purpose of this is to provide comprehensive information support for subsequent analysis and correction.

[0289] Quality control measures: Implement strict quality control measures to ensure the accuracy of collected error data. This may include steps such as consistency checks and SQL validity verification to guarantee the authenticity and reliability of the collected data.

[0290] According to one embodiment of this application, erroneous data is corrected using a closed-source large model to obtain corrected data, specifically as follows:

[0291] Select and configure a closed-source large model;

[0292] For each erroneous SQL sample, construct input information containing the following elements: the original natural language query, the erroneous SQL statement, the error message, the relevant database structure information, and the explicit instructions;

[0293] The input information is provided as context to the closed-source model to extract the corrected SQL statement;

[0294] Use PostgreSQL's parser for syntax checking;

[0295] A sample of randomly selected correction results was reviewed, and complex or critical cases were reviewed by experts.

[0296] The original erroneous SQL, the corrected SQL, and related metadata are stored in a dedicated database.

[0297] Specifically, select and configure a closed-source large model.

[0298] Selection of high-performance closed-source large models: Select a high-performance closed-source large model that performs well in natural language processing (NLP) and structured query language (SQL) generation, such as GLM-4-0520.

[0299] Model configuration optimization: To improve the accuracy and efficiency of the correction task, the model needs to be configured appropriately. For example, setting a lower temperature value (such as 0.3) can increase the determinism and consistency of the output and reduce randomness.

[0300] Construct input information containing the following elements

[0301] For each erroneous SQL sample, input information containing the following elements needs to be constructed:

[0302] Raw natural language query: This refers to the user's initial query request, such as "Please list the names and start dates of all employees who are over 30 years old".

[0303] Incorrect SQL statement: This is an SQL statement generated by the open-source large model but failed to be validated, such as SELECT name,hire_data FROM employees WHERE age>30;

[0304] Error message: A specific error description or prompt indicating why the SQL statement failed to execute correctly, such as "column 'hire_data' does not exist".

[0305] Relevant database structure information: including table names, column names, data types, and other detailed information, to ensure context consistency.

[0306] Clear instructions: Provide clear operation instructions, such as "Please correct the error in the following PostgreSQL query and provide the correct SQL statement".

[0307] The input information is provided as context to the closed-source model to extract the corrected SQL statement.

[0308] Contextual understanding and problem localization: The above input information is provided as context to the closed-source large model. The model first needs to fully understand the provided context information and identify the specific location and cause of the error.

[0309] Generating Correction Suggestions: Based on the understanding of the problem, the closed-source large model attempts to generate corrected SQL statements. For example, for the example above, the expected output might be: SELECT name, hire_date FROM employees WHERE age>30;

[0310] Syntax checking using PostgreSQL's parser

[0311] Syntax check: The corrected SQL statement is syntax-checked using the parser of the target database (such as PostgreSQL) to ensure it conforms to SQL syntax rules. This process helps filter out corrections that, while logically correct, actually contain syntax errors.

[0312] A sample of randomly selected correction results was reviewed, and complex or critical cases underwent expert verification.

[0313] Quality control measures: Although closed-source large models have high accuracy, it is still necessary to manually review a portion of randomly selected correction results. This can further ensure data quality.

[0314] Expert review: For particularly complex or critical cases, it is essential to invite domain experts to review the work. Experts can use their experience to judge whether the revised results are reasonable and provide suggestions for improvement.

[0315] The original erroneous SQL, the corrected SQL, and related metadata are stored in a dedicated database.

[0316] Metadata records: The original erroneous SQL, the corrected SQL, and related metadata (such as unique identifiers, the original natural language query, error messages, validation results, etc.) are stored in a specially designed database. The basic structure of each record is as follows:

[0317] Data security and privacy protection: Throughout the process, data security and privacy protection regulations must be strictly followed to ensure that all information involved is properly handled.

[0318] According to some embodiments of this application, error data and correction data are associated with query requirements to form training samples, specifically:

[0319] Extract the original erroneous SQL and the corresponding corrective SQL from the database;

[0320] Each pair of SQL statements is associated with the original natural language query to form a complete training sample;

[0321] Remove duplicate samples, convert all samples to a consistent JSON format, and add metadata tags to each sample;

[0322] The constructed dataset is divided into training set, validation set and test set in a ratio of 8:1:1.

[0323] Specifically, extract the original erroneous SQL and the corresponding corrected SQL from the database.

[0324] Data extraction: The first step is to extract all relevant records from a specially designed database. These records include:

[0325] Original error SQL: SQL statements generated by a large open-source model but failing to validate.

[0326] The corresponding corrected SQL: The correct SQL statement obtained after correction of the closed-source large model.

[0327] Each pair of SQL statements is associated with the original natural language query to form a complete training sample.

[0328] Associating Natural Language Queries: For each erroneous data point and its corresponding corrected data, it needs to be associated with the original natural language query. Ensure that each training sample contains complete contextual information, i.e., what the user's initial question was, what erroneous SQL the model generated, and how to correct the error.

[0329] Example: Suppose a user's query is "Please list the names and start dates of all employees older than 30." The open-source model generates the incorrect SQL: `SELECT name,hire_data FROM employees WHERE age>30;`, while the closed-source model corrects it to `SELECT name,hire_date FROM employees WHERE age>30;`. Therefore, the sample should include these three elements.

[0330] Remove duplicate samples and convert all samples to a consistent JSON format.

[0331] Deduplication: When constructing training samples, the first step is to remove any duplicate samples. This step helps prevent the model from overfitting to a particular class of error and ensures that each sample is unique.

[0332] Standardized Formatting: To facilitate subsequent data processing and model training, all samples should be converted to a consistent format, such as JSON. This not only simplifies data management and transmission but also makes automated processing easier. For example:

[0333] Add metadata tags to each sample

[0334] Metadata annotation: Add necessary metadata labels to each training sample, such as complexity classification (easy, medium, hard) and main error types (e.g., syntax errors, logical errors, etc.). These labels can help to analyze the model's performance more accurately and guide further improvement efforts.

[0335] Complexity assessment: The complexity of each sample is assessed based on the complexity of the SQL query (e.g., whether it contains subqueries, join operations, aggregate functions, etc.), and the samples are marked accordingly.

[0336] Error type labeling: Clearly indicates the main error type in each sample, so as to improve model performance in a targeted manner.

[0337] The constructed dataset is divided into training set, validation set, and test set in an 8:1:1 ratio.

[0338] Dataset Splitting: Once multiple such sample sets have been constructed, they can be aggregated into a large dataset and divided into training, validation, and test sets according to a certain ratio. A commonly recommended ratio is 8:1:1, as follows:

[0339] Training set: The main dataset used for model training, which helps the model learn how to convert incorrect SQL to correct SQL.

[0340] Validation set: Used to adjust hyperparameters and monitor model performance during training to avoid overfitting.

[0341] Test set: Used to finally evaluate the model's generalization ability and actual performance.

[0342] Maintaining complexity distribution: When splitting a dataset, care should be taken to keep the complexity distribution among the subsets as uniform as possible to ensure that samples of different difficulty levels are adequately represented in each subset.

[0343] According to some embodiments of this application, a direct preference optimization strategy is adopted to perform supervised fine-tuning of a large open-source model using training samples, specifically as follows:

[0344] For each training sample, the correct SQL is selected as the preferred choice, and the incorrect SQL is selected as the non-preferred choice.

[0345] Construct model inputs, including instructions, natural language queries, and database structure information;

[0346] The generation probabilities of preferred and unpreferred choices are calculated using the target model and the reference model, respectively.

[0347] The loss is calculated based on the objective function optimized by direct preferences, and the model parameters are updated through backpropagation.

[0348] Specifically, for each training sample, the correct SQL is selected as the preferred choice, and the incorrect SQL is selected as the non-preferred choice.

[0349] Define preferences and non-preferences: In each training sample, the correctly corrected SQL statement is considered the "preference choice," while the erroneous SQL statement generated by the large open-source model but failing validation is considered the "non-preference choice." This setting helps the model learn how to shift from generating incorrect SQL to generating correct SQL.

[0350] The model inputs include instructions, natural language queries, and database structure information.

[0351] Instructions: Explicitly tell the model that this is a task about SQL generation. For example, "Generate the correct SQL statement based on the provided natural language query and database structure information." This type of instruction helps guide the model to focus on the core requirements of the task.

[0352] Natural language queries: User-submitted queries, such as "Please list the names and start dates of all employees over 30 years old".

[0353] Database structure information: This includes detailed information such as table names, column names, and data types. For example, the `employees` table contains fields such as `name` (name), `age` (age), and `hire_date` (hire date). Sample input might look like this:

[0354] The generation probabilities of preference selection and non-preference selection are calculated using the target model and the reference model, respectively.

[0355] Target Model and Reference Model: The target model refers to the large, open-source model that is being fine-tuned, while the reference model can be an initial version that has not been fine-tuned or another model with better performance. The generation probabilities of the preferred choice (i.e., the corrected SQL) and the unpreferred choice (i.e., the original incorrect SQL) are calculated using these two models respectively.

[0356] Probability generation calculation: The constructed input is provided to the model, and the model's scores or probability estimates for different output options are obtained. This step involves complex internal calculations that ultimately produce a score for each option. For example, the target model might give the following scores:

[0357] Preference selection (correct SQL): 0.85

[0358] Non-preferred selection (erroneous SQL): 0.15

[0359] The loss is calculated based on the objective function optimized by direct preferences, and the model parameters are updated through backpropagation.

[0360] DPO Objective Function: The core of DPO lies in its unique loss function design, which aims to increase the probability of the model generating preferred outputs while reducing the probability of generating undesirable outputs. Its specific form is as follows:

[0361] f θ It is the strategy (i.e., generation probability) of the target model.

[0362] x is the input (such as natural language questions, table structure information, etc.).

[0363] y + It is a preference selection (i.e., the corrected SQL statement).

[0364] y - Yes / no preference selection (i.e., the original erroneous SQL statement).

[0365] Loss Calculation and Parameter Update: Based on the loss value calculated using the objective function described above, the model parameters are updated using the backpropagation algorithm. This step involves complex gradient calculations and weight adjustments to make the model more likely to generate preferred choices rather than unpreferred choices in the future. Specifically, by adjusting the weights in the model, it is made more likely that the model will generate correct SQL statements rather than incorrect SQL statements when receiving similar inputs.

[0366] A computer program product containing instructions that, when run on a device, causes the device to execute steps in implementing a large NL2SQL model training method based on error correction and preference optimization.

[0367] A non-volatile readable storage medium storing a program that, when executed by a processor, implements steps in a large NL2SQL model training method based on error correction and preference optimization.

[0368] An electronic device includes a memory, a processor, and a program stored in the memory and executable on the processor, wherein the processor executes the program to implement steps in a large NL2SQL model training method based on error correction and preference optimization.

[0369] For any parts not mentioned in this application, existing technologies may be used or referenced.

[0370] The various embodiments in this specification are described in a progressive manner. The same or similar parts between the various embodiments can be referred to each other. Each embodiment focuses on describing the differences from other embodiments.

[0371] The above are merely embodiments of this application and are not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A training method for a large NL2SQL model based on error correction and preference optimization, characterized in that, include: The dataset adapted to the database is input into the open-source large model, and the generated data is validated. Error data that fails to be validated is collected. The erroneous data is corrected using a closed-source large model to obtain corrected data; The erroneous data and the corrected data are associated with the query requirements to form training samples; A direct preference optimization strategy is adopted, and the open-source large model is subjected to supervised fine-tuning using the training samples; The fine-tuned open-source model is evaluated. Develop an iterative improvement plan based on the evaluation results, including expanding the training dataset, adjusting the algorithm, or making targeted fine-tuning. The finely tuned open-source large model parsing instructions are used to generate SQL statements.

2. The method according to claim 1, characterized in that, Before inputting the dataset from the adapted database into the open-source large model, the method includes: The information input to the open-source large model is constructed in a specially configured database server environment.

3. The method according to claim 2, characterized in that, The process of constructing the input information for the open-source large model in a specially configured database server environment includes: For each test case, a separate database instance is created, and the extracted table creation SQL is executed to create the necessary schema and table structure in the corresponding database instance.

4. The method according to claim 1, characterized in that, The evaluation of the fine-tuned open-source large model includes: Set evaluation metrics, including accuracy, F1 score, query execution success rate, and efficiency; Use an independent test set to evaluate the model's generalization ability; Test in real-world user environments, perform error analysis, collect user feedback, and identify shortcomings of the fine-tuned open-source model in specific scenarios or SQL types.

5. The method according to claim 4, characterized in that, The specific method for obtaining the dataset of the adapted database is as follows: Extract data from the selected base data source dataset; The extracted content is translated into Chinese using a closed-source large language model, while keeping the English SQL keywords, the structure, format, values, and dates of the SQL statements unchanged. Using a closed-source large language model, the translated table creation SQL is corrected into SQL database format, and query SQL that conforms to SQL syntax is generated; Perform consistency checks, SQL validity verification, and SQL compatibility testing; The processed dataset is divided into training and testing sets according to the original proportions, while maintaining the original complexity distribution.

6. The method according to claim 1, characterized in that, The process of validating the generated data and collecting erroneous data that fails the verification specifically involves: Generate SQL queries using open-source large models. Inputs include: natural language questions, table structure information, and target database type. The generated SQL statement is validated using the SQL executor. For SQL statements that fail to execute, record their unique identifier and specific error information.

7. The method according to claim 6, characterized in that, The SQL executor is configured to execute the generated SQL statements in an isolated environment and record the execution results.

8. The method according to claim 1, characterized in that, The step of correcting the erroneous data using a closed-source large model to obtain corrected data is as follows: Select and configure a closed-source large model; For each erroneous SQL sample, construct input information containing the following elements: the original natural language query, the erroneous SQL statement, the error message, the relevant database structure information, and the explicit instructions; The input information is provided as context to the closed-source model to extract the corrected SQL statement; Use PostgreSQL's parser for syntax checking; A sample of randomly selected correction results was reviewed, and complex or critical cases were reviewed by experts. The original erroneous SQL, the corrected SQL, and related metadata are stored in a dedicated database.

9. The method according to claim 8, characterized in that, The selection and configuration of the closed-source large model includes: Set the temperature value of the closed-source large model.

10. The method according to claim 8, characterized in that, The step of providing the input information as context to the closed-source model and extracting the corrected SQL statement includes: The closed-source large model understands the context and identifies the specific location and cause of the error; The closed-source large model uses its internal knowledge base and algorithms to propose multiple correction schemes, selects the most suitable correction scheme from the multiple correction schemes, and generates the corrected SQL statement.

11. The method according to claim 8, characterized in that, After performing syntax checking using the PostgreSQL parser, the method includes: Execute the revised SQL statement in an isolated test environment; If the corrected SQL statement is executed successfully and returns the correct dataset, the correction is considered valid.

12. The method according to claim 1, characterized in that, The step of associating the erroneous data and the corrected data with the query request to form training samples specifically involves: Extract the original erroneous SQL and the corresponding corrective SQL from the database; Each pair of SQL statements is associated with the original natural language query to form a complete training sample; Remove duplicate samples, convert all samples to a consistent JSON format, and add metadata tags to each sample; The constructed dataset is divided into training set, validation set and test set in a ratio of 8:1:

1.

13. The method according to claim 12, characterized in that, Before dividing the constructed dataset into training, validation, and test sets in an 8:1:1 ratio, the method includes: Based on the complexity of the SQL query, the complexity of each training sample is evaluated, and a corresponding complexity classification metadata tag is added to each training sample.

14. The method according to claim 1, characterized in that, The direct preference optimization strategy, using the training samples, is employed to perform supervised fine-tuning of the open-source large model, specifically as follows: For each training sample, the correct SQL is selected as the preferred choice, and the incorrect SQL is selected as the non-preferred choice. Construct model inputs, including instructions, natural language queries, and database structure information; The generation probabilities of preferred and unpreferred choices are calculated using the target model and the reference model, respectively. The loss is calculated based on the objective function optimized by direct preferences, and the model parameters are updated through backpropagation.

15. The method according to claim 14, characterized in that, The target model is the open-source large model that is being fine-tuned, and the reference model is the initial version of the open-source large model that has not been fine-tuned or another model with better performance.

16. The method according to claim 14, characterized in that, The method further includes: The model performance of the target model is evaluated periodically on the validation set during training.

17. The method according to claim 16, characterized in that, The method further includes: The learning rate, α value, and other hyperparameters are dynamically adjusted based on the validation results to find the optimal training configuration.

18. A computer program product containing instructions, characterized in that, when run on a device, This enables the device to perform the steps of the NL2SQL large model training method based on error correction and preference optimization as described in any one of claims 1-17.

19. A non-volatile readable storage medium having a program stored thereon, characterized in that, When executed by the processor, the program implements the steps of the NL2SQL large model training method based on error correction and preference optimization as described in any one of claims 1-17.

20. An electronic device comprising a memory, a processor, and a program stored in the memory and executable on the processor, characterized in that, When the processor executes the program, it implements the steps in the NL2SQL large model training method based on error correction and preference optimization as described in any one of claims 1-17.

Images