[Snowflake]: How to create table dynamically from JSON properties

user_132138 0
Viewed 102

I have activity events from PowerBI event logs and there are multiple types of event activities, and for each activity type the log contains different properties, e.g.

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

(in reality there is around 60 types each with around 20 properties). I want to be able to create as many tables as there is event types and to each table to contain the properties from JSON.

now I'd like to have a proc/sql that will create the table":

  • Activity_Report_View with all the props ReportID, WroskpaceID,
  • Activity_Dataset_Refresh with props DatasetId, IsSuccess

And this needs to be dynamic without me having to list out all the columns manually, something like this (which obviously is not working):

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;

Thank you

snowflake-cloud-data-platform
1 Answers

I don't have the full solution for you, but have you considered creating a stored procedure for this? I believe you could execute something like this.

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;

Which would give you a list of attributes and possible data types for those attributes. From this record set, you could dynamically build out a SQL query that has the correct CREATE TABLE statement, including the correct data types for those columns.

This will work if your JSON is a flat structure, but becomes more complicated if you have nested JSON structures. You can add a RECURSIVE => TRUE to the LATERAL FLATTEN statement to get all of your attributes, but the corresponding CTAS would be complex.

user_121789800
Related