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 permissionssection:

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.

   Roles required to select or create a project

   • Select a project : Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
   • Create a project : To create a project, you need the Project Creator ( roles/resourcemanager.projectCreator ), which contains the resourcemanager.projects.create permission. Learn how to grant roles .

   Go to project selector

2. Verify that billing is enabled for your Google Cloud project .

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

   Roles required to enable APIs

   To enable APIs, you need the Service Usage Admin IAM role ( roles/serviceusage.serviceUsageAdmin ), which contains the serviceusage.services.enable permission. Learn how to grant roles .

   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 .

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.

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

  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 BigQuerypage.

   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 Explorerpane. 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 BigQuerypage.

   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 BigQuerypage.

   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 BigQuerypage.

   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 Explorerpane. 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 BigQuerypage.

   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 BigQuerypage.

   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

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.