README.md

---
base_model: unsloth/qwen2.5-coder-32b-instruct-bnb-4bit
library_name: peft
datasets:
- 100suping/ko-bird-sql-schema
- won75/text_to_sql_ko
language:
- ko
pipeline_tag: text-generation
tags:
- SQL
- lora
- adapter
- instruction-tuning
---

# 100suping/Qwen2.5-Coder-34B-Instruct-kosql-adapter

<!-- Provide a quick summary of what the model is/does. -->
This Repo contains **LoRA (Low-Rank Adaptation) Adapter** for [unsloth/qwen2.5-coder-32b-instruct-bnb-4bit]

The Adapter was trained for **improving model's SQL generation capability** in **Korean question & multi-db context**. 

This adapter was created through **instruction tuning**.


## Model Details

### Model Description

<!-- Provide a longer summary of what this model is. -->


- **Base Model:** unsloth/Qwen2.5-Coder-32B-Instruct
- **Task:** Instruction Following(Korean)
- **Language:** English (or relevant language)
- **Training Data:** 100suping/ko-bird-sql-schema, won75/text_to_sql_ko
- **Model type:** Causal Language Models.
- **Language(s) (NLP):** Multi-Language

## How to Get Started with the Model

<!-- Address questions around how the model is intended to be used, including the foreseeable users of the model and those affected by the model. -->
To use this LoRA adapter, refer to the following code:

### Adapter Loading

```
from transformers import BitsAndBytesConfig

def get_bnb_config(bit=8):
    if bit == 8:
        return BitsAndBytesConfig(load_in_8bit=True)
    else:
        print(f"You put {bit} bit in argument.\nWhatever the number you put in, if it is not 8 then 4bit config would be returned.")
        return BitsAndBytesConfig(load_in_4bit=True)
```

```
from unsloth import FastLanguageModel

model_name = "unsloth/Qwen2.5-Coder-32B-Instruct"
adapter_revision = "checkpoint-200" # checkpoint-100 ~ 350, main(which is checkpoint-384)

bnb_config = get_bnb_config(bit=bit)
model, tokenizer = FastLanguageModel.from_pretrained(
    model_name=model_name,
    dtype=None,
    quantization_config=bnb_config,
)
model.load_adapter("100suping/Qwen2.5-Coder-34B-Instruct-kosql-adapter", revision=adapter_revision)
```

### Prompt

<!-- This section is for the model use without fine-tuning or plugging into a larger ecosystem/app. -->

```
GENERAL_QUERY_PREFIX = """당신은 사용자의 입력을 MySQL 쿼리문으로 바꾸어주는 조직의 팀원입니다.
당신의 임무는 DB 이름 그리고 DB내 테이블의 메타 정보가 담긴 아래의 (context)를 이용해서 주어진 질문(user_question)에 걸맞는 MySQL 쿼리문을 작성하는 것입니다.

(context)
{context}
"""

GENERATE_QUERY_INSTRUCTIONS = """
주어진 질문(user_question)에 대해서 문법적으로 올바른 MySQL 쿼리문을 작성해 주세요.
"""
```

### Example input

