A method and system for converting natural language text into SQL statements

Abstract

The application belongs to the field of natural language processing, and particularly relates to a method and system for converting a natural language text into an SQL statement. The method comprises the following steps: S1, acquiring numerical data, keywords and word segmentation results of the natural language text as main inputs; S2, determining a field to which the natural language text belongs, and taking the field as an additional input; S3, selecting a corresponding professional knowledge base based on the additional input as an external dictionary; S4, forming a splicing vector, inputting the splicing vector into a word vector matrix associated with the external dictionary for vector coding to obtain a query matrix, a key matrix and a value matrix; S5, taking the query matrix, the key matrix and the value matrix as attention factor states to acquire a query target column, an aggregation operation and a filtering condition; and S6, assembling into an SQL query statement. The application provides reliable extraction matching and semantic analysis capabilities through the strategy of integrating multi-module operation processing, and can generate high-quality standardized SQL statements.

Description

TECHNICAL FIELD

[0001] The application belongs to the field of natural language processing, and particularly relates to a method and system for converting natural language text into SQL statements. BACKGROUND

[0002] Natural language to SQL (NL2SQL) is a technology for converting natural language statements into standardized computer executable SQL query statements, and is a natural language structuring task. In actual life and production, a large amount of data is generated in activities such as e-commerce, product, online shopping, etc., most of which is structured and stored in databases. Sometimes, business needs or obtains certain data information, and users need to use SQL statements to query in the database. However, for non-professionals, it is not convenient to obtain results by writing SQL statements, and even for computer practitioners, there are many types of databases, and the operations in different business scenarios are different. In this case, the NL2SQL application technology emerges as the times require, that is, it can facilitate user operation, conveniently obtain information, and help enterprises save operation and reduce tedious work. For example, a user's demand: "What is the total overtime of employees in the July attendance data?". The corresponding SQL is generated: SELECT SUM overtime FROM attendance table WHERE month == 'July', and the converted SQL statement is used to retrieve in the database, and then the query result is obtained. It can be seen that the NL2SQL technology is widely used and has great practical significance.

[0003] At present, in the field of natural language to SQL technology, the mainstream solutions are as follows:

[0004] 1) Template and rule based method. This method is mostly based on statistical theory, uses artificially defined templates, and uses designed expressions to match and parse the user's input problem to the corresponding SQL statement.

[0005] 2) Sequence based method. This method mostly uses sequence to sequence generation task, usually considers the process of converting natural language to SQL statement as "machine translation", uses sequence generation task to construct encoder-decoder framework, and the specific process is to input the preprocessed natural language into the encoder, and then get the SQL statement through the decoder.

[0006] For the above technologies, the template and rule based method has large workload of regular expression design, is prone to matching or mismatching problems, is not suitable for nested queries, and has high requirements for the professional field of developers. The sequence based method currently mostly uses a simple encoder-decoder model, and the matching of nested queries is not perfect, and the structural feature information cannot be well utilized. SUMMARY

[0007] To solve the above problems, the application provides a method and system for converting natural language text into SQL statements, which use a multi-module integrated learning strategy to improve model adaptation to different domain queries, use a professional knowledge base to enhance template matching capabilities, and pay attention to the attention between templates and semantics, thereby accurately converting natural language text into SQL statements.

[0008] The first aspect of the application provides a method for converting natural language text into SQL statements, mainly comprising:

[0009] Step S1, obtaining numerical data, keywords and word segmentation results of the natural language text as main inputs, respectively;

[0010] Step S2, determining the domain of the natural language text based on an expert network model, and taking the domain as an additional input;

[0011] Step S3, selecting a corresponding professional knowledge base based on the additional input as an external dictionary, performing template matching based on the external dictionary and the keywords of the natural language text, and screening to generate intermediate inputs of table name to be queried, aggregation operation, and field information to be queried;

[0012] Step S4, forming a splicing vector based on the numerical data, word segmentation results of the natural language text, and the intermediate inputs, inputting the splicing vector into a word vector matrix associated with the external dictionary for vector encoding, and obtaining a query matrix, a key matrix and a value matrix;

