BigQuery has various quotas that limit the rate and volume of incoming requests. These quotas exist both to protect the backend systems, and to help guard against unexpected billing if you submit very large jobs. This document describes how to diagnose and mitigate errors resulting from quotas.
Overview
If a BigQuery operation fails because of a quota limit, the API
returns the HTTP 403 Forbidden
status code. The response body contains more
information about the limit that was reached. The response body looks similar to
the following:
{
"code" : 403,
"errors" : [ {
"domain" : "global",
"message" : "Quota exceeded: ...",
"reason" : "quotaExceeded"
} ],
"message" : "Quota exceeded: ..."
}
The message
field in the payload describes which limit was exceeded. For
example, the message
field might say Exceeded rate limits: too many table
update operations for this table
.
In general, quota limits fall into two categories, indicated by the reason
field in the response payload.
rateLimitExceeded
. This value indicates a short-term limit. Usually you can resolve these limits by retrying the operation after a few seconds. Use exponential backoff between retry attempts. That is, exponentially increase the delay between each retry.quotaExceeded
. This value indicates a longer-term limit. If you reach a longer-term quota limit, you should wait 10 minutes or longer before trying the operation again. If you consistently reach one of these longer-term quota limits, you should analyze your workload for ways to mitigate the issue. Mitigations can include optimizing your workload or requesting a quota increase.
For quotaExceeded
errors, examine the error message to understand which quota
limit was exceeded. Then analyze your workload to see if you can avoid reaching
the quota, for example by optimizing query performance. In some cases, the quota
can be raised by
contacting BigQuery support or
contacting Google Cloud sales.
You can use INFORMATION_SCHEMA
views to analyze the underlying issue. These are a set of views that contain
metadata about your BigQuery resources, including jobs,
reservations, and streaming inserts. For example, the following query uses the
JOBS_BY_PROJECT
view to list all
quota-related errors within the past day.
SELECT
job_id,
creation_time,
error_result
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY) AND
error_result.reason IN ('rateLimitExceeded', 'quotaExceeded')
You can also view errors in Cloud Audit Logs. For example, using
Logs Viewer, the following query
finds errors with Quota exceeded
in the message string:
resource.type = ("bigquery_project" OR "bigquery_dataset")
protoPayload.status.code ="7"
protoPayload.status.message: "Quota exceeded"
(Status code 7
is
PERMISSION_DENIED
, which
corresponds to the HTTP 403
status code.)
Streaming insert quota errors
This section gives some tips for troubleshooting quota errors related to streaming data into BigQuery.
In certain regions, streaming inserts have a higher quota if you don't populate
the insertId
field for each row. For more information about quotas for
streaming inserts, see Streaming inserts.
The quota-related errors for BigQuery streaming depend on the
presence or absence of insertId
.
If the insertId
field is empty, the following quota error is possible:
Quota limit | Error message |
---|---|
Bytes per second per project | Your entity with gaia_id: GAIA_ID, project: PROJECT_ID in region: REGION exceeded quota for insert bytes per second. |
If the insertId
field is populated, the following quota errors are possible:
Quota limit | Error message |
---|---|
Rows per second per project | Your project: PROJECT_ID in REGION exceeded quota for streaming insert rows per second. |
Rows per second per table | Your table: TABLE_ID exceeded quota for streaming insert rows per second. |
Bytes per second per table | Your table: TABLE_ID exceeded quota for streaming insert bytes per second. |
The purpose of the insertId
field is to deduplicate inserted rows. If multiple
inserts with the same insertId
arrive within a few minutes' window,
BigQuery writes a single version of the record. However, this
automatic deduplication is not guaranteed. For maximum streaming throughput, we
recommend that you don't include insertId
and instead use
manual deduplication.
For more information, see
Ensuring data consistency.
Diagnosis
Use the STREAMING_TIMELINE_BY_*
views to analyze the streaming traffic. These views aggregate streaming
statistics over one-minute intervals, grouped by error code. Quota errors appear
in the results with error_code
equal to RATE_LIMIT_EXCEEDED
or
QUOTA_EXCEEDED
.
Depending on the specific quota limit that was reached, look at total_rows
or
total_input_bytes
. If the error is a table-level quota, filter by table_id
.
For example, the following query shows total bytes ingested per minute, and the
total number of quota errors.
SELECT
start_timestamp,
error_code,
SUM(total_input_bytes) as sum_input_bytes,
SUM(IF(error_code IN ('QUOTA_EXCEEDED', 'RATE_LIMIT_EXCEEDED'),
total_requests, 0)) AS quota_error
FROM
`region-us`.INFORMATION_SCHEMA.STREAMING_TIMELINE_BY_PROJECT
WHERE
start_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP, INTERVAL 1 DAY)
GROUP BY
start_timestamp,
error_code
ORDER BY 1 DESC
Solution
If you are using the insertId
field for depuplication, and your project is
in a region that supports the higher streaming quota, we recommend removing the
insertId
field. This solution may require some additional steps to manually
deduplicate the data. For more information, see
Manually removing duplicates.
If you are not using insertId
, or if it's not feasible to remove it, monitor
your streaming traffic over a 24-hour period and analyze the quota errors:
If you see mostly
RATE_LIMIT_EXCEEDED
errors rather thanQUOTA_EXCEEDED
errors, and your overall traffic is below 80% of quota, the errors probably indicate temporary spikes. You can handle these errors by retrying the operation, using exponential backoff between retries.If you see
QUOTA_EXCEEDED
errors or the overall traffic consistently exceeds 80% of quota, submit a request for a quota increase.