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

本文详解如何在 google bigquery 的标准 sql 参数化查询中正确传递字符串数组（如 `['cz', 'sk']`），避免因参数配置错误导致仅返回首个元素的问题，并提供可直接运行的完整解决方案。

在 BigQuery 中使用 UNNEST(@param) 处理数组参数时，常见错误并非逻辑缺陷，而是 参数配置层级与结构不匹配。您原始代码中 query_config 的嵌套结构存在关键问题：'queryParameters' 被错误地置于 'query' 字典内，而 pd.io.gbq.read_gbq() 实际期望的是顶层 configuration 字典直接包含 'query' 键——且该键值应为符合 BigQuery REST API 规范 的纯字典，而非嵌套的 {'query': {...}}。

更关键的是，arrayValues 的构造方式虽语法合法，但 pd.io.gbq 对参数序列化的兼容性较弱，易引发静默截断。推荐采用更健壮、官方推荐的方式：直接使用字典形式的 parameterValue，并确保 configuration 结构扁平正确。

以下是修正后的完整可运行代码：

from numpy import array
import pandas as pd

PROJECT_ID = 'prj_id'  # 注意：变量名应为 PROJECT_ID（原 PROJEC_ID 拼写错误）

input_array = ['CZ', 'SK']  # 直接使用 Python list，无需 numpy array；dtype=object 非必需

query = """
SELECT country, ROUND(SUM(tvr_yr_month), 0) AS PublicSales 
FROM `your_dataset.your_table`  -- ⚠️ 请务必替换为真实表名（原查询中为 ``，会报错）
WHERE country IN UNNEST(@countries)
GROUP BY country
"""

# ✅ 正确的 configuration 结构：顶层为 'query'，无多余嵌套
query_config = {
    "query": {
        "parameterMode": "NAMED",
        "queryParameters": [
            {
                "name": "countries",
                "parameterType": {
                    "type": "ARRAY",
                    "arrayType": {"type": "STRING"}
                },
                "parameterValue": {
                    "arrayValues": [{"value": country} for country in input_array]
                }
            }
        ]
    }
}

# 执行查询
result = pd.io.gbq.read_gbq(
    query,
    project_id=PROJECT_ID,
    dialect='standard',
    configuration=query_config
)

print(result.to_string(index=False))

关键修复点说明：

• ✅ configuration 结构修正：query_config 必须是 {"query": {...}} 形式，而非 {"query": {"query": {...}}}；您的原始代码中多了一层 'query' 键嵌套，导致 BigQuery 客户端无法识别参数。
• ✅ 表名占位符替换：原始查询中的 `是无效引用，必须替换为实际的..格式（如'my-proj.sales_data.transactions'`）。
  ✅ 移除冗余 numpy array：Python 原生 list 更安全、更易序列化；numpy.array 在此场景下无优势，反而可能引入类型隐式转换风险。
  ✅ 参数名语义化：将 's' 改为 'countries'，提升可读性与维护性。

  额外建议：

  • 若仍遇到问题，可启用调试日志：pd.io.gbq.read_gbq(..., verbose=True) 查看底层请求详情。
  • 对于超大数组（>1000 元素），考虑分批查询或改用 google-cloud-bigquery 客户端库（Client.query().result().to_dataframe()），其对复杂参数支持更完善。
  • 始终验证 UNNEST 行为：可在 BigQuery 控制台中先用硬编码测试 WHERE country IN UNNEST(['CZ','SK'])，确认基础逻辑无误。

  正确配置后，输出将完整包含所有匹配国家的数据行，例如：

   country  PublicSales
      CZ      1272308
      SK       984521