API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jun 20th, 2023 (edited)
152
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.01 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE PROCEDURE CANDIVORE.SEMANTIC_LAYER."SP_INCREMENTAL_INTERVAL_Session_Inventory_Resource_Sub_Type_Amounts"()
  2. RETURNS VARCHAR(16777216)
  3. LANGUAGE JAVASCRIPT
  4. EXECUTE AS OWNER
  5. AS '
  6. /*
  7. This script merged the Interval_Session_Inventory_Resource_Name data from the client and the server
  8. The steps are:
  9. 1. Get last insert date
  10. 2. Load the merged data into a temp table, using the following logic:
  11. a. Take rows that have Insert_Date >= Max_Insert_Date we got in #1 to get the smallest delta
  12. b. Merge the data between the two sources
  13. c. Filter out any rows that already exist in target (due to the "=" in the dates)
  14. 3. Insert the content of the temp table into the traget table
  15. 4. Cleanup temp tables created by this script
  16. */
  17. var my_sql_command0 = "BEGIN TRANSACTION;"
  18. var statement0 = snowflake.createStatement({sqlText: my_sql_command0});
  19. statement0.execute();
  20.  
  21. //1. Get last insert date for each source
  22. var my_sql_command1 = `CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.TMP_UM_MAX_INSERT_DATE AS \\
  23. SELECT MAX(Interval_Date_Time) as MAX_Interval_Date_Time \\
  24. FROM CANDIVORE.SEMANTIC_LAYER.T_Interval_Session_Inventory_Resource_Sub_Type_Amounts;`
  25. var statement1 = snowflake.createStatement({sqlText: my_sql_command1});
  26. statement1.execute();
  27.  
  28. //2. Get joined data that has been inserted since the last insert date in the merged table
  29. var my_sql_command2 = `CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.TMP_T_Interval_Session_Inventory_Resource_Sub_Type_Amounts AS(
  30. SELECT
  31. Interval_Date_Time
  32. ,Resource_Type_Id
  33. ,Resource_Type
  34. ,Resource_Sub_Type_Id
  35. ,Resource_Sub_Type
  36. ,Booster_Tier_Group
  37. ,Booster_Tier_Group_Id
  38. ,RESOURCE_STATUS
  39. ,LTV_Group
  40. ,LTV_Group_30D
  41. ,Seniority_Bin
  42. ,Arena_Group
  43. ,payers_segment
  44. ,TROPHY_GROUP
  45. ,trophy_group_2
  46. ,count(case when (START_SESSION_RESOURCE_COUNT < 1) then user_id end) as ZeroBalance
  47. ,count(case when (START_SESSION_RESOURCE_COUNT >= 1) then user_id end) as over1
  48. ,count(case when (START_SESSION_RESOURCE_COUNT >= 2) then user_id end) as over2
  49. ,count(case when (START_SESSION_RESOURCE_COUNT >= 3) then user_id end) as over3
  50. ,count(case when (START_SESSION_RESOURCE_COUNT >= 5) then user_id end) as over5
  51. ,count(case when (START_SESSION_RESOURCE_COUNT >= 10) then user_id end) as over10
  52. ,count(case when (START_SESSION_RESOURCE_COUNT >= 20) then user_id end) as over20
  53. ,count(case when (START_SESSION_RESOURCE_COUNT >= 30) then user_id end) as over30
  54. ,count(case when (START_SESSION_RESOURCE_COUNT >= 40) then user_id end) as over40
  55. ,count(case when (START_SESSION_RESOURCE_COUNT >= 50) then user_id end) as over50
  56. ,count(case when (START_SESSION_RESOURCE_COUNT >= 100) then user_id end) as over100
  57. ,count(case when (START_SESSION_RESOURCE_COUNT >= 150) then user_id end) as over150
  58. ,count(case when (START_SESSION_RESOURCE_COUNT >= 250) then user_id end) as over250
  59. ,count(case when (START_SESSION_RESOURCE_COUNT >= 500) then user_id end) as over500
  60. ,count(case when (START_SESSION_RESOURCE_COUNT >= 1000) then user_id end) as over1000
  61. ,count(case when (START_SESSION_RESOURCE_COUNT >= 2500) then user_id end) as over2500
  62. ,count(case when (START_SESSION_RESOURCE_COUNT >= 10000) then user_id end) as over10000
  63. ,count(case when (START_SESSION_RESOURCE_COUNT >= 25000) then user_id end) as over25000
  64. ,count(case when (START_SESSION_RESOURCE_COUNT >= 50000) then user_id end) as over50000
  65. ,count(case when (START_SESSION_RESOURCE_COUNT >= 100000) then user_id end) as over100000
  66. FROM SNOWPLOW.DERIVED."v_INTERVAL_SESSION_INVENTORY_SUB_TYPE" I
  67. WHERE TO_DATE(Interval_Date_Time) >= DATEADD(''DAY'',-31,TO_DATE(GETDATE()))
  68. and Interval_Date_Time > (SELECT MAX_Interval_Date_Time FROM TMP_UM_MAX_INSERT_DATE)
  69. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)`
  70.  
  71.  
  72. var statement2 = snowflake.createStatement({sqlText: my_sql_command2});
  73. statement2.execute();
  74.  
  75.  
  76.  
  77. //3. Insert Delta into Target Table
  78. var my_sql_command3 = "INSERT INTO CANDIVORE.SEMANTIC_LAYER.T_Interval_Session_Inventory_Resource_Sub_Type_Amounts \\
  79. SELECT * \\
  80. FROM CANDIVORE.SEMANTIC_LAYER.TMP_T_Interval_Session_Inventory_Resource_Sub_Type_Amounts;";
  81. var statement3 = snowflake.createStatement({sqlText: my_sql_command3});
  82. statement3.execute();
  83.  
  84. //4. Clean up tables
  85. var my_sql_command4_1 = "DROP TABLE IF EXISTS CANDIVORE.SEMANTIC_LAYER.TMP_T_Interval_Session_Inventory_Resource_Sub_Type_Amounts;";
  86. var statement4_1 = snowflake.createStatement({sqlText: my_sql_command4_1});
  87. statement4_1.execute();
  88.  
  89. var my_sql_command4_1 = "DROP TABLE IF EXISTS CANDIVORE.SEMANTIC_LAYER.TMP_UM_MAX_INSERT_DATE;";
  90. var statement4_1 = snowflake.createStatement({sqlText: my_sql_command4_1});
  91. statement4_1.execute();
  92.  
  93. var my_sql_command99 = "COMMIT;";
  94. var statement99 = snowflake.createStatement({sqlText: my_sql_command99});
  95. statement99.execute();
  96. ';
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!