[0013] Step S5, loading the query matrix, the key matrix and the value matrix into a graphics processing unit (GPU), performing parallel calculation with the matrix data converted from the query table as an attention factor state, and obtaining the query target column, the aggregation operation and the filtering condition after LSTM decoding and connection layer classification;

[0014] Step S6, assembling the query target column, the aggregation operation and the filtering condition into a SQL query statement.

[0015] Preferably, step S1 further comprises:

[0016] The numerical, time and date data of the statement are identified and extracted according to rules to obtain a first input;

[0017] Based on the syntax tree formed by the Chinese grammar rules, the logical nodes are extracted to form a second input containing keywords;

[0018] The natural language statement is split according to character encoding to form a third input.

[0019] Preferably, in step S4, constructing the splicing vector comprises adding a first flag CLS before the input text and using a second flag SEP to split between each keyword.

[0020] Preferably, after step S4, further comprising adding a convolutional CNN full connection layer to further abstract semantic matrix information after extracting word vector features through a multi-layer attention mechanism, and storing in numerical form in the memory.

[0021] Preferably, in step S6, assembling the SQL query statement comprises:

[0022] The connector, the SELECT column and the corresponding data and function, the WHERE column and the corresponding WHERE operator and WHERE content are filled and corrected to assemble a standardized SQL query statement and output.

[0023] The second aspect of the application provides a system for converting natural language text into an SQL statement, mainly comprising:

[0024] A preprocessing module is configured to obtain numerical data, keywords and word segmentation results of the natural language text as main inputs, respectively.

[0025] An expert model module is configured to determine the field to which the natural language text belongs based on an expert network model, and take the field as an additional input.

[0026] A knowledge base matching module is configured to select a corresponding professional knowledge base as an external dictionary based on the additional input, and perform template matching based on the external dictionary and the keywords of the natural language text to filter and generate intermediate inputs of a table name to be queried, an aggregation operation and field information to be queried.

[0027] An encoding module is configured to form a splicing vector based on the numerical data, the word segmentation results of the natural language text and the intermediate inputs, input the splicing vector into a word vector matrix associated with the external dictionary for vector encoding, and obtain a query matrix, a key matrix and a value matrix.

[0028] A decoding module is configured to load the query matrix, the key matrix and the value matrix into a graphics processing unit (GPU) to perform parallel calculation with matrix data converted from a query table as an attention factor state, and obtain a query target column, an aggregation operation and a filtering condition after LSTM decoding and connection layer classification.

[0029] A post-processing module is configured to assemble the query target column, the aggregation operation and the filtering condition into an SQL query statement.

[0030] Preferably, the preprocessing module comprises:

[0031] The first input acquisition unit is configured to extract the first input according to the rules of numerical value, time and date data of the sentence.

[0032] The second input acquisition unit is configured to extract the logical nodes of the syntax tree formed by the Chinese syntax rules to form the second input containing the keywords.

[0033] The third input acquisition unit is configured to split the natural sentence according to the character encoding to form the third input.

[0034] Preferably, in the encoding module, the construction of the splicing vector comprises adding a first flag CLS before the input text and using a second flag SEP to split between the keywords.

[0035] Preferably, the encoding module comprises adding a convolutional CNN full connection layer to further abstract the semantic matrix information after extracting the word vector features through the multi-layer attention Attention mechanism, and storing the information in the form of numerical values in the memory.

[0036] Preferably, the post-processing module assembles the SQL query statement, which comprises:

[0037] The connector, the SELECT column and the corresponding data and functions, the WHERE column and the corresponding WHERE operator and WHERE content are filled and corrected to assemble the standardized SQL query statement and output.

[0038] The present application provides reliable extraction matching and semantic analysis capabilities through the strategy of integrating multi-module operation processing, and can generate high-quality and accurate standardized SQL statement information. BRIEF DESCRIPTION OF DRAWINGS

[0039] Figure 1 is a flowchart of a preferred embodiment of the method for converting natural language text into SQL statements according to the present application.

[0040] Figure 2 is a preferred embodiment of the natural language to SQL model diagram of the multi-module integration strategy according to the present application Figure 1 The annual attendance of the embodiment shown in the present application indicates the intention.

[0041] Figure 3 is a preferred embodiment of the natural language to SQL model diagram of the multi-module integration strategy according to the present application Figure 1 DETAILED DESCRIPTION

