BigQueryの変更履歴へTable-Valued Function(TVF)でアクセス

日本情報通信の中根です。
今回は7月6日にプレビューとなった変更履歴の機能について紹介したいと思います。

どんな機能？

BigQueryのテーブルに対する変更を追跡できる機能です。
この機能はTable-Valued FunctionのAPENDS句として利用することができます。

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

APPENDS句にて変更を記録するのは以下の操作になります。

• CREATE TABLE DDL statement
• INSERT DML statement
• MERGE DML statement
• Loading data into BigQuery
• Streaming ingestion

試してみよう

それではマニュアルに沿って実行して見ましょう。

-- 1. 【CREATE】1レコードのみのテーブル`Produce`の作成
CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS (
  SELECT "apples" AS product, 10 AS inventory);

--2. 【INSERT】2レコードインサート
INSERT INTO mydataset.Produce
VALUES
  ("bananas", 20),
  ("carrots", 30);
-- 3. 【SELECT】TVFによる変更の確認
SELECT
  product,
  inventory,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

ここでは作成時および追加した3行の変更タイプ_CHANGE_TYPEおよび変更日時_CHANGE_TIMESTAMPが表示されています。
続けて操作を行っています。

-- 4. 【ALTER】テーブルに`color`カラムの追加
ALTER TABLE mydataset.Produce ADD COLUMN color STRING;

-- 5. 【INSERT】1レコードインサート
INSERT INTO mydataset.Produce VALUES ("grapes", 40, "purple");

-- 6. 【UPDATE】すべてのレコードの`inventory`カラムの更新
UPDATE mydataset.Produce SET inventory = inventory + 5 WHERE TRUE;

-- 7. 【DELETE】特定レコードの削除
DELETE mydataset.Produce WHERE product = "bananas";

-- 8. 【SELECT】現在のレコードの確認
SELECT * FROM mydataset.Produce;

上記操作後のレコードです。

-- 9. 【SELECT】TVFによる変更の確認
SELECT
  product,
  inventory,
  color,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

そしてTVFにて変更を表示してみたのですが、、、

4, 6, 7にて行われた変更履歴が表示されていません。
先程述べましたが上記の操作は記録対象外のため表示されていません。
Limitationsにも記載がありますが現在情報を確認できるのはINSERTのみということです。
これは少し注意が必要ですね。

また上記では変更履歴期間の開始と終了をNULLとしていますが期間はどうやら7日以内でなければいけないようです。
開始時期を指定せずに7日前以前のテーブルを指定した場合以下のようなエラー文が表示されます。

Table <PROJECT_NUMBERE>:<DATASET>.<TABLE> was created at time which is before its allowed time travel interval 2022-07-12T16:40:20.611Z. Creation time: 2019-10-15T13:58:23.148Z

エラー文からもTime Travel機能を利用していることがわかります。
もちろんTime Travelですので参照期間によってスキャンするデータサイズも変わりますので注意が必要です。

過去7日間のデータ参照時は約50MBのデータスキャン

過去1日間のデータ参照時は約11MBのデータスキャン

テーブルサイズが大きい場合は注意が必要ですね。

まとめ

変更履歴の機能ですが使い方やテーブルの更新方法によっては非常に便利な機能となるかと思います。
単純追記のテーブルやストリーミングインサートがメインのテーブルなどには力を発揮できるでしょう。
一方DELETEやUPDATEについては捕捉していないのでテーブルの更新方法によって向き不向きはあるでしょう。
また注意点としては参照期間によるデータスキャンサイズも注意しましょう。

現在はプレビュー版ですので今後の実装や拡張に期待して行きたいと思います。