3

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?

antti
  • 481
  • 5
  • 12
  • how does you log-based metric definition looks ? – c69 Nov 17 '20 at 16:09
  • I've tried many things, but e.g. with resource.type="bigquery_resource" "scheduled" I can find logs related to successful scheduled queries. – antti Nov 18 '20 at 05:56

2 Answers2

3

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.

Lalo P
  • 326
  • 1
  • 10
  • Thanks for the suggestion! Unfortunately the filter doesn't show the failed queries. From the BigQuery UI (Scheduled Queries > Run details) I see that there's a failed query e.g. 4-Oct-2020 9:00 AM, but running the above filter around that time doesn't show any logs. – antti Nov 19 '20 at 12:13
  • And are you able to find the error in the logs without using any filter other than **resource.type="bigquery_resource"**? If you can find the error using this method, I recommend you to start constructing the filter from there, you can click over the protopayload of the result and select "Show matching entries" to get the exact code used by Log Explorer. – Lalo P Nov 19 '20 at 19:40
  • No, that's the problem. I am not able to see anything related to the failed job. No error, no insert job, nothing. – antti Nov 23 '20 at 12:23
  • I'm going to edit my answer as I made some modifications to the query and to display an image with my results. – Lalo P Nov 23 '20 at 14:27
0

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
Tomasz Kubat
  • 102
  • 2
  • 11