[0042] ​In order to make the purposes, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be described in more detail below with reference to the drawings in the embodiments of the present application. Identical or similar numerals in the drawings represent identical or similar elements or elements with identical or similar functions throughout. The described embodiments are part of the embodiments of the present application, rather than all the embodiments of the present application. The embodiments described below with reference to the drawings are exemplary and are intended to explain the present application, and cannot be understood as a limitation of the present application. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative labor fall within the scope of protection of the present application. The embodiments of the present application will be described in detail below with reference to the drawings.

[0043] The first aspect of the present application provides a method for converting natural language text into an SQL statement, mainly comprising:

[0044] Step S1, respectively acquiring numerical data, keywords and word segmentation results of the natural language text as main inputs;

[0045] Step S2, determining the field to which the natural language text belongs based on an expert network model, and taking the field as an additional input;

[0046] Step S3, selecting a corresponding professional knowledge base as an external dictionary based on the additional input, and performing template matching based on the external dictionary and the keywords of the natural language text to screen and generate intermediate inputs of a table name to be queried, an aggregation operation and field information to be queried;

[0047] Step S4, forming a splicing vector based on the numerical data, the word segmentation results of the natural language text and the intermediate inputs, inputting the splicing vector into a word vector matrix associated with the external dictionary for vector coding, and obtaining a query matrix, a key matrix and a value matrix;

[0048] Step S5, loading the query matrix, the key matrix and the value matrix into a graphics processing unit (GPU) to perform parallel calculation with matrix data converted from a query table as an attention factor state, and obtaining a query target column, an aggregation operation and a filtering condition after LSTM decoding and connection layer classification;

[0049] Step S6, assembling the query target column, the aggregation operation and the filtering condition into an SQL query statement.

[0050] Step S1 of the present application is an operation on the input natural language text statement, and the input natural language statement is processed in terms of rule normalization, syntax tree and word segmentation, as follows:

[0051] (1) The numerical, time, date and other data of the sentence are identified and extracted according to rules to obtain a first input 1;

[0052] (2) A syntax tree is generated according to the syntax rules of Chinese, and a keyword is identified based on the logical nodes of the syntax tree as a second input 2;

[0053] (3) The natural language sentence is segmented and split according to the character encoding algorithm to obtain a third input 3;

[0054] (4) The first input 1, the second input 2 and the third input 3 are input into the data flow of the model in parallel as the total input for the encoder coding.

[0055] In step S2, the data results of the preprocessing module are received, wherein the first input 2 passes through the expert network model to classify the field of the natural language, such as e-commerce, military, food, etc., and different domain word vectors are initialized for different fields to form a fourth input 4.

[0056] Then in step S3, according to the domain word vector fourth input 4, different professional knowledge bases are selected as introduced external dictionaries to enhance the ability of identifying the keywords of the natural language sentence, and the second input 2 template generated by the syntax tree is matched to filter and generate information such as table name to be queried, aggregation operation, field to be queried, etc. as a fifth input 5.

[0057] In step S4, the encoder dictionary part of the word vector matrix is initialized according to the domain word vector fourth input 4, and different initialization rules are selected for different fields; the natural language text is processed by the modules described above to obtain a plurality of granularities of the first input 1, the third input 3 and the fifth input 5, wherein a special token [CLS] is added before the input text and [SEP] is used to separate between inputs, so as to be spliced into the final input; the final sentence is input into the word vector matrix to obtain vector coding, and the query matrix, key matrix and value matrix are obtained through the multi-layer attention mechanism and full connection layer processing in the BERT model encoder, and are stored in the memory in the form of matrix values.

[0058] Then in step S5, decoding is performed, the query matrix, key matrix and value matrix stored in the memory are loaded into the graphics processing unit GPU, and the matrix data converted from the query table is calculated in parallel as the attention factor state, and the results of each module clause are obtained after the LSTM decoding and connection layer classification.

[0059] Finally, according to the grammar rules of the syntax tree, each clause is parsed, the operation corresponding to each clause [SEP] is extracted, the conjunction, SELECT column and corresponding data and function, WHERE column and corresponding WHERE operator and WHERE content are filled and corrected to assemble a standardized SQL query statement and output.

