6

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?

hoju
  • 28,392
  • 37
  • 134
  • 178

1 Answers1

0

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 )
Michael Diamond
  • 134
  • 2
  • how about billing tier? is it already accounted in totalBilledBytes? – Mikhail Berlyant Jan 13 '17 at 21:53
  • yep that's the tutorial I linked to in the question, and as mentioned it uses the deprecated version 1 of the API – hoju Jan 14 '17 at 05:49
  • @MikhailBerlyant for example in version 2 the "metadata.timestamp" field is now just "timestamp" and "totalBilledBytes" no longer exists – hoju Jan 16 '17 at 02:46
  • I checked with the StackDriver logging API team, and the BigQuery audit logs are not included in the v2 migration, so they are not deprecated and are not going away any time soon. I've asked them to update the documentation, though it's not clear when that will happen. – Michael Diamond Jan 23 '17 at 23:28
  • Thanks for update Michael. Seems it's not just a documentation problem though because the BigQuery console only shows the above fields (in original question) as available. – hoju Jan 28 '17 at 08:33
  • @hoju, do you see BigQuery listed in the `protopayload_google_cloud_audit_auditlog.serviceName` column? I would expect to see a column like `protopayload_google_cloud_audit_auditlog.serviceData...` for the BQ audit data. – Kirk Kelsey Jan 31 '17 at 20:08
  • sometimes i wish people had to explain why they are downvoting an answer... – Willian Fuks Apr 29 '17 at 18:28