ACT - SQL : In-Context Learning for Text-to- SQL with Automatically-Generated Chain-of-Thought (2023)

Abstract: Recently Large Language Models (LLMs) have been proven to have strong abilities in various domains and tasks. We study the problem of prompt designing in the text-to-SQL task and attempt to improve the LLMs’ reasoning ability when generating SQL queries. Besides the trivial few-shot in-context learning setting, we design our chain-of-thought (CoT) prompt with a similar method to schema linking. We provide a method named ACT-SQL to automatically generate auto-CoT exemplars and thus the whole process doesn’t need manual labeling. Our approach is cost-saving since we only use the LLMs’ API call once when generating one SQL query. Furthermore, we extend our in-context learning method to the multi-turn text-to-SQL task. The experiment results show that the LLMs’ performance can benefit from our ACT-SQL approach. Our approach achieves SOTA performance on the Spider dev set among existing in-context learning approaches.

## introduction the text-to-sql task @xcite aims to translate the natural language question into the corresponding sql query with the given database schema. it is the key technique to establish the natural language interface on relational databases, which can help common users access data from relational databases in a more convenient way. recent studies in text-to-sql research have primarily centered on the development of semantic parsers within the framework of cross-domain analysis. in cross-domain text-to-sql datasets such as spider @xcite , sparc @xcite , and cosql @xcite , the databases employed in the train set, dev set, and test set do not overlap. prior research endeavors have focused on training specialized text-to-sql models and optimizing their structural components to enhance overall performance. notably, these efforts have yielded impressive model performances across various datasets. nevertheless, the construction of such models necessitates a substantial number of highquality training examples and entails significant time investments for finetuning. moreover, these models often possess intricate structures, rendering their deployment challenging. recent research has provided empirical evidence establishing the substantial capabilities of large language models (llms), such as @xcite and chatgpt @xcite , across a wide spectrum of domains and tasks. as the scale of llms continues to expand, scholarly investigations have revealed the presence of emergent abilities @xcite exclusive to larger llms and absent in their smaller counterparts. therefore, the latest studies employ llms in the context of the text-to-sql task, utilizing the incontext learning method @xcite . owing to the impressive performance demonstrated by llms in zero-shot or few-shot prompting scenarios, the need for extensive finetuning using an abundance of training examples has been rendered unnecessary. consequently, the integration of llms in the text-to-sql process yields notable time and cost savings. nonetheless, contemporary in-context learning approaches for text-to-sql encounter certain challenges. for instance, @xcite , in comparison to sota finetuned models, employ a simplistic prompt designing approach that yields relatively subpar performance. similarly, pourreza and rafiei (2023) employs a convoluted workflow to generate the final sql query, resulting in achieving sota performance on the test set of the spider dataset. however, this approach proves time-consuming and resource-intensive, as it ne-cessitates multiple api calls to llms during the query generation process. moreover, the recent advancements in in-context learning methods for text-to-sql have yet to be extended to multi-turn datasets, such as sparc, cosql, and dir @xcite . despite the proficiency of llms as zero-shot and few-shot learners, the mere superficial prompt design fails to fully activate their capabilities. to address this limitation, @xcite proposes a novel prompting technique called chain-of-thought (cot). through the cot method, the prompt text encompasses a comprehensive thinking process that guides llms towards accurate deduction of answers. notably, the cot method mirrors the sequential nature of human reasoning, wherein intermediate answers are obtained before arriving at a final conclusion. given the intricate nature of the text-to-sql task, the cot method proves highly suitable, as generating the sql query entails complex reasoning processes. however, existing cot methodologies necessitate extensive time investments in the selection of canonical examples and manual labeling. the text-to-sql task lacks an automated approach for generating cot sequences. in this paper, we propose our in-context learning method for the text-to-sql task with the automatically-generated cot. first, under the zeroshot setting, we study the influence on llms' performance caused by the input format of the database schema. second, under the few-shot setting, we provide a hybrid strategy to select exemplars and study the influence on llms' performance caused by the number of exemplars. our experiment results show that the strategy is effective. third, we present our approach named act-sql to generate auto-cot for the dataset training example consisting of the database schema, the question, and the corresponding sql query. the experiment results show that the generated auto-cot can indeed improve the llms' performance. the act-sql achieves the sota performance on the spider dev set among existing in-context learning methods. in addition, the act-sql does not need to use extra llms' api calls, which means that our workflow is relatively fast and cheap. finally, we apply our approach in multi-turn text-to-sql datasets including sparc and cosql and achieve comparable accuracy scores with finetuned models. our main contributions can be summarized as follows: ## methodology with the in-context learning method, the sql generation process can be formulated as @xmath0, d, q, e). i represents the instruction. d represents the database schema. q represents the question. @xmath1, q 1 , p 1 ), • • • , (d n , q n , p n )] is the list of exemplars where p i is the answer prompt which contains the correct sql for the i-th exemplar. thus the performance of llms is mainly influenced by the database prompt style, the exemplar selection strategy, and the exemplar prompt design. in this section, we first describe the prompt styles of the database schema. then we state our strategy of exemplar selection for the few-shot learning setting. furthermore, we introduce our act-sql approach, i.e. the automatically generated cot method for constructing effective answer prompts. finally, we extend our approach to the multi-turn text-to-sql task. ## conclusion llms have shown a strong ability in various domains with the in-context learning method. the latest studies have attempted to use llms to solve the text-to-sql task. however, previous prompting approaches either perform worse than finetuned models or need to use llms' api call many times. we design the cot prompt which can be automatically generated and propose our act-sql approach. the act-sql approach uses llms' api call only once to generate one sql query. the experiment results prove that our approach achieves state-ofthe-art performance on the spider dev set among existing in-context learning approaches. furthermore, we extend our approach to multi-turn text-to-sql datasets.