How to query BigQuery usage with v2 of the StackDriver API?

Question

I planned to track BigQuery usage using the StackDriver API and there is a nice tutorial on setting this up here. However, the queries here use the deprecated v1 of the API and the available documentation is also still for v1.

In the BigQuery console, v2 only provides the following fields:

logName
timestamp
severity
insertId
resource
resource.type
resource.labels
resource.labels.project_id
httpRequest
httpRequest.requestMethod
httpRequest.requestUrl
httpRequest.requestSize
httpRequest.status
httpRequest.responseSize
httpRequest.userAgent
httpRequest.remoteIp
httpRequest.serverIp
httpRequest.referer
httpRequest.cacheHit
httpRequest.cacheValidatedWithOriginServer
httpRequest.latency
httpRequest.cacheLookup
httpRequest.cacheFillBytes
operation
operation.id
operation.producer
operation.first
operation.last
protopayload_google_cloud_audit_auditlog
protopayload_google_cloud_audit_auditlog.serviceName
protopayload_google_cloud_audit_auditlog.methodName
protopayload_google_cloud_audit_auditlog.resourceName
protopayload_google_cloud_audit_auditlog.numResponseItems
protopayload_google_cloud_audit_auditlog.status
protopayload_google_cloud_audit_auditlog.status.code
protopayload_google_cloud_audit_auditlog.status.message
trace
sourceLocation  
sourceLocation.file 
sourceLocation.line 
sourceLocation.function

There is now no totalBilledBytes field, which was used in the example v1 queries to calculate usage. How can usage cost be queried using v2 of the API?

Answer 1

There is a good tutorial for querying the audit logs to obtain this information. Here's an example query from there (assumes you've exported your audit log data back to BigQuery already).

SELECT
  query_date,
  ROUND(((total_bytes*5)/1000000000000),2) Cost_In_Dollars
FROM (
  SELECT
    STRFTIME_UTC_USEC(metadata.timestamp,"%Y-%m-%d") AS query_date,
    SUM(protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes) AS total_bytes
  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
    query_date )