[sql] clickstream_stg_ing_INCR.hql

Viewer

copydownloadembedprintName: clickstream_stg_ing_INCR.hql
  1. INSERT INTO pimdlkp01_dlkp.ces_tbl_clickstream_stg_ing
  2. SELECT
  3.   DOMAIN
  4.   , CASE WHEN COALESCE(post_evar1,'')='' THEN 
  5.                 CASE WHEN COALESCE(evar1,'')='' THEN 
  6.                         CASE WHEN COALESCE(click_action,'')='' THEN 
  7.                                 CASE WHEN COALESCE(post_pagename,'')='' THEN 
  8.                                         COALESCE(pagename,'') 
  9.                                 ELSE post_pagename 
  10.                                 END 
  11.                         ELSE click_action 
  12.                         END 
  13.                 ELSE evar1 
  14.                 END 
  15.         ELSE post_evar1 
  16.         END pagename
  17.   , CASE WHEN SUBSTRING(page_url,LENGTH(COALESCE(post_page_url,'')))=COALESCE(post_page_url,'') THEN 
  18.               page_url 
  19.              ELSE 
  20.                      CASE WHEN COALESCE(post_page_url,'')='' THEN 
  21.                                    COALESCE(post_evar128, evar128) 
  22.                                   ELSE 
  23.                                       post_page_url 
  24.                          END 
  25.         END page_url 
  26.   , concat_ws('.',post_visid_high,post_visid_low) visitor_id      
  27.   , concat_ws('.',post_visid_high,post_visid_low,visit_start_time_gmt,visit_num) visit_id
  28.   , visit_start_pagename
  29.   , CAST(visit_num AS INT) visit_num
  30.   , CAST(visit_page_num AS INT) visit_page_num
  31.   , post_channel channel
  32.   , post_evar2 evar2
  33.   , post_evar4 evar4
  34.   , post_evar9 evar9
  35.   , post_evar10 evar10
  36.   , post_evar11 evar11
  37.   , post_evar12 evar12
  38.   , post_evar13 evar13
  39.   , post_evar14 evar14
  40.   , post_evar64 evar64
  41.   , post_evar68 evar68
  42.   , post_evar69 evar69
  43.   , post_evar70 evar70
  44.   , post_evar71 evar71
  45.   , post_evar72 evar72
  46.   , post_evar73 evar73
  47.   , post_evar74 evar74
  48.   , post_evar75 evar75
  49.   , post_evar76 evar76
  50.   , post_evar77 evar77
  51.   , post_evar89 Video_Name
  52.   , post_evar90 File_Name
  53.   , post_evar91 File_Type
  54.   , post_evar99 evar99
  55.   , post_evar198 evar198
  56.   , post_evar199 evar199
  57.   , post_evar209 evar209
  58.   , post_evar210 evar210
  59.   , post_prop1 prop1
  60.   , paid_search
  61.   , user_agent
  62.   , ref_domain
  63.   , ref_type
  64.   , referrer
  65.   , visit_ref_type
  66.   , post_event_list event_list
  67.   , post_page_event_var2 page_event_var2
  68.   , click_action
  69.   , click_context
  70.   , click_tag
  71.   , browser
  72.   , resolution
  73.   , nonnullvalue(DOMAIN) AS is_pru_network
  74.   , geo_country country
  75.   , geo_city city
  76.   , geo_region region
  77. -- ------------MCES columns
  78.   , post_visid_high visid_high
  79.   , post_visid_low visid_low
  80.   , date_timestamp
  81.   , new_visit new_visit
  82.   , post_evar67 Form_Name 
  83.   , post_evar111 Event_Name 
  84.   , post_prop15 Percent_Page_Viewed 
  85.   , visit_start_page_url visit_start_page_url
  86. -- ----- Partition columns
  87.   , date_time AS hit_time_gmt_dt
  88.   , COALESCE(CASE
  89.   WHEN LOWER(post_channel) LIKE '%pgim.com' THEN CASE
  90.   WHEN LOWER(post_evar1) LIKE 'pgim:pgim-investments%' THEN 'Investments'
  91.   WHEN LOWER(post_evar1) LIKE 'pgim:ml%' THEN 'Investments'
  92.   WHEN LOWER(post_evar1) LIKE 'pgim:ucits%' THEN 'UCITS'
  93.   WHEN LOWER(post_evar1) LIKE 'pgim:pgim-fixed-income%' THEN 'Fixed Income'
  94.   --Adding below case functions as requested in REQUEST0009956
  95.   WHEN LOWER(post_evar1) LIKE 'pgim:real-estate%'  AND date_time >= '2020-07-20' THEN 'Real Estate' -- DPeters 8/14/20 fix for Drupal migration new URL 7/20 was day urls changed pre launch
  96.   WHEN LOWER(post_evar1) LIKE 'pgim:fixed-income%' AND date_time >= '2020-07-20' THEN 'Fixed Income' -- DPeters 8/14/20 fix for Drupal migration new URL 7/20 was day urls changed pre launch
  97.   WHEN LOWER(post_evar1) LIKE 'pgim:investments%'  AND date_time >= '2020-07-20' THEN 'Investments' -- DPeters 8/14/20 fix for Drupal migration new URL
  98.   WHEN LOWER(post_evar1) LIKE 'pgim:personal:investments%' AND date_time >= '2020-07-20' THEN 'Investments' -- DPeters 8/24/20 fix for Drupal migration new URL REQUEST0010039
  99.   WHEN LOWER(post_evar1) LIKE 'pgim:pgim-japan%'   AND date_time >= '2020-07-20' THEN 'Japan' -- DPeters 8/14/20 Add New for Drupal migration new URL
  100.   WHEN LOWER(post_evar1) LIKE 'pgim:pcom6%'  AND date_time >= '2020-07-20' THEN 'Investments'         -- DPeters 10/23/20 fix for new evar1 values
  101.   WHEN LOWER(post_evar1) LIKE 'prucom:real-estate%'  AND date_time >= '2020-07-20' THEN 'Real Estate' -- DPeters 10/23/20 fix for new evar1 values
  102.   WHEN post_evar1='pgim:error404' THEN NULL
  103.   ELSE 'Center'
  104.   END
  105.   WHEN LOWER(post_channel) IN ('pgimref.com','www.pgimref.com','pgimrealestatefinance.com','www.pgimrealestatefinance.com') THEN 'Real Estate Finance'
  106.   WHEN LOWER(post_channel)='www.jennison.com' THEN 'Jennison'
  107.   WHEN LOWER(post_channel) IN ('pgimrealestate.com','www.pgimrealestate.com') THEN 'Real Estate'
  108.   WHEN LOWER(post_channel) IN ('pgimfixedincome.com','pgimfixedincome.com') THEN 'Fixed Income'
  109.   WHEN LOWER(post_channel)='www.qma.com'  THEN 'PGIM Quant Solutions'                       -- added on 9/24/21 as request by Dennis/Cris
  110.   WHEN LOWER(post_channel)='www.qmaw.com' THEN 'PGIM Wadhwani'                              -- added on 9/24/21 as request by Dennis/Cris
  111.   WHEN LOWER(post_channel)='www.pgimwadhwani.com'              THEN 'PGIM Wadhwani'         -- added on 9/24/21 as request by Dennis/Cris
  112.   WHEN LOWER(post_channel)='www.pgimquantitativesolutions.com' THEN 'PGIM Quant Solutions'  -- added on 9/24/21 as request by Dennis/Cris
  113.   WHEN LOWER(post_channel)='www.pricoaprivatecapital.com'      THEN 'Pricoa PC'             -- added on 9/24/21 as request by Dennis/Cris
  114.   WHEN LOWER(post_channel)='www.prudentialprivatecapital.com'  THEN 'Prudential PC'         -- added on 9/24/21 as request by Dennis/Cris
  115.   --Modifying QMA when conditions as requested in REQUEST0014314
  116.   WHEN LOWER(post_channel) LIKE '%pgimselect.com' THEN 'PGIM Quant Select' -- 3/29/21
  117.   WHEN LOWER(post_channel) LIKE '%pgimquantselect.com' THEN 'PGIM Quant Select' -- 3/29/21
  118.   WHEN LOWER(post_channel) LIKE '%prudential.com' THEN 'Prudential'
  119.   END,'Unknown') AS Business_Unit
  120.   , exclude_hit 
  121.   , runid
  122.   , post_evar67 evar67
  123.   , post_evar146 evar146
  124. -- Adding column as requested by Umesh in REQUEST0007519
  125.   , mcvisid
  126. -- Adding column as requested by Cris in REQUEST0015289
  127.   , evar130
  128.   , evar65
  129. -- Adding column as requested by Cris in REQUEST0015458
  130.   , evar221
  131. -- Adding column as requested by Cris in REQUEST0019027
  132. ,evar3
  133. ,evar102
  134. -- Adding column as requested by Cris in REQUEST0020452
  135. ,eVar63
  136. -- Adding column as requested by Cris on May 25 2022
  137. ,post_page_event_var2
  138. ,evar64 link_text
  139. ,evar67 evar67_new
  140. ,evar68 form_page
  141. ,evar69 form_type
  142. -- Adding column as requested by Cris in REQUEST0023620
  143. ,evar15 taxonomy
  144. ,evar124 NewOrRepeat_Visitor
  145.  FROM ucomktgp01_mktdata.clickstream
  146.  WHERE date_time > (SELECT COALESCE(MAX(hit_time_gmt_dt),'') AS max_date FROM pimdlkp01_dlkp.ces_tbl_clickstream_stg_ing);
  147.  -- To process for a AD-HOC dates, modify the above line to have => where date_time > <the date earlier than missing date>  => where date_time > '2022-11-25'

Editor

You can edit this paste and save as new:


File Description
  • clickstream_stg_ing_INCR.hql
  • Paste Code
  • 27 Jan-2023
  • 6.5 Kb
You can Share it: