在Google的Bigquery中，SQL查询可以参数化。如果您不熟悉这个概念，它基本上意味着您可以将 SQL 查询编写为参数化模板，如下所示：

INSERT INTO mydataset.mytable(columnA, columnB)
    VALUES (@valueA, @valueB)

并分别传递值。这有很多好处：

• 查询比通过字符串连接构建的查询更具可读性
• 代码更加健壮和工业化
• 这是针对 SQL 注入攻击的强大保护（强制 XKCD）

乍一看，从 Python 脚本传递查询参数似乎很简单。例如：

from google.cloud.bigquery import (
    Client,
    ScalarQueryParameter,
    ArrayQueryParameter,
    StructQueryParameter,
    QueryJobConfig,
)

client=Client()

client.query("
INSERT INTO mydataset.mytable(columnA, columnB)
    VALUES (@valueA, @valueB)
", job_config=QueryJobConfig(
    query_parameters=[
        ScalarQueryParameter("valueA","STRING","A"), 
        ScalarQueryParameter("valueB","STRING","B")
])

上面的示例在 A 列和 B 列中插入简单（“标量”）值。但您也可以传递更复杂的参数：

• 数组（ArrayQueryParameter）
• 结构（StructQueryParameter）

当您想要插入结构数组时，就会出现问题：有很多陷阱，几乎没有文档，网络上有关该主题的资源也很少。本文的目标就是填补这一空白。

如何使用参数化查询在 bigquery 中持久保存结构数组

让我们定义要存储在目标表中的以下对象

from dataclasses import dataclass

@dataclass
class Country:
    name: str
    capital_city: str

@dataclass
class Continent:
    name: str
    countries: list[Country]

通过调用此参数化查询

query = UPDATE continents SET countries=@countries WHERE name="Oceania"

遵循浅薄文档的第一次尝试是

client.query(query, 
    job_config=QueryJobConfig(query_parameters=[
        ArrayQueryParameter("countries", "RECORD", [
             {name="New Zealand", capital_city="Wellington"},
             {name="Fiji", capital_city="Suva"} ...]
]))

这会惨败

AttributeError：'dict'对象没有属性'to_api_repr'

问题 1：ArrayQueryParameter 的值必须是 StructQueryParameter 的实例

事实证明，构造函数的第三个参数 - value - 必须是 StructQueryParameter 实例的集合，而不是直接想要的值。那么让我们来构建它们：

client.query(query, 
job_config=QueryJobConfig(query_parameters=[
    ArrayQueryParameter("countries", "RECORD", [
    StructQueryParameter("countries",
        ScalarQueryParameter("name", "STRING", ct.name), 
        ScalarQueryParameter("capital_city", "STRING", ct.capital_city)
    )
    for ct in countries])
]))

这次有效...直到你尝试设置一个空数组

client.query(query, 
    job_config=QueryJobConfig(
    query_parameters=[
        ArrayQueryParameter("countries", "RECORD", [])
]))

ValueError：缺少空数组的详细结构项类型信息，请提供 StructQueryParameterType 实例。

陷阱 n°2：提供完整的结构类型作为第二个参数

错误消息非常清楚：“RECORD”不足以让 Bigquery 知道如何处理空数组。它需要完整详细的结构。就这样吧

client.query(query, job_config=QueryJobConfig(query_parameters=[
    ArrayQueryParameter("countries",
        StructQueryParameterType(
            ScalarQueryParameterType("STRING","name"),
            ScalarQueryParameterType("STRING","capital_city")
        ), [])
]))

（注意 ...ParameterType 构造函数的参数顺序与 ...Parameter 构造函数相反。这只是路上的另一个陷阱...）

现在它也适用于空数组，耶！

最后一个需要注意的问题：StructQueryParameterType 的每个子字段都必须有一个名称，即使第二个参数（名称）在构造函数中是可选的。它实际上对于子字段是强制性的，否则你会得到一种新的错误

空结构字段名称

我想这就是我们完成查询参数中记录数组的使用所需要知道的一切，我希望这会有所帮助！

感谢您的阅读！我是 Matthieu，Stack Labs 的数据工程师。
如果您想了解 Stack Labs 数据平台或加入热情的数据工程团队，请联系我们。

Denys Nevozhai 在 Unsplash 上的照片