|
--- |
|
base_model: defog/sqlcoder-7b |
|
inference: false |
|
language: |
|
- en |
|
license: cc-by-sa-4.0 |
|
model_creator: Defog.ai |
|
model_name: SQLCoder 7B |
|
model_type: mistral |
|
pipeline_tag: text-generation |
|
prompt_template: "## Task\nGenerate a SQL query to answer the following question:\n\ |
|
`{prompt}`\n\n### Database Schema\nThis query will run on a database whose schema\ |
|
\ is represented in this string:\nCREATE TABLE products (\n product_id INTEGER\ |
|
\ PRIMARY KEY, -- Unique ID for each product\n name VARCHAR(50), -- Name of the\ |
|
\ product\n price DECIMAL(10,2), -- Price of each unit of the product\n quantity\ |
|
\ INTEGER -- Current quantity in stock\n);\n\nCREATE TABLE sales (\n sale_id INTEGER\ |
|
\ PRIMARY KEY, -- Unique ID for each sale\n product_id INTEGER, -- ID of product\ |
|
\ sold\n customer_id INTEGER, -- ID of customer who made purchase\n salesperson_id\ |
|
\ INTEGER, -- ID of salesperson who made the sale\n sale_date DATE, -- Date the\ |
|
\ sale occurred\n quantity INTEGER -- Quantity of product sold\n);\n\n-- sales.product_id\ |
|
\ can be joined with products.product_id\n\n### SQL\nGiven the database schema,\ |
|
\ here is the SQL query that answers `{prompt}`:\n```sql\n" |
|
quantized_by: TheBloke |
|
tags: |
|
- code |
|
--- |
|
<!-- markdownlint-disable MD041 --> |
|
|
|
<!-- header start --> |
|
<!-- 200823 --> |
|
<div style="width: auto; margin-left: auto; margin-right: auto"> |
|
<img src="https://i.imgur.com/EBdldam.jpg" alt="TheBlokeAI" style="width: 100%; min-width: 400px; display: block; margin: auto;"> |
|
</div> |
|
<div style="display: flex; justify-content: space-between; width: 100%;"> |
|
<div style="display: flex; flex-direction: column; align-items: flex-start;"> |
|
<p style="margin-top: 0.5em; margin-bottom: 0em;"><a href="https://discord.gg/theblokeai">Chat & support: TheBloke's Discord server</a></p> |
|
</div> |
|
<div style="display: flex; flex-direction: column; align-items: flex-end;"> |
|
<p style="margin-top: 0.5em; margin-bottom: 0em;"><a href="https://www.patreon.com/TheBlokeAI">Want to contribute? TheBloke's Patreon page</a></p> |
|
</div> |
|
</div> |
|
<div style="text-align:center; margin-top: 0em; margin-bottom: 0em"><p style="margin-top: 0.25em; margin-bottom: 0em;">TheBloke's LLM work is generously supported by a grant from <a href="https://a16z.com">andreessen horowitz (a16z)</a></p></div> |
|
<hr style="margin-top: 1.0em; margin-bottom: 1.0em;"> |
|
<!-- header end --> |
|
|
|
# SQLCoder 7B - GPTQ |
|
- Model creator: [Defog.ai](https://huggingface.co/defog) |
|
- Original model: [SQLCoder 7B](https://huggingface.co/defog/sqlcoder-7b) |
|
|
|
<!-- description start --> |
|
# Description |
|
|
|
This repo contains GPTQ model files for [Defog.ai's SQLCoder 7B](https://huggingface.co/defog/sqlcoder-7b). |
|
|
|
Multiple GPTQ parameter permutations are provided; see Provided Files below for details of the options provided, their parameters, and the software used to create them. |
|
|
|
These files were quantised using hardware kindly provided by [Massed Compute](https://massedcompute.com/). |
|
|
|
<!-- description end --> |
|
<!-- repositories-available start --> |
|
## Repositories available |
|
|
|
* [AWQ model(s) for GPU inference.](https://huggingface.co/TheBloke/sqlcoder-7B-AWQ) |
|
* [GPTQ models for GPU inference, with multiple quantisation parameter options.](https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ) |
|
* [2, 3, 4, 5, 6 and 8-bit GGUF models for CPU+GPU inference](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF) |
|
* [Defog.ai's original unquantised fp16 model in pytorch format, for GPU inference and for further conversions](https://huggingface.co/defog/sqlcoder-7b) |
|
<!-- repositories-available end --> |
|
|
|
<!-- prompt-template start --> |
|
## Prompt template: Sqlcoder |
|
|
|
``` |
|
## Task |
|
Generate a SQL query to answer the following question: |
|
`{prompt}` |
|
|
|
### Database Schema |
|
This query will run on a database whose schema is represented in this string: |
|
CREATE TABLE products ( |
|
product_id INTEGER PRIMARY KEY, -- Unique ID for each product |
|
name VARCHAR(50), -- Name of the product |
|
price DECIMAL(10,2), -- Price of each unit of the product |
|
quantity INTEGER -- Current quantity in stock |
|
); |
|
|
|
CREATE TABLE sales ( |
|
sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale |
|
product_id INTEGER, -- ID of product sold |
|
customer_id INTEGER, -- ID of customer who made purchase |
|
salesperson_id INTEGER, -- ID of salesperson who made the sale |
|
sale_date DATE, -- Date the sale occurred |
|
quantity INTEGER -- Quantity of product sold |
|
); |
|
|
|
-- sales.product_id can be joined with products.product_id |
|
|
|
### SQL |
|
Given the database schema, here is the SQL query that answers `{prompt}`: |
|
```sql |
|
|
|
``` |
|
|
|
<!-- prompt-template end --> |
|
|
|
|
|
|
|
<!-- README_GPTQ.md-compatible clients start --> |
|
## Known compatible clients / servers |
|
|
|
These GPTQ models are known to work in the following inference servers/webuis. |
|
|
|
- [text-generation-webui](https://github.com/oobabooga/text-generation-webui) |
|
- [KoboldAI United](https://github.com/henk717/koboldai) |
|
- [LoLLMS Web UI](https://github.com/ParisNeo/lollms-webui) |
|
- [Hugging Face Text Generation Inference (TGI)](https://github.com/huggingface/text-generation-inference) |
|
|
|
This may not be a complete list; if you know of others, please let me know! |
|
<!-- README_GPTQ.md-compatible clients end --> |
|
|
|
<!-- README_GPTQ.md-provided-files start --> |
|
## Provided files, and GPTQ parameters |
|
|
|
Multiple quantisation parameters are provided, to allow you to choose the best one for your hardware and requirements. |
|
|
|
Each separate quant is in a different branch. See below for instructions on fetching from different branches. |
|
|
|
Most GPTQ files are made with AutoGPTQ. Mistral models are currently made with Transformers. |
|
|
|
<details> |
|
<summary>Explanation of GPTQ parameters</summary> |
|
|
|
- Bits: The bit size of the quantised model. |
|
- GS: GPTQ group size. Higher numbers use less VRAM, but have lower quantisation accuracy. "None" is the lowest possible value. |
|
- Act Order: True or False. Also known as `desc_act`. True results in better quantisation accuracy. Some GPTQ clients have had issues with models that use Act Order plus Group Size, but this is generally resolved now. |
|
- Damp %: A GPTQ parameter that affects how samples are processed for quantisation. 0.01 is default, but 0.1 results in slightly better accuracy. |
|
- GPTQ dataset: The calibration dataset used during quantisation. Using a dataset more appropriate to the model's training can improve quantisation accuracy. Note that the GPTQ calibration dataset is not the same as the dataset used to train the model - please refer to the original model repo for details of the training dataset(s). |
|
- Sequence Length: The length of the dataset sequences used for quantisation. Ideally this is the same as the model sequence length. For some very long sequence models (16+K), a lower sequence length may have to be used. Note that a lower sequence length does not limit the sequence length of the quantised model. It only impacts the quantisation accuracy on longer inference sequences. |
|
- ExLlama Compatibility: Whether this file can be loaded with ExLlama, which currently only supports Llama and Mistral models in 4-bit. |
|
|
|
</details> |
|
|
|
| Branch | Bits | GS | Act Order | Damp % | GPTQ Dataset | Seq Len | Size | ExLlama | Desc | |
|
| ------ | ---- | -- | --------- | ------ | ------------ | ------- | ---- | ------- | ---- | |
|
| [main](https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ/tree/main) | 4 | 128 | Yes | 0.1 | [code](https://huggingface.co/datasets/nickrosh/Evol-Instruct-Code-80k-v1/viewer/) | 4096 | 4.16 GB | Yes | 4-bit, with Act Order and group size 128g. Uses even less VRAM than 64g, but with slightly lower accuracy. | |
|
| [gptq-4bit-32g-actorder_True](https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ/tree/gptq-4bit-32g-actorder_True) | 4 | 32 | Yes | 0.1 | [code](https://huggingface.co/datasets/nickrosh/Evol-Instruct-Code-80k-v1/viewer/) | 4096 | 4.57 GB | Yes | 4-bit, with Act Order and group size 32g. Gives highest possible inference quality, with maximum VRAM usage. | |
|
| [gptq-8bit--1g-actorder_True](https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ/tree/gptq-8bit--1g-actorder_True) | 8 | None | Yes | 0.1 | [code](https://huggingface.co/datasets/nickrosh/Evol-Instruct-Code-80k-v1/viewer/) | 4096 | 7.52 GB | No | 8-bit, with Act Order. No group size, to lower VRAM requirements. | |
|
| [gptq-8bit-128g-actorder_True](https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ/tree/gptq-8bit-128g-actorder_True) | 8 | 128 | Yes | 0.1 | [code](https://huggingface.co/datasets/nickrosh/Evol-Instruct-Code-80k-v1/viewer/) | 4096 | 7.68 GB | No | 8-bit, with group size 128g for higher inference quality and with Act Order for even higher accuracy. | |
|
| [gptq-8bit-32g-actorder_True](https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ/tree/gptq-8bit-32g-actorder_True) | 8 | 32 | Yes | 0.1 | [code](https://huggingface.co/datasets/nickrosh/Evol-Instruct-Code-80k-v1/viewer/) | 4096 | 8.17 GB | No | 8-bit, with group size 32g and Act Order for maximum inference quality. | |
|
| [gptq-4bit-64g-actorder_True](https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ/tree/gptq-4bit-64g-actorder_True) | 4 | 64 | Yes | 0.1 | [code](https://huggingface.co/datasets/nickrosh/Evol-Instruct-Code-80k-v1/viewer/) | 4096 | 4.29 GB | Yes | 4-bit, with Act Order and group size 64g. Uses less VRAM than 32g, but with slightly lower accuracy. | |
|
|
|
<!-- README_GPTQ.md-provided-files end --> |
|
|
|
<!-- README_GPTQ.md-download-from-branches start --> |
|
## How to download, including from branches |
|
|
|
### In text-generation-webui |
|
|
|
To download from the `main` branch, enter `TheBloke/sqlcoder-7B-GPTQ` in the "Download model" box. |
|
|
|
To download from another branch, add `:branchname` to the end of the download name, eg `TheBloke/sqlcoder-7B-GPTQ:gptq-4bit-32g-actorder_True` |
|
|
|
### From the command line |
|
|
|
I recommend using the `huggingface-hub` Python library: |
|
|
|
```shell |
|
pip3 install huggingface-hub |
|
``` |
|
|
|
To download the `main` branch to a folder called `sqlcoder-7B-GPTQ`: |
|
|
|
```shell |
|
mkdir sqlcoder-7B-GPTQ |
|
huggingface-cli download TheBloke/sqlcoder-7B-GPTQ --local-dir sqlcoder-7B-GPTQ --local-dir-use-symlinks False |
|
``` |
|
|
|
To download from a different branch, add the `--revision` parameter: |
|
|
|
```shell |
|
mkdir sqlcoder-7B-GPTQ |
|
huggingface-cli download TheBloke/sqlcoder-7B-GPTQ --revision gptq-4bit-32g-actorder_True --local-dir sqlcoder-7B-GPTQ --local-dir-use-symlinks False |
|
``` |
|
|
|
<details> |
|
<summary>More advanced huggingface-cli download usage</summary> |
|
|
|
If you remove the `--local-dir-use-symlinks False` parameter, the files will instead be stored in the central Hugging Face cache directory (default location on Linux is: `~/.cache/huggingface`), and symlinks will be added to the specified `--local-dir`, pointing to their real location in the cache. This allows for interrupted downloads to be resumed, and allows you to quickly clone the repo to multiple places on disk without triggering a download again. The downside, and the reason why I don't list that as the default option, is that the files are then hidden away in a cache folder and it's harder to know where your disk space is being used, and to clear it up if/when you want to remove a download model. |
|
|
|
The cache location can be changed with the `HF_HOME` environment variable, and/or the `--cache-dir` parameter to `huggingface-cli`. |
|
|
|
For more documentation on downloading with `huggingface-cli`, please see: [HF -> Hub Python Library -> Download files -> Download from the CLI](https://huggingface.co/docs/huggingface_hub/guides/download#download-from-the-cli). |
|
|
|
To accelerate downloads on fast connections (1Gbit/s or higher), install `hf_transfer`: |
|
|
|
```shell |
|
pip3 install hf_transfer |
|
``` |
|
|
|
And set environment variable `HF_HUB_ENABLE_HF_TRANSFER` to `1`: |
|
|
|
```shell |
|
mkdir sqlcoder-7B-GPTQ |
|
HF_HUB_ENABLE_HF_TRANSFER=1 huggingface-cli download TheBloke/sqlcoder-7B-GPTQ --local-dir sqlcoder-7B-GPTQ --local-dir-use-symlinks False |
|
``` |
|
|
|
Windows Command Line users: You can set the environment variable by running `set HF_HUB_ENABLE_HF_TRANSFER=1` before the download command. |
|
</details> |
|
|
|
### With `git` (**not** recommended) |
|
|
|
To clone a specific branch with `git`, use a command like this: |
|
|
|
```shell |
|
git clone --single-branch --branch gptq-4bit-32g-actorder_True https://huggingface.co/TheBloke/sqlcoder-7B-GPTQ |
|
``` |
|
|
|
Note that using Git with HF repos is strongly discouraged. It will be much slower than using `huggingface-hub`, and will use twice as much disk space as it has to store the model files twice (it stores every byte both in the intended target folder, and again in the `.git` folder as a blob.) |
|
|
|
<!-- README_GPTQ.md-download-from-branches end --> |
|
<!-- README_GPTQ.md-text-generation-webui start --> |
|
## How to easily download and use this model in [text-generation-webui](https://github.com/oobabooga/text-generation-webui) |
|
|
|
Please make sure you're using the latest version of [text-generation-webui](https://github.com/oobabooga/text-generation-webui). |
|
|
|
It is strongly recommended to use the text-generation-webui one-click-installers unless you're sure you know how to make a manual install. |
|
|
|
1. Click the **Model tab**. |
|
2. Under **Download custom model or LoRA**, enter `TheBloke/sqlcoder-7B-GPTQ`. |
|
|
|
- To download from a specific branch, enter for example `TheBloke/sqlcoder-7B-GPTQ:gptq-4bit-32g-actorder_True` |
|
- see Provided Files above for the list of branches for each option. |
|
|
|
3. Click **Download**. |
|
4. The model will start downloading. Once it's finished it will say "Done". |
|
5. In the top left, click the refresh icon next to **Model**. |
|
6. In the **Model** dropdown, choose the model you just downloaded: `sqlcoder-7B-GPTQ` |
|
7. The model will automatically load, and is now ready for use! |
|
8. If you want any custom settings, set them and then click **Save settings for this model** followed by **Reload the Model** in the top right. |
|
|
|
- Note that you do not need to and should not set manual GPTQ parameters any more. These are set automatically from the file `quantize_config.json`. |
|
|
|
9. Once you're ready, click the **Text Generation** tab and enter a prompt to get started! |
|
|
|
<!-- README_GPTQ.md-text-generation-webui end --> |
|
|
|
<!-- README_GPTQ.md-use-from-tgi start --> |
|
## Serving this model from Text Generation Inference (TGI) |
|
|
|
It's recommended to use TGI version 1.1.0 or later. The official Docker container is: `ghcr.io/huggingface/text-generation-inference:1.1.0` |
|
|
|
Example Docker parameters: |
|
|
|
```shell |
|
--model-id TheBloke/sqlcoder-7B-GPTQ --port 3000 --quantize gptq --max-input-length 3696 --max-total-tokens 4096 --max-batch-prefill-tokens 4096 |
|
``` |
|
|
|
Example Python code for interfacing with TGI (requires huggingface-hub 0.17.0 or later): |
|
|
|
```shell |
|
pip3 install huggingface-hub |
|
``` |
|
|
|
```python |
|
from huggingface_hub import InferenceClient |
|
|
|
endpoint_url = "https://your-endpoint-url-here" |
|
|
|
prompt = "Tell me about AI" |
|
prompt_template=f'''## Task |
|
Generate a SQL query to answer the following question: |
|
`{prompt}` |
|
|
|
### Database Schema |
|
This query will run on a database whose schema is represented in this string: |
|
CREATE TABLE products ( |
|
product_id INTEGER PRIMARY KEY, -- Unique ID for each product |
|
name VARCHAR(50), -- Name of the product |
|
price DECIMAL(10,2), -- Price of each unit of the product |
|
quantity INTEGER -- Current quantity in stock |
|
); |
|
|
|
CREATE TABLE sales ( |
|
sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale |
|
product_id INTEGER, -- ID of product sold |
|
customer_id INTEGER, -- ID of customer who made purchase |
|
salesperson_id INTEGER, -- ID of salesperson who made the sale |
|
sale_date DATE, -- Date the sale occurred |
|
quantity INTEGER -- Quantity of product sold |
|
); |
|
|
|
-- sales.product_id can be joined with products.product_id |
|
|
|
### SQL |
|
Given the database schema, here is the SQL query that answers `{prompt}`: |
|
```sql |
|
''' |
|
|
|
client = InferenceClient(endpoint_url) |
|
response = client.text_generation(prompt, |
|
max_new_tokens=128, |
|
do_sample=True, |
|
temperature=0.7, |
|
top_p=0.95, |
|
top_k=40, |
|
repetition_penalty=1.1) |
|
|
|
print(f"Model output: {response}") |
|
``` |
|
<!-- README_GPTQ.md-use-from-tgi end --> |
|
<!-- README_GPTQ.md-use-from-python start --> |
|
## Python code example: inference from this GPTQ model |
|
|
|
### Install the necessary packages |
|
|
|
Requires: Transformers 4.33.0 or later, Optimum 1.12.0 or later, and AutoGPTQ 0.4.2 or later. |
|
|
|
```shell |
|
pip3 install --upgrade transformers optimum |
|
# If using PyTorch 2.1 + CUDA 12.x: |
|
pip3 install --upgrade auto-gptq |
|
# or, if using PyTorch 2.1 + CUDA 11.x: |
|
pip3 install --upgrade auto-gptq --extra-index-url https://huggingface.github.io/autogptq-index/whl/cu118/ |
|
``` |
|
|
|
If you are using PyTorch 2.0, you will need to install AutoGPTQ from source. Likewise if you have problems with the pre-built wheels, you should try building from source: |
|
|
|
```shell |
|
pip3 uninstall -y auto-gptq |
|
git clone https://github.com/PanQiWei/AutoGPTQ |
|
cd AutoGPTQ |
|
git checkout v0.5.1 |
|
pip3 install . |
|
``` |
|
|
|
### Example Python code |
|
|
|
```python |
|
from transformers import AutoModelForCausalLM, AutoTokenizer, pipeline |
|
|
|
model_name_or_path = "TheBloke/sqlcoder-7B-GPTQ" |
|
# To use a different branch, change revision |
|
# For example: revision="gptq-4bit-32g-actorder_True" |
|
model = AutoModelForCausalLM.from_pretrained(model_name_or_path, |
|
device_map="auto", |
|
trust_remote_code=False, |
|
revision="main") |
|
|
|
tokenizer = AutoTokenizer.from_pretrained(model_name_or_path, use_fast=True) |
|
|
|
prompt = "Tell me about AI" |
|
prompt_template=f'''## Task |
|
Generate a SQL query to answer the following question: |
|
`{prompt}` |
|
|
|
### Database Schema |
|
This query will run on a database whose schema is represented in this string: |
|
CREATE TABLE products ( |
|
product_id INTEGER PRIMARY KEY, -- Unique ID for each product |
|
name VARCHAR(50), -- Name of the product |
|
price DECIMAL(10,2), -- Price of each unit of the product |
|
quantity INTEGER -- Current quantity in stock |
|
); |
|
|
|
CREATE TABLE sales ( |
|
sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale |
|
product_id INTEGER, -- ID of product sold |
|
customer_id INTEGER, -- ID of customer who made purchase |
|
salesperson_id INTEGER, -- ID of salesperson who made the sale |
|
sale_date DATE, -- Date the sale occurred |
|
quantity INTEGER -- Quantity of product sold |
|
); |
|
|
|
-- sales.product_id can be joined with products.product_id |
|
|
|
### SQL |
|
Given the database schema, here is the SQL query that answers `{prompt}`: |
|
```sql |
|
''' |
|
|
|
print("\n\n*** Generate:") |
|
|
|
input_ids = tokenizer(prompt_template, return_tensors='pt').input_ids.cuda() |
|
output = model.generate(inputs=input_ids, temperature=0.7, do_sample=True, top_p=0.95, top_k=40, max_new_tokens=512) |
|
print(tokenizer.decode(output[0])) |
|
|
|
# Inference can also be done using transformers' pipeline |
|
|
|
print("*** Pipeline:") |
|
pipe = pipeline( |
|
"text-generation", |
|
model=model, |
|
tokenizer=tokenizer, |
|
max_new_tokens=512, |
|
do_sample=True, |
|
temperature=0.7, |
|
top_p=0.95, |
|
top_k=40, |
|
repetition_penalty=1.1 |
|
) |
|
|
|
print(pipe(prompt_template)[0]['generated_text']) |
|
``` |
|
<!-- README_GPTQ.md-use-from-python end --> |
|
|
|
<!-- README_GPTQ.md-compatibility start --> |
|
## Compatibility |
|
|
|
The files provided are tested to work with Transformers. For non-Mistral models, AutoGPTQ can also be used directly. |
|
|
|
[ExLlama](https://github.com/turboderp/exllama) is compatible with Llama and Mistral models in 4-bit. Please see the Provided Files table above for per-file compatibility. |
|
|
|
For a list of clients/servers, please see "Known compatible clients / servers", above. |
|
<!-- README_GPTQ.md-compatibility end --> |
|
|
|
<!-- footer start --> |
|
<!-- 200823 --> |
|
## Discord |
|
|
|
For further support, and discussions on these models and AI in general, join us at: |
|
|
|
[TheBloke AI's Discord server](https://discord.gg/theblokeai) |
|
|
|
## Thanks, and how to contribute |
|
|
|
Thanks to the [chirper.ai](https://chirper.ai) team! |
|
|
|
Thanks to Clay from [gpus.llm-utils.org](llm-utils)! |
|
|
|
I've had a lot of people ask if they can contribute. I enjoy providing models and helping people, and would love to be able to spend even more time doing it, as well as expanding into new projects like fine tuning/training. |
|
|
|
If you're able and willing to contribute it will be most gratefully received and will help me to keep providing more models, and to start work on new AI projects. |
|
|
|
Donaters will get priority support on any and all AI/LLM/model questions and requests, access to a private Discord room, plus other benefits. |
|
|
|
* Patreon: https://patreon.com/TheBlokeAI |
|
* Ko-Fi: https://ko-fi.com/TheBlokeAI |
|
|
|
**Special thanks to**: Aemon Algiz. |
|
|
|
**Patreon special mentions**: Brandon Frisco, LangChain4j, Spiking Neurons AB, transmissions 11, Joseph William Delisle, Nitin Borwankar, Willem Michiel, Michael Dempsey, vamX, Jeffrey Morgan, zynix, jjj, Omer Bin Jawed, Sean Connelly, jinyuan sun, Jeromy Smith, Shadi, Pawan Osman, Chadd, Elijah Stavena, Illia Dulskyi, Sebastain Graf, Stephen Murray, terasurfer, Edmond Seymore, Celu Ramasamy, Mandus, Alex, biorpg, Ajan Kanaga, Clay Pascal, Raven Klaugh, 阿明, K, ya boyyy, usrbinkat, Alicia Loh, John Villwock, ReadyPlayerEmma, Chris Smitley, Cap'n Zoog, fincy, GodLy, S_X, sidney chen, Cory Kujawski, OG, Mano Prime, AzureBlack, Pieter, Kalila, Spencer Kim, Tom X Nguyen, Stanislav Ovsiannikov, Michael Levine, Andrey, Trailburnt, Vadim, Enrico Ros, Talal Aujan, Brandon Phillips, Jack West, Eugene Pentland, Michael Davis, Will Dee, webtim, Jonathan Leane, Alps Aficionado, Rooh Singh, Tiffany J. Kim, theTransient, Luke @flexchar, Elle, Caitlyn Gatomon, Ari Malik, subjectnull, Johann-Peter Hartmann, Trenton Dambrowitz, Imad Khwaja, Asp the Wyvern, Emad Mostaque, Rainer Wilmers, Alexandros Triantafyllidis, Nicholas, Pedro Madruga, SuperWojo, Harry Royden McLaughlin, James Bentley, Olakabola, David Ziegler, Ai Maven, Jeff Scroggin, Nikolai Manek, Deo Leter, Matthew Berman, Fen Risland, Ken Nordquist, Manuel Alberto Morcote, Luke Pendergrass, TL, Fred von Graf, Randy H, Dan Guido, NimbleBox.ai, Vitor Caleffi, Gabriel Tamborski, knownsqashed, Lone Striker, Erik Bjäreholt, John Detwiler, Leonard Tan, Iucharbius |
|
|
|
|
|
Thank you to all my generous patrons and donaters! |
|
|
|
And thank you again to a16z for their generous grant. |
|
|
|
<!-- footer end --> |
|
|
|
# Original model card: Defog.ai's SQLCoder 7B |
|
|
|
# Defog SQLCoder |
|
Defog's SQLCoder is a state-of-the-art LLM for converting natural language questions to SQL queries. |
|
|
|
[Interactive Demo](https://defog.ai/sqlcoder-demo/) | [🤗 HF Repo](https://huggingface.co/defog/sqlcoder2) | [♾️ Colab](https://colab.research.google.com/drive/1z4rmOEiFkxkMiecAWeTUlPl0OmKgfEu7?usp=sharing) | [🐦 Twitter](https://twitter.com/defogdata) |
|
|
|
## TL;DR |
|
SQLCoder-7B is a 7B parameter model that outperforms `gpt-3.5-turbo` for natural language to SQL generation tasks on our [sql-eval](https://github.com/defog-ai/sql-eval) framework, and significantly outperforms all popular open-source models. When fine-tuned on a given schema, it also outperforms `gpt-4` |
|
|
|
SQLCoder-7B is fine-tuned on a base Mistral-7B model. |
|
|
|
## Results on novel datasets not seen in training |
|
| model | perc_correct | |
|
|-|-| |
|
| gpt4-2023-10-04 | 82.0 | |
|
| defog-sqlcoder2 | 74.5 | |
|
| gpt4-2023-08-28 | 74.0 | |
|
| defog-sqlcoder-7b | 71.0 | |
|
| gpt-3.5-2023-10-04 | 66.0 | |
|
| claude-2 | 64.5 | |
|
| gpt-3.5-2023-08-28 | 61.0 | |
|
| claude_instant_1 | 61.0 | |
|
| text-davinci-003 | 52.5 | |
|
|
|
## License |
|
The code in this repo (what little there is of it) is Apache-2 licensed. The model weights have a `CC BY-SA 4.0` license. The TL;DR is that you can use and modify the model for any purpose – including commercial use. However, if you modify the weights (for example, by fine-tuning), you must open-source your modified weights under the same license terms. |
|
|
|
## Training |
|
SQLCoder was trained on more than 20,000 human-curated questions. These questions were based on 10 different schemas. None of the schemas in the training data were included in our evaluation framework. |
|
|
|
You can read more about our [training approach](https://defog.ai/blog/open-sourcing-sqlcoder2-7b/) and [evaluation framework](https://defog.ai/blog/open-sourcing-sqleval/). |
|
|
|
## Results by question category |
|
We classified each generated question into one of 5 categories. The table displays the percentage of questions answered correctly by each model, broken down by category. |
|
| query_category | gpt-4 | sqlcoder2-15b | sqlcoder-7b | gpt-3.5 | claude-2 | claude-instant | gpt-3 | |
|
|:-----------------|--------:|----------------:|--------------:|----------:|-----------:|-----------------:|--------:| |
|
| date | 72 | 76 | 64 | 68 | 52 | 48 | 32 | |
|
| group_by | 91.4 | 80 | 82.9 | 77.1 | 71.4 | 71.4 | 71.4 | |
|
| order_by | 82.9 | 77.1 | 74.3 | 68.6 | 74.3 | 74.3 | 68.6 | |
|
| ratio | 80 | 60 | 54.3 | 37.1 | 57.1 | 45.7 | 25.7 | |
|
| join | 82.9 | 77.1 | 74.3 | 71.4 | 65.7 | 62.9 | 57.1 | |
|
| where | 80 | 77.1 | 74.3 | 74.3 | 62.9 | 60 | 54.3 | |
|
|
|
## Using SQLCoder |
|
You can use SQLCoder via the `transformers` library by downloading our model weights from the Hugging Face repo. We have added sample code for [inference](./inference.py) on a [sample database schema](./metadata.sql). |
|
```bash |
|
python inference.py -q "Question about the sample database goes here" |
|
|
|
# Sample question: |
|
# Do we get more revenue from customers in New York compared to customers in San Francisco? Give me the total revenue for each city, and the difference between the two. |
|
``` |
|
|
|
You can also use a demo on our website [here](https://defog.ai/sqlcoder-demo), or run SQLCoder in Colab [here](https://colab.research.google.com/drive/13BIKsqHnPOBcQ-ba2p77L5saiepTIwu0#scrollTo=ZpbVgVHMkJvC) |
|
|
|
## Hardware Requirements |
|
SQLCoder has been tested on an A100 40GB GPU with `bfloat16` weights. You can also load an 8-bit and 4-bit quantized version of the model on consumer GPUs with 20GB or more of memory – like RTX 4090, RTX 3090, and Apple M2 Pro, M2 Max, or M2 Ultra Chips with 20GB or more of memory. |
|
|
|
## Todo |
|
|
|
- [x] Open-source the v1 model weights |
|
- [x] Train the model on more data, with higher data variance |
|
- [ ] Tune the model further with Reward Modelling and RLHF |
|
- [ ] Pretrain a model from scratch that specializes in SQL analysis |
|
|
