LLamaRecipes/end-to-end-use-cases/coding/text2sql/tool

Text2SQL Evaluation and Fine-Tuning Tools for Llama Models

Overview

This folder contains the scripts for evaluating Llama (original and fine-tuned) models on Text2SQL tasks using the popular BIRD dataset, generating fine-tuning datasets, and fine-tuning Llama 3.1 8B with the datasets.

We have significantly simplified the original eval scripts from the BIRD repo for Llama models hosted via Meta's Llama API or Together.ai, so you can quickly evaluate in 1-2-3 steps how well different Llama models perform on the Text2SQL task.

We have also provided end-to-end scripts for generating datasets and fine-tuning a quantized Llama 3.1 8B model to gain a 165% accuracy improvement over the original model.

Quick Start on Evaluating Llama on Text2SQL

First, run the commands below to create a new Conda environment and install all the required packages for Text2SQL evaluation and fine-tuning:

git clone https://github.com/meta-llama/llama-cookbook
cd llama-cookbook/end-to-end-use-cases/coding/text2sql/tool
conda create -n llama-text2sql python=3.10
conda activate llama-text2sql
pip install -r requirements.txt

Then, follow the steps below to evaluate Llama 3 & 4 models on Text2SQL using the BIRD benchmark:

1. Get the DEV dataset:

   cd data
   sh download_dev_unzip.sh
   

2. Open llama_eval.sh and set YOUR_API_KEY to your Llama API key or Together API key, then uncomment a line that starts with model= to specify the Llama model to use for the text2sql eval.

3. Run the evaluation script sh llama_eval.sh, which will use the BIRD DEV dataset (1534 examples in total) with external knowledge turned on to run the Llama model on each text question and compare the generated SQL with the gold SQL.

After the script completes, you'll see the accuracy of the Llama model on the BIRD DEV text2sql. For example, the total accuracy is about 54.24% with YOUR_API_KEY set to your Llama API key and model='Llama-3.3-70B-Instruct', or about 35.07% with YOUR_API_KEY set to your Together API key and model=meta-llama/Meta-Llama-3.1-8B-Instruct-Turbo.

Note: To compare your evaluated accuracy of your selected Llama model with other results in the BIRD Dev leaderboard, click here.

Evaluation Results

Below are the results of the Llama models we have evaluated on the BIRD DEV dataset:

Model	Llama API Accuracy	Together Accuracy
Llama 3.1 8b	-	35.66%
Llama 3.3 70b	54.11%	54.63%
Llama-3.1-405B	-	55.80%
Llama 4 Scout	44.39%	43.94%
Llama 4 Maverick	44.00%	41.46%

Supported Models

Together AI Models

• meta-llama/Meta-Llama-3.1-8B-Instruct-Turbo
• meta-llama/Llama-3.3-70B-Instruct-Turbo
• meta-llama/Llama-4-Maverick-17B-128E-Instruct-FP8
• meta-llama/Llama-4-Scout-17B-16E-Instruct
• other Llama models hosted on Together AI

Llama API Models

• Llama-3.3-8B-Instruct
• Llama-3.3-70B-Instruct
• Llama-4-Maverick-17B-128E-Instruct-FP8
• Llama-4-Scout-17B-16E-Instruct-FP8
• other Llama models hosted on Llama API

Evaluation Process

1. SQL Generation: llama_text2sql.py sends natural language questions to the specified Llama model and collects the generated SQL queries.

2. SQL Execution: text2sql_eval.py executes both the generated SQL and ground truth SQL against the corresponding databases, then continues with steps 3 and 4 below.

3. Result Comparison: The results from executing the generated SQL are compared with the results from the ground truth SQL to determine correctness.

4. Accuracy Calculation: Accuracy scores are calculated overall and broken down by difficulty levels (simple, moderate, challenging).

Preparing Fine-tuning Dataset

Using the TRAIN to prepare for supervised fine-tuning

1. Get the TRAIN dataset:

   cd data
   sh download_train_unzip.sh
   

2. Create the dataset

cd fine_tuning
python create_sft_dataset.py --input_json ../data/train/train.json --db_root_path ../data/train/train_databases

This will create train_text2sql_sft_dataset.json and test_text2sql_sft_dataset.json using the TRAIN set. Each line in the json files is in the conversation format ready for fine-tuning:

