Run inference on image object tables

This document describes how to use BigQuery ML to run inferenceon imageobject tables.

You can run inference on image data by using an object table as input to theML.PREDICT function.

To do this, you must first choose an appropriate model, upload it toCloud Storage, and import it into BigQuery by running theCREATE MODEL statement.You can either create your own model, or download onefromTensorFlow Hub.

Limitations

• Using BigQuery MLimported modelswith object tables is only supported when you use capacity-based pricingthrough reservations; on-demand pricing isn't supported.
• The image files associated with the object table must meet the followingrequirements:
  • Are less than 20 MB in size.
  • Have a format of JPEG, PNG or BMP.
• The combined size of the image files associated with the object table mustbe less than 1 TB.

• The model must be one of following:

  • ATensorFloworTensorFlow Lite model inSavedModel format.
  • A PyTorch model inONNX format.

• The model must meet the input requirements and limitations described in theCREATE MODEL statement for importing TensorFlow models.

• The serialized size of the model must be less than 450 MB.

• The deserialized (in-memory) size of the model must be less than1000 MB.

• The model input tensor must meet the following criteria:

  • Have a data type oftf.float32 with values in[0, 1) or havea data type oftf.uint8 with values in[0, 255).
  • Have the shape[batch_size, weight, height, 3], where:
    • batch_size must be-1,None, or1.
    • width andheight must be greater than 0.

• The model must be trained with images in one of the following color spaces:

  • RGB
  • HSV
  • YIQ
  • YUV
  • GRAYSCALE

  You can use theML.CONVERT_COLOR_SPACE functionto convert input images to the color space that the model was trained with.

Example models

The following models on TensorFlow Hub work withBigQuery ML and image object tables:

• ResNet 50. To tryusing this model, seeTutorial: Run inference on an object table by using a classification model.
• MobileNet V3.To try using this model, seeTutorial: Run inference on an object table by using a feature vector model.

Required permissions

• To upload the model to Cloud Storage, you need thestorage.objects.create andstorage.objects.get permissions.

• To load the model into BigQuery ML, you need the followingpermissions:

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

• 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

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 theresourcemanager.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 and BigQuery Connection API APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.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 theresourcemanager.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 and BigQuery Connection API APIs.

Roles required to enable APIs

To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains theserviceusage.services.enable permission.Learn how to grant roles.

Enable the APIs

Upload a model to Cloud Storage

Follow these steps to upload a model:

1. If you have created your own model, save it locally. If youare using a model from TensorFlow Hub, download it to yourlocal machine. If you are using TensorFlow, this should giveyou asaved_model.pb file and avariables folder for the model.
2. If necessary,create a Cloud Storage bucket.
3. Upload the model artifacts to the bucket.

Load the model into BigQuery ML

Loading a model that works with image object tables is the same as loading amodel that works with structured data. Follow these steps to load a model intoBigQuery ML:

CREATEMODEL`PROJECT_ID.DATASET_ID.MODEL_NAME`OPTIONS(model_type='MODEL_TYPE',model_path='BUCKET_PATH');

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.
• MODEL_TYPE: use one of the following values:
  • TENSORFLOW for a TensorFlow model
  • ONNX for a PyTorch model in ONNX format
