在資料表結構定義中指定 ObjectRef 欄

本文件說明如何定義 BigQuery 標準資料表結構定義，其中包含可儲存 ObjectRef 值的資料欄。

ObjectRef 值會提供 Cloud Storage 中物件的中繼資料和連結資訊。如需將非結構化資料整合至標準表格，請使用 ObjectRef 值。舉例來說，在產品資料表中，您可以新增含有 ObjectRef 值的資料欄，將產品圖片儲存在與其他產品資訊相同的資料列中。您可以在使用 ObjectRef 格式的 STRUCT 欄中儲存 ObjectRef 值，也就是 STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>。

如要進一步瞭解如何使用多模態資料，請參閱「分析多模態資料」。如需瞭解如何處理 ObjectRef 資料的教學課程，請參閱「使用 SQL 分析多模態資料」。如要瞭解如何在 Python 中使用多模態資料，請參閱「使用 BigQuery DataFrames 在 Python 中分析多模態資料」。

事前準備

如要在標準資料表中填入及更新 ObjectRef 值，資料表必須包含 STRING 欄，其中包含相關 Cloud Storage 物件的 URI 資訊。

您必須擁有 Cloud Storage 值區，其中包含目標標準資料表 URI 資料中所識別的相同物件。如果您想使用物件表在標準資料表中維護 ObjectRef 值，就必須同時擁有代表該儲存桶中物件的物件表。

維持 ObjectRef 值

您可以使用物件資料表，在標準資料表中填入及更新 ObjectRef 值。如果您是預覽版的許可清單成員，則您建立的任何物件資料表都會有一個 ref 欄，其中包含指定物件的 ObjectRef 值。您可以使用物件 URI 將標準資料表與物件資料表彙整，以便填入及更新 ObjectRef 值。我們建議您採用這種做法來提升可擴充性，因為這樣就不必從 Cloud Storage 擷取物件中繼資料。

如果您不想建立物件表格，可以使用 OBJ.FETCH_METADATA 和 OBJ.MAKE_REF 函式，直接從 Cloud Storage 擷取物件中繼資料，藉此填入及更新 ObjectRef 值。這個方法可能不太具擴充性，因為需要從 Cloud Storage 擷取物件中繼資料。

建立 ObjectRef 欄

如要在標準資料表中建立並填入 ObjectRef 欄，請選取下列其中一個選項：

物件資料表

根據物件資料表 ref 欄中的資料，建立並填入 ObjectRef 欄：

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往 BigQuery

  2. 在查詢編輯器中輸入以下陳述式：

    CREATE OR REPLACE TABLE PROJECT_ID.DATASET_ID.TABLE_NAME
AS
SELECT TABLE_NAME.*, OBJECT_TABLE.ref AS objectrefcolumn
FROM DATASET_ID.TABLE_NAME
INNER JOIN DATASET_ID.OBJECT_TABLE
ON OBJECT_TABLE.uri = TABLE_NAME.uri;

    請依指示取代下列項目：

    • PROJECT_ID：您的專案 ID。如果您是在目前專案中建立資料表，可以略過這個引數。
    • DATASET_ID：您要建立的資料集 ID。
    • TABLE_NAME：您要重新建立的標準資料表名稱。
    • OBJECT_TABLE：物件資料表的名稱，其中包含要整合至標準資料表的物件資料。

  3. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

SQL 函式

根據 OBJ.FETCH_METADATA 和 OBJ.MAKE_REF 函式的輸出結果，建立並填入 ObjectRef 欄：

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往 BigQuery

  2. 在查詢編輯器中輸入以下陳述式：

    CREATE OR REPLACE TABLE PROJECT_ID.DATASET_ID.TABLE_NAME
AS
SELECT TABLE_NAME.*,
OBJ.FETCH_METADATA(OBJ.MAKE_REF(uri, 'CONNECTION_ID')) AS objectrefcolumn
FROM DATASET_ID.TABLE_NAME;

    請依指示取代下列項目：

    • PROJECT_ID：您的專案 ID。如果您是在目前專案中建立資料表，可以略過這個引數。
    • DATASET_ID：您要建立的資料集 ID。
    • TABLE_NAME：您要重新建立的標準資料表名稱。
    • CONNECTION_ID：包含雲端資源連線的 STRING 值，服務可使用這項連線存取 Cloud Storage 中的物件，格式為 location.connection_id。例如：us-west1.myconnection。您可以前往 Google Cloud 控制台查看連線詳細資料，然後複製「連線 ID」中顯示的完整限定連線 ID 最後一個部分的值。例如：projects/myproject/locations/connection_location/connections/myconnection。

      您必須在使用連線存取物件的任何 Cloud Storage bucket 中，將 Storage 物件使用者 (roles/storage.objectUser) 角色授予連線的服務帳戶。

      連線必須與您呼叫函式的查詢位於相同專案和區域。

  3. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

