Tune a model using your data

This document shows you how to create a BigQuery ML remote model that references a Vertex AI model, and then configure the model to perform supervised tuning. The Vertex AI model must be one of the following:

gemini-1.5-pro-002
gemini-1.5-flash-002
gemini-1.0-pro-002 (Preview)

After you create the remote model, you use the ML.EVALUATE function to evaluate the model and confirm that the model's performance suits your use case. You can then use the model in conjunction with the ML.GENERATE_TEXT function to analyze text in a BigQuery table.

For more information, see Vertex AI Gemini API model supervised tuning.

Required permissions

To create a connection, you need membership in 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.create
- bigquery.models.create
- bigquery.models.getData
- bigquery.models.updateData
- bigquery.models.updateMetadata
To run inference, you need the following permissions:
- bigquery.tables.getData on the table
- bigquery.models.getData on the model
- bigquery.jobs.create

Before you begin

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
Make sure that billing is enabled for your Google Cloud project.
Enable the BigQuery, BigQuery Connection,Vertex AI, and Compute Engine APIs.
Enable the APIs

Create a connection

Create a Cloud resource connection and get the connection's service account.

Select one of the following options:

Console

Go to the BigQuery page.

Go to BigQuery
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_id parameter overrides the default project.

Replace the following:
- REGION: your connection region
- PROJECT_ID: your Google Cloud project ID
- CONNECTION_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

Use the google_bigquery_connection resource.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

The following example creates a Cloud resource connection named my_cloud_resource_connection in the US region:


# This queries the provider for project information.
data "google_project" "default" {}

# This creates a cloud resource connection in the US region named my_cloud_resource_connection.
# Note: The cloud resource nested object has only one output field - serviceAccountId.
resource "google_bigquery_connection" "default" {
  connection_id = "my_cloud_resource_connection"
  project       = data.google_project.default.project_id
  location      = "US"
  cloud_resource {}
}

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

Prepare Cloud Shell

Launch Cloud Shell.
Set the default Google Cloud project where you want to apply your Terraform configurations.

You only need to run this command once per project, and you can run it in any directory.
```
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
```
Environment variables are overridden if you set explicit values in the Terraform configuration file.

Prepare the directory

Each Terraform configuration file must have its own directory (also called a root module).

In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.
```
mkdir DIRECTORY && cd DIRECTORY && touch main.tf
```
If you are following a tutorial, you can copy the sample code in each section or step.

Copy the sample code into the newly created main.tf.

Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
Review and modify the sample parameters to apply to your environment.
Save your changes.
Initialize Terraform. You only need to do this once per directory.
```
terraform init
```
Optionally, to use the latest Google provider version, include the -upgrade option:
```
terraform init -upgrade
```

Apply the changes

Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:
```
terraform plan
```
Make corrections to the configuration as necessary.
Apply the Terraform configuration by running the following command and entering yes at the prompt:
```
terraform apply
```
Wait until Terraform displays the "Apply complete!" message.
Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

Give the connection's service account access

Give your service account permission to access Vertex AI. Failure to give permission results in an error. Select one of the following options:

Console

Go to the IAM & Admin page.

Go to IAM & Admin
Click Grant Access.
For New principals, enter the service account ID that you copied earlier.
Click Select a role.
In Filter, type Vertex AI Service Agent and 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 number.
MEMBER: 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 OK to assign a service agent role to it.

Create a model with supervised tuning

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

