BigQuery の変更履歴を取得可能になりました（プレビュー）

ウィスキー、シガー、パイプをこよなく愛する大栗です。

本日はクラスメソッド株式会社の創立記念日である 7 月 7 日ということで皆がたくさんブログを書いているので、乗るしかない、このビッグウェーブにという事でブログネタを探していたら BigQuery の変更履歴が取得できるようになったというアップデートがあったのでご紹介します。

Work with change history

BigQuery の変更履歴は2022年7月7日現在において、プレビューのステータスです。このプロダクトまたは機能は、Google Cloud Platform の利用規約の一般提供前のサービス規約の対象となります。一般提供前のプロダクトと機能では、サポートが制限されることがあります。また、一般提供前のプロダクトや機能に変更が加えられると、他の一般提供前バージョンと互換性がない場合があります。詳細については、リリースステージの説明をご覧ください。

BigQuery の変更履歴

BigQuery の変更履歴は、BigQuery テーブルへの変更を追跡できるようになります。変更履歴はテーブル値関数（TVF）として表現され、指定した時間の範囲に行われた特定の変更を表示します。この機能により、テーブルに加えられた増分の変更を処理できます。変更内容を把握できるので、外部データストアの更新処理で全データ取得し洗い替えを行わずに変更されたデータだけを取得して更新することが可能になります。

APPENDS テーブル値関数（TVF）

APPENDSTVF は指定された時間の範囲でテーブルに追加されたすべての行のテーブルを返します。以下の操作はAPPENDSの変更履歴に行を追加します。

• CREATE TABLE DDL 文
• INSERT DML 文
• MERGE DML 文
• BigQuery へのデータロード
• ストリーミング取り込み

シンタックス

APPENDS(
  TABLE table,
  start_timestamp DEFAULT NULL,
  end_timestamp DEFAULT NULL)

• table : BigQuery のテーブル名です。ビュー、サブクエリ、外部テーブル、マテリアライズド・ビュー、ワイルドカードテーブルは使用できません。
• start_timestamp : 変更が出力される最初の時刻を示すタイムスタンプ。NULL の場合はテーブル作成以降のすべての変更を返します。 start_timestampの後にテーブルが作成された場合は実際のテーブル作成時刻が使用されます。タイムトラベルの範囲より早い時刻の場合はエラーを返します。通常のテーブルの場合は期間は 7 日ですが、タイムトラベル期間を短く設定できます。
• end_timestamp : 変更が出力される最新の時刻を示すタイムスタンプ。NULL の場合はクエリ開始までに行われたすべての変更を含みます。

APPENDSTVF は、クエリ実行時のテーブルのすべての列、_CHANGE_TYPE列（業を生成した変更の種類を示し、INSERTのみサポート）、_CHANGE_TIMESTAMP列（変更を行ったトランザクションのコジット時刻）を持つテーブルを返します。

制限

変更履歴には以下の制限があります。

• 更新や削除ではなく、追加に関する情報のみ表示します。
• データは、テーブルのタイムトラベル期間で制限されます。

やってみる

実際にテーブルを作成してレコードを挿入して、変更履歴を確認してみます。

まずテーブルを作成します。

$ bq query --use_legacy_sql=false \
'CREATE TABLE dataset.sample_table2(
  col1 STRING,
  col2 STRING,
  col3 STRING);'

次にレコードを挿入します。

$ bq query --use_legacy_sql=false \
'INSERT INTO dataset.sample_table
VALUES
  ("10", "Guri / Hajime Oguri", "https://dev.classmethod.jp/author/oguri-hajime/"),
  ("20", "佐々木 大輔", "https://dev.classmethod.jp/author/sasaki-daisuke/");'

内容を確認します。

$ bq query --use_legacy_sql=false \
'SELECT *
FROM dataset.sample_table;'
+------+---------------------+---------------------------------------------------+
| col1 |        col2         |                       col3                        |
+------+---------------------+---------------------------------------------------+
| 20   | 佐々木 大輔         | https://dev.classmethod.jp/author/sasaki-daisuke/ |
| 10   | Guri / Hajime Oguri | https://dev.classmethod.jp/author/oguri-hajime/   |
+------+---------------------+---------------------------------------------------+

この状態で変更履歴を確認してみます。タイムスタンプ型の分解能はマイクロ秒ですが、記録されているデータとしてはミリ秒の模様です。

