Use tuning and evaluation to improve model performance

This document shows you how to create a BigQuery MLremote modelthat references aVertex AIgemini-2.0-flash-001 model.You then usesupervised tuningto tune the model with new training data, followed by evaluating the modelwith theML.EVALUATE function.

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

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

In this tutorial, the goal is to have the model generate text whose style andcontent 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 inthis document. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined 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 role (roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.create permission.Learn how to grant roles.
   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. 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 theserviceusage.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 thegemini-2.0-flash-001 model.

To generate a cost estimate based on your projected usage, use thepricing 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"}}

importgoogle.cloud.bigquerybqclient=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 publictask955_wiki_auto_style_transfer dataset from Hugging Face.

1. Open theCloud Shell.

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

   python3-mpipinstallpandaspyarrowfsspechuggingface_hubpython3-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);"bqrm-tbqml_tutorial.wiki_auto_style_transfer_trainbqrm-tbqml_tutorial.wiki_auto_style_transfer_validbqload--source_format=NEWLINE_DELIMITED_JSONbqml_tutorial.wiki_auto_style_transfer_trainwiki_auto_style_transfer_train.jsonlinput:STRING,output:STRINGbqload--source_format=NEWLINE_DELIMITED_JSONbqml_tutorial.wiki_auto_style_transfer_validwiki_auto_style_transfer_valid.jsonlinput:STRING,output:STRING

Create a baseline model

Create aremote modelover the Vertex AIgemini-2.0-flash-001 model.

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

   Go to BigQuery

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

   CREATEORREPLACEMODEL`bqml_tutorial.gemini_baseline`REMOTEWITHCONNECTIONDEFAULTOPTIONS(ENDPOINT='gemini-2.0-flash-001');

   The query takes several seconds to complete, after which thegemini_baseline model appears in thebqml_tutorial dataset in theExplorer pane. Because the query uses aCREATE MODEL statement tocreate a model, there are no query results.

Check baseline model performance

Run theAI.GENERATE_TEXT functionwith the remote model to see how it performs on the evaluation data without anytuning.

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

   Go to BigQuery

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

   SELECTresult,ground_truthFROMAI.GENERATE_TEXT(MODEL`bqml_tutorial.gemini_baseline`,(SELECTinputASprompt,outputASground_truthFROM`bqml_tutorial.wiki_auto_style_transfer_valid`LIMIT10));

   If you examine the output data and compare theresultandground_truth values, you see that while the baseline model generatestext that accurately reflects the facts provided in the groundtruth content, the style of the text is fairly different.

Evaluate the baseline model

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

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

   Go to BigQuery

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

   SELECT*FROMML.EVALUATE(MODEL`bqml_tutorial.gemini_baseline`,(SELECTinputASinput_text,outputASoutput_textFROM`bqml_tutorial.wiki_auto_style_transfer_valid`),STRUCT('text_generation'AStask_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 ofthe 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 canimprove model performance for this use case.

Create a tuned model

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

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

   Go to BigQuery

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

   CREATEORREPLACEMODEL`bqml_tutorial.gemini_tuned`REMOTEWITHCONNECTIONDEFAULTOPTIONS(endpoint='gemini-2.0-flash-001',max_iterations=500,data_split_method='no_split')ASSELECTinputASprompt,outputASlabelFROM`bqml_tutorial.wiki_auto_style_transfer_train`;

   The query takes a few minutes to complete, after which thegemini_tuned model appears in thebqml_tutorial dataset in theExplorer pane. Because the query uses aCREATE MODEL statement to create a model, there are no query results.

Check tuned model performance

Run theAI.GENERATE_TEXT function to see how the tuned model performs on theevaluation data.

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

   Go to BigQuery

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

   SELECTresult,ground_truthFROMAI.GENERATE_TEXT(MODEL`bqml_tutorial.gemini_tuned`,(SELECTinputASprompt,outputASground_truthFROM`bqml_tutorial.wiki_auto_style_transfer_valid`LIMIT10));

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

Evaluate the tuned model

Use theML.EVALUATE function to see how the tuned model's responses compareto ideal responses.

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

   Go to BigQuery

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

   SELECT*FROMML.EVALUATE(MODEL`bqml_tutorial.gemini_tuned`,(SELECTinputASprompt,outputASlabelFROM`bqml_tutorial.wiki_auto_style_transfer_valid`),STRUCT('text_generation'AStask_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 higherevaluation 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 anappspot.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 theManage resources page.

   Go to Manage resources

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