Go to BigQuery
In the query editor, run the following query to create a remote model:
```
CREATE OR REPLACE MODEL
`PROJECT_ID.DATASET_ID.MODEL_NAME`
REMOTE WITH CONNECTION `PROJECT_ID.REGION.CONNECTION_ID`
OPTIONS (
  ENDPOINT = 'ENDPOINT',
  MAX_ITERATIONS = MAX_ITERATIONS,
  LEARNING_RATE_MULTIPLIER = LEARNING_RATE_MULTIPLIER,
  DATA_SPLIT_METHOD = 'DATA_SPLIT_METHOD',
  DATA_SPLIT_EVAL_FRACTION = DATA_SPLIT_EVAL_FRACTION,
  DATA_SPLIT_COL = 'DATA_SPLIT_COL',
  EVALUATION_TASK = 'EVALUATION_TASK',
  PROMPT_COL = 'INPUT_PROMPT_COL',
  INPUT_LABEL_COLS = INPUT_LABEL_COLS)
AS SELECT PROMPT_COLUMN, LABEL_COLUMN
FROM `TABLE_PROJECT_ID.TABLE_DATASET.TABLE_NAME`;
```
Replace the following:
- PROJECT_ID: the project ID of the project in which to create the model.
- DATASET_ID: the ID of the dataset to contain the model. This dataset must be in a supported pipeline job and model upload region.
- MODEL_NAME: the name of the model.
- REGION: the region used by the connection.
- CONNECTION_ID: the ID of your BigQuery connection. This connection must be in the same location as the dataset that you are using.
  When you view the connection details in the Google Cloud console, this is the value in the last section of the fully qualified connection ID that is shown in Connection ID—for example, projects/myproject/locations/connection_location/connections/myconnection.
- ENDPOINT: a STRING value that specifies the name of the model to use.
- MAX_ITERATIONS: an INT64 value that specifies the number of steps to run for supervised tuning. The MAX_ITERATIONS value must be between 1 and ∞.
  Gemini models train using epochs rather than steps, so BigQuery ML converts the MAX_ITERATIONS value to epochs. The default value for MAX_ITERATIONS is the number of rows in the input data, which is equivalent to one epoch. To use multiple epochs, specify a multiple of the number of rows in your training data. For example, if you have 100 rows of input data and you want to use two epochs, specify 200 for the argument value. If you provide a value that isn't a multiple of the number of rows in the input data, BigQuery ML rounds up to the nearest epoch. For example, if you have 100 rows of input data and you specify 101 for the MAX_ITERATIONS value, training is performed with two epochs.
  
  For more information about the parameters used to tune Gemini models, see Create a tuning job.
- DATA_SPLIT_METHOD: a STRING value that specifies the method used to split input data into training and evaluation sets. The valid options are the following:
  - AUTO_SPLIT: BigQuery ML automatically splits the data. The way in which the data is split varies depending on the number of rows in the input table. This is the default value.
  - RANDOM: data is randomized before being split into sets. To customize the data split, you can use this option with the DATA_SPLIT_EVAL_FRACTION option.
  - CUSTOM: data is split using the column provided in the DATA_SPLIT_COL option. The DATA_SPLIT_COL value must be the name of a column of type BOOL. Rows with a value of TRUE or NULL are used as evaluation data, and rows with a value of FALSE are used as training data.
  - SEQ: split data using the column provided in the DATA_SPLIT_COL option. The DATA_SPLIT_COL value must be the name of a column of one of the following types:
    - NUMERIC
    - BIGNUMERIC
    - STRING
    - TIMESTAMP
    The data is sorted smallest to largest based on the specified column.
    
    The first n rows are used as evaluation data, where n is the value specified for DATA_SPLIT_EVAL_FRACTION. The remaining rows are used as training data.
  - NO_SPLIT: no data split; all input data is used as training data.
  For more information about these data split options, see DATA_SPLIT_METHOD.
- DATA_SPLIT_EVAL_FRACTION: a FLOAT64 value that specifies the fraction of the data to use as evaluation data when performing supervised tuning. Must be a value in the range [0, 1.0]. The default value is 0.2.
  Use this option when you specify RANDOM or SEQ as the value for the DATA_SPLIT_METHOD option. To customize the data split, you can use the DATA_SPLIT_METHOD option with the DATA_SPLIT_EVAL_FRACTION option.
- DATA_SPLIT_COL: a STRING value that specifies the name of the column to use to sort input data into the training or evaluation set. Use when you are specifying CUSTOM or SEQ as the value for the DATA_SPLIT_METHOD option.
- EVALUATION_TASK: a STRING value that specifies the type of task that you want to tune the model to perform. The valid options are:
  - TEXT_GENERATION
  - CLASSIFICATION
  - SUMMARIZATION
  - QUESTION_ANSWERING
  - UNSPECIFIED
  The default value is UNSPECIFIED.