$ bq query --use_legacy_sql=false \
'SELECT  col1
       ,col2
       ,col3
       ,_CHANGE_TYPE      AS change_type
       ,CAST(_CHANGE_TIMESTAMP AS STRING FORMAT "YYYY-MM-DD HH24:MI:SS.FF6") AS change_timeFROM APPENDS
(TABLE dataset.sample_table, NULL, NULL
);'
+------+---------------------+---------------------------------------------------+-------------+----------------------------+
| col1 |        col2         |                       col3                        | change_type |        change_time         |
+------+---------------------+---------------------------------------------------+-------------+----------------------------+
| 20   | 佐々木 大輔         | https://dev.classmethod.jp/author/sasaki-daisuke/ | INSERT      | 2022-07-07 09:11:04.558000 |
| 10   | Guri / Hajime Oguri | https://dev.classmethod.jp/author/oguri-hajime/   | INSERT      | 2022-07-07 09:11:04.558000 |
+------+---------------------+---------------------------------------------------+-------------+----------------------------+

カラムを追加します。

$ bq query --use_legacy_sql=false \
'ALTER TABLE dataset.sample_table ADD COLUMN col4 STRING;'

もう一行追加します。

$ bq query --use_legacy_sql=false \
'INSERT INTO dataset.sample_table
VALUES
  ("30", "横田 あかり", "https://dev.classmethod.jp/author/akari7/", "329");'

レコードを更新します。

$ bq query --use_legacy_sql=false \
'UPDATE dataset.sample_table SET col2 = "大栗　宗" WHERE col1 = "10";'

レコードを削除します。

$ bq query --use_legacy_sql=false \
'DELETE mydataset.sample_table WHERE col1 = "20";'

この状態の内容を確認します。

$ bq query --use_legacy_sql=false \
'SELECT *
FROM dataset.sample_table;'
+------+-------------+-------------------------------------------------+------+
| col1 |    col2     |                      col3                       | col4 |
+------+-------------+-------------------------------------------------+------+
| 30   | 横田 あかり | https://dev.classmethod.jp/author/akari7/       | 329  |
| 10   | 大栗　宗    | https://dev.classmethod.jp/author/oguri-hajime/ | NULL |
+------+-------------+-------------------------------------------------+------+

変更履歴を確認します。レコードの挿入、更新、削除を行いましたが、変更履歴では挿入のみが出力されます。col1="10"のレコードで col2 を大栗　宗へ変更しましたが、変更履歴では変わらないので注意が必要です。

$ bq query --use_legacy_sql=false \
'SELECT  col1
       ,col2
       ,col3
       ,_CHANGE_TYPE      AS change_type
       ,CAST(_CHANGE_TIMESTAMP AS STRING FORMAT "YYYY-MM-DD HH24:MI:SS.FF6") AS change_timeFROM APPENDS
(TABLE dataset.sample_table, NULL, NULL
);'
+------+---------------------+---------------------------------------------------+------+-------------+----------------------------+
| col1 |        col2         |                       col3                        | col4 | change_type |        change_time         |
+------+---------------------+---------------------------------------------------+------+-------------+----------------------------+
| 30   | 横田 あかり         | https://dev.classmethod.jp/author/akari7/         | 329  | INSERT      | 2022-07-07 09:33:40.009000 |
| 20   | 佐々木 大輔         | https://dev.classmethod.jp/author/sasaki-daisuke/ | NULL | INSERT      | 2022-07-07 09:11:04.558000 |
| 10   | Guri / Hajime Oguri | https://dev.classmethod.jp/author/oguri-hajime/   | NULL | INSERT      | 2022-07-07 09:11:04.558000 |
+------+---------------------+---------------------------------------------------+------+-------------+----------------------------+

この様に BigQuery の変更履歴を取得できました。

さいごに

会社の創立記念日ということでブログネタを探していましたが、BigQuery の重要そうなアップデートがあったので早速試しました。変更履歴がテーブルとして取得できるため、一般的な ETL/ELT　ツールでデータを取得でき別のデータストアへデータ連携するときに差分だけ取得することも可能になりそうです。更新や削除は記録されないので注意が必要ですが、活用の幅が広そうな機能なので是非試して頂ければと思います。