Reading a Json file with no lables - multiple columns

25/02/2023 11:17
with src 
as 
( 
                select 
                    try_parse_json(response) as response 
                from "RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS" 
 
)
,flatten_cols 
as 
( 
                select distinct 
                    c.index as col_index, 
                    c.value:name::string as col_name, 
                    c.value:type::string as col_type 
                from    src, 
                        lateral flatten (input => src.response:tables) t, 
                        lateral flatten (input => src.response:tables[t.index]:columns) c 
                order by 1 
) 
,flatten_rows 
as 
( 
                select 
                    v.seq as row_index, 
                    v.index as col_index, 
                    v.value::string as col_value,
  v.*
                from    src, 
                        lateral flatten (input => src.response:tables) t, 
                        lateral flatten (input => src.response:tables[t.index]:rows) r, 
                        lateral flatten (input => src.response:tables[t.index]:rows[r.index]) v 
) 
,col_row_join 
as 
( 
                select 
                    r.row_index, 
                    c.col_name, 
                    r.col_value::string as col_value 
                from flatten_cols c 
                join flatten_rows r on c.col_index = r.col_index 
) 
 
 
//select * from flatten_cols; 
//select * from col_row_join; 
//select * from flatten_rows;
 
select 
    timestamp.COL_VALUE                      as "timestamp",
    name.COL_VALUE                           as "name",
    itemType.COL_VALUE                       as "itemType",
    customDimensions.COL_VALUE               as "customDimensions",
    customMeasurements.COL_VALUE             as "customMeasurements",
    operation_Name.COL_VALUE                 as "operation_Name",
    operation_Id.COL_VALUE                   as "operation_Id",
    operation_ParentId.COL_VALUE             as "operation_ParentId",
    operation_SyntheticSource.COL_VALUE      as "operation_SyntheticSource",
    session_Id.COL_VALUE                     as "session_Id",
    user_Id.COL_VALUE                        as "user_Id",
    user_AuthenticatedId.COL_VALUE           as "user_AuthenticatedId",
    user_AccountId.COL_VALUE                 as "user_AccountId",
    application_Version.COL_VALUE            as "application_Version",
    client_Type.COL_VALUE                    as "client_Type",
    client_Model.COL_VALUE                   as "client_Model",
    client_OS.COL_VALUE                      as "client_OS",
    client_IP.COL_VALUE                      as "client_IP",
    client_City.COL_VALUE                    as "client_City",
    client_StateOrProvince.COL_VALUE         as "client_StateOrProvince",
    client_CountryOrRegion.COL_VALUE         as "client_CountryOrRegion",
    client_Browser.COL_VALUE                 as "client_Browser",
    cloud_RoleName.COL_VALUE                 as "cloud_RoleName",
    cloud_RoleInstance.COL_VALUE             as "cloud_RoleInstance",
    appId.COL_VALUE                          as "appId",
    appName.COL_VALUE                        as "appName",
    iKey.COL_VALUE                           as "iKey",
    sdkVersion.COL_VALUE                     as "sdkVersion",
    itemId.COL_VALUE                         as "itemId",
    itemCount.COL_VALUE                      as "itemCount",
    _ResourceId.COL_VALUE                    as "_ResourceId",
    timestamp.COL_VALUE                      as "Updated_Date"
