202 lines
8.0 KiB
Markdown
202 lines
8.0 KiB
Markdown
---
|
||
frameworks:
|
||
- Pytorch
|
||
license: Apache License 2.0
|
||
tasks:
|
||
- text-generation
|
||
|
||
#model-type:
|
||
##如 gpt、phi、llama、chatglm、baichuan 等
|
||
#- gpt
|
||
|
||
#domain:
|
||
##如 nlp、cv、audio、multi-modal
|
||
#- nlp
|
||
|
||
#language:
|
||
##语言代码列表 https://help.aliyun.com/document_detail/215387.html?spm=a2c4g.11186623.0.0.9f8d7467kni6Aa
|
||
#- cn
|
||
|
||
#metrics:
|
||
##如 CIDEr、Blue、ROUGE 等
|
||
#- CIDEr
|
||
|
||
#tags:
|
||
##各种自定义，包括 pretrained、fine-tuned、instruction-tuned、RL-tuned 等训练方法和其他
|
||
#- pretrained
|
||
|
||
#tools:
|
||
##如 vllm、fastchat、llamacpp、AdaSeq 等
|
||
#- vllm
|
||
tags:
|
||
- NL2SQL
|
||
- SQL
|
||
- Text-to-SQL
|
||
---
|
||
### Important Links
|
||
|
||
🤖[Github](https://github.com/XGenerationLab/XiYanSQL-QwenCoder) |
|
||
💻[HuggingFace](https://huggingface.co/collections/XGenerationLab/xiyansql-models-67c9844307b49f87436808fc) |
|
||
📖[XiYan-SQL](https://github.com/XGenerationLab/XiYan-SQL) |
|
||
🌕[析言GBI](https://bailian.console.aliyun.com/xiyan) |
|
||
🤗[Modelscope Space](https://www.modelscope.cn/studios/XGenerationLab/XiYanSQL-QwenCoder-32B)
|
||
|
||
|
||
## Introduction
|
||
We are excited to open source the XiYanSQL-QwenCoder series model, dedicated to advancing the development of LLMs in the text-to-SQL domain. As of now, XiYanSQL-QwenCoder covers four mainstream model sizes: 3B, 7B, 14B, and 32B parameters, to meet the needs of different developers.
|
||
- The XiYanSQL-QwenCoder model demonstrates strong performance in SQL generation, with the XiYanSQL-QwenCoder-32B achieving a 69.03% EX score on the BIRD TEST set, setting a new SOTA with a single fine-tuned model. Other models in the series also maintain a leading position at their respective sizes.
|
||
- The XiYanSQL-QwenCoder model supports multiple SQL dialects, such as SQLite, PostgreSQL, and MySQL.
|
||
- The XiYanSQL-QwenCoder model can be used directly for text-to-SQL tasks or serve as a better starting point for fine-tuning SQL models.
|
||
|
||
|
||
## Model Downloads
|
||
|
||
|
||
| **Model** | **Download Latest** |
|
||
|-----------|------------------|
|
||
|XiYanSQL-QwenCoder-3B |💻[HuggingFace](https://huggingface.co/XGenerationLab/XiYanSQL-QwenCoder-3B-2502) 🤗[Modelscope](https://www.modelscope.cn/models/XGenerationLab/XiYanSQL-QwenCoder-3B-2502)|
|
||
|XiYanSQL-QwenCoder-7B |💻[HuggingFace](https://huggingface.co/XGenerationLab/XiYanSQL-QwenCoder-7B-2502) 🤗[Modelscope](https://www.modelscope.cn/models/XGenerationLab/XiYanSQL-QwenCoder-7B-2502)|
|
||
|XiYanSQL-QwenCoder-14B |💻[HuggingFace](https://huggingface.co/XGenerationLab/XiYanSQL-QwenCoder-14B-2502) 🤗[Modelscope](https://www.modelscope.cn/models/XGenerationLab/XiYanSQL-QwenCoder-14B-2502)|
|
||
|XiYanSQL-QwenCoder-32B |💻[HuggingFace](https://huggingface.co/XGenerationLab/XiYanSQL-QwenCoder-32B-2412) 🤗[Modelscope](https://www.modelscope.cn/models/XGenerationLab/XiYanSQL-QwenCoder-32B-2412)|
|
||
|
||
|
||
## Performance
|
||
The XiYanSQL-QwenCoder models, as multi-dialect SQL base models, demonstrating robust SQL generation capabilities. The following presents the evaluation results at the time of release. We conducted a comprehensive evaluation of the model's performance under two schema formats, M-Schema, and original DDL, using the BIRD and Spider benchmarks in the Text-to-SQL domain.
|
||
|
||
| Model name|BIRD Dev@M-Schema |BIRD Dev@DDL|Spider Test@M-Schema|Spider Test@DDL|
|
||
|-----------|:------------------:|:---------------:|:-------------------:|:---------------:|
|
||
|Codellama-34b | 33.05% | - | 67.74% | - |
|
||
|Deepseek-coder-33b | 47.52% | 44.72% | 72.39% | - |
|
||
|TableGPT2 | 46.35% | 47.07% | 74.76% | 77.28% |
|
||
|Codestral 22b | 50.52% | 47.00% | 78.45% | 75.47% |
|
||
|GLM-4-plus | 54.37% | - | 79.40% | - |
|
||
|Claude35_sonnet-1022 | 53.32% | 50.46% | 76.27% | 73.04% |
|
||
|Deepseek(v2.5-1210) | 55.74% | 55.61% | 82.08% | 80.57% |
|
||
|Gemini-1.5-pro | 61.34% | 57.89% | 85.11% | 84.00% |
|
||
|GPT-4o-0806 | 58.47% | 54.82% | 82.89% | 78.45% |
|
||
|XiYanSQL-QwenCoder-3B | 54.11% | 53.19% | 82.69% | 78.85% |
|
||
|XiYanSQL-QwenCoder-7B | 59.78% | 56.58% | 84.86% | 80.31% |
|
||
|XiYanSQL-QwenCoder-14B | 63.10% | 60.37% | 85.76% | 82.79% |
|
||
|XiYanSQL-QwenCoder-32B | 67.01% | 63.04% | 88.39% | 85.46% |
|
||
|
||
|
||
## Requirements
|
||
|
||
transformers >= 4.37.0
|
||
|
||
## Quickstart
|
||
|
||
Here is a simple code snippet for quickly using **XiYanSQL-QwenCoder** model. We provide a Chinese version of the prompt, and you just need to replace the placeholders for "question," "db_schema," and "evidence" to get started. We recommend using our [M-Schema](https://github.com/XGenerationLab/M-Schema) format for the schema; other formats such as DDL are also acceptable, but they may affect performance.
|
||
Currently, we mainly support mainstream dialects like SQLite, PostgreSQL, and MySQL.
|
||
|
||
```python
|
||
|
||
nl2sqlite_template_cn = """你是一名{dialect}专家，现在需要阅读并理解下面的【数据库schema】描述，以及可能用到的【参考信息】，并运用{dialect}知识生成sql语句回答【用户问题】。
|
||
【用户问题】
|
||
{question}
|
||
|
||
【数据库schema】
|
||
{db_schema}
|
||
|
||
【参考信息】
|
||
{evidence}
|
||
|
||
【用户问题】
|
||
{question}
|
||
|
||
```sql"""
|
||
|
||
import torch
|
||
from transformers import AutoModelForCausalLM, AutoTokenizer
|
||
|
||
model_name = "XGenerationLab/XiYanSQL-QwenCoder-3B-2502"
|
||
model = AutoModelForCausalLM.from_pretrained(
|
||
model_name,
|
||
torch_dtype=torch.bfloat16,
|
||
device_map="auto"
|
||
)
|
||
|
||
tokenizer = AutoTokenizer.from_pretrained(model_name)
|
||
|
||
## dialects -> ['SQLite', 'PostgreSQL', 'MySQL']
|
||
prompt = nl2sqlite_template_cn.format(dialect="", db_schema="", question="", evidence="")
|
||
message = [{'role': 'user', 'content': prompt}]
|
||
|
||
text = tokenizer.apply_chat_template(
|
||
message,
|
||
tokenize=False,
|
||
add_generation_prompt=True
|
||
)
|
||
model_inputs = tokenizer([text], return_tensors="pt").to(model.device)
|
||
|
||
generated_ids = model.generate(
|
||
**model_inputs,
|
||
pad_token_id=tokenizer.pad_token_id,
|
||
eos_token_id=tokenizer.eos_token_id,
|
||
max_new_tokens=1024,
|
||
temperature=0.1,
|
||
top_p=0.8,
|
||
do_sample=True,
|
||
)
|
||
generated_ids = [
|
||
output_ids[len(input_ids):] for input_ids, output_ids in zip(model_inputs.input_ids, generated_ids)
|
||
]
|
||
response = tokenizer.batch_decode(generated_ids, skip_special_tokens=True)[0]
|
||
|
||
```
|
||
|
||
### Inference with vLLM
|
||
```python
|
||
from vllm import LLM, SamplingParams
|
||
from transformers import AutoTokenizer
|
||
model_path = "XGenerationLab/XiYanSQL-QwenCoder-3B-2502"
|
||
llm = LLM(model=model_path, tensor_parallel_size=8)
|
||
tokenizer = AutoTokenizer.from_pretrained(model_path)
|
||
sampling_params = SamplingParams(
|
||
n=1,
|
||
temperature=0.1,
|
||
max_tokens=1024
|
||
)
|
||
|
||
## dialects -> ['SQLite', 'PostgreSQL', 'MySQL']
|
||
prompt = nl2sqlite_template_cn.format(dialect="", db_schema="", question="", evidence="")
|
||
message = [{'role': 'user', 'content': prompt}]
|
||
text = tokenizer.apply_chat_template(
|
||
message,
|
||
tokenize=False,
|
||
add_generation_prompt=True
|
||
)
|
||
outputs = llm.generate([text], sampling_params=sampling_params)
|
||
response = outputs[0].outputs[0].text
|
||
```
|
||
|
||
|
||
## Contact us:
|
||
|
||
If you are interested in our research or products, please feel free to contact us.
|
||
|
||
#### Contact Information:
|
||
|
||
Yifu Liu, zhencang.lyf@alibaba-inc.com
|
||
|
||
#### Join Our DingTalk Group
|
||
|
||
<a href="https://github.com/XGenerationLab/XiYan-SQL/blob/main/xiyansql_dingding.png">Ding Group钉钉群</a>
|
||
|
||
|
||
|
||
## Acknowledgments
|
||
If you find our work useful, please give us a citation or a like, so we can make a greater contribution to the open-source community!
|
||
```bibtex
|
||
@article{XiYanSQL,
|
||
author={Liu, Yifu and Zhu, Yin and Gao, Yingqi and Luo, Zhiling and Li, Xiaoxia and Shi, Xiaorong and Hong, Yuntao and Gao, Jinyang and Li, Yu and Ding, Bolin and Zhou, Jingren},
|
||
journal={IEEE Transactions on Knowledge and Data Engineering},
|
||
title={XiYan-SQL: A Novel Multi-Generator Framework for Text-to-SQL},
|
||
year={2026},
|
||
volume={},
|
||
number={},
|
||
pages={1-14},
|
||
doi={10.1109/TKDE.2026.3657851}
|
||
}
|
||
```
|