A natural language to SQL method based on a deep learning model

Abstract

The application discloses a natural language to SQL conversion method based on a deep learning model, which is based on a deep learning model, wherein a natural language question of a user is predicted through a deep model, and a result of the prediction is converted into an SQL statement through a CYK algorithm, and the method comprises the following steps: step 1, preparing a corpus; step 2, establishing a table model; and step 3, performing SQL conversion through the CYK algorithm; the method is convenient and direct in converting human natural language into an SQL query statement and the like, and then the SQL statement can be directly used to obtain data that a user wants, the method simplifies a data query mode, the data that a user wants is inquired in a speaking mode, and the intelligent degree of a data acquisition mode is improved.

Description

Technical Field

[0001] This invention relates to a method for converting natural language to SQL, and more particularly to a method for converting natural language to SQL based on a deep learning model. Background Technology

[0002] With the rise of the Fourth Industrial Revolution, industries related to artificial intelligence (AI) have also flourished. AI can be used to assist our daily work and life. Natural Language Processing (NLP) is an important branch of AI, aiming to enable machines to understand the language people use in daily life, thereby reducing communication barriers between humans and machines and facilitating further processing and transformation by machines.

[0003] In the digital age, various types of information and data are stored in various databases and other systems. For the massive amounts of digital data on the internet, traditional relational databases are generally used for storage to facilitate management and maintenance. However, retrieving data from these systems still requires professionals to use specialized tools to write professional query statements or develop query systems that allow users to specify query parameters in an easy-to-understand way. These interactive query methods are highly specialized and require certain professional knowledge to use effectively. How to retrieve desired information from these relational databases using natural language—that is, how to translate human natural language query descriptions into executable database query statements (SQL)—has become one of the most popular research directions in the field of natural language processing. Natural language query interfaces aim to enable users to interact with relational databases using natural language to obtain the data they want, and are an indispensable part of building automated intelligent database query systems. The most important task in implementing natural language query interfaces is how to generate SQL statements from natural language queries, a task known as NL2SQL.

[0004] Natural language query interfaces enable users to directly query data using everyday natural language descriptions. This not only saves users the cost of learning professional knowledge but also saves time in constructing SQL statements, thus improving query efficiency.

[0005] Compared to English NL2SQL tasks, Chinese NL2SQL tasks present greater research challenges. Almost all existing research on NL2SQL tasks focuses on English datasets, making it difficult to directly transfer English models and methods to Chinese. Chinese natural language lacks natural word boundaries and is more varied in expression than English. Most current NL2SQL tasks process different tasks separately to achieve the final result. While this ensures task uniqueness, it lacks overall task integrity, making it more difficult for machines to understand. Therefore, research on Chinese NL2SQL tasks remains crucial. This paper, building upon English models and methods, integrates multiple tasks into a single model, directly converting human natural language into SQL query statements. The SQL statements can then be used to directly retrieve the data desired by the user. This paper proposes a novel solution for Chinese NL2SQL tasks.

[0006] This invention provides a method for converting natural language to SQL based on a deep learning model, disclosing the training method of the deep learning model and the corresponding algorithm. Through the trained deep learning model, this invention first directly converts human natural language into SQL query statements; then, the system uses SQL statements to manipulate the database and directly obtain the data needed by the user; finally, the system outputs the relevant data. Existing technologies require professionals to use specialized tools to write professional query statements to retrieve data from the system, but these query interaction methods are highly specialized and require a certain level of expertise to use effectively. The advantage of this invention is that it simplifies the data query method and reduces the professional difficulty of data retrieval. It can directly convert human natural language to query language, making it convenient for non-professionals to obtain answers, thus improving the intelligence of data querying and retrieval. Summary of the Invention

[0007] The purpose of this invention is to provide a method for converting natural language into SQL, so as to solve the problems mentioned in the above-mentioned technical background.

[0008] To achieve the above objectives, the present invention adopts the following technical solution:

