SQL (Structured Query Language) statement generation method and device based on large language model, equipment and medium

Abstract

The invention discloses an SQL (Structured Query Language) statement generation method and device based on a large language model, equipment and a medium. The method comprises the following steps: obtaining a user query text; inputting the user query text into a pre-trained Text2SQL (Structured Query Language) model to obtain an SQL statement output by the Text2SQL model; the SQL statement is input into a pre-trained SQL2Text model, so that a target query text output by the SQL2Text model is obtained; calculating semantic similarity between the user query text and the target query text; and if the semantic similarity is greater than or equal to a preset target value, determining that the SQL statement is the SQL statement corresponding to the user query text. By means of the method, the problem that the accuracy rate of SQL statement generation is low is solved, and the accuracy rate and efficiency of SQL statement generation are improved.

Description

Technical Field

[0001] The present invention relates to the field of data processing technology, and in particular to a method, device, equipment and medium for generating SQL statements based on a large language model. Background Art

[0002] In enterprise data analysis applications, business intelligence (BI) report analysis has become an indispensable part of enterprise decision-making and management. In recent years, with the rapid development of artificial intelligence technology, especially the emergence of large-scale language models, natural language processing (NLP) technology has been widely used. Generating SQL statements (Structured Query Language) through natural language to achieve report analysis has significantly improved the convenience and efficiency of data query and analysis.

[0003] However, existing large models may guess, add or omit certain key field information when generating SQL statements, resulting in errors in the generated SQL statements. Therefore, how to improve the accuracy of generated SQL statements is an urgent problem to be solved. Summary of the invention

[0004] The embodiments of the present invention provide a method, apparatus, device and medium for generating SQL statements based on a large language model to solve the problem of low accuracy in generating SQL statements.

[0005] A method for generating SQL statements based on a large language model, comprising:

[0006] Get user query text;

[0007] Input the user query text into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model;

[0008] Inputting the SQL statement into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model;

[0009] Calculating the semantic similarity between the user query text and the target query text;

[0010] If the semantic similarity is greater than or equal to a preset target value, it is determined that the SQL statement is the SQL statement corresponding to the user query text.

[0011] In one embodiment, inputting the user query text into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model includes:

[0012] Preprocessing the user query text to obtain a standard user query text;

[0013] The standard user query text is input into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model.

[0014] In one embodiment, inputting the SQL statement into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model includes:

[0015] Verify the SQL statement to obtain a verification result;

[0016] If the verification result is that the verification is passed, the SQL statement that has passed the verification is input into a pre-trained SQL2Text model to obtain the target query text output by the SQL2Text model.

[0017] In one embodiment, the method further comprises:

[0018] If the verification result is that the verification fails, obtaining error information in the verification result;

[0019] Concatenate the error message with the standard user query text to obtain a target concatenated text;

[0020] The target concatenated text is re-input into the Text2SQL model until the SQL statement output by the Text2SQL model passes verification.

[0021] In one embodiment, verifying the SQL statement to obtain a verification result includes:

[0022] Performing syntax check on the SQL statement to obtain a syntax check result;

[0023] Performing semantic verification on the SQL statement to obtain a semantic verification result;

[0024] Performing a security check on the SQL statement to obtain a security check result;

[0025] A performance check is performed on the SQL statement to obtain a performance check result.

[0026] In one embodiment, the performing syntax check on the SQL statement to obtain a syntax check result includes:

[0027] Parsing the grammatical structure of the SQL statement;

[0028] The grammatical structure is compared with predefined SQL grammar rules to obtain the grammar verification result.

[0029] In one embodiment, the SQL2Text model is trained in the following manner:

[0030] Acquire a training sample data set and a test sample data set, wherein the training sample data set includes a plurality of training sample pairs, the test sample data set includes a plurality of test sample pairs, the training sample pairs include a training prompt field and a training response field, and the test sample pairs include a test prompt field and a test response field;

[0031] Based on the training prompt field and the training response field, training the SQL2Text model to be trained;

[0032] After the SQL2Text model to be trained is trained, the trained SQL2Text model is tested based on the test prompt field and the test response field to obtain a test result;

[0033] If the test result meets the expected goal, the trained SQL2Text model is determined to be a trained SQL2Text model;

[0034] If the test result does not meet the expected goal, the hidden layer parameters of the trained SQL2Text model are adjusted and / or the training sample data set is modified, and the trained SQL2Text model is retrained until the test result meets the expected goal.

[0035] A SQL statement generation device based on a large language model, comprising:

[0036] The acquisition module is used to obtain the user query text;

[0037] An input module, used for inputting the user query text into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model; inputting the SQL statement into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model;

[0038] A calculation module, used for calculating the semantic similarity between the user query text and the target query text;

[0039] A determination module is used to determine that the SQL statement is the SQL statement corresponding to the user query text if the semantic similarity is greater than or equal to a preset target value.

[0040] A computer device comprises a memory, a processor and a computer program stored in the memory and executable on the processor, wherein the processor implements the above-mentioned SQL statement generation method based on a large language model when executing the computer program.

[0041] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the method for generating SQL statements based on a large language model is implemented.

[0042] In a solution provided by the above-mentioned SQL statement generation method, device, equipment and medium based on a large language model, a user query text is obtained; the user query text is input into a pre-trained Text2SQL model to obtain a SQL statement output by the Text2SQL model; the SQL statement is input into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model; the semantic similarity between the user query text and the target query text is calculated; if the semantic similarity is greater than or equal to a preset target value, the SQL statement is determined to be a SQL statement corresponding to the user query text. In this embodiment, by comparing the semantic similarity between the user query text and the target query text, it is determined whether the generated SQL statement is accurate and accurately reflects the user's query intention, and when the semantic similarity is greater than or equal to the preset target value, the SQL statement is determined to be a SQL statement corresponding to the user query text, thereby improving the accuracy and efficiency of SQL statement generation, ensuring that the generated SQL statement not only conforms to the database syntax, but also conforms to the user's query intention, and further enables the user to obtain the required information from the database more conveniently and accurately. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0044] Figure 1 is a flow chart of SQL2Text model training in one embodiment of the present invention;

