查詢 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 定義的下列描述元集：

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"
}

查詢範例

以下範例說明如何使用 GoogleSQL 查詢 Bigtable 和 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;

搭配 BigQuery 外部資料表使用

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

以下是資料表定義檔的範例，用於建立 BigQuery 外部資料表 (以 Music 資料表為基礎)：

{
    "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 中，以 JSON 欄的形式查詢 protobuf 資料。

下列查詢會從 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;

後續步驟