Use query queues

Preview

This product or feature is covered by the Pre-GA Offerings Terms of the Google Cloud Terms of Service. Pre-GA products and features might have limited support, and changes to pre-GA products and features might not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

This document provides an overview of query queues in BigQuery.

Enable query queues

You can enable or disable query queues for either a project using the on-demand pricing model (an on-demand project), or an entire reservation. Query queues are disabled by default. To enroll in this preview, complete the enrollment form.

When you enable query queues for an on-demand project or reservation, BigQuery automatically determines the number of queries that can run concurrently. When the maximum concurrency target is reached, queries are queued until processing resources become available.

Overview

Without query queues, BigQuery limits the number of concurrently running interactive queries to 100. Queries that exceed this limit return a quota error. The following example shows this behavior:

When query queues are enabled, BigQuery dynamically determines the query concurrency based on available compute resources. The number of concurrently running queries is computed per on-demand project or per reservation. Additional queries wait in a queue until there is enough capacity available to begin execution. For an on-demand project, the queue length is limited to 2,000 interactive queries and 20,000 batch queries. For a reservation, the queue allows up to 2,000 interactive queries and 20,000 batch queries per project within the reservation. The following example shows the behavior for an on-demand project when the computed query concurrency is 202:

Optionally, you can set the maximum concurrency target for a reservation to ensure that each query is allocated some minimum number of slots. You can't specify a maximum concurrency target for an on-demand project; it is always dynamically computed.

Queuing behavior

Whether or not query queues are enabled, BigQuery enforces fair scheduling to ensure that no single project can consume all of the slots in a reservation.

When query queues are enabled for a reservation, projects that have the smallest share of concurrency are dequeued first. During execution, slots are distributed fairly among projects before being distributed across jobs within a project.

For example, suppose you have a reservation that contains two projects: A and B. BigQuery computes 5 for the concurrency of the reservation. Project A has four currently running queries, project B has one running query, and other queries are queued. A query from project B would be dequeued first even if it was submitted after the query from project A. After a query begins execution, it receives a fair share of slots in the shared reservation.

When query queues are enabled for an on-demand project, query jobs are run in the order in which they are submitted.

Prioritization of interactive and batch queries

BigQuery supports running interactive and batch queries. BigQuery limits the portion of concurrently running batch queries to 30% of the maximum concurrency target. After batch queries reach 30% of the limit, only interactive queries are dequeued.

Changing or deleting a reservation with queued queries

When a reservation is deleted, all running and queued queries in the reservation are canceled. When a project assigned to a reservation gets reassigned to another reservation, all requests currently queued or running continue to do so in the old reservation, while all new requests go to the new reservation. Similarly, when a project assigned to a reservation is removed from the reservation, new requests execute using the on-demand model.

Set the maximum concurrency target

You can manually set the maximum concurrency target when you create a reservation. The maximum concurrency target takes effect once you enable query queues for that reservation. Until then, it has no effect.

By default, the maximum concurrency target is zero, which means that BigQuery dynamically determines the concurrency based on available resources. Otherwise, if you set a nonzero target, the maximum concurrency target specifies an upper bound on the number of queries that run concurrently in a reservation, which guarantees a minimum amount of slot capacity available for each query that runs.

Increasing the maximum concurrency target doesn't guarantee that more queries execute simultaneously. The actual concurrency depends on the available compute resources.

Required permissions

To set the concurrency in a new reservation, you need the following Identity and Access Management (IAM) permission:

• bigquery.reservations.create on the administration project that maintains ownership of the commitments

Each of the following predefined IAM roles includes this permission:

• BigQuery Resource Editor
• BigQuery Resource Admin
• BigQuery Admin

For more information about IAM roles in BigQuery, see Predefined roles and permissions.

Set the maximum concurrency target for a reservation

bq

To set the maximum concurrency target for a new reservation, run the bq mk command:

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --concurrency=CONCURRENCY \
    --reservation \
    RESERVATION_NAME

Replace the following:

• ADMIN_PROJECT_ID: the project that owns the reservation
• LOCATION: the location of the reservation
• CONCURRENCY: the maximum concurrency target
• RESERVATION_NAME: the name of the reservation

API

To set the maximum concurrency target in the BigQuery Reservation API, set the concurrency field in the reservation resource and call the CreateReservationRequest method.

Update the maximum concurrency target

You can update the maximum concurrency target for a reservation at any time. If query queues are not enabled for the reservation, then changing the value has no effect.

If query queues are enabled, then increasing the maximum concurrency target takes effect immediately. However, this doesn't guarantee that more queries execute simultaneously. The actual concurrency depends on the available compute resources. If you reduce the maximum concurrency target, then currently running queries are not impacted and queued queries won't run until the number of concurrent queries falls below the new target.

If you set the maximum concurrency target to 0, BigQuery dynamically determines the concurrency based on available resources (the default behavior).

Required permissions

To update the maximum concurrency target for a reservation, you need the following Identity and Access Management (IAM) permission:

• bigquery.reservations.update on the administration project that maintains ownership of the commitments

Each of the following predefined IAM roles includes this permission:

• BigQuery Resource Editor
• BigQuery Resource Admin
• BigQuery Admin

For more information about IAM roles in BigQuery, see Predefined roles and permissions.

Update the maximum concurrency target for a reservation

bq

To update the maximum concurrency target for an existing reservation, run the bq update command:

bq update \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --concurrency=CONCURRENCY \
    --reservation \
    RESERVATION_NAME

Replace the following:

• ADMIN_PROJECT_ID: the project that owns the reservation
• LOCATION: the location of the reservation
• CONCURRENCY: the maximum concurrency target
• RESERVATION_NAME: the name of the reservation

API

To update the maximum concurrency target in the BigQuery Reservation API, set the concurrency field in the reservation resource and call the UpdateReservationRequest method.

Monitoring

To find out which queries are running and which are queued, look at the INFORMATION_SCHEMA.JOBS_BY_* and INFORMATION_SCHEMA.JOBS_TIMELINE_BY_* views. The state field is set to RUNNING for actively running queries and to PENDING for queued queries.

You can monitor the query queue length for your reservation by using BigQuery Admin Resource Charts and selecting the Job Concurrency chart. You can also monitor the queue length in Cloud Monitoring by viewing the job count metric and checking the number of jobs in a pending state.

Limitations

• Within each project, up to 2,000 interactive queries and 20,000 batch queries can be queued at one time. Queries that exceed this limit return a quota error.
• By default, query jobs that haven't started execution time out after 6 hours for interactive queries and 24 hours for batch queries.
• You cannot set the maximum concurrency target for queries running in an on-demand project. The concurrency is always calculated automatically when query queues are enabled.

Disable query queues

To disable query queues for an individual on-demand project or an entire reservation, fill out the BigQuery query queues form. You cannot selectively enable or disable query queues for a project assigned to a reservation. After query queues are disabled, queries that exceed the concurrency fail with an error and are not queued.