birdsql/bird_mini_dev

1---2license: cc-by-sa-4.03task_categories:4- table-question-answering5- question-answering6language:7- en8size_categories:9- 1K<n<10K10---11 12# BIRD-SQL Mini-Dev 13 14## Update 2025-07-0415 16We are grateful for the valuable feedback from the community over the past year regarding BIRD Mini-Dev. Based on your suggestions, we have made significant updates to the BIRD Mini-Dev dataset.17 18### For New Users19If you are new to BIRD Mini-Dev, you can download the complete databases and datasets using the following link:20[Download BIRD Mini-Dev Complete Package](https://drive.google.com/file/d/13VLWIwpw5E3d5DUkMvzw7hvHE67a4XkG/view?usp=sharing)21 22### For Existing Users23If you have already downloaded the BIRD databases, you can pull the latest data updates through Hugging Face using the following scripts:24 25```python26from datasets import load_dataset27 28# Load the dataset29dataset = load_dataset("birdsql/bird_mini_dev")30 31# Access the SQLite version32print(dataset["mini_dev_sqlite"][0])33 34# Access the MySQL version35print(dataset["mini_dev_mysql"][0])36 37# Access the PostgreSQL version38print(dataset["mini_dev_pg"][0])39```40 41We appreciate the continuous support and feedback from the community. 42 43## Overview44Here, we provide a Lite version of developtment dataset: **Mini-Dev**. This mini-dev dataset is designed to facilitate efficient and cost-effective development cycles, especially for testing and refining SQL query generation models. This dataset results from community feedback, leading to the compilation of 500 high-quality text2sql pairs derived from 11 distinct databases in a development environment. To further enhance the practicality of the BIRD system in industry settings and support the development of text-to-SQL models, we make the Mini-Dev dataset available in both **MySQL** and **PostgreSQL**.45 46Additionally, we introduce two new evaluation metrics for the Mini-Dev dataset: the **Reward-based Valid Efficiency Score (R-VES)** and the **Soft F1-Score**. These metrics aim to evaluate the efficiency and accuracy of text-to-SQL models, respectively. It is important to note that the both metrics, currently in their beta version, applies exclusively to the Mini-Dev dataset using baseline models.47 48We welcome contributions and suggestions for enhancing these metrics, particularly regarding their integration into existing leaderboards. Please do not hesitate to contact us if you are interested in these developments or have any proposals for improvements.49 50 51Below are some key statistics of the mini-dev dataset:52 53### Difficulty Distribution54- **Simple:** 30%55- **Moderate:** 50%56- **Challenging:** 20%57 58### Database Distribution59- **Debit Card Specializing:** 30 instances60- **Student Club:** 48 instances61- **Thrombosis Prediction:** 50 instances62- **European Football 2:** 51 instances63- **Formula 1:** 66 instances64- **Superhero:** 52 instances65- **Codebase Community:** 49 instances66- **Card Games:** 52 instances67- **Toxicology:** 40 instances68- **California Schools:** 30 instances69- **Financial:** 32 instances70 71### Keywords Statistic72 73- **Main Body Keywords** •SELECT •FROM •WHERE •AND •OR •NOT •IN •EXISTS •IS •NULL •IIF •CASE •CASE WHEN.74- **Join Keywords** • INNER JOIN • LEFT JOIN • ON • AS.75- **Clause Keywords** • BETWEEN • LIKE • LIMIT • ORDER BY • ASC • DESC • GROUP BY •HAVING •UNION •ALL •EXCEPT •PARTITION BY •OVER.76- **Aggregation Keywords** • AVG • COUNT • MAX • MIN • ROUND • SUM.77- **Scalar Keywords** • ABS • LENGTH • STRFTIME • JULIADAY • NOW • CAST • SUBSTR • INSTR.78- **Comparison Keywords** •= •> •< •>= •<= •!=.79- **Computing Keywords** •- •+ •* •/.80 81## Dataset Introduction82 83The dataset contains the main following resources:84 85- `database`: The database should be stored under the [`./mini_dev_data/dev_databases/`](./mini_dev_data/dev_databases/). In each database folder, it has two components:86  - `database_description`: the csv files are manufactured to describe database schema and its values for models to explore or references.87  - `sqlite`: The database contents in BIRD.88> [!NOTE] 89> You have to download the latest dev databases in order to construct database in the MySQL and PostgreSQL. If you use the SQLite version only, you can use the original dev databases.90- `data`: Each text-to-SQL pairs with the oracle knowledge evidence is stored as a json file, i.e., `mini_dev_sqlite.json` is stored on [`./mini_dev_data/mini_dev_sqlite.json`](./mini_dev_data/mini_dev_sqlite.json). In each json file, it has three main parts:91  - `db_id`: the names of databases92  - `question`: the questions curated by human crowdsourcing according to database descriptions, database contents.93  - `evidence`: the external knowledge evidence annotated by experts for assistance of models or SQL annotators.94  - `SQL`: SQLs annotated by crowdsource referring to database descriptions, database contents, to answer the questions accurately.95- `ground-truth SQL file`: The SQL file should be stored at [`./llm/mini_dev_data/mini_dev_sqlite_gold.sql`](./llm/mini_dev_data/mini_dev_sqlite_gold.sql).96- `llm`: It contains source codes to convert texts to SQLs by calling APIs from LLMs, such as  `GPT35-turbo-instruct`, `gpt-35-turbo`, `gpt-4`, `gpt-4-32k`, and `gpt-4-turbo`.97 98 99 100## Mini-Dev Dataset in MySQL and PostgreSQL101 102 103You can locate the SQL queries within the `mini_dev_mysql.json` and `mini_dev_postgresql.json` files. These queries have been transpiled from the original SQLite versions using the sqlglot package, then refined manually and with GPT-4 Turbo. After downloading the Mini-Dev dataset, each database folder will contain .sql and command.script files. Follow the instructions below to set up the database in MySQL and PostgreSQL:104 105### MySQL1061. Download and install the MySQL from the official website: https://dev.mysql.com/downloads/mysql/1072. Set the environment variables: 108```109export PATH=$PATH:/usr/local/mysql/bin110```1113. Start the MySQL server: 112```113sudo /usr/local/mysql/support-files/mysql.server start114```1154. Login to the MySQL server and create the database (password will be the one you set during the installation)116```bash117mysql -u root -p118CREATE DATABASE BIRD;119```1205. Construct the database by run the following command (You can find MySQL version database: `BIRD_dev.sql` in the `MINIDEV_mysql` folder):121```bash122mysql -u root -p BIRD < BIRD_dev.sql123```1246. Examples that how to run mysql query in the Python (with   pymysql) can be find in the [`examples/mysql_example.ipynb`](./examples/mysql_example.ipynb) file.125 1267. If you encounter the error: "this is incompatible with sql_mode=only_full_group_by", you can run the following command to disable the sql_mode:127```sql128select @@global.sql_mode;129SET GLOBAL sql_mode='{EVERYTHING SHOW IN THE ABOVE COMMAND EXCEPT ONLY_FULL_GROUP_BY}';130```131 132### PostgreSQL1331. Download and install the postgresql from the official website: https://www.postgresql.org/download/ 1342. Download the pgAdmin4 from the official website: https://www.pgadmin.org/download/ (Recommended to monitor the database)1353. In pgADmin4/terminal create a new database called `BIRD`1364. Construct the database by run the following command (You can find PostgreSQL version database:`BIRD_dev.sql` in the `MINIDEV_postgresql` folder):137```bash138psql -U USERNAME -d BIRD -f BIRD_dev.sql139```1405. Examples that how to run postgresql query in the Python (with Psycopg) can be find in the  [`examples/postgresql_example.ipynb`](./examples/postgresql_example.ipynb) file.141 142 143 144## Baseline performance on Mini-Dev Dataset145 146###  EX Evaluation147|                        | SQLite | MySQL | PostgreSQL |148|------------------------|:------:|:-----:|:----------:|149| **mixtral-8x7b**     | 21.60  | 13.60| 12.40     |150| **llama3-8b-instruct**          | 24.40  | 24.60 | 18.40      |151|**phi-3-medium-128k-instruct**            | 30.60 | 25.00 | 21.60 |152| **gpt-35-turbo-instruct** | 33.60  | 31.20 | 26.60      |153| **gpt-35-turbo**       | 38.00  | 36.00 | 27.40      |154| **llama3-70b-instruct**         | 40.80  | 37.00 | 29.40      |155| **TA + gpt-35-turbo**       | 41.60  | - | -      |156| **TA + llama3-70b-instruct**  | 42.80 | -    |-     |157| **gpt-4-turbo**        | 45.80  | 41.00 | 36.00      |158| **gpt-4-32k**        | 47.00  | 43.20 | 35.00       |159| **gpt-4**        | 47.80  | 40.80 | 35.80      |160| **TA + gpt-4-turbo**        | 58.00  | - | -      |161| **TA + gpt-4o**        | 63.00  | - | -      |162 163 164### R-VES Evaluation165|                        | SQLite | MySQL | PostgreSQL |166|------------------------|:------:|:-----:|:----------:|167| **mixtral-8x7b**     | 20.41  | 12.99 | 14.16     |168| **llama3-8b-instruct**          | 23.27  | 23.66 | 17.90      |169|**phi-3-medium-128k-instruct**| 29.54 | 24.12 | 21.07 |170| **gpt-35-turbo-instruct** | 32.28  | 30.39 | 26.14      |171| **gpt-35-turbo**       | 37.33  | 34.94 | 26.80      |172| **llama3-70b-instruct**         | 39.02  | 35.82 | 28.80      |173| **TA + gpt-35-turbo**       | 40.59  | - | -      |174| **TA + llama3-70b-instruct**  | 41.37 | -    | -      |175| **gpt-4-turbo**        | 44.79  | 39.37 | 35.23      |176| **gpt-4-32k**        | 45.29  | 42.79 | 34.59     |177| **gpt-4**        | 45.91  | 39.92 | 35.24       |178| **TA + gpt-4-turbo**        | 56.44   | - | -      |179| **TA + gpt-4o**        | 60.86  | - | -      |180 181 182### Soft F1-Score Evaluation183|                        | SQLite | MySQL | PostgreSQL |184|------------------------|:------:|:-----:|:----------:|185| **mixtral-8x7b**     | 22.95  | 13.79 | 14.70      |186| **llama3-8b-instruct**          | 27.87  | 27.49 | 19.35      |187|**phi-3-medium-128k-instruct**                | 35.33 | 28.73  | 24.11  |188| **gpt-35-turbo-instruct** | 36.34  | 33.85 | 28.30      |189| **gpt-35-turbo**       | 41.84  | 40.75 | 30.22      |190| **TA + gpt-35-turbo**       | 44.25  | - | -      |191| **llama3-70b-instruct**         | 44.38  | 40.95 | 31.43      |192| **TA + llama3-70b-instruct**  | 46.66 | -    | -      |193| **gpt-4-turbo**        | 50.08  | 45.96 | 38.36      |194| **gpt-4-32k**        | 51.92  | 47.38 | 39.55      |195| **gpt-4**        | 52.69 | 45.78 | 38.96       |196| **TA + gpt-4-turbo**        | 62.40   | - | -      |197| **TA + gpt-4o**        | 66.97  | - | -      |198 199 200 201## Citation202 203Please cite the repo if you think our work is helpful to you.204 205```206@article{li2024can,207  title={Can llm already serve as a database interface? a big bench for large-scale database grounded text-to-sqls},208  author={Li, Jinyang and Hui, Binyuan and Qu, Ge and Yang, Jiaxi and Li, Binhua and Li, Bowen and Wang, Bailin and Qin, Bowen and Geng, Ruiying and Huo, Nan and others},209  journal={Advances in Neural Information Processing Systems},210  volume={36},211  year={2024}212}213```