[0060] The second aspect of the application provides a system for converting natural language text into SQL statements corresponding to the above method, mainly comprising:

[0061] a preprocessing module configured to obtain numerical data, keywords and word segmentation results of the natural language text as main inputs;

[0062] an expert model module configured to determine the field to which the natural language text belongs based on an expert network model, and take the field as an additional input;

[0063] a knowledge base matching module configured to select a corresponding professional knowledge base as an external dictionary based on the additional input, and perform template matching based on the external dictionary and the keywords of the natural language text to filter and generate intermediate inputs of a table name to be queried, an aggregation operation and field information to be queried;

[0064] an encoding module configured to form a splicing vector based on the numerical data, the word segmentation results of the natural language text and the intermediate inputs, input the splicing vector into a word vector matrix associated with the external dictionary for vector encoding, and obtain a query matrix, a key matrix and a value matrix;

[0065] a decoding module configured to load the query matrix, the key matrix and the value matrix into a graphics processing unit (GPU) to perform parallel calculation with matrix data converted from a query table as an attention factor state, and obtain a target column to be queried, an aggregation operation and a filtering condition after LSTM decoding and connection layer classification;

[0066] a post-processing module configured to assemble the target column to be queried, the aggregation operation and the filtering condition into a SQL query statement.

[0067] In some optional embodiments, the preprocessing module comprises:

[0068] a first input acquisition unit configured to identify and extract numerical, time and date data of a statement according to a rule to obtain a first input;

[0069] a second input acquisition unit configured to extract logical nodes of a syntax tree formed based on Chinese syntax rules to form a second input containing keywords;

[0070] a third input acquisition unit configured to split a natural language statement according to character encoding to form a third input.

[0071] In some optional embodiments, in the encoding module, constructing the splicing vector comprises adding a first flag CLS before the input text and using a second flag SEP to divide between each keyword.

[0072] In some optional embodiments, the encoding module includes adding a convolutional CNN full connection layer to further abstract semantic matrix information after extracting word vector features through a multi-layer attention mechanism, and storing the information in the form of numerical values in the memory.

[0073] In some optional embodiments, the post-processing module assembles the SQL query statement includes:

[0074] The connector, the SELECT column and the corresponding data and functions, the WHERE column and the corresponding WHERE operator and WHERE content are filled and corrected to assemble a standardized SQL query statement and output.

[0075] The following gives a specific embodiment. In this embodiment, the question "How long is the work time of the procurement department in 21?" is taken as an example to illustrate the process of processing natural language text. In addition, the database used in this embodiment is a SQL database, in which the table header column name of the annual attendance table is "year, department name, work time" in turn. The schematic diagram of the annual attendance table is shown in Figure 2 .

[0076] In addition, in this embodiment, the multi-module integrated strategy natural language to SQL model can be implemented on a single computer. The modules of the method are converted into executable model modules by programming software and are loaded in the memory. The program automatically executes the multi-module model in sequence to realize the end-to-end input of natural language text to obtain a standardized SQL query statement.

[0077] Figure 3 is the flowchart of the method of natural language to SQL statement in the embodiment of the application.

[0078] The preprocessing module (corresponding to step S1) processes the natural language text in multiple steps to facilitate the understanding and mining of the semantics of the natural language statement in subsequent steps.

[0079] 1) The numerical value, time, date and other data of the statement are processed according to the rules to obtain input 1, for example, "21 years" is standardized to "2021 years" and the like, which is consistent with the expression in the database table;

[0080] 2) A syntax tree is generated according to the grammar rules of Chinese. The key words input 2 are identified based on the logical nodes of the syntax tree. The nouns are identified according to the part-of-speech syntax tree markers, such as "procurement department, work time", and the like, and the adverbs, such as "2021 years", and the like;

[0081] 3) The natural language sentence is segmented and split according to the character encoding algorithm to obtain input 3. The character encoding is proposed in the natural language processing application to solve the problem of out-of-vocabulary words. It is widely used, for example, "2, 1, year, purchase, department, work, length, is more or less, ah".

[0082] Through the above preprocessed input results, input 1, input 2, and input 3 are obtained as data stream inputs for subsequent modules.

