The ML.ANNOTATE_IMAGE function

This document describes theML.ANNOTATE_IMAGE function, which lets youannotate images that are stored in BigQueryobject tables by using theCloud Vision API.

Syntax

ML.ANNOTATE_IMAGE(  MODEL `PROJECT_ID.DATASET.MODEL_NAME`,  TABLE `PROJECT_ID.DATASET.OBJECT_TABLE`,  STRUCT( [VISION_FEATURES] AS vision_features ))

Arguments

ML.ANNOTATE_IMAGE takes the following arguments:

• PROJECT_ID: the project thatcontains the resource.

• DATASET: the BigQuery dataset thatcontains the resource.

• MODEL_NAME: the name of aremote modelwith aREMOTE_SERVICE_TYPE argumentofCLOUD_AI_VISION_V1.

• OBJECT_TABLE : the name of theobject tablethat contains URIs of the images.

• VISION_FEATURES: aARRAY<STRING> value thatspecifies one or more featurenames of supported Vision API features, in the format['feature_name_1', 'feature_name_2', ...]. The supported features areas follows:

  • FACE_DETECTION

  • LANDMARK_DETECTION

  • LOGO_DETECTION

  • LABEL_DETECTION

  • TEXT_DETECTION

  • DOCUMENT_TEXT_DETECTION

  • IMAGE_PROPERTIES

  • OBJECT_LOCALIZATION

Output

ML.ANNOTATE_IMAGE returns the input table plus the following columns:

• ml_annotate_image_result: aJSON value that contains the image annotationresult from the Vision API.
• ml_annotate_image_status: aSTRING value that contains the API responsestatus for the corresponding row. This value is empty if the operation wassuccessful.

Quotas

SeeCloud AI service functions quotas and limits.

Known issues

Sometimes after a query job that uses this function finishes successfully,some returned rows contain the following error message:

Aretryableerroroccurred:RESOURCEEXHAUSTEDerrorfrom<remoteendpoint>

This issue occurs because BigQuery query jobs finish successfullyeven if the function fails for some of the rows. The function fails when thevolume of API calls to the remote endpoint exceeds the quota limits for thatservice. This issue occurs most often when you are running multiple parallelbatch queries. BigQuery retries these calls, but if the retriesfail, theresource exhausted error message is returned.

To iterate through inference calls until all rows are successfully processed,you can use theBigQuery remote inference SQL scriptsor theBigQuery remote inference pipeline Dataform package.

Locations

ML.ANNOTATE_IMAGE must run in the same region as the remote model that thefunction references. For more information about supported locations for modelsbased on the Vision API, seeLocations for remote models.

Examples

Example 1

The following example performs label detection on the object tablemytable inmydataset:

#CreatemodelCREATEORREPLACEMODEL`myproject.mydataset.myvisionmodel`REMOTEWITHCONNECTION`myproject.myregion.myconnection`OPTIONS(remote_service_type='cloud_ai_vision_v1');

#AnnotateimageSELECT*FROMML.ANNOTATE_IMAGE(MODEL`mydataset.myvisionmodel`,TABLE`mydataset.mytable`,STRUCT(['label_detection']ASvision_features));

The result is similar to the following:

ml_annotate_image_result|ml_annotate_image_status|uri|generation|content_type|size|md5_hash|updated|metadata|-------|--------|--------|--------|--------|--------|--------|--------|--------{"label_annotations":[{"description":"Food","mid":"/m/02wbm","score":0.97591567,"topicality":0.97591567}]}||gs://my-bucket/images/Cheeseburger.jpg|1661921874516197|image/jpeg|174600|a259a5076c22696848a1bc10b7162cc2|2022-08-3104:57:54|[]

Example 2

The following example annotates images in the object tablemytable, selectsthe rows where the detected label isfood and the score is higher than0.97,and then returns the results in separate columns:

CREATETABLE`mydataset.label_score`AS(SELECTuriAS`Inputimagepath`,STRING(ml_annotate_image_result.label_annotations[0].description)AS`Detectedlabel`,FLOAT64(ml_annotate_image_result.label_annotations[0].score)ASScore,FLOAT64(ml_annotate_image_result.label_annotations[0].topicality)ASTopicality,ml_annotate_image_statusASStatusFROMML.ANNOTATE_IMAGE(MODEL`mydataset.myvisionmodel`,TABLE`mydataset.mytable`,STRUCT(['label_detection']ASvision_features)));SELECT*FROM`mydataset.label_score`WHERE`Detectedlabel`='Food'ANDScore>0.97;

The result is similar to the following:

Inputimagepath|Detectedlabel|Score|Topicality|Status|-------|--------|--------|--------|--------gs://my-bucket/images/Cheeseburger.jpg|Food|0.97591567|0.97591567||

If you get an error likequery limit exceeded, you might have exceeded thequota for this function, whichcan leave you with unprocessed rows. Use the following query to completeprocessing the unprocessed rows:

CREATETABLE`mydataset.label_score_next`AS(SELECTuriAS`Inputimagepath`,STRING(ml_annotate_image_result.label_annotations[0].description)AS`Detectedlabel`,FLOAT64(ml_annotate_image_result.label_annotations[0].score)ASScore,FLOAT64(ml_annotate_image_result.label_annotations[0].topicality)ASTopicality,ml_annotate_image_statusASStatusFROMML.ANNOTATE_IMAGE(MODEL`mydataset.myvisionmodel`,TABLE`mydataset.mytable`,STRUCT(['label_detection']ASvision_features))WHEREuriNOTIN(SELECT`Inputimagepath`FROM`mydataset.label_score`WHERESTATUS=''));SELECT*FROM`mydataset.label_score_next`;

What's next

• Get step-by-step instructions on how toannotate images in an object tableusing theML.ANNOTATE_IMAGE function.
• Learn more aboutother functions you can useto analyze BigQuery data.
• For information about model inference, seeModel inference overview.
• For more information about supported SQL statements and functions forgenerative AI models, seeEnd-to-end user journeys for generative AI models.