建立 ARRAY<ObjectRef> 欄

您可以建立 ARRAY<STRUCT<uri STRING, version STRING, authorizer STRING, details JSON>> 資料欄，用於包含 ObjectRef 值的陣列。舉例來說，您可以將影片分割成個別圖片，然後將這些圖片儲存為 ObjectRef 值的陣列。

您可以使用 ARRAY_AGG 函式匯總 ObjectRef 值的陣列，包括在必要時使用 ORDER BY 子句保留物件順序。您可以使用 UNNEST 運算子將 ObjectRef 值陣列解析為個別的 ObjectRef 值，包括在必要時使用 WITH OFFSET 子句來保留物件順序。您可以使用物件中繼資料 (例如 URI 路徑和物件檔案名稱)，將代表物件區塊的 ObjectRef 值對應至代表原始物件的 ObjectRef 值。

如要查看如何處理 ObjectRef 值陣列的範例，請參閱「使用 SQL 分析多模態資料」教學課程的「使用 ARRAY<ObjectRef> 值處理排序的多模態資料」一節。

更新 ObjectRef 欄

如要更新標準資料表中的 ObjectRef 欄，請選取下列其中一個選項：

物件資料表

使用物件資料表 ref 欄中的資料更新 ObjectRef 欄：

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往 BigQuery

  2. 在查詢編輯器中輸入以下陳述式：

    UPDATE PROJECT_ID.DATASET_ID.TABLE_NAME
SET objectrefcolumn = (SELECT ref FROM DATASET_ID.OBJECT_TABLE WHERE OBJECT_TABLE.uri = TABLE_NAME.uri)
WHERE uri != "";

    請依指示取代下列項目：

    • PROJECT_ID：您的專案 ID。如果您是在目前專案中建立資料表，可以略過這個引數。
    • DATASET_ID：您要建立的資料集 ID。
    • TABLE_NAME：您要重新建立的標準資料表名稱。
    • OBJECT_TABLE：物件表格名稱，該物件表格包含與標準表格 ObjectRef 欄相同的物件資料。

  3. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

SQL 函式

使用 OBJ.FETCH_METADATA 和 OBJ.MAKE_REF 函式的輸出結果更新 ObjectRef 欄：

  1. 前往 Google Cloud 控制台的「BigQuery」頁面。

    前往 BigQuery

  2. 在查詢編輯器中輸入以下陳述式：

    UPDATE PROJECT_ID.DATASET_ID.TABLE_NAME
SET objectrefcolumn = (SELECT OBJ.FETCH_METADATA(OBJ.MAKE_REF(uri, 'CONNECTION_ID')))
WHERE uri != "";

    請依指示取代下列項目：

    • PROJECT_ID：您的專案 ID。如果您是在目前專案中建立資料表，可以略過這個引數。
    • DATASET_ID：您要建立的資料集 ID。
    • TABLE_NAME：您要重新建立的標準資料表名稱。
    • CONNECTION_ID：包含雲端資源連線的 STRING 值，服務可使用這項連線存取 Cloud Storage 中的物件，格式為 location.connection_id。例如：us-west1.myconnection。您可以前往 Google Cloud 控制台查看連線詳細資料，然後複製「連線 ID」中顯示的完整限定連線 ID 最後一個部分的值。例如：projects/myproject/locations/connection_location/connections/myconnection。

      您必須在使用連線存取物件的任何 Cloud Storage bucket 中，將 Storage 物件使用者 (roles/storage.objectUser) 角色授予連線的服務帳戶。

      連線必須與您呼叫函式的查詢位於相同專案和區域。

  3. 按一下 「Run」。

如要進一步瞭解如何執行查詢，請參閱「執行互動式查詢」一文。

後續步驟