唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryでクエリ課金額が多いユーザーを通知するbotを作ったら富豪的解決がなされた

qiita.com
上の記事でBigQueryに投げたクエリのログ(Audit Log)がインポートできることを知ったので、会社で使われているBigQueryのアカウントについて毎日クエリの課金額(処理したバイト数)の多いユーザーをSlackに通知してみた

ちなみにAudit Logsをインポートする設定方法はこのあたり

以下のクエリで計算している
クエリ課金の単位のTBがテラバイト\(1000^4\)なのかテビバイト\(1024^4\)なのかよくわからなかったので、とりあえず少なめに見えて人間に優しい(?)\(1024^4\)で1日何ドルかを計算している
(追記: 2017-07-01) Audit Logsのテーブル構造が変わっていたのでクエリを更新しました

SELECT
  protopayload_auditlog.authenticationInfo.principalEmail AS user,
  ROUND(SUM(((protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes * (5 * protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.billingTier)) / 1024 / 1024 /1024 /1024)), 2) AS dollars,
FROM TABLE_DATE_RANGE([AuditLogのデータセット名.cloudaudit_googleapis_com_data_access_], DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))
WHERE protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = 'query_job_completed'
GROUP EACH  BY user
ORDER BY dollars DESC
LIMIT 3

このbotを作った結果としてみんなクエリの効率を気にしてくれるかなと思ったけど、毎月のBigQueryの費用の予算を増やすという富豪的解決がなされた

古いテーブル構造のときのクエリ

SELECT
  protoPayload.authenticationInfo.principalEmail AS user,
  ROUND(SUM(((protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes * (5 * protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier)) / 1024 / 1024 /1024 /1024)), 2) AS dollars,
FROM TABLE_DATE_RANGE(AuditLogのデータセット名.cloudaudit_googleapis_com_data_access_, DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))
WHERE protoPayload.serviceData.jobCompletedEvent.eventName = 'query_job_completed'
GROUP EACH  BY user
ORDER BY dollars DESC
LIMIT 3