While writing query for bigQuery how to write table name and column name using variables instead of hard coding

Question

In the below mentioned query projectname-dataset-tableName is hardcoded in the query

query = """
SELECT city from bigquery-public-data.openaq.global_air_quality WHERE country = 'IN'
"""

How to write the same in more dynamic way? The fulltableid attribute is not returning compatible format.

query1 = """
SELECT city from """ + str(tableGAQ.full_table_id) + """ WHERE country = 'IN'
"""

score 0 · Answer 1 · answered Jul 17 '19 at 13:16

If you had the following query:

query = (
"SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013` "
'WHERE state = "TX" '
"LIMIT 100"
)

To use a variable to dynamically introduce your table ID, your query will look like this:

tableID = "`bigquery-public-data.usa_names.usa_1910_2013`"
query = (
"SELECT name FROM"+ tableID + "
'WHERE state = "TX" '
"LIMIT 100"

)

Notice that your table name has to be written between ` `.

1 Answers1