Use tuning and evaluation to improve model performance

This document shows you how to create a BigQuery ML remote model that references a Vertex AI gemini-2.0-flash-001 model. You then use supervised tuning to tune the model with new training data, followed by evaluating the model with the ML.EVALUATE function.

Tuning can help you address scenarios where you need to customize the hosted Vertex AI model, such as when the expected behavior of the model is hard to concisely define in a prompt, or when prompts don't produce expected results consistently enough. Supervised tuning also influences the model in the following ways:

• Guides the model to return specific response styles—for example being more concise or more verbose.
• Teaches the model new behaviors—for example responding to prompts as a specific persona.
• Causes the model to update itself with new information.

In this tutorial, the goal is to have the model generate text whose style and content conforms as closely as possible to provided ground truth content.

Required roles

To run this tutorial, you need the following Identity and Access Management (IAM) roles:

• Create and use BigQuery datasets, connections, and models: BigQuery Admin (roles/bigquery.admin).
• Grant permissions to the connection's service account: Project IAM Admin (roles/resourcemanager.projectIamAdmin).

These predefined roles contain the permissions required to perform the tasks in this document. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

• Create a dataset: bigquery.datasets.create
• Create a table: bigquery.tables.create
• Create, delegate, and use a connection: bigquery.connections.*
• Set the default connection: bigquery.config.*
• Set service account permissions: resourcemanager.projects.getIamPolicy and resourcemanager.projects.setIamPolicy
• Create a model and run inference:
  • bigquery.jobs.create
  • bigquery.models.create
  • bigquery.models.getData
  • bigquery.models.updateData
  • bigquery.models.updateMetadata

You might also be able to get these permissions with custom roles or other predefined roles.

Before you begin

1. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

2. Make sure that billing is enabled for your Google Cloud project.

3. Enable the BigQuery, BigQuery Connection, Vertex AI, and Compute Engine APIs.

   Enable the APIs

Costs

In this document, you use the following billable components of Google Cloud:

• BigQuery: You incur costs for the queries that you run in BigQuery.
• BigQuery ML: You incur costs for the model that you create and the processing that you perform in BigQuery ML.
• Vertex AI: You incur costs for calls to and supervised tuning of the gemini-1.0-flash-002 model.

To generate a cost estimate based on your projected usage, use the pricing calculator. New Google Cloud users might be eligible for a free trial.

For more information, see the following resources:

• BigQuery storage pricing
• BigQuery ML pricing
• Vertex AI pricing

Create a dataset

Create a BigQuery dataset to store your ML model.

Console

1. In the Google Cloud console, go to the BigQuery page.

   Go to the BigQuery page

2. In the Explorer pane, click your project name.

3. Click View actions > Create dataset.

   

4. On the Create dataset page, do the following:

   • For Dataset ID, enter bqml_tutorial.

   • For Location type, select Multi-region, and then select US (multiple regions in United States).

   • Leave the remaining default settings as they are, and click Create dataset.

bq

To create a new dataset, use the bq mk command with the --location flag. For a full list of possible parameters, see the bq mk --dataset command reference.

1. Create a dataset named bqml_tutorial with the data location set to US and a description of BigQuery ML tutorial dataset:

   bq --location=US mk -d \
    --description "BigQuery ML tutorial dataset." \
    bqml_tutorial

   Instead of using the --dataset flag, the command uses the -d shortcut. If you omit -d and --dataset, the command defaults to creating a dataset.

2. Confirm that the dataset was created:

   bq ls

API

Call the datasets.insert method with a defined dataset resource.

{
  "datasetReference": {
     "datasetId": "bqml_tutorial"
  }
}

BigQuery DataFrames

Before trying this sample, follow the BigQuery DataFrames setup instructions in the BigQuery quickstart using BigQuery DataFrames. For more information, see the BigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up ADC for a local development environment.

import google.cloud.bigquery

bqclient = google.cloud.bigquery.Client()
bqclient.create_dataset("bqml_tutorial", exists_ok=True)

Create test tables

Create tables of training and evaluation data based on the public task955_wiki_auto_style_transfer dataset from Hugging Face.

1. Open the Cloud Shell.

2. In the Cloud Shell, run the following commands to create tables of test and evaluation data:

   python3 -m pip install pandas pyarrow fsspec huggingface_hub
   
   python3 -c "import pandas as pd; df_train = pd.read_parquet('hf://datasets/Lots-of-LoRAs/task955_wiki_auto_style_transfer/data/train-00000-of-00001.parquet').drop('id', axis=1); df_train['output'] = [x[0] for x in df_train['output']]; df_train.to_json('wiki_auto_style_transfer_train.jsonl', orient='records', lines=True);"
   
   python3 -c "import pandas as pd; df_valid = pd.read_parquet('hf://datasets/Lots-of-LoRAs/task955_wiki_auto_style_transfer/data/valid-00000-of-00001.parquet').drop('id', axis=1); df_valid['output'] = [x[0] for x in df_valid['output']]; df_valid.to_json('wiki_auto_style_transfer_valid.jsonl', orient='records', lines=True);"
   
   bq rm -t bqml_tutorial.wiki_auto_style_transfer_train
   
   bq rm -t bqml_tutorial.wiki_auto_style_transfer_valid
   
   bq load --source_format=NEWLINE_DELIMITED_JSON bqml_tutorial.wiki_auto_style_transfer_train wiki_auto_style_transfer_train.jsonl input:STRING,output:STRING
   
   bq load --source_format=NEWLINE_DELIMITED_JSON bqml_tutorial.wiki_auto_style_transfer_valid wiki_auto_style_transfer_valid.jsonl input:STRING,output:STRING
   

