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 Cloud de Confiance by S3NS:

Before you begin

  1. In the Cloud de Confiance console, on the project selector page, select or create a Cloud de Confiance 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.

    Go to project selector

  2. Verify that billing is enabled for your Cloud de Confiance project.

  3. 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:

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 Cloud de Confiance 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 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

  1. In the Cloud de Confiance 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

    Delete a Cloud de Confiance 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 Cloud de Confiance 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