背景

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%"

あとは逆に、このテーブルの参照数が少ないテーブルを棚卸し対象として検討できるのも嬉しい。

参照

「DeNA データプラットフォームにおける自由と統制のバランス」