1

I am receiving this error Cannot set destination table in jobs with DDL statements

When I try to resubmit a job from the job.build_resource() function in the google.cloud.bigquery library.

It seems that the destination table is set to something like this after that function call.

'destinationTable': {'projectId': 'xxx', 'datasetId': 'xxx', 'tableId': 'xxx'},

Am I doing something wrong here? Thanks to anyone that can give me any guidance here.

EDIT:

The job is initially being triggered by this

query = bq.query(sql_rendered)

We store the job id and use it later to check the status.

We get the job like this

job = bq.get_job(job_id=job_id)

If it meets a condition, in this case it failed due to rate limiting. We retry the job.

We retry the job like this 

di = job._build_resource()
jo = bigquery.Client(project=self.project_client).job_from_resource(di)
jo._begin()

I think that's pretty much all of the code you need, but happy to provide more if needed.

dillon
  • 125
  • 2
  • 2
  • 6
  • Please share more of the code that you are using. It isn't possible to tell what is happening to cause the destination table to be set just from the error message. – Elliott Brossard Jun 28 '18 at 20:47
  • Is there a reason that you don't resubmit the original job? It's kind of weird to submit the one returned by `bq.get_job`, since that contains unrelated attributes like query statistics and so on (and in this case the destination table). – Elliott Brossard Jun 28 '18 at 21:08
  • The reason is that we are using Airflow. So it's passed through xcoms, so we are storing the job_id, grabbing that and then using it. Should we change that workflow? – dillon Jun 28 '18 at 21:16
  • The (easy) alternative would be to strip down the job resource that you get back just to the relevant fields, but a better long-term solution may be to propagate the original job or query that you want to run. It's hard to say which is more reasonable given your current setup, though. – Elliott Brossard Jun 28 '18 at 21:37

1 Answers1

1

You are seeing this error because you have a DDL statement in your query. What is happening is that the job_config is changing some values after the execution of the first query, particularly the job_config.destination . In order to try to overcome this issue, you could try to reset the value of job_config.destination to None after each job submission or use a different job_config for every query.

Philipp Sh
  • 967
  • 5
  • 11