Multi-source heterogeneous data automatic fusion method based on SQL generation
By constructing heterogeneous graphs and using relational graph attention networks and LSTM networks, SQL query statements are automatically generated, and SQL query statements are solved in the existing technology, and the efficient fusion of multi-source heterogeneous data is achieved.
Patent Information
- Application Number
- CN202410082476.8
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2024-01-19
- Publication Date
- 2024-12-27
AI Technical Summary
It is difficult for the prior art to effectively generate accurate SQL query statements, especially in the multi-source heterogeneous data fusion scenario. Manually writing SQL query statements is complicated and prone to errors, and traditional methods require manual summary of experience and are inefficient.
Heterogeneous graphs are constructed based on pre-trained language models, and SQL query statements are automatically generated through the relationship graph attention network and the LSTM network to capture the relationship between natural language problems and database patterns, and improve the accuracy of SQL generation.
It improves the accuracy and efficiency of SQL generation, simplifies the multi-source data fusion process, reduces user burden, and enhances the accuracy and efficiency of query.
Smart Images
Figure CN119203018A_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the technical field of data processing methods, and in particular to an automatic fusion method for multi-source heterogeneous data generated based on SQL. Background Art
[0002] With the advent of the big data era, the amount of data in the database is becoming increasingly large and complex, requiring efficient query and analysis. Traditional database queries require the use of structured query language (SQL), but for non-professionals, learning and using SQL may be difficult. In addition, the traditional method of manually writing SQL query statements is inefficient, and the research on natural language to SQL generation (NL2SQL) aims to achieve the goal of automatically generating SQL queries, thereby improving the efficiency and convenience of database queries. At the same time, significant progress has been made in the fields of natural language processing and artificial intelligence, including machine translation, text summarization, and question-answering systems. The development of these technologies provides the foundation and motivation for NL2SQL, making it possible to convert natural language questions into SQL queries. In recent years, question-answering systems based on natural language to SQL have become a research hotspot.
[0003] The current research on NL2SQL needs to address multiple challenges such as word sense disambiguation, grammatical analysis, semantic parsing, and query generation to ensure that the generated SQL query statements can accurately express the intent of natural language questions and can be executed efficiently. Previous SQL generation methods are generally based on semantic parsing and pattern linking based on rule templates or string matching methods. Natural language is classified according to common SQL grammars, and different categories are matched with SQL templates. This method requires manual experience summary and has high time cost. In the SQL generation process, the problem words and database patterns cannot be well connected, resulting in a large gap between the generated SQL statements and the target statements. Compared with previous research methods, the SQL generation method based on deep learning and pre-trained models uses neural networks for end-to-end implementation; this method has the advantages of high accuracy and strong adaptability. It has great potential in capturing the alignment between problem words and corresponding database patterns and alleviating the disturbance caused by the ambiguity of natural language.
[0004] SQL generation for multi-source data fusion is an important research direction in the context of the big data era. With the explosive growth of data and the diversification of data sources, many organizations and enterprises need to obtain data from multiple data sources and conduct comprehensive analysis and decision support. However, there is data heterogeneity between different data sources, including differences in data structure, data format, and data semantics. This leads to a series of challenges and problems in the process of multi-source data fusion. In traditional database queries, SQL statements usually only query a single data source. However, in the scenario of multi-source data fusion, data needs to be obtained from multiple data sources and fused and analyzed. Manually writing SQL query statements involving multiple data sources is a complex and error-prone task. Therefore, researchers have begun to explore methods and technologies for automatically generating SQL query statements for multi-source data fusion. SQL generation for multi-source data fusion aims to simplify the process of multi-source data fusion by automatically generating SQL query statements. Such research can reduce the burden on users and improve the accuracy and efficiency of queries. By automatically generating SQL query statements, the system can automatically select data sources, generate query statements, and perform data fusion and result return according to the user's query requirements and the structural characteristics of multiple data sources. Summary of the invention
[0005] The technical problem to be solved by the present invention is how to provide a method for automatically merging multi-source heterogeneous data that can improve the accuracy of SQL generation and make multi-source data fusion simpler.
[0006] In order to solve the above technical problems, the technical solution adopted by the present invention is: a method for automatically fusing multi-source heterogeneous data generated based on SQL, comprising the following steps:
[0007] S1: Input all database patterns and natural language questions in the data source to be fused into the pre-trained language model, extract the relationship structure between question words and pattern items through the pre-trained language model, and construct the input heterogeneous graph based on the predefined relationship structure;
[0008] S2: Word embedding is performed on the input heterogeneous graph structure. The nodes in the heterogeneous graph are embedded using the pre-trained model to obtain the node embedding matrix. The edges in the heterogeneous graph are calculated by the syntax parser and the relationship type is determined to obtain the relationship embedding matrix.
[0009] S3: Encode the node embedding matrix and the relationship embedding matrix through the relationship graph attention network. The node graph and the relationship graph are dual graphs to capture the relationship between nodes.
[0010] S4: Based on the AST syntax tree and the vector representation of the nodes, the LSTM network is used as a decoder to decode the output vector matrix of the nodes in the relationship graph attention network, calculate the prediction results, and output the SQL statement according to the prediction results;
[0011] S5: Extract and merge multi-source heterogeneous data based on the generated SQL statement query and present the results.
[0012] The beneficial effect of adopting the above technical solution is that: the present invention captures the relationship structure between natural language questions and pattern items through a pre-trained model, constructs heterogeneous graph data, and uses a relationship-based graph encoder to allocate attention to pattern links, thereby better helping the model learn the relevant features of structured query language, which can improve the accuracy of SQL generation and make multi-source data fusion simpler. BRIEF DESCRIPTION OF THE DRAWINGS
[0013] The present invention will be further described in detail below in conjunction with the accompanying drawings and specific embodiments.
[0014] Figure 1 is a flow chart of the method described in an embodiment of the present invention;
[0015] Figure 2 It is a detailed flow chart of the method described in the embodiment of the present invention. DETAILED DESCRIPTION
[0016] The following is a clear and complete description of the technical solutions in the embodiments of the present invention in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0017] In the following description, many specific details are set forth to facilitate a full understanding of the present invention, but the present invention may also be implemented in other ways different from those described herein, and those skilled in the art may make similar generalizations without violating the connotation of the present invention. Therefore, the present invention is not limited to the specific embodiments disclosed below.
[0018] like Figure 1-Figure 2 As shown, the embodiment of the present invention discloses a method for automatically fusing multi-source heterogeneous data generated based on SQL, comprising the following steps:
[0019] S1: Input all database patterns and natural language questions in the data source to be fused into the pre-trained language model, extract the relationship structure between question words and pattern items through the pre-trained language model, and construct the input heterogeneous graph based on the predefined relationship structure;
[0020] S2: Word embedding is performed on the input heterogeneous graph structure. The nodes in the heterogeneous graph are embedded using the pre-trained model to obtain the node embedding matrix. The edges in the heterogeneous graph are calculated by the syntax parser and the relationship type is determined to obtain the relationship embedding matrix.
[0021] S3: Encode the node embedding matrix and the relationship embedding matrix through the relationship graph attention network. The node graph and the relationship graph are dual graphs to capture the relationship between nodes.
[0022] S4: Based on the AST syntax tree and the vector representation of the nodes, the LSTM network is used as a decoder to decode the output vector matrix of the nodes in the relationship graph attention network, calculate the prediction results, and output the SQL statement according to the prediction results;
[0023] S5: Extract and merge multi-source heterogeneous data based on the generated SQL statement query and present the results.
[0024] Furthermore, the step S1 includes: creating a new data source, creating a new database under the data source, importing data under the database, and first standardizing the Excel file before importing the data. The fields of the data in the imported database need to be labeled. After the labeling is completed, the database item S including the natural language question Q, the data column name C and the data table name T is compressed into an input sequence I:
[0025] I=( <s>;q1;q2;....;q|Q|;<\s>;s1;<\s>;s2;....;<\s>;s|T+C|)
[0026] Among them, |Q| represents the number of words in the natural language question Q, and |T| and |C| represent the number of data tables and columns. <s>, <\s> is a special token used to separate input tokens. The input sequence I is encoded using a pre-trained language model. First, the input sequence I is fed into the pre-trained language model. MLM maps the input I to a deep context representation, where Represent the context representation of question token and pattern item respectively.
[0027] Using the two-step MLM process, q i is replaced with a special mask tag [MASK], and the corrupted input sequence I is fed into the pre-trained model again to obtain Calculate the two MLM processes and The distance metric determines the mode item s j With question mark q i The specific distance measurement is as follows:
[0028]
[0029] After obtaining the correlation between each question token and the pattern item, the input question and pattern item are regarded as nodes, and the relationship structure between them is used as the edge to construct the input heterogeneous graph G n .
[0030] Furthermore, in step S2, for the input heterogeneous graph G n The nodes in can be embedded using PLM: First, all the question words and the names in the database tables and columns are combined into a sequence:
[0031]
[0032] Among them, the type information t i0 and c j0 Since each word is segmented into subwords before being inserted into the pattern item, an additional subword attention pooling layer is needed to obtain the representation vector of each word. After obtaining the word vectors, they are fed into three BiLSTMs according to the node type, and the graph input X0 of all nodes is obtained.
[0033] Furthermore, in step S2, for the heterogeneous graph G n The edge relations in the model can be divided into 13 abstract types using different types of relations such as question-question, question-database table, question-database column, etc. The relationship information between the question and the pattern is calculated by the syntactic dependency parser to determine whether there is a first-order relationship and embed the relationship according to the relationship type.
[0034] Furthermore, in step S2, there are k known edges in relation R, each edge represents a relation embedding, and these relation embeddings should be diverse and have different semantics. However, it is easy to get entangled in diverse learnable embeddings. By decoupling constraints, orthogonal conditions are introduced to relation R to avoid the potential risk of coupled edge embeddings during optimization.
[0035]
[0036] Where r represents the edge relationship vector between any node of the graph and another node, 1 represents a matrix with all elements set to 1, and I is the identity matrix.
[0037] Furthermore, in step S3, a graph encoder is used to encode the node embedding vector and the relationship embedding vector, and the graph encoder uses a relationship graph attention network.
[0038] Furthermore, in step S3, the encoding of the relationship graph attention network layer includes two RGATs that capture the structures of the node graph centered on the node and the relationship graph centered on the edge, respectively. n Middle: Use the edge relationship vector as the relative position encoding to obtain the edge feature between node i and node j It represents the importance of the relationship between node i and node j, where l is the number of layers of the stacked RGAT;
[0039]
[0040]
[0041]
[0042]
[0043]
[0044] in, W0 is the trainable parameter matrix, H is the number of heads in the multi-head attention, and are the output vectors of node i and node j in step S1, Return Relationship The eigenvector of , operator First, the vector is evenly divided into H parts, and the hth partition is returned. FNN(·) represents the feedforward neural network. The edge features between node i and node j are normalized to obtain the normalized attention matrix Indicates the importance of the relationship between node i and node j; uses the attention coefficient based on the normalized attention matrix to calculate the weighted average feature representation of node i's neighbor nodes Finally, through the combination of multiple attention heads (multiple independent attention mechanisms), the final node representation can be obtained After L layers of stacking, the final matrix X is obtained L The encoding process in the relation graph is similar to the node graph G e Consistent, G. e The edge features in G are provided by the node embeddings in the node graph. n The edge features in are also provided by the node features of the relationship graph.
[0045] The interaction mode between questions and databases includes any two nodes and the relationship between the two nodes. The nodes include any natural language questions, data columns and data tables. The relationship types include whether two data columns are in the same table, whether two data tables contain the same primary key, whether the data column is the primary key of the data table, whether the data table contains the data column, whether the natural language question is related to the data column or data table, and whether two natural language questions are synonymous or independent.
[0046] Furthermore, in step S4, a single-layer ordered neuron LSTM is used as an autoregressive decoder, and the encoding memory is: X = [X q ;X t ;X c ], the decoder state h0 is initialized by attention pooling over memory X.
[0047]
[0048]
[0049]
[0050] m j ,h j =ON-LSTM([a j-1 ; a pj ;h pj ;n j ],[m j-1 ;h j-1 ])
[0051] Where: v0 is a trainable row vector, W0, W1 are parameter matrices.m j is the cell state at the jth time step, a j-1 is the embedding of the previous action, a pj is the embedding of the parent action, h pj is the embedding of the parent hidden state, n j represents the type embedding of the current boundary node j. Given the current decoder state h j , a multi-head attention mechanism is used to calculate the context vector on X Combine this context vector with h j Cascade and pass it into a 2-layer MLP with tanh activation units to obtain the attention vector
[0052] Furthermore, in step S4, a graph pruning method is used as an auxiliary output method, a separate graph encoder is used for graph pruning, and another refined graph encoder is used to jointly encode the pruned pattern graph and question. The two encoders can share network parameters that are only embedded or more upper GNN layers. And constrain the text-to-SQL decoder so that it only pays attention to and retrieves the pattern items in the pruned encoding memory when calculating the attention vector and selecting columns or tables. That is, the graph pruning module and the text-to-SQL decoder are connected in a cascade manner.
[0053] The decoder of the present invention follows the syntax-based syntactic neural decoder. First, the SQL structure is regarded as an abstract syntax tree (AST). Each node in the tree is a SQL keyword (SELECT, IF, CASE, WHERE, COUNT, AND, OR...) or a candidate value of a table and a column. A series of actions are output through the LSTM network. The input of the LSTM is the output vector matrix of the nodes in the graph network. The decoding process includes:
[0054] Initialize the root node and use it as the current position. For the current position i, a vector can be used to represent c i To represent the context information of the current location, calculate the context information. i It can be calculated from the hidden state of the LSTM encoder and other contextual information.
[0055] Then, a generation rule can be used to generate the child nodes of the current position. The calculation formula of the generation rule is as follows:
[0056] P(r|c i )=softmax(W r , c i );
[0057] Among them, r represents the generation rule, W r is a learnable weight matrix, and softmax is a normalization function used to convert probability distribution into probability.
[0058] By generating rules, the probability distribution of each possible child node can be calculated. Then, sampling can be performed based on the probability distribution to select a child node as the generation result of the current position.
[0059] According to the SQL statements generated by the model, use the database management tool to execute the SQL statement operations, extract the relationships between multi-source heterogeneous data, merge them, and present them in a table.< / s> < / s>
Claims
1. A method for automatic fusion of multi-source heterogeneous data based on SQL generation, characterized in that: The following steps are involved: S1: Input all database patterns and natural language questions in the data source to be fused into the pre-trained language model, extract the relationship structure between question words and pattern items through the pre-trained language model, and construct the input heterogeneous graph based on the predefined relationship structure; S2: Word embedding is performed on the input heterogeneous graph structure. The nodes in the heterogeneous graph are embedded using the pre-trained model to obtain the node embedding matrix. The edges in the heterogeneous graph are calculated by the syntax parser and the relationship type is determined to obtain the relationship embedding matrix. S3: Encode the node embedding matrix and the relationship embedding matrix through the relationship graph attention network. The node graph and the relationship graph are dual graphs to capture the relationship between nodes. S4: Based on the AST syntax tree and the vector representation of the nodes, the LSTM network is used as a decoder to decode the output vector matrix of the nodes in the relationship graph attention network, calculate the prediction results, and output the SQL statement according to the prediction results; S5: Extract and merge multi-source heterogeneous data based on the generated SQL statement query and present the results.
2. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: The step S1 comprises: Create a new data source and a new database under the data source. Import data under the database. Before importing data, you need to standardize the Excel file first. The fields of the data in the imported database need to be labeled. After the labeling is completed, the database item S including the natural language question Q, the data column name C and the data table name T is compressed into an input sequence I, that is, I = ( <s>;q1;q2;.....;q |Q| ;<\s>;s1;<\s>;s2;....;<\s>;s |T+C| ), |Q| represents the number of words in the natural language question Q, |T| and |C| represent the number of data tables and columns, <s> , <\s> is a special token used to separate input tokens;< / s> < / s> <s> <s> The pre-trained language model is used to encode the input sequence I. First, the input sequence I is fed into the pre-trained language model. The MLM (masked language model) in the pre-trained language model maps the input sequence I to a deep context representation. Represent the context representation of question tokens and pattern items respectively, and use a two-step MLM process to transform q i is replaced with a mask token [MASK], and the corrupted input sequence I is fed into the pre-trained language model again to obtain Calculate the two MLM processes and The distance metric determines the mode item s j With question mark q i The specific distance measurement calculation formula is as follows: After obtaining the correlation between each question token and the pattern item, the input question and pattern item are regarded as nodes, and the relationship structure between them is used as the edge to construct the input heterogeneous graph G n .
3. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: In step S2, the input heterogeneous graph G n The nodes in use PLM for word embedding: First, all the question words and the names of the database tables and columns are combined into a sequence: The type information t i0 and c j0 Before being inserted into the pattern item, since each word is segmented into sub-words, an additional sub-word attention pooling layer is needed to obtain the representation vector of each word. After obtaining the word vectors, they are fed into three BiLSTMs according to the node type, and the graph input X0 of all nodes is obtained.
4. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: In step S2, the input heterogeneous graph G n The edge relationship in the question and the pattern is used to calculate the distance between the two tags through the syntactic dependency parser to determine whether there is a first-order relationship and embed the relationship according to the relationship type.
5. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: In step S2: There are k known edges in relation R, each of which represents a relation embedding. By decoupling constraints, orthogonal conditions are introduced to relation R to avoid the potential risk of coupled edge embedding during optimization: r represents the edge relationship vector between any node of the graph and another node, where 1 represents a matrix with all elements set to 1, and I is the identity matrix.
6. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: In step S3, the encoding of the relationship graph attention network includes two RGATs, which are used to capture the structure of the node graph centered on the node and the relationship graph centered on the edge, respectively. n Middle: Use the edge relationship vector as the relative position encoding to obtain the edge feature between node i and node j It represents the importance of the relationship between node i and node j, where l is the number of layers of the stacked RGAT, in, W0 is the trainable parameter matrix, H is the number of heads in the multi-head attention, and are the output vectors of node i and node j in step S1, Return Relationship The eigenvector of , operator First, the vector is evenly divided into H parts, and the hth partition is returned. FNN(·) represents the feedforward neural network. The edge features between node i and node j are normalized to obtain the normalized attention matrix Indicates the importance of the relationship between node i and node j; based on the normalized attention matrix, the attention coefficient is used to calculate the weighted average feature representation of the neighboring nodes of node i Finally, through the combination of multiple attention heads (multiple independent attention mechanisms), the final node representation can be obtained After L layers of stacking, the final matrix X is obtained L ; Encoding process in the relational graph and node graph G e Consistent, G. e The edge features in G are provided by the node embeddings in the node graph; n The edge features in are also provided by the node features of the relationship graph.
7. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: In step S4, LSTM is used as an autoregressive decoder, and the encoding memory is: X = [X q ;X t ;X c ], initialize the decoder state h0 by attention pooling over memory X; m j ,h j =ON-LSTM([a j-1 ;a pj ;h pj ;n j ],[m j-1 ;h j-1 ]) Among them, v0 is a trainable row vector, W0, W1 are parameter matrices.m j is the cell state at the jth time step, a j-1 is the embedding of the previous action, a pj is the embedding of the parent action, h pj is the embedding of the parent hidden state, n j represents the type embedding of the current boundary node j; given the current decoder state h j , a multi-head attention mechanism is used to calculate the context vector on X Combine this context vector with h j Cascade and pass it into a 2-layer MLP with tanh activation units to obtain the attention vector 8. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: In the step S4, a graph pruning method is adopted as an auxiliary output method, a separate graph encoder is used for graph pruning, and another refined graph encoder is used to jointly encode the pruned pattern graph and question; the two encoders can share network parameters that embed only or more upper GNN layers; and the text-to-SQL decoder is constrained so that it only pays attention to and retrieves the pattern items in the pruned encoding memory when calculating the attention vector and selecting columns or tables, that is, the graph pruning module and the text-to-SQL decoder are connected in a cascade manner.
9. The method for automatically fusing multi-source heterogeneous data based on SQL generation according to claim 1, characterized in that: In step S5, according to the SQL statements generated by the model, a database management tool is used to execute the operation of the SQL statements, and the relationship between the multi-source heterogeneous data is extracted for fusion and presented in a table. < / s> < / s>