Protobuf テキスト形式とは、その使用方法 - PolarDB - Alibaba Cloud ドキュメントセンター

ゲーム業界では、アプリケーションコードが Protobuf を使用してデータをシリアル化し（場合によっては zlib 圧縮を追加で適用し）、BLOB カラムに書き込むことが一般的です。通常、SQL を通じてそのデータを読み取るには、外部の ETL コンポーネントが必要になるか、アプリケーション層で逆シリアル化を行う必要がありますが、これはソフトウェアのデバッグや開発において不便です。PolarDB for MySQL の Protobuf テキスト形式機能により、これらの要件が不要になります。PROTO_TO_JSON() 関数を使用して BLOB データをインラインで JSON に変換し、標準の MySQL JSON 関数（JSON_EXTRACT()、JSON_UNQUOTE()）を使ってフィルター処理、インデックス作成、または仮想カラムの生成をすべて SQL 内で行うことができます。

前提条件

開始する前に、以下の要件を満たしていることを確認してください。

PolarDB for MySQL 8.0 クラスターがリビジョンバージョン 8.0.2.2.5 以降で実行されていること。バージョンを確認するには、「エンジンバージョンの照会」をご参照ください。

仕組み

PROTO_TO_JSON(blob_field) は、Protobuf でシリアル化されたデータを保持する BLOB カラムを受け取り、JSON 文字列を返します。この関数は、生の Protobuf バイトデータと zlib 圧縮された Protobuf バイトデータの両方を自動的に処理します。そのため、圧縮の有無に関わらずクエリを変更する必要はありません。

BLOB データを JSON に変換した後は、標準の MySQL JSON 関数を使用して Protobuf データ内のフィールドをクエリしたり、インデックスを構築したり、仮想カラムを生成したりできます。

列のProtobuf スキーマを設定する

PROTO_TO_JSON() を呼び出す前に、ALTER TABLE ... ALTER COLUMN を使用して BLOB カラムに Protobuf スキーマを関連付けてください。この手順により、PolarDB for MySQL がそのカラム内のバイナリデータを解釈する方法が指定されます。

構文：

ALTER TABLE table_name ALTER COLUMN column_name
  [PROTO_NAME = 'protobuf_schema_name']
  PROTO_TEXT = 'protobuf_schema_definition'
  PROTO_MESSAGE = 'protobuf_message'
  [COMPRESSION = 'zlib']

パラメーター：

パラメーター	必須	説明
`PROTO_NAME`	いいえ	Protobuf スキーマの名前。
`PROTO_TEXT`	はい	Protobuf スキーマの完全な定義。
`PROTO_MESSAGE`	はい	逆シリアル化するトップレベルの Protobuf メッセージタイプ。
`COMPRESSION`	いいえ	カラムに書き込む前にデータが zlib で圧縮されている場合は `zlib` に設定します。非圧縮データの場合は、このパラメーターを省略します。

zlib 圧縮されたデータは、UNCOMPRESS() を使用して手動で解凍することも可能です。この場合、生のバイトデータが 16 進数として返されます。

列から Protobuf スキーマを削除する

スキーマを削除するには、すべてのスキーマパラメーターを空文字列に設定します。

ALTER TABLE table_name ALTER COLUMN column_name
  PROTO_NAME=""
  PROTO_TEXT=""
  PROTO_MESSAGE='';

重要

スキーマを削除する前に、そのカラムがインデックスや仮想カラムから参照されていないことを確認してください。

列のProtobuf スキーマを表示する

セッションでスキーマ表示を有効にします。
```
SET display_readable_proto_info = true;
```
カラム定義を表示します。
```
SHOW columns FROM table_name;
```

使用例

以下の例では、Protobuf コミュニティ提供の addressbook.proto スキーマを使用して、テーブル作成、スキーマ設定、データ挿入、クエリ実行という一連のワークフローを説明します。

すべての例で使用するスキーマ：

syntax = "proto2";

package tutorial;

message Person {
  optional string name = 1;
  optional int32 id = 2;
  optional string email = 3;

  enum PhoneType {
    MOBILE = 0;
    HOME = 1;
    WORK = 2;
  }

  message PhoneNumber {
    optional string number = 1;
    optional PhoneType type = 2 [default = HOME];
  }

  repeated PhoneNumber phones = 4;
}

message AddressBook {
  repeated Person people = 1;
}

ステップ 1：テーブルの作成

CREATE TABLE t1 (c1 INT, c2 BLOB);

c2 は Protobuf でシリアル化されたデータを保持します。

ステップ 2：カラムへのスキーマの関連付け

データが圧縮されているかどうかに応じて、該当する文を選択してください。

zlib 圧縮なしの場合：

ALTER TABLE t1 ALTER COLUMN c2
  PROTO_NAME="AddressBook"
  PROTO_TEXT="syntax = \"proto2\";\n\npackage tutorial;\n\nmessage Person {\n  optional string name = 1;\n  optional int32 id = 2;\n  optional string email = 3;\n\n  enum PhoneType {\n    MOBILE = 0;\n    HOME = 1;\n    WORK = 2;\n  }\n\n  message PhoneNumber {\n    optional string number = 1;\n    optional PhoneType type = 2 [default = HOME];\n  }\n\n  repeated PhoneNumber phones = 4;\n}\n\nmessage AddressBook {\n  repeated Person people = 1;\n}"
  PROTO_MESSAGE='AddressBook';

