Zenn
💰

BigQuery+スケジュールクエリで今月の処理バイト数の合計を定期的に計算する

2022/03/04に公開
SQL
BigQuery
DWH
tech

モチベーション

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