[0009] This invention provides a method for natural language to SQL conversion based on a deep learning model. The user's question is predicted by the deep model, and the predicted result is converted into an SQL statement using the CYK algorithm. The main steps include:

[0010] Step 1: Corpus preparation;

[0011] Step 2: Create a table model;

[0012] Step 3: Perform SQL conversion using the CYK algorithm.

[0013] Preferably, step 1 includes the following steps:

[0014] Step 1-1: Prepare the corpus for training the model;

[0015] Step 1-2: Corpus annotation.

[0016] Furthermore, step 2 includes the following steps:

[0017] Step 2-1: Obtain natural language semantic features;

[0018] Step 2-2: Predict the relationship between entities based on semantic features. The relationship between entities is predicted by the vector of the first character CLS of the semantic features.

[0019] Steps 2-3: Predict the function corresponding to the selected column based on semantic features. The function corresponding to the selected column is predicted by using the vector of each column of semantic features.

[0020] Steps 2-4: Predict the selected entity based on semantic features. The selected entity is predicted by the vector of each character of the semantic features.

[0021] Steps 2-5: Predict the selected entities based on semantic features. The selected entities are predicted by using the vector of each column of the semantic features and the function result corresponding to the selected column.

[0022] Preferably, step 2-1 includes the following steps:

[0023] Step 2-1-1: The natural language is transformed into three vectors through the Embedding layer: word vector, vector representing the basic semantic information of the token, and segment embedding vector.

[0024] Step 2-1-2: Obtain semantic features through 12 stacked encoder layers. The encoder layer contains self-attention, so each character can use information from the remaining characters before and after it.

[0025] Preferably, the entity relationship described in step 2-2 has three possible outcomes: no relationship, AND, and OR. The vector of the first character cls in the preprocessed inputs1 is transformed into a vector dimension of 3 through a fully linked layer, and then the value with the highest probability is selected as the entity_type result through softmax.

[0026] Preferably, the functions corresponding to the selected columns in steps 2-3 include 6 types: no function, average, maximum, minimum, total count, and summation. The vector of cls for each col in the preprocessed inputs1 is transformed into a vector dimension of 6 through a fully connected layer, and then the value with the highest probability is selected as the agg result through softmax.

[0027] Preferably, the entity extraction described in steps 2-4 is performed using named entities, employing the BERT+LSTM+CRF model. c1 is the first character in the question. LSTMOUTPUT is obtained through forward LSTM and backward LSTM, and then a CRF layer is added. The CRF algorithm includes two features: the first feature is the state feature function, which calculates the state score, and the second feature is the transition feature function, which calculates the transition score.

[0028] Preferably, in steps 2-5, the selected entity is predicted using the vector of each column of the semantic features and the corresponding function result of the selected column. There are five types of operators: no operation, equal to, not equal to, greater than, and less than. The vector of each column in the preprocessed outputs1 result is summed with the corresponding agg result, then the dimension is transformed to 5 using a fully connected layer, and finally, the value with the highest probability is selected as the relation result using softmax.

[0029] Furthermore, step 3 includes the following steps:

[0030] Step 3-1: Initialize the algorithm according to the SQL syntax;

[0031] Step 3-2: Determine whether any given string satisfies the SQL syntax.

[0032] Preferably, step 3-1 includes the following steps:

[0033] Step 3-1-1: Construct the recognition matrix. First, construct the main diagonal, let t 0，0 =0, then from t 1，1 to t n，n Place the word w of the input sentence x at the main diagonal position. i Construct element t above the main diagonal and immediately adjacent to the main diagonal. i，i+1 For the input sentence x = w1w2…w, analyze starting from w1. If there is a rule A->w1 in the production set of grammar G, then fill in t. 0，1 =A and so on, for each terminal symbol w on the main diagonal i All nonterminal symbols that can be derived from it are written above its right main diagonal.

