1 Introduction

A huge number of data is now organized in relational databases and typically accessed through SQL (Structured Query Language) queries. The interest in automatically translating questions expressed in natural language to SQL (often referred to as NL2SQL) has been intense, as one can observe through a number of excellent surveys in the literature [1,2,3]. Figure 1 depicts the whole flow from a natural language question to a SQL query result; the SQL query refers to database tables and their columns, using primary and secondary keys as appropriate.

Existing approaches for NL2SQL can be divided into entity-based and machine learning ones, the latter dominated by techniques based on deep learning [3].

Fig. 1.
figure 1

From a natural language question to a SQL query and to the database query result. Database table names appear in blue (singer) and red (song), while the primary key appears in green (Singer_ID). (Color figure online)

Full size image

Entity-based approaches focus on the interpretation of input text based on rules so as to translate it to a SQL query. The translation often goes first to an intermediary state and later to a final SQL query. Relevant systems are Bela [4], SODA [5], NaLIR [6,7,8], TR Discover [9], Athena [10, 11], Athena++ [12] and Duoquest [13, 14] .

Machine learning approaches are based on supervised learning, in which training data contains natural language questions and paired SQL queries [3]. Several architectures can be trained or fine-tuned so as to run the translation. Relevant systems are EchoQuery [15], Seq2SQL [41], SQLNet [16], DialSQL [17], TypeSQL [18], SyntaxSQLNet [19], AugmentedBI [21], IRNet [22], RAT-SQL [23], RAT-SQL+GAP [24], GraPPa [25], BRIDGE [26], DBPal [27,28,29], HydraNet [30], DT-Fixup [32] and LGESQL [31].

There are also hybrid approaches that combine entity-based and machine learning [3]; relevant examples are Aqqu [34], MEANS [35] and QUEST [33].

The previous paragraphs contain long lists of references that should suffice to demonstrate that translation from natural language to SQL is a well explored research topic. The field is relatively mature and benchmarks for NL2SQL are now widely used, containing training and testing data and ways to evaluate new proposals. Table 1 shows a few important datasets in the literature, reporting their number of questions, number of SQL queries, number of databases, number of domain and tables per database [20]. Particularly, the Spider dataset is a popular resource that contains 200 databases with multiple tables under 138 domains.Footnote 1 The complexity of these tables allows testing complex nested SQL queries. A solid test suite evaluation package for testing against Spider [46] is available;Footnote 2 in addition, there is a very active leaderboard rank for tests that use Spider.Footnote 3

Table 1. NL2SQL benchmarks.
Full size table

Currently, the best result in the Spider leaderboard for Exact set match without values, whereby a paper and code are available, is the entry by RAT-SQL+GAP [24].Footnote 4 This system appears in the 6th rank position with Dev 0.718 and Test 0.697.Footnote 5 Note that the Spider leaderboard, as of August 2021, displays in the 1st rank position LGESQL [31] with Dev 0.751 and Test 0.720 for Exact set match without values. Thus RAT-SQL+GAP is arguably at the state-of-art in NL2SQL.

RAT-SQL+GAP is based on the RAT-SQL package (Relation-Aware Transformer SQL) [23]. RAT-SQL was proposed in 2019 as a text to SQL parser based on the BERT language model [47]. Package RAT-SQL version 3 with BERT is currently the 14th entry in the Spider leaderboard rank. RAT-SQL+GAP adds Generation-Augmented Pre-training (GAP) to RAT-SQL. GAP produces synthetic data to increase the dataset size to improve pre-training; the whole generative models are trained by fine-tuning a BART [48] large model.

Despite the substantial number of techniques, systems and benchmarks for NL2SQL, most of them focus on the English language. Very few results can be found for input questions in the Portuguese language, for example. The study by Silva et al. [51] presents an architecture for NL2SQL in which natural language questions in Portuguese are translated to the English language on arrival, and are then shipped to NL2SQL existing packages.

The goal of this paper is simple to state: we present a translator for queries in Portuguese natural language into SQL. We intend to study the effect of replacing the questions in the Spider dataset with translated versions, and also to investigate how to adapt the RAT-SQL+GAP system to the needs of a different language. Using a new version of Spider with RAT-SQL+GAP to train models, we produce inferences and compare results so as to understand the difficulties and limitations of various ideas.

