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)
    )
)

Willian Fuks · Accepted Answer · 2017-06-16 17:04:38Z

6

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.

answered Jun 16, 2017 at 17:04

Willian Fuks

11.9k10 gold badges55 silver badges77 bronze badges

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

1 Comment

thn Over a year ago

Worked like a charm :) I cannot believe that I spent two days on this! Thank you Will.

Elliott Brossard · Accepted Answer · 2017-06-15 22:01:02Z

2

You need to use 'STRING' rather than 'ARRAY<STRING>' for the array element type, e.g.:

query_parameters=(
    bigquery.ArrayQueryParameter('ids', 'STRING', id_list)

The example from the querying data topic is:

def query_array_params(gender, states):
    client = bigquery.Client()
    query = """
        SELECT name, sum(number) as count
        FROM `bigquery-public-data.usa_names.usa_1910_2013`
        WHERE gender = @gender
        AND state IN UNNEST(@states)
        GROUP BY name
        ORDER BY count DESC
        LIMIT 10;
        """
    query_job = client.run_async_query(
        str(uuid.uuid4()),
        query,
        query_parameters=(
            bigquery.ScalarQueryParameter('gender', 'STRING', gender),
            bigquery.ArrayQueryParameter('states', 'STRING', states)))
    query_job.use_legacy_sql = False

    # Start the query and wait for the job to complete.
    query_job.begin()
    wait_for_job(query_job)
    print_results(query_job.results())

answered Jun 15, 2017 at 22:01

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

4 Comments

Elliott Brossard Over a year ago

You are also setting query_job.use_legacy_sql = False, right?

thn Over a year ago

Yes, I set it to False.

Elliott Brossard Over a year ago

Ah, I think it might be because query_parameters is expected to be a tuple. What if you put an extra set of parentheses around bigquery.ArrayQueryParameter(...)?

thn Over a year ago

I thought of that too. The single parenthesis around bigquery.ArrayQueryParameter(...) make it a tuple but it seems that tuple elements need to be iterable which are not, in this case, they are instances of ArrayQueryParameter. I tried that with double parenthesis too, no luck.

Krystian · Accepted Answer · 2019-12-12 10:21:31Z

0

Above answers are a better solution but you may find a use for this too whe quickly drafting something in notebooks:

turn a list into a string of date values, comma-separated and in quotes. Then pass the string into the query like so:

id_list = ['id1', 'id2'] 
# format into a query valid string
id_string = '"'+'","'.join(id_list)+'"'

client = bigquery.Client()
query = f"""
    SELECT id
    FROM `my-db`
    WHERE id in {id_string}
"""
query_job=client.query(query) 
results = query_job.result()

answered Dec 12, 2019 at 10:21

Krystian

112 bronze badges

Comments

Andrey E · Accepted Answer · 2021-08-03 19:21:10Z

If you want to use the simple query like client.query, not client.run_async_query as shown in the answers above. You can to pass an additional parameter QueryJobConfig. Simply add your arrays to query_parameters using bigquery.ArrayQueryParameter.

The following code worked for me:

    query = f"""
                SELECT distinct pipeline_commit_id, pipeline_id, name
                FROM `{self.project_id}.{self.dataset_id}.pipelines_{self.table_suffix}`,
                UNNEST(labels) AS label
                where label.value IN UNNEST(@labels)
           """
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ArrayQueryParameter('labels', 'STRING', labels)
        ]
    )
    query_job = self.client.query(query, job_config=job_config)

Based on those examples: https://cloud.google.com/bigquery/docs/parameterized-queries

Collectives™ on Stack Overflow

Passing Array Parameter to SQL for BigQuery in Python

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related