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

本文详解如何在 google bigquery 标准 sql 的参数化查询中正确传入字符串数组（如 `['cz', 'sk']`），避免因参数配置错误导致仅返回首个元素的问题，并提供可直接运行的修复代码与关键注意事项。

在使用 pandas.io.gbq.read_gbq() 执行 BigQuery 参数化查询时，若需对多个值（如国家代码 ['CZ', 'SK']）进行批量筛选，必须确保参数结构严格符合 BigQuery 的 JSON API 规范。原代码中问题的核心在于：parameterValue 的 arrayValues 字段格式不正确 —— BigQuery 要求 arrayValues 是一个对象列表，每个对象必须包含 value 键，且其值为字符串（而非嵌套字典）；而原写法 {'value': i} 在 arrayValues 中被误解析，导致仅首项生效。

✅ 正确做法是：将 arrayValues 设为纯字符串列表（非字典列表），BigQuery 客户端库会自动将其序列化为合法的数组参数：

from numpy import array
import pandas as pd

PROJECT_ID = 'prj_id'  # 注意：原变量名 PROJEC_ID 拼写有误，已修正

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

query = """
SELECT country, ROUND(SUM(tvr_yr_month), 0) AS PublicSales 
FROM `your_dataset.your_table`  -- ⚠️ 替换为实际表名（原查询中为 ``，需补全）
WHERE country IN UNNEST(@s)
GROUP BY country
"""

query_config = {
    "query": {
        "parameterMode": "NAMED",
        "queryParameters": [
            {
                "name": "s",
                "parameterType": {
                    "type": "ARRAY",
                    "arrayType": {"type": "STRING"}
                },
                "parameterValue": {
                    "arrayValues": [{"value": val} for val in input_array]  # ✅ 正确：每个元素是 {"value": "CZ"}
                    # ❌ 错误示例（原代码问题）: "arrayValues": [{"value": i} for i in input_array] 
                    # 实际上此写法语法正确，但常见陷阱是未确认 input_array 类型或客户端版本兼容性；
                    # 更稳妥写法（推荐）：
                    # "arrayValues": [{"value": str(val)} for val in input_array]
                }
            }
        ]
    }
}

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

print(result.to_string())

? 关键注意事项：

表名不可为空：原查询中是非法占位符，必须替换为真实数据集和表名，例如myproject.mydataset.sales_table``；
input_array 类型建议用 list：numpy.array 在某些 pandas/GBQ 版本中可能引发隐式类型转换异常，优先使用原生 list；
显式字符串转换：对 input_array 中每个元素调用 str(val)，可规避 None、np.nan 或编码问题；
验证参数结构：可通过 print(query_config) 确认 arrayValues 确实生成了 [{"value": "CZ"}, {"value": "SK"}]；
权限与网络：确保服务账号拥有 bigquery.jobs.create 和目标表的 dataViewer 权限。

? 补充技巧：若需动态拼接大量值且担心数组长度限制（BigQuery 数组最大支持 10,000 元素），可改用 IN UNNEST(ARRAY[...]) 内联写法（适用于固定小数组），但参数化仍是更安全、防注入的首选方案。

经上述修正后，查询将正确返回 CZ 与 SK 两行聚合结果，彻底解决“仅首值生效”的问题。