external.sql

drop view tvmetrix.metrix.`vodusage/raw`;
create view tvmetrix.metrix.`vodusage/raw` as
  (select
    t.dir0 as upload_date,
    -- Event fields
    cast(t.streaming_data_id as bigint) as streaming_data_id,
    cast(t.`module` as varchar) as `module`,
    cast(t.event as varchar) as event,
    to_timestamp(regexp_replace(t.`timestamp`, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss.SSS') as `timestamp`,
    to_timestamp(cast(t.playposition as bigint)) as play_time,
    -- 'Izzi' as affiliate_name,
    cast(t.appName as varchar) as affiliate_name,
    cast(t.appVersion as varchar) as app_version,
    cast(t.protocolVersion as varchar) as protocol_version,
    cast(t.pageName as varchar) as page_name,
    --- cast(t.signIn.mode as varchar) as signin_mode,
    -- Session/Device
    cast(t.session.device.class as varchar) as device_class,
    cast(t.session.device.platform as varchar) as device_platform,
    cast(t.session.device.platformVersion as varchar) as device_platform_version,
    cast(t.session.device.make as varchar) as device_manufacturer,
    cast(t.session.device.model as varchar) as device_model,
    cast(t.session.device.deviceId as varchar) as device_id,
    cast(t.session.device.usage as varchar) as usage,
    cast(t.session.device.mac as varchar) as mac_unit,
    -- Session/Subscriber
    cast(t.session.subscriber.subscriberId as varchar) as customer_id,
    cast(t.session.subscriber.region as varchar) as region,
    cast(t.session.subscriber.zipcode as varchar) as zipcode,
    -- Session/Timestamp
    to_timestamp(regexp_replace(t.session.sessionTimestamp, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss.SSS') as session_timestamp,
    -- Content
    cast(t.content.contentId as varchar) as content_id,
    cast(t.content.title as varchar) as title,
    cast(t.content.genres[0] as varchar) as genre,
    cast(t.content.parentalRating as varchar) as rating,
    cast(t.content.duration as bigint) as length,
    -- Delivery
    cast(t.delivery.deliveryContext as varchar) as context,
    cast(t.delivery.deliveryId as varchar) as external_ref,
    to_timestamp(regexp_replace(t.delivery.startTime, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss.SSS') as delivery_start_date,
    to_timestamp(regexp_replace(t.delivery.endTime, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss.SSS') as delivery_end_date,
    cast(t.delivery.provider as varchar) as provider,
    cast(t.delivery.systemId as varchar) as systemId,
    cast(t.delivery.url as varchar) as delivery_url,
    cast(t.delivery.audioLanguages[0] as varchar) as delivery_audios,
    cast(t.delivery.subtitleLanguages[0] as varchar) as delivery_subtitles,
    cast(t.delivery.audioFormat[0] as varchar) as delivery_audioformats,
    -- Options
    cast(t.options.audioLanguage as varchar) as audio_language,
    cast(t.options.subtitleLanguage as varchar) as subtitle_language,
    cast(t.options.audioFormat as varchar) as audio_format,
    cast(t.options.videoFormat as varchar) as video_format,
    cast(t.options.screenResolution as varchar) as scr_res,
    cast(t.options.screenFormat as varchar) as scr_format,
    -- Product
    cast(t.product.productId as varchar) as product_id,
    cast(t.product.productName as varchar) as product_name,
    cast(t.product.commercialType as varchar) as product_type,
    cast(t.product.rentalPeriod as bigint) as rental_period,
    cast(t.product.`system` as varchar) as `system`,
    cast(t.product.price as float) as price,
    -- ???
    cast(t.preview as boolean) as preview,
    cast(t.postal_code as varchar) as postal_code, -- ???
    -- PlaySession
    cast(t.playSession.serviceGroup as varchar) as service_group,
    cast(t.playSession.playSessionId as varchar) as play_session_id,
    cast(t.playSession.playSession as varchar) as play_session,
    -- TimeInterval
    cast(t.timeInterval.fromStart as bigint) as from_start,
    cast(t.timeInterval.fromPrev  as bigint) as from_prev,
    -- Subscription
    cast(t.subscription.subcriptionServiceId as varchar) as subscription_id,
    cast(t.subscription.subscriptionServiceName as varchar) as subscription_name,
    cast(t.subscription.subscriptionPackageId as varchar) as subscription_package_id,
    cast(t.subscription.subscriptionPackageName as varchar) as subscription_package_name,
    -- Trickplay
    cast(t.trickplay.trickplayMode as varchar) as trickplay,
    cast(t.trickplay.speed as varchar) as speed,
    -- Error
    case
      when t.error_code is null then ''
      else cast(t.error_code as varchar)
    end as error_code,
    cast(t.error_reason as varchar) as error_reason,
    -- ???
    cast(t.platform_type as varchar) as platform_type,
    'Streaming' as est_distribution_type
  from tvmetrix.`raw` t
  where cast(t.`module` as varchar)='play' and cast(t.delivery.deliveryContext as varchar)='VOD')

drop view tvmetrix.metrix.`vodusage/external`;
create view tvmetrix.metrix.`vodusage/external` as
  (select
    t.dir0 as upload_date,
    -- Event fields
    cast(t.streaming_data_id as bigint) as streaming_data_id,
    cast(t.`module` as varchar) as `module`,
    cast(t.event as varchar) as event,
    to_timestamp(regexp_replace(t.`timestamp`, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss') as `timestamp`,
    to_timestamp(regexp_replace(t.playtime, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss') as play_time,
    cast(t.appName as varchar) as affiliate_name,
    cast(t.appVersion as varchar) as app_version,
    cast(t.protocolVersion as varchar) as protocol_version,
    cast(t.pageName as varchar) as page_name,
    --- cast(t.signIn.mode as varchar) as signin_mode,
    -- Session/Device
    cast(t.session.device.class as varchar) as device_class,
    cast(t.session.device.platform as varchar) as device_platform,
    cast(t.session.device.platformVersion as varchar) as device_platform_version,
    cast(t.session.device.make as varchar) as device_manufacturer,
    cast(t.session.device.model as varchar) as device_model,
    cast(t.session.device.deviceId as varchar) as device_id,
    cast(t.session.device.usage as varchar) as usage,
    cast(t.session.device.mac as varchar) as mac_unit,
    -- Session/Subscriber
    cast(t.session.subscriber.subscriberId as varchar) as customer_id,
    cast(t.session.subscriber.region as varchar) as region,
    cast(t.session.subscriber.zipcode as varchar) as zipcode,
    -- Session/Timestamp
    to_timestamp(regexp_replace(t.session.`timestamp`, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss') as session_timestamp,
    -- Content
    cast(t.content.contentId as varchar) as content_id,
    cast(t.content.title as varchar) as title,
    cast(t.content.genres as varchar) as genre,
    cast(t.content.parentalRating as varchar) as rating,
    cast(t.content.duration as bigint) as length,
    -- Delivery
    cast(t.delivery.deliveryContext as varchar) as context,
    cast(t.delivery.deliveryId as varchar) as external_ref,
    to_timestamp(regexp_replace(t.delivery.startTime, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss') as delivery_start_date,
    to_timestamp(regexp_replace(t.delivery.endTime, '[TZ]', ''),
                 'yyyy-MM-ddHH:mm:ss') as delivery_end_date,
    cast(t.delivery.provider as varchar) as provider,
    cast(t.delivery.systemId as varchar) as systemId,
    cast(t.delivery.url as varchar) as delivery_url,
    cast(t.delivery.audioLanguages as varchar) as delivery_audios,
    cast(t.delivery.subtitleLanguages as varchar) as delivery_subtitles,
    cast(t.delivery.audioFormat as varchar) as delivery_audioformats,
    -- Options
    cast(t.options.audioLanguage as varchar) as audio_language,
    cast(t.options.subtitleLanguage as varchar) as subtitle_language,
    cast(t.options.audioFormat as varchar) as audio_format,
    cast(t.options.videoFormat as varchar) as video_format,
    cast(t.options.screenResolution as varchar) as scr_res,
    cast(t.options.screenFormat as varchar) as scr_format,
    -- Product
    cast(t.product.productId as varchar) as product_id,
    cast(t.product.productName as varchar) as product_name,
    cast(t.product.commercialType as varchar) as product_type,
    cast(t.product.rentalPeriod as bigint) as rental_period,
    cast(t.product.`system` as varchar) as `system`,
    cast(t.product.price as float) as price,
    -- ???
    cast(t.preview as boolean) as preview,
    cast(t.postal_code as varchar) as postal_code,
    -- PlaySession
    cast(t.playSession.serviceGroup as varchar) as service_group,
    cast(t.playSession.playSessionId as varchar) as play_session_id,
    cast(t.playSession.playSession as varchar) as play_session,
    -- TimeInterval
    cast(t.timeInterval.fromStart as bigint) as from_start,
    cast(t.timeInterval.fromPrev  as bigint) as from_prev,
    -- Subscription
    cast(t.subscription.subcriptionServiceId as varchar) as subscription_id,
    cast(t.subscription.subscriptionServiceName as varchar) as subscription_name,
    cast(t.subscription.subscriptionPackageId as varchar) as subscription_package_id,
    cast(t.subscription.subscriptionPackageName as varchar) as subscription_package_name,
    -- Trickplay
    cast(t.trickplay.trickplayMode as varchar) as trickplay,
    cast(t.trickplay.speed as varchar) as speed,
    -- Error
    case
      when t.error_code is null then ''
      else cast(t.error_code as varchar)
    end as error_code,
    cast(t.error_reason as varchar) as error_reason,
    -- ???
    cast(t.platform_type as varchar) as platform_type,
    'Streaming' as est_distribution_type
    from tvmetrix.`external/vod` t);


drop view tvmetrix.metrix.vodusage;
create view tvmetrix.metrix.vodusage as
  (select *
   from tvmetrix.metrix.`vodusage/raw`
   union all select *
   from tvmetrix.metrix.`vodusage/external`);