CODEWITHSUNDEEP
google-bigquery
goose
goose

Reputation: 2652

Confirmation of how to calculate bigquery query costs

I want to double check what I need to look at when assessing query costs for BigQuery. I've found the quoted price per TB here which says $5 per TB, but for precisely 1 TB of what? I have been assuming up until now (before it seemed to matter) that the relevant number would be that which the BigQuery UI outputs above the results, so for this example query:

BigQuery UI

...in this case 2.34GB. So as a fraction of a terabyte and multiplied by $5 this would cost around 1.2cents assuming I'd used up my allowance for the month.

Can anyone confirm that I'm correct? Checking this before I process something I think could rack up some non-negligible costs for once. I should say I've never been stung with a sizeable BigQuery bill before it seems difficult to do.

Upvotes: 2

Views: 6184

Answers (2)

user3330284
user3330284

Reputation: 383

I know I am late but this might help you.

If you are pushing your audit logs to another dataset, you can do below on that dataset. 

  WITH data as
  (
    SELECT
      protopayload_auditlog.authenticationInfo.principalEmail as principalEmail,
      protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent AS jobCompletedEvent
    FROM
      `administrative-audit-trail.gcp_audit_logs.cloudaudit_googleapis_com_data_access_20190227`
  )
  SELECT
    principalEmail,
    FORMAT('%9.2f',5.0 * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/POWER(2, 40))) AS Estimated_USD_Cost
  FROM
    data
  WHERE
    jobCompletedEvent.eventName = 'query_job_completed'
  GROUP BY principalEmail
  ORDER BY Estimated_USD_Cost DESC

Reference: https://cloud.google.com/bigquery/docs/reference/auditlogs/

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Can anyone confirm that I'm correct?

Confirmed

Please note - BigQuery UI in fact uses DryRun which only estimates Total Bytes Processed . The final cost is based on Bytes Billed which reflects some nuances - minimum 10MB per each table involved in query as an example. You can see more details here - https://cloud.google.com/bigquery/pricing#on_demand_pricing

Upvotes: 3

Related Questions