A text-to-SQL conversion method and device based on automated process supervision

Abstract

The present invention relates to a text-to-SQL conversion method and device based on automated process supervision, which belongs to the field of data processing, and includes: obtaining natural language text and generating a structured representation; inputting the structured representation into an SQL construction module to generate an SQL query draft, and converting and generating an SQL statement; inputting the SQL statement into a process supervision model, correcting the error when there is an abnormality in the supervision, and processing it again; after supervision without abnormality, inputting the SQL optimization and verification module for optimization; outputting the optimized SQL statement, and obtaining user feedback; and performing automated data annotation and learning according to database data and user feedback. The present invention realizes the rapid processing and accurate conversion of complex queries through the deep understanding of a large language model, the control of automated supervision, and the adjustment of intelligent optimization, and significantly improves the accuracy and quality of text conversion into SQL statements.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a text-to-SQL conversion method and device based on automated process supervision. Background Art

[0002] Structured Query Language (SQL) is a special-purpose programming language, a database query and programming language used to access data and query, update and manage relational database systems. In the field of database interaction, accurately converting natural language queries into SQL statements has always been a technical challenge. With the continuous advancement of artificial intelligence technology, especially in natural language processing (NLP) and database understanding, large language models have become an important tool for achieving this conversion.

[0003] Although these models perform well in processing simple queries, they still face challenges in understanding and generating accurate and effective SQL statements for complex queries. Traditional text-to-SQL conversion systems often rely on fixed templates and rules, but are prone to insufficient flexibility and low accuracy when processing complex queries, and are complex to operate and have high maintenance costs. Summary of the invention

[0004] In view of the above-mentioned problems in the prior art, the present invention provides a text-to-SQL conversion method and device based on automated process supervision, which significantly improves the accuracy and quality of text conversion into SQL statements through automated process supervision and the deep semantic understanding capability of a large language model.

[0005] In order to achieve the above purpose, the technical solution adopted in this application is:

[0006] In a first aspect, the present invention provides a text-to-SQL conversion method based on automated process supervision, the method comprising:

[0007] Step 1: Obtain natural language text and input it into the natural language understanding module to generate a structured representation;

[0008] Step 2: Input the structured representation into the SQL building module to generate a SQL query draft, and perform statement conversion to generate an SQL statement;

[0009] Step 3: SQL statements are input into the supervision model. When the supervision model has abnormalities, the automatic supervision algorithm is used to locate errors and abnormalities, correct the errors, and generate a corrected file. The corrected file corresponds to the corrected natural language text or the corrected SQL query draft, and then the step 1 corresponding to the natural language text or the step 2 corresponding to the query draft is re-executed. After no abnormalities are found in the supervision, the SQL statement is input into the SQL optimization and verification module to optimize the SQL statement.

[0010] Step 4: Output the optimized SQL statement to the user, obtain user feedback, and perform self-learning and iteration based on the feedback; the automated data labeling and learning module performs automated data labeling and learning based on database-related data and user feedback.

[0011] Furthermore, the reward function in step 3 uses the Bellman function, which is specifically defined as follows:

[0012] ,in, In Strategy Next, the value of state s is Indicates taking action in state s The probability of Indicates taking action in state s After that, the probability of transferring to state s' and obtaining an immediate reward r, r is the immediate reward, is the discount factor, Indicated in strategy Next, the value of state s'.

[0013] Furthermore, the specific process of the natural language understanding module includes entity recognition, attribute recognition, intent parsing and structured conversion in sequence. The entity recognition and attribute recognition adopt a sequence labeling model, the intent parsing adopts a classification algorithm, and the structured conversion uses JSON.

[0014] Furthermore, the specific process of the SQL construction module includes draft generation and statement conversion in sequence, and the draft generation adopts the SQL builder.

[0015] Furthermore, the specific flow of the process supervision model includes process supervision and error detection, and the process supervision model adopts a PRM system.

[0016] Furthermore, the specific process of the SQL optimization and verification module includes logic verification, performance evaluation and SQL statement optimization in sequence, and the specific process of the automatic data labeling and learning module includes automatic data labeling, model training and model evaluation.

