雪花中的嵌套变体更新和删除 [英] Nested variant updating and deleting in snowflake

问题描述

当前将变更数据捕获事件从MongoDB流式传输到Snowflake，希望将它们应用于已存在的原始数据。

假设我有一个这样的表：

+---------------------+-----------------+-----------+
|         key         |      value      | document  |
+---------------------+-----------------+-----------+
| foo.bar             | "changed value" | <variant> |
| foo.stuff.anArray.1 | 1000            | <variant> |
| ...                 | ...             | ...       |
+---------------------+-----------------+-----------+

其中VARIANT包含非常嵌套的JSON ex：

{
    "foo": {
        "bar": "Some info",
        "baz": "Other info",
        "stuff": {
            "anArray": [1, 2, 3],
            "things": "More nested info"
        }
    }
}

我要使用OBJECT_DELETE和OBJECT_INSERT函数更新Snowflake中的此嵌套变量数据。

已尝试创建js UDF，但不支持eval()。

其他方法(如编写执行key.split(".")的UDF，然后递归遍历结构并更新字段)似乎需要很长时间，在某些情况下JavaScript out of memory error: UDF thread memory limit exceeded会失败。

正在寻找更有效的方法来解决此问题。

推荐答案

我遇到过一个similar problem，并使用了一个通用的UDF来解决它。以下是将解决您需要的内容的UDF实现示例：

create or replace function edit_nested_entity("variant_object" variant, "path" string, "value" string)
returns variant
language javascript
as
$$
// https://stackoverflow.com/questions/6491463/accessing-nested-javascript-objects-and-arrays-by-string-path?page=1&tab=votes#tab-top
    Object.byString = function(o, s) {
        s = s.replace(/[(w+)]/g, '.$1'); // convert indexes to properties
        s = s.replace(/^./, '');           // strip a leading dot
        var a = s.split('.');
        for (var i = 0, n = a.length; i < n; ++i) {
            var k = a[i];
            if (k in o) {
                o = o[k];
            } else {
                return;
            }
        }
        return o;
   }
   // get the entity base
   nested_entity = Object.byString(variant_object, path)
   // update the value
   nested_entity = value
   return variant_object;
$$;

现在您需要运行以下SQL命令来实现您需要的功能：

UPDATE t1
SET document = edit_nested_entity(document, key, value) 

您可能会对此UDF进行一些微调，使其更通用(或对不同的数据类型使用不同的UDF)，但这会起作用。

这篇关于雪花中的嵌套变体更新和删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！