Big Query Costs By User

big_query_costs_by_user.sql

/*
# https://cloud.google.com/bigquery/audit-logs#sample_audit_queries_in_bigquery

select   
  protopayload_auditlog.authenticationInfo.principalEmail as user_email, 
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query, 
--   protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.state,
--   protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.code,
--   protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message,
  sum(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes) billedBytes, 
  FORMAT('%9.2f',5.0 * (SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes)/1000000000000)) as Estimated_USD_Cost
from `audit_log.cloudaudit_googleapis_com_data_access_20180610` 
where protopayload_auditlog.methodName = 'jobservice.jobcompleted'
group by 1, 2
order by billedBytes desc
*/

WITH data as
(
  SELECT
    extract(date from timestamp) as jobDate,
    protopayload_auditlog.authenticationInfo.principalEmail as principalEmail,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent as jobCompletedEvent
  FROM
    `audit_log.cloudaudit_googleapis_com_data_access_2018*`
)
SELECT
  jobDate,
  principalEmail,
  FORMAT('%9.2f',5.0 * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/1000000000000)) as Estimated_USD_Cost
FROM
  data
WHERE
  jobCompletedEvent.eventName = 'query_job_completed'
GROUP BY jobDate, principalEmail
ORDER BY Estimated_USD_Cost DESC