[0017] Furthermore, the SQL builder uses a large language model as a tool, combined with hint engineering, to generate a preliminary SQL query draft from the structured representation.

[0018] In a second aspect, the present invention provides a text-to-SQL conversion device based on automated process supervision, comprising:

[0019] Natural Language Understanding (NLU) module: Based on the natural language query input by the user, the large language model is used to parse the user query, identify the entities, attributes and intent of the query, and generate a structured representation;

[0020] SQL building module: Based on the output of the NLU module, the SQL builder generates a preliminary SQL query draft according to the structured representation, and converts the SQL query draft into a SQL statement;

[0021] Process supervision module: uses automated supervision algorithms to monitor and verify the SQL building process;

[0022] SQL optimization and verification module: performs logic verification and performance optimization on generated SQL statements;

[0023] Automated data annotation and learning module: records successful SQL queries and query conditions, annotates failed query attempts and error causes, and uses this data to train process supervision models for self-learning and iteration.

[0024] In a third aspect, the present invention provides a computer-readable storage medium, the computer-readable storage medium comprising a stored program, wherein when the program is running, the device where the computer-readable storage medium is located is controlled to execute the text-to-SQL conversion method based on automated process supervision described in the first aspect.

[0025] In a fourth aspect, the present invention provides an electronic device, comprising a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the electronic device is triggered to execute the text-to-SQL conversion method based on automated process supervision described in the first aspect.

[0026] Compared with the prior art, the advantages and positive effects of the present invention are:

[0027] The present invention provides a text-to-SQL conversion method and device based on automated process supervision, which realizes rapid processing and accurate conversion of complex queries through deep understanding of a large language model, precise control of automated supervision, and efficient adjustment of intelligent optimization, and significantly improves the accuracy and quality of text conversion into SQL statements. BRIEF DESCRIPTION OF THE DRAWINGS

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

[0029] Figure 1 The figure is a flow chart of the overall architecture of the text-to-SQL conversion method based on automated process supervision of the present invention. DETAILED DESCRIPTION

[0030] In order to more clearly understand the above-mentioned purpose, features and advantages of the present invention, the present invention is further described below in conjunction with the accompanying drawings and embodiments. It should be noted that the embodiments of the present application and the features in the embodiments can be combined with each other without conflict.

[0031] In the following description, many specific details are set forth to facilitate a full understanding of the present invention. However, the present invention may also be implemented in other ways than those described herein. Therefore, the present invention is not limited to the specific embodiments of the following disclosure.

[0032] Embodiment 1, as Figure 1 As shown, the present invention provides a text-to-SQL conversion method based on automated process supervision. The natural language understanding module adopts NLP technology to convert the natural language text input by the user into structured query intent and entity representation. The SQL construction module intelligently constructs a preliminary query statement that conforms to the SQL grammar rules based on the output of the natural language understanding module. The process supervision module uses an automated supervision algorithm to carefully monitor and verify the construction process of the SQL statement to ensure the logical correctness of each step. The SQL optimization and verification module performs logical verification and performance optimization on the generated SQL statements to ensure the accuracy and efficiency of the SQL statements. The automated data annotation and learning module automatically collects user feedback and database interaction data, and continuously optimizes the SQL generation strategy. The specific steps are as follows:

[0033] Step 1: The natural language text input by the user is parsed by the query analyzer built with the large language model. The user's query is encoded through the pre-trained BERT model, the semantic features of the natural language text are extracted, and the sequence annotation model BiLSTM-CRF is applied to identify entities and attributes. Bert is combined with the TextCNN classification algorithm to determine the user's query intention, that is, Bert converts the user's input information into a high-dimensional dense vector representation, and Bert's output is used as the input of TextCNN. TextCNN is used as a classifier to classify the user's intention and convert it into a structured query intention and entity representation using JSON; taking the user input "query detailed information of the top 10 products with the highest sales in 2022" as an example, entity recognition can identify "2022" as a time entity and "product" as an object entity. Attribute recognition can identify "sales" as a key attribute and needs to be sorted according to this attribute. Intent analysis determines that the user's intention is to query and sort data. Structured conversion converts the above information into a structured representation, and the JSON data obtained is as follows: { "intent": "query", "time": "2022", "sortAttribute": "sales", "sortOrder":"descending", "limit": 10, "entity": "product", "attributes": ["details"]};