{"messages":[{"content":"You are a text to SQL query translator. Using the SQLite DB Schema and the External Knowledge, translate the following text question into a SQLite SQL select statement.","role":"system"},{"content":"-- DB Schema: <DB_SCHEMA>\n\n-- External Knowledge: <KNOWLEDGE_FROM_TRAIN>\n\n-- Question: <TEXT_QUESTION>","role":"user"},{"content":"<GOLD_SQL>","role":"assistant"}]}

Supervised Fine-tuning

First, you need to login to HuggingFace (via running huggingface-cli login and enter your HF token) and have been granted access to the Llama 3.1 8B Instruct model.

Then run python trl_sft.py. After the fine-tuning completes, you'll see the fine-tuned model saved to llama31-8b-text2sql-fine_tuning.

After running tensorboard --logdir ./llama31-8b-text2sql-fine_tuning you can open http://localhost:6006 to see the train loss chat etc:

Evaluating the fine-tuned model

First, modify llama_eval.sh to use the fine-tuned model:

YOUR_API_KEY='finetuned'
model='fine_tuning/llama31-8b-text2sql'

Then run sh llama_eval.sh to evaluate the fine-tuned model. The accuracy on the BIRD DEV dataset is about 37.16%. This is a 165% improvement over the model before fine-tuning, which has an accuracy of about 14.02% on the same dataset - you can confirm this by comparing the fine-tuned model's accuracy above with the original model's accuracy by modifying llama_eval.sh to use the original model:

YOUR_API_KEY='huggingface'
model='meta-llama/Llama-3.1-8B-Instruct'

Then running sh llama_eval.sh to evaluate the original model.

Note: We are using the 4-bit quantized Llama 3.1 8b model to reduce the memory footprint and improve the efficiency (as shown in the code nippet of llama_text2sql.py below), hence the accuracy of the quantized version (14.02%) is quite lower than the accuracy of the original Llama 3.1 8b (35.66%).

  bnb_config = BitsAndBytesConfig(
      load_in_4bit=True,
      bnb_4bit_use_double_quant=True,
      bnb_4bit_quant_type="nf4",
      bnb_4bit_compute_dtype=torch.bfloat16,
  )

Creating a reasoning dataset from the TRAIN dataset

The script create_reasoning_dataset.py is used to create a reasoning dataset from the TRAIN dataset by asking Llama 3.3 70B to generate the reasoning for each text question and its corresponding gold SQL. The intent is to use the reasoning dataset to fine-tune the Llama model to improve the accuracy of the generated SQL.

To run the script, use the following commands:

cd fine_tuning
python create_reasoning_dataset.py --input_json ../data/train/train.json --db_root_path ../data/train/train_databases

This will create text2sql_cot_dataset dataset in the conversation format ready for fine-tuning. Each example in the dataset is generated from the code snippet below:

prompt = f"""
-- DB Schema: {db_schema}
-- External Knowledge: {external_knowledge}
-- Text Question: {question}
"""
cot = {
    "messages": [
        {
            "role": "system",
            "content": "You are a text to SQL query translator. Using the SQLite DB Schema and the External Knowledge, generate the step-by-step reasoning and the final SQLite SQL select statement from the text question.",
        },
        {"role": "user", "content": prompt},
        {"role": "assistant", "content": reasoning},
    ]
}

The prompt for Llama 3.3 70B to generate the reasoning above is:

You are a text to SQL query translator. Based on the DB Schema and External Knowledge, given the Text Question Input and its Gold SQL Output below, generate the step-by-step reasoning to infer the Gold SQL Output from the Text Question Input.

-- DB Schema: {db_schema}
-- External Knowledge: {external_knowledge}
-- Text Question Input: {question}
-- Gold SQL Output: {gold_SQL}

Your response should be as follows:\n\n
Let me think through this step by step:\n\n1. First, I need to consider...\n2. Then...\n3. Next...\n...\n\nFinally, the SQL statement for the text question is:
```sql ...```\n

"""

Next Steps

1. Fine-tune the model with the reasoning dataset and evaluate its accuracy.
2. Add a Colab notebook for fine-tuning and evaluation.
3. Try reinforcement fine-tuning to improve the accuracy further with reasoning.
4. Use torchtune for full and non-quantized fine-tuning of Llama 3.3 70b and Llama 4 models.
5. Introduce agent to try to improve the accuracy further.
6. Expand the tool to support other databases.