Error “Cannot set destination table in jobs with DML statements” even though I'm not setting the destination table

Question

I have the following code which I'm using to select data from one bigquery table and insert into another, after first deleting data in the destination:

#delete
sql = """
delete from `b` 
where _date_ = '2019-10-24'
"""
query_job = client.query(sql)
results = query_job.result()

#insert
sql = """
insert into `b` (col1,col2) 
select col1, col2 from a
where a._date_ = '2019-10-24'
"""
query_job = client.query(sql)
results = query_job.result()

It runs successfully. However, I want to parameterise the date that I run for so I've followed the examples at https://cloud.google.com/bigquery/docs/parameterized-queries and changed it to this:

query_params = [
        bigquery.ScalarQueryParameter("_date_", "STRING", "2019-10-24")
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params

#delete
sql = """
delete from `b` 
where _date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()

#insert
sql = """
insert into `b` (col1,col2)
select col1, col2 from a
where a._date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()

This now fails with error on the insertion:

Cannot set destination table in jobs with DML statements

The deletion worked fine!

I've googled around and discovered examples (e.g. Cannot set destination table in jobs with DML statements in BigQuery) of this error occurring when the destination table is specified in the job_config, but I am not doing that, so I'm a little confused.

Why does my supplying a query parameter cause the code to fail with this error?

I think you need to reset the job_config after the first query. Can you try that and see? — Elliott Brossard
Hi Elliott, nice to see you on here instead of twitter for a change :) I can try, what do you mean by reset? Just declare it again? — jamiet
Yeah, just try assigning job_config itself plus the query parameters prior to running the next statement. If I remember correctly, I think that the job_config that you pass into client.query gets modified. — Elliott Brossard
Thank you @ElliottBrossard, that worked. If you post your comment as an answer I'll mark it as such. — jamiet

Elliott Brossard Elliott Brossard · Accepted Answer · 2019-10-25T15:06:00

The problem is that running a DML statement updates the destination table within the job_config that you pass to client.query. You need to reset it in between like this:

query_params = [
        bigquery.ScalarQueryParameter("_date_", "STRING", "2019-10-24")
]
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params

#delete
sql = """
delete from `b` 
where _date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()

# reset config
job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params

#insert
sql = """
insert into `b` (col1,col2)
select col1, col2 from a
where a._date_ = @_date_
"""
query_job = client.query(sql, job_config=job_config)
results = query_job.result()

Error “Cannot set destination table in jobs with DML statements” even though I'm not setting the destination table

1 Answers