如何合并雪花中两个不同表中的变体列数据

Question

我有两个表，每个表大约有数千列。表包含变体数据列。我想根据变体数据中的键值合并变体数据列。

例如：

表1列名称：SRC value ： {col1：val1，col2：val2，col3：val3}

表 2：列名称：SRC value ： {col1：val1，col2：val2，col4：val4}

我想合并后输出： 表1 ： SRC = {col1：val1，col2：val2，col3：val3，col4：val4}

我希望更新变体列中的现有键并将新键插入表中。

我已经尝试过object_insert()方法，但它可以一次更新一个字段，并且很难在语法上实现数千列。如何使用多个字段实现此目的。 谁能帮我解决这个问题？

Answer 1

如果您事先知道键，并且变体是对象，那么这将起作用：

with 
a as ( select 1 id, parse_json('{"col1":"val1","col2":"val2","col3":"val3"}')::variant src),
b as ( select 1 id, parse_json('{"col1":"val1","col2":"val2","col4":"val4"}')::variant src)
select 
object_construct(
'col1', coalesce(b.src:col1,a.src:col1), 
'col2', coalesce(b.src:col2,a.src:col2), 
'col3', coalesce(b.src:col3,a.src:col3), 
'col4', coalesce(b.src:col4,a.src:col4)
) as src
from a left join b on a.id=b.id;

输出为：

SRC              
-----------------
{   "col1": "val1",   "col2": "val2",   "col3": "val3",   "col4": "val4" }

如果你事先不知道键，你可以展平对象，连接键，合并((值，并使用object_agg((组装组合的对象。

希望有帮助

Answer 2

你可以使用 Javascript UDF 来做到这一点。下面是一个朴素的示例，您可以在此基础上进行非常简单的浅层合并：

-- Create an example table
create or replace transient table test_table (column1 VARIANT, column2 VARIANT);
-- Insert some sample data
INSERT OVERWRITE INTO test_table (column1, column2)
select PARSE_JSON('{"a": "row1_val_a", "c": "row1_val_c"}'), PARSE_JSON('{"b": "row1_val_b"}')
union all
select PARSE_JSON('{"a": "row2_val_a", "b": "row2_val_b"}'), PARSE_JSON('{"c": "row2_val_c"}')
;
-- Have a look at the table
select * from test_table;
-- Create the javascript UDF that merges the two columns
CREATE OR REPLACE FUNCTION merge_json(OBJ_1 VARIANT, OBJ_2 VARIANT)
RETURNS variant
LANGUAGE JAVASCRIPT
AS 
$$
function extend(obj, src) {
for (var key in src) {
if (src.hasOwnProperty(key)) obj[key] = src[key];
}
return obj;
}
return extend(OBJ_1, OBJ_2)
$$;
-- Use the UDF in a SQL query
select merge_json(column1, column2) from test_table;

此示例假定VARIANT对象位于同一表中，只是位于不同的列中。您可以更改它以轻松地从不同的表中获取变体。