时间戳

首页 > 解决方案 > SQL UDF - 结构差异

google-bigquery - SQL UDF - 结构差异

问题描述

我们有一个表，其中包含 2 个“结构”类型的顶级列 - 一个是“之前”，一个是“之后”图像。结构模式是非平凡的 - 嵌套，数组的深度可变。是从复制发送给我们的，所以模式总是相同的（但模式当然可以在某个时候更新，但总是在一起）

目标是两个输入结构，返回之前和之后的 2 个结构“差异”，仅包含已更改的字段 - 本质上是复制源产生的更改的“增量”差异。我们知道有些东西发生了变化，但不是“什么”，因为我们得到了完整的前后图像。此原始数据位于 BQ 中，然后从那里进行处理，但需要确定更精细的更改以进行高阶 BQ 处理。

表模式非常宽（1000 个叶子字段），并且填充的数据相当空闲（因此快照的两侧都会出现很多空值） - 因此在执行超过 10 秒时需要尽可能地保持最佳性能数百万行。

所有东西都可以为空，以获得最大的灵活性。

所以变化可能看起来像：

数组：

递归使用上面的结构数组，如果这样更容易，可以放宽排序吗？

这可能是不可能的。

我还没有尝试过这个，因为它看起来真的很困难，所以我希望社区研究人员对此提供一些支持。我觉得阵列可能是困难的部分。在 Python 中可能有一种简单的方法，我什至没有使用 JOSN 工具进行一些 JSON 转换和比较？感觉这也是 BQ 内置的一个超级酷的功能，所以如果可以让它工作，将为它添加一个功能请求。

我想有一个 SQL UDF 以供重用（我们有 SQL 技能而不是 python，尽管如果在 python 中更容易那就没问题了），现在有了持久 SQL UDF 的新特性，这似乎是询问和测试该特性的正确时机！

sql def struct_diff（结构前，结构后）

(beforeChange, afterChange) - 签名类型，但愿意接受建议？

标签： google-bigquery

解决方案

似乎很难得到一段可重用的代码。由于目前不支持 SQL UDF 的递归函数，因此您不能对嵌套结构使用递归方法。

无效的递归定义

虽然，您可能能够获得一些特定的 SQL UDF 函数，具体取决于您的数组和结构结构。您可以使用这样的方法来比较结构。

CREATE TEMP FUNCTION final_compare(s1 ANY TYPE, s2 ANY TYPE) AS (
  STRUCT(s1 as prev, s2 as cur)
);

CREATE TEMP FUNCTION compare(s1 ANY TYPE, s2 ANY TYPE) AS (
  STRUCT(final_compare(s1.structA, s2.structA))  
);

您可以使用UNNESTarrays ，最终的 SQL UDF 将真正取决于您的数据。

正如@rtenha 所建议的那样，Python 可能更容易处理这个问题。

最后，我使用 JavaScript UDF 做了一些测试，结果基本相同，如果不是比 SQL UDF 最差的话。

控制台允许函数的递归定义，但是它会在执行期间失败。此外，javascript不允许ANY TYPE签名上的数据类型，因此您必须定义整个 STRUCT 定义或使用解决方法，例如应用于TO_JSON_STRING您的结构，以便将其作为字符串传递。

推荐阅读