A Survey on Text-to-SQL Parsing: Concepts, Methods, and Future Directions

We treat the predicted SQL query as correct only if all of the SQL clauses are correct by a set comparison as follows:

where $\hat{Y}=\left\{\right({\hat{k}}^i,{\hat{v}}^i),i\in (1,m\left)\right\}$ and $Y=\left\{\right(k^i,v^i),i\in (1,m\left)\right\}$ denote the component sets of the predicted SQL query and the ground-truth query respectively. Here, $k$ stands for a SQL clause and $v$ is the corresponding value of the clause. $m$ is the number of components. Formally, the exact set match accuracy is calculated by:

where $N$ denotes the total number of samples. EM evaluates the model performance by strictly comparing differences in SQL, but human’s SQL annotations are often biased since a NL question may correspond to multiple SQL queries.

Given a multi-turn setting, there are a total of $P$ question sequences, where each sequence contains $O$ rounds and a total of $M=P\times O$ questions.

As stated in Section 2.1, there are two types of input information to be considered for text-to-SQL parsing: the NL question and the table schemas, which are jointly represented by the encoder. Generally, the input representation learning methods can be divided into two primary categories, including LSTM-based [26, 57] and Transformer-based [69] methods.

First, the NL question and the database schema are concatenated and taken as the integrated input sequence of the encoder. Formally, the input sequence can be formulated as $I=(\left[CLS\right];q_1;\dots ;q_{|Q|};\left[SEP\right];s_1;\left[SEP\right];\dots ;\left[SEP\right];s_{|T|+|C|})$, where $\left[CLS\right]$ and $\left[SEP\right]$ indicate the pre-defined special tokens as in [15]. The input sequence can be extended to the multi-turn setting by sequentially concatenating current questions, dialog history and schema items [20].

Second, the pre-trained language models (PLMs) such as BERT [15] and RoBERTa [44] can significantly boost parsing accuracy by enhancing the generalization of the encoder and capturing long-term token dependencies. In general, for question tokens, the output hidden states from the final layer of the Transformer block in the BERT [15] or RoBERTa models are considered as the contextualized representations of question tokens. For each database schema item, the output hidden state of its front special token $\left[SEP\right]$ is regarded as the table or column header representation.

Third, leveraging flexible neural architecture on the top of PLMs can further enhance the encoder’s output representations with strong expressive ability. For example, SQLova [31] and SDSQL [30] further stack two Bi-LSTM layers on the top of output representations of BERT [15]. GAZP [100] proposes an additional self-attention layer [69] on the top of a Bi-LSTM layer to compute the intermediate representations. RYANSQL [12] sequentially employs the convolutional neural network [1] with dense connection [87] and a scaled dot-product attention layer [69] on the top of BERT [15] to align question tokens with columns. It is noteworthy that the parameters of convolutional neural network are shared across the NL question and columns. BRIDGE [40] encodes the input sequence with BERT [15] and lightweight subsequent layers (i.e., two Bi-LSTM layers). In addition, dense look-up features are applied to represent meta-data information of the table schema such as primary key, foreign key and datatype. These meta-data features are further fused with the BERT [15] encoding of the schema component via a feed-forward layer.

The development of large cross-domain datasets such as WikiSQL [101] and Spider [93] results in the realistic generalization challenge to deal with unseen table schemas. Each NL question corresponds to a multi-table database schema. The training and testing sets do not share overlapped databases. The challenge of the generalization requires the text-to-SQL parsing methods to encode the NL question and the table schema into representations with powerful expressive ability from three aspects.

• First, the encoder should be able to recognize NL tokens used to refer to tables and columns either implicitly or explicitly, which is called schema linking structure – aligning entity mentions in the NL question to the mentioned schema tables or columns.

• Second, the encoded representations should be aware of the schema structure information such as the primary keys, the foreign keys, and the column types.