[0083] The expert model module (corresponding to step S2) receives the data results of the preprocessing module, input 2 passes through the expert network model, and classifies the field of natural language. It belongs to the "general" field, that is, the default initialized field word vector is taken as input 4.

[0084] The knowledge base matching module (corresponding to step S3) receives the data results of the expert model module and the preprocessing module. According to the field word vector input 4, the external dictionary of the "general" field is selected for loading. Through the string matching algorithm and the template matching of the input 2 generated by the syntax tree, the noun "purchase department, work length" input 5 is screened and generated.

[0085] The encoder module (corresponding to step S4) receives the data results of the preprocessing module, the expert model module, and the knowledge base matching module. First, the word vector matrix of the encoder dictionary part is initialized according to the field word vector input 4. Different initialization rules are selected for different fields. Second, the natural language text is processed by the above-mentioned modules to obtain multiple granularity inputs 1, 3, and 5 in combination. Among them, a special token [CLS] is added before the input text, and [SEP] is used for segmentation between each input. The special characters [CLS] and [SEP] are used to extract the semantics of each clause during training, so as to predict the operation mode of each clause. The finally spliced input is: "[CLS] 2, 1, year, purchase, department, work, length, is more or less, ah, [SEP], purchase, department, [SEP], add, work, length, [SEP], 2, 0, 2, 1, year". The above spliced input is input into the word vector 4 of the encoder BERT for word vector encoding. After extracting the features through multiple attention mechanisms, convolutional CNN is added to further abstract semantic matrix information, which is stored in the memory in the form of numerical values.

[0086] The decoder module (corresponding to step S5) receives the data result of the encoder module, loads the query matrix, key matrix and value matrix stored in the memory into the graphics processor GPU, performs parallel calculation on the matrix data converted by the query table as the attention factor state, obtains the result of each module clause after LSTM decoding and connection layer classification, and performs classification operation on [CLS] and [SEP] to predict respectively, and is divided into conds, sel and agg three parts, wherein sel is the query target column, and the value is the serial number of the corresponding column in the table; the value of agg is the aggregation operation number, and the possible aggregation operations include [“”, “MAX”, “MIN”, “COUNT”, “SUM”, “AVG”] in total 6 kinds; and conds is the filtering condition, and the possible operations include [“=”, “>”, “<”, “OP”].

[0087] The data post-processing module (corresponding to step S6) receives the data result of the decoder module, parses each clause according to the syntax tree grammar rule, extracts the operation corresponding to [SEP] of each clause, fills and corrects the connector, SELECT column and corresponding data and function, WHERE column and corresponding WHERE operator and WHERE content to assemble into a standardized SQL query statement and output, and the final parsed SQL statement result of the embodiment is “SELECT work duration FROM annual attendance table WHERE year == '2021' AND department == 'purchase department'”.

[0088] Compared with the prior art, the natural language to SQL method and model based on the multi-module integration strategy has the beneficial effects as follows:

[0089] 1. A model framework of a natural language to SQL system based on a multi-module integration strategy is built, which is top-downly disassembled into multiple modules, and a novel processing model is proposed.

[0090] 2. The expert model is used to initialize the word vector to improve the accuracy of the model, the syntax tree structure is used, and the knowledge base model is introduced to match the input sentence information, so that the operation is simple and does not depend on complex matching rule templates.

[0091] 3. The syntax tree grammar rule and LSTM are used to parse the connector, SELECT column and corresponding aggregation function of the final SQL query statement, and the WHERE column and corresponding WHERE operator, so that the accuracy of extraction is improved.

[0092] Through the strategy of integrating multiple module operation processing, reliable extraction matching and semantic analysis capability are provided, and high-quality and accurate standardized SQL statement information is generated.

[0093] The above merely provides the specific implementation of the present application, but the protection scope of the present application is not limited to this. Any person skilled in the art can easily think of the changes or replacements within the technical range disclosed by the present application, which should be covered in the protection scope of the present application. Therefore, the protection scope of the present application should be subject to the protection scope of the claims.

Claims