What we found is that, by focusing on Portuguese, we actually produced methods and results that apply to any multilingual NL2SQL task. An important insight (and possibly the main contribution of this paper) is dealing with a non-English language, such as Portuguese, we greatly benefit from taking a multilingual approach that puts together English and the other language—in our case, English and Portuguese. We later stress this idea when we discuss our experiments. We thus refer to our “multilingual-ready” version of RAT-SQL+GAP as mRAT-SQL+GAP; all code and relevant data related to this system are freely availableFootnote 6.

2 Preliminary Tasks

The adaptation to language other than English demands at least the translation of the dataset and changing the code to read and write files UTF-8 encoding.

2.1 Translating the Spider Dataset

The translation to the Portuguese language in the NL2SQL task evolves the natural language part, the questions. The SQL queries must remain the same to make sense. To translate the questions, it is important to extract them from specific .json files. The Spider dataset has three files that contain input questions and their corresponding SQL queries: dev.json, train_others.json, and train_spider.json. We extracted the questions and translated them using the Google Cloud Translation API.Footnote 7. Table 2 presents the number of questions and number of characters per file (just for the questions). The code that reads the original files and that generates translated versions relies on the UTF-8 encoding so as to accept Portuguese characters; several files were generated in the process (.txt just for the translated questions, .csv for the SQL queries and original/translated questions, .json for the translated questions).

Table 2. Number of questions and characters per file.
Full size table

We then conducted a revision process by going through the text file and looking for questions in the csv file (together with the corresponding SQL queries). After the revision, a new .json file was generated with these translated and revised questions. Table 3 shows four examples of translated questions.

Table 3. Translation examples.
Full size table

Adapting the RAT-SQL+GAP System. We had to change the RAT-SQL+ GAP code to allow multilingual processing. For instance, the original Python code is not prepared to handle UTF-8 files; thus, we had to modify the occurrences of “open” and “json.dump” commands, together with a few other changes. We ran a RAT-SQL+GAP test and checked whether all the characters employed in Portuguese were preserved. We also noticed lemmatization errors in preprocessed files. As the original code for RAT-SQL+GAP relies on the Stanford CoreNLP lemmatization tool that currently does not support Portuguese, it was replaced by Simplemma.Footnote 8 The latter package supports multilingual texts, and particularly supports Portuguese and English.

Training. The original language model at the heart of RAT-SQL+GAP is BART-largeFootnote 9 [48], a language model pretrained for the English language. We had to change that model to another one that was pretrained for the Portuguese language. A sensible option was to work with a multilingual Sequence-to-Sequence BART model; the choice was mBART-50Footnote 10 [50] because it covers Portuguese and English languages (amongst many others). Another language model we investigated was the BERTimbau-baseFootnote 11 [49], as RAT-SQL works with BERT; the move to BERTtimbau, a Portuguese-based version of BERT, seemed promising.

Dataset. A total of 8,659 questions were used for training (7,000 questions in train_spider.json and 1,659 questions in train_others.json). The 1,034 questions in dev.json were used for testing. We later refer to three scenarios:

  • English train and test: questions are just in English for training and testing.

  • Portuguese train and testFootnote 12: questions are just in Portuguese for training and testing.

  • English and Portuguese (double-size) train and test(see footnote 12): questions are in English and Portuguese for training and testing (in this case, we thus have twice as much data as in each of the two previous individual scenarios).

Evaluation Metrics. The main evaluation metric with respect to Spider is Exact Set Match (ESM). We here present results for Exact Set Match (ESM) without values, as most results in the Spider leaderboard currently adopt this metric. Some Spider metrics are also used to classify the SQL queries into 4 levels: easy, medium, hard and extra hard. Table 4 shows an example for each level; these queries correspond to the four questions in Table 3 in the same order.

To evaluate the results, we used the Spider test suite evaluation [46]. An aside: the suite must receive a text file with the SQL query generated and another one with the gold-standard SQL query. These files obviously do not change when we move from English to any other input language.

