[Python] How to get counts on all queries running in BigQuery

I am trying to get information on the number of queries running in BigQuery, and their states.

I've tried this:

select count(job_id) as job_count, state
from `MY_PROJECT`.`region-MY_REGION`.INFORMATION_SCHEMA.JOBS
where creation_time between
TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
AND CURRENT_TIMESTAMP()
and query not like 'select count(job_id) as job_count, state%'
and project_id = 'MY_PROJECT'
group by state;


but I only seem to get counts for queries submitted by my user. I know there is more activity than that because when I go to the monitoring page, the "Jobs Explorer", I can see jobs submitted by different service accounts as well, and how many are in each state. The fact that I can see these jobs in the UI makes me doubt that it is a permissions-related issue (unless queries require different permissions than the UI).

I need to be able to get that information programmatically instead of looking at the UI, and I am not sure why the query above doesn't do it.


I thought I would try using the bigquery client, from Python:

from google.cloud import bigquery
from datetime import datetime, timedelta
project_id = 'MY_PROJECT'

client = bigquery.Client()
jobs = client.list_jobs(project=project_id,
all_users=True,
min_creation_time=datetime.now() - timedelta(hours=1),
max_creation_time=datetime.now())

states = []
job_counts = {}
# Iterate through the jobs and count the states
for job in jobs:
if job.state not in states:
states.append(job.state)
job_counts[job.state] = 0
job_counts[job.state] += 1


for state, count in job_counts.items():
print(f'Jobs in state {state}: {count}')


And this does not give the same results as the SQL query above.

What is the best way to get the number of jobs and their states in bigquery, programmatically?

Continue reading...