• BUCKET_PATH: the path to the Cloud Storage bucket that contains the model, in the format[gs://bucket_name/[folder_name/]*].

The following example uses the default project and loads a TensorFlowmodel to BigQuery ML asmy_vision_model, using thesaved_model.pb file andvariables folder fromgs://my_bucket/my_model_folder:

CREATEMODEL`my_dataset.my_vision_model`OPTIONS(model_type='TENSORFLOW',model_path='gs://my_bucket/my_model_folder/*');

Inspect the model

You can inspect the uploaded model to see what its input and output fieldsare. You need to reference these fields when you run inference on the objecttable.

Follow these steps to inspect a model:

1. Go to theBigQuery page.

   Go to BigQuery

2. In the left pane, clickexploreExplorer:

   

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

3. In theExplorer pane, expand your project and clickDatasets.

4. Click the dataset that contains your model.

5. Click theModels tab.

6. In the model pane that opens, click theSchema tab.

7. Look at theLabels section. This identifies the fields that are outputby the model.

8. Look at theFeatures section. This identifies the fields that mustbe input into the model. You reference them in theSELECT statementfor theML.DECODE_IMAGE function.

For more detailed inspection of a TensorFlow model, for example todetermine the shape of the model input,install TensorFlowand use thesaved_model_cli show command.

Preprocess images

You must use theML.DECODE_IMAGE functionto convert image bytes to a multi-dimensionalARRAY representation. You canuseML.DECODE_IMAGE output directly in anML.PREDICT function,or you can write the results fromML.DECODE_IMAGE to a table column andreference that column when you callML.PREDICT.

The following example writes the output of theML.DECODE_IMAGE function toa table:

CREATEORREPLACETABLEmydataset.mytableAS(SELECTML.DECODE_IMAGE(data)ASdecoded_imageFROMmydataset.object_table);

Use the following functions to further process images so that they work withyour model:

• TheML.CONVERT_COLOR_SPACE functionconverts images with anRGB color space to a different color space.
• TheML.CONVERT_IMAGE_TYPE functionconverts the pixel values output by theML.DECODE_IMAGE functionfrom floating point numbers to integers with a range of[0, 255).
• TheML.RESIZE_IMAGE functionresizes images.

You can use these as part of theML.PREDICT function, or run them on atable column containing image data output byML.DECODE_IMAGE.

Run inference

Once you have an appropriate model loaded, and optionally preprocessedthe image data,you can run inference on the image data.

To run inference:

SELECT*FROMML.PREDICT(MODEL`PROJECT_ID.DATASET_ID.MODEL_NAME`,(SELECT[othercolumnsfromtheobjecttable,]IMAGE_DATAASMODEL_INPUTFROMPROJECT_ID.DATASET_ID.TABLE_NAME));

Replace the following:

• PROJECT_ID: the project ID of the project that contains the model and object table.
• DATASET_ID: the ID of the dataset that contains the model and object table.
• MODEL_NAME: the name of the model.
• IMAGE_DATA: the image data, represented either by the output of theML.DECODE_IMAGE function, or by a table column containing image data output byML.DECODE_IMAGE or other image processing functions.
• MODEL_INPUT: the name of an input field for the model.You can find this information byinspecting the model and looking at the field names in theFeatures section.
• TABLE_NAME: the name of the object table.

Examples

Example 1

The following example uses theML.DECODE_IMAGE function directly in theML.PREDICT function. It returns the inference results for all images in theobject table, for a model with an input field ofinput and an outputfield offeature:

SELECT*FROMML.PREDICT(MODEL`my_dataset.vision_model`,(SELECTuri,ML.RESIZE_IMAGE(ML.DECODE_IMAGE(data),480,480,FALSE)ASinputFROM`my_dataset.object_table`));

Example 2

The following example uses theML.DECODE_IMAGE function directly in theML.PREDICT function, and uses theML.CONVERT_COLOR_SPACE function in theML.PREDICT function to convertthe image color space fromRBG toYIQ. It also shows how touse object table fields to filter the objects included in inference.It returns the inference results for all JPG images in theobject table, for a model with an input field ofinput and an outputfield offeature:

SELECT*FROMML.PREDICT(MODEL`my_dataset.vision_model`,(SELECTuri,ML.CONVERT_COLOR_SPACE(ML.RESIZE_IMAGE(ML.DECODE_IMAGE(data),224,280,TRUE),'YIQ')ASinputFROM`my_dataset.object_table`WHEREcontent_type='image/jpeg'));

Example 3

The following example uses results fromML.DECODE_IMAGE that have beenwritten to a table column but not processed any further. It usesML.RESIZE_IMAGE andML.CONVERT_IMAGE_TYPE in theML.PREDICT function toprocess the image data. It returns the inference results for all images in thedecoded images table, for a model with an input field ofinput and an outputfield offeature.

Create the decoded images table:

CREATEORREPLACETABLE`my_dataset.decoded_images`AS(SELECTML.DECODE_IMAGE(data)ASdecoded_imageFROM`my_dataset.object_table`);

Run inference on the decoded images table:

SELECT*FROMML.PREDICT(MODEL`my_dataset.vision_model`,(SELECTuri,ML.CONVERT_IMAGE_TYPE(ML.RESIZE_IMAGE(decoded_image,480,480,FALSE))ASinputFROM`my_dataset.decoded_images`));

Example 4

The following example uses results fromML.DECODE_IMAGE that have beenwritten to a table column and preprocessed usingML.RESIZE_IMAGE. It returns the inference results for all images in thedecoded images table, for a model with an input field ofinput and an outputfield offeature.

Create the table:

CREATEORREPLACETABLE`my_dataset.decoded_images`AS(SELECTML.RESIZE_IMAGE(ML.DECODE_IMAGE(data)480,480,FALSE)ASdecoded_imageFROM`my_dataset.object_table`);

Run inference on the decoded images table:

SELECT*FROMML.PREDICT(MODEL`my_dataset.vision_model`,(SELECTuri,decoded_imageASinputFROM`my_dataset.decoded_images`));

Example 5

The following example uses theML.DECODE_IMAGE function directly in theML.PREDICT function. In this example, the model has an output field ofembeddings and two input fields: one that expects animage,f_img, and one that expects a string,f_txt. The imageinput comes from the object table and the string input comes from astandard BigQuery table that is joined with the object tableby using theuri column.

SELECT*FROMML.PREDICT(MODEL`my_dataset.mixed_model`,(SELECTuri,ML.RESIZE_IMAGE(ML.DECODE_IMAGE(my_dataset.my_object_table.data),224,224,FALSE)ASf_img,my_dataset.image_description.descriptionASf_txtFROM`my_dataset.object_table`JOIN`my_dataset.image_description`ONobject_table.uri=image_description.uri));

What's next

• Learn how toanalyze object tables by using remote functions.
• Tryrunning inference on an object table by using a feature vector model.
• Tryrunning inference on an object table by using a classification model.
• Tryanalyzing an object table by using a remote function.
• Tryannotating an image with theML.ANNOTATE_IMAGE function.