Bigquery API Python - Pass from table path clause as parameter

Question

I am trying to parameterize a specific query I want to run for multiple tables, Im using scalarQueryParameter to pass in strings to be used in specific fields. However, I am trying to pass in the table path that will be used in the FROM clause of the query. All the ways I have tried so far aren't working and I am wondering if what I'm trying to do is even possible.

query_insert = """
INSERT INTO
  `my_db.edp_analysis_test.edp_analysis`(
  SELECT
    DATE(ingestion_time) AS Ingestion_time,
    COUNT(ingestion_time) AS Rows_Written,
    @table_name AS Table_ID,
    @table_schema AS Dataset_ID,
  FROM
    @table_path
  WHERE
    ingestion_time IS NOT NULL
  GROUP BY
    ingestion_time
  ORDER BY
    ingestion_time)
"""

job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("table_name", "STRING", "name_val"),
        bigquery.ScalarQueryParameter("table_schema", "STRING", "schema_val"),
        bigquery.ScalarQueryParameter("table_path", "STRING", "my_db.project.table2")
    ]
)


query_job = client.query(query_insert, job_config=job_config)  # Make an API request.

I have put `` around the @table_path in the query, and also around the table path in the parameters. None of the options have worked, is there another way to go about parameterizing/passing in the table path into the query?

unnest_me · Accepted Answer · 2022-08-01 16:10:27Z

3

You can't actually do what I originally wanted to do, instead I passed a f string for the query and passed the table_path as a variable.

table_path = "my_db.project.test"

query_insert = f"""
INSERT INTO
  `my_db.edp_analysis_test.edp_analysis`(
  SELECT
    DATE(ingestion_time) AS Ingestion_time,
    COUNT(ingestion_time) AS Rows_Written,
    @table_name AS Table_ID,
    @table_schema AS Dataset_ID,
  FROM
    `{table_path}`
  WHERE
    ingestion_time IS NOT NULL
  GROUP BY
    ingestion_time
  ORDER BY
    ingestion_time)
"""

job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ScalarQueryParameter("table_name", "STRING", "name_val"),
        bigquery.ScalarQueryParameter("table_schema", "STRING", "schema_val")
    ]
)

answered Aug 1, 2022 at 16:10

unnest_me

2331 gold badge4 silver badges14 bronze badges

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

Comments

shollyman · Accepted Answer · 2022-08-01 16:10:08Z

1

From https://cloud.google.com/bigquery/docs/parameterized-queries

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

Likely ways forward are to use a python-specific solution such as format strings, or to use something like EXECUTE IMMEDIATE to compose a query string and execute it.

answered Aug 1, 2022 at 16:10

shollyman

4,42422 silver badges18 bronze badges

Collectives™ on Stack Overflow

Bigquery API Python - Pass from table path clause as parameter

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related