D u SQL : A Large-Scale and Pragmatic C hinese Text-to- SQL Dataset (2020)

Abstract: Due to the lack of labeled data, previous research on text-to-SQL parsing mainly focuses on English. Representative English datasets include ATIS, WikiSQL, Spider, etc. This paper presents DuSQL, a larges-scale and pragmatic Chinese dataset for the cross-domain text-to-SQL task, containing 200 databases, 813 tables, and 23,797 question/SQL pairs. Our new dataset has three major characteristics. First, by manually analyzing questions from several representative applications, we try to figure out the true distribution of SQL queries in real-life needs. Second, DuSQL contains a considerable proportion of SQL queries involving row or column calculations, motivated by our analysis on the SQL query distributions. Finally, we adopt an effective data construction framework via human-computer collaboration. The basic idea is automatically generating SQL queries based on the SQL grammar and constrained by the given database. This paper describes in detail the construction process and data statistics of DuSQL. Moreover, we present and compare performance of several open-source text-to-SQL parsers with minor modification to accommodate Chinese, including a simple yet effective extension to IRNet for handling calculation SQL queries.

## introduction in the past few decades, a large amount of research has focused on searching answers from unstructured texts given natural questions, which is also known as the question answering (qa) task @xcite @xcite . however, a lot of high-quality knowledge or data are actually stored in databases in the real world. it is thus extremely useful to allow ordinary users to directly interact with databases via natural questions. to meet this need, researchers have proposed the text-to-sql task with released english datasets for model training and evaluation, such as atis @xcite , geoquery @xcite , wikisql @xcite , and spider @xcite . formally, given a natural language (nl) question and a relational database, the text-to-sql task aims to produce a legal and executable sql query that leads directly to the correct answer, as depicted in figure 1 . a database is composed of multiple tables and denoted as @xmath0, t 2 , ..., t n }. a table is composed of multiple columns and denoted as t @xmath1, col 2 , ..., col m }. tables are usually linked with each other by foreign keys. the earliest datasets include atis @xcite , geoquery @xcite , @xcite , academic @xcite , etc. each dataset only has a single database containing a certain number of tables. all question/sql pairs of train/dev/test sets are generated against the same database. many interesting approaches are proposed to handle those datasets @xcite @xcite . however, real-world applications usually in-volve more than one database, and require the model to be able to generalize to and handle unseen databases during evaluation. to accommodate this need, the wikisql dataset is then released by @xcite . it consists of 80,654 question/sql pairs for 24,241 single-table databases. they propose a new data split setting to ensure that databases in train/dev/test do not overlap. however, they focus on very simple sql queries containing one select statement with one where clause. in addition, @xcite released tableqa, a chinese dataset similar to the wikisql dataset. @xcite released a more challenging spider dataset, consisting of 10,181 question/sql pairs against 200 multi-table databases. compared with wikisql and tableqa, spider is much more complex due to two reasons: 1) the need of selecting relevant tables; 2) many nested queries and advanced sql clauses like group by and order by. as far as we know, most existing datasets are constructed for english. another issue is that they do not refer to the question distribution in realworld applications during data construction. taking spider as an example. given a database, annotators are asked to write many sql queries from scratch. the only requirement is that sql queries have to cover a list of sql clauses and nested queries. meanwhile, the annotators write nl questions corresponding to sql queries. in particular, all these datasets contain very few questions involving calculations between rows or columns, which we find are very common in real applications. this paper presents dusql, a large-scale and pragmatic chinese text-to-sql dataset, containing 200 databases, 813 tables, and 23,797 question/sql pairs. specifically, our contributions are summarized as follows. ## sql query distribution as far as we know, existing text-to-sql datasets mainly consider the complexity of sql syntax when creating sql queries. for example, wik-isql has only simple sql queries containing se-lect and where clauses. spider covers 15 sql clauses including select, where, order by, group by, etc, and allows nested queries. however, to build a pragmatic text-to-sql system that allows ordinary users to directly interact with databases via nl questions, it is very important to know the sql query distribution in realworld applications, from the aspect of user need rather than sql syntax. our analysis shows that spider mainly covers three types of sql queries, i.e., matching, sorting, and clustering, whereas wikisql only has matching queries. neither of them contains the calculation type, which we find composes a large portion of questions in certain real-world applications. to find out the sql query distribution in reallife applications, we consider the following three representative types of database-oriented applications, and conduct manual analysis against user questions. we ask annotators to divide user questions into five categories (see appendix b for details), i.e., matching, sorting, clustering, calculation, and others. ## corpus construction building a large-scale text-to-sql dataset with multi-table databases is extremely challenging. first, though there are a large amount of independent tables on the internet, connections among the tables are usually unavailable. therefore, great efforts are needed to create multi-table databases. second, it is usually difficult to obtain nl questions against certain databases. third, given a question and the corresponding database, we need proficient annotators to write a sql query for the question who understand both the database schema and the sql syntax. different from previous works, which usually rely on human to create both nl questions and sql queries @xcite , we build our dataset via a human-computer collaboration way, as illustrated in figure 4 . the key idea is to automatically generate sql queries paired with pseudo questions given a database. then pseudo questions are paraphrased to nl questions by humans. finally, to guarantee data quality, low-confidence sql queries and nl questions detected according to their overlapping and similarity metrics, and are further checked by humans. ## benchmark approaches all existing text-to-sql works focus on english datasets. considering that dusql is the most similar with spider, we choose the following three representative publicly available parsers as our benchmark approaches, to understand the performance of existing approaches on our new chinese dataset. we also extend the state-of-the-art irnet model of @xcite to accommodate the two characteristics of our data, i.e., calculation questions and the need of value prediction. seq2seq+copying @xcite incorporates the database schemas into the model input and uses a copying mechanism in the decoder. syntaxsqlnet @xcite proposes a sql syntax tree-based network to generate sql structures, and uses generation path history and table-aware column attention in the decoder. irnet @xcite designs an intermediate representation called semql for encoding higher-level abstraction structures than sql, and then uses a grammar-based decoder @xcite to synthesize a semql query. at present, irnet reports the state-of-the-art results on spider dataset. both syntaxsqlnet and irnet utilize a grammar to guide sql generation and conduct experiments on spider dataset. however, neither of their grammars can handle calculation questions. another major difference between our dataset and spider is that our evaluation metric (see section §5) also considers value prediction, since values in a sql query are from the corresponding question or database both of which are available inputs to the model. please refer to our discussion in section §3 for details. due to the characteristics of our dataset, all the three models perform poorly on dusql. therefore, we extend the irnet model to accommodate dusql as follows. firstly, we extend the grammar of semql to accommodate the two characteristics of our dataset, as shown in figure 8 . the production rules in bold are added to parse calculation questions. other production rules are modified based on original rules to support value prediction (due to space limitation, we attach the full list of extended grammar in appendix f.). then we use all the n-grams of length 1-6 in the question to match database cells or number/date to determine candidate values for the predicated sql query. the values are used in the same way as the columns and tables in the ir-net model. evaluation metrics evaluation metrics for the text-to-sql task include component matching, exact matching, and execution accuracy. component matching @xcite uses f1 score to evaluate the performance of the model on each clause. exact matching, namely the percentage of questions whose predicted sql query is equivalent to the gold sql query, is widely used in text-to-sql tasks. execution accuracy, namely the percentage of questions whose predicted sql query obtains the correct answer, assumes that each sql query has an answer. we use exact matching as the main metric, and follow @xcite and @xcite ## conclusion we present the first large-scale and pragmatic chinese dataset for cross-domain text-to-sql parsing. based on the analysis on questions from real-world applications, our dataset contains a considerable proportion of questions that require row/column calculations. we extend the state-of-the-art ir-net model on spider to accommodate dusql, and obtain substantial performance boost. yet, there is still a large room for improvement, especially on calculation questions which usually require incorporation of common-sense knowledege into the model. for future work, we will continually improve the scale and quality of our dataset, to facilitate future research and to meet the need of database-oriented applications. table.column | table.column1 mathop table.column2 | table1.column mathop table2.column mathop :: figure 9 : the production rules for sql generation.