• Third, the encoder should be able to perceive complex variations in the NL question, i.e., question structure.

Graph are the best form of data to express the complex structure in the text-to-SQL parsing task. Recently, several graph-based methods [4, 71, 8] have been proposed to reason over the NL question tokens and schema entities, and model the complex input representation. These methods consider the NL question tokens and schema items as multi-typed nodes, and the structural relations (edges) among the nodes can be pre-defined to express diverse intra-schema relations, question-schema relations and intra-question relations.

Recently, the graph-based linking approaches [71, 55, 27, 11, 8] have been proposed to reason over the NL question tokens and schema entities, and model the complex input representation. These methods consider the NL question tokens and schema items as multi-typed nodes, and the structural relations (edges) among the nodes can be pre-defined to express diverse intra-schema relations, question-schema relations and intra-question relations. In particular, in RATSQL [71], the graph is constructed based on two kinds of relations (i.e., name-based linking and value-based linking), where the name-based linking refers to partial or exact occurrences of table/column names in the same NL question and the value-based linking refers to the question-schema alignment that occurs when the NL question mentions any values appearing in the schema and the desired SQL. The relation-aware self-attention mechanism [61] is then proposed for graph representation learning, which exploits global reasoning over the constructed graph. Specifically, given a sequence of token representations, the relational-aware self-attention computes a scalar similarity score between each pair of token representations $e_{ij}\propto u_i{W}_Q(u_j{W}_K+r_{ij}^K)$, where $u_i$ and $u_j$ denote the graph nodes, and the term $r_{ij}^K$ denotes an embedding that represents a relation between $u_i$ and $u_j$ from a closed set of possible relations. To enhance the model generalization capability for unseen or rare schemas, ShadowGNN [11] alleviates the impact of the domain information by abstracting the representations of the NL question and the SQL query before applying the relation-aware graph computation [71]. In addition, several works have been devoted to tackle the challenge of heterogeneous graph encoding for the text-to-SQL parsing. LGESQL [8] constructs an edge-centric graph from the node-centric graph as in RATSQL [71], which explicitly considers the topological structure of edges. The information propagates more efficiently by considering both the connections between nodes and the topology of directed edges. Two relational graph attention networks (RGANs) [75] are devised to model the structure of the node-centric graph and the edge-centric graph respectively, mapping the input heterogeneous graph into token representations.

The sketch-based methods decompose the SQL generation procedure into sub-modules, e.g., SELECT column, AGG function, WHERE value. For example, SQLNet [82] employs the SQL sketch. The tokens SELECT, WHERE and AND indicate the SQL keywords, and the following components indicate the types of prediction slots to be filled. For example, the AGG slot indicates the slot to be filled with either an empty token or one of the aggregation operators such as SUM and MAX; the VALUE slot needs to be filled with a sub-string of the question; the COLUMN slot needs to be filled with a column name; the OP slot needs to be filled with operations such as $>$, $<$, $=$). These slots are later gathered together and interpreted to generate the final SQL query. Each slot has separate model which does not share their trainable parameters and is responsible for predicting a part of the final SQL independently. Specifically, for the COLUMN slot, a column attention mechanism [82] is applied to reflect the most relevant information in NL questions when prediction is made on a particular column. For the OP slot, predicting its value is a 3-way classification task ($>$, $<$, $=$). For the VALUE slot, [82] employs a Seq2Seq structure to generate the sub-string of the NL question.

SQLova [31] and SDSQL [30] modify the syntax-guided sketch used in [82]. The proposed sketch-based decoder consists of six prediction modules, including WHERE-NUMBER, WHERE-COLUMN, SELECT-COLUMN, SELECT-AGGREGATION, WHERE-OPERATOR, and WHERE-VALUE. The specific role of each action is described as follows:

• SELECT-COLUMN identifies the column in “SELECT” clause from the given NL question.

• SELECT-AGGREGATION identifies the aggregation operator for the given select-column prediction.

