metadata

base_model: deepseek-ai/deepseek-coder-6.7b-instruct
tags:
  - instruct
  - finetune
model-index:
  - name: NaturalQuery-6.7B-v0.1
    results: []
license: other
license_name: deepseek
language:
  - en
datasets:
  - cfahlgren1/wiki-sql-codellama-expanded
  - cfahlgren1/natural-sql

NaturalQuery-6.7B-v0.1

NaturalQuery is a series of models with state-of-the-art performance on Text to SQL instructions.

NaturalQuery is a LLM that can translate natural language queries to SQL based on your schema. It is finetuned on 8k text to PostgreSQL Natural Language <> SQL pairs. NaturalQuery matches the state of the art models in text to sql for it's size and produces the best in the field for complex questions.

Here is a write up, small test done here.

Benchmarks
- SQL-Eval on Novel Datasets
- SQL-Eval by Category
Future Improvements
Usage
SQL Generation Template
Example SQL Output
- Example Schemas
- Example Queries

Benchmarks

SQL-Eval on novel datasets not seen in training

Big thanks to the defog team for open sourcing sql-eval👏

SQL-Eval by SQL Category

NaturalQuery-6.7B-v0 matches or outperforms other industry leading models in multiple categories!

The date category will be a strong focus in the next iteration of v1.

Future Improvements in the next iteration:

Much larger training set
More complex schemas, questions, and queries
Strong focus on Date Queries
Reward modeling via DPO

Usage

Make sure you have the correct version of the transformers library installed:

pip install transformers==4.35.2

Loading the Model

Use the following Python code to load the model:

import torch
from transformers import AutoModelForCausalLM, AutoTokenizer
tokenizer = AutoTokenizer.from_pretrained("cfahlgren1/NaturalSQL-6.7B-v0")
model = AutoModelForCausalLM.from_pretrained(
    "cfahlgren1/NaturalSQL-6.7B-v0",
    device_map="auto",
    torch_dtype=torch.float16,
)

Generating Text

To generate text, use the following Python code. Here is a full notebook with the SQL table prompt format to use.

messages=[
    { 'role': 'user', 'content': prompt}
]

inputs = tokenizer.apply_chat_template(messages, add_generation_prompt=True, return_tensors="pt").to(model.device)

# 32023 is the id of <|EOT|> token
outputs = model.generate(inputs, max_new_tokens=512, do_sample=False, top_k=50, top_p=0.95, num_return_sequences=1, eos_token_id=32023)

print(tokenizer.decode(outputs[0][len(inputs[0]):], skip_special_tokens=True))

SQL Generation Template

### Task 

Generate a SQL query to answer the following question: `{natural language question}` 

### Database Schema 

The query will run on a database with the following schema: 

'''
<SQL Table DDL Statements>
'''

### Answer 
Here is the SQL query that answers the question: `{natural language question}` 
'''sql

Example SQL Output

Example Schemas

CREATE TABLE users (
        user_id SERIAL PRIMARY KEY,
        username VARCHAR(50) NOT NULL,
        email VARCHAR(100) NOT NULL,
        password_hash TEXT NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

CREATE TABLE projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    owner_id INTEGER REFERENCES users(user_id)
);

CREATE TABLE tasks (
    task_id SERIAL PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    description TEXT,
    due_date DATE,
    status VARCHAR(50),
    project_id INTEGER REFERENCES projects(project_id)
);

CREATE TABLE taskassignments (
    assignment_id SERIAL PRIMARY KEY,
    task_id INTEGER REFERENCES tasks(task_id),
    user_id INTEGER REFERENCES users(user_id),
    assigned_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    task_id INTEGER REFERENCES tasks(task_id),
    user_id INTEGER REFERENCES users(user_id)
);

Question: Show me the day with the most users joining

SELECT created_at::DATE AS day, COUNT(*) AS user_count
FROM users
GROUP BY day
ORDER BY user_count DESC
LIMIT 1;

Question: Show me the project that has a task with the most comments

SELECT p.project_name, t.task_name, COUNT(c.comment_id) AS comment_count
FROM projects p
JOIN tasks t ON p.project_id = t.project_id
JOIN comments c ON t.task_id = c.task_id
GROUP BY p.project_name, t.task_name
ORDER BY comment_count DESC
LIMIT 1;

Question: What is the ratio of users with gmail addresses vs without?

SELECT 
    SUM(CASE WHEN email ILIKE '%@gmail.com%' THEN 1 ELSE 0 END)::FLOAT / NULLIF(SUM(CASE WHEN email NOT ILIKE '%@gmail.com%' THEN 1 ELSE 0 END), 0) AS gmail_ratio
FROM 
    users;

cfahlgren1
/

NaturalSQL-6.7B-v0