How to request BigQuery usage with v2 StackDriver API?

Question

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

In the console, BigQuery v2 provides only 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 

Currently there is no field totalBilledBytes, which was used in example v1 queries to calculate usage. How can I request usage cost using v2 API?

Answer 1

There is a good tutorial for querying audit logs to get this information. Here is an example query (assuming you have already exported audit log data back to BigQuery).

 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 )