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

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

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

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]

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

@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