```
<|im_start|>system
당신은 사용자의 입력을 MySQL 쿼리문으로 바꾸어주는 조직의 팀원입니다.
당신의 임무는 DB 이름 그리고 DB내 테이블의 메타 정보가 담긴 아래의 (context)를 이용해서 주어진 질문(user_question)에 걸맞는 MySQL 쿼리문을 작성하는 것입니다.

(context)
DB: movie_platform
table DDL: CREATE TABLE `movies` ( `movie_id` INTEGER `movie_title` TEXT `movie_release_year` INTEGER `movie_url` TEXT `movie_title_language` TEXT `movie_popularity` INTEGER `movie_image_url` TEXT `director_id` TEXT `director_name` TEXT `director_url` TEXT PRIMARY KEY (movie_id) FOREIGN KEY (user_id) REFERENCES `lists_users`(user_id) FOREIGN KEY (user_id) REFERENCES `lists_users`(user_id) FOREIGN KEY (user_id) REFERENCES `lists`(user_id) FOREIGN KEY (list_id) REFERENCES `lists`(list_id) FOREIGN KEY (user_id) REFERENCES `ratings_users`(user_id) FOREIGN KEY (user_id) REFERENCES `lists_users`(user_id) FOREIGN KEY (movie_id) REFERENCES `movies`(movie_id) );


주어진 질문(user_question)에 대해서 문법적으로 올바른 MySQL 쿼리문을 작성해 주세요.
<|im_end|>
<|im_start|>user
가장 인기 있는 영화는 무엇인가요? 그 영화는 언제 개봉되었고 누가 감독인가요?<|im_end|>
<|im_start|>assistant
```sql
SELECT movie_title, movie_release_year, director_name FROM movies ORDER BY movie_popularity DESC LIMIT 1 ;
```<|im_end|>
```


### Inference - pytorch

```
messages = [
        {"role": "system", "content": GENERAL_QUERY_PREFIX.format(context=context) + GENERATE_QUERY_INSTRUCTIONS},
        {"role": "user", "content": "user_question: "+ user_question}
    ]


text = tokenizer.apply_chat_template(
        messages,
        tokenize=False,
        add_generation_prompt=True
    )

model_inputs = tokenizer([text], return_tensors="pt").to(model.device)

generated_ids = model.generate(
    **model_inputs,
    max_new_tokens=max_new_tokens
)
generated_ids = [
    output_ids[len(input_ids):] for input_ids, output_ids in zip(model_inputs.input_ids, generated_ids)
]

response = tokenizer.batch_decode(generated_ids, skip_special_tokens=True)[0]
```

### Inference - LangChain & HuggingFacePipeline

```
bnb_config = get_bnb_config(bit=bit)

model, tokenizer = FastLanguageModel.from_pretrained(
    model_name=model_name,
    dtype=None,
    quantization_config=bnb_config,
)

pipe = pipeline("text-generation", model=model, tokenizer=tokenizer, max_new_tokens=max_new_tokens)
hf_llm = HuggingFacePipeline(pipeline=pipe)

prompt = ChatPromptTemplate.from_messages(
        [
            SystemMessage(
                content=GENERAL_QUERY_PREFIX.format(context=context) + GENERATE_QUERY_INSTRUCTIONS
            ),
            (
                "human",
                "질문(user_question): {user_question}",
            ),
        ]
    )

chain = prompt | hf_llm

response = chain.invoke({"user_question" : user_question})
```


## Training Details

### Training Data

<!-- This should link to a Dataset Card, perhaps with a short stub of information on what the training data is all about as well as documentation related to data pre-processing or additional filtering. -->
https://huggingface.co/datasets/100suping/ko-bird-sql-schema
- Naive translation of english quesiton to korean quesiton

https://huggingface.co/datasets/won75/text_to_sql_ko
- Generated data from 100 seed data


### Training Procedure

<!-- This relates heavily to the Technical Specifications. Content here should link to that section when it is relevant to the training procedure. -->

https://github.com/100suping/train_with_unsloth


### Preprocess Functions

```
def get_conversation_data(examples):
    questions = examples['question']
    schemas =examples['schema']
    sql_queries =examples['SQL']
    convos = []
    for question, schema, sql in zip(questions, schemas, sql_queries):
        conv = [
        {"role": "system", "content": GENERAL_QUERY_PREFIX.format(context=schema) + GENERATE_QUERY_INSTRUCTIONS},
        {"role": "user", "content": question},
        {"role": "assistant", "content": "```sql\n"+sql+";\n```"}
        ]
        convos.append(conv)
    return {"conversation":convos,}

def formatting_prompts_func(examples):
    convos = examples["conversation"]
    texts = [tokenizer.apply_chat_template(convo, tokenize = False, add_generation_prompt = False) for convo in convos]
    return { "text" : texts, }
```

