BigQuery Using arrays in parameterized queries

Question

I need to run parameterized queries using arrays.

Python Client Library for BigQuery API

id_pull = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]

query = "SELECT column1 FROM `table1` WHERE id = @get_id;"

query_params = [
    bigquery.ArrayQueryParameter(
        'get_id', 'INT64', id_pull)
]

job_config = bigquery.QueryJobConfig()
job_config.query_parameters = query_params

query_job = client.query(query, location='US', job_config=job_config)  #API request-starts query

results = query_job.result()  # Waits for job to complete.

I followed instructions from the documentation, however, this error after execution appears:

raise self._exception google.api_core.exceptions.BadRequest: 400 No matching signature for operator = for argument types: INT64, ARRAY. Supported signatures: ANY = ANY at [1:67]

Does someone what the problem is and how to fix it?

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-08-07T18:45:49

I think the issue is in your WHERE clause

Instead of

WHERE id = @get_id

it should be something like

WHERE id IN UNNEST(@get_id)

BigQuery Using arrays in parameterized queries

1 Answers