• WHERE-NUMBER predicts the number of “WHERE” conditions in SQL queries.

• WHERE-COLUMN calculates the probability of generating each columns for the given NL question.

• WHERE-OPERATOR identifies the most probable operators given where-column prediction among three possible choices ($>$, =, $<$).

• WHERE-VALUE identifies which tokens of a NL question correspond to condition values for the given “WHERE” columns.

In the SQL query generation stage, an execution-guided decoding strategy [73] is utilized to exclude the non-executable partial SQL queries from the output candidates. TypeSQL [88] further improves the above approach by declining the number of modules. TypeSQL chooses to combine the select-column module and the where-column module into a single module since their prediction procedures are similar, and the where-column module depends on the output of the select-column module. In addition, the where-operator and where-value modules are combined together because the predictions of these two modules depend on the outputs of the where-column module. Generally, the sketch-based approaches are fast and guaranteed to conform to correct SQL syntax rules. However, it is difficult for these approaches to handle complex SQL statements such as multi-table JOINs, nested queries, and so on. Thus, the sketch-based approaches are popular on the WikiSQL [101] dataset, but are difficult to be applied on the Spider [93] dataset which involves complex SQLs. Only RYANSQL [12] implements complex SQL generation by recursively applying the sketch method.

On the other hand, the generation-based approaches are based on the Seq2Seq model to decode SQL, which are more preferable for complex SQL scenarios than the sketch-based approaches. For example, Bridge [40] uses a LSTM-based pointer-generator [58] with multi-head attention and copy mechanism as the decoder which is initiated with the final state of the encoder. At each decoding step, the decoder performs one of the following actions: generating a token from the vocabulary $V$, copying a token from the question $Q$, or copying a schema component from the database schema $S$.

Since the above generation-based approaches may not generate SQL queries with correct grammar, some advanced methods [22, 71] generate the SQL as an abstract syntax tree (AST) [74] in the depth-first traversal order [68]. In particular, these methods employ an LSTM decoder to perform a sequence of three types of actions that either expand the last generated node into a grammar rule, called APPLY-RULE action or choose a column/table from the schema when completing a leaf node, called SELECT-COLUMN action and SELECT-TABLE action respectively. These actions can construct the corresponding AST of the target SQL query. Specifically, APPLY-RULE applies a production rule to the current derivation tree of a SQL query and expands the last generated node with the grammar rule. The probability distribution is computed by a softmax classification layer for the pre-defined abstract syntax description language (ASDL) rules. SELECT-COLUMN and SELECT-TABLE complete a leaf node by selecting a column $c$ or a table $t$ from the database schema respectively by directly copying the table and column names from database schema via copy mechanism [58].

There are also several works [81, 60, 83] which neglect the SQL grammar during the decoding process, by leveraging the powerful large scale pre-trained language model like T5 [53] finetuned on the text-to-SQL training set for SQL query generation. Formally, the transformer-based decoder follows the standard text generation process and produces the hidden state in step $t$ for generating the $t$-th token as described in [69]. An affine transformation is then applied on the learned hidden state to obtain prediction probability over the target vocabulary $V$ for each word. In addition, as revealed in [34], for some cases, although the best generated SQL is in the candidate list of beam search, it is not at the top of the candidate list. Therefore, a discriminative re-ranker (Re-Ranking) strategy is introduced to extract the best SQL query from the candidate list predicted by the text-to-SQL parser. The re-ranker is constructed as a BERT fine-tuned classifier that is independent of schema, and the probability of the classifier is utilized as the score for the query to re-rank. Some studies [22, 11] introduce a domain specific language (DSL) serving as an intermediate representation to bridge the NL question and the SQL query. The methods reveal that there is a invariable mismatch between the intentions conveyed in natural language and the implementation details in SQL when the resulting SQL is processed to tree-structured form. Hence, the key idea of DSL is to omit the implementation details of the intermediate representations. IRNet [22] presents a grammar-based neural model to generate a SemQL query as the intermediate representation bridging the NL question and the SQL query, and a SQL query is then inferred from the generated SemQL query with domain knowledge. Furthermore, a constrained decoding strategy is proposed in Picard [56] via incremental parsing, which facilitates the parser to identify valid target sequences by rejecting inadmissible tokens at each decoding step.

