如何在 BigQuery 参数化查询中传递结构数组
在 BigQuery 中使用参数化查询不仅可以防止 SQL 注入，还能提高复杂查询的复用性。对于简单的标量类型（如 STRING、INT64）传递参数非常直观，但当我们需要传递复杂的数据结构，例如结构数组（ARRAY of STRUCT）时，许多开发者会遇到类型定义和参数构造的难题。
本文将详细介绍如何在 BigQuery 参数化查询中正确地传递结构数组，并提供 Python 客户端的完整代码示例。
1. 核心概念：ARRAY 与 STRUCT
在 BigQuery 中，结构数组的本质是：一个数组，其中的每个元素都是一个结构体（STRUCT）。
STRUCT：类似于其他语言中的对象或字典，包含多个命名字段，例如
STRUCT<name STRING, age INT64>。ARRAY：相同类型元素的有序列表，例如
ARRAY<INT64>。
将两者结合，我们就可以定义一个结构数组类型：ARRAY<STRUCT<name STRING, age INT64>>。
2. SQL 查询语句的编写
在 BigQuery SQL 中，处理数组参数通常需要配合 UNNEST() 函数，将数组展开为多行记录以便进行关系型操作。
假设我们需要根据传入的用户列表（包含姓名和年龄）查询相关数据，SQL 语句如下：
-- 假设 @users 是一个 ARRAY<STRUCT<name STRING, age INT64>> 类型的参数 SELECT user.name, user.age FROM UNNEST(@users) AS user WHERE user.age > 18;
3. Python 客户端实现
在 Python 中，使用 google-cloud-bigquery 库构造结构数组参数时，必须显式地定义嵌套的类型结构。我们需要用到 StructQueryParameterType、ArrayQueryParameterType 和 StructQueryParameter。
以下是完整的代码示例：
from google.cloud import bigquery
# 1. 初始化客户端
client = bigquery.Client()
# 2. 定义 SQL 查询
query = """
SELECT
user.name,
user.age
FROM UNNEST(@users) AS user
WHERE user.age > @min_age
"""
# 3. 构造结构数组参数
# 3.1 首先定义 STRUCT 内部的字段类型
struct_type = bigquery.StructQueryParameterType([
bigquery.StructQueryParameterType.Field("name", "STRING"),
bigquery.StructQueryParameterType.Field("age", "INT64")
])
# 3.2 然后定义 ARRAY 类型，并将上面的 struct_type 作为其元素类型
array_type = bigquery.ArrayQueryParameterType(struct_type)
# 3.3 构造实际的数据值
# 注意：StructQueryParameter 的参数依次为：名称(对于数组内的元素可为None), 字段1值, 字段2值...
struct_values = [
bigquery.StructQueryParameter(
None, # 数组内的 STRUCT 不需要指定参数名称
bigquery.ScalarQueryParameter("name", "STRING", "Alice"),
bigquery.ScalarQueryParameter("age", "INT64", 25)
),
bigquery.StructQueryParameter(
None,
bigquery.ScalarQueryParameter("name", "STRING", "Bob"),
bigquery.ScalarQueryParameter("age", "INT64", 17)
)
]
# 3.4 组合成最终的 ARRAY 参数
users_param = bigquery.ArrayQueryParameter(
name="users",
array_type=array_type,
values=struct_values
)
# 4. 构造其他普通参数
min_age_param = bigquery.ScalarQueryParameter("min_age", "INT64", 18)
# 5. 组装查询作业配置
job_config = bigquery.QueryJobConfig(
query_parameters=[users_param, min_age_param]
)
# 6. 执行查询
query_job = client.query(query, job_config=job_config)
# 7. 打印结果
for row in query_job.result():
print(f"Name: {row.name}, Age: {row.age}")4. 常见问题与注意事项
1. 类型必须严格匹配
BigQuery 是强类型系统。如果 STRUCT 中定义了 INT64，传入的值必须是可以转化为整型的数据，不能传入字符串，否则查询会报类型不匹配错误。
2. 参数大小限制
通过参数化查询传递的数据量不宜过大。BigQuery 单次查询请求的大小限制通常为 10MB（具体请参考官方最新文档）。如果结构数组非常大，建议将数据先写入 BigQuery 的临时表，然后再通过 SQL 关联查询该表。
3. 嵌套结构
BigQuery 支持更深层次的嵌套（如 STRUCT 内部包含 ARRAY，ARRAY 内部再包含 STRUCT）。你可以通过递归构建 StructQueryParameterType 和对应的 Parameter 来实现，逻辑与上述二维结构一致，但层级越深，代码构造越复杂。
4. 在线测试工具
如果你想在可视化界面中快速测试此类参数化查询，可以访问 www.ipipp.com 提供的在线 BigQuery SQL 模拟调试器，验证你的参数格式和 SQL 逻辑是否正确。
总结
在 BigQuery 参数化查询中传递结构数组，核心在于类型的嵌套定义。只要按照 "先定义 STRUCT 字段类型 -> 再包装为 ARRAY 类型 -> 最后填充数据" 的步骤，就能在客户端代码中优雅地构建复杂的查询参数，从而充分发挥 BigQuery 处理嵌套和重复数据的能力。