Generate text by using a Gemini model and the AI.GENERATE_TEXT function

This tutorial shows you how to create aremote modelthat's based on thegemini-2.5-flash model,and then how to use that model with theAI.GENERATE_TEXT functionto extract keywords from and perform sentiment analysis on movie reviews fromthebigquery-public-data.imdb.reviews public table.

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.

Costs

In this document, you use the following billable components of Google Cloud:

• BigQuery ML: You incur costs for the data that you process in BigQuery.
• Vertex AI: You incur costs for calls to the Vertex AI service that's represented by the remote model.

To generate a cost estimate based on your projected usage, use thepricing calculator.

For more information about BigQuery pricing, seeBigQuery pricing inthe BigQuery documentation.

For more information about Vertex AI pricing, see theVertex AI pricingpage.

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, and Vertex AI 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

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 a connection

Create aCloud resource connectionand get the connection's service account. Create the connection inthe samelocation as the dataset you created in theprevious step.

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

importgoogle.api_core.exceptionsfromgoogle.cloudimportbigquery_connection_v1client=bigquery_connection_v1.ConnectionServiceClient()defcreate_connection(project_id:str,location:str,connection_id:str,):"""Creates a BigQuery connection to a Cloud Resource.    Cloud Resource connection creates a service account which can then be    granted access to other Google Cloud resources for federated queries.    Args:        project_id: The Google Cloud project ID.        location: The location of the connection (for example, "us-central1").        connection_id: The ID of the connection to create.    """parent=client.common_location_path(project_id,location)connection=bigquery_connection_v1.Connection(friendly_name="Example Connection",description="A sample connection for a Cloud Resource.",cloud_resource=bigquery_connection_v1.CloudResourceProperties(),)try:created_connection=client.create_connection(parent=parent,connection_id=connection_id,connection=connection)print(f"Successfully created connection:{created_connection.name}")print(f"Friendly name:{created_connection.friendly_name}")print(f"Service Account:{created_connection.cloud_resource.service_account_id}")exceptgoogle.api_core.exceptions.AlreadyExists:print(f"Connection with ID '{connection_id}' already exists.")print("Please use a different connection ID.")exceptExceptionase:print(f"An unexpected error occurred while creating the connection:{e}")

const{ConnectionServiceClient}=require('@google-cloud/bigquery-connection').v1;const{status}=require('@grpc/grpc-js');constclient=newConnectionServiceClient();/** * Creates a new BigQuery connection to a Cloud Resource. * * A Cloud Resource connection creates a service account that can be granted access * to other Google Cloud resources. * * @param {string} projectId The Google Cloud project ID. for example, 'example-project-id' * @param {string} location The location of the project to create the connection in. for example, 'us-central1' * @param {string} connectionId The ID of the connection to create. for example, 'example-connection-id' */asyncfunctioncreateConnection(projectId,location,connectionId){constparent=client.locationPath(projectId,location);constconnection={friendlyName:'Example Connection',description:'A sample connection for a Cloud Resource',// The service account for this cloudResource will be created by the API.// Its ID will be available in the response.cloudResource:{},};constrequest={parent,connectionId,connection,};try{const[response]=awaitclient.createConnection(request);console.log(`Successfully created connection:${response.name}`);console.log(`Friendly name:${response.friendlyName}`);console.log(`Service Account:${response.cloudResource.serviceAccountId}`);}catch(err){if(err.code===status.ALREADY_EXISTS){console.log(`Connection '${connectionId}' already exists.`);}else{console.error(`Error creating connection:${err.message}`);}}}

# 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 {}}

Grant permissions to the connection's service account

Grant the connection's service account the Vertex AI User role. You must grant this role in the same project you created or selected in theBefore you begin section. Granting the role in a different project results in the errorbqcx-1234567890-xxxx@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to access resource.

To grant the role, follow these steps:

1. Go to theIAM & Admin page.

   Go to IAM & Admin

2. Clickperson_addGrant Access.

3. In theNew principals field, enter the service account ID that youcopied earlier.

4. In theSelect a role field, chooseVertex AI, and thenselectVertex AI User role.

5. ClickSave.

Create the remote model

Create a remote model that represents a hosted Vertex AImodel:

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

   Go to BigQuery

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

CREATEORREPLACEMODEL`bqml_tutorial.gemini_model`REMOTEWITHCONNECTION`LOCATION.CONNECTION_ID`OPTIONS(ENDPOINT='gemini-2.5-flash');

Replace the following:

• LOCATION: the connection location
• CONNECTION_ID: the ID of yourBigQuery connection

  When youview 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 exampleprojects/myproject/locations/connection_location/connections/myconnection

The query takes several seconds to complete, after which the modelgemini_model appears in thebqml_tutorial dataset. Because the query uses aCREATE MODEL statementto create a model, there are no query results.

Perform keyword extraction

Perform keyword extraction onIMDB movie reviews byusing the remote model and theAI.GENERATE_TEXT function:

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

   Go to BigQuery

2. In the query editor, enter the following statement to perform keywordextraction on five movie reviews:

   SELECTtitle,result,reviewFROMAI.GENERATE_TEXT(MODEL`bqml_tutorial.gemini_model`,(SELECTCONCAT("""Extract a list of only 3 key words from this review.            List only the key words, nothing else. Review:""",review)ASprompt,*FROM`bigquery-public-data.imdb.reviews`LIMIT5),STRUCT(0.2AStemperature,100ASmax_output_tokens));

   The output is similar to the following:

   +--------------+------------------+----------------------------------------+| title        | result           | review                                 |+--------------+------------------+----------------------------------------+| The Guardian | * Costner        | Once again Mr. Costner has dragged out ||              | * Kutcher        | a movie for far longer than necessary. ||              | * Rescue         | Aside from the terrific sea rescue...  ||              |                  |                                        || Trespass     | * Generic        | This is an example of why the majority ||              | * Waste          | of action films are the same. Generic  ||              | * Cinematography | and boring, there's really nothing...  || ...          | ...              | ...                                    |+--------------+------------------+----------------------------------------+

Perform sentiment analysis

Perform sentiment analysis onIMDB movie reviews byusing the remote model and theAI.GENERATE_TEXT function:

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

   Go to BigQuery

2. In the query editor, run the following statement to perform sentimentanalysis on movie reviews:

   SELECTtitle,result,reviewFROMAI.GENERATE_TEXT(MODEL`bqml_tutorial.gemini_model`,(SELECTCONCAT("""Perform sentiment analysis on the following text and             return one the following categories: positive, negative:""",review)ASprompt,*FROM`bigquery-public-data.imdb.reviews`LIMIT5),STRUCT(0.2AStemperature,100ASmax_output_tokens));

   The output is similar to the following:

   +----------+----------+------------------------------------------------+| title    | result   | review                                         |+----------+----------+------------------------------------------------+| Quitting | Positive | This movie is amazing because the fact that... || Trespass | Negative | This is an example of why the majority of ...  || ...      | ...      | ...                                            |+----------+----------+------------------------------------------------+

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.