In this lab, you learn how to help streamline an image classification workflow in BigQuery using an imported model (PyTorch model created in ONNX format), and Google SQL queries.
As a new data analyst at Cymbal Media and Entertainment, you have been tasked to experiment with an imported model and BigQuery Machine Learning for inference to classify wildlife images. This project aims to automate the image classification process, simplify content creation, and potentially build a larger database of wildlife images for future content on Cymbal's streaming platform.
ONNX is an open format built to represent machine learning models. ONNX defines a common set of operators — the building blocks of machine learning and deep learning models — and a common file format to enable AI developers to use models with a variety of frameworks, tools, runtimes, and compilers.
BigQuery is a fully managed, AI-ready data analytics platform which helps you maximize value from your data and is designed to be multi-engine, multi-format, and multi-cloud. One of its key features is BigQuery Machine Learning for inference, which lets you create and run machine learning (ML) models by using SQL queries.
Running ML models using GoogleSQL Queries
Usually, performing ML or artificial intelligence (AI) on large datasets requires extensive programming and knowledge of ML frameworks. This restricts solution development to a small set of people within each company, and excludes data analysts who understand the data but have limited ML knowledge and programming expertise. However, with BigQuery ML, SQL practitioners can use existing SQL knowledge, skills, and tools to build and to generate results from models built with ONNX, and stored in a cloud storage bucket. This helps companies with model choice, and flexibility from an MLOPs perspective. It also helps scale their ML initiatives.
Grant permissions to the connection's service account.
Create a BigQuery dataset and tables.
Import the ONNX model into BigQuery.
Classify images using the imported model.
Setup and requirements
Before you click the Start Lab button
Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.
This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.
To complete this lab, you need:
Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.
Time to complete the lab—remember, once you start, you cannot pause a lab.
Note: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.
How to start your lab and sign in to the Google Cloud console
Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method.
On the left is the Lab Details pane with the following:
The Open Google Cloud console button
Time remaining
The temporary credentials that you must use for this lab
Other information, if needed, to step through this lab
Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).
The lab spins up resources, and then opens another tab that shows the Sign in page.
Tip: Arrange the tabs in separate windows, side-by-side.
Note: If you see the Choose an account dialog, click Use Another Account.
If necessary, copy the Username below and paste it into the Sign in dialog.
{{{user_0.username | "Username"}}}
You can also find the Username in the Lab Details pane.
Click Next.
Copy the Password below and paste it into the Welcome dialog.
{{{user_0.password | "Password"}}}
You can also find the Password in the Lab Details pane.
Click Next.
Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials.
Note: Using your own Google Cloud account for this lab may incur extra charges.
Click through the subsequent pages:
Accept the terms and conditions.
Do not add recovery options or two-factor authentication (because this is a temporary account).
Do not sign up for free trials.
After a few moments, the Google Cloud console opens in this tab.
Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.
Task 1. Create a Cloud Resource connection
In this task, you create a Cloud Resource connection in BigQuery, so you can import the images from Cloud Storage into the object table within your dataset.
Note: Create the resource connection using the Google Cloud Region assigned in your lab guide. We also recommend you create the dataset in this region, which will help reduce errors in the lab.
In the Google Cloud Console, on the Navigation menu, click BigQuery.
The Welcome to BigQuery pop-up is displayed. Click DONE.
To create a connection, click + ADD, and then click Connections to external data sources.
Note: Alternatively, if you do not see the option for + Add followed by Connections to external data sources, you can click + Add data, and then use the search bar for data sources to search for Vertex AI. Click on the result for Vertex AI.
In the Connection type list, select Vertex AI remote models, remote functions and BigLake (Cloud Resource).
In the Connection ID field, enter onnx_conn for your connection.
For Location type, choose Region.
For Region, choose .
Click CREATE CONNECTION.
Click GO TO CONNECTION.
You will also see the connection added under the External Connections section of your project in the BigQuery Explorer. In the Connection info pane, copy the service account ID to a text file for use in the next task.
Click Check my progress to verify the objective.
Create a Cloud Resource connection.
Task 2. Review images, and files, and grant access to service account
In this task, you review images and files (ONNX model and classifier csv), and grant IAM permissions to the Cloud Resource connection's service account.
Review the images and ONNX model on Cloud Storage
Before you dive into this task to grant permissions to the resource connection service account, review the images, the model and the classifier.csv file as they are stored in the Cloud Storage bucket.
In the Google Cloud console, select the Navigation menu (), and then select Cloud Storage > Buckets.
Click on the -bucket bucket.
The bucket contains the gsp1248 folder, open the folder. You will see three items in it:
The images folder contains all images you will classify. Feel free to access the images folder and review the images. You should see an image of a lion, black swan, hippopotamus, American black bear, and king crab.
The resnet18.onnx file is the model file. You will import it into BigQuery and use it in later tasks.
The classifier.csv file will be used with the model within a query to classify the images.
Grant IAM permissions to the connection's service account
Granting IAM permissions to the resource connection's service account before you start working in BigQuery will ensure you do not encounter access denied errors when running queries.
Return to the bucket root.
Click PERMISSIONS.
Click GRANT ACCESS.
In the New principals field, enter the service account ID you copied earlier.
In the Select a role field, enter Storage Object, and then select Storage Object Admin role.
Click Save.
The result is the service account id now includes the Storage Object Admin role.
Click Check my progress to verify the objective.
Grant IAM permissions to the connection's service account
Task 3. Create the dataset and tables in BigQuery
In this task, you create a dataset for the project, an object table to store the images, and a table for the classification list.
Create a dataset
In the Google Cloud console, select the Navigation menu (), and then select BigQuery.
In the Explorer panel, for , select View actions (), and then select Create dataset.
You create a dataset to store database objects, including tables and models.
In the Create dataset pane, enter the following information:
Field
Value
Dataset ID
onnx_demo
Location type > Region
select
Leave the other fields at their defaults.
Click Create Dataset.
The result is the onnx_demo dataset is created and listed underneath your project in the BigQuery Explorer.
Create the object table for the images
With the dataset created, you can now create the object table referencing the images in the bucket.
To create the object table you will use a SQL query.
Click the + to create a new SQL query.
In the query editor, paste the query below.
CREATE OR REPLACE EXTERNAL TABLE
`onnx_demo.wildlife`
WITH CONNECTION `{{{project_0.default_region|lab region}}}.onnx_conn`
OPTIONS (
object_metadata = 'SIMPLE',
uris = ['gs://{{{project_0.project_id|set at lab start}}}-bucket/gsp1248/images/*']
);
Run the query.
The result is the wildlife object table is added to the onnx_demo dataset and loaded with the URI (the cloud storage location) of each animal image.
In the Explorer, click on the wildlife table and review the schema and details.
Create the table for the classification list
To create the table classification list you will use a SQL query.
Click the + to create a new SQL query.
In the query editor, paste the query below.
LOAD DATA OVERWRITE onnx_demo.classifier
(ID INT64,Classification STRING)
FROM FILES (
format = 'CSV',
uris = ['gs://{{{project_0.project_id|set at lab start}}}-bucket/gsp1248/classifier.csv']);
Run the query.
The result is the classifier table added to the onnx_demo dataset, loaded with the ID and the classification of each item in the dataset. This includes wildlife you will classify later in the lab.
In the Explorer, click on the classifier table and review the schema and details. Feel free to query the table to review how it classifies each item. Note: the table contains many other items, not just wildlife (like animals, fish, insects and birds).
Click Check my progress to verify the objective.
Create the dataset and tables in BigQuery
Task 4. Import the model into BigQuery
In this task, you import the resnet18 ONNX model into BigQuery so you may use it to classify images.
Click the + to create a new SQL query.
In the query editor, paste the query below.
CREATE OR REPLACE MODEL `onnx_demo.model`
OPTIONS (MODEL_TYPE='ONNX',
MODEL_PATH='gs://{{{project_0.project_id|set at lab start}}}-bucket/gsp1248/resnet18.onnx')
Run the query.
The result is the model is added to the onnx_demo dataset in the models section.
In the Explorer, click on the model and review the details.
Click Check my progress to verify the objective.
Import the model into BigQuery
Task 5. Classify images using the imported ONNX model
In this task, you reserve a slot, assign it to your project, and use a query to classify new images with the ONNX model.
Add a slot reservation
In order for you to use the ONNX model, you will have to create a slot reservation, and assign it to your project.
From the BigQuery menu click Capacity Management.
On the Capacity Management page, click CREATE RESERVATION.
On the Create Reservation page, enter reservation as the reservation name.
Choose the for the Location.
Choose Small (100 Slots) for the Max reservation size selector option.
Click SAVE.
Assign the slot reservation to your project.
Click on the Reservation Actions button (vertical 3 dots) for your reservation.
In the pop-up menu, click Create Assignment.
In the Create an Assignment pop-up window, you will see the Job Type default to QUERY. Keep this option.
Click BROWSE.
In the pop-up choose the project name. The project is added to the Select an organization, folder or project field.
Click CREATE.
The result is the project is assigned to the slot reservation you just created.
Wait 2 minutes. The slot reservation assignment takes some time to apply to the account.
Use the model to classify images
Return to BigQuery Studio.
Click the + to create a new SQL query.
In the query editor, paste the query below.
SELECT * FROM `{{{project_0.project_id|set at lab start}}}.onnx_demo.classifier`
WHERE EXISTS (
SELECT
1
FROM
ML.PREDICT(MODEL onnx_demo.model,
(
SELECT
ML.RESIZE_IMAGE(ML.DECODE_IMAGE(DATA),
224,
224,
FALSE) AS input
FROM
`onnx_demo.wildlife`
)
)
WHERE
class_label = ID - 1
)
Run the query.
The result is the images are each classified by row with the ID and the predicted classification based on the ONNX model.
Click Check my progress to verify the objective.
Classify images using the imported ONNX model
Congratulations!
You successfully created a Cloud Resource connection, granted access to the service account for the connection, created a dataset and tables, imported an ONNX model into BigQuery, and classified images using the model.
...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.
Manual Last Updated March 21, 2025
Lab Last Tested March 21, 2025
Copyright 2025 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.
Moduły tworzą projekt Google Cloud i zasoby na określony czas.
Moduły mają ograniczenie czasowe i nie mają funkcji wstrzymywania. Jeśli zakończysz moduł, musisz go zacząć od początku.
Aby rozpocząć, w lewym górnym rogu ekranu kliknij Rozpocznij moduł.
Użyj przeglądania prywatnego
Skopiuj podaną nazwę użytkownika i hasło do modułu.
Kliknij Otwórz konsolę w trybie prywatnym.
Zaloguj się w konsoli
Zaloguj się z użyciem danych logowania do modułu. Użycie innych danych logowania może spowodować błędy lub naliczanie opłat.
Zaakceptuj warunki i pomiń stronę zasobów przywracania.
Nie klikaj Zakończ moduł, chyba że właśnie został przez Ciebie zakończony lub chcesz go uruchomić ponownie, ponieważ spowoduje to usunięcie wyników i projektu.
Ta treść jest obecnie niedostępna
Kiedy dostępność się zmieni, wyślemy Ci e-maila z powiadomieniem
Świetnie
Kiedy dostępność się zmieni, skontaktujemy się z Tobą e-mailem
Jeden moduł, a potem drugi
Potwierdź, aby zakończyć wszystkie istniejące moduły i rozpocząć ten
Aby uruchomić moduł, użyj przeglądania prywatnego
Uruchom ten moduł w oknie incognito lub przeglądania prywatnego. Dzięki temu unikniesz konfliktu między swoim kontem osobistym a kontem do nauki, co mogłoby spowodować naliczanie dodatkowych opłat na koncie osobistym.
In this lab, you learn how to streamline an image classification workflow in BigQuery using an imported model (PyTorch model created in ONNX format), and Google SQL queries.
Czas trwania:
Konfiguracja: 0 min
·
Dostęp na 60 min
·
Ukończono w 60 min