Using query parameters in FROM statements

Question

I am running a DELETE FROM job on a BigQuery table using the python API. I am trying to make my code as reusable as possible by adding a variable for the table which can be scanned, for example:

dml_statement = (
    """
    DELETE FROM `my_project.my_dateset.detail_@market`
    WHERE order_date = @date
    """)

This unfortunately returns the error:

NotFound: 404 Not found: Table my_project:my_dataset.detail_@market was not found in location EU

If I cannot use variables in the FROM clause is there any other way I can make the selected table a dynamic input?

I also tried using wildcards and _TABLE_SUFFIX, but wildcard selections are not allowed with DELETE queries.

@JoeTaras I must be missing something, I can't see the solution in this post? — Ben P
I suppose yes. In that question, wants to know if exists in Google Big Query a way to replicate the behaviour of dynamic sql query of MS Sql Server — Joe Taras

Enrique Zetina Enrique Zetina · Accepted Answer · 2020-05-19T22:25:32

You can try with something like:

from google.cloud import bigquery

client = bigquery.Client()  

project = 'YOUR_PROJECT'
dataset = 'YOUR_DATASET'
table = 'YOUR_TABLE'

sql ="""DELETE FROM `{}.{}.{}` WHERE something IS NULL"""
query=sql.format(project,dataset,table)

query_job = client.query(query)

Using query parameters in FROM statements

1 Answers