pavankumarbalijepalli/phi2-sqlcoder

1---2license: mit3datasets:4- b-mc2/sql-create-context5language:6- en7metrics:8- accuracy9- code_eval10library_name: transformers11pipeline_tag: text-generation12tags:13- peft14- nl2sql15widget:16- text: "### Task\nGenerate a SQL query to answer the following question:\n`How many heads of the departments are older than 56?`\n\n### Database Schema\nThe query will run on a database with the following schema:\nCREATE TABLE head (age INTEGER)\n\n### Answer\nGiven the database schema, here is the SQL query that answers `How many heads of the departments are older than 56?`:\n```sql"17  example_title: "One Table"18- text: "### Task\nGenerate a SQL query to answer the following question:\n`How many departments are led by heads who are not mentioned?`\n\n### Database Schema\nThe query will run on a database with the following schema:\nCREATE TABLE management (department_id VARCHAR);\nCREATE TABLE department (department_id VARCHAR)\n\n### Answer\nGiven the database schema, here is the SQL query that answers `How many departments are led by heads who are not mentioned?`:\n```sql"19  example_title: "Two Tables" 20---21 22# Thanks for being patient! 💜💜23 24# Model Card for Model ID25 26<!-- Provide a quick summary of what the model is/does. -->27 28A fine-tuned version of Phi-2 for the NL2SQL usecase on `b-mc2/sql-create-context` dataset. 29 30## Model Details31 32### Model Description33 34<!-- Provide a longer summary of what this model is. -->35This model has been finetuned with `b-mc2/sql-create-context` on `microsoft/phi-2`. This performed better than `defog/sqlcoder-7b-2` in terms of inference time and accuracy on the holdback dataset. The evaluation is done on `.gguf` models on CPU machine with limited RAM. The average inference times of the Phi-2, and SQLCoder are 24 secs, and 41 secs respectively. That is 41% faster on average. This is due to its smaller size. The Finetuned Phi-2 is 29% better than the SQLCoder based on execution success. The major drawback is its context window of 2048 tokens which requires additional input engineering to get results.36 37- **Developed by:** pavankumarbalijepalli38- **Model type:** CASUAL_LM39- **Language(s) (NLP):** English, SQL40- **License:** MIT41- **Finetuned from model:** [microsoft/phi-2](https://huggingface.co/microsoft/phi-2)42 43### Model Sources44 45<!-- Provide the basic links for the model. -->46 47- **Repository:** [pavankumarbalijepalli/pr-phi2-vs-defog](https://github.com/pavankumarbalijepalli/pr-phi2-vs-defog/)48- **Paper :** [BITS Project Paper](https://github.com/pavankumarbalijepalli/pr-phi2-vs-defog/blob/main/2021SC04115%20-%20Final.pdf)49 50## Uses51 52<!-- Address questions around how the model is intended to be used, including the foreseeable users of the model and those affected by the model. -->53 54Model is supposed to be used for the cases where you have a natural language question, database schema which is relevant the question to retrieve a SQL query which answers the question. The context should be below 2048 tokens. The output will be generated in postgresql.55 56### Direct Use57 58<!-- This section is for the model use without fine-tuning or plugging into a larger ecosystem/app. -->59 60```python61# SAME TEMPLATE AS DEFOG MODEL62prompt = f"""### Task63Generate a SQL query to answer the following question:64`{data_point['question']}`65 66### Database Schema67The query will run on a database with the following schema:68{data_point['context']}69 70### Answer71Given the database schema, here is the SQL query that answers `{data_point['question']}`:72```sql"""73```74 75```python76# USING ON CPU MACHINE77from llama_cpp import Llama78 79phi2 = Llama(model_path=f"{path_to_model}/phi2_sqlcoder_f16.gguf")80 81response = phi2(prompt=prompt, max_tokens = 200, temperature = 0.2, stop = ['```'])82 83print(response['choices'][0]['text'].strip())84```85 86### Downstream Use87 88<!-- This section is for the model use when fine-tuned for a task, or when plugged into a larger ecosystem/app -->89 90```python91# USING ON GPU MACHINE92import torch93from transformers import AutoModelForCausalLM, AutoTokenizer94# from peft import PeftModel, PeftConfig95 96model_name = "pavankumarbalijepalli/phi2-sqlcoder"97 98model = AutoModelForCausalLM.from_pretrained(99    model_name,100    trust_remote_code=True,101    device_map="auto"102)103 104prompt = ""105 106tokenizer = AutoTokenizer.from_pretrained(model_name, trust_remote_code=True)107tokenizer.pad_token = tokenizer.eos_token108inputs = tokenizer(prompt, return_tensors="pt", padding=True, truncation=True)109inputs.to('cuda')110 111outputs = model.generate(**inputs, max_length=1000)112text = tokenizer.batch_decode(outputs,skip_special_tokens=True)[0]113print(text)114```115 116### Out-of-Scope Use117 118<!-- This section addresses misuse, malicious use, and uses that the model will not work well for. -->119 120__Generating Unintended Code:__121 122While the model can translate natural language into SQL queries, it may not be robust enough to handle complex logic or edge cases. Using it to generate critical production code could lead to errors or unexpected behavior in databases.123 124__Security Risks:__125 126NL2SQL models can be susceptible to adversarial attacks where malicious users input natural language designed to trick the model into generating SQL code with security vulnerabilities, like SQL injection attacks.127 128__Beyond its Training Scope:__129 130The model is trained on a specific SQL Language (e.g., PostgreSQL). Using it for a different SQL Syntax (e.g., MS SQL Server) could lead to inaccurate or nonsensical SQL queries.131 132## Bias, Risks, and Limitations133 134<!-- This section is meant to convey both technical and sociotechnical limitations. -->135 136__Bias and Fairness:__137 138The model's training data may contain biases that are reflected in the generated SQL queries. This could lead to unfair or discriminatory outcomes, especially if the data is not carefully curated.139 140__Interpretability and Explainability:__141 142NL2SQL models are often "black boxes" where it's difficult to understand how they translate natural language to SQL. This lack of interpretability makes it challenging to debug errors or ensure the generated queries are safe and efficient.143 144__Replacing Human Expertise:__145 146While the model can automate some SQL query generation tasks, it shouldn't be a complete replacement for human database administrators or analysts. Understanding the data schema and database design is crucial for writing efficient and secure SQL queries.147 148 149### Recommendations150 151<!-- This section is meant to convey recommendations with respect to the bias, risk, and technical limitations. -->152 153Users (both direct and downstream) should be made aware of the risks, biases and limitations of the model.154 155## Training Details156 157### Training Data158 159<!-- 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. -->160 161```162@misc{b-mc2_2023_sql-create-context,163  title   = {sql-create-context Dataset},164  author  = {b-mc2}, 165  year    = {2023},166  url     = {https://huggingface.co/datasets/b-mc2/sql-create-context},167  note    = {This dataset was created by modifying data from the following sources: \cite{zhongSeq2SQL2017, yu2018spider}.},168}169```170 171## Evaluation172 173<!-- This section describes the evaluation protocols and provides the results. -->174 175### Testing Data, Factors & Metrics176 177#### Testing Data178 179<!-- This should link to a Dataset Card if possible. -->180 181Used b-mc2/sql-create-context and split the data into training and testing datasets. The holdout dataset is used for testing the model. 182 183#### Factors184 185<!-- These are the things the evaluation is disaggregating by, e.g., subpopulations or domains. -->186The complexity of the questions are calculated using the number of tables per question, number of joins, group by, and sub queries per answer. This complexity is used to prepare the test data by stratifying the split around the complexity.187 188 189#### Metrics190 191<!-- These are the evaluation metrics being used, ideally with a description of why. -->192* __Execution Success:__ This metric is used to find out if the generated query is executable without arising any errors. For this, a sqllite3 connection is made to the memory, and using context the dummy tables are created. Then the predicted SQL is executed. This checks out if the generated query is in proper syntax, and if the model is hallucinating any new columns.193* __Inference Time:__ This metric is used to find out which model is providing results in less amount of time. This combined with the execution success, gives the efficiency of the model.194- 195### Results196 197* __Execution Success:__ Finetuned Phi-2 has 29% more success rate than the SQLCoder-7b-2198* __Inference Time:__ Finetuned Phi-2 has 41% increased inference speed than SQLCoder-7b-2  199 200#### Summary201* __Reduced Inference Time and Memory Footprint:__ The fine-tuned Phi-2 model202demonstrated a reduction in inference time and memory usage compared to the DeFog203SQLCoder. This is attributed to Phi-2's smaller size and the efficiency of quantization204techniques employed during fine-tuning. This finding implies that NL2SQL models can205be deployed on lower-powered devices like laptops or even mobile phones, potentially206democratizing access to this technology for a wider range of users.207 208* __Competitive Performance on Easy and Medium Queries:__ The fine-tuned Phi-2209achieved comparable performance to the DeFog SQLCoder in terms of accuracy on easy,210medium, and hard difficulty queries. This indicates that Phi-2, despite its smaller size,211can effectively handle a significant portion of real-world NL2SQL tasks, especially for212simpler queries.213 214* __Challenges with Complex Queries:__ While Phi-2 performed well on easier queries, it215encountered challenges with complex queries, exhibiting a drop in execution success216compared to the DeFog SQLCoder. This highlights the trade-off between model size and217complexity, suggesting that larger models might still be necessary for tackling highly218intricate tasks.219 220* __Potential for Further Improvement:__ The fine-tuning process employed in this study221can be further optimized by exploring different hyperparameter configurations and222potentially investigating alternative fine-tuning techniques like adapter-based methods.223This optimization has the potential to improve the model's performance on complex224queries while maintaining its efficiency.225 226## Environmental Impact227 228<!-- Total emissions (in grams of CO2eq) and additional considerations, such as electricity usage, go here. Edit the suggested text below accordingly -->229 230Carbon emissions can be estimated using the [Machine Learning Impact calculator](https://mlco2.github.io/impact#compute) presented in [Lacoste et al. (2019)](https://arxiv.org/abs/1910.09700).231 232- **Hardware Type:** A100 PCIE 40GB X1233- **Hours used:** 18 Hours234- **Cloud Provider:** Google Cloud235- **Compute Region:** Asia-East-1236- **Carbon Emitted:** 2.52 kg eq. CO2237 238 239## Citation240 241<!-- If there is a paper or blog post introducing the model, the APA and Bibtex information for that should go in this section. -->242 243**BibTeX:**244```245@misc {pavan_kumar_balijepalli_2024,246	author       = { {Pavan Kumar Balijepalli} },247	title        = { phi2-sqlcoder (Revision 7a5dc3a) },248	year         = 2024,249	url          = { https://huggingface.co/pavankumarbalijepalli/phi2-sqlcoder },250	doi          = { 10.57967/hf/1886 },251	publisher    = { Hugging Face }252}253```