[SOLVED] Handle 503 error when making jobs.insert call

Handle 503 error when making jobs.insert call

In Bigquery's documentation, the troubleshooting section for the 503 backendError states:

If you receive this error when making a jobs.insert call, it's unclear if the job succeeded. In this situation, you'll need to retry the job.

source: https://cloud.google.com/bigquery/docs/error-messages

What I don't understand is, if we're not sure the original job is success or not, how can we know if the retry is safe to be called and wouldn't cause duplication?

For instance, if the first job eventually completes but the retry also succeeds, wouldn't that lead to duplicated data?

I’ve searched for a few days, but couldn’t find clear information on handling this situation.

Solution

A good practice would be to have a control over the jobs you launch. I mean, you can generate unique IDs for the jobs and then you can check their status and what to do with them. Practical example in python: I want to load data from a csv. I create a unique job_id using the hashlib library

import hashlib
file_name = ‘data.csv’
job_id = hashlib.sha256(file_name.encode()).hexdigest()

Next, I'll launch the job

from google.cloud import bigquery

client = bigquery.Client()
job_config = bigquery.LoadJobConfig(...)
uri = ‘gs://your_bucket/data.csv’

job = client.load_table_from_uri(
    uri,
    ‘your_dataset.your_table’,
    job_config=job_config,
    job_id=job_id
)

If I want to check what state it is in, I can use the following command from the bq console (https://cloud.google.com/bigquery/docs/reference/bq-cli-reference?hl=es-419#bq_show):

bq show --job <PROJECT_ID>:<JOB_ID>

With this we will verify in which state is the job and therefore, if you want to run it again or wait for it to finish (with error or not) so you don't get the duplicity you are talking about.

I hope this is useful for you!