BigQuery ArrayQueryParameter值列表最大长度及超长查询方案咨询

问题描述 #

使用Python API Client Library通过ArrayQueryParameter查询BigQuery时，当数组参数的values列表长度达到30万及以上时，查询失败并抛出SSL连接错误；长度在20万及以下时查询正常。官方文档未明确该参数的最大长度限制，需了解其最大值及超长列表的解决办法。

代码示例 #

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)

报错信息 #

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

解决方案 #

1. ArrayQueryParameter的隐性限制 #

BigQuery官方文档未明确标注ArrayQueryParameter的最大元素个数，但实际使用中，单个数组参数的总数据量（而非元素个数）受限于HTTP请求的最大负载。当元素过多（如30万条字符串），请求体体积会超过默认SSL连接或API网关的传输阈值，导致SSL连接中断。

2. 超长列表的替代方案 #

方案一：写入临时表后JOIN查询（推荐）

把超长列表写入BigQuery会话级临时表，通过JOIN替代IN UNNEST，这是最稳定的方案：

from google.cloud import bigquery

client = bigquery.Client()

# 定义临时表Schema
schema = [bigquery.SchemaField("name", "STRING")]
# 创建会话级临时表（会话结束自动删除）
temp_table = client.create_table(
    bigquery.Table(f"{client.project}.{client.location}.__tmp_metros", schema=schema),
    exists_ok=True
)

# 准备批量数据
LIST_LENGTH = 300000
values = [{"name": "Los Angeles-Long Beach-Anaheim, CA"} for _ in range(LIST_LENGTH)]
# 批量写入临时表
load_job = client.load_table_from_json(values, temp_table)
load_job.result()  # 等待数据写入完成

# 调整查询语句使用JOIN
query = """
    SELECT
        c.*
    FROM
        `bigquery-public-data.geo_us_boundaries.cbsa` c
    JOIN
        __tmp_metros t
    ON
        c.name = t.name
"""

# 执行查询
query_job = client.query(query)
results = query_job.result()
for row in results:
    print(row)

方案二：拆分列表为多批次查询后合并结果

若无法创建临时表，可将大列表拆分为多个小批次（如每批次10万条），分别查询后合并结果：

from google.cloud import bigquery

client = bigquery.Client()

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

LIST_LENGTH = 300000
values = ["Los Angeles-Long Beach-Anaheim, CA" for _ in range(LIST_LENGTH)]
batch_size = 100000
all_results = []

# 分批次执行查询
for i in range(0, LIST_LENGTH, batch_size):
    batch_values = values[i:i+batch_size]
    job_config = bigquery.QueryJobConfig(
        query_parameters=[
            bigquery.ArrayQueryParameter("metros", array_type="STRING", values=batch_values),
        ]
    )
    query_job = client.query(query, job_config=job_config)
    all_results.extend(list(query_job.result()))

# 处理合并后的结果
for row in all_results:
    print(row)

方案三：调整API客户端请求配置（临时缓解）

尝试增大HTTP客户端的超时时间和连接池大小，但仅能缓解部分场景，无法从根本解决大请求体问题：

from google.cloud import bigquery
from requests.adapters import HTTPAdapter

client = bigquery.Client()

# 调整HTTP适配器参数
adapter = HTTPAdapter(pool_connections=100, pool_maxsize=100, max_retries=5)
client._http.mount("https://", adapter)
client._http.timeout = 300  # 设置超时为5分钟

# 后续查询代码保持不变

内容的提问来源于stack exchange，提问作者Begona Echavarren