1

I would like to get execution log of scheduled queries in bigquery programmatically. It is very much possible using web UI but if there any way using Standard SQL or any API which is providing that log would be fine.

Thanks in advance

vignesh
  • 1,414
  • 5
  • 19
  • 38

1 Answers1

1

Try querying the INFORMATION_SCHEMA.JOBS_BY_PROJECT view.

For example:

select *
FROM `elzagales`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
where
  job_id like "%scheduled_query%"
  and priority = "BATCH"

you may need to adjust your job_id if you provide a custom job_id to your scheduled queries.

Daniel Zagales
  • 2,948
  • 2
  • 5
  • 18
  • Thanks for the code, but this is not providing Scheduled queries displayname – vignesh Sep 27 '22 at 14:14
  • 1
    Things to take note of in the above query: 1. replace with your project name 2. the region should reflect the region your scheduled query is running in. 3. It should be run in the project your scheduled queries are being run in 4. it is assuming your are using the default naming convention for job_id, if so like i mentioned in the original answer you will need to update it to reflect whatever naming convention you use, or remove it entirely – Daniel Zagales Sep 27 '22 at 16:23
  • im not understanding the 4th point you mentioned. Please refer some articles to educate my self. Im expecting like select ScheduledQueryName, Executiondate, Status from logtable – vignesh Oct 05 '22 at 09:46
  • from your query i can only get till job id. Every run for a scheduled query there will be a new job created. would like to find all jobs related to a specific scheduled query – vignesh Oct 05 '22 at 09:47