Hugging Face's logo

TheBloke
/
sqlcoder2-GGUF

Text Generation
Transformers
GGUF
English
starcoder
code
Model card Files Community
6
sqlcoder2-GGUF / README.md
TheBloke's picture
TheBloke
Upload README.md
bb29b37
preview code
|
raw
history blame contribute delete
23 kB
metadata
base_model: defog/sqlcoder2
inference: false
language:
  - en
license: other
model_creator: Defog.ai
model_name: Sqlcoder2
model_type: starcoder
pipeline_tag: text-generation
prompt_template: |
  ## 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
quantized_by: TheBloke
tags:
  - code
TheBlokeAI

TheBloke's LLM work is generously supported by a grant from andreessen horowitz (a16z)

Sqlcoder2 - GGUF

Description

This repo contains GGUF format model files for Defog.ai's Sqlcoder2.

About GGUF

GGUF is a new format introduced by the llama.cpp team on August 21st 2023. It is a replacement for GGML, which is no longer supported by llama.cpp.

Here is an incomplate list of clients and libraries that are known to support GGUF:

  • llama.cpp. The source project for GGUF. Offers a CLI and a server option.
  • text-generation-webui, the most widely used web UI, with many features and powerful extensions. Supports GPU acceleration.
  • KoboldCpp, a fully featured web UI, with GPU accel across all platforms and GPU architectures. Especially good for story telling.
  • LM Studio, an easy-to-use and powerful local GUI for Windows and macOS (Silicon), with GPU acceleration.
  • LoLLMS Web UI, a great web UI with many interesting and unique features, including a full model library for easy model selection.
  • Faraday.dev, an attractive and easy to use character-based chat GUI for Windows and macOS (both Silicon and Intel), with GPU acceleration.
  • ctransformers, a Python library with GPU accel, LangChain support, and OpenAI-compatible AI server.
  • llama-cpp-python, a Python library with GPU accel, LangChain support, and OpenAI-compatible API server.
  • candle, a Rust ML framework with a focus on performance, including GPU support, and ease of use.

Repositories available

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

Compatibility

These quantised GGUFv2 files are compatible with llama.cpp from August 27th onwards, as of commit d0cee0d

They are also compatible with many third party UIs and libraries - please see the list at the top of this README.

Explanation of quantisation methods

Click to see details

The new methods available are:

  • GGML_TYPE_Q2_K - "type-1" 2-bit quantization in super-blocks containing 16 blocks, each block having 16 weight. Block scales and mins are quantized with 4 bits. This ends up effectively using 2.5625 bits per weight (bpw)
  • GGML_TYPE_Q3_K - "type-0" 3-bit quantization in super-blocks containing 16 blocks, each block having 16 weights. Scales are quantized with 6 bits. This end up using 3.4375 bpw.
  • GGML_TYPE_Q4_K - "type-1" 4-bit quantization in super-blocks containing 8 blocks, each block having 32 weights. Scales and mins are quantized with 6 bits. This ends up using 4.5 bpw.
  • GGML_TYPE_Q5_K - "type-1" 5-bit quantization. Same super-block structure as GGML_TYPE_Q4_K resulting in 5.5 bpw
  • GGML_TYPE_Q6_K - "type-0" 6-bit quantization. Super-blocks with 16 blocks, each block having 16 weights. Scales are quantized with 8 bits. This ends up using 6.5625 bpw

Refer to the Provided Files table below to see what files use which methods, and how.

Provided files

