Sqlcoder 7B GGUF

🚀 SQLCoder 7B - GGUF

SQLCoder 7B is a model designed for SQL query generation. This repository offers its GGUF format model files, which are quantized for efficient use in various environments.

🚀 Quick Start

This section will guide you through the basic steps to get started with SQLCoder 7B.

Prerequisites

• Ensure you have the appropriate hardware and software environment for running the model.
• Familiarize yourself with the GGUF format and its supported clients/libraries.

Download the Model

You can download the model using different methods. For example, with the huggingface-hub Python library:

pip3 install huggingface-hub
huggingface-cli download TheBloke/sqlcoder-7B-GGUF sqlcoder-7b.Q4_K_M.gguf --local-dir . --local-dir-use-symlinks False

Run the Model

Here is an example command using llama.cpp:

./main -ngl 32 -m sqlcoder-7b.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\n"

✨ Features

• Multiple Quantization Options: Offers various quantization methods (Q2_K, Q3_K, etc.) to balance model size and quality.
• Wide Compatibility: Compatible with many clients and libraries such as llama.cpp, text-generation-webui, etc.
• SQL Query Generation: Specifically designed to generate SQL queries based on given questions and database schemas.

📦 Installation

Install Dependencies

If you want to download the model using the command line, you need to install the huggingface-hub Python library:

pip3 install huggingface-hub

Download the Model File

You can download a single model file to the current directory with a command like this:

huggingface-cli download TheBloke/sqlcoder-7B-GGUF sqlcoder-7b.Q4_K_M.gguf --local-dir . --local-dir-use-symlinks False

💻 Usage Examples

Basic Usage

Here is an example of using the model to generate a SQL query. First, you need to set up the prompt according to the prompt template:

# Assume you have loaded the model and set up the environment
prompt = "Find the total number of products sold"
# Format the prompt according to the template
formatted_prompt = f"## 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"
# Then use the model to generate the SQL query
# The actual code to interact with the model depends on the client/library you are using

Advanced Usage

In more complex scenarios, you can adjust the parameters of the model to get better results. For example, in the llama.cpp command, you can change the temperature (--temp) and repeat penalty (--repeat_penalty) to control the randomness and repetition of the generated text.

./main -ngl 32 -m sqlcoder-7b.Q4_K_M.gguf --color -c 2048 --temp 0.8 --repeat_penalty 1.2 -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\n"

📚 Documentation

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 a 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

• AWQ model(s) for GPU inference.
• GPTQ models for GPU inference, with multiple quantisation parameter options.
• 2, 3, 4, 5, 6 and 8-bit GGUF models for CPU+GPU inference
• Defog.ai's original unquantised fp16 model in pytorch format, for GPU inference and for further conversions

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](https://github.com/ggerganov/llama.cpp/commit/d0cee0d36d5be95a0d9088b674dbb27354107221).

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
<details>
  <summary>Click to see details</summary>

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.
</details>

### Provided files
| Name | Quant method | Bits | Size | Max RAM required | Use case |
| ---- | ---- | ---- | ---- | ---- | ----- |
| [sqlcoder-7b.Q2_K.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q2_K.gguf) | Q2_K | 2 | 3.08 GB| 5.58 GB | smallest, significant quality loss - not recommended for most purposes |
| [sqlcoder-7b.Q3_K_S.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q3_K_S.gguf) | Q3_K_S | 3 | 3.16 GB| 5.66 GB | very small, high quality loss |
| [sqlcoder-7b.Q3_K_M.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q3_K_M.gguf) | Q3_K_M | 3 | 3.52 GB| 6.02 GB | very small, high quality loss |
| [sqlcoder-7b.Q3_K_L.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q3_K_L.gguf) | Q3_K_L | 3 | 3.82 GB| 6.32 GB | small, substantial quality loss |
| [sqlcoder-7b.Q4_0.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q4_0.gguf) | Q4_0 | 4 | 4.11 GB| 6.61 GB | legacy; small, very high quality loss - prefer using Q3_K_M |
| [sqlcoder-7b.Q4_K_S.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q4_K_S.gguf) | Q4_K_S | 4 | 4.14 GB| 6.64 GB | small, greater quality loss |
| [sqlcoder-7b.Q4_K_M.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q4_K_M.gguf) | Q4_K_M | 4 | 4.37 GB| 6.87 GB | medium, balanced quality - recommended |
| [sqlcoder-7b.Q5_0.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q5_0.gguf) | Q5_0 | 5 | 5.00 GB| 7.50 GB | legacy; medium, balanced quality - prefer using Q4_K_M |
| [sqlcoder-7b.Q5_K_S.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q5_K_S.gguf) | Q5_K_S | 5 | 5.00 GB| 7.50 GB | large, low quality loss - recommended |
| [sqlcoder-7b.Q5_K_M.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q5_K_M.gguf) | Q5_K_M | 5 | 5.13 GB| 7.63 GB | large, very low quality loss - recommended |
| [sqlcoder-7b.Q6_K.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q6_K.gguf) | Q6_K | 6 | 5.94 GB| 8.44 GB | very large, extremely low quality loss |
| [sqlcoder-7b.Q8_0.gguf](https://huggingface.co/TheBloke/sqlcoder-7B-GGUF/blob/main/sqlcoder-7b.Q8_0.gguf) | Q8_0 | 8 | 7.70 GB| 10.20 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/sqlcoder-7B-GGUF and below it, a specific filename to download, such as: sqlcoder-7b.Q4_K_M.gguf. Then click Download.

#### On the command line, including multiple files at once
I recommend using the `huggingface-hub` Python library:
```shell
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/sqlcoder-7B-GGUF sqlcoder-7b.Q4_K_M.gguf --local-dir . --local-dir-use-symlinks False

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

pip3 install hf_transfer

HF_HUB_ENABLE_HF_TRANSFER=1 huggingface-cli download TheBloke/sqlcoder-7B-GGUF sqlcoder-7b.Q4_K_M.gguf --local-dir . --local-dir-use-symlinks False

🔧 Technical Details

Model Architecture

SQLCoder 7B is based on the Mistral architecture, which provides a solid foundation for natural language processing tasks, especially SQL query generation.

Quantization

The model uses different quantization methods (Q2_K, Q3_K, etc.) to reduce the model size and improve the inference speed. Each quantization method has its own characteristics in terms of bit usage, quality loss, and memory requirements.

📄 License

The model is released under the cc-by-sa-4.0 license.

Additional Information