Python BigQuery Error:400 configuration.query.destinationTable cannot be set for scripts

Question

Hello everyone what I am trying to do is through a query insert data to a tableA(), once data is inserted into Table A then delete the newly inserted values in A, then write the response/output into Table B that I created.

Here is my python code :

client = bigquery.Client()
#This is Table B
table_id = "ntest.practice.btabletest"
#here is the table I am writing my deleted output to
job_config = bigquery.QueryJobConfig(destination=table_id)


sql2 ="""
   INSERT INTO `ntest.practice.atabletest`(%s) VALUES (%s);
   DELETE FROM `ntest.practice.atabletest`
   WHERE name = 'HEART'
"""%(columns_aaa,valueaaa)

query_job1 = client.query(sql2,job_config=job_config)  # Make an API request.
query_job1.result()  # Waits for query to finish
print("Query results loaded to the table {}".format(table_id))

Yet, I get an error code saying:

google.api_core.exceptions.BadRequest: 400 configuration.query.destinationTable cannot be set for scripts

Any thoughts on how to fix this error? I don't believe, that my query is wrong, nor my tables or values are incorrect.

Refer to official documentation, you should consider using Data Manipulation Language. Please, take a look for this Stackoverflow thread. — aga
– aga, Commented Feb 13, 2020 at 9:06

Yun Zhang · Accepted Answer · 2020-02-13 17:42:54Z

0

Although BigQuery scripting doesn't support destination table, it doesn't seems that you'll need it for your specific query.

DELETE query never writes any data to a destination table. You may work around it by sending INSERT first then DELETE, this way, destination table will "work" (I mean BigQuery won't complaint about it). But you're getting an empty table.

answered Feb 13, 2020 at 17:42

Yun Zhang

5,6032 gold badges14 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sbecon Over a year ago

I seem to be running into a similar issue, and not quite sure how to resolve it: stackoverflow.com/questions/65590829/…

Collectives™ on Stack Overflow

Python BigQuery Error:400 configuration.query.destinationTable cannot be set for scripts

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related