API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jun 20th, 2023 (edited)
190
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 5.05 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE PROCEDURE CANDIVORE.SEMANTIC_LAYER.SP_INCREMENTAL_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME_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_NAME_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_NAME_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. ,Resource_Id
  37. ,Resource_Name_Id
  38. ,Resource_Display_Name
  39. ,Booster_Tier_Group_Id
  40. ,Booster_Tier_Group
  41. ,Resource_Status
  42. ,LTV_Group
  43. ,LTV_Group_30D
  44. ,payers_segment
  45. ,Seniority_Bin
  46. ,Arena_Group
  47. ,trophy_group
  48. ,trophy_group_2
  49. ,count(case when (START_SESSION_RESOURCE_COUNT < 1) then user_id end) as ZeroBalance
  50. ,count(case when (START_SESSION_RESOURCE_COUNT >= 1) then user_id end) as over1
  51. ,count(case when (START_SESSION_RESOURCE_COUNT >= 2) then user_id end) as over2
  52. ,count(case when (START_SESSION_RESOURCE_COUNT >= 3) then user_id end) as over3
  53. ,count(case when (START_SESSION_RESOURCE_COUNT >= 5) then user_id end) as over5
  54. ,count(case when (START_SESSION_RESOURCE_COUNT >= 10) then user_id end) as over10
  55. ,count(case when (START_SESSION_RESOURCE_COUNT >= 20) then user_id end) as over20
  56. ,count(case when (START_SESSION_RESOURCE_COUNT >= 30) then user_id end) as over30
  57. ,count(case when (START_SESSION_RESOURCE_COUNT >= 40) then user_id end) as over40
  58. ,count(case when (START_SESSION_RESOURCE_COUNT >= 50) then user_id end) as over50
  59. ,count(case when (START_SESSION_RESOURCE_COUNT >= 100) then user_id end) as over100
  60. ,count(case when (START_SESSION_RESOURCE_COUNT >= 150) then user_id end) as over150
  61. ,count(case when (START_SESSION_RESOURCE_COUNT >= 250) then user_id end) as over250
  62. ,count(case when (START_SESSION_RESOURCE_COUNT >= 500) then user_id end) as over500
  63. ,count(case when (START_SESSION_RESOURCE_COUNT >= 1000) then user_id end) as over1000
  64. ,count(case when (START_SESSION_RESOURCE_COUNT >= 2500) then user_id end) as over2500
  65. ,count(case when (START_SESSION_RESOURCE_COUNT >= 10000) then user_id end) as over10000
  66. ,count(case when (START_SESSION_RESOURCE_COUNT >= 25000) then user_id end) as over25000
  67. ,count(case when (START_SESSION_RESOURCE_COUNT >= 50000) then user_id end) as over50000
  68. ,count(case when (START_SESSION_RESOURCE_COUNT >= 100000) then user_id end) as over100000
  69. FROM SNOWPLOW.DERIVED."v_INTERVAL_SESSION_INVENTORY" I
  70. WHERE TO_DATE(Interval_Date_Time) >= DATEADD(''DAY'',-31,TO_DATE(GETDATE()))
  71. and Interval_Date_Time > (SELECT MAX_Interval_Date_Time FROM TMP_UM_MAX_INSERT_DATE)
  72. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18)`
  73.  
  74.  
  75. var statement2 = snowflake.createStatement({sqlText: my_sql_command2});
  76. statement2.execute();
  77.  
  78.  
  79.  
  80. //3. Insert Delta into Target Table
  81. var my_sql_command3 = "INSERT INTO CANDIVORE.SEMANTIC_LAYER.T_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME_AMOUNTS \\
  82. SELECT * \\
  83. FROM CANDIVORE.SEMANTIC_LAYER.TMP_T_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME_AMOUNTS;";
  84. var statement3 = snowflake.createStatement({sqlText: my_sql_command3});
  85. statement3.execute();
  86.  
  87. //4. Clean up tables
  88. var my_sql_command4_1 = "DROP TABLE IF EXISTS CANDIVORE.SEMANTIC_LAYER.TMP_T_INTERVAL_SESSION_INVENTORY_RESOURCE_NAME_AMOUNTS;";
  89. var statement4_1 = snowflake.createStatement({sqlText: my_sql_command4_1});
  90. statement4_1.execute();
  91.  
  92. var my_sql_command4_1 = "DROP TABLE IF EXISTS CANDIVORE.SEMANTIC_LAYER.TMP_UM_MAX_INSERT_DATE;";
  93. var statement4_1 = snowflake.createStatement({sqlText: my_sql_command4_1});
  94. statement4_1.execute();
  95.  
  96. var my_sql_command99 = "COMMIT;";
  97. var statement99 = snowflake.createStatement({sqlText: my_sql_command99});
  98. statement99.execute();
  99. ';
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!