CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Sander van den Oord
Sander van den Oord

Reputation: 12818

BigQuery: Trying to recognize scheduled queries in INFORMATION_SCHEMA. Possible idea: Add key:value label

I try to recognize scheduled queries on gcp to see which scheduled query is costing me the most.

But if I run the following query, I can only see the job_id which is not really informative, and the labels also don't contain the resource name of the scheduled query.

SELECT creation_time, job_id, parent_job_id, user_email, labels, bytes_billed
FROM `my_project_id.region-eu.INFORMATION_SCHEMA.JOBS`
WHERE starts_with(job_id, 'scheduled_query') and parent_job_id is null
ORDER BY creation_time desc

One solution could be to add labels to the scheduled query.

I tried adding a label to a session, by adding this to my scheduled query, but this only added the query_key and label to a child job:
SET @@query_label = "scheduled_query:some_name_that_i_like";
https://cloud.google.com/bigquery/docs/adding-labels#adding-label-to-session

I also tried the bq cli client, but this also didn't work:

bq update --set_label scheduled_query:some_name_that_i_like --transfer_config projects/some_project_id_number/locations/europe/transferConfigs/some_transfer_config_number

How can I make it easier to recognize which scheduled query was run?

This question is (somewhat) related:
BigQuery - Get the display name of scheduled query from INFORMATION_SCHEMA

Upvotes: 2

Views: 1589

Answers (1)

Aditya Kalia
Aditya Kalia

Reputation: 21

all scheduled queries have %scheduled_query% in the job_id you can filter it in the where clause like so:

FROM `my_project_id.region-eu.INFORMATION_SCHEMA.JOBS`
where
  job_id like "%scheduled_query%"
  and priority = "BATCH"

Upvotes: 0

Related Questions