[0045] Figure 2 is a flow chart of a method for generating SQL statements based on a large language model in one embodiment of the present invention;

[0046] Figure 3 is another flow chart of a method for generating SQL statements based on a large language model in one embodiment of the present invention;

[0047] Figure 4 is another flow chart of a method for generating SQL statements based on a large language model in one embodiment of the present invention;

[0048] Figure 5 is a schematic diagram of a SQL statement generation device based on a large language model in one embodiment of the present invention;

[0049] Figure 6 is a schematic diagram of a computer device in one embodiment of the present invention. DETAILED DESCRIPTION

[0050] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0051] The SQL statement generation method based on the large language model provided in the embodiment of the present invention can be applied to multiple fields, including but not limited to the fields of real estate development, commercial operation, and building operation and maintenance. In addition, the SQL statement generation method based on the large language model of the present invention can also be applied to various systems, which may exist in various terminal devices, including but not limited to personal computers, laptops, smart phones, and tablet computers.

[0052] In order to facilitate understanding of the embodiments of the present invention, the SQL2Text model, Text2SQL model, Prompt preprocessing module, Embeding module, verification module, execution module and data display module involved in the present invention are explained as follows:

[0053] SQL2Text Model:

[0054] The SQL2Text model is a model that converts structured query language (SQL) into natural language (Text). The main task of the SQL2Text model is to convert SQL statements in database queries into natural language descriptions that can be understood by humans. Its essence is to extract key information by analyzing the structure and semantics of SQL statements, and to restate this information in natural language. Specifically, the SQL2Text model in the present invention can be obtained by training and fine-tuning on the basis of the base large model, or by training and fine-tuning other large models. As long as the large model supports fine-tuning training and transfer learning, the specific present invention does not limit it. Among them, the base large model refers to a machine learning model with huge parameters and complex network architecture, which can handle a variety of different tasks, support fine-tuning training, and can be migrated to other task fields similar to the target task field for use. Large models, such as neural network models with billions to hundreds of billions of parameters.

[0055] Among them, Figure 1 As shown, the SQL2Text model can be trained through the following steps:

[0056] S1. Obtain a training sample data set and a test sample data set. The training sample data set includes multiple training sample pairs, and the test sample data set includes multiple test sample pairs. The training sample pairs include a training prompt field and a training response field, and the test sample pairs include a test prompt field and a test response field.

[0057] It can be understood that the training sample data set may include but is not limited to thousands, tens of thousands or hundreds of thousands of training sample pairs, and the specific present invention is not limited thereto. Among them, the training sample pair (for example, QA pair) includes a training prompt field (for example, Prompt field) and a training response field (for example, Response field). Specifically, the training prompt field may include task instructions, SQL statements, and metadata of the data table, and the training response field includes the user query text corresponding to the SQL statement in the training prompt field. Among them, task instructions: are used to describe the specific tasks input to the SQL2Text model for execution. For example, please generate user query text based on the SQL statement. SQL statement: is an SQL statement that complies with the SQL2000 standard. For example, "SELECTamount FROM sale_table WHERE project='A project' AND year_month='2021-01'". Metadata of the data table: is metadata information that defines the table structure, including the name of the data table in the database, the field name of each column, data type, value range, unit, constraint, field usage description, etc. It should be noted that the above is only an example and the specific present invention is not limited thereto. The test sample data set may also include but not limited to thousands, tens of thousands or hundreds of thousands of test sample pairs. The test sample pair includes a test prompt field and a test response field. The test prompt field and the test response field in the test sample data set may be the same as or different from the training prompt field and the training response field in the training sample data set, and the specific present invention is not limited thereto.

[0058] S2. Based on the training prompt field and the training response field, the SQL2Text model to be trained is trained.

[0059] It can be understood that after obtaining the training sample set, the SQL2Text model to be trained is trained and fine-tuned through the training prompt field and the training response field in the training sample set. The fine-tuning process enables the SQL2Text model to be trained to learn how to extract key information from the test prompt field (for example, extract key information from the SQL statement) and convert it into an accurate and fluent natural language description, so that the SQL2Text model has the ability to generate natural language through SQL statements. Among them, the fine-tuning method can adopt full fine-tuning or incremental fine-tuning technology. Specifically, the incremental fine-tuning technology can include but is not limited to incremental fine-tuning technologies such as LoRA and Prompt Tuning.

[0060] S3. After the SQL2Text model to be trained is trained, the trained SQL2Text model is tested based on the test prompt field and the test response field to obtain a test result.

[0061] It can be understood that after the SQL2Text model to be trained is trained, the trained SQL2Text model is tested based on the test prompt field and the test response field in the test sample data set, for example, the trained SQL2Text model is tested based on the task instructions, SQL statements and metadata of the data table in the test prompt field to obtain the output generated natural language description. Subsequently, these generated natural language descriptions are compared with the standard descriptions (for example, user query texts) in the test sample data set to obtain test results. For example, the test results can be obtained by calculating the similarity or accuracy index between the generated natural language description and the standard description to evaluate the accuracy of the trained SQL2Text model, which is not limited in the specific present invention.

