[CodeGemma 1]Finetune With SQL
Copyright 2024 Google LLC.
Fine-Tuning CodeGemma on the SQL Spider Dataset
Author: Carlo Fisicaro
GitHub: github.com/carlofisicaro
X: @carlo_fisicaro
CodeGemma text-to-sql (Hugging Face)
This notebook demonstrates how to load, fine-tune and deploy CodeGemma model on SQL by utilising Hugging Face.
Setup
Select the Colab runtime
To complete this tutorial, you'll need to have a Colab runtime with sufficient resources to run the Gemma model. In this case, you can use a T4 GPU:
- In the upper-right of the Colab window, select ▾ (Additional connection options).
- Select Change runtime type.
- Under Hardware accelerator, select T4 GPU.
CodeGemma setup
Before we dive into the tutorial, let's get you set up with CodeGemma:
- Hugging Face Account: If you don't already have one, you can create a free Hugging Face account by clicking here.
- CodeGemma Model Access: Head over to the CodeGemma model page and accept the usage conditions.
- Colab with Gemma Power: For this tutorial, you'll need a Colab runtime with enough resources to handle the Gemma 2B model. Choose an appropriate runtime when starting your Colab session.
- Hugging Face Token: Generate a Hugging Face access (preferably
writepermission) token by clicking here. You'll need this token later in the tutorial.
Once you've completed these steps, you're ready to move on to the next section where we'll set up environment variables in your Colab environment.
Configure your HF token
Add your Hugging Face token to the Colab Secrets manager to securely store it.
- Open your Google Colab notebook and click on the 🔑 Secrets tab in the left panel.

