如何在 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 两行聚合结果，彻底解决“仅首值生效”的问题。