from col_row_join as timestamp 
Inner Join col_row_join as name                         on name.ROW_INDEX = timestamp.ROW_INDEX                         and name.COL_NAME = 'name'
Inner Join col_row_join as itemType                     on itemType.ROW_INDEX = timestamp.ROW_INDEX                     and itemType.COL_NAME = 'itemType'
Inner Join col_row_join as customDimensions             on customDimensions.ROW_INDEX = timestamp.ROW_INDEX             and customDimensions.COL_NAME = 'customDimensions'
Inner Join col_row_join as customMeasurements           on customMeasurements.ROW_INDEX = timestamp.ROW_INDEX           and customMeasurements.COL_NAME = 'customMeasurements'
Inner Join col_row_join as operation_Name               on operation_Name.ROW_INDEX = timestamp.ROW_INDEX               and operation_Name.COL_NAME = 'operation_Name'
Inner Join col_row_join as operation_Id                 on operation_Id.ROW_INDEX = timestamp.ROW_INDEX                 and operation_Id.COL_NAME = 'operation_Id'
Inner Join col_row_join as operation_ParentId           on operation_ParentId.ROW_INDEX = timestamp.ROW_INDEX           and operation_ParentId.COL_NAME = 'operation_ParentId'
Inner Join col_row_join as operation_SyntheticSource    on operation_SyntheticSource.ROW_INDEX = timestamp.ROW_INDEX    and operation_SyntheticSource.COL_NAME = 'operation_SyntheticSource'
Inner Join col_row_join as session_Id                   on session_Id.ROW_INDEX = timestamp.ROW_INDEX                   and session_Id.COL_NAME = 'session_Id'
Inner Join col_row_join as user_Id                      on user_Id.ROW_INDEX = timestamp.ROW_INDEX                      and user_Id.COL_NAME = 'user_Id'
Inner Join col_row_join as user_AuthenticatedId         on user_AuthenticatedId.ROW_INDEX = timestamp.ROW_INDEX         and user_AuthenticatedId.COL_NAME = 'user_AuthenticatedId'
Inner Join col_row_join as user_AccountId               on user_AccountId.ROW_INDEX = timestamp.ROW_INDEX               and user_AccountId.COL_NAME = 'user_AccountId'
Inner Join col_row_join as application_Version          on application_Version.ROW_INDEX = timestamp.ROW_INDEX          and application_Version.COL_NAME = 'application_Version'
Inner Join col_row_join as client_Type                  on client_Type.ROW_INDEX = timestamp.ROW_INDEX                  and client_Type.COL_NAME = 'client_Type'
Inner Join col_row_join as client_Model                 on client_Model.ROW_INDEX = timestamp.ROW_INDEX                 and client_Model.COL_NAME = 'client_Model'
Inner Join col_row_join as client_OS                    on client_OS.ROW_INDEX = timestamp.ROW_INDEX                    and client_OS.COL_NAME = 'client_OS'
Inner Join col_row_join as client_IP                    on client_IP.ROW_INDEX = timestamp.ROW_INDEX                    and client_IP.COL_NAME = 'client_IP'
Inner Join col_row_join as client_City                  on client_City.ROW_INDEX = timestamp.ROW_INDEX                  and client_City.COL_NAME = 'client_City'
Inner Join col_row_join as client_StateOrProvince       on client_StateOrProvince.ROW_INDEX = timestamp.ROW_INDEX       and client_StateOrProvince.COL_NAME = 'client_StateOrProvince'
Inner Join col_row_join as client_CountryOrRegion       on client_CountryOrRegion.ROW_INDEX = timestamp.ROW_INDEX       and client_CountryOrRegion.COL_NAME = 'client_CountryOrRegion'
Inner Join col_row_join as client_Browser               on client_Browser.ROW_INDEX = timestamp.ROW_INDEX               and client_Browser.COL_NAME = 'client_Browser'
Inner Join col_row_join as cloud_RoleName               on cloud_RoleName.ROW_INDEX = timestamp.ROW_INDEX               and cloud_RoleName.COL_NAME = 'cloud_RoleName'
Inner Join col_row_join as cloud_RoleInstance           on cloud_RoleInstance.ROW_INDEX = timestamp.ROW_INDEX           and cloud_RoleInstance.COL_NAME = 'cloud_RoleInstance'
Inner Join col_row_join as appId                        on appId.ROW_INDEX = timestamp.ROW_INDEX                        and appId.COL_NAME = 'appId'
Inner Join col_row_join as appName                      on appName.ROW_INDEX = timestamp.ROW_INDEX                      and appName.COL_NAME = 'appName'
Inner Join col_row_join as iKey                         on iKey.ROW_INDEX = timestamp.ROW_INDEX                         and iKey.COL_NAME = 'iKey'
Inner Join col_row_join as sdkVersion                   on sdkVersion.ROW_INDEX = timestamp.ROW_INDEX                   and sdkVersion.COL_NAME = 'sdkVersion'
Inner Join col_row_join as itemId                       on itemId.ROW_INDEX = timestamp.ROW_INDEX                       and itemId.COL_NAME = 'itemId'
Inner Join col_row_join as itemCount                    on itemCount.ROW_INDEX = timestamp.ROW_INDEX                    and itemCount.COL_NAME = 'itemCount'
Inner Join col_row_join as _ResourceId                  on _ResourceId.ROW_INDEX = timestamp.ROW_INDEX                  and _ResourceId.COL_NAME = '_ResourceId'
Where timestamp.COL_NAME = 'timestamp'
 