#### Training Hyperparameters

- **Training regime:** bf16 mixed-precision
- **Load-in-8bit:** True
- **LoRA config:**
  - r=16
  - lora_alpha=32
  - lora_dropout=0.0
  - target_modules = "q proj", "k_proj", "v_proj", "o_proj","gate_proj", "up_proj", "down_proj"
  - bias = "none"
  - use_gradient_checkpointing = "unsloth"
  - use_rslora = False
  - loftq_config = None
- **Training Data:** 100suping/ko-bird-sql-schema, won75/text_to_sql_ko
- **Max_seq_length:** 4096
- **Save_steps:** 50
- **Epochs:** 2
- **Global_steps:** 384
- **Batch_size:** 16
- **Gradient_accumulation_steps:** 2
- **Learning_rate:** 2e-4
- **Warmup_steps:** 20 

#### Speeds, Sizes, Times [optional]

<!-- This section provides information about throughput, start/end time, checkpoint size if relevant, etc. -->
- **Device:** G-NAHP-80 from EliceCloud(https://elice.io/ko/products/cloud/on-demand)
  - A100 80GB PCle (However somehow if i use more than 60GB, error shows up)
  - CPU 16 vCore
  - Memory 192 GiB
  - Storage 100 GiB
- **Memory-Used(GPU VRAM):** ~60GB

## For Continuous Instruction-tuning

To use this LoRA adapter, refer to the following code:

```
from peft import PeftModel

bnb_config = get_bnb_config(bit=bit)

model, tokenizer = FastLanguageModel.from_pretrained(
    model_name=model_name,
    dtype=None,
    quantization_config=bnb_config,
)

model = PeftModel.from_pretrained(model, adapter_path, is_trainable=True)
model = FastLanguageModel.patch_peft_model(model, use_gradient_checkpointing="unsloth")

model.print_trainable_parameters()
```


## Citation
<!-- If there is a paper or blog post introducing the model, the APA and Bibtex information for that should go in this section. -->

```
@article{hui2024qwen2,
      title={Qwen2. 5-Coder Technical Report},
      author={Hui, Binyuan and Yang, Jian and Cui, Zeyu and Yang, Jiaxi and Liu, Dayiheng and Zhang, Lei and Liu, Tianyu and Zhang, Jiajun and Yu, Bowen and Dang, Kai and others},
      journal={arXiv preprint arXiv:2409.12186},
      year={2024}
}
@article{qwen2,
      title={Qwen2 Technical Report}, 
      author={An Yang and Baosong Yang and Binyuan Hui and Bo Zheng and Bowen Yu and Chang Zhou and Chengpeng Li and Chengyuan Li and Dayiheng Liu and Fei Huang and Guanting Dong and Haoran Wei and Huan Lin and Jialong Tang and Jialin Wang and Jian Yang and Jianhong Tu and Jianwei Zhang and Jianxin Ma and Jin Xu and Jingren Zhou and Jinze Bai and Jinzheng He and Junyang Lin and Kai Dang and Keming Lu and Keqin Chen and Kexin Yang and Mei Li and Mingfeng Xue and Na Ni and Pei Zhang and Peng Wang and Ru Peng and Rui Men and Ruize Gao and Runji Lin and Shijie Wang and Shuai Bai and Sinan Tan and Tianhang Zhu and Tianhao Li and Tianyu Liu and Wenbin Ge and Xiaodong Deng and Xiaohuan Zhou and Xingzhang Ren and Xinyu Zhang and Xipin Wei and Xuancheng Ren and Yang Fan and Yang Yao and Yichang Zhang and Yu Wan and Yunfei Chu and Yuqiong Liu and Zeyu Cui and Zhenru Zhang and Zhihao Fan},
      journal={arXiv preprint arXiv:2407.10671},
      year={2024}
}
```

## Model Card Authors

joonavel[https://github.com/joonavel] from 100suping [https://github.com/100suping]

### Framework versions

- PEFT 0.13.2