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?

consider voting up answers that helped

Mikhail Berlyant
– Mikhail Berlyant

2018-08-08 16:31:05 +00:00
Commented Aug 8, 2018 at 16:31 — Mikhail Berlyant
– Mikhail Berlyant, Commented Aug 8, 2018 at 16:31

Elliott Brossard · Accepted Answer · 2018-08-07 18:51:16Z

1

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)

edited Aug 7, 2018 at 18:51

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

answered Aug 7, 2018 at 18:45

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

BigQuery Using arrays in parameterized queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related