[0034] Step 3-1-2: Analyze the sentence using the CYK algorithm.

[0035] In a preferred embodiment, if the sentence is TNWO, then:

[0036] s->select where

[0037] select->T

[0038] where->PQV

[0039] P->N

[0040] Q->W

[0041] V->O

[0042] (1) There are four strings, so they form a 5*5 matrix. Fill in the keywords that are hit on the diagonal and indicate the type of hit above.

[0043] 0 select T P N Q W V O

[0044] (2) PQV can form a WHERE clause, but SELECT has no possible values, so it shifts one cell to the right.

[0045] 0 select select T P where N Q W V O

[0046] (3) Select and where can form S, and s is the termination point, then the algorithm ends.

[0047] 0 select select select s T P where N Q W V O

[0048] The structure of the SQL parse tree is obtained as follows: Figure 6 As shown.

[0049] Preferably, the data needs to be preprocessed before obtaining semantic features. The preprocessing steps include:

[0050] Step (1): Concatenate the question with the table data. Add CLS at the beginning of the question and SEP at the end. Do the same for the columns of the table. c1 represents the first character of the string output by the user, and c2 and so on. col1 represents the first column of the table, and col2 and so on.

[0051] Step (2): Convert the preprocessed question into input embedding, then add positional encoding, perform residual linking through a multi-head attention layer, then normalize the norm, and then perform residual linking and normalization through a feedforward neural network.

[0052] Step (3): Finally, output two vectors, outputs1 and outputs2.

[0053] Compared with the prior art, the technical solution of the present invention has the following beneficial effects:

[0054] 1. Adding a CRF model to the named entity task improves accuracy;

[0055] 2. Establish an SQL rule model using the CYK algorithm to facilitate rapid SQL generation;

[0056] 3. Multiple tasks are combined into a single model, facilitating rapid training and deployment. Attached Figure Description

[0057] The accompanying drawings, which constitute a part of this application, are used to provide a further understanding of this application. The illustrative embodiments and descriptions of this application are used to explain this application and do not constitute an undue limitation of this application. In the drawings:

[0058] Figure 1 This is a flowchart of natural language data preprocessing;

[0059] Figure 2 This is a diagram of the entity relationship prediction model;

[0060] Figure 3 It is the function model corresponding to the predicted column;

[0061] Figure 4 Predicting the selection of entity models;

[0062] Figure 5 Predictive selection of entity operator model;

[0063] Figure 6 String parsing tree based on CYK algorithm;

[0064] Figure 7 A block diagram illustrating the logic principle of a preferred embodiment of the present invention. Detailed Implementation

[0065] This invention provides a method for converting natural language to SQL based on a deep learning model. To make the objectives, technical solutions, and effects of this invention clearer and more explicit, the invention is further described in detail below with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are only for explaining the invention and are not intended to limit the invention.

[0066] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this invention are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be used interchangeably where appropriate. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion. For example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to these processes, methods, products, or apparatuses.

[0067] Example 1:

[0068] A method for natural language to SQL conversion based on a deep learning model is proposed. The user's question is predicted by the deep model, and the predicted result is converted into an SQL statement using the CYK algorithm. The main steps include:

[0069] Step 1: Corpus Preparation. Step 1 includes the following steps:

[0070] Step 1-1: Prepare the corpus for model training. Collect tabular data and process it into the data structure required by the model. The preferred data structure is: {"question":"question","sql":{"agg":[function type],"entity_type":entity relationship,"col":[number of columns in the table],"relation":[[number of columns in the table, operator, entity]]},"ner":[BIO annotation]}. The collected data has five features. The first feature, `agg`, represents the function corresponding to the selected column. Functions include six types: no function, average, maximum, minimum, total count, and summation. The second feature, `ner`, represents the entity in the question. Different types of entities are uniformly annotated using BIO, where B represents the beginning of the entity, I represents the rest, and O indicates no entity. The third feature, `entity_type`, represents the relationship between entities. There are three types of relationships: no relationship, AND, OR, AND (multiple entities must satisfy the condition simultaneously), OR (only one condition needs to be satisfied). The fourth feature, `col`, represents the column number of the selected column in the table. The fifth feature is relation, which represents the column number of the selected entity in the table and the type of operator used. There are five types of operators: no operation, equal to, not equal to, greater than, and less than.

