使用遠端函式分析物件資料表

您可以使用遠端函式分析物件資料表，透過外部工具和程式庫從非結構化資料擷取洞察資訊。將查詢與 Cloud Run 或 Cloud Run functions 整合，即可直接將 Google 預先訓練的 AI 模型、開放原始碼程式庫或自訂模型，套用至 BigQuery 資料。

如要使用遠端函式分析物件資料表資料，您必須在呼叫遠端函式時，為物件資料表中的物件產生並傳入經簽署的網址。這些經簽署的網址會授予遠端函式物件存取權。

所需權限

• 如要建立遠端函式使用的連線資源，您必須具備下列權限：

  • bigquery.connections.create
  • bigquery.connections.get
  • bigquery.connections.list
  • bigquery.connections.update
  • bigquery.connections.use
  • bigquery.connections.delete

• 如要建立遠端函式，您必須具備與 Cloud Functions 開發人員或 Cloud Run 開發人員角色相關聯的權限。

• 如要叫用遠端函式，您必須具備「遠端函式」一文所述的權限。

• 如要使用遠端函式分析物件資料表，您必須具備物件資料表的 bigquery.tables.getData 權限。

事前準備

登入 Google Cloud 帳戶。如果您未曾使用過 Google Cloud，歡迎 建立帳戶，親自體驗實際使用我們產品的成效。新客戶還能獲得價值 $300 美元的免費抵免額，用於執行、測試及部署工作負載。

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 the resourcemanager.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, BigQuery Connection API, Cloud Run functions APIs, if any are not already enabled.

Roles required to enable APIs

To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). 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 the resourcemanager.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, BigQuery Connection API, Cloud Run functions APIs, if any are not already enabled.

Roles required to enable APIs

To enable APIs, you need the serviceusage.services.enable permission. If you created the project, then you likely already have this permission through the Owner role (roles/owner). Otherwise, you can get this permission through the Service Usage Admin role (roles/serviceusage.serviceUsageAdmin). Learn how to grant roles.

Enable the APIs

1. 確認 BigQuery 管理員已建立連線，並設定 Cloud Storage 存取權。

建立遠端函式

如需建立遠端函式的一般操作說明，請參閱「使用遠端函式」。

建立遠端函式來分析物件資料表資料時，您必須傳入為物件資料表中的物件產生的經簽署的網址。方法是使用資料類型為 STRING 的輸入參數。經簽署的網址會以 HTTP POST 要求的 calls 欄位中的輸入資料形式，提供給遠端函式。要求範例如下：

{
  // Other fields omitted.
  "calls": [
    ["https://storage.googleapis.com/mybucket/1.pdf?X-Goog-SignedHeaders=abcd"],
    ["https://storage.googleapis.com/mybucket/2.pdf?X-Goog-SignedHeaders=wxyz"]
  ]
}

您可以使用向經簽署的網址發出 HTTP GET 要求的方法，讀取遠端函式中的物件。遠端函式可以存取物件，因為經簽署的網址的查詢字串中含有驗證資訊。

為遠端函式指定 CREATE FUNCTION 陳述式時，建議將 max_batching_rows 選項設為 1，以避免 Cloud Run functions 逾時，並提高處理平行處理。

範例

下列 Cloud Run functions Python 程式碼範例會讀取儲存空間物件，並將內容長度傳回 BigQuery：

import functions_framework
import json
import urllib.request

@functions_framework.http
def object_length(request):
  calls = request.get_json()['calls']
  replies = []
  for call in calls:
    object_content = urllib.request.urlopen(call[0]).read()
    replies.append(len(object_content))
  return json.dumps({'replies': replies})

部署後，這個函式會有類似 https://us-central1-myproject.cloudfunctions.net/object_length 的端點。

以下範例說明如何根據這個 Cloud Run 函式，建立 BigQuery 遠端函式：

CREATE FUNCTION mydataset.object_length(signed_url STRING) RETURNS INT64
REMOTE WITH CONNECTION `us.myconnection`
OPTIONS(
  endpoint = "https://us-central1-myproject.cloudfunctions.net/object_length",
  max_batching_rows = 1
);

如需逐步指南，請參閱「教學課程：使用遠端函式分析物件資料表」。

呼叫遠端函式

如要對物件資料表資料呼叫遠端函式，請在查詢的 select_list 中參照遠端函式，然後在 FROM 子句中呼叫 EXTERNAL_OBJECT_TRANSFORM 函式，為物件產生已簽署的網址。

注意： 使用 AI API 時，請留意目標 API 的相關配額。如有必要，請使用 LIMIT 子句限制傳回的結果，以免超出配額。

以下範例顯示一般陳述式語法：

SELECT uri, function_name(signed_url) AS function_output
FROM EXTERNAL_OBJECT_TRANSFORM(TABLE my_dataset.object_table, ["SIGNED_URL"])
LIMIT 10000;

以下範例說明如何使用遠端函式，僅處理物件資料表內容的子集：

SELECT uri, function_name(signed_url) AS function_output
FROM EXTERNAL_OBJECT_TRANSFORM(TABLE my_dataset.object_table, ["SIGNED_URL"])
WHERE content_type = "application/pdf";

後續步驟

瞭解如何針對圖片物件資料表執行推論。