[Snowflake]:如何从JSON属性动态创建表

Question

我有来自PowerBI事件日志的活动事件，并且有多种类型的事件活动，对于每种活动类型的日志包含不同的属性，例如

[{ "Activity": "ViewReport", "ReportID": "aaa-bbb-ccc", "WorkspaceID": "eee-fff-ddd"},
{ "Activity": "DatasetRefresh", "DatasetID": "...", "IsSuccess": true}]

(实际上大约有60个类型，每个类型大约有20个属性)。我希望能够创建尽可能多的表，因为有事件类型和每个表包含JSON的属性。

现在我想有一个proc/sql将创建表";

• Activity_Report_View与所有道具ReportID,WroskpaceID，
• Activity_Dataset_Refresh与道具DatasetId,IsSuccess

这需要是动态的，而不需要我手动列出所有列，像这样(这显然是不工作):

CREATE OR REPLACE TABLE ACTIVITY_REPORT_VIEW AS 
SELECT
d.JSON_DATA:*
FROM JSON_TEMP d
WHERE d.JSON_DATA:Activity = 'ViewReport'
ORDER BY d.JSON_DATA:CreationTime DESC;

谢谢

Answer 1

我没有完整的解决方案，但是您考虑过为此创建一个存储过程吗?我相信你可以这样做。

SELECT KEY AS COLUMN_NAME
, LISTAGG(DISTINCT(TYPEOF(VALUE)),', ') AS POSSIBLE_DATA_TYPES
FROM
JSON_TEMP a
, LATERAL FLATTEN (INPUT => var) b
GROUP BY 1;

它会给你一个属性列表和这些属性可能的数据类型。从这个记录集，您可以动态地构建一个具有正确的CREATE TABLE语句的SQL查询，包括这些列的正确数据类型。

如果你的JSON是一个平面结构，这将工作，但如果你有嵌套JSON结构变得更复杂。您可以将RECURSIVE => TRUE添加到LATERAL FLATTEN语句中以获得所有属性，但是相应的cta将是复杂的。