RB-SQL: A Retrieval-based LLM Framework for Text-to-SQL

Zhenhe Wu, Zhongqiu Li, Zhang Jie, Mengxiang Li, Yu Zhao, Ruiyu Fang, Zhongjiang He, Xuelong Li, Zhoujun Li, Shuangyong Song

TL;DR

This paper introduces RB-SQL, a novel retrieval-based framework designed to enhance the performance of Large Language Models (LLMs) in the Text-to-SQL task. By intelligently retrieving relevant tables, columns, and few-shot examples, RB-SQL significantly reduces noise and improves the efficiency and accuracy of SQL generation, especially for complex and large databases.

What Problem Does This Paper Address?

The Text-to-SQL task, which involves converting natural language questions into executable SQL queries, has seen significant advancements with the advent of LLMs and in-context learning. However, existing approaches often struggle with large databases containing numerous tables and columns. These methods tend to include the entire database schema in prompts, leading to excessive redundancy, irrelevant information, and potential context window limitations for LLMs. This can negatively impact reasoning ability and increase the likelihood of "hallucinations" (generating incorrect SQL). The paper aims to address this by proposing a more efficient and targeted approach to prompt engineering for Text-to-SQL.

Methods

RB-SQL is a retrieval-based framework that employs three independent retrieval modules to refine the input for LLMs:

  • Table-Retriever (TR): This module filters out irrelevant tables from a database based on their semantic similarity to the input question. It converts table information (name, column names, types) into a continuous text representation and uses a BERT-based encoder with a MaxSim-based late interaction mechanism to calculate similarity scores.
  • Column-Retriever (CR): Building upon the tables identified by TR, CR further filters relevant columns. It also converts column features (table name, column name, description, examples, etc.) into a continuous text and uses a similar BERT-based approach to calculate similarity scores with the question. TR and CR together form a SQL schema construction and linking pre-processing step.
  • SQL-Skeleton-Retriever (SR): This module is used for selecting effective few-shot examples for in-context learning. It translates training set SQL queries into "SQL skeletons" (masked versions of SQL queries) and uses a retrieval mechanism similar to TR and CR to find the top-k SQL skeletons most similar to the target question's skeleton. The corresponding original question-SQL pairs are then used as few-shot examples.

Additionally, RB-SQL incorporates SQL skeletons into the example organization process for in-context learning. This guides the LLM to think step-by-step, first by generating a skeleton and then filling in the specific details, which helps in producing more syntactically correct SQL. An optional Error Correction module is also included to refine generated SQL queries based on execution feedback.

Datasets

The paper evaluates RB-SQL on two widely used Text-to-SQL datasets:

  • BIRD: A pioneering, cross-domain dataset known for its extensive database contents, featuring over 12,751 question-SQL pairs and 95 large databases.
  • Spider: A large-scale, complex, and cross-domain semantic parsing and Text-to-SQL dataset comprising 10,181 questions and 5,693 complex SQL queries across 200 databases.

The authors also generated additional "evidence" for the Spider dataset to enhance its performance, inspired by the BIRD dataset's approach.

Key Insights and Ideas

  • Schema Pruning is Crucial: Including the entire database schema in prompts for LLMs is inefficient and detrimental. RB-SQL's retrieval-based approach to select only relevant tables and columns significantly reduces noise and improves performance.
  • SQL Skeletons as Reasoning Guides: Using SQL skeletons as an intermediate step in both example selection and organization helps LLMs break down complex queries and think more systematically, leading to better SQL generation.
  • Targeted Few-Shot Examples Matter: Simply providing random few-shot examples is less effective than selecting examples that share similar SQL structures (skeletons) with the target question.
  • Retrieval Enhances LLM Capabilities: The retrieval-based modules act as an intelligent pre-processing step, making the LLM's task easier and allowing it to focus on accurate SQL generation rather than sifting through irrelevant information.

Key Results

RB-SQL demonstrates superior performance compared to several competitive baselines on both the BIRD and Spider datasets.

  • On BIRD, RB-SQL with GPT-4 achieved an Execution Accuracy (EX) of 58.07% and a Valid Efficiency Score (VES) of 59.72%, outperforming baselines like DIN-SQL and DAIL-SQL by significant margins.
  • On Spider, RB-SQL achieved an EX of 84.91% on the dev set and 85.68% on the test set (without generated evidence), and 85.89% (dev) and 86.73% (test) with generated evidence. This represents a state-of-the-art performance, with improvements of at least 1.49% on the dev set and 0.13% on the test set.
  • An ablation study showed that each component of RB-SQL (Table-Retriever, Column-Retriever, SQL skeleton organization, and SQL-Skeleton-Retriever) contributes positively to the overall performance.

Limitations and Future Work

The authors acknowledge the following limitations:

  • Limited Adaptability of RB-models: The current RB-models are not designed to be highly adaptable to different input structures or to integrate seamlessly with pre-trained models and LLMs for more refined prompt engineering.
  • SQL Skeleton as an "Extra Step": While effective, the SQL skeleton is currently introduced as an additional step in example organization, suggesting potential for deeper integration for even better results.

Future work could focus on developing more adaptable retrieval models and exploring deeper integration of SQL skeletons into the LLM's reasoning process.

Summary updated

Abstract

Large language models (LLMs) with in-context learning have significantly improved the performance of text-to-SQL task. Previous works generally focus on using exclusive SQL generation prompt to improve the LLMs' reasoning ability. However, they are mostly hard to handle large databases with numerous tables and columns, and usually ignore the significance of pre-processing database and extracting valuable information for more efficient prompt engineering. Based on above analysis, we propose RB-SQL, a novel retrieval-based LLM framework for in-context prompt engineering, which consists of three modules that retrieve concise tables and columns as schema, and targeted examples for in-context learning. Experiment results demonstrate that our model achieves better performance than several competitive baselines on public datasets BIRD and Spider.
BibTeX
@misc{wu2024rb,
  author = {Zhenhe Wu and Zhongqiu Li and Zhang Jie and Mengxiang Li and Yu Zhao and Ruiyu Fang and Zhongjiang He and Xuelong Li and Zhoujun Li and Shuangyong Song},
  title = {{RB-SQL: A Retrieval-based LLM Framework for Text-to-SQL}},
  year = {2024},
  eprint = {2407.08273},
  archivePrefix = {arXiv},
  url = {https://arxiv.org/abs/2407.08273},
}
Figure