如何在 Bigquery 参数化查询中传递结构数组

百变鹏仔 4天前 #Python

文章标签数组

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

insert into mydataset.mytable(columna, columnb)    values (@valuea, @valueb)

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

从 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 列中插入简单（“标量”）值。但您也可以传递更复杂的参数：

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

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

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

from dataclasses import dataclass@dataclassclass country:    name: str    capital_city: str@dataclassclass 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 上的照片

文章推荐

如何在 Bigquery 参数化查询中传递结构数组

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

问题 1：arrayqueryparameter 的值必须是 structqueryparameter 的实例

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

高级Python元编程：动态代码生成和反射

如何在Python中实现梯度下降算法以寻找局部最小值？

Python程序删除数组中的重复元素

使用Networxx模块的超链接诱导主题搜索（HITS）算法- Python

使用索引列表在Python中查找元素的乘积