Passing Array Parameter to SQL for BigQuery in Python

Question

I have a set of IDs (~200k) and I need to get all the rows in a BigQuery Table with those IDs. I tried to construct a list in python and pass it as a parameter to the SQL query using @ but I get TypeError: 'ArrayQueryParameter' object is not iterable error. Here is the code I tried (very similar to https://cloud.google.com/bigquery/querying-data#running_parameterized_queries):

id_list = ['id1', 'id2'] 
query = """
    SELECT id
    FROM `my-db`
    WHERE id in UNNEST(@ids)
"""
query_job = client.run_async_query(
    str(uuid.uuid4()),
    query,
    query_parameters=(
        bigquery.ArrayQueryParameter('ids', 'ARRAY<STRING>', id_list)
    )
)

Answer 1

Probably the issue here is that you are not passing a tuple to the function.

Try adding a comma before closing the parenthesis, like so:

id_list = ['id1', 'id2'] 
query = """
    SELECT id
    FROM `my-db`
    WHERE id in UNNEST(@ids)
"""
query_job = client.run_async_query(
    str(uuid.uuid4()),
    query,
    query_parameters=(
        bigquery.ArrayQueryParameter('ids', 'STRING', id_list),
    )
)

In Python if you do:

t = (1)

and then run:

type(t)

You will find the result to be int. But if you do:

t = (1,)

Then it results in a tuple.