With regard to the multi-turn representation learning, previous works mainly focus on two aspects, including (i) how to learn high-quality contextual question and schema representations and (ii) how to effectively encode the historical SQL queries.

For the contextual question and schema representation learning, as shown in Fig. 4, [41] investigates the impact of different contextual information encoding methods on the multi-turn T2S parsing performance, including (a) contacting all the NL questions within each question sequence as input, (b) using a turn-level encoder to deal with each question, and (c) devising a gate mechanism to balance the importance of each historical question. Typically, EditSQL [97] utilizes two separate Bi-LSTMs [26] for encoding the NL questions and the table schema respectively. Specifically, for the question at each turn, EditSQL first utilizes a Bi-LSTM to encode the question tokens. The output hidden states are then fed into a dot-product attention layer [45] over the column header embeddings. The relationships among the multi-turn questions are captured by the turn attention mechanism. At the current turn, the dot-product attention between the current question and previous questions in the history is computed, and the weighted average of previous question representations is then added to the current question representation to form the context-aware question representation. For each column header, the table name and the column name are concatenated and passed into a Bi-LSTM layer. The output hidden states are then fed into a self-attention layer [69] to better capture the internal structure of the table schemas such as foreign keys. In addition, the self-attention vector and the question attention vector are concatenated and fed into the second Bi-LSTM layer to obtain the final column header representation. [33] learns a context memory controller to maintain the memory by keeping the cumulative meaning of the sequential NL questions and using an external memory to represent contextual information. [10] decouples the multi-turn T2S parsing into two pipeline tasks: question rewriting and single-turn T2S parsing. The question rewriting (QR) module aims to generate semantic-completion question based on the dialogue context, which concatenates the dialogue history and the latest question as the input of the QR module. The goal of the QR module is to generate a simplified expression based on the latest question and the dialogue history. The single-turn T2S parser predicts the corresponding SQL query with the simplified expression generated by the QR module, which chooses a pre-trained BART as question rewriter and RAT-SQL [71] as the single-turn text-to-SQL parser.

For the historical SQL input encoding, HIE-SQL [98] treats the logic-forms of the SQL query as an another modality for the NL question and incorporates additional SQL encoders to capture the semantic connection between the SQL query and the NL question. It produces comprehensive representations of the previously predicted SQL queries to improve the contextual representation.

Different from the single-turn setting, the multi-turn setting requires the integration of contextual inductive bias into the structural modelling.

We briefly introduce several sources that have been extensively used for table collection. WikiTableQuestion [50] is a representative corpus which is composed of 22,033 question-answer pairs on 2,108 tables, where the tables are randomly collected from Wikipedia with at least five columns and eight rows. The tables are pre-processed by omitting all the non-textual information, and each merged cell is duplicated to keep the table valid. In total, there are 3,929 distinct column headers (relations) among the 13,396 columns. WikiSQL [101] is a collection of 80,654 hand-crafted question-SQL pairs along with 24,241 HTML tables collected from Wikipedia. The tables are collected from [2], and the small tables that have less than five columns or five rows are filtered. WDC WebTables [36] is a large-scale table collection, which contains over 233 million tables and has been extracted from the July 2015 version of the CommonCrawl. Those tables are classified as either relational (90 million), entity (139 million), or matrix (3 million). WikiTables [3] contains 1.6 million high-quality relational Wikipedia tables constructed by extracting all HTML tables from Wikipedia which had the class attribute “wikitable” (used to easily identify data tables) from the November 2013 XML dump of English Wikipedia. ToTTo [49] is an open-domain English table-to-text dataset with over 120,000 training examples which contains 83,141 corresponding web tables automatically collected from Wikipedia using heuristics with various schema spanning various topical categories. WebQueryTable [65] is composed of 273,816 tables by using 21,113 web queries, where each query is used to search the Web pages and the relevant tables are obtained from the top ranked Web pages. Spider [93] is a large-scale text-to-SQL dataset, which contains 200 databases belonging to 138 different domains. In particular, the databases come from three resources: (i) 70 complex databases are collected from SQL tutorials, college database courses, and textbook examples, (ii) 40 databases are collected from the DatabaseAnswers¹¹1http://www.databaseanswers.org/, (iii) 90 databases are selected from WikiSQL [101].