Name Quant method Bits Size Max RAM required Use case
sqlcoder2.Q2_K.gguf Q2_K 2 6.73 GB 9.23 GB smallest, significant quality loss - not recommended for most purposes
sqlcoder2.Q3_K_S.gguf Q3_K_S 3 6.93 GB 9.43 GB very small, high quality loss
sqlcoder2.Q3_K_M.gguf Q3_K_M 3 8.18 GB 10.68 GB very small, high quality loss
sqlcoder2.Q4_0.gguf Q4_0 4 8.99 GB 11.49 GB legacy; small, very high quality loss - prefer using Q3_K_M
sqlcoder2.Q4_K_S.gguf Q4_K_S 4 9.06 GB 11.56 GB small, greater quality loss
sqlcoder2.Q3_K_L.gguf Q3_K_L 3 9.08 GB 11.58 GB small, substantial quality loss
sqlcoder2.Q4_K_M.gguf Q4_K_M 4 9.96 GB 12.46 GB medium, balanced quality - recommended
sqlcoder2.Q5_0.gguf Q5_0 5 10.93 GB 13.43 GB legacy; medium, balanced quality - prefer using Q4_K_M
sqlcoder2.Q5_K_S.gguf Q5_K_S 5 10.93 GB 13.43 GB large, low quality loss - recommended
sqlcoder2.Q5_K_M.gguf Q5_K_M 5 11.54 GB 14.04 GB large, very low quality loss - recommended
sqlcoder2.Q6_K.gguf Q6_K 6 12.99 GB 15.49 GB very large, extremely low quality loss
sqlcoder2.Q8_0.gguf Q8_0 8 16.82 GB 19.32 GB very large, extremely low quality loss - not recommended

Note: the above RAM figures assume no GPU offloading. If layers are offloaded to the GPU, this will reduce RAM usage and use VRAM instead.

How to download GGUF files

Note for manual downloaders: You almost never want to clone the entire repo! Multiple different quantisation formats are provided, and most users only want to pick and download a single file.

The following clients/libraries will automatically download models for you, providing a list of available models to choose from:

  • LM Studio
  • LoLLMS Web UI
  • Faraday.dev

In text-generation-webui

Under Download Model, you can enter the model repo: TheBloke/sqlcoder2-GGUF and below it, a specific filename to download, such as: sqlcoder2.Q4_K_M.gguf.

Then click Download.

On the command line, including multiple files at once

I recommend using the huggingface-hub Python library:

pip3 install huggingface-hub

Then you can download any individual model file to the current directory, at high speed, with a command like this:

huggingface-cli download TheBloke/sqlcoder2-GGUF sqlcoder2.Q4_K_M.gguf --local-dir . --local-dir-use-symlinks False
More advanced huggingface-cli download usage

You can also download multiple files at once with a pattern:

huggingface-cli download TheBloke/sqlcoder2-GGUF --local-dir . --local-dir-use-symlinks False --include='*Q4_K*gguf'

For more documentation on downloading with huggingface-cli, please see: HF -> Hub Python Library -> Download files -> Download from the CLI.

To accelerate downloads on fast connections (1Gbit/s or higher), install hf_transfer:

pip3 install hf_transfer

And set environment variable HF_HUB_ENABLE_HF_TRANSFER to 1:

HF_HUB_ENABLE_HF_TRANSFER=1 huggingface-cli download TheBloke/sqlcoder2-GGUF sqlcoder2.Q4_K_M.gguf --local-dir . --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.

Example llama.cpp command

Make sure you are using llama.cpp from commit d0cee0d or later.

./main -ngl 32 -m sqlcoder2.Q4_K_M.gguf --color -c 2048 --temp 0.7 --repeat_penalty 1.1 -n -1 -p "## 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"

Change -ngl 32 to the number of layers to offload to GPU. Remove it if you don't have GPU acceleration.

Change -c 2048 to the desired sequence length. For extended sequence models - eg 8K, 16K, 32K - the necessary RoPE scaling parameters are read from the GGUF file and set by llama.cpp automatically.

If you want to have a chat-style conversation, replace the -p <PROMPT> argument with -i -ins

For other parameters and how to use them, please refer to the llama.cpp documentation

How to run in text-generation-webui

Further instructions here: text-generation-webui/docs/llama.cpp.md.

How to run from Python code

You can use GGUF models from Python using the llama-cpp-python or ctransformers libraries.

How to load this model in Python code, using ctransformers

First install the package

Run one of the following commands, according to your system:

# Base ctransformers with no GPU acceleration
pip install ctransformers
# Or with CUDA GPU acceleration
pip install ctransformers[cuda]
# Or with AMD ROCm GPU acceleration (Linux only)
CT_HIPBLAS=1 pip install ctransformers --no-binary ctransformers
# Or with Metal GPU acceleration for macOS systems only
CT_METAL=1 pip install ctransformers --no-binary ctransformers

Simple ctransformers example code 

