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

本文說明如何定義 BigQuery 標準資料表結構定義，並使用可儲存 ObjectRef 值的資料欄。

ObjectRef 值提供 Cloud Storage 中物件的中繼資料和連線資訊。如要將非結構化資料整合至標準表格，請使用 ObjectRef 值。舉例來說，在產品資料表中，您可以新增包含 ObjectRef 值的資料欄，將產品圖片與其他產品資訊儲存在同一列。您可以將 ObjectRef 值儲存在使用 ObjectRef 格式的 STRUCT 欄中，也就是 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. 按一下「執行」。

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

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 中物件的 Cloud 資源連線，格式為 location.connection_id。例如：us-west1.myconnection。如要取得連線 ID，請在 Google Cloud 控制台中查看連線詳細資料，然後複製「連線 ID」中顯示的完整連線 ID 最後一個部分的值。例如：projects/myproject/locations/connection_location/connections/myconnection。

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

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

  3. 按一下「執行」。

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

建立 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. 按一下「執行」。

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

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 中物件的 Cloud 資源連線，格式為 location.connection_id。例如：us-west1.myconnection。如要取得連線 ID，請在 Google Cloud 控制台中查看連線詳細資料，然後複製「連線 ID」中顯示的完整連線 ID 最後一個部分的值。例如：projects/myproject/locations/connection_location/connections/myconnection。

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

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

  3. 按一下「執行」。

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

後續步驟