So far, different question annotation methods have been introduced to annotate natural language questions based on the collected databases. Generally, the methods can be divided into three categories: sampling-based methods, template-based methods, and generation-based methods.

There are also some studies that invite annotators to manually create natural questions and corresponding SQL queries without leveraging templates or rules, such that the generated NL questions are natural and diverse. In particular, Spider [93] generates 10,181 NL questions and 5,693 unique SQL queries over 200 databases by considering three primary aspects: SQL pattern coverage ensuring that enough SQL samples are obtained to cover all common SQL patterns, SQL consistency ensuring that the semantically equivalent NL questions share the same SQL query, and question clarity ensuring that the vague or too ambiguous questions are not included.

Generally, the SQL annotation methods can be divided into three primary categories: logic perturbation, SQL template instantiation and hierarchical synthesis pipeline.

Text encoding can be divided into dynamic and static types based on the word encoding in natural language processing. Some methods applied GloVe [51] to initialize the word embedding of each input item by looking up an embedding dictionary without the context such as RATSQL [71] and LGESQL [8]. However, the static embedding methods are still limited. Neither of the static methods are able to tackle the polysemy problem. In addition, the learned features are restricted by the pre-defined window size. With the development of the pre-train language models, some studies attempt to encode textual data with PLMs instead of static word embeddings. In particular, plenty of methods (e.g., TaBERT [86], TaPas [25], MATE [16], StruG [14]) utilize the pre-trained BERT [15] as the encoder to get the contextualized word-level representations, and the parameters of BERT [15] are updated along with the training process. Grappa [89] uses RoBerta [44] as the encoder. Tapex [42] leverages both BART [38] encoder and decoder, while GAP [62] merely utilizes the BART encoder.

Different from textual data, the tabular data is distributed in two-dimensional (2-D) structures. The table pre-training approaches need to first convert the 2-D table data into linearized 1-D sequence input before feeding the tabular data into language models. A common serialization method is to flatten the table data into a sequence of tokens in the row-by-row manner and then concatenate the question tokens before the table tokens for tabular pre-training, such as TaPas [25], MATE [16], and TableFormer [84]. TaBERT [86] proposes content snapshots to encode a subset of table content which is most relevant to the input utterance. This strategy is then combined with a vertical attention mechanism, sharing the information among the cell representations in different rows. There are also some studies (e.g., StruG [14], GraPPa [89] and UnifiedSKG [80]) which only take the headers of tables as input without considering the data cells.

While the NLP models generally take the 1-D sequences as input, the positional encoding becomes crucial for tabular data to facilitate the neural models to better capture the structure information. Most previous pre-training methods such as TaBERT [86], GraPPa [89], and TaPEx [42] explored the global positional encoding strategy on the flattened tabular sequences. Nevertheless, in addition to the 1-D sequential positions, the tables have structured columns and rows which consists of critical two-dimensional and hierarchical information. The works such as TaPas [25] and MATE [16] encode the row and column content based on column/row IDs. TableFormer [84] decides whether two cells are in the same column/row and the column header, rather than considers the absolute order information of columns and rows in the tables.

