Translating Natural Language Queries to SQL Using the T5 Model
^††thanks: We acknowledge the support of the Natural Sciences and Engineering Research Council of Canada (NSERC), Harris SmartWorks Division of Harris Computers, Okanagan College, and Langara College.

In this paper, the approach we used for the text-to-SQL task is the training of the T5 model using a customized training data set. The T5 is a pre-trained, transformer-based model that aims to have a unified framework that transforms text in one format into another [36]. Every NLP task is cast as feeding the model with text as input and then training it to generate some text output.

There are several variations of the T5 model. Our experiments used the T5-base as the base model for its smaller size. We trained the T5-base model using a set of custom-developed training data. Parameters such as the number of epochs, batch size, learning rate, etc. were fine-tuned using mainly the free Kaggle online computing resource. The project aims to demonstrate that an effective NL to SQL model that is sufficiently accurate could be rapidly developed using basic computing resources.

To fine tune the T5 model for the text-to-SQL downstream task, the large-scale, cross-domain, text-to-SQL Spider [10] dataset was used. The Spider dataset used in our experiments has a total of 8,659 training queries and 1,034 testing queries for 166 databases. A natural language query to a database and its corresponding SQL query are the two main fields in this dataset.

In addition to the Spider dataset, we also used two sets of custom datasets associated with the utility service company’s database systems. The first one is for the OLTP database which has a total of 1,705 queries where 1,192 of the queries are for training while 513 queries are for testing. The second custom dataset is for the data warehouse which has a total of 597 queries where 440 of the queries are for training and 157 queries are for testing.

Again, a natural language query to a database and its corresponding SQL query are the two main fields in these datasets. Queries in the custom training datasets were developed so that all columns and tables in the OLTP or the data warehouse were covered at least a couple of times. This is to ensure that the T5 model is “made aware” of them during the training process. Queries in the testing datasets were similarly developed to test whether the resulting model could translate the natural language queries that are often used in real situations and have coverage over the OLTP or the data warehouse. These custom datasets were developed iteratively and jointly with the technical staff from the utility service company and were the most time-consuming part of the research and development process.

Two models were developed in the research; one for the OLTP and one for the DW. The training data sets used to train the T5 model are a concatenation of the Spider data set and the corresponding custom training dataset. The idea was that the Spider dataset was used to train the model in the “grammar” of SQL translation while the custom dataset ensured that the model learned the necessary “vocabulary” (name of columns and tables) so that the model could provide effective translation for queries from the natural language to SQL.

The development was completed on both Kaggle cloud computing environment using GPU P100 and on our own Ubuntu server with an NVIDIA GPU. A typical training run of the model takes about 4 hours.

After using the trained T5 model to translate the natural language query to SQL, we implemented a simple post-processing method to correct the SQL query with reference to the database schema.

This process scans the output SQL for incorrect names of columns and tables and, if necessary, corrects them according to the database schema. For example, it will change “meter” in the SQL query to “meters” if the correct name for the column in the schema is “meters”. It is done using a character-by-character, sequential match that is based on positions. It replaces tokens with the correct tables or column names based on the information from the database schema.

For model evaluation, Zhong proposed two evaluation metrics from different perspectives: logical form accuracy and execution accuracy [13].

Logical form accuracy is defined as the percentage of generated queries that are converted correctly from the actual query. On the other hand, execution accuracy stands for the percentage of generated queries that can be executed against the database and produce the correct results.

Zhong mentioned that two database queries can produce the same result. If only the logical form accuracy is utilized, some generated queries with a correctly executed result but not in the same syntax would be treated as incorrect queries. It is suggested that both of the metrics should be considered to evaluate the performance of models [13].

For this research, we used the exact match accuracy with manual inspection of the testing results to measure performance. We believe that with manual inspection and adjustments, the exact match accuracy and execution accuracy are the same in this case.

Two datasets were used to evaluate performance: one used only the test datasets described above with queries not seen by the model (Test 1); the other the testing and the custom-developed training dataset together (Test 2). This was applied in the OLTP model as well as the data warehouse model. It was expected that the performance for the first test dataset would be lower and be a better measure of the accuracy of the model.

