過去７日間で最も費用がかかったクエリを抽出する

most_expensive_query.sql

  SELECT
    protoPayload.authenticationInfo.principalEmail AS email,
    ROUND(SUM(protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes) /1000000000, 3) AS total_GBytes,
    protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier AS billing_tier,
    COUNT(protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query) AS query_count,
    protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query AS query_string,
  FROM
    TABLE_DATE_RANGE(AuditLogs.cloudaudit_googleapis_com_data_access_, DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP())
  WHERE
    protoPayload.serviceData.jobCompletedEvent.eventName = 'query_job_completed'
  GROUP BY
    email,
    billing_tier,
    query_string
  ORDER BY
    total_GBytes DESC
  LIMIT 10