[0071] Step 1-2: Corpus annotation.

[0072] Step 2: Build the table model. The table model mainly consists of three layers: an encoder, a context reinforcement layer, and an output layer. The encoder layer is based on BERT and outputs a vector for each character; the context reinforcement layer adds the vectors corresponding to each column name to the question; the output layer obtains a column vector. The table model integrates four tasks (predicting relationships between entities, predicting the function corresponding to the selected column, predicting the selected entity, and predicting the operator of the entity) into a single model. The model has 7 inputs and 3 outputs, ultimately completing the four tasks of predicting relationships between entities, predicting the function corresponding to the selected column, predicting the selected entity, and predicting the operator of the entity.

[0073] Step 2 includes the following steps:

[0074] Step 2-1: Semantic Features. Natural language processing transforms characters into three vectors through an embedding layer: word vector, vector representing the basic semantic information of the token, and segment embedding vector. Then, semantic features are obtained through a 12-layer stacked encoder layer. The encoder layer includes self-attention, so each character can simultaneously use information from the remaining characters before and after it.

[0075] like Figure 1 As shown, the data is preprocessed. The preprocessing steps are as follows: (1) First, the question and the table data are concatenated. The question begins with the character CLS and ends with the character SEP. The same operation is performed on the columns of the table. c1 represents the first character of the string output by the user, and c2 and so on. col1 represents the first column in the table, and col2 and so on. (2) The preprocessed question is converted into input embedding, and then the position variable Positional Encoding is added. After residual linking through a layer of Multi-HeadAttention, normalization is performed. Then, residual linking and normalization are performed through a feedforward neural network. (3) Finally, two vectors are output, namely outputs1 and outputs2.

[0076] Step 2-2: Predict relationships between entities based on semantic features. This involves predicting relationships between entities using the vector of the first character (CLS) of the semantic features. For example... Figure 2 As shown, there are three possible outcomes for the relationships between entities: no relationship, AND, and OR. The vector of the first character cls in the preprocessed inputs1 is transformed to 3 dimensions through a fully linked layer, and then the entity_type result is selected by softmax with the highest probability.

[0077] Steps 2-3: Predict the function corresponding to the selected column based on semantic features. This involves predicting the function corresponding to the selected column using the vector of each column's semantic features. For example... Figure 3 As shown, the functions corresponding to the selected columns include 6 types: no function, average, maximum, minimum, total count, and sum. The vector of cls for each col in the preprocessed inputs1 is transformed to 6 dimensions through a fully connected layer, and then the value with the highest probability is selected as the agg result through softmax.

[0078] Steps 2-4: Predict the selected entity based on semantic features. The selected entity is predicted using the vector of each character in the semantic features. For example... Figure 4As shown, entity extraction is performed using named entities, employing a BERT+LSTM+CRF model. c1 is the first character in the question. LSTM OUTPUT is obtained through forward LSTM and backward LSTM, followed by a CRF layer. While BERT alone considers contextual relationships, CRF is more sensitive to non-rule-compliant cases like B followed directly by B, reducing the probability of obviously non-rule-compliant situations and further improving the accuracy of BERT model predictions. The CRF algorithm mainly uses two features: a state feature function, which calculates state scores, and a transition feature function, which calculates transition scores. The former identifies which entity labels the character at the current position can be converted into, while the latter focuses on the combinations of entity labels between the current position and its neighboring positions.

