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