Recent Advances in Text-to- SQL : A Survey of What We Have and What We Expect (2022)

Abstract: Text-to-SQL has attracted attention from both the natural language processing and database communities because of its ability to convert the semantics in natural language into SQL queries and its practical application in building natural language interfaces to database systems. The major challenges in text-to-SQL lie in encoding the meaning of natural utterances, decoding to SQL queries, and translating the semantics between these two forms. These challenges have been addressed to different extents by the recent advances. However, there is still a lack of comprehensive surveys for this task. To this end, we review recent progress on text-to-SQL for datasets, methods, and evaluation and provide this systematic survey, addressing the aforementioned challenges and discussing potential future directions. We hope this survey can serve as quick access to existing work and motivate future research.

## introduction the task of text-to-sql is to convert natural utterances into sql queries @xcite . figure 1 shows an example. given a user utterance "what are the major cities in the state of kansas?", the system outputs a corresponding sql that can be used for retrieving the answer from a database. it builds a natural language interface to the database (nlidb) to help lay users access information in the database @xcite , inspiring research in human-computer interaction @xcite . because the sql query can be regarded as a semantic representation @xcite ), textto-sql is also a representative task in semantic parsing, helping downstream applications such as question answering @xcite . thus, text-to-sql has attracted researchers in the natural 1 the github link for this survey is: https:// github.com/text-to-sql-survey-coling22/ text-to-sql-survey-coling22.github.io. given the database schema and user utterance, the system outputs a corresponding sql query to query the database system for the result. appendix b gives more text-to-sql examples. the challenges in text-to-sql lie within three aspects: (1) extracting the meaning of natural utterances (encoding); (2) transforming the extracted meaning into another expression which is pragmatically equivalent to the nl meaning (translating) and; (3) producing the corresponding sql queries (decoding). a wide range of methods has been investigated to address the technical challenges, from representation learning, intermediate structures, decoding, model structures, training objectives, and other perspectives. in addition, much work has been conducted on data resources and evaluation. however, relatively little work has been done in the literature to provide a comprehensive survey of the landscape. the only exceptions are @xcite and @xcite ), but they cover a limited scope. to this end, we aim to provide a systematic survey that involves a broader range of text-to-sql research and addresses the aforementioned challenges. in this paper, we survey the recent progress on text-to-sql, from datasets ( § 2), methods ( § 3) to evaluation ( § 4) foot_0 and highlight potential direc- ## datasets as shown in single-domain datasets single-domain text-to-sql datasets typically collect question-sql pairs for a single database in some real-world tasks, including early ones such as academic @xcite , advising @xcite , atis @xcite , geoquery (zelle and mooney, 1996), yelp and imdb @xcite , scholar @xcite and @xcite , as well as recent ones such as sede @xcite , esql @xcite and mimicsql @xcite . these single-domain datasets, particularly the early ones, are usually limited in size, containing only a few hundred to a few thousand examples. because of the limited size and similar sql patterns in training and testing phases, text-to-sql models that are trained on these single-domain datasets can achieve decent performance by simply memorizing the sql patterns and fail to generalize to unseen sql queries or sql queries from other unless otherwise specified. appendix b gives a detailed discussion on domain knowledge and dataset convention, and concrete text-to-sql examples. large scale cross-domain datasets large cross-domain datasets such as wikisql @xcite and spider @xcite are proposed to better evaluate deep neural models. wik-isql uses tables extracted from wikipedia and lets annotators paraphrase questions generated for the tables. compared to other datasets, wikisql is an order of magnitude larger, containing 80,654 natural utterances in total @xcite . however, wikisql contains only simple sql queries, and only a single table is queried within each sql query @xcite . @xcite propose spider, which contains 200 databases with an average of 5 tables for each database, to test models' performance on complicated unseen sql queries and their ability to generalize to new domains. furthermore, researchers expand spider to study various issues of their inter-est @xcite @xcite @xcite . besides, researchers build several large-scale text-to-sql datasets in different languages such as cspider @xcite , tableqa @xcite , dusql @xcite in chinese, vitext2sql @xcite in @xcite in portuguese. given that human translation has shown to be more accurate than machine translation @xcite , these datasets are annotated mainly by human experts based on the english spider dataset. these spider-based datasets can serve as potential resources for multi-lingual text-to-sql research. other datasets several context-dependent textto-sql datasets have been proposed, which involve user interactions with the text-to-sql system in english @xcite @xcite and chinese @xcite . in addition, researchers collect datasets to study questions in text-to-sql being answerable or not @xcite , lexicon-level mapping @xcite and cross-domain evaluation for real web databases @xcite . appendix c.1 discusses more details about datasets mentioned in § 2. ## methods early text-to-sql systems employ rule-based and template-based methods @xcite , which is suitable for simple user queries and databases. however, with the progress in both db and nlp communities, recent work focuses on more complex settings @xcite . in these settings, deep models can be more useful because of their great feature representation ability and generalization ability. in this survey, we focus on the deep learning methods primarily. we divide these methods employed in text-to-sql research into data augmentation ( § 3.1), encoding ( § 3.2), decoding ( § 3.3), learning techniques ( § 3.4), and miscellaneous ( § 3.5). ## evaluation metrics evaluation setup early single-domain datasets typically use the standard train/dev/test split @xcite by splitting the question-sql pairs randomly. to evaluate generalization to unseen sql queries within the current domain, @xcite propose sql query split, where no sql query is allowed to appear in more than one set among the train, dev, and test sets. furthermore, @xcite propose a database split, where the model does not see the databases in the test set in its training time. other splitting methods also exist to help different research topics @xcite . ## discussion and future directions ever since the lunar system @xcite , systems for retrieving db information have witnessed an increasing amount of research interest and an enormous growth, especially in the field of text-to-sql in the deep learning era. with the ever-increasing model performance on the wikisql and spider leaderboards, one can be optimistic because models are becoming more sophisticated than ever. but there are still several challenges to overcome. first, these sophisticated models suffer a great performance loss when tested against different textto-sql datasets from other domains @xcite . it is unclear how to incorporate domain knowledge to the models trained on spider and deploy these models efficiently on different domains, especially those with similar information stored in db but slightly different db schemas. although large-scale datasets promote the cross-domain settings, question-sql pairs from spider are free from domain knowledge, ambiguity, or domain convention. thus, cross-domain text-to-sql needs to be studied in future research to build a practical cross-domain system that can handle real-world requests. there are different use cases in real-world scenarios, which requires models to be robust to different settings and be smart to handle different user requests. for instance, the model trained with db schemas can need to handle a corrupted table, or no table is provided in its practical use. besides, the input from users can vary from the standard question input in spider or wikisql, which poses challenges to models trained on these datasets. more user studies need to be done to study how well the current systems serve the end-users and the input pattern from the end-users. apart from sql queries, administrators can want to change db schemas, where a system that can translate the natural language to such db commands can be helpful. also, although there are already works on text-to-sql beyond english @xcite @xcite ), but we still lack a comprehensive study on multilingual text-to-sql, which can be challenging but useful in real-life scenarios. finally, it is important to build nlidb for people with disabilities. @xcite propose speech-to-sql that translates voice input to sql queries, which helps visually impaired end users. more work can be done to address various needs from the perspective of endusers, in particular, the needs from minorities. text-to-sql research can also be integrated into a larger scope of research. application-wise, @xcite develop a question answering system for the database, @xcite generate taskoriented dialogue by retrieving knowledge from the database using the text-to-sql model. an example of the possible directions is to employ the text-to-sql model to query databases for fact-checking. research-wise, @xcite compare sql queries to other logical forms in semantic pars-ing, @xcite include text-to-sql as one of the tasks to achieve a generalized semantic parsing framework. the inter-relations between various logical forms in semantic parsing can be further studied. a generalized framework or a generalized model can come as the fruit for our semantic parsing community. in hindsight, the development of text-to-sql has been pushed by the innovation in the general ml/nlp community, such as lstm @xcite , self-attention @xcite , plms @xcite , etc. recently, prompt learning has achieved decent performance on various tasks, in particular, in the lowresource setting @xcite . such characteristics align well with the expectation of having a functional text-to-sql model with a few training samples. some recent works already explore applying prompt learning to the text-to-sql task @xcite . the practical expectation for the text-to-sql task is to deploy the model in different scenarios, requiring robustness across domains. however, prompt learning struggles with being robust, and the performance can be easily affected by the selected data. this misalignment encourages researchers to study how to employ prompt learning in the real-world text-to-sql task, which can need further understanding of the cross-domain challenges for text-to-sql. another line of research is to evaluate these sophisticated text-to-sql systems. the typical measure is to evaluate the performance of the system on some existing datasets. as there are operational systems using nl input to perform tasks such as getting answers from database management system or building ontologies or playing some games, the performance of these systems can be measured by the diminution of the (human) time taken to get the searched information @xcite . while there are context-dependent text-to-sql datasets available @xcite , researchers can draw inspirations from other fields of research @xcite to design interactive set-ups to evaluate text-to-sql systems. appendix e discusses tasks relevant to the task of text-to-sql. ruiqi zhong, tao yu, and dan klein. 2020a. semantic evaluation for text-to-sql with distilled test suites. in proceedings of the 2020 conference on empirical methods in natural language processing (emnlp), pages 396-411, online. association for computational linguistics. victor zhong, mike lewis, sida i. wang, and luke zettlemoyer. 2020b. grounded adaptation for zeroshot executable semantic parsing. in proceedings of the 2020 conference on empirical methods in natural language processing (emnlp), pages 6869-6882, online. association for computational linguistics. victor zhong, caiming xiong, and richard socher. 2017. seq2sql: generating structured queries from natural language using reinforcement learning. arxiv preprint, abs/1709.00103. jiawei zhou, jason eisner, michael newman, emmanouil antonios platanios, and sam thomson. 2022. online semantic parsing for latency reduction in task-oriented dialogue. in proceedings of the 60th annual meeting of the association for computational linguistics (volume 1: long papers), pages 1554-1576, dublin, ireland. association for computational linguistics. figure 5 shows the topology for the text-to-sql task.