Annotate images with the ML.ANNOTATE_IMAGE function

This tutorial explains how to use the ML.ANNOTATE_IMAGE function to extract insights from unstructured image data. When you work with large repositories of images, you might need to automatically categorize the files, detect specific objects, or extract image properties to make the dataset searchable. By creating a BigQuery ML remote model that connects to the Cloud Vision API, you can perform these image analysis tasks directly on a BigQuery object table using standard SQL, eliminating the need to move files or build complex data pipelines.

Objectives

• Create a dataset and a resource connection.
• Create an object table for image files stored in Cloud Storage.
• Create a remote model that connects to the Cloud Vision API.
• Annotate images using the ML.ANNOTATE_IMAGE function.

Costs

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

• BigQuery
• Cloud Vision API

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

New Google Cloud users might be eligible for a free trial.

Before you begin

Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.

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 the resourcemanager.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 API, and Cloud Vision API APIs, if any are not already enabled.

Roles required to enable APIs

To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

Enable the APIs

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 the resourcemanager.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 API, and Cloud Vision API APIs, if any are not already enabled.

Roles required to enable APIs

To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

Enable the APIs

Required roles

To get the permissions that you need to complete this tutorial, ask your administrator to grant you the following IAM roles on the project:

• Create and use BigQuery datasets, tables, and models: BigQuery Data Editor (roles/bigquery.dataEditor)
• Create, delegate, and use BigQuery connections: BigQuery Connection Admin (roles/bigquery.connectionAdmin)
• Grant permissions to the connection's service account: Project IAM Admin (roles/resourcemanager.projectIamAdmin)
• Create BigQuery jobs: BigQuery Job User (roles/bigquery.jobUser)

For more information about granting roles, see Manage access to projects, folders, and organizations.

You might also be able to get the required permissions through custom roles or other predefined roles.

Create a dataset

To create a BigQuery dataset, select one of the following options:

Console

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

   Go to BigQuery

2. In the left pane, click Explorer:

   

   If you don't see the left pane, click Expand left pane to open the pane.

3. In Explorer, expand your project, and then click Datasets.

4. On the Datasets page, click Create dataset.

5. In the Create dataset pane, do the following:

   • For Dataset ID, enter bqml_tutorial.

   • For Data location, select US.

   Leave the remaining default settings as they are.

6. Click Create dataset.

bq

To create a new dataset, use the bq mk --dataset command.

1. Create a dataset named bqml_tutorial with the data location set to US:

   bq mk --dataset \
     --location=US \
     --description "BigQuery ML tutorial dataset." \
     bqml_tutorial

2. Confirm that the dataset was created:

   bq ls

API

Call the datasets.insert method with a defined dataset resource:

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

Create an object table

Create an object table named my_object_table that has image contents. The object table makes it possible to analyze the images without moving them from Cloud Storage.

The Cloud Storage bucket used by the object table should be in the same project where you plan to create the model and call the ML.ANNOTATE_IMAGE function. If you want to call the ML.ANNOTATE_IMAGE function in a different project than the one that contains the Cloud Storage bucket used by the object table, you must grant the Storage Admin role at the bucket level.

Create a model

Create a remote model named my_model with a REMOTE_SERVICE_TYPE of CLOUD_AI_VISION_V1:

CREATE OR REPLACE MODEL
`PROJECT_ID.bqml_tutorial.my_model`
REMOTE WITH CONNECTION DEFAULT
OPTIONS (REMOTE_SERVICE_TYPE = 'CLOUD_AI_VISION_V1');

Replace PROJECT_ID with your project ID.

Annotate images

Use the ML.ANNOTATE_IMAGE function with your preferred features to annotate images in the object table.

1. To label the items shown in the images, use the ML.ANNOTATE_IMAGE function with the label_detection feature:

   SELECT *
   FROM ML.ANNOTATE_IMAGE(
   MODEL `PROJECT_ID.bqml_tutorial.my_model`,
   TABLE `PROJECT_ID.bqml_tutorial.my_object_table`,
   STRUCT(['label_detection'] AS vision_features)
   );

   Replace PROJECT_ID with your project ID.

2. To detect any faces shown in the images and return image attributes, like dominant colors, use the ML.ANNOTATE_IMAGE function with the face_detection and image_properties features:

   SELECT *
   FROM ML.ANNOTATE_IMAGE(
   MODEL `PROJECT_ID.bqml_tutorial.my_model`,
   TABLE `PROJECT_ID.bqml_tutorial.my_object_table`,
   STRUCT(['face_detection', 'image_properties'] AS vision_features)
   );

   Replace PROJECT_ID with your project ID.

Clean up

To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, either delete the project that contains the resources, or keep the project and delete the individual resources.

Delete the project

Console

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 an appspot.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 the Manage resources page.

   Go to Manage resources

2. In the project list, select the project that you want to delete, and then click Delete.
3. In the dialog, type the project ID, and then click Shut down to delete the project.

gcloud

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 an appspot.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.

Delete a Google Cloud project:

gcloud projects delete PROJECT_ID

Delete individual resources

If you plan to keep the project you used for this tutorial, you can avoid incurring further charges by deleting the individual resources you created:

1. Delete the dataset: deleting the dataset also removes the remote model and the object table you created inside it.

   • In the Google Cloud console, go to BigQuery Studio.
   • In the Explorer pane, expand your project and select the dataset you created.
   • Click View actions, and then click Delete.
   • In the dialog, type delete, and then click Delete.

2. Delete the connection:

   • In the Explorer pane, expand your project name and click Connections.
   • Click the View actions icon next to the connection you created, and select Delete.
   • In the dialog, click Delete to confirm.

What's next

• For more information about model inference in BigQuery ML, see Model inference overview.
• For more information about using Cloud AI APIs to perform AI tasks, see AI application overview.
• For information about supported SQL statements and functions for generative AI models, see End-to-end user journeys for generative AI models.
• Try the Unstructured data analytics with BigQuery ML and Gemini Enterprise Agent Platform pre-trained models notebook.