如何在 BigQuery 参数化查询中正确传递并处理字符串数组

本文详解如何在 bigquery 标准 sql 的参数化查询中正确传入字符串数组（如 `['cz', 'sk']`），避免因参数配置错误导致仅返回部分结果，并提供可直接运行的完整示例与关键注意事项。

在使用 pandas.io.gbq.read_gbq() 执行 BigQuery 参数化查询时，若需对 IN UNNEST(@param) 子句传入多个字符串值（例如国家代码 ['CZ', 'SK']），核心问题往往出在参数配置格式不兼容 BigQuery 的 ARRAY 类型预期结构——尤其是 parameterValue.arrayValues 的构造方式。

你原代码中使用了：

'parameterValue': {'arrayValues': [{'value': i} for i in input_array]}

这看似合理，但 BigQuery Python 客户端（google-cloud-bigquery）实际要求 arrayValues 中每个元素必须是 {'value': ...} 形式，且 value 字段本身需为原始 Python 值（如字符串），而非嵌套字典。更关键的是：pandas.io.gbq.read_gbq() 对复杂参数配置的支持较弱，推荐改用 google.cloud.bigquery.Client 原生 API，它对 ARRAY 参数有明确、稳定的支持。

✅ 正确做法如下（推荐方案）：

from google.cloud import bigquery
import pandas as pd

PROJECT_ID = 'prj_id'
input_array = ['CZ', 'SK']  # 直接使用 Python list，无需 numpy.array

client = bigquery.Client(project=PROJECT_ID)

query = """
SELECT country, ROUND(SUM(tvr_yr_month), 0) AS PublicSales 
FROM `your_dataset.your_table`  -- ⚠️ 替换为真实表名！
WHERE country IN UNNEST(@countries)
GROUP BY country
"""

job_config = bigquery.QueryJobConfig(
    query_parameters=[
        bigquery.ArrayQueryParameter(
            "countries", "STRING", input_array
        )
    ]
)

query_job = client.query(query, job_config=job_config)
result_df = query_job.to_dataframe()

print(result_df)

? 关键修正点说明：

• ✅ 使用 bigquery.ArrayQueryParameter("countries", "STRING", ['CZ','SK']) 替代手动拼装 JSON 式参数配置，语义清晰、零出错；
• ✅ UNNEST(@countries) 在标准 SQL 中能正确展开字符串数组，匹配多行结果；
• ✅ 表名占位符 `your_dataset.your_table` 必须替换为真实项目/数据集/表路径（你原查询中为空反引号 `` `` 是语法错误，将导致查询失败或静默截断）；
• ❌ 避免 numpy.array(..., dtype=object)：BigQuery 客户端对 numpy 类型兼容性差，统一用原生 list[str]；
• ❌ 不要混用 pandas.io.gbq.read_gbq() 与复杂参数配置：该函数已标记为 legacy，对 ARRAY/STRUCT 等高级参数支持不完善，易丢数据。

? 额外验证技巧：
执行前可打印 query_job.query 和 query_job.configuration.query.query_parameters 检查参数是否注入成功；也可在 BigQuery Console 中粘贴相同 SQL + 手动设置 @countries = ["CZ","SK"] 测试逻辑是否正确。

总结：数组参数失效的根本原因，不是循环逻辑缺陷，而是客户端参数序列化与 BigQuery 服务端解析之间的格式错配。坚持使用 google-cloud-bigquery 官方 SDK 的类型化参数构造器（如 ArrayQueryParameter），是确保数组完整传递的最可靠方式。