Run delete query from python in BigQuery

Question

I'm using the following python code to delete some rows in a bigquery table:

from google.cloud import bigquery

bigquery_client = bigquery.Client(project='my-project')

query_delete = f"""
    delete from `my_table` where created_at >= '2021-01-01'
"""
print(query_delete)
# query_delete
job = bigquery_client.query(query)
job.result()
print("Deleted!")

However, the rows don't seem to be deleted when doing this from python. What am I missing?

Have you tried running the same query query from the Cloud Console? Does it work there? — tituszban, Jul 30 '21 at 08:56
Alright. Have you tried running a non-delete query from your Python client? Including just querying and trying to modify records such as insert/update? — tituszban, Jul 30 '21 at 11:34
I'm assuming that it's a typo where you assign `query_delete` but then pass `query` to the job? — sgdata, Aug 02 '21 at 16:57
Some other common issues that might apply here: Billing is enabled for this project? The service credential has been assigned dataset & bigquery owner level permissions? — sgdata, Aug 02 '21 at 17:13
Can you do a print(job.result()) to see if there are any exceptions? — Ryan Yuan, Aug 03 '21 at 01:43

Sabil · Accepted Answer · 2021-08-05T07:56:38.190

I think below code snippet should work for you. You should pass query_delete instead of query

from google.cloud import bigquery

bigquery_client = bigquery.Client(project='my-project')

query_delete = f"""
  delete from `my_table` where created_at >= '2021-01-01'
"""

print(query_delete)
job = bigquery_client.query(query_delete)
job.result()
print("Deleted!")

Or you can try below formatted query

query_delete = (
  "DELETE from my_table "
  "WHERE created_at >= '2021-01-01'"
)

score 0 · Answer 2 · answered Aug 03 '21 at 06:26

You probably need to enable standard SQL in BigQuery table.

https://stackoverflow.com/a/42831957/1683626

It should be set as default as is noted in official documentation, but maybe you changed it to legacy sql.

In the Cloud Console and the client libraries, standard SQL is the default.

Run delete query from python in BigQuery

2 Answers2