NL2SQL large model training method based on error correction and preference optimization

Abstract

The invention provides an NL2SQL large model training method based on error correction and preference optimization, and relates to the technical field of natural language processing, and the method comprises the steps: inputting a data set of an adaptive database into an open source large model, verifying the generated data, and collecting error data which fails in verification; correcting the error data through a closed source large model to obtain corrected data; associating the error data and the correction data with a query requirement to form a training sample; and performing supervised fine tuning on the open-source large model by adopting a direct preference optimization strategy and utilizing the training sample.

Description

Technical Field

[0001] This application belongs to the technical field of natural language processing, and specifically relates to a method for training an NL2SQL large model based on error correction and preference optimization. Background Art

[0002] The technology of natural language to SQL (NL2SQL) is an important research direction in the fields of database query and business intelligence (BI), aiming to reduce 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) have shown high accuracy in NL2SQL tasks, but their applications are limited by insufficient openness, privacy risks, and high costs. In contrast, although open-source large models (such as qwen-14b) have the advantages of transparency and cost, they have significant defects when generating SQL, such as high error rates, weak instruction understanding ability, and poor adaptability to the syntax of specific databases.

[0003] The current technical solutions mainly optimize the NL2SQL task in the following ways: relying on high-performance closed-source models to generate SQL, but it needs to be called through a third-party API, which has data security risks and is difficult to customize. Fine-tuning the open-source model with domain data, but limited by the lack of high-quality labeled data and large computational resource requirements. Combining closed-source and open-source models to generate data, but lacking a systematic error correction mechanism, resulting in unstable training data quality.

[0004] The SQL generated by open-source models often contains syntax or semantic errors and needs to rely on manual correction, which is inefficient. The model is difficult to accurately parse complex query requirements, resulting in the generated SQL deviating from the user's intention. The general training data does not cover the syntax features of different databases (such as PostgreSQL, MySQL), and the generated results have low compatibility. Existing methods rely on manual annotation or unoptimized synthetic data, which are difficult to scale and have weak generalization ability. Summary of the Invention

[0005] This application provides a method for training an NL2SQL large model based on error correction and preference optimization to solve the technical problems of high error rates, weak instruction following ability, and difficulty in adapting to the syntax rules of specific databases when open-source large language models execute NL2SQL tasks.

[0006] The technical solution adopted by this application is as follows:

[0007] An embodiment of this application provides a method for training an NL2SQL large model based on error correction and preference optimization, including:

[0008] Input the dataset adapted to the database into the open-source large model, verify the generated data, and collect the error data that fails the verification;

[0009] The error data is corrected through a closed-source large model to obtain corrected data;

[0010] The error data and the corrected data are associated with the query requirements to form a training sample;

[0011] Adopt the direct preference optimization strategy and use the training sample to perform supervised fine-tuning on the open-source large model.

[0012] According to an embodiment of the present application, after adopting the direct preference optimization strategy and using the training sample to perform supervised fine-tuning on the open-source large model, it further includes:

[0013] Evaluate the fine-tuned open-source large model, specifically:

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

[0015] Use an independent test set to evaluate the generalization ability of the model;

[0016] Conduct tests in an actual user environment, perform error analysis, and collect user feedback to identify the deficiencies of the fine-tuned open-source large model in specific scenarios or SQL types;

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

[0018] According to an embodiment of the present application, the specific method for obtaining the dataset adapted to the database is:

[0019] Extract the dataset of the selected basic data source;

[0020] Use a closed-source large language model to translate the extracted content into Chinese, keeping SQL keywords in English, the structure, format, numerical values, and dates of SQL statements unchanged;

[0021] Use a closed-source large language model to correct the translated SQL for creating tables into the SQL database format and generate query SQL that conforms to SQL syntax;

[0022] Perform consistency check, SQL validity verification, and SQL compatibility test;

[0023] Divide the processed dataset into a training set and a test set according to the original ratio, maintaining the original complexity distribution.

[0024] According to an embodiment of the present application, the verification of the generated data and the collection of error data that fails the verification are specifically:

[0025] Generate SQL queries using an open-source large model. The inputs include: natural language questions, table structure information, and the target database type;

[0026] Verify the generated SQL statements through an SQL executor;

[0027] For SQL statements that fail to execute, record their unique identifiers and specific error messages.

[0028] According to an embodiment of the present application, the error data is corrected by a closed-source large model to obtain corrected data, specifically:

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

[0030] 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 a clear instruction;

[0031] Provide the input information as context to the closed-source model and extract the corrected SQL statement;

[0032] Use the parser of PostgreSQL for syntax checking;

[0033] Randomly sample and review some of the corrected results, and conduct expert review for complex or critical cases;

[0034] Store the original erroneous SQL, the corrected SQL, and the relevant metadata in a dedicated database.

[0035] According to an embodiment of the present application, the error data and the corrected data are associated with the query requirements to form training samples, specifically:

[0036] Extract the original erroneous SQL and the corresponding corrected SQL from the database;

[0037] Associate each pair of SQL statements with the original natural language query to form complete training samples;

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

[0039] Divide the constructed dataset into a training set, a validation set, and a test set in a ratio of 8:1:1.

[0040] According to an embodiment of the present application, the direct preference optimization strategy is adopted to perform supervised fine-tuning on the open-source large model using the training samples, specifically:

[0041] For each training sample, use the correct SQL as the preferred choice and the incorrect SQL as the non-preferred choice;

[0042] Construct the model input, including the instruction, natural language query, and database structure information;

[0043] Use the target model and the reference model to calculate the generation probabilities of the preferred choice and the non-preferred choice respectively;

[0044] Calculate the loss according to the direct preference optimization objective function and update the model parameters through backpropagation.

[0045] A computer program product containing instructions, when it runs on a device, enables the device to execute the steps in the NL2SQL large model training method based on error correction and preference optimization.

[0046] A computer-readable storage medium, on which a program is stored, and when the program is executed by a processor, it implements the steps in the NL2SQL large model training method based on error correction and preference optimization.

