Make predictions with PyTorch models in ONNX format

Open Neural Network Exchange (ONNX) provides a uniform format designed torepresent any machine learning framework. BigQuery ML support for ONNXlets you:

  • Train a model using your favorite framework.
  • Convert the model into ONNX model format.
  • Import the ONNX model into BigQuery and make predictionsusing BigQuery ML.

This tutorial shows you how to import ONNX models trained withPyTorchinto a BigQuery dataset and use them to make predictions from aSQL query.

Important: You must have a reservation in order to run predictions usingimported models and object tables. For more information, see theLimitationson imported ONNX models.

Objectives

Costs

In this document, you use the following billable components of Google Cloud:

To generate a cost estimate based on your projected usage, use thepricing calculator.

New Google Cloud users might be eligible for afree trial.

When you finish the tasks that are described in this document, you can avoid continued billing by deleting the resources that you created. For more information, seeClean up.

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.

    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

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

  4. Verify that billing is enabled for your Google Cloud project.

  5. Enable the BigQuery, BigQuery Connection, and Cloud Storage 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

  6. Ensure that you have thenecessary permissions to perform the tasks in this document.

Required roles

If you create a new project, you're the project owner, and you're granted allof the required Identity and Access Management (IAM) permissions that you need to completethis tutorial.

If you are using an existing project, do the following.

Make sure that you have the following role or roles on the project:

Check for the roles

  1. In the Google Cloud console, go to theIAM page.

    Go to IAM
  2. Select the project.

  3. In thePrincipal column, find all rows that identify you or a group that you're included in. To learn which groups you're included in, contact your administrator.

  4. For all rows that specify or include you, check theRole column to see whether the list of roles includes the required roles.

Grant the roles

  1. In the Google Cloud console, go to theIAM page.

    Go to IAM
  2. Select the project.
  3. ClickGrant access.

  4. In theNew principals field, enter your user identifier. This is typically the email address for a Google Account.

  5. In theSelect a role list, select a role.
  6. To grant additional roles, clickAdd another role and add each additional role.
  7. ClickSave.

For more information about IAM permissions in BigQuery,seeIAM permissions.

Optional: Train a model and convert it to ONNX format

The following code samples show you how to import a pretrained classificationmodel into PyTorch and how to convert the resulting model into ONNX format. Thistutorial uses a prebuilt example model stored atgs://cloud-samples-data/bigquery/ml/onnx/resnet18.onnx. You don't have tocomplete these steps if you're using the sample model.

Create a PyTorch vision model for image classification

Use the following code sample to import a PyTorch pretrainedresnet18 modelthat accepts decoded image data returned by the BigQuery MLML.DECODE_IMAGE andML.RESIZE_IMAGE functions.

importtorchimporttorch.nnasnn# Define model input format to match the output format of# ML.DECODE_IMAGE function: [height, width, channels]dummy_input=torch.randn(1,224,224,3,device="cpu")# Load a pretrained pytorch model for image classificationmodel=torch.hub.load('pytorch/vision:v0.10.0','resnet18',pretrained=True)# Reshape input format from [batch_size, height, width, channels]# to [batch_size, channels, height, width]classReshapeLayer(nn.Module):def__init__(self):super().__init__()defforward(self,x):x=x.permute(0,3,1,2)# reorder dimensionsreturnxclassArgMaxLayer(nn.Module):def__init__(self):super().__init__()defforward(self,x):returntorch.argmax(x,dim=1)final_model=nn.Sequential(ReshapeLayer(),model,nn.Softmax(),ArgMaxLayer())

Convert the model into ONNX format

Use the following sample to export the PyTorch vision model usingtorch.onnx. The exported ONNX file is namedresnet18.onnx.