As a digression, note that it is possible to plug values during evaluation. A query with a value looks like this:

SELECT Count(*) FROM airlines JOIN airports WHERE airports.City = “Abilene"

A query without values has the word “terminal" instead of the value:

SELECT Count(*) FROM airlines JOIN airports WHERE airports.City = “terminal"

Table 4. SQL query levels: easy/medium/hard/extra.
Full size table

3 Experiments

Experiments were run in a machine with AMD Ryzen 9 3950X 16-Core Processor, 64 GB RAM, 2 GPUs NVidia GeForce RTX 3090 24 GB running Ubuntu 20.04.2 LTS. Figure 2 shows the architecture of the training, inference and evaluation processes described in this section.

Results can be found in Table 5. This table shows the results of Exact Set Match without Values for RAT-SQL+GAP trained locally for all models. We have 3 datasets, 5 trained model checkpoints and 7 distinct relevant results.

The first line corresponds to the original model BART and original questions in English. Note that the result in line #1 achieved the same performance reported by [24] for Exact Set Match without values in Spider: 0.718 (All) for Dev. This indicates that our testing apparatus can produce state-of-the-art results. Moreover, line #1 shows a well-tuned model in English that can be attained.

Fig. 2.
figure 2

Architecture of the training, inference, evaluation. Results related to Table 5: each line in that table appears here as a square at the bottom of the figure.

Full size image

Our first experiment was to change the questions to Portuguese and the model to BERTimbau-base which is pretrained in Portuguese. In Table 5 the result in line #2 for BERTimbau 0,417(All) is quite low when compared to the result in line #1. This happens for many reasons. The model is BERT and the best result uses BART. Another important difference is that the Portuguese content used in fine-tuning has a mixture of Portuguese and English words because the SQL query inevitably consists of English keywords, see examples of SQL queries in Table 4. In fact, some questions demand untranslated words to make sense, for example “Boston Red Stockings” in the translated question: Qual é o salário médio dos jogadores da equipe chamada "Boston Red Stockings"?. This suggested that a multilingual approach might be more successful. The mBART-50 language model was then tested within the whole architecture.

mBART-50 was in fact fine-tuned in three different ways: with questions only in English, only in Portuguese, and with questions both in English and in Portuguese (that is, a dataset with questions and their translations). Inferences were run with English test questions and Portuguese test questions, while the model was fine-tuned with the corresponding training questions language. For the mBART-50 model fine-tuned with the train dataset with two languages, three inferences were made: only English, only Portuguese, and the combined English Portuguese test dataset.

mBART-50 fine-tuned with questions in English in line #3 achieved 0.651 (All) when tested with questions in English. The same model mBART-50 fine-tuned with questions in Portuguese in line #4 achieved 0.588(All) when tested with questions in Portuguese.

A multilingual model such as mBART-50 can be trained with the two languages at the same time. This is certainly appropriate for data augmentation and to produce a fine-tuning process that can better generalize. The results in Table 5 lines #5, #6 and #7 were obtained with mBART-50 fine-tuned with the double-size training dataset (English and Portuguese); the three inferences were made using the same model checkpoint. The test datasets were in English for line #5, in Portuguese for line #6, and the double-size test dataset in English and Portuguese for line #7. The results demonstrate improvements, if we compare inferences with English test dataset lines #3 and #5. Results went up from 0.651 (All) to 0.664 (All), better for all levels of questions. If we compare inferences with the Portuguese test dataset in lines #4 and #6, the results went from 0.588 (All) to 0.595 (All). However, they are better just for an easy level of questions; this was enough to influence the overall results for line #6.

Table 5. Results.
Full size table
Fig. 3.
figure 3

Examples of keywords in the prediction of the SQL query in English and Portuguese languages. Top pair: question in English and corresponding SQL query predicted from it. Bottom pair: question in Portuguese and corresponding SQL query predicted from it.

Full size image

The inference made with the double-size test dataset in English and Portuguese, in line #7, cannot be compared with the other inferences because they used just one language. Nevertheless, the model mBART-50 trained with English and Portuguese (double-size training dataset) produced good results with this rather uncommon testing dataset.

