BigQuery - How to see failed scheduled queries in Cloud Logging?

user_7808647 0
Viewed 1860

I would like to monitor the state of my BigQuery scheduled queries in a Cloud Monitoring dashboard. I have created several logs-based metrics to track errors in other services/resources, but having trouble finding any indication of scheduled query errors in Cloud Logging.

From the Scheduled Queries page in the BigQuery UI, I can check the run details on failed scheduled queries and it shows some log entries explaining the error e.g.

9:02:59 AM  Error code 8 : Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex..; JobID: PROJECT:12345abc-0000-12a3-1234-123456abcdf   
9:00:17 AM  Starting to process the query job with no parameters.   
9:00:00 AM  Dispatched run to data source with id 1234567890

But for some reason I cannot find any of these messages in Cloud Logging. For succeeded jobs, there are some entries in the BigQuery logs, but the failed jobs are missing completely.

Any idea how to view failed scheduled queries in Cloud Logging or Cloud Monitoring?

google-cloud-monitoring google-cloud-logging google-cloud-stackdriver google-bigquery
2 Answers

You can use the following advanced filter to filter all the BigQuery errors related to "jobservice.insert"

resource.type="bigquery_resource"
protoPayload.serviceName="bigquery.googleapis.com"
protoPayload.methodName="jobservice.insert"
severity: "ERROR"

This is the result of that query:

enter image description here

Even a simple query like:

resource.type="bigquery_resource"
severity: "ERROR"

Is able to retrieve all the BigQuery related errors, as you can see here:

enter image description here

Once you find the one related to failed scheduled queries, you can click over the protopayload of the result and select "Show matching entries" to start constructing your own Advanced Query.

I was able to assemble this filter using the Advanced logs queries and BigQuery queries documents.

user_120110220

Please verify the permissions you have - in my case I was able to see failed scheduled queries in Cloud Logging in project where I had been set up as Owner. For another project, where I am just an Editor I was unable to see the erros, just like in your case.

The code bellow works (e.g. I am able to get errors related to "Permission denied while getting Drive credentials" - missing credentials on the Google Drive files for service account running scheduled query):

resource.type="bigquery_resource"
protoPayload.serviceName="bigquery.googleapis.com"
protoPayload.methodName="jobservice.insert"
severity=ERROR
user_46974710
Related