Annotate 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 from an
object table.
Required permissions
To create a connection, you need membership in the following role:
roles/bigquery.connectionAdmin
To grant permissions to the connection's service account, you need the following permission:
resourcemanager.projects.setIamPolicy
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 tablebigquery.models.getData
on the modelbigquery.jobs.create
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.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection API, and Cloud Vision API APIs.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
-
Make sure that billing is enabled for your Google Cloud project.
-
Enable the BigQuery, BigQuery Connection API, and Cloud Vision API APIs.
Create a connection
Create a cloud resource connection and get the connection's service account.
Select one of the following options:
Console
Go to the BigQuery page.
To create a connection, click
Add, and then click Connections to external data sources.In the Connection type list, select Vertex AI remote models, remote functions and BigLake (Cloud Resource).
In the Connection ID field, enter a name for your connection.
Click Create connection.
Click Go to connection.
In the Connection info pane, copy the service account ID for use in a later step.
bq
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 regionPROJECT_ID
: your Google Cloud project IDCONNECTION_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.
Troubleshooting: 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...
Retrieve and copy the service account ID for use 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": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}
Terraform
Use the
google_bigquery_connection
resource.
To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.
The following example creates a Cloud resource connection named
my_cloud_resource_connection
in the US
region:
To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.
Prepare Cloud Shell
- Launch Cloud Shell.
-
Set the default Google Cloud project where you want to apply your Terraform configurations.
You only need to run this command once per project, and you can run it in any directory.
export GOOGLE_CLOUD_PROJECT=PROJECT_ID
Environment variables are overridden if you set explicit values in the Terraform configuration file.
Prepare the directory
Each Terraform configuration file must have its own directory (also called a root module).
-
In Cloud Shell, create a directory and a new
file within that directory. The filename must have the
.tf
extension—for examplemain.tf
. In this tutorial, the file is referred to asmain.tf
.mkdir DIRECTORY && cd DIRECTORY && touch main.tf
-
If you are following a tutorial, you can copy the sample code in each section or step.
Copy the sample code into the newly created
main.tf
.Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.
- Review and modify the sample parameters to apply to your environment.
- Save your changes.
-
Initialize Terraform. You only need to do this once per directory.
terraform init
Optionally, to use the latest Google provider version, include the
-upgrade
option:terraform init -upgrade
Apply the changes
-
Review the configuration and verify that the resources that Terraform is going to create or
update match your expectations:
terraform plan
Make corrections to the configuration as necessary.
-
Apply the Terraform configuration by running the following command and entering
yes
at the prompt:terraform apply
Wait until Terraform displays the "Apply complete!" message.
- Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.
Grant access to the service account
Select one of the following options:
Console
Go to the IAM & Admin page.
Click
Add.The Add principals dialog opens.
In the New principals field, enter the service account ID that you copied earlier.
In the Select a role field, select Service Usage, and then select Service Usage Consumer.
Click Add another role.
In the Select a role field, select BigQuery, and then select BigQuery Connection User.
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 an object table
Create an 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 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_ID 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. This dataset must be in the same location as the connection that you are using.MODEL_NAME
: the name of the model.REGION
: the region used by the connection.CONNECTION_ID
: the connection ID—for example,myconnection
.When you view the connection details in the Google Cloud console, the connection ID is the value in the last section of the fully qualified connection ID that is shown in Connection ID—for example
projects/myproject/locations/connection_location/connections/myconnection
.
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
- To learn more about model inference, including other functions you can use to analyze BigQuery data, see Model inference overview.
- For information about the supported SQL statements and functions for each model type, see End-to-end user journey for each model.
- Try the Unstructured data analytics with BigQuery ML and Vertex AI pre-trained models notebook.