Text to SQL conversion method and system
By randomly selecting the head node of the abstract syntax tree in text-to-SQL technology and gradually expanding it, the problems of poor generalization and unreasonable inference paths in the existing technology are solved. The generated SQL statements are more accurate and suitable for different tasks and data sets .
Patent Information
- Application Number
- CN202111596478.1
- Authority / Receiving Office
- CN · China
- Patent Type
- Patents(China)
- Current Assignee / Owner
- Filing Date
- 2021-12-24
- Publication Date
- 2024-12-03
- Estimated Expiration
- 2041-12-24
AI Technical Summary
There are problems in the existing text-to-SQL technology with poor generalization and unreasonable inference paths, which leads to the low quality of generated SQL statements, especially when migrating between different tasks and data sets, the model design is too refined and difficult to adapt.
By determining the node candidate set of the abstract syntax tree by using the problem text and database table columns, randomly selecting the head nodes and generating action distributions through the decoder, gradually expanding the abstract syntax tree until it cannot be expanded, avoiding overfitting, and finally generating SQL statements.
Improves the combined generalization of text to SQL decoding process, reduces overfitting, and the generated SQL statements are more accurate and are suitable for different tasks and data sets.
Smart Images

Figure CN114282497B_ABST
Abstract
Description
Technical Field
[0001] The present invention relates to the field of intelligent speech, and in particular to a method and system for converting text to SQL. Background Art
[0002] The text-to-SQL (Structured Query Language) task converts a natural language question into a corresponding SQL query given a database schema.
[0003] Usually, modular parallel decoding is adopted to design different network modules for different SQL clauses, such as classifiers, pointer networks, and sequence generation networks, and perform predictions separately. The representative model is SQLNet.
[0004] Alternatively, symbol-based end-to-end decoding can be used to directly generate each symbol in SQL, including some SQL keywords (such as SELECT and FROM). Representatives include Seq2SQL and Picard. This approach uses an end-to-end approach to solve the generation of SQL as an autoregressive sequence modeling problem.
[0005] You can also use grammar-based end-to-end decoding, the goal is to generate an equivalent grammar tree of SQL, convert the generation of the sequence into the generation of a structured grammar tree, and when the prediction is finished, restore the grammar tree to the original SQL through a post-processing transduction program. Currently, this method can be divided into two categories: one is a top-down grammar-guided generation model, such as the IRNet model, and the other is a bottom-up grammar-guided generation model, such as the SmBoP model.
[0006] In the process of implementing the present invention, the inventors found that there are at least the following problems in the related art:
[0007] Module-based parallel decoding model: It is necessary to explicitly design different modules and the way information flows and shares between them. The parallel decoding process also needs to consider the duplication and missing problems of predictions between different modules. Overly detailed model design and hyperparameter adjustment make it difficult to migrate between different tasks and datasets.
[0008] Symbol-based end-to-end model: Since there are no restrictions on the decoding process, it is easy to generate SQL statements with irregular syntax and unreasonable semantics, and a large amount of computing power is used to generate outputs that do not meet the requirements.
[0009] Grammar-based end-to-end model: The top-down tree generation process is determined by a predefined traversal order, which can easily lead to problems such as poor combinatorial generalization and unreasonable reasoning paths. The search space of the bottom-up tree generation process is too large to find the optimal spanning tree. There may even be multiple equivalent syntax trees, and it is often necessary to use an additional reordering model to score them globally and return the tree with the highest score. Summary of the invention
[0010] In order to at least solve the problem of poor generalization of text-to-SQL conversion in the prior art due to unreasonable reasoning path.
[0011] In a first aspect, an embodiment of the present invention provides a method for converting text to SQL, comprising:
[0012] Determine a node candidate set for generating an abstract syntax tree through the question text and the database table and columns;
[0013] Determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, input it into the decoder, and obtain the action distribution of the head node;
[0014] Determine multiple nodes that can be expanded at the next moment for the node at the current moment based on the action distribution, determine the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree until the next moment child node cannot be determined, and obtain a final abstract syntax tree that avoids overfitting;
[0015] The question text is converted into a corresponding SQL statement based on the final abstract syntax tree.
[0016] In a second aspect, an embodiment of the present invention provides a method for training a text-to-SQL model, comprising:
[0017] Inputting the candidate set of training nodes determined by the training question text and the database tables and columns into the text-to-SQL model;
[0018] Determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, and decode the head node using the decoder of the text-to-SQL model to obtain the action distribution of the head node;
[0019] Determine multiple nodes that can be expanded at the next moment of the node at the current moment based on the action distribution, determine the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree, input the next moment child node into the decoder again to expand the abstract syntax tree, until the child node of the next moment child node cannot be determined, and obtain an estimated abstract syntax tree;
[0020] The text-to-SQL model is trained to avoid overfitting based on the gap between the real abstract syntax tree corresponding to the training problem text and the estimated abstract syntax tree, until the estimated abstract syntax tree approaches the real abstract syntax tree.
[0021] In a third aspect, an embodiment of the present invention provides a text-to-SQL system, including:
[0022] A node candidate set determination program module, used for determining a node candidate set for generating an abstract syntax tree through question text and database tables and columns;
[0023] An action distribution determination program module is used to determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, input it to the decoder, and obtain the action distribution of the head node;
[0024] A syntax tree construction program module is used to determine multiple nodes that can be expanded at the next moment of the node at the current moment based on the action distribution, determine the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree until the next moment child node cannot be determined, so as to obtain a final abstract syntax tree that avoids overfitting;
[0025] A conversion program module is used to convert the question text into a corresponding SQL statement based on the final abstract syntax tree.
[0026] In a fourth aspect, an embodiment of the present invention provides a text-to-SQL model training system, including:
[0027] A training data input program module, used to input a candidate set of training nodes determined by training question text and database tables and columns into the text-to-SQL model;
[0028] An action distribution determination program module is used to determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, decode the head node using the decoder of the text-to-SQL model, and obtain the action distribution of the head node;
[0029] A syntax tree estimation program module, used to determine multiple nodes that can be expanded at the next moment of the node at the current moment based on the action distribution, determine the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree, and input the next moment child node into the decoder again to expand the abstract syntax tree until the child node of the next moment child node cannot be determined, so as to obtain an estimated abstract syntax tree;
[0030] A training program module is used to train the text-to-SQL model to avoid overfitting based on the gap between the real abstract syntax tree corresponding to the training problem text and the estimated abstract syntax tree, until the estimated abstract syntax tree approaches the real abstract syntax tree.
[0031] In a fifth aspect, an electronic device is provided, comprising: at least one processor, and a memory communicatively connected to the at least one processor, wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can perform the steps of the text-to-SQL and text-to-SQL model training method of any embodiment of the present invention.
[0032] In a sixth aspect, an embodiment of the present invention provides a storage medium having a computer program stored thereon, characterized in that when the program is executed by a processor, the steps of the text-to-SQL conversion and text-to-SQL model training method of any embodiment of the present invention are implemented.
[0033] The beneficial effect of the embodiments of the present invention is that in the text-to-SQL decoding process, for the top-down grammar decoding process, by treating it as a combinatorial structured set prediction problem, a certain randomness is introduced when selecting nodes at the decoding input end, and the decoder autonomously chooses the current optimal action according to the predicted output, thereby alleviating the overfitting problem caused by poor combinatorial generalization and obtaining a more accurate SQL statement. BRIEF DESCRIPTION OF THE DRAWINGS
[0034] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.
[0035] Figure 1 is a flowchart of a method for converting text to SQL provided by an embodiment of the present invention;
[0036] Figure 2A flowchart of processing a set of types at the input end of a decoder of a method for converting text to SQL provided by an embodiment of the present invention;
[0037] Figure 3 It is a flowchart of processing untyped collections at a decoder output end of a method for converting text to SQL provided by an embodiment of the present invention;
[0038] Figure 4 It is a schematic diagram of an application of an abstract syntax tree of a text-to-SQL conversion method provided by an embodiment of the present invention;
[0039] Figure 5 It is a flowchart of a training method of a text-to-SQL model provided by an embodiment of the present invention;
[0040] Figure 6 It is a main result data diagram of a data set Spider of a training method for a text-to-SQL model provided by an embodiment of the present invention;
[0041] Figure 7 It is a result data graph of a data set DuSQL of a training method for a text-to-SQL model provided by an embodiment of the present invention;
[0042] Figure 8 It is a TS and UTS evaluation data diagram of a text-to-SQL model training method provided by an embodiment of the present invention;
[0043] Fig. 9 It is a sequential curve diagram of a random vs. probe type set of a text-to-SQL model training method provided by an embodiment of the present invention;
[0044] Fig.10 It is a sequential curve diagram of a random vs non-type set of probes in a training method of a text-to-SQL model provided by an embodiment of the present invention;
[0045] Fig.11 It is a schematic diagram of an ordered path of TS of a training method for a text-to-SQL model provided by an embodiment of the present invention;
[0046] Fig.12 This is a result data diagram of a text-to-SQL model training method provided by an embodiment of the present invention when the TS sequence is fixed;
[0047] Fig.13 It is a non-type set-exploration sequential change curve diagram of a text-to-SQL model training method provided by an embodiment of the present invention;
[0048] Fig.14It is a mixed training data diagram of TS and UTS of a training method for a text-to-SQL model provided by an embodiment of the present invention;
[0049] Fig.15 It is a structural diagram of a text-to-SQL system provided by an embodiment of the present invention;
[0050] Fig.16 It is a structural diagram of a text-to-SQL model training system provided by one embodiment of the present invention;
[0051] Fig.17 A schematic diagram of the structure of an embodiment of an electronic device for converting text to SQL provided by an embodiment of the present invention. DETAILED DESCRIPTION
[0052] In order to make the purpose, technical solution and advantages of the embodiments of the present invention clearer, the technical solution in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.
[0053] like Figure 1 The flowchart of a method for converting text to SQL provided by an embodiment of the present invention includes the following steps:
[0054] S11: determining a node candidate set for generating an abstract syntax tree through the question text and the database table and column;
[0055] S12: any node randomly selected from the node candidate set is determined as the head node of the abstract syntax tree, and inputted into the decoder to obtain the action distribution of the head node;
[0056] S13: determining multiple nodes that can be expanded at the next moment for the node at the current moment based on the action distribution, determining the corresponding node selection scheme according to the node, determining the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree until the next moment child node cannot be determined, thereby obtaining a final abstract syntax tree that avoids overfitting;
[0057] S14: Convert the question text into a corresponding SQL statement based on the final abstract syntax tree.
[0058] In this embodiment, the purpose is to convert the input question text into a corresponding SQL syntax generation tree, which contains nodes and their types, as well as the action of expanding each node. Specifically, the input at each moment of decoding is the feature of the node, and the output is the action corresponding to each node, which indicates how to expand the current input node, including the type and number of child nodes to be added.
[0059] For step S11, the question text and the database table and column determine a node candidate set of the abstract syntax tree, specifically, including determining the vector representation of all words in the question text by the encoder;
[0060] Determine, by the encoder, a vector representation of the database table and a vector representation of a column;
[0061] The vector representations of all the words, the vector representations of the database table, and the vector representations of the columns are used to determine the node candidate set of the abstract syntax tree. In this embodiment,
[0062] Assume that the encoder obtains the vector representation Q of all words in the question text, the vector representation T and C of all tables and columns in the database, and the feature memory V of all extracted candidate value sets. The output of the encoder is X = [Q; T; C], which is stored in the feature memory V and accessed during decoding to calculate the attention vector.
[0063] For step S12, AST (Abstract Syntax Tree) is a tree representation of the source code. The input of the decoder is a traversal of the nodes in the AST (when gradually building the abstract syntax tree, there is only one head node at the beginning), and the output is a series of corresponding operations indicating how to expand the input node, the type and number of child nodes, such as Figure 2 As shown, the input head node is SQL, or the abstract syntax tree is the root node, and the output action ApplyRule:sql=SFW(select,from,where) is obtained, which can indicate how to expand the input node, the type and number of child nodes.
[0064] For step S13, continue with the head node determined in step S12:
[0065] (1) Select a head node n from the current part of the abstract syntax tree j To expand. (Initially there is only the head node, and as it continues to expand, the abstract syntax tree gradually expands)
[0066] (2) Process the characteristics of the node and obtain
[0067] (3) According to the type of head node selected Calculate its output action distribution
[0068] (4) Select an action a from the syntax tree j as the current goal.
[0069] (5) Apply the action a in the symbol space j Expand the abstract syntax tree and return to step (1) until there is no head node that can be expanded.
[0070] Continue as Figure 2 As shown in the figure, in the expansion, since there are three directions for expansion: select, from, and where, the corresponding node selection scheme will be determined according to the type of the node. Among them, the nodes include: typed nodes and untyped nodes;
[0071] When the node is a typed node, determining a corresponding node selection scheme according to the node, and determining a next-moment child node from the expandable multiple nodes to expand the abstract syntax tree includes:
[0072] Based on a controller scheme of a preset order or a random scheme or an exploration scheme based on enumeration, the next-moment child node is path-inferred from the expandable multiple nodes.
[0073] In this embodiment, for nodes, each node in the AST is assigned a type attribute to indicate its grammatical role. For example, a node of type sql represents the root of a complete SQL query. Other nodes with types such as from, select, or where represent different clauses with finer-grained semantics. Generally, nodes can be divided into non-terminal nodes and terminal nodes according to their type. Specifically in the task of this method, terminal types include tab_id, col_id, and val_id.
[0074] For grammar rules, each rule takes the form of type = RuleName (type1, type2, ...) (just like the ApplyRule: sql = SFW (select, from, where) in the above example). Here, type indicates the specific type of the parent non-terminal node to be expanded, and type1, type2, ... indicate the type and number of bindings of the child nodes to be attached. Only non-terminal symbol types can appear on the left, and RuleName should be globally unique to distinguish how to expand the parent node (that is, the head node of the abstract syntax tree).
[0075] With type n τ The embedding of each node n of the grammar rule r is composed of two separate embedding functions ψ(n τ) and φ(r) are provided.
[0076] For a collection of nodes with different types, such as Figure 2 As shown on the left, after the head node is input, there are three options for the output action: based on a predefined generation order, random selection, and enumeration of all possible node types.
[0077] like Figure 2 As shown on the right, if the controller solution is selected, the child node at the next moment is select, and the corresponding action distribution is: ApplyRuleAction: select = SelectTwoColumns (column, column); if the random solution is selected, the child node at the next moment is from, and the corresponding action distribution is: ApplyRuleAction: from = TreeTalbe (tab_id, tab_id, tab_id); if the exploration solution is selected, the child node at the next moment is where, and the corresponding action distribution is: ApplyRuleAction: where = OneCondition (dondition). It is preferred to adopt a random solution when selecting nodes from a typed set. In the same way, the selected node is used as the input of the encoder at the next moment to expand the next node.
[0078] As another implementation, when the node is a typeless node, determining the corresponding node selection scheme according to the node, and determining the next-moment child node from the expandable multiple nodes to expand the abstract syntax tree includes:
[0079] Based on a controller scheme of a preset order or a random scheme or an exploration scheme based on a beam search, a sub-node at the next moment is path-inferred from the expandable multiple nodes.
[0080] In this embodiment, the target is a typeless node set: at this time, the node features of different node inputs in the set are are exactly the same, the difference is the way to expand each input node, that is, the output action. There are also three schemes, named similarly to the typed collections above. For the above exploration scheme, all possible actions are selected to be added to the Beam, and according to the probability score of the model and the limit K of the Beam size, only the best K paths so far are retained to avoid exponential explosion. Figure 3As described above, the head node is input into the decoder to obtain the output action of ApplyRuleAction:from=TreeTalbe(tab_id, tab_id, tab_id). The id=2, id=3, id=4 in the marked action tree are guided, and the controller scheme, random scheme and exploration scheme are also used to obtain the output action SelectTableAction:table_id=?. The exploration scheme is adopted when selecting nodes from the preferred typeless set. In the same way, the selected node is used as the input of the encoder at the next moment to expand the next node. Thus, the abstract syntax tree is expanded, as shown in Figure 4 As shown, the final abstract syntax tree is obtained, and the SQL statement can be deduced through the abstract syntax tree.
[0081] For step S14, the node tree composed of nodes traversed from the final abstract syntax tree is decoded; an action tree composed of the action distribution corresponding to the node is obtained; and the corresponding SQL statement is determined by the action tree. An abstract syntax tree is obtained, which contains nodes and their types, as well as the actions to expand each node. Specifically, the input at each moment during decoding is the feature of the node, and the output is the action corresponding to each node, which indicates how to expand the current input node, including the type and number of child nodes to be added. The final abstract syntax tree is thus converted to SELECT MAX(col_id1)FROM tab_id1. The smart device that receives the SQL statement executes the corresponding operation.
[0082] It can be seen from this implementation that in the text-to-SQL decoding process, for the top-down grammar decoding process, by treating it as a combinatorial structured set prediction problem, a certain degree of randomness is introduced when selecting nodes at the decoding input end, and the decoder autonomously chooses the current optimal action based on the predicted output, thereby alleviating the overfitting problem caused by poor combinatorial generalization and obtaining more accurate SQL statements.
[0083] like Figure 5 The flowchart of a text-to-SQL model training method provided by an embodiment of the present invention includes the following steps:
[0084] S21: inputting the training node candidate set determined by the training question text and the database table and column into the text-to-SQL model;
[0085] S22: any node randomly selected from the node candidate set is determined as the head node of the abstract syntax tree, and the head node is decoded by using the decoder of the text-to-SQL model to obtain the action distribution of the head node;
[0086] S23: determining multiple nodes that can be expanded at the next moment of the node at the current moment based on the action distribution, determining the corresponding node selection scheme according to the node, determining the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree, and inputting the next moment child node into the decoder again to expand the abstract syntax tree until the child node of the next moment child node cannot be determined, thereby obtaining an estimated abstract syntax tree;
[0087] S24: Based on the gap between the real abstract syntax tree corresponding to the training problem text and the estimated abstract syntax tree, the text-to-SQL model is trained to avoid overfitting until the estimated abstract syntax tree approaches the real abstract syntax tree.
[0088] In this implementation, considering that a text-to-SQL model can be trained, better interaction effects can be achieved when applied to intelligent interactions.
[0089] For step S21, similarly, it is assumed that the encoder obtains the vector representation Q of all words in the question text, the vector representation T and C of all tables and columns in the database, and the feature memory V of all extracted candidate value sets. The output of the encoder is X = [Q; T; C], which is stored in the feature memory V.
[0090] For step S22, simply put, in each expansion step: select the head node n j To expand; process n j The characteristics of Depending on the type of head node selected Calculate its output action distribution Pick an action a from the syntax tree j as the current target; apply the action a in the symbol space j Expand the abstract syntax tree and return to step (1) until there is no head node that can be expanded.
[0091] Specifically, for the decoder input, the input features at time j include three parts:
[0092] (1) Previous action embedding a j-1 , which will be defined later (that is, the action distribution of the previous round) to tell the decoder how to update the AST in the neural space;
[0093] (2) Previous decoder hidden state h j-1 ;
[0094] (3) Structural Position Embeddings (SPEs) j .
[0095] Among them, Structural Position Embedding (SPE) represents the boundary node n j At the local head node of the AST. Usually, s j is the concatenation of four vectors:
[0096] (1)n j Type embedding
[0097] (2) The type of its parent node is embedded (p j Indicates n j The length of time when the parent node is expanded);
[0098] (3) The action embedding a used to expand its parent node pj ;
[0099] (4) The decoder hidden state h when its parent node is used as the frontier node pj The formula is as follows:
[0100]
[0101] Decoder hidden state h j and the attention vector Calculated as follows:
[0102]
[0103]
[0104]
[0105] Where X = [Q; T; C] is the encoder output, f dec (-, -) represents the RNN series network.
[0106] It consists of:
[0107] h0=AttentivePooling dec (X),
[0108] a0=0,
[0109] Where n0 is the root node of type sql.
[0110] For the output of the decoder, the decoder predicts an action aj at each moment j to expand the AST generated by some nodes. The overall training goal is:
[0111]
[0112] where a=(a1,...,a |a|) is a sequence of actions, and V is the feature memory mentioned in step S21. There are two types of operations, namely, APPLYRULE customized for non-terminal types and SELECTITEM customized for terminal types. In this task, an item can be a table, a column, or a value. For the sake of brevity, only the SELECTTABLE operation is discussed, and the other two operations can be inferred.
[0113] APPLYRULE operation based on non-terminal symbol type Select a rule r from the entire grammar rule set i Specifically, rule r i The left side must be equal to the boundary node n j The type of . Given an attention vector The distribution of APPLYRULE actions is given by
[0114]
[0115] SELECTTABLE action To expand the node of terminal type option id, select the SELECTTABLE action. The probability of selecting the i-th item from the table memory T is calculated by the following formula:
[0116]
[0117] You can define actions to embed a j (as decoder input for next and future steps),
[0118]
[0119] where t j is the entry selected in T, r j is the grammar rule selected at the current moment j.
[0120] For step S23, what is not described in the above steps is how to determine the boundary nodes and the output operation of each step. According to the top-down syntax, the method follows a depth-first search order in the vertical direction. After expanding a node n, switch to one of its child nodes in the next step. If all child nodes have been completed, backtrack to the parent node of node n. In the horizontal direction, try to adjust the priority.
[0121] For typed and untyped nodes, the child node sets are divided into two classes. Typed sets represent sets of nodes where each node has a different type, while untyped sets represent sets of the same type. Figure 2In the example, the rule sql=SFW(select, from, where) defines a typed set containing three tables with select, from, and where, while the rule from=ThreeTables(...) Figure 3 The table contains an untyped collection of three nodes of type tab_id.
[0122] In practice, nodes of the same type attached to a parent node usually have the same syntactic function, such as user intent in a SELECT clause. Their precedence order is instance-specific, depending on each training sample. However, type sets are more domain-general. It is assumed that there is a dominant canonical order for different examples of each type set.
[0123] like Figure 2 and Figure 3 In the GTOL (golden tree oriented learning) framework of the present method shown in FIG, the target AST is also constructed step by step during training according to the instructions of the golden action tree. In order to cope with exploration and uncertainty, a bunch of K best ASTs explored historically may be retained.
[0124] For the handle type set at the input, given a set of nodes of different types, select the boundary node (actually type) as input, and the sequence controller has three options, such as Figure 2 As shown:
[0125] Controller: Return the next unexpanded node according to some predefined order on type, e.g. select → from → where.
[0126] Random: Randomly select a type from the remaining unexpanded child nodes.
[0127] Explore: Enumerate all different types of unexpanded nodes and let the model make its own decision based on the output distribution. This choice may be delayed until several steps later, depending on the beam size K.
[0128] For the output side processing untyped sets, since the subnodes have the same type, the input SPE s j There is no difference, for example Figure 3 Nodes of type tab_id in . On the output, the golden label of each child node is available during training. All choices can be added to beams that go deep into different child nodes, ranked by prediction score and keeping the top K paths. Other alternatives such as random selection (Random) or one canonical order per untyped set per training example (Controller) are also feasible.
[0129] Through the above method, the overall estimated abstract syntax tree is obtained.
[0130] For step S24, since the training data is prepared in advance and the corresponding real abstract syntax tree is also prepared, the model can be reversely trained using the estimated and real errors. There are other training methods, which are not limited here. Until the estimated abstract syntax tree approaches the real abstract syntax tree, a trained text-to-SQL model is obtained.
[0131] It can be seen from this implementation that for the top-down syntax decoding process, by treating it as a combinatorial structured set prediction problem, a certain degree of randomness is introduced when selecting nodes at the decoding input end, and the decoder autonomously chooses the current optimal action based on the predicted output, so as to alleviate the problem of training overfitting caused by poor combinatorial generalization. At the same time, in order to solve the problem that the traversal path of the abstract syntax tree is not enumerable, the idea of the cluster decoding strategy is borrowed to maintain an optimal set of historical paths and increase the exploration space of the training process. Obtaining more accurate estimated SQL is not only applicable to the field of text-to-SQL, but can also be migrated to other structured decoding tasks.
[0132] The above method is tested and described, and the evaluation indicators are as follows:
[0133] Unvalued Exact Set Matching (EM) This metric measures the equivalence of two SQL queries by comparing each component. The prediction is correct only if each fine-grained unit is correct. Order issues are ignored, such as SELECT col1, col2 = SELECT col2, col1. However, EM only checks the sketch and ignores the values.
[0134] Exact Set and Value Matching (EMV) Based on EM, this metric further checks the correctness of SQL values without actually executing it. It may be too strict because different SQL queries may be semantically consistent.
[0135] Execution Accuracy (EX) It measures the accuracy by comparing the execution results of two SQL queries. To mitigate the problems caused by pseudo procedures, where the predicted SQL is executed and checked on multiple databases, this setting is followed and the results are reported accordingly.
[0136] The dataset used is Spider, which is a large-scale cross-domain zero-shot text-to-SQL benchmark containing 8659 training examples and 1034 validation examples across 146 databases. The test dataset is unseen and contains 2147 samples and 40 databases. The model is submitted to the challenge organizers for evaluation. EM and EX are reported in the experiments. DuSQL is a large-scale practical cross-domain Chinese dataset for zero-shot text-to-SQL tasks. There are 22521 / 2482 training / validation examples in 177 databases. Participants only have access to the input of 3759 test samples containing 23 databases, and the output SQL is not available. EM and EMV are used to evaluate the models of this method.
[0137] For the experimental configuration, a 300-dim word embedding model and PLM (pre-trained language model) are used for Spider, and the default beam size of the framework in the word embedding model during training is 4. For evaluation, if there is a sequence that regulates each type set, it will be followed directly. Otherwise, the exploration method is used to expand the search space.
[0138] like Figure 6 The main results on the Spider dataset are shown in Figure 2. The hearts in spades indicate that the accuracy is borrowed from the original code link because the leaderboard is not shown. Figure 7 The main results of the dataset DuSQL are shown. The proposed framework achieves significant improvements on DuSQL, surpassing the previous state-of-the-art data augmentation method (+Aug) by 15:9% on the metric EMV. As for the dataset Spider, it achieves competitive performance on the development set, only lower than the PICARD model using extremely large PLM.
[0139] The performance of different sorting methods for typed / untyped sets and their combinations is also analyzed. For brevity, in the rest of the article, capital letters TS are used to represent Typed Set, UTS to represent UnTyped Set, and C / R / P to represent Controller, Random, and Probe, respectively. All results of this method are averaged over at least three random seeds to reduce randomness. For time and memory space considerations, we use GLOVE (a language model based on a neural network language model) vectors for the Spider dataset.
[0140] For the same order, first, the sorting methods of TS and UTS are unified, such as Figure 8Evaluation of TS and UTS shown. Crosses indicate evaluation on the test set, otherwise on the development set. For the C (controller abbreviation above) method, a canonical order is randomly sampled for each TS, which is tightly coupled to a grammar rule, and a canonical order is randomly sampled for each UTS for each training example. Subsequently, these orders are fixed throughout the training process.
[0141] According to Figure 8 As shown: (1) Method R, which randomly selects a specific order in each iteration during training, outperforms other methods. (2) Fine-tuning the pre-trained model using reinforcement learning (+RL) only achieves limited improvement over the baseline TS-C+UTS+C. (3) Disappointingly, the performance of method P, which leaves the CS-Seq2Set problem to the model itself, degrades significantly. To find out why, Fig. 9 and Fig.10 The training curves of methods P and R are plotted in . For R, after each training epoch, the model is run using method P in evaluation mode to plot the order changes. It records the number of samples whose order has changed compared to the previous epoch. The cumulative amount is further divided by TS and UTS. By comparing the curves, the following conclusions can be drawn: (1) The R model converges quickly after iterations. (2) This is not the case for P, especially for TS. It is observed that each training sample selects a custom order for the same set of types. However, they never reach a consensus. This interaction leads to oscillations between several orders for each training sample, and the optimal ergodic order never converges. This phenomenon can be called "order collapse".
[0142] To address the above issues, a canonical order (TS-C) was specified for each TS before training, and only the order method of UTS was changed. For the priority order of TS, a more heuristic method was used: in evaluation mode, the saved TS-R+UTS-R model was run again on the training set instead of randomly sampling an order for each TS. By counting the number of times each order of TS appears, we select the one with the largest frequency. Fig.11 Some of the canonical prediction sequences are listed.
[0143] According to Fig.12 As shown in Figure 2, the most exciting observation is that when the order of each TS is fixed in the sample, UTS-P starts to outperform R. When the training curves are plotted again, UTS eventually converges and parses the last segment with TS-C (as shown in Figure 2). Fig.13 ). Even with a beam size of 1 during training, it consistently outperforms UTS-R on the cross axis. Increasing the size (=4) leads to even better results. The irregular fluctuations on the DuSQL development set can be attributed to the limited size of its average UTS.
[0144] A more aggressive attempt is to combine the advantages of TS's method R and UTS's method P. The results are as follows Fig.14 Compared with the baseline TS-C+UTS-C, this combination provides the best performance so far, achieving 4:1 / 4:6 EM / EX improvement on Spider and 2:2 / 2:1 EM / EMV improvement on DuSQL. This is also the strategy adopted in the main results of this method.
[0145] In this method, a golden tree-oriented learning framework is proposed, and the top-down grammar-based text-to-SQL task is set as a combined structure sequence. Through experiments, randomly selecting nodes from the typed set as decoder input and letting the model select output actions from the untyped set based on their predictions will be an empirical criterion to improve the training effect.
[0146] like Fig.15 The figure is a schematic diagram of the structure of a text-to-SQL system provided by an embodiment of the present invention. The system can execute the text-to-SQL method described in any of the above embodiments and is configured in a terminal.
[0147] The text-to-SQL system 10 provided in this embodiment includes: a node candidate set determination program module 11 , an action distribution determination program module 12 , a syntax tree construction program module 13 and a conversion program module 14 .
[0148] Among them, the node candidate set determination program module 11 is used to determine the node candidate set for generating an abstract syntax tree through the question text and the database tables and columns; the action distribution determination program module 12 is used to determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, input it to the decoder, and obtain the action distribution of the head node; the syntax tree construction program module 13 is used to determine the multiple nodes that can be expanded at the next moment based on the action distribution of the node at the current moment, judge the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree until the child node at the next moment cannot be determined, and obtain the final abstract syntax tree that avoids overfitting; the conversion program module 14 is used to convert the question text into the corresponding SQL statement based on the final abstract syntax tree.
[0149] The embodiment of the present invention further provides a non-volatile computer storage medium, the computer storage medium stores computer executable instructions, and the computer executable instructions can execute the text-to-SQL method in any of the above method embodiments;
[0150] As an implementation mode, the non-volatile computer storage medium of the present invention stores computer executable instructions, and the computer executable instructions are configured as follows:
[0151] Determine a node candidate set for generating an abstract syntax tree through the question text and the database table and columns;
[0152] Determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, input it into the decoder, and obtain the action distribution of the head node;
[0153] Determine multiple nodes that can be expanded at the next moment for the node at the current moment based on the action distribution, determine the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree until the next moment child node cannot be determined, and obtain a final abstract syntax tree that avoids overfitting;
[0154] The question text is converted into a corresponding SQL statement based on the final abstract syntax tree.
[0155] like Fig.16 The figure is a schematic diagram of the structure of a text-to-SQL model training system provided by an embodiment of the present invention. The system can execute the text-to-SQL model training method described in any of the above embodiments and be configured in a terminal.
[0156] A text-to-SQL model training system 20 provided in this embodiment includes: a training data input program module 21, an action distribution determination program module 22, a syntax tree estimation program module 23 and a training program module 24.
[0157] Among them, the training data input program module 21 is used to input the training node candidate set determined by the training problem text and the database table and column into the text-to-SQL model; the action distribution determination program module 12 is used to determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, and use the decoder of the text-to-SQL model to decode the head node to obtain the action distribution of the head node; the syntax tree estimation program module 23 is used to determine multiple nodes that can be expanded at the next moment based on the action distribution of the node at the current moment, determine the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree, and input the next moment child node into the decoder again to expand the abstract syntax tree until the child node of the next moment child node cannot be determined, so as to obtain the estimated abstract syntax tree; the training program module 24 is used to train the text-to-SQL model to avoid overfitting based on the gap between the real abstract syntax tree corresponding to the training problem text and the estimated abstract syntax tree, until the estimated abstract syntax tree approaches the real abstract syntax tree.
[0158] The embodiment of the present invention further provides a non-volatile computer storage medium, the computer storage medium stores computer executable instructions, and the computer executable instructions can execute the text-to-SQL model training method in any of the above method embodiments;
[0159] As an implementation mode, the non-volatile computer storage medium of the present invention stores computer executable instructions, and the computer executable instructions are configured as follows:
[0160] Inputting the candidate set of training nodes determined by the training question text and the database tables and columns into the text-to-SQL model;
[0161] Determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, and decode the head node using the decoder of the text-to-SQL model to obtain the action distribution of the head node;
[0162] Determine multiple nodes that can be expanded at the next moment of the node at the current moment based on the action distribution, determine the corresponding node selection scheme according to the node, determine the next moment child node and the corresponding action distribution from the multiple expandable nodes to expand the abstract syntax tree, input the next moment child node into the decoder again to expand the abstract syntax tree, until the child node of the next moment child node cannot be determined, and obtain an estimated abstract syntax tree;
[0163] The text-to-SQL model is trained to avoid overfitting based on the gap between the real abstract syntax tree corresponding to the training problem text and the estimated abstract syntax tree, until the estimated abstract syntax tree approaches the real abstract syntax tree.
[0164] As a non-volatile computer-readable storage medium, it can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions / modules corresponding to the method in the embodiment of the present invention. One or more program instructions are stored in the non-volatile computer-readable storage medium, and when executed by the processor, the text-to-SQL method and the text-to-SQL model training method in any of the above method embodiments are executed.
[0165] Fig.17 is a schematic diagram of the hardware structure of an electronic device of a text-to-SQL method and a text-to-SQL model training method provided in another embodiment of the present application, such as Fig.17 As shown, the device includes:
[0166] One or more processors 1710 and memory 1720, Fig.17 A processor 1710 is taken as an example. The device of the text-to-SQL method may further include: an input device 1730 and an output device 1740.
[0167] The processor 1710, the memory 1720, the input device 1730 and the output device 1740 may be connected via a bus or other means. Fig.17 The example of connecting through bus is taken in the following.
[0168] The memory 1720, as a non-volatile computer-readable storage medium, can be used to store non-volatile software programs, non-volatile computer executable programs and modules, such as program instructions / modules corresponding to the text-to-SQL method and the text-to-SQL model training method in the embodiments of the present application. The processor 1710 executes various functional applications and data processing of the server by running the non-volatile software programs, instructions and modules stored in the memory 1720, that is, the text-to-SQL method and the text-to-SQL model training method in the above method embodiments are implemented.
[0169] The memory 1720 may include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data, etc. In addition, the memory 1720 may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the memory 1720 may optionally include a memory remotely arranged relative to the processor 1710, and these remote memories may be connected to the mobile device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.
[0170] The input device 1730 may receive input digital or character information. The output device 1740 may include a display device such as a display screen.
[0171] The one or more modules are stored in the memory 1720, and when executed by the one or more processors 1710, the text-to-SQL method and the text-to-SQL model training method in any of the above method embodiments are executed.
[0172] The above-mentioned product can execute the method provided in the embodiment of the present application, and has the functional modules and beneficial effects corresponding to the execution method. For technical details not fully described in this embodiment, please refer to the method provided in the embodiment of the present application.
[0173] The non-volatile computer-readable storage medium may include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data created according to the use of the device, etc. In addition, the non-volatile computer-readable storage medium may include a high-speed random access memory, and may also include a non-volatile memory, such as at least one disk storage device, a flash memory device, or other non-volatile solid-state storage device. In some embodiments, the non-volatile computer-readable storage medium may optionally include a memory remotely arranged relative to the processor, and these remote memories may be connected to the device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.
[0174] An embodiment of the present invention also provides an electronic device, comprising: at least one processor, and a memory communicatively connected to the at least one processor, wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor so that the at least one processor can execute the steps of the text-to-SQL method and the text-to-SQL model training method of any embodiment of the present invention.
[0175] The electronic device of the embodiment of the present application exists in various forms, including but not limited to:
[0176] (1) Mobile communication equipment: This type of equipment is characterized by having mobile communication functions and its main purpose is to provide voice and data communications. This type of terminal includes: smart phones, multimedia phones, functional phones, and low-end phones.
[0177] (2) Ultra-mobile personal computer devices: These devices fall into the category of personal computers, have computing and processing capabilities, and generally also have mobile Internet access features. These terminals include: PDAs, MIDs, and UMPC devices, such as tablet computers.
[0178] (3) Portable entertainment devices: These devices can display and play multimedia content. They include audio and video players, handheld game consoles, e-books, smart toys, and portable car navigation devices.
[0179] (4) Other electronic devices with data processing functions.
[0180] In this article, relational terms such as first and second, etc. are used only to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include" and "comprise" include not only those elements, but also other elements not explicitly listed, or also include elements inherent to such processes, methods, articles or equipment. In the absence of further restrictions, the elements defined by the statement "include..." do not exclude the existence of other identical elements in the process, method, article or equipment that includes the elements.
[0181] The device embodiments described above are merely illustrative, wherein the units described as separate components may or may not be physically separated, and the components displayed as units may or may not be physical units, that is, they may be located in one place, or they may be distributed on multiple network units. Some or all of the modules may be selected according to actual needs to achieve the purpose of the scheme of this embodiment. Ordinary technicians in this field can understand and implement it without paying creative labor.
[0182] Through the description of the above implementation methods, those skilled in the art can clearly understand that each implementation method can be implemented by means of software plus a necessary general hardware platform, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solution is essentially or the part that contributes to the prior art can be embodied in the form of a software product, and the computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, a disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a server, or a network device, etc.) to execute the methods described in each embodiment or some parts of the embodiments.
[0183] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it. Although the present invention has been described in detail with reference to the aforementioned embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the aforementioned embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.
Claims
1. A method of converting text to SQL. include: Determining a node candidate set for generating an abstract syntax tree through a question text and a database table and column, including: determining vector representations of all words in the question text by an encoder; determining vector representations of the database table and vector representations of columns by the encoder; determining a node candidate set of an abstract syntax tree by using the vector representations of all words, the vector representations of the database table and the vector representations of columns; Determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, input it into the decoder, and obtain the action distribution of the head node; Determine multiple nodes that can be expanded by the node at the current moment at the next moment based on the action distribution, wherein the nodes include: typed nodes and untyped nodes; When the node is a typed node, a controller scheme based on a preset order, a random scheme, or an enumeration-based exploration scheme is used to infer the next-moment child node from the expandable multiple nodes. When the node is a typeless node, a controller scheme based on a preset order, a random scheme, or an exploration scheme based on a beam search is used to infer the next-moment child node from the expandable multiple nodes. Expanding the abstract syntax tree from the next-moment child nodes and the corresponding action distribution until the next-moment child nodes cannot be determined, thereby obtaining a final abstract syntax tree that avoids overfitting; The question text is converted into a corresponding SQL statement based on the final abstract syntax tree.
2. The method according to claim 1, in, The method further comprises: When the node is a typed node, a next-time child node is inferred from the expandable multiple nodes based on a random solution.
3. The method according to claim 1, in, The method further comprises: When the node is a typeless node, a beam search-based exploration scheme is used to path-infer the next-moment child node from the expandable multiple nodes.
4. The method according to claim 1, in, The converting the question text into a corresponding SQL statement based on the final abstract syntax tree comprises: Decoding a node tree consisting of nodes traversed from the final abstract syntax tree; Obtaining an action tree consisting of action distribution corresponding to the node; The corresponding SQL statement is determined through the action tree.
5. A training method for a text-to-SQL model, include: Inputting the training node candidate set determined by training the question text and the database table and column into the text-to-SQL model, wherein the training node candidate set includes: determining the vector representation of all words in the question text by an encoder; determining the vector representation of the database table and the vector representation of the column by the encoder; determining the node candidate set of the abstract syntax tree by the vector representation of all words, the vector representation of the database table and the vector representation of the column; Determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, and decode the head node using the decoder of the text-to-SQL model to obtain the action distribution of the head node; Determine multiple nodes that can be expanded by the node at the current moment at the next moment based on the action distribution, wherein the nodes include: typed nodes and untyped nodes; When the node is a typed node, a controller scheme based on a preset order, a random scheme, or an enumeration-based exploration scheme is used to infer the next-moment child node from the expandable multiple nodes. When the node is a typeless node, a controller scheme based on a preset order, a random scheme, or an exploration scheme based on a beam search is used to infer the next-moment child node from the expandable multiple nodes. Expanding the abstract syntax tree from the next-moment child node and the corresponding action distribution, inputting the next-moment child node into the decoder again to expand the abstract syntax tree until the child node of the next-moment child node cannot be determined, thereby obtaining an estimated abstract syntax tree; The text-to-SQL model is trained to avoid overfitting based on the gap between the real abstract syntax tree corresponding to the training problem text and the estimated abstract syntax tree, until the estimated abstract syntax tree approaches the real abstract syntax tree.
6. A text-to-SQL system, include: A node candidate set determination program module is used to determine a node candidate set for generating an abstract syntax tree through a question text and a database table and column, including: determining the vector representation of all words in the question text by an encoder; determining the vector representation of the database table and the vector representation of the column by the encoder; determining the node candidate set of the abstract syntax tree by the vector representation of all words, the vector representation of the database table and the vector representation of the column; An action distribution determination program module is used to determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, input it to the decoder, and obtain the action distribution of the head node; A syntax tree construction program module, used to determine multiple nodes that can be expanded from the node at the current moment at the next moment based on the action distribution, wherein the nodes include: typed nodes and untyped nodes; When the node is a typed node, a controller scheme based on a preset order, a random scheme, or an enumeration-based exploration scheme is used to infer the next-moment child node from the expandable multiple nodes. When the node is a typeless node, a controller scheme based on a preset order, a random scheme, or an exploration scheme based on a beam search is used to infer the next-moment child node from the expandable multiple nodes. Expanding the abstract syntax tree from the next-moment child nodes and the corresponding action distribution until the next-moment child nodes cannot be determined, thereby obtaining a final abstract syntax tree that avoids overfitting; A conversion program module is used to convert the question text into a corresponding SQL statement based on the final abstract syntax tree.
7. A training system for text-to-SQL models, include: A training data input program module is used to input the training node candidate set determined by the training question text and the database table and column into the text-to-SQL model, wherein the training node candidate set includes: determining the vector representation of all words in the question text by the encoder; determining the vector representation of the database table and the vector representation of the column by the encoder; determining the node candidate set of the abstract syntax tree by the vector representation of all words, the vector representation of the database table and the vector representation of the column; An action distribution determination program module is used to determine any node randomly selected from the node candidate set as the head node of the abstract syntax tree, decode the head node using the decoder of the text-to-SQL model, and obtain the action distribution of the head node; A syntax tree prediction program module, used to determine multiple nodes that can be expanded from the node at the current moment at the next moment based on the action distribution, wherein the nodes include: typed nodes and untyped nodes; When the node is a typed node, a controller scheme based on a preset order, a random scheme, or an enumeration-based exploration scheme is used to infer the next-moment child node from the expandable multiple nodes. When the node is a typeless node, a controller scheme based on a preset order, a random scheme, or an exploration scheme based on a beam search is used to infer the next-moment child node from the expandable multiple nodes. Expanding the abstract syntax tree from the next-moment child node and the corresponding action distribution, inputting the next-moment child node into the decoder again to expand the abstract syntax tree until the child node of the next-moment child node cannot be determined, thereby obtaining an estimated abstract syntax tree; A training program module is used to train the text-to-SQL model to avoid overfitting based on the gap between the real abstract syntax tree corresponding to the training problem text and the estimated abstract syntax tree, until the estimated abstract syntax tree approaches the real abstract syntax tree.
8. An electronic device, wherein include: At least one processor, and a memory communicatively connected to the at least one processor, wherein the memory stores instructions executable by the at least one processor, and the instructions are executed by the at least one processor to enable the at least one processor to perform the steps of the method described in any one of claims 1 to 5.
9. A storage medium having a computer program stored thereon, It is characterized in that When the program is executed by a processor, the steps of the method described in any one of claims 1 to 5 are implemented.
Citation Information
Cited By
Text-to-SQL method and system based on context awareness
CN121935270A