[0062] S4. If the test result meets the expected goal, the trained SQL2Text model is determined to be a trained SQL2Text model;

[0063] S5. If the test result does not meet the expected goal, adjust the hidden layer parameters of the trained SQL2Text model and / or modify the training sample data set, and re-train the trained SQL2Text model until the test result meets the expected goal.

[0064] It can be understood that after the test is completed, the trained SQL2Text model is judged whether it meets the expectations by comparing the test results with the expected goals, ensuring that the obtained SQL2Text model has high performance and stability, and can provide users with high-quality SQL statement to natural language description conversion services in practical applications. If the test results show that the SQL2Text model has achieved the expected goals in key indicators, such as accuracy, recall rate and F1 score, the trained SQL2Text model is determined to be a trained model. If the test results show that the performance of the SQL2Text model does not meet the expected goals, the hidden layer parameters of the trained SQL2Text model are adjusted, such as changing the learning rate, adjusting the number of hidden layer neurons, changing the activation function, etc., to optimize the performance of the SQL2Text model. In addition, if the performance of the SQL2Text model after adjusting the parameters is still not significantly improved, the training sample data set is modified, for example, the number of samples is increased, the sample quality is improved, or sample data that is more in line with the actual application scenario is collected again. After completing the parameter adjustment or data set modification, the trained SQL2Text model is retrained and tested again using the test sample data set. This process will be repeated until the test results meet the expected goals. Through the above method, the SQL2Text model learns the semantic mapping relationship between the metadata of the database table, the SQL statement and the user query text, thereby supporting the conversion of the SQL statement into the user query text in natural language form, and further making the SQL2Text model high-performance and stable, and able to provide users with high-quality SQL statement to natural language description conversion services in practical applications.

[0065] For example, examples of each sample of the training sample data set and the test sample data set of the SQL2Text model are as follows:

