Is it possible to retrieve full query history and correlate its cost in google bigquery?

user_12870131 0
Viewed 2350

I am querying multiple tables and I am able to see the cost of each query for my personal use. As I view the Query History I only see the queries I ran on my account.

So my question is, is it possible to somehow to see the queries which have been run by others (as well as the cost of the query ) in a project from the query history ?

google-bigquery
2 Answers

You can use Jobs information schema:

SELECT query, total_bytes_processed FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE project_id = 'you_project_id' AND user_email = 'my@eamil.com'
user_64767910

According to the documentation, there is not a direct method of getting costs by job and user. However, there is a way of doing it.

For a detailed billing analysis, I would advise you to export the logs to BigQuery with a custom filter and from there analyse the billing for each user and query job.

So, you can create an export using the Logs Viewer or the API. While creating your sink use the following custom filter: 

resource.type="bigquery_resource"
logName="projects/<your_project>/logs/cloudaudit.googleapis.com%2Fdata_access"
protoPayload.methodName="jobservice.jobcompleted"

The above filter will retrieve completed query jobs whilst the data access logs are a comprehensive audit of every query run in BigQuery along with the total bytes scanned. I would like to point that you have to make sure that data_access logs are enable, link.

From the log entries you will get the fields:

  • protoPayload.authenticationInfo.principalEmail
  • protoPayload.serviceData.jobCompletedEvent.job.jobName.jobId
  • protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query
  • protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes

In BigQuery, you can use a query as follows: 

    SELECT
      protopayload_auditlog.authenticationInfo.principalEmail AS email,
      protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes AS total_billed_bytes,
      protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query AS query,
      protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId as job_id
    FROM
      `<myproject>.<mydataset>.cloudaudit_googleapis_com_data_access`
    WHERE
      protopayload_auditlog.methodName = 'jobservice.jobcompleted';

Afterwards, to get an estimate of the price per each query you can use the totalBilledBytes and the Pricing summary in order to add a new column with a price estimative for each query. Therefore, you have a final table with the user's email, the query code, total bytes billed, job id and an estimate price.

user_125713870
Related