1. A method for converting natural language text into SQL statements, characterized in that, include: Step S1: Obtain the numerical data, keywords, and word segmentation results of the natural language text as the main input; Step S2: Determine the domain to which the natural language text belongs based on the expert network model, and use the domain as additional input; Step S3: Select the corresponding professional knowledge base as an external dictionary based on the additional input; perform template matching between the external dictionary and the keywords of the natural language text; and filter to generate intermediate inputs such as the table name to be queried, aggregation operation, and field information to be queried. Step S4: Based on the numerical data of the natural language text, the word segmentation results, and the intermediate input, a concatenated vector is formed. The concatenated vector is input into the word vector matrix associated with the external dictionary for vector encoding to obtain the query matrix, key matrix, and value matrix. The concatenated vector is processed by the multi-layer attention mechanism and fully connected layer in the BERT model encoder to obtain the query matrix, key matrix, and value matrix, and stored in memory in the form of matrix values. Step S5: Load the query matrix, key matrix, and value matrix into the graphics processing unit (GPU) and perform parallel computation with the matrix data transformed from the query table to generate attention weight factors. After LSTM decoding and connection layer classification, the query target column, aggregation operation, and filtering conditions are obtained. Step S6: Assemble the target column, aggregation operation, and filtering conditions into an SQL query statement.

2. The method for converting natural language text into SQL statements as described in claim 1, characterized in that, Step S1 further includes: The first input is obtained by identifying and extracting the numerical, time, and date data of the statement according to rules; Based on the syntax tree formed by Chinese grammar rules, its logical nodes are extracted to form a second input containing keywords; The natural language is split according to the character encoding to form a third input.

3. The method for converting natural language text into SQL statements as described in claim 1, characterized in that, In step S4, constructing the concatenation vector includes adding a first flag CLS before the input text and using a second flag SEP to separate each keyword.

4. The method for converting natural language text into SQL statements as described in claim 1, characterized in that, In step S6, assembling the SQL query statement includes: The concatenation operator, the SELECT column with its corresponding data and function, the WHERE column with its corresponding WHERE operator and WHERE content are filled, corrected and assembled into a normalized SQL query statement and then output.

5. A system for converting natural language text into SQL statements, characterized in that, include: The preprocessing module is used to obtain numerical data, keywords, and word segmentation results from the natural language text as the main input; An expert model module is used to determine the domain to which the natural language text belongs based on an expert network model, with the domain as additional input. The knowledge base matching module is used to select the corresponding professional knowledge base as an external dictionary based on the additional input, and to perform template matching between the external dictionary and the keywords of the natural language text to filter and generate intermediate inputs such as the table name to be queried, aggregation operation, and field information to be queried. The encoding module is used to form a concatenated vector based on the numerical data of the natural language text, the word segmentation results, and the intermediate input. The concatenated vector is then input into the word vector matrix associated with the external dictionary for vector encoding to obtain a query matrix, a key matrix, and a value matrix. The concatenated vector is processed by a multi-layer attention mechanism and a fully connected layer in the BERT model encoder to obtain the query matrix, key matrix, and value matrix, and stored in memory in the form of matrix values. The decoding module loads the query matrix, key matrix, and value matrix into the graphics processing unit (GPU), performs parallel computation with the matrix data transformed from the query table to generate attention weight factors, and obtains the query target column, aggregation operation, and filtering conditions after LSTM decoding and connection layer classification. The post-processing module is used to assemble the target columns, aggregation operations, and filtering conditions into an SQL query statement.

6. The system for converting natural language text into SQL statements as described in claim 5, characterized in that, The preprocessing module includes: The first input acquisition unit is used to identify and extract the numerical, time, and date data of the statement according to rules to obtain the first input; The second input acquisition unit is used to extract logical nodes based on the syntax tree formed by Chinese grammar rules to form a second input containing keywords. The third input acquisition unit is used to split the natural language statement according to the character encoding to form the third input.

7. The system for converting natural language text into SQL statements as described in claim 5, characterized in that, In the encoding module, constructing the concatenation vector includes adding a first flag CLS before the input text and using a second flag SEP to separate each keyword.

8. The system for converting natural language text into SQL statements as described in claim 5, characterized in that, The post-processing module assembles the SQL query statement, including: The concatenation operator, the SELECT column with its corresponding data and function, the WHERE column with its corresponding WHERE operator and WHERE content are filled, corrected and assembled into a normalized SQL query statement and then output.

Images