How can I get BigQuery cost per query per user?
Author: Omid Vahdaty 19.9.2019
How Can I monitor the costs breakdown in GCP BigQuery?
In order to be able to get costs for jobs in a particular table, you can use BigQuery Audit Logs . You can create a BigQuery logging sink and then, you can query the logs table to get cost breakdowns.
Attached links to the official examples, by GCP.
How to parse the query logs data from StackDriver?
After you created the loggin sink, you will get an unpartitioned table called:
cloudaudit_googleapis_com_data_access_*
The table is highly nested, attached quick snippet to parse out costly select queries:
select resource.labels.project_id as project_id, protopayload_auditlog.authenticationInfo.principalEmail as user, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime as startTime, cast(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime as date) as date, protopayload_auditlog.requestMetadata.callerSuppliedUserAgent as userAgent, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query. query as query, protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes/1024/1024/1024/1024*5 as cost from `MyDataSet.cloudaudit_googleapis_com_data_access_*`
Note this query is also committed in our Big Data Demystified GitHub
Why should I monitor Costs in BigQuery?
- Because sometimes you will find scheduled queries which should not run anymore.
- Sometimes the ETL costs much more than you think.
- Determent ROI on using pay as you go services flat rate.
——————————————————————————————————————————
I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way.
If you have any comments, thoughts, questions, or you need someone to consult with,
feel free to contact me via LinkedIn:
1 thought on “How can I get BigQuery cost per query per user?”