from ctransformers import AutoModelForCausalLM

# Set gpu_layers to the number of layers to offload to GPU. Set to 0 if no GPU acceleration is available on your system.
llm = AutoModelForCausalLM.from_pretrained("TheBloke/sqlcoder2-GGUF", model_file="sqlcoder2.Q4_K_M.gguf", model_type="starcoder", gpu_layers=50)

print(llm("AI is going to"))

How to use with LangChain

Here are guides on using llama-cpp-python and ctransformers with LangChain:

Discord

For further support, and discussions on these models and AI in general, join us at:

TheBloke AI's Discord server

Thanks, and how to contribute

Thanks to the chirper.ai team!

Thanks to Clay from gpus.llm-utils.org!

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.

Special thanks to: Aemon Algiz.

Patreon special mentions: Pierre Kircher, Stanislav Ovsiannikov, Michael Levine, Eugene Pentland, Andrey, 준교 김, Randy H, Fred von Graf, Artur Olbinski, Caitlyn Gatomon, terasurfer, Jeff Scroggin, James Bentley, Vadim, Gabriel Puliatti, Harry Royden McLaughlin, Sean Connelly, Dan Guido, Edmond Seymore, Alicia Loh, subjectnull, AzureBlack, Manuel Alberto Morcote, Thomas Belote, Lone Striker, Chris Smitley, Vitor Caleffi, Johann-Peter Hartmann, Clay Pascal, biorpg, Brandon Frisco, sidney chen, transmissions 11, Pedro Madruga, jinyuan sun, Ajan Kanaga, Emad Mostaque, Trenton Dambrowitz, Jonathan Leane, Iucharbius, usrbinkat, vamX, George Stoitzev, Luke Pendergrass, theTransient, Olakabola, Swaroop Kallakuri, Cap'n Zoog, Brandon Phillips, Michael Dempsey, Nikolai Manek, danny, Matthew Berman, Gabriel Tamborski, alfie_i, Raymond Fosdick, Tom X Nguyen, Raven Klaugh, LangChain4j, Magnesian, Illia Dulskyi, David Ziegler, Mano Prime, Luis Javier Navarrete Lozano, Erik Bjäreholt, 阿明, Nathan Dryer, Alex, Rainer Wilmers, zynix, TL, Joseph William Delisle, John Villwock, Nathan LeClaire, Willem Michiel, Joguhyik, GodLy, OG, Alps Aficionado, Jeffrey Morgan, ReadyPlayerEmma, Tiffany J. Kim, Sebastain Graf, Spencer Kim, Michael Davis, webtim, Talal Aujan, knownsqashed, John Detwiler, Imad Khwaja, Deo Leter, Jerry Meng, Elijah Stavena, Rooh Singh, Pieter, SuperWojo, Alexandros Triantafyllidis, Stephen Murray, Ai Maven, ya boyyy, Enrico Ros, Ken Nordquist, Deep Realms, Nicholas, Spiking Neurons AB, Elle, Will Dee, Jack West, RoA, Luke @flexchar, Viktor Bowallius, Derek Yates, Subspace Studios, jjj, Toran Billups, Asp the Wyvern, Fen Risland, Ilya, NimbleBox.ai, Chadd, Nitin Borwankar, Emre, Mandus, Leonard Tan, Kalila, K, Trailburnt, S_X, Cory Kujawski

Thank you to all my generous patrons and donaters!

And thank you again to a16z for their generous grant.

Original model card: Defog.ai's Sqlcoder2

Defog SQLCoder

Defog's SQLCoder is a state-of-the-art LLM for converting natural language questions to SQL queries.

Interactive Demo | 🤗 HF Repo | ♾️ Colab | 🐦 Twitter

TL;DR

SQLCoder is a 15B parameter model that outperforms gpt-3.5-turbo for natural language to SQL generation tasks on our sql-eval framework, and significantly outperforms all popular open-source models. When fine-tuned on a given schema, it also outperforms gpt-4

SQLCoder is fine-tuned on a base StarCoder 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, with additional responsible use restrictions added. 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

Defog 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 and evaluation framework.

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 on a sample database schema.

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, or run SQLCoder in Colab here

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

  • Open-source the v1 model weights
  • 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