zlib 圧縮ありの場合：

ALTER TABLE t1 ALTER COLUMN c2
  PROTO_NAME="AddressBook"
  PROTO_TEXT="syntax = \"proto2\";\n\npackage tutorial;\n\nmessage Person {\n  optional string name = 1;\n  optional int32 id = 2;\n  optional string email = 3;\n\n  enum PhoneType {\n    MOBILE = 0;\n    HOME = 1;\n    WORK = 2;\n  }\n\n  message PhoneNumber {\n    optional string number = 1;\n    optional PhoneType type = 2 [default = HOME];\n  }\n\n  repeated PhoneNumber phones = 4;\n}\n\nmessage AddressBook {\n  repeated Person people = 1;\n}"
  PROTO_MESSAGE='AddressBook'
  COMPRESSION='zlib';

ステップ 3：データの挿入

zlib 圧縮なしの場合：

INSERT INTO t1 VALUES(1, X'0a380a0b56697375616c50726f746f10011a1776697375616c70726f746f40706f6c617264622e636f6d220e0a0a313233343536373839301002');

zlib 圧縮ありの場合：

INSERT INTO t1 VALUES(1, X'3C000000785ee3b2e0e20ecb2c2e4dcc0928ca2fc9176094122f03730b405c8782fc9cc4a29424bde4fc5c253e2e2e432363135333730b4b03012600183d10de');

圧縮バイトを検証するには、UNCOMPRESS() を使用して解凍します。

SELECT HEX(UNCOMPRESS(X'3C000000785ee3b2e0e20ecb2c2e4dcc0928ca2fc9176094122f03730b405c8782fc9cc4a29424bde4fc5c253e2e2e432363135333730b4b03012600183d10de')) AS UNCOMPRESS_DATA;

結果：

+----------------------------------------------------------------------------------------------------------------------+
| UNCOMPRESS_DATA                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------+
| 0A380A0B56697375616C50726F746F10011A1776697375616C70726F746F40706F6C617264622E636F6D220E0A0A313233343536373839301002 |
+----------------------------------------------------------------------------------------------------------------------+

ステップ 4：データの読み取りとクエリ

PROTO_TO_JSON() を使わずに BLOB データを読み取る

Protobuf テキスト形式機能を使わずに c2 カラムを直接クエリすると、解釈できないバイナリデータが返されます。

zlib 圧縮なしの場合：

SELECT c2 FROM t1\G

結果：

*************************** 1. row ***************************
c2:
8
VisualProtovisualproto@polardb.com"
1234567890

zlib 圧縮ありの場合：

SELECT c2 FROM t1\G

結果：

*************************** 1. row ***************************
c2: <   x^...

圧縮された出力は直接読み取れないバイナリゴミです。

BLOB データを JSON として読み取る

PROTO_TO_JSON() は圧縮済みデータと非圧縮データの両方に対応しており、クエリを変更する必要はありません。

SELECT PROTO_TO_JSON(c2) FROM t1;

結果：

+------------------------------------------------------------------------------------------------------------------------------------------+
| PROTO_TO_JSON(c2)                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------+
| {"people": [{"id": 1, "name": "VisualProto", "email": "visualproto@polardb.com", "phones": [{"type": "WORK", "number": "1234567890"}]}]} |
+------------------------------------------------------------------------------------------------------------------------------------------+

PROTO_TO_JSON() は zlib 圧縮済みデータと非圧縮データの両方を読み取ることができます。

特定のフィールドを抽出する

JSON_EXTRACT() を使用して、JSON 出力から個別のフィールドを抽出します。

SELECT JSON_EXTRACT(PROTO_TO_JSON(c2), '$.people[0].name') FROM t1;

結果：

+-----------------------------------------------------+
| json_extract(PROTO_TO_JSON(c2), '$.people[0].name') |
+-----------------------------------------------------+
| "VisualProto"                                       |
+-----------------------------------------------------+

Protobuf フィールドに対するインデックスの作成

メールアドレスフィールドに対してファンクショナルインデックスを作成します。

CREATE INDEX i_email ON t1 ((CAST(JSON_UNQUOTE(JSON_EXTRACT(PROTO_TO_JSON(c2), '$.people[0].email')) AS CHAR(100))));

EXPLAIN を実行して、インデックスが使用されていることを確認します。

EXPLAIN SELECT * FROM t1 WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(PROTO_TO_JSON(c2), '$.people[0].email')) AS CHAR(100)) = 'visualproto@polardb.com';

結果：

+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | i_email       | i_email | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

仮想カラムの作成

Protobuf フィールドを通常のカラムとして公開する仮想カラムを生成します。

ALTER TABLE t1 ADD COLUMN c3 VARCHAR(100) AS (JSON_EXTRACT(PROTO_TO_JSON(`c2`), _utf8mb4'$.people[0].email'));

スキーマを確認します。

DESC t1;

結果：

+-------+--------------+------+-----+---------+-------------------+
| Field | Type         | Null | Key | Default | Extra             |
+-------+--------------+------+-----+---------+-------------------+
| c1    | int(11)      | YES  |     | NULL    |                   |
| c2    | blob         | YES  |     | NULL    |                   |
| c3    | varchar(100) | YES  |     | NULL    | VIRTUAL GENERATED |
+-------+--------------+------+-----+---------+-------------------+

c3 は、c2 の Protobuf データに基づく仮想生成カラムです。