Maximum list length of the BigQuery ArrayQueryParameter values parameter

Question

I am trying to query BigQuery through the Python API Client Library using query parameters, specifically an ArrayQueryParameter.

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT
        *
    FROM
        `bigquery-public-data.geo_us_boundaries.cbsa`
    WHERE
        name IN UNNEST(@metros)
"""

# Problematic list length
LIST_LENGTH = 300000

# Example values list
values = ["Los Angeles-Long Beach-Anaheim, CA" for i in range(LIST_LENGTH)]

# Generate BigQuery job config
job_config = bigquery.QueryJobConfig(
    priority=bigquery.QueryPriority.BATCH,
    query_parameters=[
        bigquery.ArrayQueryParameter("metros", array_type="STRING", values=values),
    ]
)

# Generate BigQuery query job
query_job = client.query(
    query,
    job_config=job_config,
)

# Obtain results
results = query_job.result()
for row in results:
    print(row)

When the list length in the ArrayQueryParameter "values" parameter is 300000 or longer (approximately), the query does not finish and if I inspect the BigQuery logs I am getting the following error log: HTTPSConnectionPool(host='bigquery.googleapis.com', port=443): Max retries exceeded with url: /bigquery/v2/projects/{...}/jobs?prettyPrint=false (Caused by SSLError(SSLEOFError(8, 'EOF occurred in violation of protocol (_ssl.c:2396)'))) 2.05274303188268 But with the length of the list being 200000 or lower, there is no problem and the query results are returned.

From this log I interpret that I am exceeding the maximum list length of this "values" parameter, but don't find this to be included in BigQuery documentation...

Which is the maximum list length of the BigQuery ArrayQueryParameter "values" parameter? Is there any workaround to be able to perform a query with a 300000 or longer list length?

Thank you in advance!

Answer 1

The documentation at https://cloud.google.com/bigquery/quotas#query_jobs states that

The request size can be up to 10 MB, including additional properties like query parameters.

So I guess this might be the limit you are hitting.