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-1.0-pro-002 model.
You then use
supervised tuning
to tune the model with new training data, followed by evaluating the model
with the
ML.EVALUATE function.
This 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.
Required permissions
To create a connection, you need the following Identity and Access Management (IAM) role:
roles/bigquery.connectionAdmin
To grant permissions to the connection's service account, you need the following permission:
resourcemanager.projects.setIamPolicy
To create the model using BigQuery ML, you need the following IAM permissions:
bigquery.jobs.createbigquery.models.createbigquery.models.getDatabigquery.models.updateDatabigquery.models.updateMetadata
To run inference, you need the following permissions:
bigquery.tables.getDataon the tablebigquery.models.getDataon the modelbigquery.jobs.create
Before you begin
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection, Vertex AI, and Compute Engine 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-pro-002model.
To generate a cost estimate based on your projected usage,
use the pricing calculator.
For more information, see the following resources:
Create a dataset
Create a BigQuery dataset to store your ML model:
In the Google Cloud console, go to the BigQuery page.
In the Explorer pane, click your project name.
Click View actions > Create dataset.
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).
The public datasets are stored in the
USmulti-region. For simplicity, store your dataset in the same location.Leave the remaining default settings as they are, and click Create dataset.
Create a connection
Create a Cloud resource connection and get the connection's service account ID. Create the connection in the same location as the dataset that you created in the previous step.
Select one of the following options:
Console
Go to the BigQuery page.
To create a connection, click Add, and then click Connections to external data sources.
In the Connection type list, select Vertex AI remote models, remote functions and BigLake (Cloud Resource).
In the Connection ID field, enter a name for your connection.
Click Create connection.
Click Go to connection.
In the Connection info pane, copy the service account ID for use in a later step.
bq
In a command-line environment, create a connection:
bq mk --connection --location=REGION --project_id=PROJECT_ID \ --connection_type=CLOUD_RESOURCE CONNECTION_ID
The
--project_idparameter overrides the default project.Replace the following:
REGION: your connection regionPROJECT_ID: your Google Cloud project IDCONNECTION_ID: an ID for your connection
When you create a connection resource, BigQuery creates a unique system service account and associates it with the connection.
Troubleshooting: If you get the following connection error, update the Google Cloud SDK:
Flags parsing error: flag --connection_type=CLOUD_RESOURCE: value should be one of...
Retrieve and copy the service account ID for use in a later step:
bq show --connection PROJECT_ID.REGION.CONNECTION_ID
The output is similar to the following:
name properties 1234.REGION.CONNECTION_ID {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}
Terraform
Append the following section into your main.tf file.
## This creates a cloud resource connection. ## Note: The cloud resource nested object has only one output only field - serviceAccountId. resource "google_bigquery_connection" "connection" { connection_id = "CONNECTION_ID" project = "PROJECT_ID" location = "REGION" cloud_resource {} }
CONNECTION_ID: an ID for your connectionPROJECT_ID: your Google Cloud project IDREGION: your connection region
Give the connection's service account access
Grant your service account the Vertex AI Service Agent role so that the service account can access Vertex AI. Failure to grant this role results in an error. Select one of the following options:
Console
Go to the IAM & Admin page.
Click Grant access.
The Add principals dialog opens.
In the New principals field, enter the service account ID that you copied earlier.
Click Select a role.
In Filter, type
Vertex AI Service Agentand then select that role.Click Save.
gcloud
Use the
gcloud projects add-iam-policy-binding command:
gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/aiplatform.serviceAgent' --condition=None
Replace the following:
PROJECT_NUMBER: your project numberMEMBER: the service account ID that you copied earlier
The service account associated with your connection is an instance of the BigQuery Connection Delegation Service Agent, so it is acceptable to assign a service agent role to it.
Give the Compute Engine default service account access
When you enable the Compute Engine API, the Compute Engine default service account is automatically granted the Editor role on the project, unless you have disabled this behavior for your project. If that's the case, then you must re-grant the Editor role to the Compute Engine default service account access so that it has sufficient permissions to create and tune a remote model.
Console
Go to the IAM & Admin page.
Click Grant Access.
For New principals, enter the service account ID, which is
PROJECT_NUMBER-compute@developer.gserviceaccount.com.Click Select a role.
In Select a role, choose Basic, and then select Editor.
Click Save.
gcloud
Use the
gcloud projects add-iam-policy-binding command:
gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/editor' --condition=None
Replace the following:
PROJECT_NUMBER: your project number.MEMBER: the service account ID, which isPROJECT_NUMBER-compute@developer.gserviceaccount.com.
Create a tuning service agent
Create an instance of the Vertex AI Secure Fine Tuning Service Agent:
Use the
gcloud beta services identity createcommand to create the tuning service agent:gcloud beta services identity create --service=aiplatform.googleapis.com --project=PROJECT_NUMBER
Replace
PROJECT_NUMBERwith your project number.Use the
gcloud projects add-iam-policy-bindingcommand to grant the tuning service agent the Vertex AI Tuning Service Agent (roles/aiplatform.tuningServiceAgent) role:gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:service-PROJECT_NUMBER@gcp-sa-vertex-tune.iam.gserviceaccount.com' --role='roles/aiplatform.tuningServiceAgent'
Replace
PROJECT_NUMBERwith your project number.
Create test tables
Create tables of training and evaluation data based on the public
MTSamples dataset of transcribed medical reports.
This dataset has an input_text column that contains the medical transcript
and a output_text column that contains the label that best describes the
category of the transcript, for example Allergy/Immunology, Dentistry,
or Cardiovascular/Pulmonary. Also, create a table that contains the prompt
data for medical transcript classification.
You import the medical transcription data from a public Cloud Storage bucket.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement to create a table of evaluation data:
LOAD DATA INTO `bqml_tutorial.medical_transcript_eval` FROM FILES(format='NEWLINE_DELIMITED_JSON', uris = ['gs://cloud-samples-data/vertex-ai/model-evaluation/peft_eval_sample.jsonl']);
In the query editor, run the following statement to create a table of training data:
LOAD DATA INTO `bqml_tutorial.medical_transcript_train` FROM FILES(format='NEWLINE_DELIMITED_JSON', uris = ['gs://cloud-samples-data/vertex-ai/model-evaluation/peft_train_sample.jsonl']);
In the query editor, run the following statement to create a prompt table:
CREATE OR REPLACE TABLE `bqml_tutorial.transcript_classification` AS (SELECT 'Please assign a label for the given medical transcript from among these labels [Allergy / Immunology, Autopsy, Bariatrics, Cardiovascular / Pulmonary, Chiropractic, Consult - History and Phy., Cosmetic / Plastic Surgery, Dentistry, Dermatology, Diets and Nutritions, Discharge Summary, ENT - Otolaryngology, Emergency Room Reports, Endocrinology, Gastroenterology, General Medicine, Hematology - Oncology, Hospice - Palliative Care, IME-QME-Work Comp etc., Lab Medicine - Pathology, Letters, Nephrology, Neurology, Neurosurgery, Obstetrics / Gynecology, Office Notes, Ophthalmology, Orthopedic, Pain Management, Pediatrics - Neonatal, Physical Medicine - Rehab, Podiatry, Psychiatry / Psychology, Radiology, Rheumatology, SOAP / Chart / Progress Notes, Sleep Medicine, Speech - Language, Surgery, Urology].' AS prompt);
Create a baseline model
Create a
remote model
over the Vertex AI gemini-1.0-pro-002 model that you can use to classify
medical transcripts.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement to create a remote model:
CREATE OR REPLACE MODEL `bqml_tutorial.gemini-baseline` REMOTE WITH CONNECTION `LOCATION.CONNECTION_ID` OPTIONS (ENDPOINT ='gemini-1.0-pro-002');
Replace the following:
LOCATION: the connection location.CONNECTION_ID: the ID of your BigQuery connection.When you view the connection details in the Google Cloud console, the
CONNECTION_IDis the value in the last section of the fully qualified connection ID that is shown in Connection ID, for exampleprojects/myproject/locations/connection_location/connections/myconnection.
The query takes several seconds to complete, after which the
gemini-baselinemodel appears in thebqml_tutorialdataset in the Explorer pane. Because the query uses aCREATE MODELstatement 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.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
SELECT * FROM ML.GENERATE_TEXT( MODEL `bqml_tutorial.gemini-baseline`, ( SELECT CONCAT( (SELECT prompt from `bqml_tutorial.transcript_classification`), ' ', input_text) AS prompt, output_text AS label FROM `bqml_tutorial.medical_transcript_eval` ), STRUCT(TRUE AS flatten_json_output)) ORDER BY ml_generate_text_llm_result;
If you examine the output data and compare the
ml_generate_text_llm_resultandlabelvalues, you see that while the baseline model predicts many transcript classifications correctly, it classifies some transcripts incorrectly. The following is a representative example of incorrect output. In this example, the correct classification isAutopsy, notPulmonary Embolism.+------------------------------------+---------------------------------+-------------------------------------------------------------------------+--------------+ | ml_generate_text_llm_result | ml_generate_text_rai_result | ml_generate_text_status | prompt | label | +------------------------------------+---------------------------------+-------------------------------------------------------------------------+--------------+ | ## Label:Pulmonary Embolism | [{"category":1,"probability":1 | | Please assign a label for the given medical | Autopsy | | | ,"probability_score":0.19433 | | transcript from among these labels [Allergy / | | | | "594,"severity":2,... | | Immunology, Autopsy, Bariatrics, | | | | | | Cardiovascular / Pulmonary, Chiropractic, | | | | | | Consult - History and Phy., Cosmetic / | | | | | | Plastic Surgery, Dentistry, Dermatology, | | | | | | Diets and Nutritions, Discharge Summary, ENT | | | | | | - Otolaryngology, Emergency Room Reports, | | | | | | Endocrinology, Gastroenterology, General | | | | | | Medicine, Hematology - Oncology, Hospice - | | | | | | Palliative Care, IME-QME-Work Comp etc., | | | | | | Lab Medicine - Pathology, Letters, | | | | | | Nephrology, Neurology, Neurosurgery, | | | | | | Obstetrics / Gynecology, Office Notes, | | | | | | Ophthalmology, Orthopedic, Pain Management, | | | | | | Pediatrics - Neonatal, Physical Medicine - | | | | | | Rehab, Podiatry, Psychiatry / Psychology, | | | | | | Radiology, Rheumatology, SOAP / Chart / | | | | | | Progress Notes, Sleep Medicine, Speech - | | | | | | Language, Surgery, Urology]. | | | | | | TRANSCRIPT: | | | | | | SUMMARY OF CLINICAL HISTORY:, | | | | | | OF The patient was a 35-year-old | | | | | | African-American male with no | | | | | | significant past medical history | | | | | | a who called EMS with shortness of breath | | | | | | had and chest pain. Upon EMS | | | | | | arrival, patient was... | | +------------------------------------+---------------------------------+-------------------------------------------------------------------------+--------------+
Evaluate the baseline model
To perform a more detailed evaluation of the model performance, use the
ML.EVALUATE function.
This function computes model metrics, such as
precision,
recall, and
F1 score, in order to see how the
model's responses compare to ideal responses.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
SELECT * FROM ML.EVALUATE( MODEL `bqml_tutorial.gemini-baseline`, ( SELECT CONCAT( (SELECT prompt FROM `bqml_tutorial.transcript_classification`), ' ', input_text) AS input_text, output_text FROM `bqml_tutorial.medical_transcript_eval` ), STRUCT('classification' AS task_type)) ORDER BY label;
The output looks similar to the following:
+------------------------------+----------------------------------+-------------------------------------------------------------------------+
| precision | recall | f1_score | label | evaluation_status |
+---------------------+---------------------+---------------------+----------------------------+--------------------------------------------+
| 0.0 | 0.0 | 0.0 | Allergy / Immunology | { |
| | | | | "num_successful_rows": 474, |
| | | | | "num_total_rows": 492 |
| | | | | } |
+---------------------+---------------------+ --------------------+----------------------------+--------------------------------------------+
| 0.0 | 0.0 | 0.0 | Autopsy | { |
| | | | | "num_successful_rows": 474, |
| | | | | "num_total_rows": 492 |
| | | | | } |
+---------------------+---------------------+--------------- -----+----------------------------+--------------------------------------------+
| 0.0 | 0.0 | 0.0 | Bariatrics | { |
| | | | | "num_successful_rows": 474, |
| | | | | "num_total_rows": 492 |
| | | | | } |
+---------------------+---------------------+---------------------+----------------------------+--------------------------------------------+
You can see that the baseline model doesn't perform well for this data, based
on the low f1 scores. 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.
This query might take a couple of hours to complete.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement to create a remote model:
CREATE OR REPLACE MODEL `bqml_tutorial.gemini-tuned` REMOTE WITH CONNECTION `LOCATION.CONNECTION_ID` OPTIONS ( endpoint = 'gemini-1.0-pro-002', max_iterations = 300, data_split_method = 'no_split') AS SELECT CONCAT( (SELECT prompt FROM `bqml_tutorial.transcript_classification`), ' ', input_text) AS prompt, output_text AS label FROM `bqml_tutorial.medical_transcript_train`;
Replace the following:
LOCATION: the connection location.CONNECTION_ID: the ID of your BigQuery connection.When you view the connection details in the Google Cloud console, the
CONNECTION_IDis the value in the last section of the fully qualified connection ID that is shown in Connection ID, for exampleprojects/myproject/locations/connection_location/connections/myconnection.
The query might take several hours to complete, after which the
gemini-tunedmodel appears in thebqml_tutorialdataset in the Explorer pane. Because the query uses aCREATE MODELstatement 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.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
SELECT * FROM ML.GENERATE_TEXT( MODEL `bqml_tutorial.gemini-tuned`, ( SELECT CONCAT( (SELECT prompt from `bqml_tutorial.transcript_classification`), ' ', input_text) AS prompt, output_text AS label FROM `bqml_tutorial.medical_transcript_eval` ), STRUCT(TRUE AS flatten_json_output)) ORDER BY ml_generate_text_llm_result;
If you examine the output data, you see that the tuned model classifies more transcripts correctly. The example you looked at earlier is now correctly classified as
Cardiovascular/ Pulmonary.+-----------------------------+---------------------------------+-------------------------------------------------------------------------+----------------------------+ | ml_generate_text_llm_result | ml_generate_text_rai_result | ml_generate_text_status | prompt | label | +-----------------------------+---------------------------------+-------------------------------------------------------------------------+----------------------------+ | Autopsy | [{"category":1,"probability":1 | | Please assign a label for the given medical | Autopsy | | | ,"probability_score":0.19335938 | | transcript from among these labels [Allergy / | | | | ,"severity":2,"severity_scor... | | Immunology, Autopsy, Bariatrics, | | | | | | Cardiovascular / Pulmonary, Chiropractic, | | | | | | Consult - History and Phy., Cosmetic / | | | | | | Plastic Surgery, Dentistry, Dermatology, | | | | | | Diets and Nutritions, Discharge Summary, ENT | | | | | | - Otolaryngology, Emergency Room Reports, | | | | | | Endocrinology, Gastroenterology, General | | | | | | Medicine, Hematology - Oncology, Hospice - | | | | | | Palliative Care, IME-QME-Work Comp etc., | | | | | | Lab Medicine - Pathology, Letters, | | | | | | Nephrology, Neurology, Neurosurgery, | | | | | | Obstetrics / Gynecology, Office Notes, | | | | | | Ophthalmology, Orthopedic, Pain Management, | | | | | | Pediatrics - Neonatal, Physical Medicine - | | | | | | Rehab, Podiatry, Psychiatry / Psychology, | | | | | | Radiology, Rheumatology, SOAP / Chart / | | | | | | Progress Notes, Sleep Medicine, Speech - | | | | | | Language, Surgery, Urology]. | | | | | | TRANSCRIPT: | | | | | | SUMMARY OF CLINICAL HISTORY:, | | | | | | The patient was a 35-year-old | | | | | | African-American male with no | | | | | | significant past medical history | | | | | | a who called EMS with shortness of breath | | | | | | had and chest pain. Upon EMS | | | | | | arrival, patient was... | | +-----------------------------+---------------------------------+-------------------------------------------------------------------------+----------------------------+
Evaluate the tuned model
Use the ML.EVALUATE function to see how the tuned model's responses compare
to ideal responses.
In the Google Cloud console, go to the BigQuery page.
In the query editor, run the following statement:
SELECT * FROM ML.EVALUATE( MODEL `bqml_tutorial.gemini-tuned`, ( SELECT CONCAT( (SELECT prompt from `bqml_tutorial.transcript_classification`), ' ', input_text) AS prompt, output_text AS label FROM `bqml_tutorial.medical_transcript_eval` ), STRUCT('classification' AS task_type)) ORDER BY label;
The output looks similar to the following:
+------------------------------+----------------------------------+-------------------------------------------------------------------------+
| precision | recall | f1_score | label | evaluation_status |
+---------------------+---------------------+---------------------+----------------------------+--------------------------------------------+
| 0.5 | 0.66666666666666663 | 0.5714285714285714 | Allergy/Immunology | { |
| | | | | "num_successful_rows": 439, |
| | | | | "num_total_rows": 492 |
| | | | | } |
+---------------------+---------------------+ --------------------+----------------------------+--------------------------------------------+
| 1.0 | 1.0 | 1.0 | Autopsy | { |
| | | | | "num_successful_rows": 439, |
| | | | | "num_total_rows": 492 |
| | | | | } |
+---------------------+---------------------+--------------- -----+----------------------------+--------------------------------------------+
| 0.5 | 1.0 | 0.66666666666666663 | Bariatrics | { |
| | | | | "num_successful_rows": 439, |
| | | | | "num_total_rows": 492 |
| | | | | } |
+---------------------+---------------------+---------------------+----------------------------+--------------------------------------------+
You can see that even though the training dataset used only 519 examples,
there is a marked improvement in performance as indicated by the higher f1
scores.
Clean up
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.