Analyze images with a Gemini model

This tutorial shows you how to create a BigQuery MLremote modelthat is based on thegemini-2.5-flash model,and then use that model with theAI.GENERATE_TEXT functionfunctions to analyze a set of movie poster images.

This tutorial covers the following tasks:

Creating aBigQuery object tableover image data in a Cloud Storage bucket.
Creating a BigQuery ML remote model that targets theVertex AIgemini-2.5-flash model.
Using the remote model with theAI.GENERATE_TEXT functionto identify the movies associated with a set of movie posters.

The movie poster data is available from the public Cloud Storage bucketgs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters.

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:

Required permissions

Create a dataset:bigquery.datasets.create
Create, delegate, and use a connection:bigquery.connections.*
Set the default connection:bigquery.config.*
Set service account permissions:resourcemanager.projects.getIamPolicy andresourcemanager.projects.setIamPolicy
Create an object table:bigquery.tables.create andbigquery.tables.update
Create a model and run inference:
- bigquery.jobs.create
- bigquery.models.create
- bigquery.models.getData
- bigquery.models.updateData
- bigquery.models.updateMetadata

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 model that is represented by the BigQuery remote model.

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

New Google Cloud users might be eligible for afree trial.

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

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

Before you begin

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
Verify that billing is enabled for your Google Cloud project.
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.

Console

In the Google Cloud console, go to theBigQuery page.
Go to the BigQuery page
In theExplorer pane, click your project name.
ClickView actions > Create dataset
On theCreate dataset page, do the following:
- ForDataset ID, enterbqml_tutorial.
- ForLocation type, selectMulti-region, and then selectUS (multiple regions in United States).
- Leave the remaining default settings as they are, and clickCreate dataset.

bq

To create a new dataset, use thebq mk commandwith the--location flag. For a full list of possible parameters, see thebq mk --dataset commandreference.

Create a dataset namedbqml_tutorial with the data location set toUSand a description ofBigQuery ML tutorial dataset:
```
bq --location=US mk -d \ --description "BigQuery ML tutorial dataset." \ bqml_tutorial
```
Instead of using the--dataset flag, the command uses the-d shortcut.If you omit-d and--dataset, the command defaults to creating adataset.
Confirm that the dataset was created:
```
bqls
```

API

Call thedatasets.insertmethod with a defineddataset resource.

{"datasetReference":{"datasetId":"bqml_tutorial"}}

BigQuery DataFrames

Before trying this sample, follow the BigQuery DataFrames setup instructions in theBigQuery quickstart using BigQuery DataFrames. For more information, see theBigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up ADC for a local development environment.

importgoogle.cloud.bigquerybqclient=google.cloud.bigquery.Client()bqclient.create_dataset("bqml_tutorial",exists_ok=True)

Create the object table

Create an object table over the movie poster images in the publicCloud Storagebucket.The object table makes it possible to analyze the images without moving themfrom Cloud Storage.

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

In the query editor, run the following query to create the object table:

CREATEORREPLACEEXTERNALTABLE`bqml_tutorial.movie_posters`WITHCONNECTIONDEFAULTOPTIONS(object_metadata='SIMPLE',uris=['gs://cloud-samples-data/vertex-ai/dataset-management/datasets/classic-movie-posters/*']);

Create the remote model

Create a remote model that represents a Vertex AIgemini-2.5-flash model:

In the Google Cloud console, go to theBigQuery page.
Go to BigQuery
In the query editor, run the following query to create the remote model:
```
CREATEORREPLACEMODEL`bqml_tutorial.gemini-vision`REMOTEWITHCONNECTIONDEFAULTOPTIONS(ENDPOINT='gemini-2.5-flash');
```
The query takes several seconds to complete, after which thegemini-vision model appears in thebqml_tutorial dataset in theExplorer pane.Because the query uses aCREATE MODEL statement to create a model, thereare no query results.

Analyze the movie posters

Use the remote model to analyze the movie posters and determine what movie eachposter represents, and then write this data to a table.

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

In the query editor, run the following query to analyze the movie posterimages:

CREATEORREPLACETABLE`bqml_tutorial.movie_posters_results`AS(SELECTuri,resultFROMAI.GENERATE_TEXT(MODEL`bqml_tutorial.gemini-vision`,TABLE`bqml_tutorial.movie_posters`,STRUCT(0.2AStemperature,'For the movie represented by this poster, what is the movie title and year of release? Answer in JSON format with two keys: title, year. title should be string, year should be integer.'ASPROMPT)));

In the query editor, run the following statement to view the table data:

SELECT*FROM`bqml_tutorial.movie_posters_results`;

The output is similar to the following:

+--------------------------------------------+----------------------------------+| uri                                        | result                           |+--------------------------------------------+----------------------------------+| gs://cloud-samples-data/vertex-ai/dataset- |json                          || management/datasets/classic-movie-         | {                                || posters/little_annie_rooney.jpg            |  "title": "Little Annie Rooney", ||                                            |  "year": 1912                    ||                                            | }                                ||                                            |                              |+--------------------------------------------+----------------------------------+| gs://cloud-samples-data/vertex-ai/dataset- |json                          || management/datasets/classic-movie-         | {                                || posters/mighty_like_a_mouse.jpg            |  "title": "Mighty Like a Moose", ||                                            |  "year": 1926                    ||                                            | }                                ||                                            |                              |+--------------------------------------------+----------------------------------+| gs://cloud-samples-data/vertex-ai/dataset- |json                          || management/datasets/classic-movie-         | {                                || posters/brown_of_harvard.jpeg              |  "title": "Brown of Harvard",    ||                                            |  "year": 1926                    ||                                            | }                                ||                                            |                              |+--------------------------------------------+----------------------------------+

Format the model output

Format the movie analysis data returned by the model to make the movie title andyear data more readable.

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

In the query editor, run the following query to format the data:

CREATEORREPLACETABLE`bqml_tutorial.movie_posters_results_formatted`AS(SELECTuri,JSON_QUERY(RTRIM(LTRIM(results.result," ```json"),"```"),"$.title")AStitle,JSON_QUERY(RTRIM(LTRIM(results.result," ```json"),"```"),"$.year")ASyearFROM`bqml_tutorial.movie_posters_results`results);