All the detailed results presented in this paper are openly availableFootnote 13.

4 Analysis and Discussion

These experiments indicate that multilingual pretrained transformers can be extremely useful when dealing with languages other than English. There is always the need to integrate English processing with the additional languages of interest, in our case, with Portuguese.

Overall, questions in the English language have a closer similarity with SQL queries, thus simplifying inferences. Conversely, questions in Portuguese require further work. Figure 3 show a real example of correct predictions in English (Table 5 line #5) and in Portuguese (Table 5 line #6). In Fig. 3, words such as song, names, singers, average in the English question are keywords needed to resolve the query, and they are very close to the target word in the query. In Portuguese, the same keywords are músicas, nomes, cantores, média that must respectively match song, name, singer, and avg. This introduced an additional level of difficulty that explains the slightly worse results for inference with questions in Portuguese: 0.595 (Table 5 line #6). This is to be compared with questions in English: 0.664 (Table 5 line #5). In any case, it is surprising that the same model checkpoint resolved the translation with such different questions.

Fig. 4.
figure 4

Example of words that represent the value in the prediction of the SQL query. En Q: English question, Pt Q: Question translated to Portuguese, P: SQL query predicted, and G: Gold SQL query.

Full size image

Some translations actually keep a mix of languages, because some words represent the value and cannot be translated. Figure 4 shows an example. There the show name “The Rise of the Blue Beetle” should not be translated to maintain the overall meaning of the question; these words in English must be part of the Portuguese question. This successful SQL query inference was produced with mBART-50 fine-tuned with the English and Portuguese training dataset (Table 5 line #6) and mBART-50 fine-tuned in the Portuguese-only training dataset (Table 5 line #4). The show name was then replaced with “terminal” during the RAT-SQL+GAP prediction process, as it is processed through the Spider Exact Set Match without Values evaluation. In any case, the show name is part of the input and will introduce difficulties in the prediction.

In addition, for real-world databases, it is a practice, at least in Brazil, to name tables and columns with English words even for databases with content in Portuguese. This practical matter is another argument in favor of a multilingual approach.

Figure 5 shows a sample of failed translations evaluated by the Spider Exact Set Match for inferences using mBART-50 (Table 5 line #6). It is actually difficult to find the errors without knowing the database schema related to every query. The objective of this figure is to show that even when the query is incorrect, it is not composed of random or nonsensical words. Our manual analysis indicates that this is true for queries failing with all other models.

Fig. 5.
figure 5

Sample of failed queries evaluated by Spider Exact Set Match for inferences from mBART-50 (Table 5 line #6) . Q: question, P: SQL query predicted, and G: Gold SQL query.

Full size image

5 Conclusion and Future Work

In sum, we have explored the possible ways to create a translator that takes questions in the Portuguese language and outputs correct SQL queries corresponding to the questions. By adapting a state-of-the-art NL2SQL to the Portuguese language, our main conclusion is that a multilingual approach is needed: it is not enough to do everything in Portuguese; rather, we must simultaneously work with English and Portuguese.

In Table 5, our best result is in line #5 0.664 (All) whereby we test with questions in English (original test set from Spider) using mBART-50 model fine-tuned with a double-size training dataset (English and Portuguese). This yields 92% of the English-only performance of 0.718 (All) in line #1. Testing with questions in Portuguese (using our translation) with the same BART-50 model fine-tuned with a double-size training dataset (English and Portuguese), we achieve instead 0.595 (All) line #6. Now this is 83% of the English-only performance of 0.718 (All). These results should work as a baseline for future NL2SQL research in Portuguese.

Our multilingual RAT-SQL+GAP, or mRAT-SQL+GAP for short, the translated datasets, the trained checkpoint, and the results; are open-source availableFootnote 14.

Future work should try other multilingual transformers (and possibly other seq-to-seq models), always seeking ways to use English and Portuguese together. Another possible future work is to fine-tune BERTimbau-largeFootnote 15 [49] so as to better understand the effect of the size of the language model. Lastly, a translation of the Spider dataset to other languages so as to work with several languages at the same time should produce valuable insights.