Option to Abort or Suspend BigQuery's Scheduled Query

[mdideles]

We are running Scheduled Query every 15 minutes. 

We need an option to abort the running job when it is not able to complete in 15 minutes or, suspend the next run when the previous run is not finished yet.

Do you have any way to do this? or, is this feature in the roadmap?

[ms4446]

Yes, there are a few ways to achieve this in Google Cloud:

Option 1: Cloud Monitoring alerts

You can create a Cloud Monitoring alert that will notify you when a scheduled query exceeds a certain runtime. When you receive an alert, you can then manually terminate the query. To create a Cloud Monitoring alert for scheduled queries, follow these steps:

1. Go to the Cloud Monitoring console.
2. Click the "Create alert" button.
3. Select the "Metric" alert type.
4. In the "Metric" field, select the bigquery.googleapis.com/query_execution_durationmetric.
5. In the "Condition" field, enter the following expression:

metric.value > 900

6. In the "Notifications" section,specify how you want to be notified when the alert is triggered.
7. Click the "Create alert" button.

Option 2: Use a Cloud Function to monitor scheduled queries

You can create a Cloud Function that will periodically check the status of scheduled queries. If a query is found to be running for longer than a certain amount of time, the Cloud Function can then terminate the query. Note that the following is a conceptual approach, and you'll need to integrate it with the BigQuery Python client library:

def monitor_scheduled_queries(event, context):
  """Cloud Function to monitor scheduled queries."""

  from google.cloud import bigquery
  import datetime

  client = bigquery.Client()
  current_time = datetime.datetime.utcnow()

  # Get a list of all jobs in the project
  for job in client.list_jobs():
    if job.state == 'RUNNING' and job.job_type == 'QUERY':
      job_start_time = datetime.datetime.utcfromtimestamp(job.created.timestamp())

      if (current_time - job_start_time).total_seconds() > 900:
        # The query has exceeded the timeout. Terminate the query.
        client.cancel_job(job.job_id)

By using one of these methods, you can ensure that your scheduled queries do not run for longer than the desired amount of time. Always test any code or configurations in a safe environment before deploying to production.

[mdideles]

Thanks for the reply @ms4446 .

When I create a policy, I don't see the bigquery.googleapis.com/query_execution_duration metric.  is it the bigquery.googleapis.com/query/execution_times?

[ms4446]

Yes, you're right; the correct metric for BigQuery query execution times in Cloud Monitoring is bigquery.googleapis.com/query/execution_times.

To set up an alert for this metric:

1. Go to the Cloud Monitoring console.
2. Click the "Create alert" button.
3. Select the "Metric" alert type.
4. In the "Metric" field, select the bigquery.googleapis.com/query/execution_times metric.
5. In the "Condition" field, set the threshold to trigger the alert. For example, if you want to be alerted when a query runs for more than 15 minutes, you can set the condition to: metric.value > 900000 (since the metric is in milliseconds).
6. In the "Notifications" section, specify how you want to be notified when the alert is triggered.
7. Click the "Create alert" button.

Thank you for pointing it out.

[mdideles]

thanks.  Will try it out

[mdideles]

@ms4446  I think.  I am good for now.  We will look into doing the abort automatically as well using code. 

But, is there a chance that we can request for this feature, to have an option, to suspend the next run when the previous job is not yet complete, or even abort the previous one when the new one runs?

[ms4446 · Accepted solution]

You can request new features or enhancements to existing Google Cloud services by using Google Cloud Issue Tracker. Google Cloud values feedback from its users.

Google Cloud Issue Tracker is a tool used by the Google Cloud team and its users to track feature requests, bug reports, and other issues related to Google Cloud Platform products.

You can access Google Cloud Issue Tracker at the following URL: https://issuetracker.google.com/

When you visit the Issue Tracker:

1. Use the search bar to find existing issues related to your concern.
2. If you don't find a relevant issue, you can create a new one by clicking on the "Create Issue" button.
3. Choose the appropriate product or component, provide a detailed description, and submit the issue.