CODEWITHSUNDEEP
pythongoogle-apigoogle-bigquery
phil_scott_a_person
phil_scott_a_person

Reputation: 384

bigquery python client.run_async_query gives error: 409 Already Exists

I'm coding a python script that runs a certain SELECT query async. After the first time running the script, it always errors out after that with the following error:

google.cloud.exceptions.Conflict: 409 Already Exists: Job ps-bigdata:vci-temp-sales-query-job (POST https://www.googleapis.com/bigquery/v2/projects/ps-bigdata/jobs)

Here is a code snippet:

from google.cloud import bigquery

google_auth_json_file = './myprojectauth.json'
client = bigquery.Client.from_service_account_json( google_auth_json_file )

project = 'myProject'
dataset = 'myDataset'
ds = client.dataset(dataset)
query = "SELECT X,y,z FROM mytable;"

#--- Clear/create temp table
temp_table_name = 'myTempTable'
temp_tbl = myCreateTempTableFunction( client, project, dataset, temp_table_name )

#--- Create an async query job
job_name = 'vci-temp-sales-query-job'
job = client.run_async_query(job_name, query)
job.destination = temp_tbl
job.write_disposition = 'WRITE_TRUNCATE'
job.begin()

This script fails at the "job.begin()" line. I didn't know that named jobs live on beyond the end of the session or the execution of the job. How do I check if a named job already exists, and if it exists, how do I delete the existing named job to create a new one? Or do I have to create random or unique job names ever time I run an async job?

Upvotes: 2

Views: 1543

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33705

You need to use a unique job ID, since this is what the metadata for the operation is associated with. Referring to the querying data example, your code could be something like this:

job_name = 'vci-temp-sales-query-job_{}'.format(uuid.uuid4())

Upvotes: 2

Related Questions