API tools faq
paste
Advertisement
redskins29

Untitled

redskins29
Feb 2nd, 2017
549
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.25 KB | None | 0 0
raw download clone embed print report diff
  1. #EXTM3U
  2. ---------
  3. -- hbo --
  4. ---------
  5. -- :name streaming-query-hbo-new :? :*
  6. /* :meta {:schedule "0 /30 * * * * *"
  7. :alter-session {"store.json.all_text_mode" false}
  8. :format tsv
  9. :output s3
  10. :headers false
  11. :preamble "#Generic Data Source (Full Processing) template file (user: hbogobroadbandlatamprod ds_id: 2);\r\n
  12. visitorID\tcharSet\ttimestamp\ts_account\ttimezone\tevents\tpageName\tprop9\tprop10\tprop17\tprop18\tprop20\tprop21\tprop22\tprop23\tprop25\tprop26\tprop29\tprop33\tprop36\tprop37\tprop39\tprop41\tprop47\tprop48\tprop61\tprop66\tprop69\teVar2\teVar9\teVar10\teVar12\teVar17\teVar18\teVar20\teVar21\teVar22\teVar23\teVar25\teVar26\teVar29\teVar33\teVar36\teVar37\teVar39\teVar41\teVar47\teVar48\teVar61\teVar66\teVar69\tlinkName\tlinkType"
  13. :bucket-name "tvmetrix-televisa"
  14. :file "reports/hbo/%YEAR-MONTH%/PRODizziHBOData%CDAY%.txt"}
  15. */
  16. select visitor_id,
  17. charset,
  18. regexp_replace(cast(val_timestamp as varchar), ' ', 'T') || timezone || ':00' as val_timestamp,
  19. s_account,
  20. timezone,
  21. events,
  22. pagename as page_name_1,
  23. user_id as user_id_1,
  24. requirements as requirements_1,
  25. video_content_channel as video_content_channel_1,
  26. video_language as video_language_1,
  27. video_type as video_type_1,
  28. video_playback_mode as video_playback_mode_1,
  29. video_content_type as video_content_type_1,
  30. video_player_name as video_player_name_1,
  31. video_rating as video_rating_1,
  32. asset_id as asset_id_1,
  33. logged_in as logged_in_1,
  34. affiliate_id as affiliate_id_1,
  35. platform as platform_1,
  36. channel_id as channel_id_1,
  37. device as device_1,
  38. play_next as play_next_1,
  39. device_detail as device_detail_1,
  40. app_version as app_version_1,
  41. video_duration as video_duration_1,
  42. video_title as video_title_1,
  43. country as country_1,
  44. pagename as page_name_2,
  45. user_id as user_id_2,
  46. requirements as requirements_2,
  47. message_id,
  48. video_content_channel as video_content_channel_2,
  49. video_language as video_language_2,
  50. video_type as video_type_2,
  51. video_playback_mode as video_playback_mode_2,
  52. video_content_type as video_content_type_2,
  53. video_player_name as video_player_name_2,
  54. video_rating as video_rating_2,
  55. asset_id as asset_id_2,
  56. logged_in as logged_in_2,
  57. affiliate_id as affiliate_id_2,
  58. platform as platform_2,
  59. channel_id as channel_id_2,
  60. device as device_2,
  61. play_next as play_next_2,
  62. device_detail as device_detail_2,
  63. app_version as app_version_2,
  64. video_duration as video_duration_2,
  65. video_title as video_title_2,
  66. country as country_2,
  67. link_name,
  68. link_type
  69. from
  70. (select data.events as events,
  71. 'Logged-In' as logged_in,
  72. data.active_user_count as active_user_count,
  73. 'izzi' as affiliate_id,
  74. data.app_version as app_version,
  75. substring(data.provider_asset_id,2,9) as asset_id,
  76. 'HBO' as channel_id,
  77. 'UTF8' as charset,
  78. 'Mexico' as country,
  79. '' as requirements,
  80. case
  81. when data.device_class = 'STB' then data.device_class
  82. else data.device_platform
  83. end as device,
  84. data.device_model || '_VOD' as device_detail,
  85. data.title as video_title,
  86. data.link_name as link_name,
  87. 'o' as link_type,
  88. data.message_id as message_id,
  89. ltrim(data.page_name,substring(data.page_name,1,strpos(substring(data.page_name,1,1),'|'))) as pagename,
  90. data.page_name as page_name,
  91. data.device_class as platform,
  92. '' as play_next,
  93. data.s_account as s_account,
  94. 'FALSE' as sign_in,
  95. data.`timestamp` as val_timestamp,
  96. cast(data.offsetutc as varchar(4)) as timezone,
  97. data.customer_id as user_id,
  98. 'HBO' as video_content_channel,
  99. 'VOD' as video_content_type,
  100. cast(data.length as varchar(50)) as video_duration,
  101. case
  102. when (data.audio_language is not null
  103. and data.audio_language <> '') then upper(data.audio_language)
  104. else 'N/A'
  105. end as video_language,
  106. data.rating as video_rating,
  107. 'fullscreen-VOD' as video_playback_mode,
  108. 'IRISSTB' as video_player_name,
  109. 'Full-length' as video_type,
  110. 'IZZI:' || data.customer_id as visitor_id
  111. from
  112. (select usage.play_session,
  113. usage.events,
  114. usage.active_user_count,
  115. vc.provider_asset_id,
  116. usage.app_version,
  117. usage.device_platform,
  118. usage.device_model,
  119. usage.device_class,
  120. vc.title,
  121. usage.s_account,
  122. ltrim(usage.customer_id,substring(usage.customer_id,1,strpos(usage.customer_id,':'))) as customer_id,
  123. vc.rating,
  124. case
  125. when (usage.page_name is not null
  126. and usage.page_name <> '') then usage.page_name
  127. else vc.title
  128. end as page_name,
  129. usage.region,
  130. usage.offsetutc,
  131. usage.from_prev,
  132. usage.playback,
  133. usage.length,
  134. usage.error_reason,
  135. usage.audio_language,
  136. usage.link_name,
  137. usage.message_id,
  138. usage.context,
  139. usage.`timestamp`
  140. from
  141. (with usagedata as
  142. (select vu.play_session,
  143. vu.product_id,
  144. vu.app_version,
  145. vu.device_platform,
  146. vu.device_model,
  147. vu.device_class,
  148. vu.customer_id,
  149. vu.page_name,
  150. vu.region,
  151. tz.offsetutc,
  152. convert_timezone('UTC',tz.timezone_name, vu.`timestamp`) as `timestamp`,
  153. vu.from_prev,
  154. ((sum(extract(hour
  155. from vu.play_time)) * 3600) + (sum(extract(minute
  156. from vu.play_time)) * 60) + (sum(extract(second
  157. from vu.play_time)))) as playback,
  158. vu.length,
  159. vu.error_reason,
  160. vu.audio_language,
  161. vu.context,
  162. case
  163. when (vu.usage is null
  164. or vu.usage <> 'Test') then :sql:hbo-provider || 'broadbandlatamprod'
  165. else :sql:hbo-provider || 'broadbandlatamdev'
  166. end as s_account,
  167. vu.usage,
  168. (0.10 * vu.length) as percent10,
  169. (0.25 * vu.length) as percent25,
  170. (0.50 * vu.length) as percent50,
  171. (0.75 * vu.length) as percent75,
  172. (0.95 * vu.length) as percent95
  173. from tvmetrix.metrix.vodusage vu
  174. join tvmetrix.metrix.timezones tz
  175. on vu.region = tz.keyname
  176. where partition_date >= :sql:day-before
  177. and partition_date <= :sql:day
  178. and (((vu.play_session_id <> ''
  179. and vu.play_session_id is not null)
  180. and vu.trickplay = 'PLAY'
  181. and (vu.event = 'stop'
  182. or vu.event = 'alive'))
  183. or ((vu.play_session = 'START')
  184. or (vu.play_session = 'STOP')
  185. or (vu.error_reason <> ''
  186. and vu.error_reason is not null)))
  187. and (vu.usage is null
  188. or vu.usage <> 'Test')
  189. and (vu.customer_id is not null
  190. and vu.customer_id <> '')
  191. group by vu.product_id,
  192. vu.play_session,
  193. vu.app_version,
  194. vu.device_platform,
  195. vu.device_model,
  196. vu.device_class,
  197. vu.customer_id,
  198. vu.page_name,
  199. vu.region,
  200. tz.offsetutc,
  201. tz.timezone_name,
  202. vu.`timestamp`,
  203. vu.length,
  204. vu.error_reason,
  205. vu.audio_language,
  206. vu.context,
  207. vu.usage,
  208. vu.from_prev
  209. ) select usagedata.play_session,
  210. (case
  211. when (usagedata.play_session = 'START') then 'event5,event31'
  212. else (case
  213. when ((usagedata.play_session = 'STOP')
  214. and ((usagedata.length-1) between (usagedata.playback - usagedata.from_prev) and usagedata.playback)) then 'event11'
  215. else 'event32=' || cast(usagedata.playback as varchar(50))
  216. end)
  217. end) as events,
  218. (case
  219. when (usagedata.play_session = 'START') then 'TRUE'
  220. else 'FALSE'
  221. end) as active_user_count,
  222. usagedata.product_id,
  223. usagedata.app_version,
  224. usagedata.device_platform,
  225. usagedata.device_model,
  226. usagedata.device_class,
  227. usagedata.s_account,
  228. usagedata.customer_id,
  229. usagedata.page_name,
  230. usagedata.region,
  231. usagedata.offsetutc,
  232. usagedata.`timestamp`,
  233. usagedata.from_prev,
  234. usagedata.playback,
  235. usagedata.length,
  236. usagedata.error_reason,
  237. usagedata.audio_language,
  238. case
  239. when (usagedata.play_session = 'START') then 'AMACTION:Video Start'
  240. else (case
  241. when ((usagedata.play_session = 'STOP')
  242. and ((usagedata.length-1) between (usagedata.playback - usagedata.from_prev) and usagedata.playback)) then 'AMACTION:Video Complete'
  243. else 'AMACTION:Video Time Spent'
  244. end)
  245. end as link_name,
  246. '' as message_id,
  247. usagedata.context
  248. from usagedata
  249. where usagedata.play_session = 'START'
  250. or (usagedata.play_session = 'STOP'
  251. and (usagedata.error_reason = ''
  252. or usagedata.error_reason is null))
  253. ) as usage
  254. join tvmetrix.metrix.vodcontents vc
  255. on usage.product_id = vc.product_id
  256. where vc.provider_id = :sql:hbo-provider
  257. ) as data
  258. where date_trunc('day', `timestamp` - offsetutc * INTERVAL '1' hour) = :sql:day-before)
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!