CODEWITHSUNDEEP
pythongoogle-bigquery
rob
rob

Reputation: 2146

Issue parameterized queries in BigQuery against same column

I'm using the python BigQuery client to query a simple table, and I'd like to return results within a given time range based on a single date column received_datetime.

from google.cloud import bigquery
query_body = """SELECT * FROM `my_table` 
                WHERE received_datetime >= @received_datetime
                AND received_datetime =< @received_datetime
                LIMIT 100"""

query_parameters = [
    ScalarQueryParameter('received_datetime', 'DATE', datetime.datetime(2022, 12, 7, 0, 0)), 
    ScalarQueryParameter('received_datetime', 'DATE', datetime.datetime(2022, 11, 30, 0, 0))
]


job_config = bigquery.QueryJobConfig(query_parameters=query_parameters)
query_job = client.query(query_string, job_config=job_config).result()

However when attempting to query a 'range' like this, I get

BadRequest: 400 POST https://bigquery.googleapis.com/bigquery/v2/projects/cool-project/jobs?prettyPrint=false: Duplicate query parameters named received_datetime

There is clearly some issue in how I'm formulating this parameterized query..as I know multiple WHERE clauses against a single column is totally acceptable. Sadly the docs aren't really helpful in this regard.

Is there a correct way to use parameterized queries for multiple values on a single column?

Upvotes: 0

Views: 682

Answers (1)

Mazlum Tosun
Mazlum Tosun

Reputation: 6572

I think you have to pass the second date param with another name, because you passed the same param and name twice :

from google.cloud import bigquery
query_body = """SELECT * FROM `my_table` 
                WHERE received_datetime >= @start_received_datetime
                AND received_datetime =< @end_received_datetime
                LIMIT 100"""

query_parameters = [
    ScalarQueryParameter('start_received_datetime', 'DATE', datetime.datetime(2022, 12, 7, 0, 0)), 
    ScalarQueryParameter('end_received_datetime', 'DATE', datetime.datetime(2022, 11, 30, 0, 0))
]


job_config = bigquery.QueryJobConfig(query_parameters=query_parameters)
query_job = client.query(query_string, job_config=job_config).result()

I used the following params : start_received_datetime and end_received_datetime for the received_datetime column.

Upvotes: 1

Related Questions