- INPUT_PROMPT_COL: a STRING value that contains the name of the prompt column in the training data table to use when performing supervised tuning. The default value is prompt.
- INPUT_LABEL_COLS: an ARRAY<<STRING> value that contains the name of the label column in the training data table to use in supervised tuning. You can only specify one element in the array. The default value is an empty array. This causes label to be the default value of the LABEL_COLUMN argument.
- PROMPT_COLUMN: the column in the training data table that contains the prompt for evaluating the content in the LABEL_COLUMN column. This column must be of STRING type or be cast to STRING. If you specify a value for the INPUT_PROMPT_COL option, you must specify the same value for PROMPT_COLUMN. Otherwise this value must be prompt. If your table does not have a prompt column, use an alias to specify an existing table column. For example, AS SELECT hint AS prompt, label FROM mydataset.mytable.
- LABEL_COLUMN: the column in the training data table that contains the examples to train the model with. This column must be of STRING type or be cast to STRING. If you specify a value for the INPUT_LABEL_COLS option, you must specify the same value for LABEL_COLUMN. Otherwise this value must be label. If your table does not have a label column, use an alias to specify an existing table column. For example, AS SELECT prompt, feature AS label FROM mydataset.mytable.
- TABLE_PROJECT_ID: the project ID of the project that contains the training data table.
- TABLE_DATASET: the name of the dataset that contains the training data table.
- TABLE_NAME: the name of the table that contains the data to use to train the model.

Evaluate the tuned model

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

Go to BigQuery
In the query editor, run the following query to evaluate the tuned model:
```
SELECT
*
FROM
ML.EVALUATE(
  MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`,
  TABLE `TABLE_PROJECT_ID.TABLE_DATASET.TABLE_NAME`,
  STRUCT('TASK_TYPE' AS task_type, TOKENS AS max_output_tokens,
    TEMPERATURE AS temperature, TOP_K AS top_k,
    TOP_P AS top_p));
```
Replace the following:
- PROJECT_ID: the project ID of the project that contains the model.
- DATASET_ID: the ID of the dataset that contains the model.
- MODEL_NAME: the name of the model.
- TABLE_PROJECT_ID: the project ID of the project that contains the evaluation data table.
- TABLE_DATASET: the name of the dataset that contains the evaluation data table.
- TABLE_NAME: the name of the table that contains the evaluation data.
  The table must have a column whose name matches the prompt column name that is provided during model training. You can provide this value by using the prompt_col option during model training. If prompt_col is unspecified, the column named prompt in the training data is used. An error is returned if there is no column named prompt.
  
  The table must have a column whose name matches the label column name that is provided during model training. You can provide this value by using the input_label_cols option during model training. If input_label_cols is unspecified, the column named label in the training data is used. An error is returned if there is no column named label.
- TASK_TYPE: a STRING value that specifies the type of task that you want to evaluate the model for. The valid options are:
  - TEXT_GENERATION
  - CLASSIFICATION
  - SUMMARIZATION
  - QUESTION_ANSWERING
  - UNSPECIFIED
- TOKENS: an INT64 value that sets the maximum number of tokens that can be generated in the response. This value must be in the range [1,1024]. Specify a lower value for shorter responses and a higher value for longer responses. The default is 128.
- TEMPERATURE: a FLOAT64 value in the range [0.0,1.0] that controls the degree of randomness in token selection. The default is 0.
  Lower values for temperature are good for prompts that require a more deterministic and less open-ended or creative response, while higher values for temperature can lead to more diverse or creative results. A value of 0 for temperature is deterministic, meaning that the highest probability response is always selected.
- TOP_K: an INT64 value in the range [1,40] that determines the initial pool of tokens the model considers for selection. Specify a lower value for less random responses and a higher value for more random responses. The default is 40.
- TOP_P: a FLOAT64 value in the range [0.0,1.0] helps determine which tokens from the pool determined by TOP_K are selected. Specify a lower value for less random responses and a higher value for more random responses. The default is 0.95.

Generate text

Generate text with the ML.GENERATE_TEXT function:

Prompt column

Generate text by using a table column to provide the prompt.

SELECT *
FROM ML.GENERATE_TEXT(
  MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`,
  TABLE PROJECT_ID.DATASET_ID.TABLE_NAME,
  STRUCT(TOKENS AS max_output_tokens, TEMPERATURE AS temperature,
  TOP_P AS top_p, FLATTEN_JSON AS flatten_json_output,
  STOP_SEQUENCES AS stop_sequences)
);