[0034] Step 2: According to the structured representation output by the natural language understanding module, the SQL builder, under the prompt of the database architecture information and the SQL template information, inputs the structured representation output by the natural language understanding module into the large language model, and generates a preliminary SQL query draft in combination with the prompt engineering. The prompt engineering is a LLM usage technology that improves the performance of LLM by designing and improving the prompt of LLM. Its goal is to create an effective and controllable LLM system so that it can accurately and reliably perform specific tasks. The SQL builder starts the mapping mechanism to map the logic and structure in the natural language text to the SQL grammar rules, including the accurate mapping of the table, field name, conditional expression, aggregate function, etc., and converts entities, attributes and query intent into components of SQL statements. For the example, according to the structured representation obtained in step 1, the generated SQL draft is as follows: SELECT product, detailed information FROM product JOIN sales record ON product.ID = sales record.product ID WHERE YEAR(sales record.sales date) = 2022 GROUP BY product.ID ORDER BY SUM(sales record.sales amount) DESC LIMIT 10;

[0035] Step 3: During the SQL statement construction process, an automated supervision algorithm is used for monitoring and verification. When there is an abnormality in the supervision, the process supervision module uses a binary search algorithm to find the middle position of the SQL statement in the execution order, and determines whether there is a field name mismatch, conditional logic error, etc. from the beginning to the middle position, and promptly discovers and reminds the user to correct the error, generates a corrected file, and the corrected file corresponds to the corrected natural language text or the corrected SQL query draft, and then re-executes step 1 corresponding to the corrected natural language text or step 2 corresponding to the query draft; Among them, the automated supervision algorithm uses a process reward model (Process Reward model), which is an improvement over the traditional result reward model. The reward function uses the Bellman function, which is specifically defined as follows:

[0036]

[0037] in, Indicated in strategy Next, the value of state s is Indicates taking action in state s The probability of Indicates taking action in state s After that, the probability of transferring to state s' and obtaining an immediate reward r, r is the immediate reward, is the discount factor, Indicated in strategy The value of state s' is used to balance the importance of current rewards and future rewards. The automated process supervision module monitors the SQL building process and checks the syntax and logic of SQL statements, such as: verifying whether the table name and field name exist, checking whether the date function YEAR() is used correctly, and verifying GROUP BY and ORDER Whether the logic of the BY clause meets expectations, use binary search to quickly locate and prompt possible errors, such as spelling errors in field names; after supervision without abnormalities, the SQL statement is entered into the SQL optimization and verification module, and the generated SQL statement is logically verified and performance optimized by means of execution plan analysis, index usage check, etc. For queries with poor performance, the system adopts a variety of optimization strategies, such as rewriting queries, adding indexes, adjusting query order, etc., to improve query efficiency and response time. The optimized SQL statement will be re-entered into the SQL optimization and verification module for logical verification and performance evaluation again to ensure the accuracy and efficiency of the SQL statement; for the example, ensure that the SQL statement can correctly return "detailed information of the top 10 products with the highest sales in 2022", evaluate the query efficiency, such as checking the execution plan to confirm whether there are inefficient operations such as full table scans; optimize SQL statements, such as adding appropriate indexes to speed up queries (assuming that the index already exists or the system recommends adding it). If the result is accurate and the performance is good, the system records the success case. If the result is inaccurate or the performance is poor, the user reports the problem, such as "the returned product information is incomplete" or "the query speed is too slow."

[0038] Step 4: The optimized SQL statement is input to the user, and user feedback is obtained. The query conversion performance is continuously improved by adjusting model parameters and optimizing algorithm logic according to user feedback. The process supervision model is trained according to the collected feedback data to improve the intelligence level of query conversion. The automated data annotation and learning module can automatically collect relevant data from database query logs and user feedback, including successful SQL queries, failed SQL query attempts, and user modification suggestions. Through data cleaning, annotation, and feature extraction, these data are converted into high-quality data sets that can be used for model training. These data sets are used to train the process supervision model to identify and correct problems in the SQL construction process, so as to continuously adapt to new query patterns and user needs and improve the accuracy and efficiency of query conversion. The training process supervision module process is as follows:

