Tutorial: Analyze an object table by using a remote function
This tutorial show you how to create an object table based on the images from the flowers dataset, create a remote function that labels images using Cloud Vision API, and then analyze the images in the object table using the remote function.
Required permissions
- To create the dataset, you need the
bigquery.datasets.create
permission. To create the connection resource, you need the following permissions:
bigquery.connections.create
bigquery.connections.get
To grant permissions to the connection's service account, you need the following permission:
resourcemanager.projects.setIamPolicy
To create the object table, you need the following permissions:
bigquery.tables.create
bigquery.tables.update
bigquery.connections.delegate
To create the remote function, you need the permissions associated with the Cloud Functions Developer role.
To invoke the remote function, you need the Cloud Run Invoker role.
To analyze the object table with the remote function, you need the
bigquery.tables.getData
permission on the object table.
Costs
In this document, you use the following billable components of Google Cloud:
- BigQuery: You incur storage costs for the object table you create in BigQuery.
- Cloud Functions: You incur costs for invoking the remote function and for any compute resources it uses, including calls to Cloud Vision API.
To generate a cost estimate based on your projected usage,
use the pricing calculator.
For more information on Cloud Storage pricing, see the Cloud Storage pricing page.
For more information on BigQuery storage pricing, see Storage pricing in the BigQuery documentation.
For more information on Cloud Functions pricing, see the Cloud Functions pricing page.
For more information on Vision API pricing, see Cloud Vision pricing in the Vision documentation.
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 Functions 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 Functions APIs.
Create a Cloud Functions function
To create a function, follow these steps:
Go to the Cloud Functions page.
Click Create function.
For Environment, select 2nd gen.
For Function name, type
vision-ai
.Click Next.
For Runtime, select Python 3.9.
For Entry point, type
label_detection
.Select
main.py
. Copy and paste in the following code:Python
Select
requirements.txt
. Copy and paste in the following text:Click Deploy.
When the function has finished deploying, click the Trigger tab.
Copy the Trigger URL value and save it somewhere. You need this information when you create the remote function.
Create a dataset
Create a dataset named remote_function_test
:
SQL
Go to the BigQuery page.
In the Editor pane, run the following SQL statement:
CREATE SCHEMA `PROJECT_ID.remote_function_test`;
Replace
PROJECT_ID
with your project ID.
bq
In the Google Cloud console, activate Cloud Shell.
Run the
bq mk
command to create the dataset:bq mk --dataset --location=us PROJECT_ID:remote_function_test
Replace
PROJECT_ID
with your project ID.
Create a connection
Create a connection named lake-connection
:
Console
Go to the BigQuery page.
Click
Add data, and then click External data source.In the Connection type list, select BigLake and remote functions (Cloud Resource).
In the Connection ID field, type
lake-connection
.Click Create connection.
In the Connection info pane, copy the value from the Service account id field and save it somewhere. You need this information to grant permissions to the connection's service account.
bq
In Cloud Shell, run the
bq mk
command to create the connection:bq mk --connection --location=us --connection_type=CLOUD_RESOURCE \ lake-connection
Run the
bq show
command to retrieve information about the connection:bq show --connection us.lake-connection
From the
properties
column, copy the value of theserviceAccountId
property and save it somewhere. You need this information to grant permissions to the connection's service account.
Create a Cloud Storage bucket
Create a Cloud Storage bucket to contain the flowers dataset.
Grant permissions to the connection's service account
To grant permissions to the service account, follow these steps:
Go to the IAM & Admin page.
Click Grant Access.
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 Cloud Run, and then select Cloud Run Invoker.
Click Add another role.
In the Select a role field, select Cloud Storage, and then select Storage Object Viewer.
Click Save.
Upload the dataset to Cloud Storage
Get the dataset files and make them available in Cloud Storage:
- Download the flowers dataset to your local machine.
- Upload the dataset to the bucket you previously created.
Create an object table
Create an object table named sample_images
based on the flowers dataset you
uploaded:
SQL
Go to the BigQuery page.
In the Editor pane, run the following SQL statement:
CREATE EXTERNAL TABLE remote_function_test.sample_images WITH CONNECTION `us.lake-connection` OPTIONS( object_metadata = 'SIMPLE', uris = ['gs://BUCKET_NAME/*']);
Replace
BUCKET_NAME
with the name of the bucket you previously created.
bq
In Cloud Shell, run the
bq mk
command
to create the connection:
bq mk --table \ --external_table_definition=gs:"//BUCKET_NAME/*@us.lake-connection" \ --object_metadata=SIMPLE \ remote_function_test.sample_images
Replace BUCKET_NAME
with the name of the bucket you
previously created.
Create the BigQuery remote function
Create a remote function named label_detection
:
Go to the BigQuery page.
In the Editor pane, run the following SQL statement:
CREATE OR REPLACE FUNCTION `remote_function_test.label_detection` (signed_url_ STRING) RETURNS JSON REMOTE WITH CONNECTION `us.lake-connection` OPTIONS( endpoint = 'TRIGGER_URL', max_batching_rows = 1 );
Replace
TRIGGER_URL
with the trigger URL that you saved earlier. The URL should look similar tohttps://vision-ai-1abcd2efgh-uc.a.run.app
.
Call the remote function
Call the label_detection
remote function on the sample_images
object table:
Go to the BigQuery page.
In the Editor pane, run the following SQL statement:
SELECT uri, remote_function_test.label_detection(signed_url) FROM EXTERNAL_OBJECT_TRANSFORM( TABLE remote_function_test.sample_images, ["SIGNED_URL"] ) LIMIT 100;
The results should look similar to the following:
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | uri | f0_ | —--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | gs://bq_huron_demo/flowers/daisy/100080576_f52e8ee070_n.jpg | {"face_annotations":[],"label_annotations":[{"confidence":0.0,"description":"Flower", | | | "locale":"","locations":[],"mid":"/m/0c9ph5","properties":[],"score":0.9785631,"topicality":0.9785631}, | | | {"confidence":0.0,"description":"Plant","locale":"","locations":[],"mid":"/m/05s2s","properties":[], | | | "Score":0.9635679,"topicality":0.9635679},{"confidence":0.0,"description":"camomile","locale":"", | | | "locations":[],"mid":"/m/011bc8hg","properties":[],"score":0.9110366,"topicality":0.9110366}, | | | {"confidence":0.0,"description":"Petal","locale":"","locations":[],"mid":"/m/016q19","properties":[], | | | "score":0.8927441,"topicality":0.8927441},{"confidence":0.0,"description":"Chamaemelum nobile","locale":"", | | | "locations":[],"mid":"/m/05cmcg","properties":[],"score":0.8460995,"topicality":0.8460995}, | | | {"confidence":0.0,"description":"Flowering plant","locale":"","locations":[],"mid":"/m/04sjm", | | | "properties":[],"score":0.7642974,"topicality":0.7642974},{"confidence":0.0,"description":"Annual plant", | | | "locale":"","locations":[],"mid":"/m/0jqb","properties":[],"score":0.7478164, | | | "topicality":0.7478164},{"confidence":0.0,"description":"Close-up","locale":"","locations":[], | | | "mid":"/m/02cqfm","properties":[],"score":0.7207553,"topicality":0.7207553},{"confidence":0.0, | | | "description":"Oxeye daisy","locale":"","locations":[],"mid":"/m/02qvnf","properties":[], | | | "score":0.71786934,"topicality":0.71786934},{"confidence":0.0,"description":"Daisy family","locale":"", | | | "locations":[],"mid":"/m/0l5r","properties":[],"score":0.7164383,"topicality":0.7164383}], | | | "landmark_annotations":[],"localized_object_annotations":[],"logo_annotations":[],"text_annotations":[]} | —--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | gs://bq_huron_demo/flowers/daisy/10140303196_b88d3d6cec.jpg | {"face_annotations":[],"label_annotations":[{"confidence":0.0,"description":"Flower","locale":"", | | | "locations":[],"mid":"/m/0c9ph5","properties":[],"score":0.9770426,"topicality":0.9770426}, | | | {"confidence":0.0,"description":"Plant","locale":"","locations":[],"mid":"/m/05s2s", | | | "Properties":[],"score":0.95798975,"topicality":0.95798975},{"confidence":0.0, | | | "description":"Petal","locale":"","locations":[],"mid":"/m/016q19","properties":[], | | | "score":0.88984144,"topicality":0.88984144},{"confidence":0.0,"description":"Yellow", | | | "locale":"","locations":[],"mid":"/m/088fh","properties":[],"score":0.84456813, | | | "Topicality":0.84456813},{"confidence":0.0,"description":"camomile","locale":"", | | | "locations":[],"mid":"/m/011bc8hg","properties":[],"score":0.7926449, "topicality":0.7926449}, | | | {"confidence":0.0,"description":"Annual plant","locale":"","locations":[],"mid":"/m/0jqb", | | | "Properties":[],"score":0.75020844, "topicality":0.75020844},{"confidence":0.0, | | | "description":"Flowering plant","locale":"","locations":[],"mid":"/m/04sjm", | | | "Properties":[],"score":0.7403478,"topicality":0.7403478},{"confidence":0.0, | | | "description":"Chamaemelum nobile","locale":"","locations":[],"mid":"/m/05cmcg", | | | "Properties":[],"score":0.7264577,"topicality":0.7264577},{"confidence":0.0, | | | "description":"Close-up","locale":"","locations":[],"mid":"/m/02cqfm","properties":[], | | | "score":0.721242,"topicality":0.721242},{"confidence":0.0,"description":"Daisy family", | | | "locale":"","locations":[],"mid":"/m/0l5r","properties":[],"score":0.7012979, | | | "Topicality":0.7012979}],"landmark_annotations":[],"localized_object_annotations":[], | | | "logo_annotations":[],"text_annotations":[]} | —------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------
Clean up
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.