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. New Google Cloud users might be eligible for a free trial.

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

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.

   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. Make sure that billing is enabled for your Google Cloud project.

4. Enable the BigQuery, BigQuery Connection API, and Cloud Functions APIs.

   Enable the APIs

5. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   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

6. Make sure that billing is enabled for your Google Cloud project.

7. Enable the BigQuery, BigQuery Connection API, and Cloud Functions APIs.

   Enable the APIs

Create a Cloud Functions function

To create a function, follow these steps:

1. Go to the Cloud Functions page.

   Go to Cloud Functions

2. Click Create function.

3. For Environment, select 2nd gen.

4. For Function name, type vision-ai.

5. Click Next.

6. For Runtime, select Python 3.9.

7. For Entry point, type label_detection.

8. Select main.py. Copy and paste in the following code:

   Python

   import urllib.request
   
   import flask
   import functions_framework
   from google.cloud import vision
   
   
   @functions_framework.http
   def label_detection(request: flask.Request) -> flask.Response:
       """BigQuery remote function to label input images.
       Args:
           request: HTTP request from BigQuery
           https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions#input_format
       Returns:
           HTTP response to BigQuery
           https://cloud.google.com/bigquery/docs/reference/standard-sql/remote-functions#output_format
       """
       try:
           client = vision.ImageAnnotatorClient()
           calls = request.get_json()["calls"]
           replies = []
           for call in calls:
               content = urllib.request.urlopen(call[0]).read()
               results = client.label_detection({"content": content})
               replies.append(vision.AnnotateImageResponse.to_dict(results))
           return flask.make_response(flask.jsonify({"replies": replies}))
       except Exception as e:
           return flask.make_response(flask.jsonify({"errorMessage": str(e)}), 400)
   
   

9. Select requirements.txt. Copy and paste in the following text:

   Flask==2.2.2
   functions-framework==3.5.0
   google-cloud-vision==3.4.2
   Werkzeug==2.3.7
   

10. Click Deploy.

11. When the function has finished deploying, click the Trigger tab.

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

1. Go to the BigQuery page.

   Go to BigQuery

2. In the Editor pane, run the following SQL statement:

   CREATE SCHEMA `PROJECT_ID.remote_function_test`;
   

   Replace PROJECT_ID with your project ID.

bq

1. In the Google Cloud console, activate Cloud Shell.

   Activate Cloud Shell

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

1. Go to the BigQuery page.

   Go to BigQuery

2. Click Add data, and then click External data source.

3. In the Connection type list, select BigLake and remote functions (Cloud Resource).

4. In the Connection ID field, type lake-connection.

5. Click Create connection.

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

1. In Cloud Shell, run the bq mk command to create the connection:

   bq mk --connection --location=us --connection_type=CLOUD_RESOURCE \
   lake-connection
   

2. Run the bq show command to retrieve information about the connection:

   bq show --connection us.lake-connection
   

3. From the properties column, copy the value of the serviceAccountId 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:

1. Go to the IAM & Admin page.

   Go to IAM & Admin

2. Click Grant Access.

   The Add principals dialog opens.

3. In the New principals field, enter the service account ID that you copied earlier.

4. In the Select a role field, select Cloud Run, and then select Cloud Run Invoker.

5. Click Add another role.

6. In the Select a role field, select Cloud Storage, and then select Storage Object Viewer.

7. Click Save.

Note: There can be a delay of up to a minute before new permissions take effect.

Upload the dataset to Cloud Storage

Get the dataset files and make them available in Cloud Storage:

1. Download the flowers dataset to your local machine.
2. 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

1. Go to the BigQuery page.

   Go to BigQuery

2. 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:

1. Go to the BigQuery page.

   Go to BigQuery

2. 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 to https://vision-ai-1abcd2efgh-uc.a.run.app.

   Note: When you specify the CREATE FUNCTION statement for the remote function, we recommend that you set the max_batching_rows option to 1 in order to avoid Cloud Functions timeout and increase processing parallelism.

Call the remote function

Call the label_detection remote function on the sample_images object table:

1. Go to the BigQuery page.

   Go to BigQuery

2. 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;
   

   Tip: This example uses a LIMIT clause to limit the results returned in order to stay within quota for the Cloud Vision API.

   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

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 an appspot.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 the Manage resources page.

   Go to Manage resources

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