背景
BigQueryデータ基盤のコストを下げたい場合を想定。
データエンジニアとしては不要なテーブルを棚卸ししたい。
「この毎日更新されているデータマートは本当に必要なのか？」
「この2年前から更新されていない異常に大きなサイズのテーブルは本当に必要なのか？」
などなかなか棚卸しの判断がつかないことが多い。
前の記事(BigQueryデータ基盤のテーブル依存関係を管理する)同様、今回もBigQueryの INFORMATION_SCHEMA.JOBS_BY_PROJECT を使って、過去180日にどのテーブルが何回実行されたを表すテーブルを作成する。
やる方
以下のSQLの実行結果でテーブル作成するだけ。過去180日に実行されたBigQueryJobから、どのテーブルが何回実行されたかのテーブルを生成する。
WITH base_tables as (
SELECT
job_id,
creation_time,
REGEXP_EXTRACT_ALL(query, "(?:FROM|JOIN)[\\s \\n]+`(.+?)`") as tables
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_type = "QUERY"
AND state = "DONE"
)
SELECT
SPLIT(t, ".")[OFFSET(ARRAY_LENGTH(SPLIT(t, ".")) - 1)] as table_name,
COUNT(creation_time) as reffered_count
FROM base_tables, UNNEST(tables) as t
GROUP BY 1
ORDER BY 2 desc
これで嬉しいこと
コスト削減のため削除したいテーブルがある場合、以下のようなSQLを投げると削除して良さそうなテーブルか確認できる。180日参照されていなかったら削除を検討してもいいと思う。
SELECT * FROM reffered_count_table WHERE table_name LIKE "%the_table_name%"
あとは逆に、このテーブルの参照数が少ないテーブルを棚卸し対象として検討できるのも嬉しい。