Replace the following:

PROJECT_ID: your project ID.
DATASET_ID: the ID of the dataset that contains the model.
MODEL_NAME: the name of the model.
TABLE_NAME: the name of the table that contains the prompt. This table must have a column whose name matches the name of the feature column in the tuned model. The feature column name in the model can be set by using the PROMPT_COL option when creating the model. Otherwise, the feature column name in the model is prompt by default, or you can use an alias to use a differently named column.
TOKENS: an INT64 value that sets the maximum number of tokens that can be generated in the response. This value must be in the range [1,8192]. Specify a lower value for shorter responses and a higher value for longer responses. The default is 128.
TEMPERATURE: a FLOAT64 value in the range [0.0,1.0] that controls the degree of randomness in token selection. The default is 1.0.
Lower values for temperature are good for prompts that require a more deterministic and less open-ended or creative response, while higher values for temperature can lead to more diverse or creative results. A value of 0 for temperature is deterministic, meaning that the highest probability response is always selected.
TOP_P: a FLOAT64 value in the range [0.0,1.0] helps determine the probability of the tokens selected. Specify a lower value for less random responses and a higher value for more random responses. The default is 0.95.
FLATTEN_JSON: a BOOL value that determines whether to return the generated text and the safety attributes in separate columns. The default is FALSE.
STOP_SEQUENCES: an ARRAY<STRING> value that removes the specified strings if they are included in responses from the model. Strings are matched exactly, including capitalization. The default is an empty array.
GROUND_WITH_GOOGLE_SEARCH: a BOOL value that determines whether the Vertex AI model uses Grounding with Google Search when generating responses. Grounding lets the model use additional information from the internet when generating a response, in order to make model responses more specific and factual. When both flatten_json_output and this field are set to True, an additional ml_generate_text_grounding_result column is included in the results, providing the sources that the model used to gather additional information. The default is FALSE.
SAFETY_SETTINGS: an ARRAY<STRUCT<STRING AS category, STRING AS threshold>> value that configures content safety thresholds to filter responses. The first element in the struct specifies a harm category, and the second element in the struct specifies a corresponding blocking threshold. The model filters out content that violate these settings. You can only specify each category once. For example, you can't specify both STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_MEDIUM_AND_ABOVE' AS threshold) and STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_ONLY_HIGH' AS threshold). If there is no safety setting for a given category, the BLOCK_MEDIUM_AND_ABOVE safety setting is used.

Supported categories are as follows:
- HARM_CATEGORY_HATE_SPEECH
- HARM_CATEGORY_DANGEROUS_CONTENT
- HARM_CATEGORY_HARASSMENT
- HARM_CATEGORY_SEXUALLY_EXPLICIT
Supported thresholds are as follows:
- BLOCK_NONE (Restricted)
- BLOCK_LOW_AND_ABOVE
- BLOCK_MEDIUM_AND_ABOVE (Default)
- BLOCK_ONLY_HIGH
- HARM_BLOCK_THRESHOLD_UNSPECIFIED
For more information, refer to the definition of safety category and blocking threshold.

The following example shows a request with these characteristics:

Uses the prompt column of the prompts table for the prompt.
Returns a short and moderately probable response.
Returns the generated text and the safety attributes in separate columns.

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL `mydataset.mymodel`,
    TABLE mydataset.prompts,
    STRUCT(
      0.4 AS temperature, 100 AS max_output_tokens, 0.5 AS top_p,
      TRUE AS flatten_json_output));

Prompt query

Generate text by using a query to provide the prompt.

