API tools faq
paste
Advertisement
YuvalGai

Untitled

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