---
library_name: transformers
license: mit
datasets:
- gretelai/synthetic_text_to_sql
pipeline_tag: text-generation
---

# Model Card for LLaMA 3.2 3B Instruct Text2SQL

## Model Details

### Model Description

This is a fine-tuned version of LLaMA 3.2 3B Instruct model, specifically optimized for Text-to-SQL generation tasks. The model has been trained to convert natural language queries into structured SQL commands.

- **Developed by:** Zhafran Ramadhan - XeAI
- **Model type:** Decoder-only Language Model
- **Language(s):** English - MultiLingual
- **License:** MIT
- **Finetuned from model:** LLaMA 3.2 3B Instruct
- **Log WandB Report:** [WandB Report](https://wandb.ai/zhafranr/LLaMA_3-2_3B_Instruct_FineTune_Text2SQL/reports/LLaMa-3-2-3B-Instruct-Fine-Tune-Text2SQL--VmlldzoxMDA2NDkzNA)

### Model Sources
- **Repository:** [LLaMA 3.2 3B Instruct](https://huggingface.co/meta-llama/Llama-3.2-3B-Instruct)
- **Dataset:** [Synthethic Text2SQL](https://huggingface.co/datasets/gretelai/synthetic_text_to_sql)

## How to Get Started with the Model

### Installation
```python
pip install transformers torch accelerate
```

### Input Format and Usage
The model expects input in a specific format following this template:
```text
<|begin_of_text|><|start_header_id|>system<|end_header_id|>

[System context and database schema]

<|eot_id|><|start_header_id|>user<|end_header_id|>

[User query]

<|eot_id|><|start_header_id|>assistant<|end_header_id|>
```

### Basic Usage
```python
from transformers import pipeline
import torch

# Initialize the pipeline
generator = pipeline(
    "text-generation",
    model="XeAI/LLaMa_3.2_3B_Instruct_Text2SQL",  # Replace with your model ID
    torch_dtype=torch.float16,
    device_map="auto"
)

def generate_sql_query(context, question):
    # Format the prompt according to the training template
    prompt = f"""<|begin_of_text|><|start_header_id|>system<|end_header_id|>

Cutting Knowledge Date: December 2023
Today Date: 07 Nov 2024

You are a specialized SQL query generator focused solely on the provided RAG database. Your tasks are:
1. Generate SQL queries based on user requests that are related to querying the RAG database.
2. Only output the SQL query itself, without any additional explanation or commentary.
3. Use the context provided from the RAG database to craft accurate queries.

Context: {context}
<|eot_id|><|start_header_id|>user<|end_header_id|>

{question}<|eot_id|><|start_header_id|>assistant<|end_header_id|>"""

    response = generator(
        prompt,
        max_length=500,
        num_return_sequences=1,
        temperature=0.1,
        do_sample=True,
        pad_token_id=generator.tokenizer.eos_token_id
    )
    
    return response[0]['generated_text']

# Example usage
context = """CREATE TABLE upgrades (id INT, cost FLOAT, type TEXT);
INSERT INTO upgrades (id, cost, type) VALUES 
(1, 500, 'Insulation'), 
(2, 1000, 'HVAC'), 
(3, 1500, 'Lighting');"""

questions = [
    "Find the energy efficiency upgrades with the highest cost and their types.",
    "Show me all upgrades costing less than 1000 dollars.",
    "Calculate the average cost of all upgrades."
]

for question in questions:
    sql = generate_sql_query(context, question)
    print(f"\nQuestion: {question}")
    print(f"Generated SQL: {sql}\n")
```

### Advanced Usage with Custom System Prompt
```python
def generate_sql_with_custom_prompt(context, question, custom_system_prompt=""):
    base_prompt = """<|begin_of_text|><|start_header_id|>system<|end_header_id|>

Cutting Knowledge Date: December 2023
Today Date: 07 Nov 2024

You are a specialized SQL query generator focused solely on the provided RAG database."""

    full_prompt = f"""{base_prompt}
{custom_system_prompt}

Context: {context}
<|eot_id|><|start_header_id|>user<|end_header_id|>

{question}<|eot_id|><|start_header_id|>assistant<|end_header_id|>"""

    response = generator(
        full_prompt,
        max_length=500,
        num_return_sequences=1,
        temperature=0.1,
        do_sample=True,
        pad_token_id=generator.tokenizer.eos_token_id
    )
    
    return response[0]['generated_text']
```

### Best Practices
1. **Input Formatting**:
   - Always include the special tokens (<|begin_of_text|>, <|eot_id|>, etc.)
   - Provide complete database schema in context
   - Keep questions clear and focused on data retrieval

2. **Parameter Configuration**:
   - Use temperature=0.1 for consistent SQL generation
   - Adjust max_length based on expected query complexity
   - Enable do_sample for more natural completions

3. **Context Management**:
   - Include relevant table schemas
   - Provide sample data when needed
   - Keep context concise but complete
     
## Uses

### Direct Use
The model is designed for converting natural language questions into SQL queries. It can be used for:
- Database query generation from natural language
- SQL query assistance
- Data analysis automation

### Out-of-Scope Use
- Production deployment without human validation
- Critical decision-making without human oversight
- Direct database execution without query validation

## Training Details

### Training Data
- Dataset: [Synthethic Text2SQL](https://huggingface.co/datasets/gretelai/synthetic_text_to_sql)
- Data preprocessing: Standard text-to-SQL formatting

### Training Procedure

#### Training Hyperparameters
- **Total Steps:** 4,149
- **Final Training Loss:** 0.1168
- **Evaluation Loss:** 0.2125
- **Learning Rate:** Dynamic with final LR = 0
- **Epochs:** 2.99
- **Gradient Norm:** 1.3121

#### Performance Metrics
- **Training Samples/Second:** 6.291
- **Evaluation Samples/Second:** 19.325
- **Steps/Second:** 3.868
- **Total FLOPS:** 1.92e18

#### Training Infrastructure
- **Hardware:** Single NVIDIA H100 GPU
- **Training Duration:** 5-6 hours
- **Total Runtime:** 16,491.75 seconds
- **Model Preparation Time:** 0.0051 seconds

## Evaluation

### Metrics
The model's performance was tracked using several key metrics:
- **Training Loss:** Started at ~1.2, converged to 0.1168
- **Evaluation Loss:** 0.2125
- **Processing Efficiency:** 19.325 samples per second during evaluation

### Results Summary
- Achieved stable convergence after ~4000 steps
- Maintained consistent performance metrics throughout training
- Shows good balance between training and evaluation loss

## Environmental Impact

- **Hardware Type:** NVIDIA H100 GPU
- **Hours used:** ~6 hours
- **Training Location:** [GPUaaS](www.runpod.io)

## Technical Specifications

### Compute Infrastructure
- **GPU:** NVIDIA H100
- **Training Duration:** 5-6 hours
- **Total Steps:** 4,149
- **FLOPs Utilized:** 1.92e18

## Model Card Contact
[Contact information to be added by Zhafran Ramadhan]

---
*Note: This model card follows the guidelines set by the ML community for responsible AI development and deployment.*