BigQueryのINFORMATION_SCHEMAにTABLE_STORAGE_USAGE_TIMELINEが追加されました

hanzawa.yuya

2023.11.04

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

Google Cloudデータエンジニアのはんざわです。
2023年11月1日のアップデートでINFORMATION_SCHEMAにTABLE_STORAGE_USAGE_TIMELINEが新たにプレビューとして追加されました。

これによりBigQueryのストレージ容量の確認が容易になると思います。
早速詳細を確認してみましょう。

概要

TABLE_STORAGE_USAGE_TIMELINEから標準テーブルやマテビューなどのテーブルの過去90日間のストレージ使用量の日次合計を確認することができます。
他のINFORMATION_SCHEMAと同様にregion-REGIONの形式でリージョンを指定する必要があります。

例）

SELECT
  *
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_PROJECT

必要な権限

TABLE_STORAGE_USAGE_TIMELINEを確認するためには以下の2つの権限が必要です。

bigquery.tables.get
bigquery.tables.list

参照元：required_permissions

カラム一覧

TABLE_STORAGE_USAGE_TIMELINEから取得可能はカラムは以下のとおりです。

カラム名	型	詳細
USAGE_DATE	DATE	表示されているバイトの取得日
PROJECT_ID	STRING	データセットを含むプロジェクトのID
TABLE_CATALOG	STRING	PROJECT_IDと同じ？
PROJECT_NUMBER	INT64	データセットを含むプロジェクトの番号
TABLE_SCHEMA	STRING	データセットの名前
TABLE_NAME	STRING	テーブル名
BILLABLE_TOTAL_LOGICAL_USAGE	INT64	合計論理バイト数（MB/s）。物理ストレージ課金モデルを使用している場合は0を返す。
BILLABLE_ACTIVE_LOGICAL_USAGE	INT64	アクティブな論理バイト数（MB/s）。物理ストレージ課金モデルを使用している場合は0を返す。
BILLABLE_LONG_TERM_LOGICAL_USAGE	INT64	長期の論理バイト数（MB/s）。物理ストレージ課金モデルを使用している場合は0を返す。
BILLABLE_TOTAL_PHYSICAL_USAGE	INT64	合計物理バイト数（MB/s）。フェイルセーフとタイムトラベルストレージも含まれる。論理ストレージ課金モデルを使用している場合は0を返す。
BILLABLE_ACTIVE_PHYSICAL_USAGE	INT64	アクティブな物理バイト数（MB/s）。フェイルセーフとタイムトラベルストレージも含まれる。論理ストレージ課金モデルを使用している場合は0を返す。
BILLABLE_LONG_TERM_PHYSICAL_USAGE	INT64	長期の物理バイト数（MB/s）。フェイルセーフとタイムトラベルストレージも含まれる。論理ストレージ課金モデルを使用している場合は0を返す。

参照元：schema

自分が確認した際には、USAGE_DATEに時間単位パーティションは切られていないようでした。

日付分割なし

$ bq query \
--use_legacy_sql=false \
--dry_run \
'SELECT
  *
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_PROJECT
'

> Query successfully validated. Assuming the tables are not modified, running this query will process upper bound of 30042446 bytes of data.

日付分割あり

$ bq query \
--use_legacy_sql=false \
--dry_run \
'SELECT
  *
FROM
  `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_PROJECT
WHERE
  USAGE_DATE >= "2023-11-01"
'

> Query successfully validated. Assuming the tables are not modified, running this query will process upper bound of 30042446 bytes of data.

日付分割した場合とそうでない場合でドライランの見積もり結果は同じでした。

利用ケース

利用できそうなケースを考察してみました。

データセット単位での日毎のデータ増加量

下記は対象のデータセットにおける日毎の物理ストレージ容量の増加量を導入するクエリです。
少しアレンジを加えるだけでデータセット単位ではなく、テーブル単位に変更したり、日毎ではなく基準日からの増加量に変更したりすることも可能です。

SELECT
  usage_date,
  billable_total_physical_usage_by_dataset - FIRST_VALUE(billable_total_physical_usage_by_dataset) 
  OVER (
    ORDER BY usage_date
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS diff_billable_total_physical_usage_by_dataset
FROM
  (
    SELECT
      usage_date,
      SUM(billable_total_physical_usage) AS billable_total_physical_usage_by_dataset,
    FROM
      `region-asia-northeast1`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_PROJECT
    WHERE
      table_schema = '対象のデータセット名'
    GROUP BY usage_date
  )
ORDER BY usage_date

他にも公式ドキュメントに3つほどサンプルが紹介されていたので確認してみてください。

サンプル：examples