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