[0066] {"prompt":

[0067] "Task instruction: Please generate user query text based on SQL statement.

[0068] SQL statement: SELECT amount FROM sale_table WHERE project = 'Project A' AND year_month = '2021-01'

[0069] Metadata of the data table:

[0070] CREATE TABLE sale_table(

[0071] year_month VARCHAR(100)COMMENT'Time dimension: data format: yyyy-mm',

[0072] project VARCHAR(90)NOT NULL COMMENT'Organization dimension: project name',

[0073] amount DECIMAL(24,6)NOT NULL COMMENT'Sales amount, unit: 10,000 yuan',

[0074] )COMMENT 'Sales data table'

[0075] "

[0076] ,

[0077] "response": "User query text: Query the sales amount of project A in January 2021"

[0078] "}

[0079] Text2SQL Model:

[0080] The Text2SQL (also known as NL2SQL) model is a model that converts natural language (NL) problems in the database field into structured query language (SQL) that can be executed in a relational database. Its essence is to convert the user's natural language statements into standard semantic representations that can be understood and executed by computers. Specifically, the Text2SQL model in the present invention is also obtained by training and fine-tuning on the basis of the base large model. The specific training process is similar to the SQL2Text model training process, for example, as described in steps S1-S5, and will not be repeated here to avoid repetition. The difference lies in the difference between the training sample pairs and the test sample pairs in the training sample data set and the test sample data set. Specifically, the training prompt field (for example, the Prompt field) can include task instructions, user query text, metadata of the data table, and sample data of the data table, and the training response field (for example, the Response field) includes the SQL statement corresponding to the user query text in the training prompt field. Among them, task instructions: are used to describe the specific tasks input to the Text2SQL model for execution. For example: please generate an SQL statement that conforms to the SQL2000 standard based on the user's question. User query text: user questions described in the form of textual natural language, including Chinese, English, numbers, characters, etc. For example: What is the sales amount of Project A in January 2023? Metadata of data table: Metadata of data table is metadata information that defines the table structure, including: the name of the data table in the database, the field name of each column, data type, value range, value unit, value constraint, field usage description, etc. Sample data of data table: In order to help the Text2SQL model understand the structure and content of the data table, a small amount of sample data in the data table needs to be provided. The sample data may not be exactly the same as the real data of the data table, or it may be the same, as long as the expression method, value range, and data granularity of the two are basically the same, and the specific present invention is not limited. Among them, in order to ensure the quality of the training sample pairs of the training sample data set, it is necessary to ensure the correctness of the SQL statement. For example, the SQL statement can be verified manually or automatically by a tool to ensure that its syntax is correct, the semantics are clear, and it meets business requirements to avoid errors. Through the above method, the Text2SQL model learns the mapping relationship between the text semantics hidden in the user query text (Text), the metadata of the database table, and the SQL statement, thereby supporting the conversion of the user query text in natural language into the corresponding SQL statement. It should be noted that the above is only an example and the specific present invention is not limited thereto.

[0081] For example, examples of each sample of the training sample data set and the test sample data set of the Text2SQL model are as follows:

[0082]

[0083]

[0084] Prompt preprocessing module:

[0085] The prompt preprocessing module is part of the natural language processing (NLP) system. It preprocesses the input user query text data by designing specific prompts (prompts), thereby optimizing the output of the model so that it can accurately identify user intent and correctly generate SQL statements. For example, before submitting the user query text (e.g., user question text) to the Text2SQL model, the user query text input by the user is preprocessed by the prompt preprocessing module to obtain a semantically complete, standard and normative standard user query text.

[0086] Embedding module:

[0087] The Embedding module (also called the word embedding module) is a technology used to represent text data. It maps high-dimensional data (such as text, pictures, videos, etc.) to a low-dimensional space, thereby representing the input text as a point in a continuous numerical space, and finally obtaining an Embedding vector. Among them, the Embedding vector can accurately represent the intrinsic semantics of the natural language text. The Embedding vectors with similar geometric distances have similar meanings for the corresponding natural language texts. Among them, the Embedding module can be established by the Word2Vec, GloVe or FastText algorithm, and is used to calculate the geometric distance between the Embedding vectors corresponding to two text phrases or sentences to determine the semantic similarity between the two text phrases or sentences.

[0088] Verification module:

[0089] Mainly used to verify the correctness, security and performance of SQL statements.

[0090] Execution module:

[0091] It is mainly used to establish the connection between the natural language processing system and the database, connection pool management, and SQL script execution.

[0092] Data display module:

[0093] It is mainly used to receive query result data transmitted by the execution module, parse the query results, extract the data set required for chart display, and provide a variety of chart types for users to choose from, such as bar charts, line charts, pie charts, scatter charts, and area charts, etc., to display corresponding charts according to the characteristics of the data and user needs.

[0094] The following describes in detail the SQL statement generation method based on the large language model provided by the present invention through various embodiments.

[0095] In one embodiment, if Figure 2 As shown, a method for generating SQL statements based on a large language model is provided, comprising the following steps:

[0096] S10: Obtain user query text.

[0097] It can be understood that the user query text can be a user question text, for example, "What is the sales amount of Project A in January 2023?", and the user query text is used to input into the Text2SQL model to generate an SQL statement corresponding to the user query text.

[0098] S20. Input the user query text into a pre-trained Text2SQL model to obtain the SQL statement output by the Text2SQL model.

[0099] It can be understood that after obtaining the user query text, the obtained user query text is input into the above-mentioned pre-trained Text2SQL model to obtain the SQL statement output by the Text2SQL model. The purpose is to allow users to ask queries in natural language without directly writing complex SQL statements, thereby making database queries more intuitive and convenient. Figure 3 As shown, the user query text is input into the pre-trained Text2SQL model to obtain the SQL statement output by the Text2SQL model, which specifically includes the following steps:

[0100] S21. Preprocess the user query text to obtain a standard user query text.

[0101] It can be understood that in order for the Text2SQL model to accurately recognize the user's intention and accurately generate SQL statements, before inputting the user's query text, that is, the user's question text, into the Text2SQL model, it is necessary to preprocess the user's query text through the Prompt preprocessing module. For example, the user's input query text can be preprocessed based on Natural Language Processing (NLP) technology to obtain a standard user query text with complete semantics and standard specifications, so as to eliminate the noise and redundant information in the user's query text and improve the accuracy and efficiency of subsequent processing.

[0102] The specific preprocessing process is as follows:

[0103] Split the user's input query text into individual words or terms so that the Text2SQL model can understand and process the meaning of each word one by one;

[0104] Furthermore, filter or screen the split words or terms. For example, filter the words that frequently appear in the user's query text but have little effect on information retrieval and text semantic processing (i.e., stop words), such as "de", "shi", "zai", etc., to save storage space and improve the efficiency of search and processing.

[0105] Then, according to business requirements and industry knowledge, establish a knowledge base of standard vocabulary explanations and industry term definitions. Through semantic search in the knowledge base, replace some colloquial words with standardized terms. For example, replace "Please query how the houses in Project A are selling?" with "Please query the sales amount of Project A". Another example is to replace "Please query how many customers have visited Project A?" with "Please query the number of visitors to Project A", etc.

[0106] Finally, collect the user's common questions and colloquial expression habits, establish an abbreviation mapping table, and map the commonly used abbreviations in the business field to complete text descriptions. For example, replace "Project A" with "XX Company's Project A". Through the above processing process, the user's query text is transformed into a standard user query text, improving the accuracy and efficiency of subsequent processing.

[0107] It should be noted that the above is only an example, and the specific invention is not limited.

[0108] S22. Input the standard user query text into the pre-trained Text2SQL model to obtain the SQL statement output by the Text2SQL model.

[0109] It can be understood that after obtaining the standard user query text, the standard user query text is passed as input data to the Text2SQL model. The reasoning or prediction function of the Text2SQL model is called to perform a complex calculation process, including text encoding, semantic understanding, SQL structure generation and other steps, to generate SQL statements.

[0110] For example, suppose the user query text entered by the user is "I want to find the information of all employees in 'Beijing' whose salary exceeds 10,000 yuan." In order to convert this user query text into a format that the Text2SQL model can understand and output the corresponding SQL statement, the following steps are required:

[0111] First, the text is cleaned to remove redundant information, such as punctuation and unnecessary spaces. Next, the text is segmented to split the text into individual words or phrases: "I", "want", "find", "all", "in", "Beijing", "and", "salary", "more than", "10,000 yuan", "of", "employee", "of", "information". Then, stop words are removed, that is, words that do not contribute much to the query intent, such as "I", "want", "of", etc. In this way, the core vocabulary obtained is: "find", "all", "in", "Beijing", "salary", "more than", "10,000 yuan", "employee", "information". Next, these words are recombined into a concise and clear query statement, that is, the standard user query text: "Find all employee information in Beijing whose salary exceeds 10,000 yuan." Finally, this standard user query text is input into the pre-trained Text2SQL model. The Text2SQL model generates a corresponding SQL statement by analyzing the semantic and grammatical information in the standard user query text: "SELECT * FROM employee table WHERE salary>10,000 AND location = 'Beijing'". Through the above preprocessing and application of the Text2SQL model, the conversion from natural language query to SQL statement is realized, and the efficiency and accuracy of the query are improved. It should be noted that the above is only an example, and the specific present invention is not limited thereto.

[0112] S30: Input the SQL statement into the pre-trained SQL2Text model to obtain the target query text output by the SQL2Text model.

[0113] It can be understood that after obtaining the SQL statement corresponding to the user query text output by the Text2SQL model, as in the above example, the obtained SQL statement is "SELECT * FROM employee table WHERE salary > 10000 AND location = 'Beijing'". Further, this SQL statement needs to be input into the pre-trained SQL2Text model. The SQL2Text model generates a corresponding natural language text (that is, the target query text) by parsing the structure and meaning of the SQL statement, such as: "Find employee information in Beijing with a salary of more than 10,000 yuan", the purpose of which is to compare it with the user query text in the future, so as to further improve the accuracy of the SQL statement corresponding to the query statement.

[0114] Among them, Figure 4 As shown, the SQL statement is input into the pre-trained SQL2Text model to obtain the target query text output by the SQL2Text model, which specifically includes the following steps:

[0115] S31. Verify the SQL statement and obtain the verification result.

[0116] It can be understood that the SQL statement obtained by the verification module is verified, and specifically the syntax, semantics, security and performance of the SQL can be verified to obtain a corresponding verification result, wherein the verification result may include a verification result of passing and a verification result of failing, and the specific present invention is not limited. After the SQL statement is verified, it is input into the SQL2Text model, which further improves the quality, security and performance of the SQL statement, and reduces the risks and problems caused by SQL statement errors. The specific verification process is described in steps S311-S314:

[0117] S311. Perform syntax check on the SQL statement to obtain syntax check result.

[0118] It can be understood that before performing syntax check on the SQL statement, it is necessary to first parse the SQL statement to obtain the syntax structure corresponding to the SQL statement, and further compare the syntax structure corresponding to the parsed SQL statement with the predefined SQL syntax rules to obtain the corresponding syntax check result. Specifically, the grammatical correctness of the SQL statement can be verified, including checking whether the use of keywords, identifiers, data types, operators, etc. conforms to the predefined SQL syntax rules to obtain the syntax check result. Among them, the syntax check result can include "syntax check passed" and "syntax check failed". When the syntax check result is "syntax check failed", the error log, error location, and error type recorded when the syntax error is detected are obtained. On the contrary, if all checks pass, the semantic check result of "syntax check passed" is returned.

[0119] S312: Perform semantic verification on the SQL statement to obtain a semantic verification result.

[0120] It can be understood that after the SQL statement is syntax-checked, it is further necessary to perform semantic verification on the SQL statement to obtain a semantic verification result. Specifically, the semantic correctness of the SQL statement can be verified, including checking whether the referenced tables, columns, functions, etc. exist, and whether the logic in the SQL statement is correct. For example, check whether the conditions of the WHERE clause are reasonable, whether the sorting fields in the ORDER BY clause are valid, etc. If any logical errors are found during the verification process, such as the column name does not exist or the wrong table name is used, a semantic verification result of "semantic verification failed" will be generated, and the location and type of the error will be clearly indicated. If all checks pass, the semantic verification result of "semantic verification passed" is returned, indicating that the SQL statement is logically correct.

[0121] For example, when the SQL statement is "SELECT amount FROM sale_table WHERE project = 'A project'", when performing semantic verification, first check whether the "sale_table" table exists and whether the "amount" column belongs to the sale_table table. Then, it will verify whether "product" is a valid column name and check whether there is such a column in the "sale_table" table. It should be noted that the above is only an example and the present invention is not limited thereto.

[0122] S313: Perform security check on the SQL statement to obtain a security check result.

[0123] It can be understood that after the SQL statement is syntax-checked and semantic-checked, it is further necessary to perform a security check on the SQL statement to obtain a security check result. Specifically, it can be verified whether there is a SQL injection risk in the SQL statement and whether the principle of least privilege is followed, etc., to ensure that the SQL statement only performs the required operations and does not perform redundant or sensitive operations. If a security risk is detected, a security check result of "security check failed" will be generated, and the location and type of the security vulnerability risk will be clearly indicated. If all checks pass, a security check result of "security check passed" will be returned.

[0124] Exemplarily, when the SQL statement is "SELECT*FROM users WHERE username='"+userInput+"'""; if userInput is not properly processed and escaped, it may contain malicious code, such as 'OR'1'='1, which will cause the query to return all user information instead of just the specified user, or detect potential risks of SQL injection, such as SQL special characters contained in the user input, a security verification result will be generated, and a security verification result of "security verification failed" will be generated. If the verification passes, a security verification result of "security verification passed" will be returned. It should be noted that the above is only an example and the present invention is not limited to it.

[0125] S314. Perform performance verification on the SQL statement to obtain a performance verification result.

[0126] It is understandable that after the syntax, semantics and security of the SQL statement are verified, the performance of the SQL statement needs to be further verified to obtain the performance verification result. Specifically, it can be verified whether the SQL statement lacks indexes, whether there are unnecessary full table scans and unreasonable connection operations, etc. If performance risks are detected, a performance verification result of "Performance verification failed" will be generated, and the location and type of performance risks will be clearly pointed out. If all checks pass, the performance verification result of "Performance verification passed" will be returned.

[0127] In summary, when the user query text input by the user is "query the sales amount of project A in January 2023", for example. When the natural language processing system receives the user query text, it first uses natural language processing (NLP) technology to preprocess it. The preprocessing process includes text segmentation, stop word filtering, term replacement and abbreviation mapping to generate a semantically complete and standardized standard user query text. After preprocessing, the user's user query text is standardized as "Please query the sales amount of project A of XX company in January 2023". Then, the standard user query text is input into the pre-trained Text2SQL model to generate an SQL statement. For example, the SQL statement generated by the Text2SQL model is "SELECT SUM(amount)FROM sales_table WHERE project='Project A'ANDdate='2023-01'". In order to ensure the accuracy of the generated SQL statement, it is further necessary to perform a comprehensive verification on the generated SQL statement. The verification process includes syntax verification, semantic verification, security verification and performance verification, etc., to obtain the verification result.

[0128] S32. If the verification result is passed, the SQL statement that has passed the verification is input into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model.

[0129] It can be understood that after the above SQL statement passes the verification, the verified SQL statement is input into the pre-trained SQL2Text model to obtain the corresponding target query text to ensure the accuracy and efficiency of the SQL statement. If the verification result is that the verification fails, steps S33-S35 are executed:

[0130] S33, obtaining error information in the verification result;

[0131] S34, concatenating the error message with the standard user query text to obtain a target concatenated text;

[0132] S35. Re-input the target concatenated text into the Text2SQL model until the SQL statement output by the Text2SQL model passes verification.

[0133] It can be understood that during the SQL verification process, if any error is found, the error information in the verification result will be obtained. Assume that in the above example, the format of the "date" field is found to be wrong during the syntax verification process, and the following error information is generated: the error type is "syntax error", the error location is in the "WHERE clause", and the error description is "The format of the 'date' field should be 'YYYY-MM'". Then the error information is spliced ​​with the standard user query text to generate the target spliced ​​text. The splicing process can be as follows:

[0134] First, the standard user query text "Please query the sales amount of XX Company A Project in January 2023" is spliced ​​with the error information to generate a target spliced ​​text, wherein the splicing process can be a random combination or other methods, which is not limited by the specific present invention. For example, the generated target spliced ​​text is: "Please query the sales amount of XX Company A Project in January 2023. Note: The format of the 'date' field should be 'YYYY-MM'." Then, the target spliced ​​text is input into the Text2SQL model again to regenerate the SQL statement. Since the natural language processing system has included the error information in the target spliced ​​text, the Text2SQL model will adjust the generation logic according to the information to avoid making the same mistake again. For example, when regenerating the SQL statement, the target spliced ​​text may generate the following corrected SQL statement: "SELECT SUM(amount)FROM sales_table WHERE project='A project'ANDdate_format(date,'%Y-%m')='2023-01'". Subsequently, the newly generated SQL statement is fully verified again to check its syntax, semantics, security, and performance. If the verification passes, the verified SQL statement is input into the pre-trained SQL2Text model to obtain the target query text output by the SQL2Text model. For example, the SQL2Text model may convert the generated SQL statement back into a query text in natural language form, "Please query the sales amount of Project A of Company XX in January 2023."

[0135] Through the above method, the natural language processing system can effectively improve the accuracy of SQL statements generated by large models, ensure the correctness and reliability of the generated SQL statements in all aspects, thereby avoiding analysis errors caused by SQL errors, reducing the adverse impact on business decisions, and providing accurate and reliable data support for enterprises. It should be noted that if the verification result is that the verification fails, and the number of cycles of inputting into the Text2SQL model again, regenerating SQL statements, and calling the verification module again exceeds the preset number of times, it indicates an abnormality. At this time, the user is prompted to re-enter the user query text to avoid consuming too many resources.

[0136] S40, calculating the semantic similarity between the user query text and the target query text;

[0137] S50, if the semantic similarity is greater than or equal to the preset target value, determining that the SQL statement is the SQL statement corresponding to the user query text;

[0138] S60. If the semantic similarity is less than the preset target value, steps S10-S40 are repeated until the semantic similarity is greater than or equal to the preset target value.

[0139] It can be understood that after obtaining the target query text, in order to verify whether the generated SQL statement accurately reflects the user's query intention, it is necessary to compare the obtained target query text with the user query text. This process is completed by calculating the semantic similarity between the user query text and the target query text through the Embedding module. For example, the user query text is "query the sales amount of project A in January 2023", and the generated SQL statement is "SELECT SUM(amount)FROMsales_table WHERE project='Project A'AND date_format(date,'%Y-%m')='2023-01'". The SQL2Text model converts the SQL statement back to the target query text "Please query the total sales amount of project A in January 2023". In order to determine the semantic similarity between the user query text and the target query text, it can be calculated as follows:

[0140] First, the user query text and the target query text are processed by the Embedding module respectively to generate two corresponding embedding vectors. For example, the user query text "query the sales amount of project A in January 2023" is mapped to vector A, and the target query text "please query the total sales amount of project A in January 2023" is mapped to vector B. Then, the geometric distance between vector A and vector B is calculated using methods such as cosine similarity or Euclidean distance. Among them, cosine similarity is an indicator to measure the directional similarity of two vectors. Its value ranges from -1 to 1. The closer the value is to 1, the more similar the two vectors are. For example, the calculated cosine similarity is 0.98, indicating that the semantics of the two texts are very close. Finally, the calculated semantic similarity value is compared with the preset target value. If the semantic similarity value is greater than the preset target value (for example, 0.95), it is considered that the generated SQL statement accurately reflects the user's query intention, and the query process ends. If the semantic similarity value is less than the preset target value, it is considered that the generated SQL statement fails to accurately reflect the user's query intention. At this time, the process will return to the SQL generation and verification steps, regenerate the SQL statement, and repeat the process of steps S10-S40 until the semantic similarity value is greater than or equal to the preset target value. This method can effectively avoid the problem of inaccurate query results caused by errors in the SQL statement generated by the model, and further improve the trust and satisfaction of enterprises when using data analysis and business intelligence tools.

[0141] It should be noted that in order to avoid anomalies that cause the above steps S10-S40 to be repeatedly cycled multiple times and consume too many system resources, a maximum number of times can be set. If the maximum number of times is exceeded, the user is prompted to re-enter the user query text, where the maximum number of times can be 30 times, 40 times or 50 times, which is not limited to the specific number in the present invention.

[0142] In summary, in a solution provided by the above-mentioned SQL statement generation method, device, equipment and medium based on a large language model, a user query text is obtained; the user query text is input into a pre-trained Text2SQL model to obtain a SQL statement output by the Text2SQL model; the SQL statement is input into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model; the semantic similarity between the user query text and the target query text is calculated; if the semantic similarity is greater than or equal to a preset target value, the SQL statement is determined to be a SQL statement corresponding to the user query text. In this embodiment, by comparing the semantic similarity between the user query text and the target query text, it is determined whether the generated SQL statement is accurate and accurately reflects the user's query intention, and when the semantic similarity is greater than or equal to the preset target value, the SQL statement is determined to be a SQL statement corresponding to the user query text, thereby improving the accuracy and efficiency of SQL statement generation, ensuring that the generated SQL statement not only conforms to the database syntax, but also conforms to the user's query intention, and further enables the user to obtain the required information from the database more conveniently and accurately.

[0143] In one embodiment, after step S50, that is, if the semantic similarity is greater than or equal to the preset target value, after determining that the SQL statement is the SQL statement corresponding to the user query text, the following steps are also included:

[0144] S70, inputting the SQL statement into the target database to obtain corresponding query result data;

[0145] S80, parsing the query result data to obtain a data set for chart display, wherein the data set includes data values, data labels, and classification information;

[0146] S90: Receive a chart type selected by a user, and display the data set based on the chart type, wherein the chart types include a bar chart, a line chart, a pie chart, a scatter chart, and an area chart.

[0147] It can be understood that after obtaining the SQL statement, the SQL statement is directly input into the target database, and the corresponding query result data is obtained by querying the execution module. For example, the query result data includes detailed information such as the name, sales, and sales time of the product, and is sent to the data display module. Among them, the target database is a database that stores relevant query data. Then, the query result data is parsed by the data display module, including extracting the product name as the data label, the sales as the data value, and the sales time as the classification information in the query result data, forming a data set specifically for chart display. In addition, the data display module also provides a variety of chart types for users to choose, such as bar charts, line charts, pie charts, scatter charts, and area charts. Then, according to the chart type selected by the user (for example, bar chart), the data display module displays the parsed data set. In the chart, the name of the product is clearly marked, the sales are represented by the height of the column, and the sales time is distinguished by different colors or groups. Through this chart, users can intuitively understand the sales of each product last year, providing strong data support for subsequent decision-making. Through the above method, it is avoided that the user cannot distinguish whether the SQL statement output by the model is correct or not, resulting in serious losses to the business decision-making of the enterprise due to erroneous analysis reports.

[0148] It should be understood that the order of execution of the steps in the above embodiment does not necessarily mean the order of execution. The execution order of each process should be determined by its function and internal logic, and should not constitute any limitation on the implementation process of the embodiment of the present invention.

[0149] In one embodiment, a device for generating SQL statements based on a large language model is provided, and the device for generating SQL statements based on a large language model corresponds one-to-one to the method for generating SQL statements based on a large language model in the above embodiment. Figure 5 As shown, the SQL statement generation device based on the large language model includes an acquisition module, an input module, a calculation module and a determination module. The detailed description of each functional module is as follows:

[0150] The acquisition module is used to obtain the user query text;

[0151] An input module, used for inputting the user query text into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model; inputting the SQL statement into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model;

[0152] A calculation module, used for calculating the semantic similarity between the user query text and the target query text;

[0153] A determination module is used to determine that the SQL statement is the SQL statement corresponding to the user query text if the semantic similarity is greater than or equal to a preset target value.

[0154] In one embodiment, the SQL statement generation device based on the large language model further includes:

[0155] A preprocessing module, used for preprocessing the user query text to obtain a standard user query text;

[0156] The input module is used to input the standard user query text into a pre-trained Text2SQL model to obtain the SQL statement output by the Text2SQL model.

[0157] In one embodiment, the SQL statement generation device based on the large language model further includes:

[0158] The verification module is used to verify the SQL statement and obtain a verification result; if the verification result is passed, the SQL statement that has passed the verification is input into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model.

[0159] In one embodiment, the verification module is further used for:

[0160] If the verification result is that the verification fails, obtaining error information in the verification result;

[0161] Concatenate the error message with the standard user query text to obtain a target concatenated text;

[0162] The target concatenated text is re-input into the Text2SQL model until the SQL statement output by the Text2SQL model passes verification.

[0163] In one embodiment, the verification module is further used for:

[0164] Parsing the grammatical structure of the SQL statement;

[0165] The grammatical structure is compared with predefined SQL grammar rules to obtain the grammar verification result.

[0166] For the specific definition of the SQL statement generation device based on the large language model, please refer to the definition of the SQL statement generation method based on the large language model above, which will not be repeated here. Each module in the above-mentioned SQL statement generation device based on the large language model can be implemented in whole or in part by software, hardware and a combination thereof. The above-mentioned modules can be embedded in or independent of the processor in the computer device in the form of hardware, or can be stored in the memory of the computer device in the form of software, so that the processor can call and execute the operations corresponding to the above modules.

[0167] In one embodiment, a computer device is provided. The computer device may be a server, and its internal structure diagram may be as follows: Figure 6 As shown. The computer device includes a processor, a memory, a network interface and a database connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store data required for a method for generating SQL statements based on a large language model. The network interface of the computer device is used to communicate with an external terminal through a network connection. When the computer program is executed by the processor, a method for generating SQL statements based on a large language model is implemented.

[0168] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored in the memory and executable on the processor. When the processor executes the computer program, the method for generating SQL statements based on a large language model in the above embodiment is implemented, such as the contents described in the embodiment of steps S10-S90. To avoid repetition, it is not repeated here. Alternatively, when the processor executes the computer program, the functions of each module / unit in the embodiment of the device for generating SQL statements based on a large language model are implemented, such as the functions of generating SQL statements based on a large language model in the embodiment of steps S10-S90. To avoid repetition, it is not repeated here.

[0169] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the method for generating SQL statements based on a large language model in the above embodiment is implemented, such as the contents described in steps S10-S90 of the embodiment. To avoid repetition, it is not repeated here. Alternatively, when the computer program is executed by a processor, the functions of each module / unit in the embodiment of the above-mentioned device for generating SQL statements based on a large language model are implemented, such as the functions of generating SQL statements based on a large language model in steps S10-S90 of the embodiment. To avoid repetition, it is not repeated here.

[0170] Those skilled in the art can understand that all or part of the processes in the above-mentioned embodiment methods can be completed by instructing the relevant hardware through a computer program, and the computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above-mentioned methods. Among them, any reference to memory, storage, database or other media used in the embodiments provided in this application can include non-volatile and / or volatile memory. Non-volatile memory may include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM) or flash memory. Volatile memory may include random access memory (RAM) or external cache memory. As an illustration and not limitation, RAM is available in many forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDRSDRAM), enhanced SDRAM (ESDRAM), synchronous link (Synchlink) DRAM (SLDRAM), memory bus (Rambus) direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and memory bus dynamic RAM (RDRAM).

