BigQuery の全テーブルのレコード件数やサイズを保存して、前日からの変化を確認する

BigQuery のテーブル別レコード数を履歴として保存して、その変化を見ることで期待した操作が行われているか(逆に、意図しない操作をしていないか)をチェックする方法を考えました。全体的にどういう変化が起きたのかをざっくり確認したい、というのが目的です。

具体的には、INFORMATION_SCHEMA.PARTITIONS ビュー *1のレコードを履歴として保存して、指定した日とその前日分を比較しています。

ソースコード

github.com

Python スクリプトを実行する方式をとっていますが、レコード操作は SQL でやっています。Jinja パラメータを置換すれば、そのまま使えるはずです。以下では SQL をベースにやっていることを紹介します。

やっていること

データセット一覧の取得

INFORMATION_SCHEMA.SCHEMATA ビューを参照して、データセット一覧を取得します。この一覧をもとに、次の手順で参照する PARTITIONS ビューの対象データセットを指定します。

SELECT
    SCHEMA_NAME
FROM
    region-{{ region }}.INFORMATION_SCHEMA.SCHEMATA

https://github.com/tosh2230/bigquery-table-history/blob/main/src/sql/get_target_datasets.sql

PARTITIONS ビューのレコードを日次保存

データセットごとの INFORMATION_SCHEMA.PARTITIONS ビューから、履歴テーブルである information_schema_partitions_history へ INSERT します。

INFORMATION_SCHEMA.PARTITIONS ビューでは、テーブル内のレコード件数やサイズ、最終更新日などを確認できます。名前のとおり、パーティション別にレコードがつくられています。このビューで参照できるレコードを、CURRENT_TIMESTAMP と共に別テーブルに保存します。これを日次で行います。

INSERT INTO
    {{ history_dataset }}.information_schema_partitions_history
SELECT
    CURRENT_TIMESTAMP() as recorded_at,
    table_catalog,
    table_schema,
    table_name,
    partition_id,
    total_rows,
    total_logical_bytes,
    total_billable_bytes,
    last_modified_time,
    storage_tier
FROM
    {{ target_dataset }}.INFORMATION_SCHEMA.PARTITIONS

https://github.com/tosh2230/bigquery-table-history/blob/main/src/sql/add_records.sql

information_schema_partitions_history テーブル

前日との差分抽出

基準日とその前日のレコード群の対象差 (A△B = (A−B)∪(B−A)) をとります*2。

この結果が、指定した日付に BigQuery で起きた変化です。

パーティションごとに件数を確認できるので、意図せず過去分のレコードに手を加えていた...といった事態に気づけたり、テーブルサイズの推移をチェックしたりできそうです。

WITH today AS (
    SELECT
        latest.*
    FROM (
        SELECT
            table_catalog,
            table_schema,
            table_name,
            partition_id,
            ARRAY_AGG(history ORDER BY history.recorded_at DESC LIMIT 1)[OFFSET(0)] AS latest
        FROM
            {{ history_dataset }}.information_schema_partitions_history AS history
        WHERE
            recorded_at >= TIMESTAMP("{{ base_date }}", "Asia/Tokyo")
            AND recorded_at < TIMESTAMP_ADD(TIMESTAMP("{{ base_date }}", "Asia/Tokyo"), INTERVAL 1 DAY)
            AND table_schema != "{{ history_dataset }}"
            AND table_name != "information_schema_partitions_history"
        GROUP BY
            table_catalog,
            table_schema,
            table_name,
            partition_id
    )
),
yesterday AS (
    SELECT
        latest.*
    FROM (
        SELECT
            table_catalog,
            table_schema,
            table_name,
            partition_id,
            ARRAY_AGG(history ORDER BY history.recorded_at DESC LIMIT 1)[OFFSET(0)] AS latest
        FROM
            {{ history_dataset }}.information_schema_partitions_history AS history
        WHERE
            recorded_at >= TIMESTAMP_SUB(TIMESTAMP("{{ base_date }}", "Asia/Tokyo"), INTERVAL 1 DAY)
            AND recorded_at < TIMESTAMP("{{ base_date }}", "Asia/Tokyo")
            AND table_schema != "{{ history_dataset }}"
            AND table_name != "information_schema_partitions_history"
        GROUP BY
            table_catalog,
            table_schema,
            table_name,
            partition_id
    )
)

SELECT
    FORMAT_TIMESTAMP("%F %T", today.recorded_at, "Asia/Tokyo") AS recorded_at_jst,
    diff.*
FROM (
    SELECT * EXCEPT(recorded_at) FROM today
    EXCEPT DISTINCT
    SELECT * EXCEPT(recorded_at) FROM yesterday
) AS diff
    LEFT OUTER JOIN today ON
        diff.table_catalog = today.table_catalog
        AND diff.table_schema = today.table_schema
        AND diff.table_name = today.table_name
UNION ALL
SELECT
    FORMAT_TIMESTAMP("%F %T", yesterday.recorded_at, "Asia/Tokyo") AS recorded_at_jst,
    diff.*
FROM (
    SELECT * EXCEPT(recorded_at) FROM yesterday
    EXCEPT DISTINCT
    SELECT * EXCEPT(recorded_at) FROM today
) AS diff
    LEFT OUTER JOIN yesterday ON
        diff.table_catalog = yesterday.table_catalog
        AND diff.table_schema = yesterday.table_schema
        AND diff.table_name = yesterday.table_name
ORDER BY
    table_catalog,
    table_schema,
    table_name,
    partition_id,
    recorded_at_jst

https://github.com/tosh2230/bigquery-table-history/blob/main/src/sql/diff_records.sql

*1:INFORMATION_SCHEMA.PARTITIONS ビューは、2023/01/29 時点でプレビューです

*2:正確には、基準日において最後に実行した結果と、前日の最後に実行した結果を比較しています