Read a flatten Json file without labels - taking the first array
26/02/2023 11:13
with JsonBase
with JsonBase
as
(
select parse_json(response) as src,
INGEST_PIPELINETITLE,
INGEST_PIPELINEVERSION,
INGEST_TIMESTAMP,
INGEST_SOURCE
from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
),FullColumn
as
(
select
S.src:tables[0].rows[0][0]::varchar as "timestamp",
S.src:tables[0].rows[0][1]::varchar as "name",
S.src:tables[0].rows[0][2]::varchar as "itemType",
S.src:tables[0].rows[0][3]::varchar as "customDimensions",
S.src:tables[0].rows[0][4]::varchar as "customMeasurements",
S.src:tables[0].rows[0][5]::varchar as "operation_Name",
S.src:tables[0].rows[0][6]::varchar as "operation_Id",
S.src:tables[0].rows[0][7]::varchar as "operation_ParentId",
S.src:tables[0].rows[0][8]::varchar as "operation_SyntheticSource",
S.src:tables[0].rows[0][9]::varchar as "session_Id",
S.src:tables[0].rows[0][10]::varchar as "user_Id",
S.src:tables[0].rows[0][11]::varchar as "user_AuthenticatedId",
S.src:tables[0].rows[0][12]::varchar as "user_AccountId",
S.src:tables[0].rows[0][13]::varchar as "application_Version",
S.src:tables[0].rows[0][14]::varchar as "client_Type",
S.src:tables[0].rows[0][15]::varchar as "client_Model",
S.src:tables[0].rows[0][16]::varchar as "client_OS",
S.src:tables[0].rows[0][17]::varchar as "client_IP",
S.src:tables[0].rows[0][18]::varchar as "client_City",
S.src:tables[0].rows[0][19]::varchar as "client_StateOrProvince",
S.src:tables[0].rows[0][20]::varchar as "client_CountryOrRegion",
S.src:tables[0].rows[0][21]::varchar as "client_Browser",
S.src:tables[0].rows[0][22]::varchar as "cloud_RoleName",
S.src:tables[0].rows[0][23]::varchar as "cloud_RoleInstance",
S.src:tables[0].rows[0][24]::varchar as "appId",
S.src:tables[0].rows[0][25]::varchar as "appName",
S.src:tables[0].rows[0][26]::varchar as "iKey",
S.src:tables[0].rows[0][27]::varchar as "sdkVersion",
S.src:tables[0].rows[0][28]::varchar as "itemId",
S.src:tables[0].rows[0][29]::varchar as "itemCount",
S.src:tables[0].rows[0][30]::varchar as "_ResourceId",
// {{ willow_to_utc_timestamp('S.src:tables[0].rows[0][0]::varchar') }} AS "UPDATED_DATE",
INGEST_PIPELINETITLE,
INGEST_PIPELINEVERSION,
INGEST_TIMESTAMP,
INGEST_SOURCE,
S.src:tables[0].rows[0]::varchar as "JSON_RAW"
//src:Tables:Rows[0]
from JsonBase s
),FullRecord
as
(
select
// S.src:root[0].tables[0].rows[0] as T1,
S.src:tables[0].rows[0] as T2
//src:Tables:Rows[0]
from JsonBase s
),DuplicationCheck
as
(
select
"iKey",
"operation_Id"
from FullColumn s
group by "iKey","operation_Id"
having count(*)>1
)
Select * from FullRecord
//where "iKey" = "b6fb36ad-f7a1-4f26-83ac-3b77540f3a05";
//Select "iKey" from DuplicationCheck;
/*
,DuplicationCheck
as
(
select
iKey
from FullColumn s
group by iKey
having count(iKey)>1
)
select
RESPONSE::VARCHAR,
RESPONSE:tables[0]::VARCHAR,
RESPONSE:tables[0].columns[0]::VARCHAR,
RESPONSE:tables[0].rows::VARCHAR,
RESPONSE:tables[0].rows[0]::VARCHAR
from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
;
*/
/*
Select Max(S.src:tables[0].rows[0][0]) as T2 From ( select parse_json(response) as MODIFIED_AFTER from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS" ) S
Select * from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
*/
"STAGING"."RAW_SSUD_WILLOW_APPINSIGHTS_CONSUME"."CUSTOMEVENTS_MASTER_MATERIALIZED_VIEW"
"SANDBOX"."RAW_SSUD_WILLOW_APPINSIGHTS_CONSUME"."CUSTOMEVENTS_MASTER_MATERIALIZED_VIEW"
select * from MANAGED.LOGGING.LOG_SNOWFLAKE_ACC_USAGE_GRANTS_TO_ROLES
where TABLE_CATALOG = 'SANDBOX'
AND TABLE_SCHEMA = 'RAW_SSUD_WILLOW_APPINSIGHTS_CONSUME'
AND GRANTED_ON = 'MATERIALIZED VIEW'
Select * from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS" order by ingest_timestamp desc
SELECT MAX(S.$1:tables[0].rows[1500][0]) AS MODIFIED_AFTER FROM (SELECT PARSE_JSON(response) FROM CUSTOMEVENTS) S HAVING COUNT(*) > 0
SELECT (S.src:tables[0].rows[][0]) AS MODIFIED_AFTER FROM (SELECT PARSE_JSON(response) AS src FROM CUSTOMEVENTS) S
SELECT MAX(MODIFIED_AFTER) AS mAX_MODIFIED_AFTER FROM (SELECT (S.$1:tables[0].rows[0][0]) AS MODIFIED_AFTER FROM (SELECT PARSE_JSON(response) FROM CUSTOMEVENTS) S) X
with JsonBase
as
(
select parse_json(response) as src,
INGEST_PIPELINETITLE,
INGEST_PIPELINEVERSION,
INGEST_TIMESTAMP,
INGEST_SOURCE
from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
),FullColumn
as
(
select
S.src:tables[0].rows[0][0]::varchar as "timestamp",
S.src:tables[0].rows[0][1]::varchar as "name",
S.src:tables[0].rows[0][2]::varchar as "itemType",
S.src:tables[0].rows[0][3]::varchar as "customDimensions",
S.src:tables[0].rows[0][4]::varchar as "customMeasurements",
S.src:tables[0].rows[0][5]::varchar as "operation_Name",
S.src:tables[0].rows[0][6]::varchar as "operation_Id",
S.src:tables[0].rows[0][7]::varchar as "operation_ParentId",
S.src:tables[0].rows[0][8]::varchar as "operation_SyntheticSource",
S.src:tables[0].rows[0][9]::varchar as "session_Id",
S.src:tables[0].rows[0][10]::varchar as "user_Id",
S.src:tables[0].rows[0][11]::varchar as "user_AuthenticatedId",
S.src:tables[0].rows[0][12]::varchar as "user_AccountId",
S.src:tables[0].rows[0][13]::varchar as "application_Version",
S.src:tables[0].rows[0][14]::varchar as "client_Type",
S.src:tables[0].rows[0][15]::varchar as "client_Model",
S.src:tables[0].rows[0][16]::varchar as "client_OS",
S.src:tables[0].rows[0][17]::varchar as "client_IP",
S.src:tables[0].rows[0][18]::varchar as "client_City",
S.src:tables[0].rows[0][19]::varchar as "client_StateOrProvince",
S.src:tables[0].rows[0][20]::varchar as "client_CountryOrRegion",
S.src:tables[0].rows[0][21]::varchar as "client_Browser",
S.src:tables[0].rows[0][22]::varchar as "cloud_RoleName",
S.src:tables[0].rows[0][23]::varchar as "cloud_RoleInstance",
S.src:tables[0].rows[0][24]::varchar as "appId",
S.src:tables[0].rows[0][25]::varchar as "appName",
S.src:tables[0].rows[0][26]::varchar as "iKey",
S.src:tables[0].rows[0][27]::varchar as "sdkVersion",
S.src:tables[0].rows[0][28]::varchar as "itemId",
S.src:tables[0].rows[0][29]::varchar as "itemCount",
S.src:tables[0].rows[0][30]::varchar as "_ResourceId",
// {{ willow_to_utc_timestamp('S.src:tables[0].rows[0][0]::varchar') }} AS "UPDATED_DATE",
INGEST_PIPELINETITLE,
INGEST_PIPELINEVERSION,
INGEST_TIMESTAMP,
INGEST_SOURCE,
S.src:tables[0].rows[0]::varchar as "JSON_RAW"
//src:Tables:Rows[0]
from JsonBase s
),FullRecord
as
(
select
// S.src:root[0].tables[0].rows[0] as T1,
S.src:tables[0].rows[0] as T2
//src:Tables:Rows[0]
from JsonBase s
),DuplicationCheck
as
(
select
"iKey",
"operation_Id"
from FullColumn s
group by "iKey","operation_Id"
having count(*)>1
)
Select * from FullRecord
//where "iKey" = "b6fb36ad-f7a1-4f26-83ac-3b77540f3a05";
//Select "iKey" from DuplicationCheck;
/*
,DuplicationCheck
as
(
select
iKey
from FullColumn s
group by iKey
having count(iKey)>1
)
select
RESPONSE::VARCHAR,
RESPONSE:tables[0]::VARCHAR,
RESPONSE:tables[0].columns[0]::VARCHAR,
RESPONSE:tables[0].rows::VARCHAR,
RESPONSE:tables[0].rows[0]::VARCHAR
from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
;
*/
/*
Select Max(S.src:tables[0].rows[0][0]) as T2 From ( select parse_json(response) as MODIFIED_AFTER from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS" ) S
Select * from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
*/
"STAGING"."RAW_SSUD_WILLOW_APPINSIGHTS_CONSUME"."CUSTOMEVENTS_MASTER_MATERIALIZED_VIEW"
"SANDBOX"."RAW_SSUD_WILLOW_APPINSIGHTS_CONSUME"."CUSTOMEVENTS_MASTER_MATERIALIZED_VIEW"
select * from MANAGED.LOGGING.LOG_SNOWFLAKE_ACC_USAGE_GRANTS_TO_ROLES
where TABLE_CATALOG = 'SANDBOX'
AND TABLE_SCHEMA = 'RAW_SSUD_WILLOW_APPINSIGHTS_CONSUME'
AND GRANTED_ON = 'MATERIALIZED VIEW'
Select * from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS" order by ingest_timestamp desc
SELECT MAX(S.$1:tables[0].rows[1500][0]) AS MODIFIED_AFTER FROM (SELECT PARSE_JSON(response) FROM CUSTOMEVENTS) S HAVING COUNT(*) > 0
SELECT (S.src:tables[0].rows[][0]) AS MODIFIED_AFTER FROM (SELECT PARSE_JSON(response) AS src FROM CUSTOMEVENTS) S
SELECT MAX(MODIFIED_AFTER) AS mAX_MODIFIED_AFTER FROM (SELECT (S.$1:tables[0].rows[0][0]) AS MODIFIED_AFTER FROM (SELECT PARSE_JSON(response) FROM CUSTOMEVENTS) S) X
with JsonBase
as
(
select parse_json(response) as src,
INGEST_PIPELINETITLE,
INGEST_PIPELINEVERSION,
INGEST_TIMESTAMP,
INGEST_SOURCE
from "SANDBOX"."RAW_WILLOW_APPINSIGHTS"."CUSTOMEVENTS"
),FullColumn
as
(
select
S.src:tables[0].rows[0][0]::varchar as "timestamp",
S.src:tables[0].rows[0][1]::varchar as "name",
S.src:tables[0].rows[0][2]::varchar as "itemType",
S.src:tables[0].rows[0][3]::varchar as "customDimensions",
S.src:tables[0].rows[0][4]::varchar as "customMeasurements",
S.src:tables[0].rows[0][5]::varchar as "operation_Name",
S.src:tables[0].rows[0][6]::varchar as "operation_Id",
S.src:tables[0].rows[0][7]::varchar as "operation_ParentId",
S.src:tables[0].rows[0][8]::varchar as "operation_SyntheticSource",
S.src:tables[0].rows[0][9]::varchar as "session_Id",
S.src:tables[0].rows[0][10]::varchar as "user_Id",
S.src:tables[0].rows[0][11]::varchar as "user_AuthenticatedId",
S.src:tables[0].rows[0][12]::varchar as "user_AccountId",
S.src:tables[0].rows[0][13]::varchar as "application_Version",
S.src:tables[0].rows[0][14]::varchar as "client_Type",
S.src:tables[0].rows[0][15]::varchar as "client_Model",
S.src:tables[0].rows[0][16]::varchar as "client_OS",
S.src:tables[0].rows[0][17]::varchar as "client_IP",
S.src:tables[0].rows[0][18]::varchar as "client_City",
S.src:tables[0].rows[0][19]::varchar as "client_StateOrProvince",
S.src:tables[0].rows[0][20]::varchar as "client_CountryOrRegion",
S.src:tables[0].rows[0][21]::varchar as "client_Browser",
S.src:tables[0].rows[0][22]::varchar as "cloud_RoleName",
S.src:tables[0].rows[0][23]::varchar as "cloud_RoleInstance",
S.src:tables[0].rows[0][24]::varchar as "appId",
S.src:tables[0].rows[0][25]::varchar as "appName",
S.src:tables[0].rows[0][26]::varchar as "iKey",
S.src:tables[0].rows[0][27]::varchar as "sdkVersion",
S.src:tables[0].rows[0][28]::varchar as "itemId",
S.src:tables[0].rows[0][29]::varchar as "itemCount",
S.src:tables[0].rows[0][30]::varchar as "_ResourceId",
// {{ willow_to_utc_timestamp('S.src:tables[0].rows[0][0]::varchar') }} AS "UPDATED_DATE",
INGEST_PIPELINETITLE,
INGEST_PIPELINEVERSION,
INGEST_TIMESTAMP,
INGEST_SOURCE,
S.src:tables[0].rows[0]::varchar as "JSON_RAW"
//src:Tables:Rows[0]
from JsonBase s
),FullRecord
as
(
select
// S.src:root[0].tables[0].rows[0] as T1,
S.src:tables[0].rows[0] as T2
//src:Tables:Rows[0]
from JsonBase s
),DuplicationCheck
as
(
select
"iKey",
"operation_Id"
from FullColumn s
group by "iKey","operation_Id"
having count(*)>1
)
Select * from FullRecord