Google Bq: Running Parameterized Queries Where Parameter Variable Is The Bq Table Destination

I am trying to run a SQL from the Linux Commandline for a BQ Table destination. This SQL script will be used for multiple dates, clients, and BQ Table destinations, so this would r

Solution 1:

From the documentation that you linked:

Parameters cannot be used as substitutes for identifiers, column names, table names, or other parts of the query.

I think what might work for you in this case, though, is performing the injection of the table name as a regular shell variable (instead of a query parameter). You'd want to make sure that you trust the contents of it, or that you are building the string yourself in order to avoid SQL injection. One approach is to have hardcoded constants for the table names and then choose which one to insert into the query text based on the user input.

Solution 2:

I thought I would just post my example here which only covers your question about creating a "dynamic table name", but you can also use my approach for your other variables. My approach was to do this operation directly in python just before doing the BigQuery API call, by leveraging python's internal time function (assuming you want your variables to be time-based).

Create BigQuery-table via Python BQ API:

from google.colab import auth
from datetime import datetime
from google.cloud import bigquery

auth.authenticate_user()
now = datetime.now()
current_time = now.strftime("%Y%m%d%H%M")

project_id = '<project_id>'
client = bigquery.Client(project=project_id)

table_id = "<project_id>.<dataset_id>.table_"
table_id = table_id + current_time
job_config = bigquery.QueryJobConfig(destination=table_id)

sql = """
SELECT
    dataset_id,
    project_id,
    table_id,
    CASE
      WHEN type = 1 THEN 'table'
      WHEN type = 2 THEN 'view'
      WHEN type = 3 THEN 'external'
      ELSE '?'
    END AS type,
    DATE(TIMESTAMP_MILLIS(creation_time)) AS creation_date,
    TIMESTAMP_MILLIS(creation_time) AS creation_time,
    row_count,
    size_bytes,
    round(safe_divide(size_bytes, (1000*1000)),1) as size_mb,
    round(safe_divide(size_bytes, (1000*1000*1000)),3) as size_gb
FROM (select * from `<project_id>:<dataset_id>.__TABLES__`)
ORDER BY dataset_id, table_id asc;
"""

query_job = client.query(sql, job_config=job_config)
query_job.result()
print("Query results loaded to the table {}".format(table_id))

# Output: # Query results loaded to the table <project_id>.<dataset_id>.table_202101141450

Feel free to copy and test it within a google colab notebook. Just fill in your own:

• <project_id>
• <dataset_id>