Troubleshoot the BigQuery Storage API errors

This document explains how to troubleshoot issues when you read or stream data in BigQuery using the BigQuery Storage Read API, the BigQuery Storage Write API (gRPC), or streaming inserts with the BigQuery Storage Write API (REST) (tabledata.insertAll method).

Analyze streaming telemetry with INFORMATION_SCHEMA views

You can query INFORMATION_SCHEMA views to monitor streaming ingestion health, identify throughput bottlenecks, and inspect error codes over one-minute intervals:

• Storage Write API (gRPC): query the INFORMATION_SCHEMA.WRITE_API_TIMELINE views to inspect gRPC streaming ingestion requests, total bytes and rows appended, and error counts by error_code.
• Storage Write API (REST): query the INFORMATION_SCHEMA.STREAMING_TIMELINE views to inspect legacy REST tabledata.insertAll streaming requests and quota or rate limit errors.

The following example queries INFORMATION_SCHEMA.WRITE_API_TIMELINE_BY_PROJECT to retrieve error counts and ingested bytes for the Storage Write API (gRPC) over the past 24 hours:

SELECT
  start_timestamp,
  error_code,
  SUM(total_requests) AS request_count,
  SUM(total_input_bytes) AS input_bytes
FROM
  `region-REGION`.INFORMATION_SCHEMA.WRITE_API_TIMELINE_BY_PROJECT
WHERE
  start_timestamp > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND error_code IS NOT NULL
GROUP BY
  start_timestamp,
  error_code
ORDER BY
  start_timestamp DESC;

Replace REGION with the dataset region name, such as us or europe-west1.

Troubleshoot Storage Read API errors

The following are common errors encountered when you use the Storage Read API:

Error: Stream removed

Resolution: Retry the Storage Read API request. This is likely a transient error that you can resolve by retrying the request. If the problem persists, contact Cloud Customer Care.

Error: Stream expired

Cause: This error occurs when the Storage Read API session reaches the 6-hour timeout.

Resolution:

1. Increase the parallelism of the job.
2. If the CPU utilization of the worker nodes is relatively consistent and doesn't exceed 85%, consider running the job on a larger machine type.
3. Split the job into multiple jobs or smaller queries.

For more information about session management and reading data, see the Storage Read API overview.

Troubleshoot streaming inserts

The following sections discuss how to troubleshoot errors that occur when you stream data into BigQuery using the Storage Write API (REST). For more information about how to resolve quota errors for streaming inserts, see Streaming insert quota errors.

Failure HTTP response codes

If you receive a failure HTTP response code, such as a network error, there's no way to tell whether the streaming insert succeeded. If you try to resend the request, you might get duplicated rows in your table. To help protect your table against duplication, set the insertId property when you send your request. BigQuery uses the insertId property for deduplication.

If you receive a permission error, an invalid table name error, or an exceeded quota error, no rows are inserted and the entire request fails.

Success HTTP response codes

Even if you receive a success HTTP response code, you must check the insertErrors property of the response to determine whether the row insertions were successful, because BigQuery might be only partially successful at inserting the rows. You might encounter one of the following scenarios:

• All rows inserted successfully: If the insertErrors property is an empty list, all of the rows were inserted successfully.
• Some rows inserted successfully: Except in cases where there's a schema mismatch in any of the rows, rows indicated in the insertErrors property aren't inserted, and all other rows are inserted successfully. The errors property contains detailed information about why each unsuccessful row failed. The index property indicates the 0-based row index of the request that the error applies to.
• No rows inserted successfully: If BigQuery encounters a schema mismatch on individual rows in the request, none of the rows are inserted and an insertErrors entry is returned for each row, even for rows that didn't have a schema mismatch. Rows that didn't have a schema mismatch have an error with the reason property set to stopped, and you can resend them as-is. Rows that failed include detailed information about the schema mismatch. To learn about the supported protocol buffer types for each BigQuery data type, see Supported protocol buffer and Arrow data types.

Metadata errors for streaming inserts

Because the BigQuery streaming API is designed for high insertion rates, modifications to the underlying table metadata are eventually consistent when interacting with the streaming system. Most of the time, metadata changes propagate within minutes, but during this period API responses might reflect the inconsistent state of the table.

Some scenarios include the following:

• Schema changes: Modifying the schema of a table that recently received streaming inserts can cause responses with schema mismatch errors because the streaming system might not immediately detect the schema change.
• Table creation or deletion: Streaming to a nonexistent table returns a variation of a notFound response. A table created in response might not immediately be recognized by subsequent streaming inserts. Similarly, deleting or recreating a table can create a period of time where streaming inserts are delivered to the old table. The streaming inserts might not be present in the new table.
• Table truncation: Truncating a table's data (by using a query job that uses a writeDisposition value of WRITE_TRUNCATE) can similarly cause subsequent inserts during the consistency period to be dropped.

Missing or unavailable data

Streaming inserts reside temporarily in write-optimized storage, which has different availability characteristics than managed storage. Certain operations in BigQuery don't interact with write-optimized storage, such as table copy jobs and API methods like tabledata.list. Recent streaming data isn't present in the destination table or output.

Streaming insert quota errors

This section provides 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.

Error message

If the insertId field is empty, the following quota error is possible:

If the insertId field is populated, the following quota errors are possible:

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.

When you encounter this error, diagnose the issue, and then follow the recommended steps to resolve it.

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-REGION_NAME`.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

Resolution

To resolve this quota error, do the following:

• If you are using the insertId field for deduplication, and your project is in a region that supports the higher streaming quota, we recommend removing the insertId field. This solution might 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 than QUOTA_EXCEEDED errors, and your overall traffic is less than 80% of quota, the errors probably indicate temporary spikes. You can address these errors by retrying the operation using exponential backoff between retries.

  • If you are using a Dataflow job to insert data, consider using load jobs instead of streaming inserts. For more information, see Setting the insertion method. If you are using Dataflow with a custom I/O connector, consider using a built-in I/O connector instead. For more information, see Custom I/O patterns.

  • If you see QUOTA_EXCEEDED errors or the overall traffic consistently exceeds 80% of the quota, submit a request for a quota increase. For more information, see Request a quota adjustment.

  • You might also want to consider replacing streaming inserts with the newer Storage Write API, which has higher throughput, lower price, and many useful features.