[0039] 1. Divide the data set: Divide the preprocessed data set into training set, validation set and test set. The training set is used to train the process supervision model, the validation set is used to adjust the process supervision model parameters and perform hyperparameter optimization, and the test set is used to evaluate the final performance of the process supervision model.

[0040] 2. Training model: Use the training set data to train the process supervision model. During the training process, the process supervision model will learn how to map the input data to the expected output data (including the prediction results of each step and the final answer); use the Adam optimizer optimization algorithm to update the parameters of the process supervision model to minimize the value of the loss function; in each iteration, the process supervision model will calculate the prediction results based on the current parameters and input data, compare them with the labeled data, calculate the value of the loss function, and update the parameters according to the gradient of the loss function;

[0041] 3. Verification and adjustment: Use the validation set to verify the trained process supervision model and evaluate its performance on unseen data;

[0042] According to the verification results, the hyperparameters (such as learning rate, batch size, number of iterations, etc.) and model architecture (such as number of layers, number of nodes, etc.) of the process supervision model are adjusted to improve the performance of the model. The process of training, verification and adjustment needs to be repeated many times until the performance of the process supervision model reaches a satisfactory level.

[0043] Embodiment 2, the present invention provides a text-to-SQL conversion device based on automated process supervision, comprising:

[0044] Natural Language Understanding (NLU) module: Based on the natural language query input by the user, the large language model is used to parse the user query, identify the entities, attributes and intent of the query, and generate a structured representation;

[0045] SQL building module: Based on the output of the NLU module, the SQL builder generates a preliminary SQL query draft according to the structured representation, and converts the SQL query draft into a SQL statement;

[0046] Process supervision module: uses automated supervision algorithms to monitor and verify the SQL building process;

[0047] SQL optimization and verification module: performs logic verification and performance optimization on generated SQL statements;

[0048] Automated data annotation and learning module: records successful SQL queries and query conditions, annotates failed query attempts and error causes, and uses this data to train process supervision models for self-learning and iteration.

[0049] Embodiment 3, the present invention provides a computer-readable storage medium, the computer-readable storage medium includes a stored program, wherein when the program is running, the device where the computer-readable storage medium is located is controlled to execute the text-to-SQL conversion method based on automated process supervision described in Embodiment 1

[0050] Embodiment 4, the present invention provides an electronic device, comprising a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the electronic device is triggered to execute the text-to-SQL conversion method based on automated process supervision described in Embodiment 1.

[0051] The electronic device may include: a processor, a memory and a communication unit. These components communicate via one or more buses. Those skilled in the art will appreciate that the structure of the electronic device does not limit the embodiments of the present invention, and it may be a bus structure or a star structure, or a combination of certain components, or different component arrangements.

[0052] The communication unit is used to establish a communication channel so that the electronic device can communicate with other devices, receive user data sent by other devices or send user data to other devices.

[0053] The processor is the control center of the electronic device. It uses various interfaces and lines to connect various parts of the entire electronic device. It runs or executes software programs and / or modules stored in the memory, and calls data stored in the memory to perform various functions of the electronic device and / or process data. The processor can be composed of an integrated circuit (IC), for example, it can be composed of a single packaged IC, or it can be composed of multiple packaged ICs with the same or different functions. For example, the processor can only include a central processing unit (CPU). In an embodiment of the present invention, the CPU can be a single computing core or multiple computing cores.

[0054] The memory is used to store the execution instructions of the processor. The memory can be implemented by any type of volatile or non-volatile storage device or a combination of them, such as static random access memory (SRAM), electrically erasable programmable read-only memory (EEPROM), erasable programmable read-only memory (EPROM), programmable read-only memory (PROM), read-only memory (ROM), magnetic memory, flash memory, magnetic disk or optical disk.

[0055] When the execution instructions in the memory are executed by the processor, the electronic device is enabled to execute part or all of the steps of Embodiment 1.

