|
|
--- |
|
|
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 <br> |
|
|
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. <br> |
|
|
<br> |
|
|
We increased the BIRD dev set performance from 51.1% to 60%. <br> |
|
|
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. <br> |
|
|
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 <br> |
|
|
|
|
|
|
|
|
|
|
|
 |
|
|
|
|
|
|
|
|
## 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 <br> |
|
|
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 <br> |
|
|
-- External Knowledge: Atlantic Ave refers to street_name = 'atlantic ave'; rating refers to review\n <br> |
|
|
-- Using valid SQLite and understanding External Knowledge, answer the following question for the tables provided above.\n <br> |
|
|
-- What is the rating of each restaurant reviews on Atlantic Ave?\n <br> |
|
|
Generate the SQL after thinking step by step:\n <br> |
|
|
``` |
|
|
|
|
|
```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 |
|
|
|
|
|
|
|
|
|
