| | --- |
| | 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 |
| |
|
| |
|
| |
|