- Create a new secret with the name
HF_TOKEN. - Copy/paste your token key into the Value input box of
HF_TOKEN. - Toggle the button on the left to allow notebook access to the secret.
Install dependencies
Run the cell below to install all the required dependencies.
Log into Hugging Face Hub
All set and ready to explore the possibilities with Gemma!
Instantiate the CodeGemma 7B model
CodeGemma is a collection of powerful, lightweight models that can perform a variety of coding tasks like fill-in-the-middle code completion, code generation, natural language understanding, mathematical reasoning, and instruction following. Here we're importing the 7B instruction-tuned variant for natural language-to-code chat and instruction following.
Let's get started by loading the model from Hugging Face Hub.
Loading the model from HF Hub
/usr/local/lib/python3.10/dist-packages/tqdm/auto.py:21: TqdmWarning: IProgress not found. Please update jupyter and ipywidgets. See https://ipywidgets.readthedocs.io/en/stable/user_install.html from .autonotebook import tqdm as notebook_tqdm
Loading checkpoint shards: 100%|██████████████████████████████████████████████████████████████████████████████████████████| 4/4 [00:05<00:00, 1.38s/it]
Let's define a preamble so that our models understands we want to get SQL queries out of it.
Trying it out
Tell me the title of the product list page with the highest conversion rate to detail pages in February 2021. The product list page with the highest conversion rate to detail pages in February 2021 is the **Women's Clothing** page. This page had a conversion rate of **2.5%**, which means that for every 100 visitors to the page, 2.5 of them clicked through to a product detail page. This is a significant conversion rate, and it suggests that the Women's Clothing page is doing a good job of converting visitors into customers. The page features a wide variety of products, from dresses and skirts to jeans and sweaters, and it also provides a variety of helpful features, such as product filters and a search bar. These features make it easy for visitors to find the products they are looking for, and they are also likely to contribute to the high conversion rate.
Let's ask an ambiguous question to CodeGemma
What is the place with the zip code in which the average mean sea level pressure is the lowest? Generate the SQL query with python.
```python
import pandas as pd
import sqlalchemy as sa
# Create a connection to the database
engine = sa.create_engine('postgresql://postgres:password@localhost:5432/postgres')
# Create a query to get the average mean sea level pressure for each zip code
query = """
SELECT zip_code, AVG(mean_sea_level_pressure) AS avg_pressure
FROM weather_data
GROUP BY zip_code
ORDER BY avg_pressure ASC
LIMIT 1;
"""
# Execute the query and store the results in a DataFrame
df = pd.read_sql_query(query, engine)
# Print the zip code with the lowest average mean sea level pressure
print(df['zip_code'].iloc[0])
```
The question is ambiguous because it's not clear whether we're asking for:
- a python script producing a SQL query
- two separate scripts producing respectively, python and SQL code.
CodeGemma picked the first option. Bear it in mind!
Fine-tuning the model with LoRA
This section of the guide focuses on training your Large Language Model (LLM) to generate SQL code from natural language. Here, we will explore the process of fine-tuning your model to enable it to produce high quality SQL queries.
Example item: {'db_id': 'department_management', 'query': 'SELECT count(*) FROM head WHERE age > 56', 'question': 'How many heads of the departments are older than 56 ?', 'query_toks': ['SELECT', 'count', '(', '*', ')', 'FROM', 'head', 'WHERE', 'age', '>', '56'], 'query_toks_no_value': ['select', 'count', '(', '*', ')', 'from', 'head', 'where', 'age', '>', 'value'], 'question_toks': ['How', 'many', 'heads', 'of', 'the', 'departments', 'are', 'older', 'than', '56', '?']}
We need to define a function to tokenize the input. Let's tokenize the 'question' and 'query' columns for training
/usr/local/lib/python3.10/dist-packages/trl/trainer/sft_trainer.py:292: UserWarning: You didn't pass a `max_seq_length` argument to the SFTTrainer, this will default to 1024 warnings.warn( /usr/local/lib/python3.10/dist-packages/trl/trainer/sft_trainer.py:493: UserWarning: You passed a dataset that is already processed (contains an `input_ids` field) together with a valid formatting function. Therefore `formatting_func` will be ignored. warnings.warn( /usr/local/lib/python3.10/dist-packages/trl/trainer/sft_trainer.py:396: UserWarning: You passed a tokenizer with `padding_side` not equal to `right` to the SFTTrainer. This might lead to some unexpected behaviour due to overflow issues when training a model in half-precision. You might consider adding `tokenizer.padding_side = 'right'` to your code. warnings.warn( max_steps is given, it will override any value given in num_train_epochs
TrainOutput(global_step=50, training_loss=56.65366875648498, metrics={'train_runtime': 112.6611, 'train_samples_per_second': 1.775, 'train_steps_per_second': 0.444, 'total_flos': 9555457081344000.0, 'train_loss': 56.65366875648498, 'epoch': 2.0}) Let's ask the same ambiguous question to our CodeGemma finetuned on SQL
/usr/local/lib/python3.10/dist-packages/transformers/generation/configuration_utils.py:567: UserWarning: `do_sample` is set to `False`. However, `temperature` is set to `0.2` -- this flag is only used in sample-based generation modes. You should set `do_sample=True` or unset `temperature`. warnings.warn(
What is the place with the zip code in which the average mean sea level pressure is the lowest? Generate the SQL query with python code to find the answer.
```sql
SELECT zip_code, avg(mean_sea_level_pressure) AS average_pressure
FROM weather_data
GROUP BY zip_code
ORDER BY average_pressure ASC
LIMIT 1;
```
```python
import pandas as pd
# Read the weather data from a CSV file
weather_data = pd.read_csv('weather_data.csv')
# Group the data by zip code and calculate the average mean sea level pressure for each zip code
average_pressure_by_zip_code = weather_data.groupby('zip_code')['mean_sea_level_pressure'].mean()
# Find the zip code with the lowest average mean sea level pressure
zip_code_with_lowest_average_pressure = average_pressure_by_zip_code.idxmin()
# Print the zip code with the lowest average mean sea level pressure
print(zip_code_with_lowest_average_pressure)
```
This time the model picked the second option providing two separate scripts producing respectively, python and SQL code!
The model knows we 'prefer' to get a SQL query now but it didn't forget the other programming languages it's been trained on.
Push the model to your Hugging Face Hub
Hugging Face allow to you easily store trained models in their hub.
Serve you model using Text Generation Inference (TGI)
Text Generation Inference is a toolkit that simplifies deploying and using large language models (LLMs) like Gemma. It optimizes models for text generation tasks, enabling them to run faster and produce results quicker. TGI achieves this through techniques like tensor parallelism, which distributes the workload across multiple graphics cards (GPUs) for faster processing, and optimized code specifically designed for text generation. Additionally, TGI offers features that make it suitable for production environments, such as distributed tracing for monitoring model performance, Prometheus metrics for detailed data collection, and security measures like watermarking to protect model outputs. You can read more about TGI by referring to the official documentation.
To deploy your model with TGI you can either:
-
Deploy it locally (requires Docker): Uncomment the code cells below to run the model on your local machine. This approach requires Docker to be installed and GPU attached.
-
Deploy it on Google Cloud Platform using GKE: Follow this guide Serve Gemma open models using GPUs on GKE with Hugging Face TGI to deploy your model on Google Cloud's GKE service. This option leverages GPUs for high-performance inference.
Both deployment methods will provide you with an HTTP endpoint for sending requests and receiving text generation responses from your model.
Run in Google Colab