💰
BigQuery+スケジュールクエリで今月の処理バイト数の合計を定期的に計算する
モチベーション
BigQueryに必要な料金は大きく分けて、クエリ処理とストレージ保存の2つです。
クエリ処理はオンデマンド料金と定額料金がありますが、今回の記事はオンデマンド料金を想定しています。
オンデマンド料金の仕組みとしては各クエリによって処理されたバイト数によって課金されます。
毎月1TBまで無料で、超えると1TBあたり$5の課金になります(USリージョンの場合)
この処理バイト数を定期的に計算して可視化したいと思い、やり方を調べたのでメモを残します。
BigQuery公式で提供されているモニタリング機能で、処理バイト数の項目があったのですが、
上手く表示がされなかったのと、クエリの方が汎用性は高そうだと思い、クエリで実装する方式にしています。
クエリを書く
DECLARE
first_date,
end_date date;
SET
first_date = DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH);
SET
end_date = DATE_ADD(DATE_TRUNC(CURRENT_DATE('Asia/Tokyo'), MONTH), INTERVAL 1 MONTH);
SELECT
first_date,
end_date,
SUM(total_bytes_processed) / 1024 / 1024 / 1024 AS total_giga_bytes_processed
FROM
`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
DATETIME(creation_time,
'Asia/Tokyo') BETWEEN first_date
AND end_date
AND total_bytes_processed IS NOT NULL
ポイントとしては以下になります。
- creation_timeがTIMESTAMP型なので、DATETIMEに変換して比較する
- タイムゾーンはAsia/Tokyoに統一
- total_bytes_processedはバイト単位なので、1TBとの比較がしやすいようにGB単位に変換
場合によっては組織全体での処理バイト数を知りたいときもあると思いますが、
その場合はINFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
を使いましょう。
今回はプロジェクト内で十分だったので、INFORMATION_SCHEMA.JOBS_BY_PROJECT
を使っています。ちなみに実行にはプロジェクトのbigquery.jobs.listAll
権限が必要になります。
スケジュールクエリを設定する
こんな感じで毎日定期実行するスケジュールクエリを設定して保存します。
スケジュールクエリを利用するには認証が必要になりますが、こちらはサービスアカウント経由で認証するように設定します。
次にやりたいこと
これで今月BigQueryで処理したバイト数を確認することが出来るようになりました。
ただまだ以下のようにやりたいことは沢山あります。
- Slack連携させてBigQueryの内容を通知で飛ばしたい
- バイト数を年月で蓄積させたい
- 1TB超えた場合の金額を計算したい
- データポータルに可視化したい
- クエリだけでなくストレージ保存料金の方も確認したい
次回以降はこの辺の改善を加えていきたいと思います。
Discussion