Tune a model using your data (original) (raw)

Skip to main content

This document shows you how to create a BigQuery MLremote modelthat references a Gemini Enterprise Agent Platform model, and then configure the model to perform supervised tuning. The Agent Platform model must be one of the following:

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

For more information, seeVertex AI Gemini API model supervised tuning.

Required roles

To create and evaluate a tuned model, you need the following Identity and Access Management (IAM) roles:

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

Required permissions

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.
      Go to project selector
  2. Verify that billing is enabled for your Google Cloud project.
  3. Enable the BigQuery, BigQuery Connection,Agent Platform API, 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

Create a dataset

Create a BigQuery dataset to contain your resources:

Console

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the left pane, click Explorer:
    Highlighted button for the Explorer pane.
    If you don't see the left pane, click Expand left pane to open the pane.
  3. In the Explorer pane, click your project name.
  4. Click View actions > Create dataset.
  5. On the Create dataset page, do the following:
    1. For Dataset ID, type a name for the dataset.
    2. For Location type, select Region or Multi-region.
      • If you selected Region, then select a location from theRegion list.
      • If you selected Multi-region, then select US or Europefrom the Multi-region list.
    3. Click Create dataset.

bq

  1. To create a new dataset, use thebq mk command with the --location flag:
    bq --location=LOCATION mk -d DATASET_ID
    Replace the following:
    • LOCATION: the dataset's location.
    • DATASET_ID is the ID of the dataset that you're creating.
  2. Confirm that the dataset was created:
    bq ls

Create a connection

Create aCloud resource connectionand get the connection's service account. Create the connection in the same location as the dataset you created in the previous step.

You can skip this step if you either have a default connection configured, or you have the BigQuery Admin role.

Select one of the following options:

Console

  1. Go to the BigQuery page.
    Go to BigQuery
  2. In the left pane, click Explorer:
    Highlighted button for the Explorer pane.
    If you don't see the left pane, click Expand left pane to open the pane.
  3. In the Explorer pane, expand your project name, and then clickConnections.
  4. On the Connections page, click Create connection.
  5. For Connection type, choose Vertex AI remote models, remote functions, BigLake and Spanner (Cloud Resource).
  6. In the Connection ID field, enter a name for your connection.
  7. For Location type, select a location for your connection. The connection should be colocated with your other resources such as datasets.
  8. Click Create connection.
  9. Click Go to connection.
  10. In the Connection info pane, copy the service account ID for use in a later step.

SQL

Use the CREATE CONNECTION statement:

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the query editor, enter the following statement:
    CREATE CONNECTION [IF NOT EXISTS] CONNECTION_NAME
    OPTIONS (
    connection_type = "CLOUD_RESOURCE",
    friendly_name = "FRIENDLY_NAME",
    description = "DESCRIPTION"
    );
    Replace the following:
    • CONNECTION_NAME: the name of the connection in either thePROJECT_ID.LOCATION.CONNECTION_ID,LOCATION.CONNECTION_ID, orCONNECTION_ID format. If the project or location are omitted, then they are inferred from the project and location where the statement is run.
    • FRIENDLY_NAME (optional): a descriptive name for the connection.
    • DESCRIPTION (optional): a description of the connection.
  3. Click Run.

For more information about how to run queries, see Run an interactive query.

bq

  1. 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: yourconnection 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...
  2. 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"}

Python

Before trying this sample, follow the Python setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Python API reference documentation.

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

Node.js

Before trying this sample, follow the Node.js setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQuery Node.js API reference documentation.

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

Terraform

Use thegoogle_bigquery_connectionresource.

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

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

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

Prepare Cloud Shell

  1. Launch Cloud Shell.
  2. 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).

  1. 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
  2. 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.
  3. Review and modify the sample parameters to apply to your environment.
  4. Save your changes.
  5. 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

  1. 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.
  2. 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.
  3. 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

Grant the connection's service account the Vertex AI Service Agent role.

If you plan to specify the endpoint as a URL when you create the remote model, for example endpoint = 'https://us-central1-aiplatform.googleapis.com/v1/projects/myproject/locations/us-central1/publishers/google/models/gemini-2.0-flash', grant this role in the same project you specify in the URL.

If you plan to specify the endpoint by using the model name when you create the remote model, for example endpoint = 'gemini-2.0-flash', grant this role in the same project where you plan to create the remote model.

Granting the role in a different project results in the errorbqcx-1234567890-wxyz@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access resource.

To grant the role, follow these steps:

Console

  1. Go to the IAM & Admin page.
    Go to IAM & Admin
  2. Click Grant Access.
  3. For New principals, enter the service account ID that you copied earlier.
  4. Click Select a role.
  5. In Filter, type Vertex AI Service Agent and then select that role.
  6. Click Save.

gcloud

Use thegcloud 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:

The service account associated with your connection is an instance of theBigQuery Connection Delegation Service Agent, so it is OK to assign a service agent role to it.

Create a model with supervised tuning

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the query editor, run the following query to create aremote model:
    CREATE OR REPLACE MODEL
    PROJECT_ID.DATASET_ID.MODEL_NAME
    REMOTE WITH CONNECTION {DEFAULT | 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 asupported Gemini Enterprise Agent Platform 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 samelocation 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 inConnection 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 theMAX_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, seeCreate 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 theDATA_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 typeBOOL. Rows with a value of TRUE orNULL are used as evaluation data, and rows with a value ofFALSE 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 forDATA_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, seeDATA_SPLIT_METHOD.

    • DATA_SPLIT_EVAL_FRACTION: aFLOAT64 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 is0.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 theDATA_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 isUNSPECIFIED.

    • INPUT_PROMPT_COL: aSTRING 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: anARRAY<<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 ofSTRING 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 beprompt. 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 ofSTRING 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 belabel. 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

  1. In the Google Cloud console, go to the BigQuery page.
    Go to BigQuery
  2. In the query editor, run the following query to evaluate the tuned model:
    SELECT

Generate text

Generate text with theAI.GENERATE_TEXT function:

Prompt column

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

SELECT * FROM AI.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, STOP_SEQUENCES AS stop_sequences) );

Replace the following:

The default value is UNSPECIFIED.
For more information, seeUse Agent Platform Provisioned Throughput.

The following example shows a request with these characteristics:

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

Prompt query

Generate text by using a query to provide the prompt.

SELECT * FROM AI.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, STOP_SEQUENCES AS stop_sequences) );

Replace the following:

The default value is UNSPECIFIED.
For more information, seeUse Agent Platform Provisioned Throughput.

Example 1

The following example shows a request with these characteristics:

SELECT * FROM AI.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));

Example 2

The following example shows a request with these characteristics:

SELECT * FROM AI.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));

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-06-15 UTC.