[0171] Those skilled in the art can clearly understand that for the convenience and simplicity of description, only the division of the above-mentioned functional units and modules is used as an example. In actual applications, the above-mentioned functions can be distributed and completed by different functional units and modules as needed, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0172] The embodiments described above are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some of the technical features may be replaced by equivalents. Such modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A method for generating SQL statements based on a large language model, characterized in that: include: Get user query text; Input the user query text into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model; Inputting the SQL statement into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model; Calculating the semantic similarity between the user query text and the target query text; If the semantic similarity is greater than or equal to a preset target value, it is determined that the SQL statement is the SQL statement corresponding to the user query text.

2. The method for generating SQL statements based on a large language model according to claim 1, characterized in that: The step of inputting the user query text into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model includes: Preprocessing the user query text to obtain a standard user query text; The standard user query text is input into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model.

3. The method for generating SQL statements based on a large language model according to claim 2, characterized in that: The step of inputting the SQL statement into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model includes: Verify the SQL statement to obtain a verification result; If the verification result is that the verification is passed, the SQL statement that has passed the verification is input into a pre-trained SQL2Text model to obtain the target query text output by the SQL2Text model.

4. The method for generating SQL statements based on a large language model according to claim 3, characterized in that: The method further comprises: If the verification result is that the verification fails, obtaining error information in the verification result; Concatenate the error message with the standard user query text to obtain a target concatenated text; The target concatenated text is re-input into the Text2SQL model until the SQL statement output by the Text2SQL model passes verification.