torch.onnx.export(final_model,# model being rundummy_input,# model input"resnet18.onnx",# where to save the modelopset_version=10,# the ONNX version to export the model toinput_names=['input'],# the model's input namesoutput_names=['class_label'])# the model's output names

Upload the ONNX model to Cloud Storage

After you save your model, do the following:

Create a dataset

Create a BigQuery dataset to store your ML model.

Console

  1. In the Google Cloud console, go to theBigQuery page.

    Go to the BigQuery page

  2. In theExplorer pane, click your project name.

  3. ClickView actions > Create dataset

  4. On theCreate dataset page, do the following:

    • ForDataset ID, enterbqml_tutorial.

    • ForLocation type, selectMulti-region, and then selectUS (multiple regions in United States).

    • Leave the remaining default settings as they are, and clickCreate dataset.

bq

To create a new dataset, use thebq mk commandwith the--location flag. For a full list of possible parameters, see thebq mk --dataset commandreference.

  1. Create a dataset namedbqml_tutorial with the data location set toUSand a description ofBigQuery ML tutorial dataset:

    bq --location=US mk -d \ --description "BigQuery ML tutorial dataset." \ bqml_tutorial

    Instead of using the--dataset flag, the command uses the-d shortcut.If you omit-d and--dataset, the command defaults to creating adataset.

  2. Confirm that the dataset was created:

    bqls

API

Call thedatasets.insertmethod with a defineddataset resource.

{"datasetReference":{"datasetId":"bqml_tutorial"}}

BigQuery DataFrames

Before trying this sample, follow the BigQuery DataFrames setup instructions in theBigQuery quickstart using BigQuery DataFrames. For more information, see theBigQuery DataFrames reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up ADC for a local development environment.

importgoogle.cloud.bigquerybqclient=google.cloud.bigquery.Client()bqclient.create_dataset("bqml_tutorial",exists_ok=True)

Import the ONNX model into BigQuery

The following steps show you how to import the sample ONNX model fromCloud Storage into your dataset by using aCREATE MODELstatement.

Console

  1. In the Google Cloud console, go to theBigQuery Studio page.

    Go to BigQuery Studio

  2. In the query editor, enter the followingCREATE MODEL statement.

    CREATEORREPLACEMODEL`bqml_tutorial.imported_onnx_model`OPTIONS(MODEL_TYPE='ONNX',MODEL_PATH='BUCKET_PATH')

    ReplaceBUCKET_PATH with the path to the modelthat you uploaded to Cloud Storage. If you're using the sample model,replaceBUCKET_PATH with the following value:gs://cloud-samples-data/bigquery/ml/onnx/resnet18.onnx.

    When the operation is complete, you see a message similar to thefollowing:Successfully created model named imported_onnx_model.

    Your new model appears in theResources panel. Models areindicated by the model icon:The model icon in the Resources panel.If you select the new model in theResources panel, informationabout the model appears adjacent to theQuery editor.

    The information panel for `imported_onnx_model`.

bq

  1. Import the ONNX model from Cloud Storage by entering thefollowingCREATE MODEL statement.

    bqquery--use_legacy_sql=false\"CREATE OR REPLACE MODEL  `bqml_tutorial.imported_onnx_model`OPTIONS  (MODEL_TYPE='ONNX',   MODEL_PATH='BUCKET_PATH')"

    ReplaceBUCKET_PATH with the path to the modelthat you uploaded to Cloud Storage. If you're using the sample model,replaceBUCKET_PATH with this value:gs://cloud-samples-data/bigquery/ml/onnx/resnet18.onnx.

  2. After you import the model, verify that the model appears in thedataset.

    bq ls -m bqml_tutorial

    The output is similar to the following:

    tableIdType----------------------------imported_onnx_modelMODEL

For more information about importing ONNX models into BigQuery,including format and storage requirements, seeTheCREATE MODEL statement forimporting ONNX models.

Create an object table in BigQuery to analyze image data

Anobject table is a read-only table over unstructured data objects thatreside in Cloud Storage. Object tables let you analyze unstructured datafrom BigQuery.

In this tutorial, you use theML.PREDICT function to output the predictedclass label of an input image that is stored in a Cloud Storage bucket.

Creating the object table requires you to do the following:

  • Create a Cloud Storage bucket and upload an image of a goldfish.
  • Create a Cloud resource connection that is used to access the object table.
  • Grant access to the resource connection's service account.

Create a bucket and upload an image

Follow these steps to create a Cloud Storage bucket and to upload an image ofa goldfish.

Console

Note: When creating a bucket using the Google Cloud console, you're onlyrequired to set a globally unique name for your bucket; all other steps areeither optional or have default settings.
  1. In the Google Cloud console, go to the Cloud StorageBuckets page.

    Go to Buckets

  2. ClickCreate.

  3. On theCreate a bucket page, enter your bucket information.

    1. In theGet started section, do the following:

      1. In the box, enterbqml_images.

      2. ClickContinue.

    2. In theChoose where to store your data section, do the following:

      1. ForLocation type, selectMulti-region.

      2. From the location type's menu, selectUS (multipleregions in United States).

      3. ClickContinue.

    3. In theChoose a storage class for your data section:

      1. SelectSet a default class.

      2. SelectStandard.

      3. ClickContinue.

    4. In the remaining sections, leave the default values.

  4. ClickCreate.

Command line

Enter the followinggcloud storage buckets create command:

gcloud storage buckets create gs://bqml_images --location=us

If the request is successful, the command returns the following message:

Creating gs://bqml_images/...

Upload an image to your Cloud Storage bucket

After the bucket is created, download an image of a goldfish, and uploadit to your Cloud Storage bucket.

Complete the following steps to upload the image:

Console

  1. In the Google Cloud console, go to the Cloud StorageBuckets page.

    Go to Buckets

  2. In the list of buckets, clickbqml_images.

  3. In theObjects tab for the bucket, do one of the following:

    • Drag the file from your desktop or file managerto the main pane in the Google Cloud console.

    • ClickUpload> Upload files, select the image file youwant to upload in the dialog that appears, then clickOpen.

Command line

Enter the followinggcloud storage cp command:

gcloud storage cpOBJECT_LOCATION gs://bqml_images/IMAGE_NAME

Replace the following:

  • OBJECT_LOCATION: the local path to your imagefile. For example,Desktop/goldfish.jpg.
  • IMAGE_NAME: the name of the image. For example,goldfish.jpg.

If successful, the response is similar to the following:

Completed files 1/1 | 164.3kiB/164.3kiB

Create a BigQuery Cloud resource connection

You must have a Cloud resource connection to connect to theobject tablethat you create later in this tutorial.

Cloud resource connections let you query data that's stored outside ofBigQuery in Google Cloud services like Cloud Storage orSpanner, or in third-party sources like AWS orAzure. These external connections use theBigQuery Connection API.

Follow these steps to create your Cloud resource connection.

Console

  1. Go to theBigQuery Studio page.

    Go to BigQuery Studio

  2. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

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

  3. In theExplorer pane, clickAdd data.

    TheAdd data dialog opens.

  4. In theFilter By pane, in theData Source Type section, selectDatabases.

    Alternatively, in theSearch for data sources field, you can enterVertex AI.

  5. In theFeatured data sources section, clickVertex AI.

  6. Click theVertex AI Models: BigQuery Federation solution card.

  7. In theConnection type list, selectVertex AI remote models,remote functions and BigLake (Cloud Resource).

  8. In theConnection ID field, enterbqml_tutorial.

  9. Verify thatMulti-region—US is selected.

  10. ClickCreate connection.

  11. At the bottom of the window, clickGo to connection. Alternatively, intheExplorer pane, clickConnections, and then clickus.bqml_tutorial.

  12. In theConnection info pane, copy the service account ID. You needthis ID when you configure permissions for the connection. When you createa connection resource, BigQuery creates a unique systemservice account and associates it with the connection.

bq

  1. Create a connection:

    bqmk--connection--location=US--project_id=PROJECT_ID\--connection_type=CLOUD_RESOURCEbqml_tutorial

    ReplacePROJECT_ID with yourGoogle Cloud project ID. The--project_id parameter overrides thedefault project.

    When you create a connection resource, BigQuery creates aunique 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...

  2. Retrieve and copy the service account ID for use in a laterstep:

    bqshow--connectionPROJECT_ID.us.bqml_tutorial

    The output is similar to the following:

    name                          properties1234.REGION.CONNECTION_ID {"serviceAccountId": "connection-1234-9u56h9@gcp-sa-bigquery-condel.iam.gserviceaccount.com"}

Set up connection access

Grant the Storage Object Admin role to the Cloud resource connection's serviceaccount. You must grant this role in the same project where you uploaded theimage files.

Note: If the connection is in a different project, this error is returned:bqcx-1234567890-xxxx@gcp-sa-bigquery-condel.iam.gserviceaccount.com does not have the permission to accessresource.

To grant the role, follow these steps:

  1. Go to theIAM & Admin page.

    Go to IAM & Admin

  2. ClickGrant Access.

  3. In theNew principals field, enter the Cloud resource connection'sService account ID that you copied previously.

  4. In theSelect a role field, chooseCloud Storage, and then selectStorage object admin.

  5. ClickSave.

Create the object table

Follow these steps to create an object table namedgoldfish_image_table usingthe goldfish image you uploaded to Cloud Storage.

Console

  1. Go to theBigQuery Studio page.

    Go to BigQuery Studio

  2. In the query editor, enter this query to create the object table.

    CREATEEXTERNALTABLE`bqml_tutorial.goldfish_image_table`WITHCONNECTION`us.bqml_tutorial`OPTIONS(object_metadata='SIMPLE',uris=['gs://bqml_images/IMAGE_NAME'],max_staleness=INTERVAL1DAY,metadata_cache_mode='AUTOMATIC');

    ReplaceIMAGE_NAME with the name of the imagefile—for example,goldfish.jpg.

    When the operation is complete, you see a message likeThis statement created a new table named goldfish_image_table.

bq

  1. Create the object table by entering the followingCREATE EXTERNAL TABLEstatement.

    bqquery--use_legacy_sql=false\"CREATE EXTERNAL TABLE `bqml_tutorial.goldfish_image_table`WITH CONNECTION `us.bqml_tutorial`OPTIONS(object_metadata = 'SIMPLE',uris = ['gs://bqml_images/IMAGE_NAME'],max_staleness = INTERVAL 1 DAY,metadata_cache_mode = 'AUTOMATIC')"

    ReplaceIMAGE_NAME with the name of the imagefile—for example,goldfish.jpg.

  2. After you create the object table, verify that it appears in thedataset.

    bq ls bqml_tutorial

    The output is similar to the following:

    tableIdType-----------------------------goldfish_image_tableEXTERNAL

For more information, seeCreate object tables.

Make predictions with the imported ONNX model

Important: You must have a reservation in order to run predictions using imported models and object tables. For more information, see thelimitations on imported ONNX models.

If you don't have a reservation, running a query usingML.PREDICT produces this error:BigQuery ML inference using imported models and object tables requires a reservation, but no reservations were assigned for job type `QUERY`...`.

You use the following query that contains theML.PREDICT function tomake predictions from image data in the input object tablegoldfish_image_table. This query outputs the predicted class label of theinput image based on theImageNet labels dictionary.

In the query, theML.DECODE_IMAGE functionis required to decode the image data so that it can be interpreted byML.PREDICT. TheML.RESIZE_IMAGE function is called toresize the image to fit the size of the model's input (224*224).

For more information about running inference on image object tables, seeRuninference on image object tables.

To make predictions from your image data, do the following.

Console

  1. Go to theBigQuery Studio page.

    Go to BigQuery Studio

  2. In the query editor, enter the followingML.PREDICT query.

    SELECTclass_labelFROMML.PREDICT(MODELbqml_tutorial.imported_onnx_model,(SELECTML.RESIZE_IMAGE(ML.DECODE_IMAGE(DATA),224,224,FALSE)ASinputFROMbqml_tutorial.goldfish_image_table))

    The query results are similar to the following:

    The results of the ML.PREDICT query

bq

Enter the followingbq query command:

bq query --use_legacy_sql=false \'SELECT  class_labelFROM  ML.PREDICT(MODEL`bqml_tutorial.imported_onnx_model`,    (    SELECT      ML.RESIZE_IMAGE(ML.DECODE_IMAGE(DATA),        224,        224,        FALSE) AS input    FROM      bqml_tutorial.goldfish_image_table))'

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

    Caution: Deleting a project has the following effects:
    • Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
    • Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

    If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

  1. In the Google Cloud console, go to theManage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then clickDelete.
  3. In the dialog, type the project ID, and then clickShut down to delete the project.

gcloud

    Caution: Deleting a project has the following effects:
    • Everything in the project is deleted. If you used an existing project for the tasks in this document, when you delete it, you also delete any other work you've done in the project.
    • Custom project IDs are lost. When you created this project, you might have created a custom project ID that you want to use in the future. To preserve the URLs that use the project ID, such as anappspot.com URL, delete selected resources inside the project instead of deleting the whole project.

    If you plan to explore multiple architectures, tutorials, or quickstarts, reusing projects can help you avoid exceeding project quota limits.

  1. In the Google Cloud console, go to theManage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then clickDelete.
  3. In the dialog, type the project ID, and then clickShut down to delete the project.

Delete individual resources

Alternatively, to remove the individual resources used in this tutorial, do thefollowing:

  1. Delete the imported model.

  2. (Optional)Delete the dataset.

  3. Delete the Cloud resource connection.

  4. Delete the Cloud Storage bucket.

What's next

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.