---
library_name: transformers
tags:
- text-to-sql
- sql
license: apache-2.0
language:
- en
base_model:
- Qwen/Qwen2.5-Coder-7B-Instruct
datasets:
- AlioLeuchtmann/BIRD_SPIDER_Qwen2.5-Coder-32B
---
BIRD-bench: https://bird-bench.github.io/
## 57% on BIRD private test Set (Qwen 2.5 Coder Models might have been trained on BIRD dev, which could explain the Performance Drop)
## 60% on BIRD dev one shot
## 70% on BIRD dev 4 shot
Offical Results on BIRD Test Set
| Metric | Simple | Moderate | Challenging | Total |
| ----------- | -----: | -------: | ----------: | ----: |
| **Count** | 949 | 555 | 285 | 1789 |
| **EX** | 65.33 | 53.33 | 39.65 | 57.52 |
| **Soft F1** | 66.31 | 55.51 | 41.48 | 59.01 |
| **R-VES** | 61.15 | 47.73 | 36.71 | 53.09 |
## Model Details
SFT of Qwen2.5-Coder-7B
Dataset is a combination of BIRD and Spider; answers were created through knowledge distillation with CoT prompting from Qwen2.5-Coder-32B, and filtered for correctness.
We increased the BIRD dev set performance from 51.1% to 60%.
Our model beats much larger universal models like GPT-4o and Gemini 1.5 Pro, demonstrating the effectiveness of black-box knowledge distillation for constrained domains.
After training, this checkpoint even exceeds the performance of its teacher model, Qwen2.5-Coder-32B, which can be achieved by filtering the teacher’s outputs for Quality
## Prompt:
Single Prompt used throughout the Dataset so best used as follows:
```sql
CREATE TABLE geographic\n(\n city TEXT not null\n primary key,\n county TEXT null,\n region TEXT null\n) \n /* \n 2 example rows: \n SELECT * FROM geographic LIMIT 2; \n city county region \nalameda alameda county bay area \n alamo contra costa county bay area \n */\n\n
CREATE TABLE generalinfo\n(\n id_restaurant INTEGER not null\n primary key,\n label TEXT null,\n food_type TEXT null,\n city TEXT null,\n review REAL null,\n foreign key (city) references geographic(city)\n on update cascade on delete cascade\n) \n /* \n 2 example rows: \n SELECT * FROM generalinfo LIMIT 2; \n id_restaurant label food_type city review \n 1 sparky's diner 24 hour diner san francisco 2.3 \n 2 kabul afghan cuisine afghani san carlos 3.8 \n */\n\nCREATE TABLE location\n(\n id_restaurant INTEGER not null\n primary key,\n street_num INTEGER null,\n street_name TEXT null,\n city TEXT null,\n foreign key (city) references geographic (city)\n on update cascade on delete cascade,\n foreign key (id_restaurant) references generalinfo (id_restaurant)\n on update cascade on delete cascade\n) \n /* \n 2 example rows: \n SELECT * FROM location LIMIT 2; \n id_restaurant street_num street_name city \n 1 242 church st san francisco \n 2 135 el camino real san carlos \n */\n\n
-- External Knowledge: Atlantic Ave refers to street_name = 'atlantic ave'; rating refers to review\n
-- Using valid SQLite and understanding External Knowledge, answer the following question for the tables provided above.\n
-- What is the rating of each restaurant reviews on Atlantic Ave?\n
Generate the SQL after thinking step by step:\n
```
```python
def bird_gpt_template_no_format(question, commonsense, schema):
return f"""{schema}
-- External Knowledge: {commonsense}
-- Using valid SQLite and understanding External Knowledge, answer the following question for the tables provided above.
-- {question}
Generate the SQL after thinking step by step:
"""
```
### Generate Schema:
```python
def generate_schema_prompt(db_path, num_rows=None):
# extract create ddls
'''
:param root_place:
:param db_name:
:return:
'''
full_schema_prompt_list = []
conn = sqlite3.connect(db_path)
# Create a cursor object
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()
schemas = {}
for table in tables:
if table == 'sqlite_sequence':
continue
cursor.execute("SELECT sql FROM sqlite_master WHERE type='table' AND name='{}';".format(table[0]))
create_prompt = cursor.fetchone()[0]
schemas[table[0]] = create_prompt
if num_rows:
cur_table = table[0]
if cur_table in ['order', 'by', 'group','transaction'] or ' ' in str(cur_table).strip() or '-' in str(cur_table).strip():
cur_table = '"{}"'.format(cur_table)
cursor.execute("SELECT * FROM {} LIMIT {}".format(cur_table, num_rows))
column_names = [description[0] for description in cursor.description]
values = cursor.fetchall()
rows_prompt = nice_look_table(column_names=column_names, values=values)
verbose_prompt = "/* \n {} example rows: \n SELECT * FROM {} LIMIT {}; \n {} \n */".format(num_rows,
cur_table,
num_rows,
rows_prompt)
schemas[table[0]] = "{} \n {}".format(create_prompt, verbose_prompt)
for k, v in schemas.items():
full_schema_prompt_list.append(v)
schema_prompt = "\n\n".join(full_schema_prompt_list)
return schema_prompt
```
### System Prompt:
Default Qwen2.5 System Prompt
```python
def preprocess_prompt(prompt):
return f'''<|im_start|>system
You are Qwen, created by Alibaba Cloud. You are a helpful assistant.<|im_end|>
<|im_start|>user
{prompt}<|im_end|>
<|im_start|>assistant
'''
```
### Generation Config:
- No Sampling for best Performance
### Restrictions:
- only trained on SQLite Dialect
- only trained in English
- Did not care to keep any other Skills than Text to SQL