Untitled

YuvalGai

Jun 20th, 2023 (edited)

168

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

text 3.38 KB | None | 0 0

raw download clone embed print report

CREATE OR REPLACE PROCEDURE CANDIVORE.SEMANTIC_LAYER."SP_INCREMENTAL_Interval_Session_Inventory_Resource_Name"()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS OWNER
AS '
/*
This script merged the Interval_Session_Inventory_Resource_Name data from the client and the server
The steps are:
1. Get last insert date
2. Load the merged data into a temp table, using the following logic:
a. Take rows that have Insert_Date >= Max_Insert_Date we got in #1 to get the smallest delta
b. Merge the data between the two sources
c. Filter out any rows that already exist in target (due to the "=" in the dates)
3. Insert the content of the temp table into the traget table
4. Cleanup temp tables created by this script
*/
var my_sql_command0 = "BEGIN TRANSACTION;"
var statement0 = snowflake.createStatement({sqlText: my_sql_command0});
statement0.execute();
//1. Get last insert date for each source
var my_sql_command1 = `CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.TMP_UM_MAX_INSERT_DATE AS \\
SELECT MAX(Interval_Date_Time) as MAX_Interval_Date_Time \\
FROM CANDIVORE.SEMANTIC_LAYER.T_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME;`
var statement1 = snowflake.createStatement({sqlText: my_sql_command1});
statement1.execute();
//2. Get joined data that has been inserted since the last insert date in the merged table
var my_sql_command2 = `CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.TMP_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME AS(
SELECT
Interval_Date_Time
,Resource_Type_Id
,Resource_Type
,Resource_Sub_Type_Id
,Resource_Sub_Type
,Resource_Id
,Resource_Name_Id
,Resource_Display_Name
,Booster_Tier_Group_Id
,Booster_Tier_Group
,Resource_Status
,LTV_Group
,LTV_Group_30D
,payers_segment
,Seniority_Bin
,Arena_Group
,TROPHY_GROUP
,trophy_group_2
,COUNT(DISTINCT User_Id) as User_Count
,SUM(Start_Session_Resource_Count) as Start_Session_Resource_Count
FROM SNOWPLOW.DERIVED."v_INTERVAL_SESSION_INVENTORY" I
WHERE TO_DATE(Interval_Date_Time) >= DATEADD(''DAY'',-31,TO_DATE(GETDATE()))
and Interval_Date_Time > (SELECT MAX_Interval_Date_Time FROM TMP_UM_MAX_INSERT_DATE)
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)`
var statement2 = snowflake.createStatement({sqlText: my_sql_command2});
statement2.execute();
//3. Insert Delta into Target Table
var my_sql_command3 = "INSERT INTO CANDIVORE.SEMANTIC_LAYER.T_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME \\
SELECT * \\
FROM CANDIVORE.SEMANTIC_LAYER.TMP_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME;";
var statement3 = snowflake.createStatement({sqlText: my_sql_command3});
statement3.execute();
//4. Clean up tables
var my_sql_command4_1 = "DROP TABLE IF EXISTS CANDIVORE.SEMANTIC_LAYER.TMP_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME;";
var statement4_1 = snowflake.createStatement({sqlText: my_sql_command4_1});
statement4_1.execute();
var my_sql_command4_1 = "DROP TABLE IF EXISTS CANDIVORE.SEMANTIC_LAYER.TMP_UM_MAX_INSERT_DATE;";
var statement4_1 = snowflake.createStatement({sqlText: my_sql_command4_1});
statement4_1.execute();
var my_sql_command99 = "COMMIT;";
var statement99 = snowflake.createStatement({sqlText: my_sql_command99});
statement99.execute();
';

Add Comment

Please, Sign In to add comment