Annotate object table images with the ML.ANNOTATE_IMAGE function

This document describes how to use the ML.ANNOTATE_IMAGE function with a remote model to annotate images in an object table.

For information about model inference in BigQuery ML, see Model inference overview.

For information about supported model types of each SQL statement and function, and all supported SQL statements and functions for each model type, read End-to-end user journey for each model.

Required permissions

• To create a connection, you need membership in the following role:

  • roles/bigquery.connectionAdmin

• To create the model using BigQuery ML, you need the following permissions:

  • bigquery.jobs.create
  • bigquery.models.create
  • bigquery.models.getData
  • bigquery.models.updateData
  • bigquery.models.updateMetadata

• To run inference, you need the following permissions:

  • bigquery.tables.getData on the object table
  • bigquery.models.getData on the model
  • bigquery.jobs.create

Before you begin

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

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   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

3. Make sure that billing is enabled for your Google Cloud project.

4. Enable the BigQuery, BigQuery Connection API, and Cloud Vision API APIs.

   Enable the APIs

5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   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

6. Make sure that billing is enabled for your Google Cloud project.

7. Enable the BigQuery, BigQuery Connection API, and Cloud Vision API APIs.

   Enable the APIs

Create a connection

Create a cloud resource connection and get the connection's service account.

Select one of the following options:

Console

1. Go to the BigQuery page.

   Go to BigQuery

2. To create a connection, click Add data, and then click Connections to external data sources.

3. In the Connection type list, select BigLake and remote functions (Cloud Resource).

4. In the Connection ID field, enter a name for your connection.

5. Click Create connection.

6. Click Go to connection.

7. In the Connection info pane, copy the service account ID for use in a later step.

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: your connection 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.

2. Retrieve and copy the service account ID because you need it 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": "[e-mail]"}
   

Terraform

Append the following section into your main.tf file.

 ## This creates a cloud resource connection.
 ## Note: The cloud resource nested object has only one output only field - serviceAccountId.
 resource "google_bigquery_connection" "connection" {
    connection_id = "CONNECTION_ID"
    project = "PROJECT_ID"
    location = "REGION"
    cloud_resource {}
}        

Replace the following:

• CONNECTION_ID: an ID for your connection
• PROJECT_ID: your Google Cloud project ID
• REGION: your connection region

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

Grant access to the service account

Select one of the following options:

Console

1. Go to the IAM & Admin page.

   Go to IAM & Admin

2. Click Add.

   The Add principals dialog opens.

3. In the New principals field, enter the service account ID that you copied earlier.

4. In the Select a role field, select Service Usage, and then select Service Usage Consumer.

5. Click Add another role.

6. In the Select a role field, select BigQuery, and then select BigQuery Connection User.

7. Click Save.

gcloud

Use the gcloud projects add-iam-policy-binding command:

gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/serviceusage.serviceUsageConsumer' --condition=None
gcloud projects add-iam-policy-binding 'PROJECT_NUMBER' --member='serviceAccount:MEMBER' --role='roles/bigquery.connectionUser' --condition=None

Replace the following:

• PROJECT_NUMBER: your project number.
• MEMBER: the service account ID that you copied earlier.

Failure to grant the permission results in an error.

Create a model

Create a remote model with a REMOTE_SERVICE_TYPE of CLOUD_AI_VISION_V1:

CREATE OR REPLACE MODEL
PROJECT_ID.DATASET_ID.MODEL_NAME
REMOTE WITH CONNECTION PROJECT_ID.REGION.CONNECTION_NAME
OPTIONS (REMOTE_SERVICE_TYPE = 'CLOUD_AI_VISION_V1');

Replace the following:

• PROJECT_ID: your project ID.
• DATASET_ID: the ID of the dataset to contain the model.
• MODEL_NAME: the name of the model.
• REGION: the region used by the connection.
• CONNECTION_NAME: the name of the connection.

Annotate images

Annotate images with the ML.ANNOTATE_IMAGE function:

SELECT *
FROM ML.ANNOTATE_IMAGE(
  MODEL PROJECT_ID.DATASET_ID.MODEL_NAME,
  TABLE PROJECT_ID.DATASET_ID.OBJECT_TABLE_NAME,
  STRUCT(['FEATURE_NAME' [,...]] AS vision_features)
);

Replace the following:

• PROJECT_ID: your project ID.
• DATASET_ID: the ID of the dataset that contains the model.
• MODEL_NAME: the name of the model.
• OBJECT_TABLE_NAME: the name of the object table that contains the URIs of the images to annotate.
• FEATURE_NAME: the name of a supported Cloud Vision API feature.

Example 1

The following example labels the items shown in the images:

SELECT *
FROM ML.ANNOTATE_IMAGE(
  MODEL myproject.mydataset.myvisionmodel,
  TABLE myproject.mydataset.image_table,
  STRUCT(['label_detection'] AS vision_features)
);

Example 2

The following example detects any faces shown in the images, and also returns image attributes, like dominant colors:

SELECT *
FROM ML.ANNOTATE_IMAGE(
  MODEL myproject.mydataset.myvisionmodel,
  TABLE myproject.mydataset.image_table,
  STRUCT(['face_detection', 'image_properties'] AS vision_features)
);

What's next

Try the Unstructured data analytics with BigQuery ML and Vertex AI pre-trained models notebook.