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

2
votes

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?

1
google-bigquerygoogle-cloud-stackdrivergoogle-cloud-logginggoogle-cloud-monitoring
how does you log-based metric definition looks ? - c69
I've tried many things, but e.g. with resource.type="bigquery_resource" "scheduled" I can find logs related to successful scheduled queries. - antti

1 Answers

1
votes

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.