SELECT *
FROM ML.GENERATE_TEXT(
  MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`,
  (PROMPT_QUERY),
  STRUCT(TOKENS AS max_output_tokens, TEMPERATURE AS temperature,
  TOP_P AS top_p, FLATTEN_JSON AS flatten_json_output,
  STOP_SEQUENCES AS stop_sequences)
);

Replace the following:

PROJECT_ID: your project ID.
DATASET_ID: the ID of the dataset that contains the model.
MODEL_NAME: the name of the model.
PROMPT_QUERY: a query that provides the prompt data.
TOKENS: an INT64 value that sets the maximum number of tokens that can be generated in the response. This value must be in the range [1,8192]. Specify a lower value for shorter responses and a higher value for longer responses. The default is 128.
TEMPERATURE: a FLOAT64 value in the range [0.0,1.0] that controls the degree of randomness in token selection. The default is 1.0.
Lower values for temperature are good for prompts that require a more deterministic and less open-ended or creative response, while higher values for temperature can lead to more diverse or creative results. A value of 0 for temperature is deterministic, meaning that the highest probability response is always selected.
TOP_P: a FLOAT64 value in the range [0.0,1.0] helps determine the probability of the tokens selected. Specify a lower value for less random responses and a higher value for more random responses. The default is 0.95.
FLATTEN_JSON: a BOOL value that determines whether to return the generated text and the safety attributes in separate columns. The default is FALSE.
STOP_SEQUENCES: an ARRAY<STRING> value that removes the specified strings if they are included in responses from the model. Strings are matched exactly, including capitalization. The default is an empty array.
GROUND_WITH_GOOGLE_SEARCH: a BOOL value that determines whether the Vertex AI model uses Grounding with Google Search when generating responses. Grounding lets the model use additional information from the internet when generating a response, in order to make model responses more specific and factual. When both flatten_json_output and this field are set to True, an additional ml_generate_text_grounding_result column is included in the results, providing the sources that the model used to gather additional information. The default is FALSE.
SAFETY_SETTINGS: an ARRAY<STRUCT<STRING AS category, STRING AS threshold>> value that configures content safety thresholds to filter responses. The first element in the struct specifies a harm category, and the second element in the struct specifies a corresponding blocking threshold. The model filters out content that violate these settings. You can only specify each category once. For example, you can't specify both STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_MEDIUM_AND_ABOVE' AS threshold) and STRUCT('HARM_CATEGORY_DANGEROUS_CONTENT' AS category, 'BLOCK_ONLY_HIGH' AS threshold). If there is no safety setting for a given category, the BLOCK_MEDIUM_AND_ABOVE safety setting is used.

Supported categories are as follows:
- HARM_CATEGORY_HATE_SPEECH
- HARM_CATEGORY_DANGEROUS_CONTENT
- HARM_CATEGORY_HARASSMENT
- HARM_CATEGORY_SEXUALLY_EXPLICIT
Supported thresholds are as follows:
- BLOCK_NONE (Restricted)
- BLOCK_LOW_AND_ABOVE
- BLOCK_MEDIUM_AND_ABOVE (Default)
- BLOCK_ONLY_HIGH
- HARM_BLOCK_THRESHOLD_UNSPECIFIED
For more information, refer to the definition of safety category and blocking threshold.

Example 1

The following example shows a request with these characteristics:

Prompts for a summary of the text in the body column of the articles table.
Returns a moderately long and more probable response.
Returns the generated text and the safety attributes in separate columns.

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL `mydataset.mymodel`,
    (
      SELECT CONCAT('Summarize this text', body) AS prompt
      FROM mydataset.articles
    ),
    STRUCT(
      0.2 AS temperature, 650 AS max_output_tokens, 0.2 AS top_p,
      TRUE AS flatten_json_output));

Example 2

The following example shows a request with these characteristics:

Uses a query to create the prompt data by concatenating strings that provide prompt prefixes with table columns.
Returns a short and moderately probable response.
Doesn't return the generated text and the safety attributes in separate columns.

SELECT *
FROM
  ML.GENERATE_TEXT(
    MODEL `mydataset.mytuned_model`,
    (
      SELECT CONCAT(question, 'Text:', description, 'Category') AS prompt
      FROM mydataset.input_table
    ),
    STRUCT(
      0.4 AS temperature, 100 AS max_output_tokens, 0.5 AS top_p,
      FALSE AS flatten_json_output));