[0047] An electronic device, including a memory, a processor, and a program stored on the memory and executable on the processor, and when the processor executes the program, it implements the steps in the NL2SQL large model training method based on error correction and preference optimization.

[0048] Due to the adoption of the above technical solutions, the beneficial effects obtained by this application are:

[0049] By inputting a dataset adapted to the database into an open-source large model and verifying the generation results, this application can systematically collect incorrect SQL samples, accurately locate the defects of the model in syntax, semantics, and database adaptability, and provide high-quality negative samples for subsequent correction.

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

[0051] This application strongly associates incorrect SQL, corrected SQL, and the original natural language query to form an "input-incorrect output-correct output" triple training sample, enabling the model to learn the mapping relationship from natural language to SQL and enhancing the instruction understanding and execution capabilities.

[0052] This application uses the DPO strategy to perform supervised fine-tuning on the open-source model. By comparing the generation probabilities of positive and negative samples, it directly optimizes the model preference, reduces the generation probability of incorrect SQL, and simultaneously improves the adaptability to complex queries and database features.

[0053] Through the closed-loop data generation-correction-training mechanism, this technical solution solves the core defects of open-source large models in the NL2SQL task. The combination of error correction and DPO fine-tuning increases the SQL generation accuracy by more than 30% (measured data); the design of training samples associated with natural language queries improves the model's parsing success rate for complex instructions by 25%; customizing training data for the syntax characteristics of different databases enables the generated SQL to be compatible with mainstream databases (such as PostgreSQL and MySQL); the automated correction and preference optimization strategy reduces manual intervention by 80% and the training cost by 40%. BRIEF DESCRIPTION OF THE DRAWINGS

[0054] The drawings described herein are used to provide a further understanding of the present application and form a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation of the present application. In the drawings:

[0055] Figure 1 It is a schematic flow chart of a method for training an NL2SQL large model based on error correction and preference optimization provided by an embodiment of the present application. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0056] In order to more clearly explain the overall concept of the present application, the following will be described in detail by way of examples in conjunction with the drawings of the specification.

[0057] In the following description, many specific details are set forth in order to fully understand the present application. However, the present application may be implemented in other ways different from those described herein. Therefore, the protection scope of the present application is not limited by the specific embodiments disclosed below. It should be noted that, without conflict, the embodiments of the present application and the features in each embodiment may be combined with each other.

[0058] In the present application, unless otherwise clearly specified and limited, the first feature being "on" or "under" the second feature may be that the first and second features are in direct contact, or the first and second features are indirectly in contact through an intermediate medium. In the description of this specification, the description with reference to terms such as "one embodiment", "some embodiments", "example", "specific example", or "some examples" means that the specific features, structures, materials, or characteristics described in connection with the embodiment or example are included in at least one embodiment or example of the present application. In this specification, the schematic representations of the above terms do not necessarily refer to the same embodiment or example. Moreover, the specific features, structures, materials, or characteristics described may be combined in any one or more embodiments or examples in a suitable manner.

[0059] Embodiment 1

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

[0061] Input the dataset adapted to the database into the open-source large model, verify the generated data, and collect the error data that fails the verification.

[0062] Specifically, first, the dataset adapted to the database refers to those 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, etc.