Using the training datasets as described above, the model was trained and tuned iteratively with adjustments made mainly to the training datasets. We evaluated the models using the two sets of test data as described above. For the OLTP queries, the final model achieved a 72.9% exact match accuracy on Test 1 and an 83.7% exact match accuracy on Test 2. On the other hand, the accuracy is higher for the data warehouse queries with 85.4% exact match accuracy on Test 1 and 87.5% exact match accuracy on Test 2.

The results for the data warehouse model were better in terms of exact match accuracy. This was expected as the data warehouse has a simpler database schema and the queries tend also to be simpler and therefore easier to “learn”.

Compared with other works that use the T5 model for text-to-SQL tasks, the performance of our model is similar or better than the results of previous experiments. For instance, PICARD [38] has an exact-set match accuracy of 65.8% on T5-base and 75.5% on the larger T5-3B model on Spider’s development test set. RASAT [39], which employs a more sophisticated architecture with T5, has an exact-set match accuracy of 72.6% on the development test set. Lastly, UnifiedSKG [37] showed 58.12% match accuracy on their model trained based on the T5-base model.

The development of the models was completed in August 2022. The two models, along with the chatbot developed by students in the Okanagan College research team and a graphical interface for output generated from the SQL queries, have since been implemented for several clients by the utility service company in early 2023. The models were proven to be usable based on the experience of using the models in real situations.

While the results of the research have been proven through implementation, they could be further improved by using a more powerful version of a pre-trained language model such as T5-Large, T5-3B, T5-11B or a more recent model. Also, while the approach employed allowed for a development path that is straightforward, research on how to generate the set of training queries quickly based on the database schema would further improve the speed of the development efforts and the deployment of the model to other database environments.

After our model was completed in August 2022, some research groups developed new architectures based on T5, and these models further improved the accuracy of the text-to-SQL parser, taking advantage of database schema linkages. For example, Graphix-T5 [5] combined the standard T5 encoding layer with semi-pretrained relational graph neural network (GNN) to integrate semantic information and develop scheme linkage relationship, which achieved 74% accuracy rate in exact match text-to-SQL evaluation. While most of the groups focus on how the NLP questions can generate the corresponding SQL, Zhao et al [40] improved the model from SQL to text direction (SQL-to-text), they identified the existing limitation of current synthesis methods and proposed the synthesis framework which used intermediate representation (IR) to preserve the important information from the query to construct the correspondence text of the natural language query (NLQ) as the parallel data. The synthesis framework achieved 73.1% accuracy of an exact match. Zeng et al. [41] improved the correctness and the coherence of query execution plan and the schema linkage, and reached 72.2% exact match accuracy.

These groups demonstrated that the linkages within the database schema could significantly improve the accuracy rate. Li et al. [4] not only made use of the database schema linkages but also proposed the Ranking-enhanced Encoding plus a Skeleton-aware Decoding framework for Text-to-SQL (RESDSQL) framework, which enhances the encoding system with a ranking-based approach using the schema items. By the ranking approach, the schema items with less probability can be filtered therefore it can reduce the noise from the schemas. In the decoder, they first generated the SQL structure and then the complete SQL statement, which helps reduce the challenges associated with text-to-SQL conversion.

Another approach to enhance execution performance is by improving the training technique through self-play [42]. This method leverages interactions to generate and refine SQL queries based on multiple conversational turns. The self-play approach allows the model to learn from diverse query-generation scenarios through iterative iterations. Each iteration contributes to refining the SQL statements, leading to the development of more robust and accurate text-to-SQL models over time. This iterative refinement enables the model to use less training data as the process augments the data progressively. However, this training approach has its limitations. In the real world, when someone misunderstands a concept and subsequently makes decisions based on that misunderstanding, rectifying those decisions can be challenging.

In addition to the T5 model, several other models like GPT-4 and CodeX[6] have demonstrated impressive results. Notably, GPT-4 utilized a decomposition model with schema linking and integrated chatbot functionality, leading to a remarkable accuracy rate of 85.3% as posted on the Spider Text-to-SQL Challenge leaderboard, which currently stands as the highest reported performance.

In 2023, ChatGPT is considered one of the most popular natural language processing tools due to its ability in both language and code generation. Researchers have also assessed the text-to-SQL capability of ChatGPT through zero-shot performance evaluation [43]. In this experiment, ChatGPT demonstrated impressive performance without the use of specific training data. It is believed that the text-to-SQL ability of ChatGPT can be further improved through refinement of its training strategy.