[0079] Steps 2-5: Predict the selected entity based on semantic features. This involves predicting the selected entity using the vector of each column of the semantic features and the function result corresponding to the selected column. For example... Figure 5 As shown, there are five types of operators: no operation, equal to, not equal to, greater than, and less than. The vector of each column in the preprocessed outputs1 result is summed with the corresponding agg result. Then, a fully connected layer is used to transform the dimension to 5, and finally, a softmax layer is used to select the value with the highest probability as the relation result.

[0080] Step 3: CYK Algorithm. The CYK algorithm is a bottom-up analysis algorithm based on CFG rules. Following the SQL query rules, after the user's statement passes through the table model, the result of the table model is input into the CYK algorithm to obtain the corresponding SQL statement. Step 3 includes the following steps:

[0081] Step 3-1: Initialize the algorithm according to SQL syntax. This mainly includes the following steps:

[0082] Step 3-1-1: Construct the recognition matrix. First, construct the main diagonal, let t 0，0 =0, then from t 1，1 to t n，n Place the word w of the input sentence x at the main diagonal position. i Construct element t above the main diagonal and immediately adjacent to the main diagonal. i，i+1 For the input sentence x = w1w2…w, analyze starting from w1. If there is a rule A->w1 in the production set of grammar G, then fill in t. 0，1 =A and so on, for each terminal symbol w on the main diagonal i All nonterminal symbols that can be derived from it are written above its right main diagonal.

[0083] Step 3-1-2: Analyze the sentence using the CYK algorithm. If the sentence is TNWO, then:

[0084] s->select where

[0085] select->T

[0086] where->PQV

[0087] P->N

[0088] Q->W

[0089] V->O

[0090] (2) There are four strings, so they form a 5*5 matrix. Fill in the keywords that are hit on the diagonal and indicate the type of hit above.

[0091] 0 select T P N Q W V O

[0092] (2) PQV can form a WHERE clause, but SELECT has no possible values, so it shifts one cell to the right.

[0093] 0 select select T P where N Q W V O

[0094] (3) Select and where can form S, and s is the termination point, then the algorithm ends.

[0095] 0 select select select s T P where N Q W V O

[0096] The structure of the SQL parse tree is obtained as follows: Figure 6 As shown.

[0097] Step 3-2: Given any string, determine whether it satisfies the SQL syntax.

[0098] The logic process diagram of the embodiment is as follows: Figure 7 As shown.

[0099] Example 2:

[0100] Furthermore, a method for natural language to SQL based on a deep learning model includes the following steps:

[0101] Step 1:

[0102] Question: What was the per capita disposable income of urban residents in Shanghai in 2021?

[0103] Step 2:

[0104] Identification via tabular model:

[0105] {"agg":[2],"entity_type":[2, 2],"col":[2],"relation":[(1==2021),(3==urban residents)]},

[0106] "ner":[BA,BI,BI,IA,IA,IA,BN,IN,IN,IN,O,O,O,O,O,O,O,O,O,O,O]}

[0107] Explanation: Here, col indicates that the selected column is in the second column of the table, the column header of the second column is per capita disposable income, agg indicates that the per capita disposable income needs to be calculated as an average, entity_type indicates that the relationship between the entity Shanghai urban residents and the entity 2022 is AND, and relation indicates that the residence mode is urban residents and the year is 2021.

[0108] Step 3:

[0109] The SQL analysis tree result is: select avg(per capita disposable income)where from table where year==2021and residence mode==urban residents.

[0110] Step 4:

[0111] The SQL statement was used to search the database, and the result was: the average per capita disposable income was 23,772 yuan.

[0112] The specific embodiments of the present invention have been described in detail above, but they are merely examples, and the present invention is not limited to the specific embodiments described above. For those skilled in the art, any equivalent modifications and substitutions to the present invention are also within the scope of the present invention. Therefore, all equivalent transformations and modifications made without departing from the spirit and scope of the present invention should be covered within the scope of the present invention.

Claims