[0056] The above description is only a preferred embodiment of the present invention and does not limit the present invention in other forms. Any technician familiar with the profession may use the technical content disclosed above to change or modify it into an equivalent embodiment with equivalent changes and apply it to other fields. However, any simple modification, equivalent change and modification made to the above embodiment based on the technical essence of the present invention without departing from the content of the technical solution of the present invention still falls within the protection scope of the technical solution of the present invention.

Claims

1. A text-to-SQL conversion method based on automated process supervision, characterized in that: The method includes: Step 1: Obtain natural language text and input it into a natural language understanding module to generate a structured representation. The specific process of the natural language understanding module includes entity recognition, attribute recognition, intent analysis and structured conversion in sequence. The entity recognition and attribute recognition use a sequence labeling model, the intent analysis uses a classification algorithm, and the structured conversion uses JSON; Step 2: Input the structured representation into the SQL building module to generate a SQL query draft, and perform statement conversion to generate an SQL statement; Step 3: SQL statements are input into the process supervision model. When the supervision model has abnormalities, the automated supervision algorithm is used to locate errors and abnormalities, and correct the errors. Then, step 1 corresponding to the corrected natural language text or step 2 corresponding to the query draft is re-executed. After no abnormalities are found in the supervision, the SQL statements are input into the SQL optimization and verification module to optimize the SQL statements. Among them, the automated supervision algorithm uses the process reward model, and the reward function uses the Bellman function. Step 4: Output the optimized SQL statement to the user, obtain user feedback, and perform self-learning and iteration based on the feedback; the automated data labeling and learning module performs automated data labeling and learning based on database-related data and user feedback.

2. A text-to-SQL conversion method based on automated process supervision according to claim 1, characterized in that: The reward function in step 3 uses the Bellman function, which is specifically defined as follows: ,in, Indicated in strategy Next, the value of state s is Indicates taking action in state s The probability of Indicates taking action in state s After that, the probability of transferring to state s' and obtaining an immediate reward r, r is the immediate reward, is the discount factor, Indicated in strategy Next, the value of state s'.

3. A text-to-SQL conversion method based on automated process supervision according to claim 1, characterized in that: The specific process of the SQL building module includes draft generation and statement conversion in sequence, and the draft generation adopts the SQL builder.

4. A text-to-SQL conversion method based on automated process supervision according to claim 1, characterized in that: The specific flow of the process supervision model includes process supervision and error detection, and the process supervision model adopts a PRM system.

5. The text-to-SQL conversion method based on automated process supervision according to claim 1, characterized in that: The specific process of the SQL optimization and verification module includes logic verification, performance evaluation and SQL statement optimization in sequence, and the specific process of the automatic data labeling and learning module includes automatic data labeling, model training and model evaluation.

6. A text-to-SQL conversion method based on automated process supervision according to claim 4, characterized in that: The SQL builder uses a large language model as a tool, combined with hint engineering, to generate a preliminary SQL query draft from a structured representation.

7. A text-to-SQL conversion device based on automated process supervision, characterized in that: include: Natural Language Understanding (NLU) module: Based on the natural language query input by the user, the large language model is used to parse the user query, the sequence labeling model is used for entity recognition and attribute recognition, the classification algorithm is used for intent analysis, and JSON is used to generate structured representation; SQL building module: Based on the output of the NLU module, the SQL builder generates a preliminary SQL query draft according to the structured representation, and converts the SQL query draft into a SQL statement; Process supervision module: uses the process reward model to perform an automated supervision algorithm to monitor and verify the SQL building process, where the reward function uses the Bellman function; SQL optimization and verification module: performs logic verification and performance optimization on generated SQL statements; Automated data annotation and learning module: records successful SQL queries and query conditions, annotates failed query attempts and error causes, and uses this data to train process supervision models for self-learning and iteration.

8. A computer-readable storage medium, characterized in that: The computer-readable storage medium includes a stored program, wherein when the program is executed, the device where the computer-readable storage medium is located is controlled to execute the text-to-SQL conversion method based on automated process supervision as described in any one of claims 1 to 7.

9. An electronic device, characterized in that: It comprises a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the electronic device is triggered to execute the text-to-SQL conversion method based on automated process supervision as described in any one of claims 1-7.

Images