GCP BigQuery resource level costs aggregation

user_3182907 0
Viewed 168

Am working on a solution to get the resource costs from the BigQuery.

We have recently moved to BigQuery implementation for collecting billing information since CSV/JSON has been deprecated for use from GCP.

Though Bigquery provides only SKU level billing information, our application needs to collect the resource level billing information as well.

Usage reports can be exported as a CSV in cloud storage and which contains Measurement id, Resource id and USage units per resource. But, in BigQuery we don't have Measurement id to match with usage reports to get the resource level billing information.

Exported usage report

The billing result from Bigquery is as follows.

Billing result

Needed information on how we can collect the resource level costs along with BigQuery line items.

google-bigquery google-cloud-platform
2 Answers

Following query can give you (user email & total bytes processed ). Since we know Total bytes BQ has processed for last ´N´ days, accordingly we can estimate cost of BQ analysis

 SELECT
   user_email,
   sum(total_bytes_processed)

FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY) 
 AND CURRENT_TIMESTAMP()
 AND job_type = "QUERY"
 AND end_time BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY) AND CURRENT_TIMESTAMP()
group by user_email
user_44952380
user_31829070
Related