1. A method for converting natural language to SQL based on a deep learning model, characterized in that: The user's natural language questions are predicted using a deep model, and the prediction results are converted into SQL statements using the CYK algorithm, including the following steps: Step 1: Prepare the corpus, including preparing the corpus for model training and labeling the corpus, including function type (agg), entity (ner), relationship between entities (entity_type), the column position of the entity in the table, and the corresponding operator; Step 2: Build the table model, which integrates the four tasks of predicting relationships between entities, predicting the function corresponding to the selected column, predicting the selected entity, and predicting the operator of the entity into a single model, specifically including the following steps 2-1 to 2-5: Step 2-1: Obtain natural language semantic features; the natural language is transformed into three vectors through the Embedding layer: word vector, basic semantic information vector representing the token, and segment embeddings vector. Then, semantic features are obtained through a stacked 12-layer Encoder layer. The Encoder layer contains self-attention, so each word can use information from the words before and after it. Step 2-2: Predict the relationship between entities based on semantic features. The relationship between entities is predicted by the vector of the first character CLS of the semantic features. Steps 2-3: Predict the function corresponding to the selected column based on semantic features. The function corresponding to the selected column is predicted by using the vector of each column of semantic features. Steps 2-4: Predict the selected entity based on semantic features. The selected entity is predicted by the vector of each character of the semantic features. Steps 2-5: Predict the selected entities based on semantic features. The selected entities are predicted by using the vector of each column of the semantic features and the function result corresponding to the selected column. Step 3: Perform SQL conversion using the CYK algorithm.

2. The method for natural language to SQL based on a deep learning model according to claim 1, characterized in that, Step 3, the SQL transformation process using the CYK algorithm, includes the following steps: Step 3-1: Initialize the algorithm according to the SQL syntax; Step 3-2: Given any string, determine whether it satisfies the SQL syntax.

3. The method for natural language to SQL based on a deep learning model according to claim 1, characterized in that, Before obtaining natural language semantic features, data preprocessing is required. The preprocessing steps include: Step (1): Concatenate the question with the table data. Add CLS at the beginning of the question and SEP at the end. Do the same for the columns of the table. c1 represents the first character of the string output by the user, and c2 and so on. col1 represents the first column of the table, and col2 and so on. Step (2): Convert the preprocessed question into input embedding, then add positional encoding, perform residual linking through a multi-head attention layer, then normalize the norm, and then perform residual linking and norm normalization through a feedforward neural network. Step (3): Finally, output two vectors, outputs1 and outputs2.

4. The method for natural language to SQL based on a deep learning model according to claim 1, characterized in that, The entity relationship described in step 2-2 has three possible outcomes: no relationship, AND, and OR. The vector of the first character cls in the preprocessed inputs1 is transformed into a vector dimension of 3 through a fully linked layer, and then the value with the highest probability is selected as the entity_type result through softmax.

5. The method for natural language to SQL based on a deep learning model according to claim 1, characterized in that, The functions corresponding to the selected columns mentioned in steps 2-3 include 6 types: no function, average, maximum, minimum, total count, and summation. The vector of cls for each col in the preprocessed inputs1 is transformed into a vector dimension of 6 through a fully connected layer, and then the value with the highest probability is selected as the agg result through softmax.

6. The method for natural language to SQL based on a deep learning model according to claim 1, characterized in that, The entity extraction described in steps 2-4 uses named entities and adopts the BERT+LSTM+CRF model. c1 is the first character in the question. The LSTM OUTPUT is obtained through forward LSTM and backward LSTM, and then a CRFlayer layer is added. The CRF algorithm includes two features, among which The first feature is the state feature function, which calculates the state score; the second feature is the transition feature function, which calculates the transition score.

7. The method for natural language to SQL based on a deep learning model according to claim 2, characterized in that, The algorithm initialization based on SQL syntax described in step 3-1 includes the following steps: Step 3-1-1: Construct the recognition matrix; Step 3-1-2: Analyze the sentence using the CYK algorithm.

Images