503 ServiceUnavailable Error in BigQuery during load job, but job is successful in console

[txu77]

Hi all,

Recently we have been experiencing a 503 Error (google.api_core.exceptions.ServiceUnavailable) when running a BigQuery load job through the Python Client for BQ.

The error appears when we create a load_job and call load_job.result(). However, in the BQ console if I go to the project history the job appears as a success.

Any explanation why this is happening? Please let me know if more details are needed.

[ms4446]

The 503 Service Unavailable error is a temporary error that indicates that the BigQuery service is not ready to handle the request. This can arise due to various reasons, such as:

• A high volume of requests.
• Maintenance or upgrades being performed on the BigQuery service.
• Network issues, either on Google Cloud's side or related to your own connectivity.

In many instances, the error will resolve itself within a few minutes. However, if you're encountering this issue, consider the following troubleshooting steps:

1. Retry the request after waiting for a few minutes.
2. If possible, reduce the number of concurrent requests you are making.
3. Contact Google Cloud support for assistance.

If the error persists after trying the above steps, please provide the following details to assist in further investigation:

• The project ID and dataset ID associated with the load job.
• The exact time the error was encountered.
• The complete error message you received.
• Any other pertinent information.

[txu77]

Hi @ms4446, the project ID is cmg-data-stg, and the dataset_id is ops_label_engine_raw. The error occurred multiple times yesterday, the most recent was October 18 at 15:16 UTC.

In Python, we got the following error:

google.api_core.exceptions.ServiceUnavailable: 503 GET https://bigquery.googleapis.com/bigquery/v2/projects/cmg-data-stg/jobs/e96efd89-3d94-45b8-a240-6bda3...: Error encountered during execution. Retrying may solve the problem.

However in the BQ console, when I go the job_id it shows successful load. We successfully completed the load job after implementing a retry. Just curious why there is a mismatch between Python and BQ. 

[ms4446 · Accepted solution]

The mismatch between the Python error and the successful job status in BigQuery is likely due to the transient nature of the 503 Service Unavailable error. As mentioned previously, this error can occur when the BigQuery service is overloaded or unavailable. In this case, the Python client was unable to successfully get the job status due to the error. However, the job itself was still able to complete successfully.

The retry mechanism that you implemented was able to successfully get the job status after the 503 error had resolved. This is why you were able to see the successful load in the BigQuery console.

In general, it is a good idea to implement retries when using APIs that can return transient errors. This will help to ensure that your code is able to handle these errors gracefully.