; 
 
 
 
 
 
 
with src 
as 
( 
                select 
                    try_parse_json(response) as response 
                from "RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS" 
 
)
,flatten_cols 
as 
( 
                select distinct 
                    c.index as col_index, 
                    c.value:name::string as col_name, 
                    c.value:type::string as col_type 
                from    src, 
                        lateral flatten (input => src.response:tables) t, 
                        lateral flatten (input => src.response:tables[t.index]:columns) c 
                order by 1 
) 
,flatten_rows 
as 
( 
                select 
                    v.seq as row_index, 
                    v.index as col_index, 
                    v.value::string as col_value,
  v.*
                from    src, 
                        lateral flatten (input => src.response:tables) t, 
                        lateral flatten (input => src.response:tables[t.index]:rows) r, 
                        lateral flatten (input => src.response:tables[t.index]:rows[r.index]) v 
) 
,col_row_join 
as 
( 
                select 
                    r.row_index, 
                    c.col_name, 
                    r.col_value::string as col_value 
                from flatten_cols c 
                join flatten_rows r on c.col_index = r.col_index 
) 
 
 
//select * from flatten_cols; 
//select * from col_row_join; 
//select * from flatten_rows;
 
select 
    timestamp.COL_VALUE                      as "timestamp",
    name.COL_VALUE                           as "name",
    itemType.COL_VALUE                       as "itemType",
    customDimensions.COL_VALUE               as "customDimensions",
    customMeasurements.COL_VALUE             as "customMeasurements",
    operation_Name.COL_VALUE                 as "operation_Name",
    operation_Id.COL_VALUE                   as "operation_Id",
    operation_ParentId.COL_VALUE             as "operation_ParentId",
    operation_SyntheticSource.COL_VALUE      as "operation_SyntheticSource",
    session_Id.COL_VALUE                     as "session_Id",
    user_Id.COL_VALUE                        as "user_Id",
    user_AuthenticatedId.COL_VALUE           as "user_AuthenticatedId",
    user_AccountId.COL_VALUE                 as "user_AccountId",
    application_Version.COL_VALUE            as "application_Version",
    client_Type.COL_VALUE                    as "client_Type",
    client_Model.COL_VALUE                   as "client_Model",
    client_OS.COL_VALUE                      as "client_OS",
    client_IP.COL_VALUE                      as "client_IP",
    client_City.COL_VALUE                    as "client_City",
    client_StateOrProvince.COL_VALUE         as "client_StateOrProvince",
    client_CountryOrRegion.COL_VALUE         as "client_CountryOrRegion",
    client_Browser.COL_VALUE                 as "client_Browser",
    cloud_RoleName.COL_VALUE                 as "cloud_RoleName",
    cloud_RoleInstance.COL_VALUE             as "cloud_RoleInstance",
    appId.COL_VALUE                          as "appId",
    appName.COL_VALUE                        as "appName",
    iKey.COL_VALUE                           as "iKey",
    sdkVersion.COL_VALUE                     as "sdkVersion",
    itemId.COL_VALUE                         as "itemId",
    itemCount.COL_VALUE                      as "itemCount",
    _ResourceId.COL_VALUE                    as "_ResourceId",
    timestamp.COL_VALUE                      as "Updated_Date"
