查詢 protobuf 資料

本文提供常見查詢模式的範例，說明如何讀取及查詢儲存在 Bigtable 中的通訊協定緩衝區 (protobuf) 資料。

閱讀本頁面前，請先熟悉下列內容：

範例資料

以下範例使用 Music 資料表，儲存專輯和藝人的相關資訊。資料會儲存在名為 album_details 的資料欄系列中，並具有資料欄限定詞 album。資料欄限定詞包含 protobuf 訊息。

protobuf 結構定義定義於下列 proto 檔案：

  • artist.proto：

    syntax = "proto3";

package package_name;

message Artist {
 string name = 1;
}

  • album.proto：

    syntax = "proto3";

package package_name;

import "artist.proto";

message Album {
 string title = 1;
 Artist artist = 2;
 int32 release_year = 3;
}

因此，Bigtable 會為這個資料表建立結構定義套件，其中包含下列 protobuf 檔案描述元集，用於這些 protobuf 定義：

file {
  name: "artist.proto"
  package: "package_name"
  message_type {
    name: "Artist"
    field {
      name: "name"
      number: 1
      label: LABEL_OPTIONAL
      type: TYPE_STRING
      json_name: "name"
    }
  }
  syntax: "proto3"
}
file {
  name: "album.proto"
  package: "package_name"
  dependency: "artist.proto"
  message_type {
    name: "Album"
    field {
      name: "title"
      number: 1
      label: LABEL_OPTIONAL
      type: TYPE_STRING
      json_name: "title"
    }
    field {
      name: "artist"
      number: 2
      label: LABEL_OPTIONAL
      type: TYPE_MESSAGE
      type_name: ".package_name.Artist"
      json_name: "artist"
    }
    field {
      name: "release_year"
      number: 3
      label: LABEL_OPTIONAL
      type: TYPE_INT32
      json_name: "releaseYear"
    }
  }
  syntax: "proto3"
}

查詢範例

下列範例說明如何使用 Bigtable 的 GoogleSQL 和 BigQuery 外部資料表查詢 protobuf 資料。

將資料欄轉換為 protobuf 訊息

您可以使用 CAST 運算子，將 BYTES 值解讀為 protobuf 訊息。如要這麼做，您必須以下列格式提供 protobuf 訊息的全名：SCHEMA_BUNDLE_ID.FULLY_QUALIFIED_MESSAGE_NAME。

更改下列內容：

  • SCHEMA_BUNDLE_ID：建立結構定義套件時指派的專屬 ID。
  • FULLY_QUALIFIED_MESSAGE_NAME：訊息的全名，必須包含 proto 檔案中定義的套件名稱，例如 package_name.message_name。

下列查詢範例會將 album 欄轉換為 Album protobuf 訊息。這則訊息是在 package_name 套件中定義，屬於名為 bundle_name 的結構定義套件：

SELECT
  CAST(album_details['album'] AS bundle_name.package_name.Album).title
FROM
  Music;

存取巢狀欄位

您可以使用點標記法，存取 protobuf 訊息中的巢狀欄位。

下列查詢會從 Album 訊息中巢狀結構的 Artist 訊息擷取藝人名稱：

SELECT
  CAST(album_details['album'] AS bundle_name.package_name.Album).artist.name
FROM
  Music;

根據 protobuf 欄位篩選

您可以使用 WHERE 子句，根據 protobuf 訊息中的欄位值篩選資料列。

下列查詢會選取藝人 Dana A. 的所有專輯：

SELECT
  *
FROM
  Music
WHERE
  CAST(album_details['album'] AS bundle_name.package_name.Album).artist.name = 'Dana A.';

匯總 protobuf 欄位

您可以在 protobuf 訊息中的數值欄位上使用 SUM、AVG、MIN、MAX 和 COUNT 等匯總函式。

下列查詢會計算資料表中所有專輯的平均發行年份：

SELECT
  AVG(CAST(album_details['album'] AS bundle_name.package_name.Album).release_year)
FROM
  Music;

依 protobuf 欄位排序

您可以使用 ORDER BY 子句，根據 protobuf 訊息中的欄位排序結果集。

下列查詢會擷取所有專輯，並依發行年份遞減排序：

SELECT
  *
FROM
  Music
ORDER BY
  CAST(album_details['album'] AS bundle_name.package_name.Album).release_year DESC;

與檢視表搭配使用

您可以建立持續性 materialized view 或邏輯檢視表，讀取 protobuf 資料。

下列指令會建立邏輯檢視表，擷取 Album 訊息中的所有欄位：

QUERY="SELECT _key, CAST(album_details['album'] AS bundle_name.package_name.Album).* FROM Music"

gcloud bigtable logical-views create LOGICAL_VIEW_ID \
    --instance=INSTANCE_ID \
    --query=$QUERY

更改下列內容：

  • LOGICAL_VIEW_ID：參照結構定義套件的邏輯檢視表 ID。
  • INSTANCE_ID：用於建立結構定義套件的執行個體 ID。

如果檢視表參照結構定義套件，您必須先刪除檢視表，才能刪除套件。此外，請勿將結構定義套件更新為與現有檔案描述元不回溯相容的新 protobuf 檔案描述元。

與 BigQuery 外部資料表搭配使用

您可以建立外部資料表，透過 BigQuery 查詢儲存在 Bigtable 中的 protobuf 資料。建立外部資料表時，請將資料欄類型指定為 JSON，編碼指定為 PROTO_BINARY，並與結構定義套件建立關聯。這項程序會將 protobuf 訊息位元組轉換為對等的 JSON 資料，讓您直接查詢其欄位。

以下是資料表定義檔案範例，用於在 Music 資料表上建立 BigQuery 外部資料表：

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID/tables/Music"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "album_details",
                "columns": [
                    {
                        "qualifierString": "album",
                        "type": "JSON",
                        "encoding": "PROTO_BINARY",
                        "protoConfig": {
                            "schemaBundleId": "bundle_name",
                            "protoMessageName": "package_name.Album"
                        }
                    }
                ]
            }
        ]
    }
}

建立外部資料表後，您可以在 BigQuery 中，將 protobuf 資料當做 JSON 資料欄查詢。

下列查詢會從 BigQuery 外部資料表擷取 2022 年發行的所有專輯名稱：

SELECT JSON_VALUE(value, '$.title') AS title
FROM
`PROJECT_ID.DATASET.TABLE_NAME` AS t,
UNNEST(t.album_details.album.cell)
WHERE INT64(JSON_EXTRACT(value, '$.releaseYear')) = 2022;

後續步驟