How to know the number of queries per user in Bigquery?

Question

I want to know:

1. how to get the number of queries a user makes per month in BigQuery?
2. how many queries a specific user makes, how many per year.

I found the command

bq ls -j

with that bring the number of jobs and with the comand bq show get the detail, but it does not bring me all the elements that I need, I would like to know if someone knows another way to achieve this.

Answer 1

You need to enable Audit Logs, and create a Sink for Bigquery.

Then you can write a query that uses for protoPayload.methodName column one of the google.cloud.bigquery.v2.JobService.Query values

Example: Bytes processed per user identity

This query shows the total bytes billed for query jobs per user, in terabytes.

 #standardSQL
  WITH data as
  (
    SELECT
      protopayload_auditlog.authenticationInfo.principalEmail as principalEmail,
      protopayload_auditlog.metadataJson AS metadataJson,
      CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson,
          "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64) AS totalBilledBytes,
    FROM
      `MYPROJECTID.MYDATASETID.cloudaudit_googleapis_com_data_access_*`
  )
  SELECT
    principalEmail,
    FORMAT('%9.2f',SUM(totalBilledBytes)/POWER(2, 40)) AS Billed_TB
  FROM
    data
  WHERE
    JSON_EXTRACT_SCALAR(metadataJson, "$.jobChange.job.jobConfig.type") = "QUERY"
  GROUP BY principalEmail
  ORDER BY Billed_TB DESC