5. The method for generating SQL statements based on a large language model according to claim 3, characterized in that: The step of verifying the SQL statement to obtain a verification result includes: Performing syntax check on the SQL statement to obtain a syntax check result; Performing semantic verification on the SQL statement to obtain a semantic verification result; Performing a security check on the SQL statement to obtain a security check result; A performance check is performed on the SQL statement to obtain a performance check result.

6. The method for generating SQL statements based on a large language model according to claim 5, characterized in that: The step of performing syntax checking on the SQL statement to obtain a syntax checking result includes: Parsing the grammatical structure of the SQL statement; The grammatical structure is compared with predefined SQL grammar rules to obtain the grammar verification result.

7. The method for generating SQL statements based on a large language model according to claim 1, characterized in that: The SQL2Text model is trained in the following way: Acquire a training sample data set and a test sample data set, wherein the training sample data set includes a plurality of training sample pairs, the test sample data set includes a plurality of test sample pairs, the training sample pairs include a training prompt field and a training response field, and the test sample pairs include a test prompt field and a test response field; Based on the training prompt field and the training response field, training the SQL2Text model to be trained; After the SQL2Text model to be trained is trained, the trained SQL2Text model is tested based on the test prompt field and the test response field to obtain a test result; If the test result meets the expected goal, the trained SQL2Text model is determined to be a trained SQL2Text model; If the test result does not meet the expected goal, the hidden layer parameters of the trained SQL2Text model are adjusted and / or the training sample data set is modified, and the trained SQL2Text model is retrained until the test result meets the expected goal.

8. A SQL statement generation device based on a large language model, characterized in that: include: The acquisition module is used to obtain the user query text; An input module, used for inputting the user query text into a pre-trained Text2SQL model to obtain an SQL statement output by the Text2SQL model; inputting the SQL statement into a pre-trained SQL2Text model to obtain a target query text output by the SQL2Text model; A calculation module, used for calculating the semantic similarity between the user query text and the target query text; A determination module is used to determine that the SQL statement is the SQL statement corresponding to the user query text if the semantic similarity is greater than or equal to a preset target value.

9. A computer device comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, characterized in that: When the processor executes the computer program, the method for generating SQL statements based on a large language model according to any one of claims 1 to 7 is implemented.

10. A computer-readable storage medium storing a computer program, characterized in that: When the computer program is executed by a processor, the method for generating SQL statements based on a large language model according to any one of claims 1 to 7 is implemented.

Images