# :mag_right:NL2SQL360
<div align="center"><img width="25%" src="./assets/nl2sql360.png"><img width="75%" src="./assets/leaderboard.png"></div>
## :dizzy:Overview
**NL2SQL360** is a testbed for fine-grained evaluation of NL2SQL solutions. Our testbed integrates existing NL2SQL benchmarks, a repository of NL2SQL models, and various evaluation metrics, which aims to provide an intuitive and user-friendly platform to enable both standard and customized performance evaluations. Users can utilize **NL2SQL360** to assess different NL2SQL methods against established benchmarks or tailor their evaluations based on specific criteria. This flexibility allows for testing solutions in specific data domains or analyzing performance on different characteristics of SQL queries.
In addition, we propose **SuperSQL**, which achieves competitive performance with execution accuracy of **87%** and **62.66%** on the Spider and BIRD test sets, respectively.
## :tada:News
[24/9/23] We release NL2SQL360 `1.1.0` version, which supports two new metrics **Reward-based VES** (RVES), **Soft-F1 Score** (F1), from [BIRD-Mini-Dev ](https://github.com/bird-bench/mini_dev) dataset. **Please update your package with** `pip install --upgrade nl2sql360`.
[24/9/1] We have released our **[Homepage & Leaderboard](https://nl2sql360.github.io)!**
[24/8/2] We have released CLI usage / Code usage tutorials. **Please [check out](#rocketquick-start)!**
[24/7/30] We have refactored the code and released the official python package([nl2sql360 · PyPI](https://pypi.org/project/nl2sql360)). **Stay tuned for the complete documents!**
[24/6/30] Our paper [The Dawn of Natural Language to SQL: Are We Fully Ready?](https://arxiv.org/abs/2406.01265) has been accepted by VLDB'24.
## :balloon:Features
- **Easy-to-use Evaluation**: Command Line Usage / Python Code Usage.
- **Integrated Metrics**: Execution Accuracy / Exact-Match Accuracy / Valid Efficiency Score / Question Variance Testing.
- **Multi-angle Performance**: Fine-grained performance (JOIN, Sub-query, etc.) / Scenario-based (Business Intelligence, etc.)
## :wrench:Installation
```bash
pip install nl2sql360
```
## :rocket:Quick Start
<details><summary>Prepare Dataset</summary>
Download NL2SQL dataset to `DATASET_DIR_PATH`. The directory structure should be like:
```bash
DATASET_DIR_PATH:
├─database
│ ├─academic
│ │ ├─academic.sqlite
│ ├─college
│ │ ├─college.sqlite
├─dev.json
├─tables.json
```
- `database` directory contains multiple subdirectories, which include the corresponding `sqlite` database file.
- `dev.json` is the samples file in JSON format, which at least contains three keys for `NL Question`, `Gold SQL`, `Databae Id`. You can also add the key for `Sample Complexity` for categorizing samples into different difficulty levels.
- `tables.json` contains all database schema, following [Spider Preprocess Procedure](https://github.com/taoyds/spider/tree/master/preprocess). **You can also ignore this file if you do not want to evaluate Exact-Match Accuracy Metic.**
- Note that the name for `database` directory, samples file `dev.json` and tables file `tables.json` can be changed.
</details>
<details><summary>Import Dataset into NL2SQL360</summary>
- CLI Usage:
- Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/dataset_spider.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/dataset_spider.yaml).
- Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:
```bash
nl2sql360 dataset DATASET_YAML_PATH
```
- Code Usage:
- Create / Modify Python File following [NL2SQL360/examples/py_examples/dataset_import.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/dataset_import.py).
- Run the python file to import dataset.
</details>
<details><summary>Evaluation NL2SQL Model</summary>
- CLI Usage:
- Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/evaluation.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/evaluation.yaml).
- Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:
```bash
nl2sql360 evaluate DATASET_YAML_PATH
```
- Code Usage:
- Create / Modify Python File following [NL2SQL360/examples/py_examples/evaluation.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/evaluation.py).
- Run the python file to evaluate the model.
</details>
<details><summary>Query Multi-angle Performance</summary>
- CLI Usage:
- Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/report.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/report.yaml).
- Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:
```bash
nl2sql360 report DATASET_YAML_PATH
```
- The generated report will be in `save_path` specified in the YAML file.
- Code Usage:
- Create / Modify Python File following [NL2SQL360/examples/py_examples/report.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/report.py).
- Run the python file to generate report.
</details>
<details><summary>Delete History Cache</summary>
- CLI Usage:
- Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/delete_history.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/delete_history.yaml).
- Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:
```bash
nl2sql360 delete DATASET_YAML_PATH
```
- Code Usage:
- Create / Modify Python File following [NL2SQL360/examples/py_examples/delete_history.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/delete_history.py).
- Run the python file to delete dataset / evaluation cache.
</details>
## :dart:Road Map
:white_check_mark:Release **NL2SQL360** evaluation code.
:white_check_mark:Release **NL2SQL360** experiments data.
:white_check_mark:Release **NL2SQL360** Official Python Package.
## :floppy_disk:Experiment Data
We have released all experiment data used in our paper.
[Download Link](https://drive.google.com/drive/folders/1SDwY30H2r6XNYeS53wcZNocVFm0hVgpz?usp=sharing)
## :pushpin:Citation
```
@misc{li2024dawn,
title={The Dawn of Natural Language to SQL: Are We Fully Ready?},
author={Boyan Li and Yuyu Luo and Chengliang Chai and Guoliang Li and Nan Tang},
year={2024},
eprint={2406.01265},
archivePrefix={arXiv},
primaryClass={id='cs.DB' full_name='Databases' is_active=True alt_name=None in_archive='cs' is_general=False description='Covers database management, datamining, and data processing. Roughly includes material in ACM Subject Classes E.2, E.5, H.0, H.2, and J.1.'}
}
```
Raw data
{
"_id": null,
"home_page": "https://github.com/HKUSTDial/NL2SQL360",
"name": "nl2sql360",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.8.0",
"maintainer_email": null,
"keywords": "NL2SQL, Text-to-SQL, T2S, SQL, Database, NLIDB",
"author": "boyanli",
"author_email": "boyanli@hkust-gz.edu.cn",
"download_url": "https://files.pythonhosted.org/packages/11/29/f887dc506ad660364cc87dfc54362ea31c02a617287508ccfa36ace911c3/nl2sql360-1.1.0.tar.gz",
"platform": null,
"description": "# :mag_right:NL2SQL360\r\n\r\n<div align=\"center\"><img width=\"25%\" src=\"./assets/nl2sql360.png\"><img width=\"75%\" src=\"./assets/leaderboard.png\"></div>\r\n\r\n## :dizzy:Overview\r\n\r\n**NL2SQL360** is a testbed for fine-grained evaluation of NL2SQL solutions. Our testbed integrates existing NL2SQL benchmarks, a repository of NL2SQL models, and various evaluation metrics, which aims to provide an intuitive and user-friendly platform to enable both standard and customized performance evaluations. Users can utilize **NL2SQL360** to assess different NL2SQL methods against established benchmarks or tailor their evaluations based on specific criteria. This flexibility allows for testing solutions in specific data domains or analyzing performance on different characteristics of SQL queries. \r\n\r\nIn addition, we propose **SuperSQL**, which achieves competitive performance with execution accuracy of **87%** and **62.66%** on the Spider and BIRD test sets, respectively.\r\n\r\n## :tada:News\r\n\r\n[24/9/23] We release NL2SQL360 `1.1.0` version, which supports two new metrics **Reward-based VES** (RVES), **Soft-F1 Score** (F1), from [BIRD-Mini-Dev ](https://github.com/bird-bench/mini_dev) dataset. **Please update your package with** `pip install --upgrade nl2sql360`.\r\n\r\n[24/9/1] We have released our **[Homepage & Leaderboard](https://nl2sql360.github.io)!**\r\n\r\n[24/8/2] We have released CLI usage / Code usage tutorials. **Please [check out](#rocketquick-start)!**\r\n\r\n[24/7/30] We have refactored the code and released the official python package([nl2sql360 \u00b7 PyPI](https://pypi.org/project/nl2sql360)). **Stay tuned for the complete documents!**\r\n\r\n[24/6/30] Our paper [The Dawn of Natural Language to SQL: Are We Fully Ready?](https://arxiv.org/abs/2406.01265) has been accepted by VLDB'24.\r\n\r\n## :balloon:Features\r\n\r\n- **Easy-to-use Evaluation**: Command Line Usage / Python Code Usage.\r\n- **Integrated Metrics**: Execution Accuracy / Exact-Match Accuracy / Valid Efficiency Score / Question Variance Testing.\r\n- **Multi-angle Performance**: Fine-grained performance (JOIN, Sub-query, etc.) / Scenario-based (Business Intelligence, etc.)\r\n\r\n## :wrench:Installation\r\n\r\n```bash\r\npip install nl2sql360\r\n```\r\n\r\n## :rocket:Quick Start\r\n\r\n<details><summary>Prepare Dataset</summary>\r\n\r\nDownload NL2SQL dataset to `DATASET_DIR_PATH`. The directory structure should be like:\r\n```bash\r\nDATASET_DIR_PATH:\r\n\u251c\u2500database\r\n\u2502 \u251c\u2500academic\r\n\u2502 \u2502 \u251c\u2500academic.sqlite\r\n\u2502 \u251c\u2500college\r\n\u2502 \u2502 \u251c\u2500college.sqlite\r\n\u251c\u2500dev.json\r\n\u251c\u2500tables.json\r\n```\r\n\r\n- `database` directory contains multiple subdirectories, which include the corresponding `sqlite` database file.\r\n- `dev.json` is the samples file in JSON format, which at least contains three keys for `NL Question`, `Gold SQL`, `Databae Id`. You can also add the key for `Sample Complexity` for categorizing samples into different difficulty levels.\r\n- `tables.json` contains all database schema, following [Spider Preprocess Procedure](https://github.com/taoyds/spider/tree/master/preprocess). **You can also ignore this file if you do not want to evaluate Exact-Match Accuracy Metic.**\r\n- Note that the name for `database` directory, samples file `dev.json` and tables file `tables.json` can be changed.\r\n\r\n</details>\r\n\r\n<details><summary>Import Dataset into NL2SQL360</summary>\r\n\r\n- CLI Usage:\r\n\r\n - Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/dataset_spider.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/dataset_spider.yaml).\r\n\r\n - Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:\r\n\r\n ```bash\r\n nl2sql360 dataset DATASET_YAML_PATH\r\n ```\r\n\r\n- Code Usage:\r\n\r\n - Create / Modify Python File following [NL2SQL360/examples/py_examples/dataset_import.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/dataset_import.py).\r\n - Run the python file to import dataset.\r\n\r\n</details>\r\n\r\n<details><summary>Evaluation NL2SQL Model</summary>\r\n\r\n- CLI Usage:\r\n\r\n - Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/evaluation.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/evaluation.yaml).\r\n\r\n - Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:\r\n\r\n ```bash\r\n nl2sql360 evaluate DATASET_YAML_PATH\r\n ```\r\n\r\n- Code Usage:\r\n\r\n - Create / Modify Python File following [NL2SQL360/examples/py_examples/evaluation.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/evaluation.py).\r\n - Run the python file to evaluate the model.\r\n\r\n</details>\r\n\r\n<details><summary>Query Multi-angle Performance</summary>\r\n\r\n- CLI Usage:\r\n\r\n - Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/report.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/report.yaml).\r\n\r\n - Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:\r\n\r\n ```bash\r\n nl2sql360 report DATASET_YAML_PATH\r\n ```\r\n\r\n - The generated report will be in `save_path` specified in the YAML file.\r\n\r\n- Code Usage:\r\n - Create / Modify Python File following [NL2SQL360/examples/py_examples/report.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/report.py).\r\n - Run the python file to generate report.\r\n\r\n</details>\r\n\r\n<details><summary>Delete History Cache</summary>\r\n\r\n- CLI Usage:\r\n\r\n - Create / Modify the YAML configuration following [NL2SQL360/examples/cli_examples/delete_history.yaml](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/cli_examples/delete_history.yaml).\r\n\r\n - Save the YAML file to the path `DATASET_YAML_PATH`. Then run the command line:\r\n\r\n ```bash\r\n nl2sql360 delete DATASET_YAML_PATH\r\n ```\r\n\r\n- Code Usage:\r\n\r\n - Create / Modify Python File following [NL2SQL360/examples/py_examples/delete_history.py](https://github.com/HKUSTDial/NL2SQL360/blob/master/examples/py_examples/delete_history.py).\r\n - Run the python file to delete dataset / evaluation cache.\r\n\r\n</details>\r\n\r\n## :dart:Road Map\r\n\r\n:white_check_mark:Release **NL2SQL360** evaluation code.\r\n\r\n:white_check_mark:Release **NL2SQL360** experiments data.\r\n\r\n:white_check_mark:Release **NL2SQL360** Official Python Package.\r\n\r\n## :floppy_disk:Experiment Data\r\n\r\nWe have released all experiment data used in our paper.\r\n\r\n[Download Link](https://drive.google.com/drive/folders/1SDwY30H2r6XNYeS53wcZNocVFm0hVgpz?usp=sharing)\r\n\r\n## :pushpin:Citation\r\n\r\n```\r\n@misc{li2024dawn,\r\n title={The Dawn of Natural Language to SQL: Are We Fully Ready?}, \r\n author={Boyan Li and Yuyu Luo and Chengliang Chai and Guoliang Li and Nan Tang},\r\n year={2024},\r\n eprint={2406.01265},\r\n archivePrefix={arXiv},\r\n primaryClass={id='cs.DB' full_name='Databases' is_active=True alt_name=None in_archive='cs' is_general=False description='Covers database management, datamining, and data processing. Roughly includes material in ACM Subject Classes E.2, E.5, H.0, H.2, and J.1.'}\r\n}\r\n```\r\n",
"bugtrack_url": null,
"license": "MIT License",
"summary": "A Multi-angle NL2SQL Evaluation Framework",
"version": "1.1.0",
"project_urls": {
"Homepage": "https://github.com/HKUSTDial/NL2SQL360"
},
"split_keywords": [
"nl2sql",
" text-to-sql",
" t2s",
" sql",
" database",
" nlidb"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "1c1671089d57c01147d7ee7da8f60c9ad67f86e622b67a5965fdfce6df2cde38",
"md5": "b572d116b9eaed4d61160dcca905cc90",
"sha256": "63f499070de32acdcc936fcf243c8ef21584c139a13ee99254bb91271ad6a827"
},
"downloads": -1,
"filename": "nl2sql360-1.1.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "b572d116b9eaed4d61160dcca905cc90",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8.0",
"size": 65086,
"upload_time": "2024-09-23T10:31:22",
"upload_time_iso_8601": "2024-09-23T10:31:22.220818Z",
"url": "https://files.pythonhosted.org/packages/1c/16/71089d57c01147d7ee7da8f60c9ad67f86e622b67a5965fdfce6df2cde38/nl2sql360-1.1.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "1129f887dc506ad660364cc87dfc54362ea31c02a617287508ccfa36ace911c3",
"md5": "3336f0992242a9902f94ee39ce920f3d",
"sha256": "9106f2c5fe1387e3bfa65ad49b635b8ad84c627c6b94813278b050757862fb73"
},
"downloads": -1,
"filename": "nl2sql360-1.1.0.tar.gz",
"has_sig": false,
"md5_digest": "3336f0992242a9902f94ee39ce920f3d",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8.0",
"size": 3021747,
"upload_time": "2024-09-23T10:31:43",
"upload_time_iso_8601": "2024-09-23T10:31:43.377593Z",
"url": "https://files.pythonhosted.org/packages/11/29/f887dc506ad660364cc87dfc54362ea31c02a617287508ccfa36ace911c3/nl2sql360-1.1.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-09-23 10:31:43",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "HKUSTDial",
"github_project": "NL2SQL360",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"requirements": [
{
"name": "psycopg2-binary",
"specs": []
},
{
"name": "pymysql",
"specs": []
},
{
"name": "func-timeout",
"specs": []
},
{
"name": "pydantic",
"specs": []
},
{
"name": "sqlglot",
"specs": []
},
{
"name": "tqdm",
"specs": []
},
{
"name": "loguru",
"specs": []
},
{
"name": "sqlalchemy",
"specs": []
},
{
"name": "pandas",
"specs": []
}
],
"lcname": "nl2sql360"
}
JSON