Create a baseline model

Create a remote model over the Vertex AI gemini-1.0-flash-002 model.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement to create a remote model:

   CREATE OR REPLACE MODEL `bqml_tutorial.gemini_baseline`
   REMOTE WITH CONNECTION DEFAULT
   OPTIONS (ENDPOINT ='gemini-2.0-flash-001');

   The query takes several seconds to complete, after which the gemini_baseline model appears in the bqml_tutorial dataset in the Explorer pane. Because the query uses a CREATE MODEL statement to create a model, there are no query results.

Check baseline model performance

Run the ML.GENERATE_TEXT function with the remote model to see how it performs on the evaluation data without any tuning.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement:

   SELECT ml_generate_text_llm_result, ground_truth
   FROM
     ML.GENERATE_TEXT(
       MODEL `bqml_tutorial.gemini_baseline`,
       (
         SELECT
           input AS prompt, output AS ground_truth
         FROM `bqml_tutorial.wiki_auto_style_transfer_valid`
         LIMIT 10
       ),
       STRUCT(TRUE AS flatten_json_output));

   If you examine the output data and compare the ml_generate_text_llm_result and ground_truth values, you see that while the baseline model generates text that accurately reflects the facts provided in the ground truth content, the style of the text is fairly different.

Evaluate the baseline model

To perform a more detailed evaluation of the model performance, use the ML.EVALUATE function. This function computes model metrics that measure the accuracy and quality of the generated text, in order to see how the model's responses compare to ideal esponses.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement:

   SELECT *
   FROM
     ML.EVALUATE(
       MODEL `bqml_tutorial.gemini_baseline`,
       (
         SELECT
           input AS input_text, output AS output_text
         FROM `bqml_tutorial.wiki_auto_style_transfer_valid`
       ),
       STRUCT('text_generation' AS task_type));

The output looks similar to the following:

   +---------------------+---------------------+-------------------------------------------+--------------------------------------------+
   | bleu4_score         | rouge-l_precision   | rouge-l_recall      | rouge-l_f1_score    | evaluation_status                          |
   +---------------------+---------------------+---------------------+---------------------+--------------------------------------------+
   | 0.23317359667074181 | 0.37809145226740043 | 0.45902937167791508 | 0.40956844061733139 | {                                          |
   |                     |                     |                     |                     |  "num_successful_rows": 176,               |
   |                     |                     |                     |                     |  "num_total_rows": 176                     |
   |                     |                     |                     |                     | }                                          |
   +---------------------+---------------------+ --------------------+---------------------+--------------------------------------------+
   

You can see that the baseline model performance isn't bad, but the similarity of the generated text to the ground truth is low, based on the evaluation metrics. This indicates that it is worth performing supervised tuning to see if you can improve model performance for this use case.

Create a tuned model

Create a remote model very similar to the one you created in Create a model, but this time specifying the AS SELECT clause to provide the training data in order to tune the model.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement to create a remote model:

   CREATE OR REPLACE MODEL `bqml_tutorial.gemini_tuned`
     REMOTE
       WITH CONNECTION DEFAULT
     OPTIONS (
       endpoint = 'gemini-2.0-flash-001',
       max_iterations = 500,
       data_split_method = 'no_split')
   AS
   SELECT
     input AS prompt, output AS label
   FROM `bqml_tutorial.wiki_auto_style_transfer_train`;

   The query takes a few minutes to complete, after which the gemini_tuned model appears in the bqml_tutorial dataset in the Explorer pane. Because the query uses a CREATE MODEL statement to create a model, there are no query results.

Check tuned model performance

Run the ML.GENERATE_TEXT function to see how the tuned model performs on the evaluation data.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement:

   SELECT ml_generate_text_llm_result, ground_truth
   FROM
     ML.GENERATE_TEXT(
       MODEL `bqml_tutorial.gemini_tuned`,
       (
         SELECT
           input AS prompt, output AS ground_truth
         FROM `bqml_tutorial.wiki_auto_style_transfer_valid`
         LIMIT 10
       ),
       STRUCT(TRUE AS flatten_json_output));

   If you examine the output data, you see that the tuned model produces text that is much more similar in style to the ground truth content.

Evaluate the tuned model

Use the ML.EVALUATE function to see how the tuned model's responses compare to ideal responses.

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, run the following statement:

   SELECT *
   FROM
     ML.EVALUATE(
       MODEL `bqml_tutorial.gemini_tuned`,
       (
         SELECT
           input AS prompt, output AS label
         FROM `bqml_tutorial.wiki_auto_style_transfer_valid`
       ),
       STRUCT('text_generation' AS task_type));

The output looks similar to the following:

   +---------------------+---------------------+-------------------------------------------+--------------------------------------------+
   | bleu4_score         | rouge-l_precision   | rouge-l_recall      | rouge-l_f1_score    | evaluation_status                          |
   +---------------------+---------------------+---------------------+---------------------+--------------------------------------------+
   | 0.416868792119966   | 0.642001000843349   | 0.55910008048151372 | 0.5907226262084847  | {                                          |
   |                     |                     |                     |                     |  "num_successful_rows": 176,               |
   |                     |                     |                     |                     |  "num_total_rows": 176                     |
   |                     |                     |                     |                     | }                                          |
   +---------------------+---------------------+ --------------------+---------------------+--------------------------------------------+
   

You can see that even though the training dataset used only 1,408 examples, there is a marked improvement in performance as indicated by the higher evaluation metrics.

Clean up

Caution: Deleting a project has the following effects:

• Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
• Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as an appspot.com URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

1. In the Google Cloud console, go to the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.