metadata

license: llama2
datasets:
  - bugdaryan/sql-create-context-instruction
language:
  - en
pipeline_tag: text-generation
widget:
  - text: >-
      [INST] Write SQLite query to answer the following question given the
      database schema. Please wrap your code answer using ```: Schema: CREATE
      TABLE head (age INTEGER) Question: How many heads of the departments are
      older than 56 ? [/INST] Here is the SQLite query to answer to the
      question: How many heads of the departments are older than 56 ?: ```
    example_title: Example 1
  - text: >-
      [INST] Write SQLite query to answer the following question given the
      database schema. Please wrap your code answer using ```: Schema: CREATE
      TABLE people (first_name VARCHAR) Question: List the first names of people
      in alphabetical order? [/INST] Here is the SQLite query to answer to the
      question: List the first names of people in alphabetical order?: ```
    example_title: Example 2
  - text: >-
      [INST] Write SQLite query to answer the following question given the
      database schema. Please wrap your code answer using ```: Schema: CREATE
      TABLE weather (zip_code VARCHAR, mean_sea_level_pressure_inches INTEGER)
      Question: What is the zip code in which the average mean sea level
      pressure is the lowest? [/INST] Here is the SQLite query to answer to the
      question: What is the zip code in which the average mean sea level
      pressure is the lowest?: ```
    example_title: Example 3
  - text: >-
      [INST] Write SQLite query to answer the following question given the
      database schema. Please wrap your code answer using ```: Schema: CREATE
      TABLE weather (date VARCHAR, mean_temperature_f VARCHAR, mean_humidity
      VARCHAR, max_gust_speed_mph VARCHAR) Question: What are the date, mean
      temperature and mean humidity for the top 3 days with the largest max gust
      speeds? [/INST] Here is the SQLite query to answer to the question: What
      are the date, mean temperature and mean humidity for the top 3 days with
      the largest max gust speeds?: ```
    example_title: Example 4
  - text: >-
      [INST] Write SQLite query to answer the following question given the
      database schema. Please wrap your code answer using ```: Schema: CREATE
      TABLE trip (end_station_id VARCHAR); CREATE TABLE station (id VARCHAR,
      city VARCHAR) Question: Count the number of trips that did not end in San
      Francisco city. [/INST] Here is the SQLite query to answer to the
      question: Count the number of trips that did not end in San Francisco
      city.: ```
    example_title: Example 5

Code-Llama-2-13B-instruct-text2sql Model Card

Model Name: Code-Llama-2-13B-instruct-text2sql

Description: This model is a fine-tuned version of the Code Llama 2 with 13 billion parameters, specifically tailored for text-to-SQL tasks. It has been trained to generate SQL queries given a database schema and a natural language question.

Model Information

Base Model: codellama/CodeLlama-13b-Instruct-hf
Finetuning Dataset: bugdaryan/sql-create-context-instruction
Training Time: Approximately 4 hours on 2 V100 32GB GPUs

LoRA Parameters

lora_r: 64
lora_alpha: 16
lora_dropout: 0.1

bitsandbytes Parameters

use_4bit: True
bnb_4bit_compute_dtype: float16
bnb_4bit_quant_type: nf4
use_nested_quant: False

Training Parameters

Number of Training Epochs: 1
Mixed-Precision Training (fp16/bf16): False
Batch Size per GPU for Training: 32
Batch Size per GPU for Evaluation: 4
Gradient Accumulation Steps: 1
Gradient Checkpointing: True
Maximum Gradient Norm (Gradient Clipping): 0.3
Initial Learning Rate: 2e-4
Weight Decay: 0.001
Optimizer: paged_adamw_32bit
Learning Rate Scheduler Type: cosine
Max Steps: -1
Warmup Ratio: 0.03
Group Sequences by Length: True
Save Checkpoint Every X Update Steps: 0
Log Every X Update Steps: 25

License

This model is governed by a custom commercial license from Code Llama. For details, please visit: Custom Commercial License

Intended Use

Intended Use Cases: This model is intended for commercial and research use in English. It is designed for text-to-SQL tasks, enabling users to generate SQL queries from natural language questions.

Out-of-Scope Uses: Any use that violates applicable laws or regulations, use in languages other than English, or any other use prohibited by the Acceptable Use Policy and Licensing Agreement for Code Llama and its variants.

Model Capabilities

Code completion.
Infilling.
Instructions / chat.

Model Architecture

Code-Llama-2-13B-instruct-text2sql is an auto-regressive language model that uses an optimized transformer architecture.

Model Dates

This model was trained between January 2023 and July 2023.

Ethical Considerations and Limitations

Code-Llama-2-13B-instruct-text2sql is a powerful language model, but it may produce inaccurate or objectionable responses in some instances. Safety testing and tuning are recommended before deploying this model in specific applications.

Hardware and Software

Training Libraries: Custom training libraries
Training Hardware: 2 V100 32GB GPUs
Carbon Footprint: Training all Code Llama models required 400K GPU hours on A100-80GB hardware with emissions offset by Meta's sustainability program.

Training Data

This model was trained and fine-tuned on the same data as Llama 2 with different weights.

Evaluation Results

For evaluation results, please refer to Section 3 and safety evaluations in Section 4 of the research paper.

Example Code

You can use the Code-Llama-2-13B-instruct-text2sql model to generate SQL queries from natural language questions, as demonstrated in the following code snippet:

from transformers import (
    AutoModelForCausalLM,
    AutoTokenizer,
    pipeline
)
import torch

model_name = 'bugdaryan/Code-Llama-2-13B-instruct-text2sql'

model = AutoModelForCausalLM.from_pretrained(model_name, device_map='auto')
tokenizer = AutoTokenizer.from_pretrained(model_name)

pipe = pipeline('text-generation', model=model, tokenizer=tokenizer)

table = "CREATE TABLE sales ( sale_id number PRIMARY KEY, product_id number, customer_id number, salesperson_id number, sale_date DATE, quantity number, FOREIGN KEY (product_id) REFERENCES products(product_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id)); CREATE TABLE product_suppliers ( supplier_id number PRIMARY KEY, product_id number, supply_price number, FOREIGN KEY (product_id) REFERENCES products(product_id)); CREATE TABLE customers ( customer_id number PRIMARY KEY, name text, address text ); CREATE TABLE salespeople ( salesperson_id number PRIMARY KEY, name text, region text ); CREATE TABLE product_suppliers ( supplier_id number PRIMARY KEY, product_id number, supply_price number );"

question = 'Find the salesperson who made the most sales.'

prompt = f"[INST] Write SQLite query to answer the following question given the database schema. Please wrap your code answer using ```: Schema: {table} Question: {question} [/INST] Here is the SQLite query to answer to the question: {question}: ``` "

ans = pipe(prompt, max_new_tokens=100)
print(ans[0]['generated_text'].split('```')[2])

This code demonstrates how to utilize the model for generating SQL queries based on a provided database schema and a natural language question. It showcases the model's capability to assist in SQL query generation for text-to-SQL tasks.