Existing works often explore different variants of masked language modeling (MLM) to guide the language models to learn better representations of both natural language and tabular data. Concretely, the MLM objectives can be divided into three primary categories: reconstructing the corrupted NL sentences, reconstructing the corrupted table headers or cell values, and reconstructing the tokens from the corrupted NL sentences and tables.

In particular, most pre-training models [86, 25, 89, 62, 16, 84, 92] adopt masked language modelling by randomly masking a part of the input tokens from NL sentences or table headers and then predicting the masked tokens. Then, the MLM loss is calculated by minimizing the cross-entropy loss between the original masked tokens and the predicted masked tokens. In addition, TaBERT [86] also proposed a Masked Column Prediction (MCP) and a Cell Value Recovery (CVR) to learn the column representations of tables, where the MCP objective predicts the names and data types of masked columns and the CVR objective attempts to predict the original value of each cell in the masked column given its cell vector. GAP [62] devised a Column Recovery (CRec) objective to recovery the corresponding column name conditioned on a sampled cell value.

Schema linking is a key component in text-to-SQL parsing, which learns the alignment between NL questions and given tables. In particular, schema linking aims at identifying the references of columns, tables and condition values in NL questions. It is especially important for complex SQL generation and cross-domain generalization, where the text-to-SQL parser should be aware of what tables and columns are involved in the NL question even when referring against with tables from arbitrary domains and modelling complex semantic dependencies between NL questions and SQL queries.

Recently, several pre-training objectives [89, 62, 14, 92] are devised to model the schema linking information by learning the correlations of NL questions and tables. GraPPa [89] proposed a SQL Semantic Prediction (SSP) objective, which aims at predicting whether a column name appears in the SQL query and which SQL operation is triggered conditioned on the NL question and given table headers. The SSP objective is implemented by converting the SQL sequence labeling into operation classification for each column, which results in 254 possible operation classes. Similar to GraPPa[89], SCoRE [92] proposed a Column Contextual Semantics (CCS) objective, aiming to predict what operation should be performed on the given column. STRUG [14] proposed three structure-grounded objectives to learn the text-table alignment, including Column Grounding (CG), Value Grounding (VG) and Column-Value mapping (CV). Concretely, the CG objective is a binary classification task, aiming at predicting whether a column is mentioned in the NL question or not. The VG objective is also transformed to binary classification, which aims at predicting where a token is a part of a grounded value conditioned on the NL question and the table schema. To further align the grounded columns and values, the CG objective is devised to match the tokens in the NL question and the columns. Similar to the CG objective, GAP [62] also developed a Column Prediction (CPred) objective to predict whether a column is used in the NL question or not.

Modelling structured tables plays a crucial role in text-to-SQL pre-training. TaPEx [42] proposed a SQL executor objective by pre-training a neural model to mimic a SQL executor on tables. Specifically, the neural SQL executor is learned to execute the SQL query and output the corresponding correct result, which requires the model to have deep understanding of the SQL queries and tables.

The goal of text-to-SQL parsing is to translate NL questions into SQL queries that can be executed on the given tables. Therefore, it can be beneficial to incorporate the SQL generation objective into the pre-training methods so as to further enhance the downstream tasks. GAP [62] proposes a SQL generation objective to generate specific SQL keywords or column names in the appropriate positions rather than merely predict whether a column is mentioned or not.

The above pre-training objectives primarily model stand-alone NL questions without considering the context-dependent interactions, which result in sub-optimal performance for context-dependent text-to-SQL parsing. SCoRe [92] is the first representative pre-training method for context-dependent Text-to-SQL parsing. In particular, SCoRe designs a turn contextual switch (TCS) objective to model the context flow by predicting the context switch label (from 26 possible operations) between two consecutive user utterances. Despite its effectiveness, the TCS objective ignores the complex interactions of context utterances, and it is difficult to track the dependence between distant utterances.