[0063] Then, input these adapted data into the open-source large model with the aim of having the model generate corresponding SQL queries. In this process, the input information includes but is not limited to: natural language questions (i.e., what the user wants to query), table structure information (such as detailed information about table names, column names, data types, and constraints), and the target database type (clearly specifying which database's SQL statements are to be generated, such as PostgreSQL).

[0064] Next is to verify the generated SQL statements. This step is completed by designing an SQL executor that can execute the generated SQL statements in an isolated environment and record the execution results. Whether the SQL is successfully executed or not, its results will be recorded in detail. In particular, for SQL statements that fail to execute, the system will record their unique identifiers and specific error messages. This is done to collect those SQL statements that fail to execute due to syntax or logical errors as the basis for subsequent improvement.

[0065] Finally, collecting the error data that fails the verification means screening out all the SQL queries that fail to execute correctly and their related information (such as unique identifiers and error details) from the above process, and organizing and archiving these error data. These error data will be used in subsequent steps to correct and optimize the model. The closed-source large model will be used to correct them, thereby forming high-quality training samples and further improving the accuracy and reliability of the open-source large model when generating SQL queries.

[0066] For example, assume that a dataset adapted to the PostgreSQL database is used. This dataset contains natural language questions, table structure information (such as table names, column names, data types, etc.), and the target database type (PostgreSQL). For example, extract a record from this dataset:

[0067] Natural language question: "Please list the names and hire dates of all employees whose age is greater than 30 years old."

[0068] Table structure information: It contains a table named employees with fields such as name (first name), age (age), hire_date (hire date), etc.

[0069] Target database type: PostgreSQL

[0070] Model generates SQL statements

[0071] Provide the above information as input to an open-source large model (such as qwen-14b) to generate corresponding SQL query statements based on this information. Assume the model generates the following SQL statement:

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

[0073] SQL executor verification

[0074] Design an SQL executor to execute this SQL statement in an isolated environment. In this example, if the SQL statement is correct, the result will be successfully returned; but if an error occurs, for example, the model wrongly generates the following SQL statement:

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

[0076] There is an obvious error here: hire_data should be hire_date.

[0077] Error data collection

[0078] When executing the above incorrect SQL statement, the SQL executor will capture the information of the execution failure, including but not limited to:

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

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

[0081] For each SQL statement that fails to execute, the system will record its unique identifier and specific error information for subsequent analysis and correction. In this example, the record may look like this:

[0082]

[0083] Further, select a dataset suitable for a specific database (such as PostgreSQL, MySQL, etc.). This dataset should include natural language questions, table structure information (such as 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 enable the dataset to be correctly understood and used by open-source large models, it may be necessary to perform certain format conversions on the data. For example, ensure that all SQL keywords remain in English unchanged, while ensuring that data elements such as numerical values and dates do not change.

[0084] Construct the information input to the open-source large model, including natural language questions, detailed table structure information (such as table names, column names, data types, etc.), and a clear target database type (for example, specified as PostgreSQL). This step is the basis for ensuring that the model can accurately generate SQL queries that meet the requirements. To ensure the consistency and reproducibility of the results, these operations need to be performed in a specially configured database server environment. For each test case, create an independent database instance to avoid cross-contamination, and execute the extracted table-creation SQL to create the necessary schema and table structure in the corresponding database instance.

[0085] Design an executor for verifying the executed SQL statements. This executor can execute SQL statements in an isolated environment and record the results of each execution. Whether the SQL is successfully executed or not, its results should be recorded in detail. When the SQL statement fails to execute, the system needs to capture and record the relevant error information. This usually includes the unique identifier of the SQL statement and the specific error message (such as a detailed description of a syntax error or a logical error). For example, if the generated SQL statement attempts to access a non-existent column, the error message may indicate this error.

[0086] Once the error information is captured, it needs to be sorted and archived together with the original natural language question, the generated SQL statement, and any other associated information. The purpose of doing this is to provide comprehensive information support for subsequent analysis. Implement strict quality control measures to ensure the accuracy of the collected error data. This may include steps such as consistency checks and SQL validity verification to ensure the authenticity and reliability of the collected data.

[0087] Use a closed-source large model to correct the error data to obtain corrected data.

[0088] Specifically, select and configure a closed-source large model: First, a high-performance closed-source large language model (such as GLM-4-0520) needs to be selected. To optimize the performance of the SQL correction task, it is necessary to configure the model appropriately. This may include setting a lower temperature value (such as 0.3) to increase the determinacy and consistency of the output.

[0089] Construct input information: For each incorrect 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: The SQL statement generated by the open-source large model but failed the verification.

[0092] Error message: Specific error description or hint, explaining why the SQL statement failed to execute correctly.

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

[0094] Clear instruction: For example, "Please correct the errors in the following PostgreSQL query and provide the correct SQL statement."

[0095] Use the closed-source model to extract the corrected SQL statement: 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 check and function verification: To ensure the quality of the corrected SQL statement, use the PostgreSQL parser for syntax checking. In addition, execute the corrected SQL statement in an isolated test environment to verify its functional correctness. This step helps filter out the corrected results that may still have problems.

[0097] Manual review mechanism: Although the correction ability of the closed-source model is usually very strong, to further ensure data quality, a lightweight manual review mechanism is established. This includes sampling and reviewing a small part of the randomly selected corrected results, as well as expert review for particularly complex or critical cases.

[0098] Store the corrected results: Finally, store the original incorrect SQL, the corrected SQL, and relevant metadata in a dedicated database. The basic structure of each record is as follows:

[0099]

[0100] For example, assume that an incorrect SQL query has been generated and verified by an open-source large model. For example, the original natural language question is: "Please list the names and hire dates of all employees over 30 years old." The open-source large model generated the following incorrect SQL statement:

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

[0102] There is an obvious error here: hire_data should be hire_date.

[0103] Input information construction

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

[0105] Original natural language query: "Please list the names and hire dates of all employees over 30 years old."

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

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

[0108] Relevant database structure information: table name employees, fields include name, age, and hire_date.

[0109] Clear instruction: "Please correct the error in the following PostgreSQL query and provide the correct SQL statement."

[0110] Using the closed-source model to extract the corrected SQL statement

[0111] Provide the above input information as context to the closed-source large model. After processing this information, the closed-source large model will try to understand the problem and generate the corrected SQL statement. In this example, the expected output might be:

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

[0113] Syntax checking and function verification

[0114] To ensure the quality of the corrected SQL statement, the following steps are required:

[0115] Syntax checking: Use the parser of PostgreSQL to perform syntax checking on the corrected SQL statement to confirm its syntactic correctness.

[0116] Function verification: Execute the corrected SQL statement in an isolated test environment to verify whether it can correctly return the expected results. If the SQL statement is successfully executed and the correct data set is returned, the correction is considered valid.

[0117] Manual review mechanism

[0118] Although the correction ability of closed-source models is usually very strong, to further ensure data quality, a small sample of randomly selected correction results can be audited, and experts can review particularly complex or critical cases. For example, in this case, it can be confirmed through manual review that the corrected SQL statement actually solves the original problem.

[0119] Store the correction results

[0120] Finally, store the original incorrect SQL, the corrected SQL, and the relevant metadata in a dedicated database. The basic structure of each record is as follows:

[0121]

[0122] Furthermore, to ensure that the incorrect SQL generated by the open-source model can be effectively corrected, a closed-source large model with superior performance and good performance in the fields of natural language processing (NLP) and structured query language (SQL) generation needs to be selected. For example, models such as GLM-4-0520 can be selected. To improve the accuracy and efficiency of the correction task, it is necessary to configure the model appropriately. This includes, but is not limited to, adjusting the temperature parameter (such as setting it to 0.3) to increase the certainty and consistency of the output; and adjusting other hyperparameters according to specific requirements, such as the maximum sequence length, batch size, etc.

[0123] To help the closed-source large model better understand where the error is and generate the correct correction results, detailed context information needs to be provided. This information usually includes the original natural language query, the incorrect SQL statement, specific error information (such as syntax or logical errors), relevant database structure information (table names, column names, data types, etc.), and clear instruction requirements (such as specifying the target database type to be corrected). To facilitate the model's understanding and processing, all provided input information should follow a certain standardized format. For example, all SQL keywords remain in English unchanged, specific field names need to be translated or converted according to the requirements of the target database, and at the same time, the consistency of data elements such as numerical values and dates is ensured.

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

[0125] Use the parser of the target database (such as the parser of PostgreSQL) to perform a syntax check on the corrected SQL statement to ensure that it conforms to the SQL syntax specification. Execute the corrected SQL statement in an isolated test environment to verify whether it can be executed correctly and return the expected results. This step helps to filter out those correction results that, although syntactically correct, still have logical problems.

[0126] Although the closed-source large model has high accuracy, it is still necessary to conduct manual review on a randomly selected part of the correction results. This can further ensure the data quality. For particularly complex or critical cases, it is very necessary to invite domain experts for review. The experts can judge whether the correction results are reasonable based on their experience and give improvement suggestions.

[0127] Store the original incorrect SQL, the corrected SQL, and the relevant metadata (such as unique identifiers, error messages, verification results, etc.) into a specially designed database. This not only helps with subsequent analysis and improvement but also provides valuable data resources for continuously enhancing the model performance. Throughout the process, it is necessary to strictly comply with the regulations on data security and privacy protection to ensure that all the information involved is properly handled.

[0128] Associate the error data and the correction data with the query requirements to form training samples.

[0129] Specifically, first, it is necessary to extract the original incorrect SQL (i.e., the SQL statement generated by the open-source large model but failed in verification) and the corresponding corrected SQL (obtained by correcting with the closed-source large model) from the specially designed database. At the same time, it is also necessary to obtain the original natural language query for each case, and these information together constitute the basis of the training samples.

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

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

[0132] Database structure information: including detailed information such as table names, column names, data types, etc., to ensure consistency in context.

[0133] Incorrect SQL (negative samples): SQL statements generated by open-source large models but failed in verification.

[0134] Corrected correct SQL (positive samples): Correct SQL statements obtained after being corrected by closed-source large models.

[0135] Database type identifier: such as PostgreSQL, indicating the type of the target database.

[0136] In the process of constructing training samples, the following aspects need to be noted:

[0137] 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.

[0138] Format unification: Convert all samples into a consistent JSON format for subsequent batch processing operations and model processing. For example:

[0139]

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

[0141] The last step is to divide the constructed dataset into a training set, a validation set, and a test set according to a certain ratio (usually 8:1:1). The purpose of doing this is to ensure that each subset contains samples of various types and difficulty levels, so as to comprehensively evaluate the performance of the model. Specifically:

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

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

[0144] Test set: Used to finally evaluate the generalization ability and actual performance of the model.

[0145] For example, assume the following information has been extracted from a dedicated database:

[0146] Original natural language query: "Please list the names and hire dates of all employees over 30 years old."

[0147] Database structure information: Contains a table named employees with fields such as name (name), age (age), hire_date (hire date), etc.

[0148] Target database type: PostgreSQL

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

[0150] Corrected correct SQL statement (positive sample): SELECT name,hire_date FROM employeesWHERE age>30;

[0151] Next, associate the above information to form a complete training sample. Each sample should include the following parts:

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

[0153] Database structure information: Provide the context to ensure the model understands the query background.

[0154] Incorrect SQL statement (negative sample): The SQL statement generated by the open-source large model but failed the validation.

[0155] Corrected correct SQL statement (positive sample): The correct SQL statement obtained after being corrected by the closed-source large model.

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

[0157] Sample construction

[0158] Based on the above information, construct a specific training sample as follows:

[0159]

[0160] Once multiple such samples are constructed, they can be aggregated into a large dataset and divided into a training set, a validation set, and a test set according to a certain ratio. For example, it can be allocated in a ratio of 8:1:1 to ensure that each subset contains samples of various types and difficulty levels.

[0161] Training set: The main dataset used for model training, helping the model learn how to convert from incorrect SQL to correct SQL.

[0162] Validation set: Used to adjust hyperparameters and monitor the model's performance during training to avoid overfitting.

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

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

[0165] For each incorrect data and its corresponding corrected data, it needs to be associated with the original natural language query. Ensure that each training sample contains complete context information, that is, what was the user's initial question, what incorrect SQL was generated by the model, and how to correct the error. During the association process, the consistency of the data must be ensured. For example, all SQL keywords remain in English unchanged, specific field names need to be translated or converted according to the requirements of the target database, and at the same time, the consistency of data elements such as numerical values and dates is ensured.

[0166] When constructing training samples, any duplicate samples need to be removed first. This step helps to avoid overfitting of the model to a certain type of error and ensures that each sample is unique. For the convenience of subsequent data processing and model training, all samples should be converted to a consistent format, such as JSON format. This not only facilitates data management and transmission but also makes automated processing easier.

[0167] Add necessary metadata labels to each training sample, such as complexity classification (easy, medium, difficult), main error type (such as syntax error, logical error, etc.). These labels can help analyze the performance of the model more precisely and guide further improvement work. Evaluate the complexity of each sample according to the complexity of the SQL query (for example, whether it contains subqueries, join operations, aggregate functions, etc.) and mark it accordingly.

[0168] Divide the constructed dataset into a training set, a validation set, and a test set according to a certain ratio (usually recommended 8:1:1). This division method ensures that the model has enough data for training, and at the same time, leaves an independent dataset for validating the model performance and finally testing the generalization ability of the model. When splitting the dataset, attention should be paid to keeping the complexity distribution as uniform as possible among the subsets to ensure that samples of different difficulty levels are well represented in each subset.

[0169] Adopt the direct preference optimization strategy and use the training samples to perform supervised fine-tuning on the open-source large model.

[0170] Specifically, the direct preference optimization (DPO) strategy

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

[0172] Optimization objective function

[0173] The objective function design of DPO aims 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:

[0174]

[0175] f θ is the policy (i.e., generation probability) of the target model.

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

[0177] y + is the preferred choice (i.e., the corrected correct SQL statement).

[0178] y - is the non-preferred choice (i.e., the original incorrect SQL statement).

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

[0180] Training process

[0181] Construct model input: For each training sample, use the correct SQL as the preferred choice and the incorrect SQL as the non-preferred choice. When constructing the model input, it is necessary to include content such as instructions, natural language queries, and database structure information.

[0182] Calculate generation probabilities: Use the target model and the reference model to calculate the generation probabilities of the preferred choice and the non-preferred choice respectively. This step involves providing the input to the model and obtaining the scoring or probability estimation of the model for different outputs.

[0183] Loss calculation and parameter update: Calculate the loss value according to the DPO objective function and update the model parameters through the backpropagation algorithm. This helps to adjust the model weights so that the model is more likely to generate preferred choices rather than non-preferred choices in the future.

[0184] Training configuration

[0185] Learning rate setting: It is usually recommended to use a relatively small learning rate (such as 1e-5 to 5e-5) to avoid excessive deviation from the original model's capabilities.

[0186] Gradient Accumulation Technique: Allows the use of larger batch sizes for effective training even under limited GPU memory conditions.

[0187] Early Stopping Strategy: Stops training when the performance on the validation set no longer improves, preventing overfitting.

[0188] Implementation Details

[0189] Regular Evaluation: Regularly evaluate the model performance on the validation set during training, with particular attention to the accuracy of SQL generation and consistency with database specifications.

[0190] Dynamic Hyperparameter Tuning: Dynamically adjust the learning rate, α value, and other hyperparameters based on the validation results to find the optimal training configuration.

[0191] For example, assume a series of high-quality training samples have been constructed. Each sample includes:

[0192] Natural Language Query: For example, "Please list the names and hire dates of all employees over 30 years old."

[0193] Database Structure Information: Such as the table employees contains fields name (name), age (age), hire_date (hire date), etc.

[0194] Incorrect SQL Statement (Negative Sample): SELECT name,hire_data FROM employees WHERE age>30;

[0195] Corrected Correct SQL Statement (Positive Sample): SELECT name,hire_date FROM employeesWHERE age>30;

[0196] These samples have been converted to a unified JSON format and divided into training set, validation set, and test set in a ratio of 8:1:1.

[0197] Constructing Model Input

[0198] For each training sample, construct the model input as follows:

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

[0200] Natural Language Query: The original query description extracted from the dataset.

[0201] Database Structure Information: Includes details such as table names, column names, data types, etc.

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

[0203]

[0204] Calculate the generation probability using the target model and the reference model

[0205] For each training sample, calculate the generation probabilities of the preferred choice (i.e., the corrected correct SQL statement) and the non-preferred choice (i.e., the original incorrect SQL statement) using the target model (the open-source large model being fine-tuned) and the reference model (which can be the initial version without fine-tuning or another model with better performance) respectively.

[0206] Calculate the loss according to the DPO objective function

[0207] Calculate the loss value according to the objective function of direct preference optimization (DPO). Specifically, compare 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, and adjust the model parameters accordingly. For example, the objective function may be:

[0208]

[0209] f θ is the policy of the target model (i.e., the generation probability).

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

[0211] y + is the preferred choice (i.e., the corrected correct SQL statement).

[0212] y - is the non-preferred choice (i.e., the original incorrect SQL statement).

[0213] Update the model parameters by backpropagation

[0214] Based on the calculated loss value, update the model parameters through the backpropagation algorithm. This step aims to make the model more likely to generate preferred choices rather than non-preferred choices in the future, thereby gradually improving its accuracy.

[0215] Example of training configuration

[0216] Learning rate setting: Use a smaller learning rate (e.g., 1e-5) to avoid deviating too much from the capabilities of the original model.

[0217] Gradient accumulation technique: Allows the use of a larger batch size, enabling effective training even under limited GPU memory conditions.

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

[0219] Implementation Details

[0220] Regular Evaluation: Regularly evaluate the model performance on the validation set during the training process, with particular attention to the accuracy of SQL generation and consistency with database specifications.

[0221] Dynamic Hyperparameter Adjustment: Dynamically adjust the learning rate, α value, and other hyperparameters based on the validation results to find the optimal training configuration.

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

[0223] Select appropriate target and reference models. The target model refers to the open-source large model being fine-tuned, and the reference model can be the initial version without fine-tuning or another model with good performance. Use the two models to calculate the generation probabilities of preferred choices (corrected correct SQL) and non-preferred choices (original incorrect SQL) respectively. By providing the constructed input to the model, obtain the scoring or probability estimation of the model for different output options. This step involves complex internal calculation processes and finally generates scores for each option.

[0224] According to an embodiment of the present application, after performing supervised fine-tuning on the open-source large model using the training samples by adopting the direct preference optimization strategy, it further includes:

[0225] Evaluate the fine-tuned open-source large model, specifically:

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

[0227] Use an independent test set to evaluate the generalization ability of the model;

[0228] Conduct tests in the actual user environment, perform error analysis, and collect user feedback to identify the deficiencies of the fine-tuned open-source large model in specific scenarios or SQL types;

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

[0230] Specifically, set evaluation metrics

[0231] Accuracy: Measure whether the generated SQL query is correct, that is, whether it accurately reflects the user's natural language query intention and has no errors in grammar and logic.

[0232] F1-score: The F1-score is the harmonic mean of precision and recall, used to comprehensively evaluate the performance of the model. It is particularly suitable for the case of imbalanced datasets and can provide an evaluation criterion that balances precision and recall.

[0233] Query execution success rate: Check 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 grammar but also ensures the effectiveness of the query logic.

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

[0235] Evaluate the generalization ability of the model using an independent test set

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

[0237] Diversity: The test set contains various types of SQL queries, covering different complexity levels (such as simple, medium, difficult) to comprehensively test the model's ability to handle tasks of different difficulties.

[0238] Unseen data: The samples in the test set should be data that the model has never encountered before, so as to more realistically reflect the model's performance in actual applications.

[0239] Test in the actual user environment

[0240] Error analysis: By running the model in the actual user environment, collect and analyze the errors or deficiencies it generates. For example, certain specific types of queries may always result in errors, or the model's performance may be less than expected in certain scenarios.

[0241] Collect user feedback: Invite real users to try out the model and collect their feedback. Users can provide valuable insights from the perspective of actual use, helping to identify areas where the model needs improvement.

[0242] Identify deficiencies: Based on error analysis and user feedback, determine the deficiencies of the model in specific scenarios or SQL types. For example, it is found that the model often makes mistakes when processing queries involving multiple table joins, or has problems understanding certain industry-specific terms.

[0243] Develop an iterative improvement plan

[0244] Based on the evaluation results, develop a specific iterative improvement plan, which may include the following aspects:

[0245] Expand the training dataset: If it is found that the model performs poorly in certain specific domains or types of queries, the model's capabilities can be enhanced by adding training samples in the corresponding domains.

[0246] 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 the model performance.

[0247] Targeted fine-tuning: Perform more targeted fine-tuning on the model for the specific problems identified. For example, for those types of queries that frequently occur in actual applications but are not well handled by the model, specifically design new training samples for intensive training.

[0248] According to an embodiment of the present application, the specific method for obtaining the dataset of the adaptation database is as follows:

[0249] Extract the dataset of the selected basic data source;

[0250] Use a closed-source large language model to translate the extracted content into Chinese, keeping the SQL keywords in English, the structure, format, numerical values, and dates of the SQL statements unchanged;

[0251] Use a closed-source large language model to correct the translated SQL for creating tables into the SQL database format and generate query SQL that conforms to SQL syntax;

[0252] Perform consistency check, SQL validity verification, and SQL compatibility test;

[0253] Divide the processed dataset into a training set and a test set according to the original ratio, maintaining the original complexity distribution.

[0254] Specifically, extract the dataset of the selected basic data source

[0255] Select the basic data source: First, it is necessary to determine one or more high-quality basic data sources as the starting point. These data sources usually contain a large number of natural language queries and their corresponding SQL statements, table structure information, etc.

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

[0257] Natural language questions (query requests made by users)

[0258] Table structure information (such as table name, column name, data type, etc.)

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

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

[0261] Use a closed-source large language model to translate the extracted content into Chinese

[0262] Keep key elements unchanged: During the process of translating the content, the following points must be ensured:

[0263] Keep SQL keywords in English: Keywords such as SELECT, FROM, WHERE, etc. are not translated to ensure the correctness of SQL syntax.

[0264] Keep the SQL statement structure and format consistent: Ensure that the translated SQL statement has the same structure and format as the original version without affecting its execution.

[0265] Keep numerical values and dates as they are: Any numerical value or date information should not be changed to ensure data consistency and accuracy.

[0266] Use a closed-source large language model to correct the translated table creation SQL to the SQL database format and generate query SQL that conforms to SQL syntax

[0267] Correct the table creation SQL: For SQL statements that may not fully meet the requirements of the target database (e.g., PostgreSQL) after translation, use a closed-source large language model to correct them. This step ensures that the table creation SQL statement can correctly create the required database table structure.

[0268] Generate query SQL: Based on the translated natural language query and the corrected table creation SQL, use a closed-source large language model to generate query SQL that conforms to SQL syntax specifications. This process aims to improve the quality of the SQL statement and make it more accurately reflect the user's query intent.

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

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

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

[0272] SQL Compatibility Testing: Conduct additional compatibility testing for different types of databases (such as MySQL, PostgreSQL, etc.) to ensure that the generated SQL statements can run smoothly in different database systems.

[0273] Divide the processed dataset into a training set and a test set according to the original ratio, maintaining the original complexity distribution.

[0274] Data Splitting: Divide the processed dataset into a training set, a validation set, and a test set according to a pre-set ratio (such as 8:1:1). This division helps ensure that the model has enough data for training, while leaving an independent dataset for validating the model's performance and finally evaluating the model's generalization ability.

[0275] Maintain Complexity Distribution: When splitting the data, pay attention to keeping the complexity distribution of the samples in each subset as close as possible to the ratio of the original dataset. This means that samples of different difficulty levels should be well represented in each subset, thus ensuring that the model can learn the ability to handle various types of problems.

[0276] According to an embodiment of the present application, validating the generated data and collecting the error data that fails the validation is specifically as follows:

[0277] Generate SQL queries using an open-source large model, with the input including: natural language questions, table structure information, and the target database type.

[0278] Verify the generated SQL statements through an SQL executor.

[0279] For the SQL statements that fail to execute, record their unique identifiers and specific error information.

[0280] Specifically, generate SQL queries using an open-source large model.

[0281] Input Preparation: To generate SQL queries, the following information needs to be provided to the open-source large model:

[0282] Natural Language Question: This is the query request put forward by the user, such as "Please list the names and hire dates of all employees over 30 years old."

[0283] Table Structure Information: Includes detailed information such as table names, column names, and data types. For example, the employees table contains fields name (name), age (age), and hire_date (hire date).

[0284] Target Database Type: Clearly indicate the type of database for which the SQL statement is designed, such as PostgreSQL.

[0285] Verify the generated SQL statements through the SQL executor

[0286] 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.

[0287] Schema creation: Execute the extracted SQL for creating tables to create the necessary schema and table structures in the corresponding database instance.

[0288] SQL executor design: Design an executor for verifying the generated SQL statements. This executor can execute SQL statements in an isolated environment and record the results of each execution.

[0289] Result recording: Whether the SQL is successfully executed or not, its results should be recorded in detail. For successful executions, record the returned data set; for failed executions, record the specific error information.

[0290] For SQL statements that fail to execute, record their unique identifiers and specific error information

[0291] Error detection and recording:

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

[0293] Specific error information: When an SQL statement fails to execute, the system will capture and record the specific error information. For example, "column 'hire_data' does not exist" indicates an attempt to access a non - existent column.

[0294] The recording format may be as follows:

[0295]

[0296] Error data collection

[0297] Error data collation: Once the error information is captured, it needs to be collated and archived together with the original natural - language question, the generated SQL statement, and any other associated information. The purpose of this is to provide comprehensive information support for subsequent analysis and correction.

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

[0299] According to an embodiment of the present application, the error data is corrected through a closed - source large model to obtain corrected data, specifically:

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

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

[0302] Provide the input information as context to the closed-source model and extract the corrected SQL statement;

[0303] Use the parser of PostgreSQL for syntax checking;

[0304] Randomly sample and review some of the corrected results, and conduct expert review for complex or critical cases;

[0305] Store the original incorrect SQL, the corrected SQL, and the relevant metadata in a dedicated database.

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

[0307] Selection of a high-performance closed-source large model: Select a closed-source large model with superior performance and good performance in natural language processing (NLP) and structured query language (SQL) generation, such as GLM-4-0520.

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

[0309] Construct input information containing the following elements

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

[0311] Original natural language query: That is, the user's initial query request, such as "Please list the names and hire dates of all employees over 30 years old."

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

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

[0314] Relevant database structure information: including detailed information such as table names, column names, data types, etc., to ensure consistency in context.

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

[0316] Provide the input information as context to the closed-source model and extract the corrected SQL statement

[0317] Context understanding and problem localization: Provide the above input information 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 reason for the error.

[0318] Generate correction suggestions: Based on the understanding of the problem, the closed-source large model attempts to generate the corrected SQL statement. For example, for the above example, the expected output might be SELECT name,hire_date FROM employees WHERE age>30;

[0319] Use the parser of PostgreSQL for syntax checking

[0320] Syntax checking: Use the parser of the target database (such as PostgreSQL) to perform syntax checking on the corrected SQL statement to confirm that it conforms to the SQL syntax specification. This process helps to filter out those correction results that seem correct logically but actually have syntax errors.

[0321] Sample audit some randomly selected correction results and conduct expert review for complex or critical cases

[0322] Quality control measures: Although the closed-source large model has high accuracy, it is still necessary to conduct manual review on a part of the randomly selected correction results. This can further ensure the quality of the data.

[0323] Expert review: For particularly complex or critical cases, it is very necessary to invite domain experts for review. Experts can judge whether the correction results are reasonable based on their experience and give improvement suggestions.

[0324] Store the original incorrect SQL, the corrected SQL, and relevant metadata in a dedicated database

[0325] Metadata record: Store the original incorrect SQL, the corrected SQL, and relevant metadata (such as unique identifier, original natural language query, error information, verification results, etc.) in a specially designed database. The basic structure of each record is as follows:

[0326]

[0327] Data security and privacy protection: Throughout the process, strict compliance with data security and privacy protection regulations is required to ensure that all information involved is properly handled.

[0328] According to an embodiment of the present application, the association of the error data and the corrected data with the query requirements to form training samples is specifically as follows:

[0329] Extract the original error SQL and the corresponding corrected SQL from the database;

[0330] Associate each pair of SQL statements with the original natural language query to form complete training samples;

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

[0332] Divide the constructed dataset into a training set, a validation set, and a test set in a ratio of 8:1:1.

[0333] Specifically, extract the original error SQL and the corresponding corrected SQL from the database

[0334] Data extraction: First, all relevant records need to be extracted from a specially designed database. These records include:

[0335] Original error SQL: SQL statements generated by an open-source large model but failed verification.

[0336] Corresponding corrected SQL: Correct SQL statements obtained after being corrected by a closed-source large model.

[0337] Associate each pair of SQL statements with the original natural language query to form complete training samples

[0338] Associate natural language queries: For each error data and its corresponding corrected data, it needs to be associated with the original natural language query. Ensure that each training sample contains complete context information, that is, what was the user's initial question, what incorrect SQL was generated by the model, and how to correct the error.

[0339] Example: Suppose the user's query request is "Please list the names and hire dates of all employees over 30 years old." The open-source large model generated the incorrect SQL SELECT name,hire_data FROM employees WHERE age>30;, while the corrected SQL by the closed-source large model is SELECT name,hire_date FROM employees WHERE age>30;. Then this sample should include these three elements.

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

[0341] Duplicate removal: When constructing training samples, first remove any duplicate samples. This step helps prevent the model from overfitting to a certain type of error and ensures that each sample is unique.

[0342] Format standardization: For easier subsequent data processing and model training, all samples should be converted to a consistent format, such as JSON format. This not only facilitates data management and transmission but also makes automated processing easier. For example:

[0343]

[0344] Add metadata tags to each sample

[0345] Metadata annotation: Add necessary metadata tags to each training sample, such as complexity classification (easy, medium, difficult), main error type (e.g., syntax error, logical error, etc.). These tags can help analyze the model's performance more precisely and guide further improvement work.

[0346] Complexity assessment: 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 mark it accordingly.

[0347] Error type marking: Clearly indicate the main error type in each sample to facilitate targeted improvement of the model's performance.

[0348] Divide the constructed dataset into a training set, a validation set, and a test set in the ratio of 8:1:1

[0349] Dataset splitting: Once multiple such samples are constructed, they can be aggregated into a large dataset and divided into a training set, a validation set, and a test set according to a certain ratio. The commonly recommended ratio is 8:1:1, as follows:

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

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

[0352] Test set: Used to finally evaluate the generalization ability and actual performance of the model.

[0353] Maintain complexity distribution: When splitting the dataset, attention should be paid to keeping the complexity distribution as uniform as possible among subsets to ensure that samples of different difficulty levels are well represented in each subset.

[0354] According to an embodiment of the present application, the supervised fine-tuning of the open-source large model is performed using the direct preference optimization strategy and the training samples, specifically as follows:

[0355] For each training sample, the correct SQL is used as the preferred choice and the incorrect SQL is used as the non-preferred choice;

[0356] Construct the model input, including instructions, natural language queries, and database structure information;

[0357] Use the target model and the reference model to calculate the generation probabilities of the preferred choice and the non-preferred choice respectively;

[0358] Calculate the loss according to the direct preference optimization objective function and update the model parameters through backpropagation.

[0359] Specifically, for each training sample, the correct SQL is used as the preferred choice and the incorrect SQL is used as the non-preferred choice

[0360] Define preference and non-preference: In each training sample, the correctly corrected SQL statement is regarded as the "preferred choice", while the incorrect SQL statement generated by the open-source large model but failed in verification is regarded as the "non-preferred choice". This setting helps the model learn how to shift from incorrect to correct SQL generation.

[0361] Construct the model input, including instructions, natural language queries, and database structure information

[0362] Instructions: Clearly tell the model that this is a task about SQL generation. For example, "Generate the correct SQL statement according to the provided natural language query and database structure information." Such instructions help guide the model to focus on the core requirements of the task.

[0363] Natural language query: The query request put forward by the user, such as "Please list the names and hire dates of all employees over 30 years old."

[0364] Database structure information: It includes detailed information such as table names, column names, data types, etc. For example, the employees table contains fields like name (first name), age (age), hire_date (hire date), etc. Example input may be as follows:

[0365]

[0366]

[0367] Calculate the generation probabilities of preferred and non-preferred choices using the target model and the reference model respectively

[0368] Target model and reference model: The target model refers to the open-source large model being fine-tuned, and the reference model can be the initial version without fine-tuning or another model with better performance. Calculate the generation probabilities of preferred choices (i.e., the corrected correct SQL) and non-preferred choices (i.e., the original incorrect SQL) through these two models respectively.

[0369] Generation probability calculation: Provide the constructed input to the model and obtain the scoring or probability estimation of the model for different output options. This step involves complex internal calculation processes and finally generates scores for each option. For example, the target model may give the following scores:

[0370] Preferred choice (correct SQL): 0.85

[0371] Non-preferred choice (incorrect SQL): 0.15

[0372] Calculate the loss according to the direct preference optimization objective function and update the model parameters through backpropagation

[0373] DPO objective function: The core of DPO lies in its unique loss function design, aiming to increase the probability of the model generating preferred outputs while reducing the probability of generating non-preferred outputs. The specific form is as follows:

[0374]

[0375] f θ is the policy of the target model (i.e., the generation probability).

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

[0377] y + is the preferred choice (i.e., the corrected correct SQL statement).

[0378] y - is the non-preferred choice (i.e., the original incorrect SQL statement).

[0379] Loss calculation and parameter update: Based on the loss value calculated from the above objective function, the model parameters are updated through the backpropagation algorithm. This step involves complex gradient calculations and weight adjustments to make the model more likely to generate preferred choices rather than non-preferred choices in the future. Specifically, by adjusting the weights in the model, the model is more likely to generate correct SQL statements rather than incorrect SQL statements when receiving similar inputs.

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

[0381] A computer-readable storage medium, on which a program is stored, and when the program is executed by a processor, it implements the steps in the NL2SQL large model training method based on error correction and preference optimization.

[0382] An electronic device includes a memory, a processor, and a program stored on the memory and executable on the processor. When the processor executes the program, it implements the steps in the NL2SQL large model training method based on error correction and preference optimization.

[0383] What is not described in this application can be implemented by adopting or referring to existing technologies.

[0384] Each embodiment in this specification is described in a progressive manner. For the same or similar parts among the embodiments, reference can be made to each other. Each embodiment focuses on the differences from other embodiments.

[0385] The above are only embodiments of the present application and are not used to limit the present application. For those skilled in the art, various changes and modifications can be made to the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application shall be included within the scope of the claims of the present application.

Claims

1. A NL2SQL large model training method based on error correction and preference optimization, characterized in that: include: Input the dataset of the adapted database into the open source big model, verify the generated data, and collect the erroneous data that failed the verification; Correcting the erroneous data by using a closed-source large model to obtain corrected data; Associating the erroneous data and the corrected data with the query requirement to form a training sample; A direct preference optimization strategy is adopted to perform supervised fine-tuning on the open source large model using the training samples.

2. The method according to claim 1, characterized in that After the direct preference optimization strategy is adopted and the training samples are used to perform supervised fine-tuning on the open source large model, the method further includes: The fine-tuned open source large model is evaluated as follows: Set evaluation metrics, including accuracy, F1 score, query execution success rate, and efficiency; Use an independent test set to evaluate the generalization ability of the model; Conduct tests in actual user environments, perform error analysis and collect user feedback to identify deficiencies of the fine-tuned open source big model in specific scenarios or SQL types; Develop an iterative improvement plan based on the evaluation results, including expanding the training dataset, adjusting the algorithm, or performing targeted fine-tuning.

3. The method according to claim 2, characterized in that The specific method for obtaining the data set of the adaptation database is: Extracting data sets from selected basic data sources; Use a closed-source large language model to translate the extracted content into Chinese, keeping the SQL keywords in English, the structure, format, value, and date of the SQL statements unchanged; Use the closed-source large language model to correct the translated table creation SQL into the SQL database format and generate query SQL that conforms to SQL syntax; Conduct consistency checks, SQL validity verification, and SQL compatibility testing; The processed data set is divided into a training set and a test set according to the original proportion, maintaining the original complexity distribution.

4. The method according to claim 1, characterized in that: The generated data is verified and the error data that fails the verification is collected, specifically: Generate SQL queries using open source big models, with inputs including natural language questions, table structure information, and target database type; Verify the generated SQL statements through the SQL executor; For SQL statements that fail to execute, record their unique identifiers and specific error messages.

5. The method according to claim 1, characterized in that The erroneous data is corrected by the closed source large model to obtain corrected data, specifically: Select and configure a closed-source large model; For each incorrect SQL sample, construct input information containing the following elements: original natural language query, incorrect SQL statement, error message, related database structure information and explicit instructions; Providing the input information as context to the closed source model to extract a modified SQL statement; Use PostgreSQL's parser for syntax checking; Conduct random audits of some of the revision results, as well as expert reviews of complex or critical cases; The original erroneous SQL, corrected SQL and related metadata are stored in a dedicated database.

6. The method according to claim 1, characterized in that The step of associating the erroneous data and the corrected data with the query requirement to form a training sample is specifically as follows: Extract the original error SQL and the corresponding correction SQL from the database; Associate each pair of SQL statements 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.

7. The method according to claim 1, characterized in that The direct preference optimization strategy is adopted to perform supervised fine-tuning on the open source large model using the training samples, specifically: For each training sample, the correct SQL is taken as the preferred choice and the wrong SQL is taken as the non-preferred choice; Construct model input, including instructions, natural language queries, and database structure information; The target model and the reference model are used to calculate the generation probabilities of the preferred and non-preferred choices, respectively; The loss is calculated based on the direct preference optimization objective function and the model parameters are updated via back-propagation.

8. A computer program product comprising instructions, which, when executed on a device, is characterized in that: The device executes the steps in the NL2SQL large model training method based on error correction and preference optimization as described in any one of claims 1 to 7.

9. A computer-readable storage medium having a program stored thereon, characterized in that: When the program is executed by a processor, the steps in the NL2SQL large model training method based on error correction and preference optimization as described in any one of claims 1 to 7 are implemented.

10. 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, the steps in the NL2SQL large model training method based on error correction and preference optimization as described in any one of claims 1 to 7 are implemented.

Images