时间戳

首页 > 解决方案 > Snowflake - 使目标表模式与源表变量列值保持同步

snowflake-cloud-data-platform - Snowflake - 使目标表模式与源表变量列值保持同步

问题描述

我将数据摄取到source_table带有 AVRO 数据的表中。此表中有一列表示"avro_data"将填充变体数据。我计划将数据复制到结构化表target_table中，其中列的名称和数据类型与avro_data源表中的字段相同。例子： select avro_data from source_table

{"C1":"V1", "C2", "V2"}

这将导致

select * from target_table
------------
| C1  | C2 |
------------
| V1  | V2 |
------------

我的问题是，当avro_data进化模式和新字段被添加时，如何target_table通过在目标表中添加等效列来保持模式同步？

雪花中是否有任何开箱即用的东西来实现这一目标，或者是否有人创建了任何代码来做类似的事情？

标签： snowflake-cloud-data-platformsnowflake-schema

解决方案

这里有一些东西可以帮助您入门。它展示了如何获取一个变体列并解析出内部列。这使用了 Snowflake 示例数据数据库中的一个表，该表并不总是相同的。您可以调整表名和列名。

SELECT DISTINCT regexp_replace(regexp_replace(f.path,'\\\\[(.+)\\\\]'),'(\\\\w+)','\"\\\\1\"')                      AS path_name,       -- This generates paths with levels enclosed by double quotes (ex: "path"."to"."element").  It also strips any bracket-enclosed array element references (like "[0]")
                DECODE (substr(typeof(f.value),1,1),'A','ARRAY','B','BOOLEAN','I','FLOAT','D','FLOAT','STRING')     AS attribute_type,  -- This generates column datatypes of ARRAY, BOOLEAN, FLOAT, and STRING only
                REGEXP_REPLACE(REGEXP_REPLACE(f.path, '\\\\[(.+)\\\\]'),'[^a-zA-Z0-9]','_')                         AS alias_name       -- This generates column aliases based on the path
FROM
        "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."JCUSTOMER",
        LATERAL FLATTEN("CUSTOMER", RECURSIVE=>true) f
WHERE   TYPEOF(f.value) != 'OBJECT'
        AND NOT contains(f.path, '[');

这是从此处修改的代码片段：https ://community.snowflake.com/s/article/Automating-Snowflake-Semi-Structured-JSON-Data-Handling 。博客作者将此部分代码归功于一位同事。

虽然存储过程的当前化身将从变体中的内部列创建视图，但替代版本可以创建和/或更改表以使其与更改保持同步。

推荐阅读