from col_row_join as timestamp 
Inner Join col_row_join as name                         on name.ROW_INDEX = timestamp.ROW_INDEX                         and name.COL_NAME = 'name'
Inner Join col_row_join as itemType                     on itemType.ROW_INDEX = timestamp.ROW_INDEX                     and itemType.COL_NAME = 'itemType'
Inner Join col_row_join as customDimensions             on customDimensions.ROW_INDEX = timestamp.ROW_INDEX             and customDimensions.COL_NAME = 'customDimensions'
Inner Join col_row_join as customMeasurements           on customMeasurements.ROW_INDEX = timestamp.ROW_INDEX           and customMeasurements.COL_NAME = 'customMeasurements'
Inner Join col_row_join as operation_Name               on operation_Name.ROW_INDEX = timestamp.ROW_INDEX               and operation_Name.COL_NAME = 'operation_Name'
Inner Join col_row_join as operation_Id                 on operation_Id.ROW_INDEX = timestamp.ROW_INDEX                 and operation_Id.COL_NAME = 'operation_Id'
Inner Join col_row_join as operation_ParentId           on operation_ParentId.ROW_INDEX = timestamp.ROW_INDEX           and operation_ParentId.COL_NAME = 'operation_ParentId'
Inner Join col_row_join as operation_SyntheticSource    on operation_SyntheticSource.ROW_INDEX = timestamp.ROW_INDEX    and operation_SyntheticSource.COL_NAME = 'operation_SyntheticSource'
Inner Join col_row_join as session_Id                   on session_Id.ROW_INDEX = timestamp.ROW_INDEX                   and session_Id.COL_NAME = 'session_Id'
Inner Join col_row_join as user_Id                      on user_Id.ROW_INDEX = timestamp.ROW_INDEX                      and user_Id.COL_NAME = 'user_Id'
Inner Join col_row_join as user_AuthenticatedId         on user_AuthenticatedId.ROW_INDEX = timestamp.ROW_INDEX         and user_AuthenticatedId.COL_NAME = 'user_AuthenticatedId'
Inner Join col_row_join as user_AccountId               on user_AccountId.ROW_INDEX = timestamp.ROW_INDEX               and user_AccountId.COL_NAME = 'user_AccountId'
Inner Join col_row_join as application_Version          on application_Version.ROW_INDEX = timestamp.ROW_INDEX          and application_Version.COL_NAME = 'application_Version'
Inner Join col_row_join as client_Type                  on client_Type.ROW_INDEX = timestamp.ROW_INDEX                  and client_Type.COL_NAME = 'client_Type'
Inner Join col_row_join as client_Model                 on client_Model.ROW_INDEX = timestamp.ROW_INDEX                 and client_Model.COL_NAME = 'client_Model'
Inner Join col_row_join as client_OS                    on client_OS.ROW_INDEX = timestamp.ROW_INDEX                    and client_OS.COL_NAME = 'client_OS'
Inner Join col_row_join as client_IP                    on client_IP.ROW_INDEX = timestamp.ROW_INDEX                    and client_IP.COL_NAME = 'client_IP'
Inner Join col_row_join as client_City                  on client_City.ROW_INDEX = timestamp.ROW_INDEX                  and client_City.COL_NAME = 'client_City'
Inner Join col_row_join as client_StateOrProvince       on client_StateOrProvince.ROW_INDEX = timestamp.ROW_INDEX       and client_StateOrProvince.COL_NAME = 'client_StateOrProvince'
Inner Join col_row_join as client_CountryOrRegion       on client_CountryOrRegion.ROW_INDEX = timestamp.ROW_INDEX       and client_CountryOrRegion.COL_NAME = 'client_CountryOrRegion'
Inner Join col_row_join as client_Browser               on client_Browser.ROW_INDEX = timestamp.ROW_INDEX               and client_Browser.COL_NAME = 'client_Browser'
Inner Join col_row_join as cloud_RoleName               on cloud_RoleName.ROW_INDEX = timestamp.ROW_INDEX               and cloud_RoleName.COL_NAME = 'cloud_RoleName'
Inner Join col_row_join as cloud_RoleInstance           on cloud_RoleInstance.ROW_INDEX = timestamp.ROW_INDEX           and cloud_RoleInstance.COL_NAME = 'cloud_RoleInstance'
Inner Join col_row_join as appId                        on appId.ROW_INDEX = timestamp.ROW_INDEX                        and appId.COL_NAME = 'appId'
Inner Join col_row_join as appName                      on appName.ROW_INDEX = timestamp.ROW_INDEX                      and appName.COL_NAME = 'appName'
Inner Join col_row_join as iKey                         on iKey.ROW_INDEX = timestamp.ROW_INDEX                         and iKey.COL_NAME = 'iKey'
Inner Join col_row_join as sdkVersion                   on sdkVersion.ROW_INDEX = timestamp.ROW_INDEX                   and sdkVersion.COL_NAME = 'sdkVersion'
Inner Join col_row_join as itemId                       on itemId.ROW_INDEX = timestamp.ROW_INDEX                       and itemId.COL_NAME = 'itemId'
Inner Join col_row_join as itemCount                    on itemCount.ROW_INDEX = timestamp.ROW_INDEX                    and itemCount.COL_NAME = 'itemCount'
Inner Join col_row_join as _ResourceId                  on _ResourceId.ROW_INDEX = timestamp.ROW_INDEX                  and _ResourceId.COL_NAME = '_ResourceId'
Where timestamp.COL_NAME = 'timestamp'
 
; 
 
 
 
 
 
 

Etiquetas:

Voltar