Adapt and Decompose: Efficient Generalization of Text-to- SQL via Domain Adapted Least-To-Most Prompting (2023)

Abstract: Cross-domain and cross-compositional generalization of Text-to-SQL semantic parsing is a challenging task. Existing Large Language Model (LLM) based solutions rely on inference-time retrieval of few-shot exemplars from the training set to synthesize a run-time prompt for each Natural Language (NL) test query. In contrast, we devise an algorithm which performs offline sampling of a minimal set-of few-shots from the training data, with complete coverage of SQL clauses, operators and functions, and maximal domain coverage within the allowed token length. This allows for synthesis of a fixed Generic Prompt (GP), with a diverse set-of exemplars common across NL test queries, avoiding expensive test time exemplar retrieval. We further auto-adapt the GP to the target database domain (DA-GP), to better handle cross-domain generalization; followed by a decomposed Least-To-Most-Prompting (LTMP-DA-GP) to handle cross-compositional generalization. The synthesis of LTMP-DA-GP is an offline task, to be performed one-time per new database with minimal human intervention. Our approach demonstrates superior performance on the KaggleDBQA dataset, designed to evaluate generalizability for the Text-to-SQL task. We further showcase consistent performance improvement of LTMP-DA-GP over GP, across LLMs and databases of KaggleDBQA, highlighting the efficacy and model agnostic benefits of our prompt based adapt and decompose approach.

## introduction recently, large language models (llms) such as gpt3 @xcite , @xcite , @xcite , pretrained with massive volumes of data have shown improved performance for multiple reasoning tasks using incontext learning @xcite , including program synthesis @xcite @xcite and semantic parsing @xcite @xcite . there are a few recent approaches where llms are specifically used for text-to-sql semantic parsing in a (i) zero-shot setting @xcite @xcite where only the test natural language (nl) query constitutes the prompt, (ii) few-shot setting where exemplars similar to the test query in the target domain are retrieved from the available training data and appended to the test nl query to constitute the prompt @xcite @xcite . for this setting the available nl-sql pairs would belong to domains that are different from the target database domain (iii) few-shot setting where exemplars are sampled nl-sql queries available for the target-domain with maximum coverage of compositions @xcite @xcite @xcite . in this paper, we are mainly interested in (ii) i.e. cross-domain generalization along with the scenario where the test queries may not have the set-of compositions covered in the training data (cross-composition generalizability). moreover, considering a purely cross-domain setting, as opposed to (iii) above, we assume no availability of exemplars belonging to the target databases. one solution to this setting is manual synthesis of few-shots for every new target database from scratch. however, this process is very tedious, time-consuming and also does not ensure diversity in the few-shots, with good coverage of sql operators. thus, there is a need for an efficient approach which can exploit available nl-sql pairs from distinct domains, to intelligently sample few-shots and design prompts. synchromesh @xcite and @xcite have a similar setting, except they retrieve exemplars during runtime (during inference) by selecting nl queries as exemplars with similarity based on (a) nl query semantics @xcite or (b) target sqls (target similarity tuning) @xcite or (c) llm generated 'skill' based nl representation, focusing on program compositions and ignoring the surface nl forms @xcite . this reliance on inference-time retrieval of similar few-shots from the available data to build a run-time prompt and generate sql for a test nl query, results in a less efficient solution. as opposed to this, we devise an algorithm which samples a minimal set-of fewshots from the training data ensuring complete coverage of sql clauses, operators and functions and maximum coverage of database domains, which fits into the token length restriction. we append these few-shots with the out-of-distribution test nl query to define what we term as a generic prompt (gp), which is further used to generate the corresponding sql. the gp is generated offline and is common across distinct test queries, resulting in a more time-efficient solution obviating the need for real time retrieval. we further auto-adapt the gp to (a) the target database domain, and refer to it as domain adapted gp (da-gp), to better handle cross-domain generalization, (b) decompose it into a least-to-most-prompting approach @xcite (ltmp-gp) to better handle crosscompositional generalization, and (c) combine the approaches (a) and (b) to exploit their complementary benefits (ltmp-da-gp). in line with our motivation, formation of ltmp-da-gp is an efficient solution, as it is an offline task to be performed onetime per new database and is mostly programmatic with minimal human intervention (only needed for validation of prompts). we further demonstrate a consistent performance improvement of (a) and (b) over the base gp and (c) over (a) and (b) on the databases of kaggle-dbqa dataset @xcite , designed to evaluate the generalizability of the text-to-sql task using distinct llms. moreover, our approach not only yields an efficient solution, but also yields best performance for the used llms on kaggledbqa. following are our main contributions: kaggle-dbqa @xcite : this is a cross-domain text-to-sql evaluation dataset with real world databases (db). it covers a total of 8 dbs, viz. (i) nuclear (10,22), (ii) crime (9,18), (iii) pesticide (16, 34), (iv) mathscore (9,19), (v) baseball (12,27), (vi) fires (12, 25), (vii) whatcd @xcite , and (viii) soccer (6,12), indicating finetuning and test examples, respectively. as we assume a purely cross-domain setting with no availability of in-domain few-shots, we do not use the fine-tuning samples but use only the test samples to evaluate our proposed llm based pipelines. we prefer kaggle-dbqa over other cross-domain and composition evaluation datasets such as spider-cg @xcite , because it is a real-web dataset and contains fewer test queries allowing us to showcase the efficacy of our approach using commercial llms with low cost overheads. ## large language models (llms) the literature has demonstrated state-of-the-art fewshot performance for nl-to-sql @xcite @xcite tasks, with codex (chen et al., 2021a) (codeda-vincci) (chen et al., 2021b) and gpt4 foot_0 from openai. however, codex is discontinued by ope-nai 2 and the cost of gpt4 is very high. instead, we use two llms to demonstrate the efficacy of the proposed pipeline, viz. openai's gpt-3.5-turbo (chatgpt), which is 60x cheaper than gpt4 and text-da-vinci-003, which is 6x cheaper than gpt4, both with 175b parameters and 4096 token length restriction foot_2 . we have not used other open-source models pretrained on code, such as codet5+ @xcite (768 token length) or codegen (2048 token length) @xcite for our study as they do not offer the token length required for our prompt (4k). ## conclusion in this paper, we leverage llms for the crossdomain and cross-composition generalization of text-to-sql. as opposed to prior approaches, which rely on inference-time retrieval of exemplars similar to the test query; we devise an algorithm which samples diverse set-of exemplars with complete coverage of sql operators, clauses and functions and maximal coverage of databases to form the generic prompt (gp), which is common across every test sample obviating the need for dynamic exemplar retrieval and thus leading to an efficient approach. we further perform programmatic domain-adaptation of this prompt da-gp, which consistently showcases performance improvement across multiple databases and llms better achieving domain generalization. we further decompose the exemplars of da-gp, to execute a novel pipeline of least-to-most-prompting (ltmp-da-gp) for compositional generalization of the complex nl-to-sql task. this pipeline showcases consistent improvement over gp across multiple databases and llms demonstrating complementary benefits of the adapt and decompose steps and thus proving the efficacy of our approach. our pipeline, being offline with minimal human inter-vention, is not only efficient; but also yields the best performance reported in the literature with the experimented llms, on kaggledbqa dataset designed to test generalizability of nl-to-sql task.