Skip to content

Instantly share code, notes, and snippets.

@pentium10

pentium10/Workflow_BQ_get_queries_cost.sql

Created February 9, 2022 19:20
Show Gist options
  • Save pentium10/c0136b61d7f1ab230d1f7c8acdea4fea to your computer and use it in GitHub Desktop.
Save pentium10/c0136b61d7f1ab230d1f7c8acdea4fea to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Workflow_BQ_get_queries_cost.sql
DECLARE var_day STRING DEFAULT '2021-09-09';
SELECT
protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime,
round(5* (protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalProcessedBytes/POWER(2,40) ),2) AS processedBytesCostProjection,
round(5* (protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/POWER(2,40) ),2) AS billedBytesCostInUSD
FROM
`<dataset_auditlogs>.cloudaudit_googleapis_com_data_access_*`
WHERE
_TABLE_SUFFIX >= var_day and protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime>=TIMESTAMP(var_day)
AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName="query_job_completed"
AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalProcessedBytes IS NOT NULL
ORDER BY
protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics. totalProcessedBytes DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment