Skip to content

Instantly share code, notes, and snippets.

@masihyeganeh

masihyeganeh/APARAT_KSQL.md

Last active February 2, 2020 09:38
Show Gist options
  • Save masihyeganeh/345eb6c97f0416c1b7fd8ed90703395d to your computer and use it in GitHub Desktop.
Save masihyeganeh/345eb6c97f0416c1b7fd8ed90703395d to your computer and use it in GitHub Desktop.
Download ZIP
Raw
APARAT_KSQL.md

KSQL Stuffs

To Aggregate watchtime

Aparat

CREATE STREAM APARAT_WATCH (ip VARCHAR, req_ip VARCHAR, vid BIGINT, uid VARCHAR, userid BIGINT, afcn VARCHAR, session_id VARCHAR, profile VARCHAR, watch DOUBLE, progress DOUBLE, position BIGINT, duration VARCHAR, timestamp BIGINT, payload_time BIGINT, chunkname VARCHAR, download INTEGER, watch_type VARCHAR) WITH (kafka_topic='aparat_watch', value_format='JSON', timestamp='timestamp');
CREATE STREAM APARAT_WATCH_WITH_PROPER_KEY
  WITH(KAFKA_TOPIC='aparat-watch-with-proper-key', TIMESTAMP='TIMESTAMP') AS
  SELECT (AFCN + '.' + CAST (VID AS STRING) + '.') AS ID, VID, UID, AFCN, USERID, session_id AS SESSIONID, PROGRESS, POSITION, PROFILE, TIMESTAMP, CHUNKNAME, DOWNLOAD, WATCH_TYPE
  FROM APARAT_WATCH
  PARTITION BY ID;
CREATE TABLE APARAT_WATCH_HOURLY_AGG AS
  SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
    FROM APARAT_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 HOUR)
    WHERE progress IS NOT NULL and position IS NOT NULL
    GROUP BY ID;
CREATE TABLE APARAT_WATCH_DAILY_AGG AS
  SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
    FROM APARAT_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL
    GROUP BY ID;

Filimo

CREATE STREAM FILIMO_WATCH (ip VARCHAR, req_ip VARCHAR, vid BIGINT, uid VARCHAR, userid BIGINT, afcn VARCHAR, session_id VARCHAR, profile VARCHAR, watch DOUBLE, progress DOUBLE, position BIGINT, duration VARCHAR, timestamp BIGINT, payload_time BIGINT, chunkname VARCHAR, download INTEGER, watch_type VARCHAR) WITH (kafka_topic='filimo_watch', value_format='JSON', timestamp='timestamp');
CREATE STREAM FILIMO_WATCH_WITH_PROPER_KEY
  WITH(KAFKA_TOPIC='filimo-watch-with-proper-key', TIMESTAMP='TIMESTAMP') AS
  SELECT (AFCN + '.' + CAST (VID AS STRING) + '.') AS ID, VID, UID, AFCN, USERID, session_id AS SESSIONID, PROGRESS, POSITION, PROFILE, TIMESTAMP, CHUNKNAME, DOWNLOAD, WATCH_TYPE
  FROM FILIMO_WATCH
  PARTITION BY ID;
CREATE TABLE FILIMO_WATCH_HOURLY_AGG AS
  SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
    FROM FILIMO_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 HOUR)
    WHERE progress IS NOT NULL and position IS NOT NULL
    GROUP BY ID;
CREATE TABLE FILIMO_WATCH_DAILY_AGG AS
  SELECT ID, TOPKDISTINCT(AFCN,1)[0] AS AFCN, TOPKDISTINCT(UID, 1)[0] AS UID, TOPKDISTINCT(VID, 1)[0] AS VID, TOPKDISTINCT(SESSIONID, 1)[0] AS SESSIONID, MAX(progress) AS progress, MAX(position) AS position, MAX(timestamp) AS timestamp, TOPKDISTINCT(CHUNKNAME,1)[0] AS chunkname, MAX(DOWNLOAD) AS download
    FROM FILIMO_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL
    GROUP BY ID;

To Aggregate visits, unique visits, watchtime and progress

Aparat

CREATE TABLE APARAT_VIDEO_UNIQUE_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, AFCN, SESSIONID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS UNIQUE_VISIT
    FROM APARAT_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID, AFCN, SESSIONID;
CREATE TABLE APARAT_VIDEO_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS VISIT
    FROM APARAT_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID, AFCN;
CREATE TABLE APARAT_VIDEO_WATCH_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, SUM(POSITION) AS WATCH_TIME
    FROM APARAT_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID;
CREATE TABLE APARAT_VIDEO_PROGRESS_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, MAX(PROGRESS) AS PROGRESS
    FROM APARAT_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID, AFCN;

Filimo

CREATE TABLE FILIMO_VIDEO_UNIQUE_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, AFCN, SESSIONID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS UNIQUE_VISIT
    FROM FILIMO_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID, AFCN, SESSIONID;
CREATE TABLE FILIMO_VIDEO_VISIT_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, COUNT(AFCN) AS VISIT
    FROM FILIMO_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID, AFCN;
CREATE TABLE FILIMO_VIDEO_WATCH_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, SUM(POSITION) AS WATCH_TIME
    FROM FILIMO_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID;
CREATE TABLE FILIMO_VIDEO_PROGRESS_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT VID, UID, AFCN, TIMESTAMPTOSTRING(MAX(timestamp), 'yyyy-MM-dd') AS DATE, MAX(PROGRESS) AS PROGRESS
    FROM FILIMO_WATCH_WITH_PROPER_KEY
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE progress IS NOT NULL and position IS NOT NULL and download=0
    GROUP BY VID, UID, AFCN;

When using AVRO, there should be a "[Topic Name]-value" schema in schema-registry with typed properties according to ksql query

To Configure Mongo Sink with KCQL

PUT http://kfk1.saba-e.com:8083/connectors/aparat-hourly-watch-mongo/config

{
  "topics": "APARAT_WATCH_HOURLY_AGG",
  "connector.class": "com.datamountaineer.streamreactor.connect.mongodb.sink.MongoSinkConnector",
  "name": "aparat-watch-mongo",
  "tasks.max": "1",
  "connect.progress.enabled": true,
  "connect.mongo.connection": "mongodb://kafka:tHe51ODj1B0Y@els9.saba-e.com:37017/aparat?w=1&journal=true",
  "connect.mongo.db": "aparat",
  "connect.mongo.kcql": "UPSERT INTO aparat_watch SELECT * FROM APARAT_WATCH_HOURLY_AGG PK ID",
  "config.action.reload": "RESTART",
  "errors.log.include.messages": true,
  "errors.deadletterqueue.topic.name": "aparat_watch_mongo_dlq",
  "connect.mongo.username": "kafka",
  "connect.mongo.password": "tHe51ODj1B0Y",
  "connect.mongo.error.policy": "RETRY",
  "connect.mongo.batch.size": "5",
  "connect.mongo.max.retries": "5",
  "transforms": "id_to_object",
  "transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
  "transforms.id_to_object.field": "id",
  "value.converter.schemas.enable": "false",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "key.converter.schemas.enable": "false",
  "key.converter": "org.apache.kafka.connect.storage.StringConverter"
}

PUT http://kfk1.saba-e.com:8083/connectors/aparat-daily-watch-mongo/config

{
  "topics": "APARAT_WATCH_DAILY_AGG",
  "connector.class": "com.datamountaineer.streamreactor.connect.mongodb.sink.MongoSinkConnector",
  "name": "aparat-watch-mongo",
  "tasks.max": "1",
  "connect.progress.enabled": true,
  "connect.mongo.connection": "mongodb://kafka:tHe51ODj1B0Y@els9.saba-e.com:37017/aparat?w=1&journal=true",
  "connect.mongo.db": "aparat",
  "connect.mongo.kcql": "UPSERT INTO aparat_watch_daily SELECT * FROM APARAT_WATCH_DAILY_AGG PK ID",
  "config.action.reload": "RESTART",
  "errors.log.include.messages": true,
  "errors.deadletterqueue.topic.name": "aparat_watch_mongo_dlq",
  "connect.mongo.username": "kafka",
  "connect.mongo.password": "tHe51ODj1B0Y",
  "connect.mongo.error.policy": "RETRY",
  "connect.mongo.batch.size": "5",
  "connect.mongo.max.retries": "5",
  "transforms": "id_to_object",
  "transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
  "transforms.id_to_object.field": "id",
  "value.converter.schemas.enable": "false",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "key.converter.schemas.enable": "false",
  "key.converter": "org.apache.kafka.connect.storage.StringConverter"
}

To Configure Elasticsearch Sink with KCQL

PUT http://kfk1.saba-e.com:8083/connectors/aparat-hourly-watch-elastic/config

{
  "topics": "APARAT_WATCH_HOURLY_AGG",
  "connector.class": "com.datamountaineer.streamreactor.connect.elastic6.ElasticSinkConnector",
  "name": "aparat-watch-elastic",
  "tasks.max": "1",
  "connect.progress.enabled": true,
  "connect.elastic.url": "els9.saba-e.com:19200",
  "connect.elastic.use.http": "http",
  "connect.elastic.use.http.username": "",
  "connect.elastic.use.http.password": "",
  "connect.elastic.batch.size": "4000",
  "connect.elastic.kcql": "UPSERT INTO aparat_watch SELECT * FROM APARAT_WATCH_HOURLY_AGG PK ID",
  "config.action.reload": "RESTART",
  "errors.log.include.messages": true,
  "errors.deadletterqueue.topic.name": "aparat_watch_elastic_dlq",
  "transforms": "id_to_object",
  "transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
  "transforms.id_to_object.field": "id",
  "value.converter.schemas.enable": "false",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "key.converter.schemas.enable": "false",
  "key.converter": "org.apache.kafka.connect.storage.StringConverter"
}

To Configure Mongo Sink without KCQL

PUT http://kfk1.saba-e.com:8083/connectors/aparat-hourly-watch-mongo/config

{
  "connector.class": "at.grahsl.kafka.connect.mongodb.MongoDbSinkConnector",
  "topics": "APARAT_WATCH_HOURLY_AGG",
  "mongodb.connection.uri": "mongodb://kafka:tHe51ODj1B0Y@els9.saba-e.com:37017/aparat?w=1&journal=true",
  "name": "aparat-hourly-watch-mongo",
  "value.converter.schemas.enable": "false",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "key.converter.schemas.enable": "false",
  "key.converter": "org.apache.kafka.connect.storage.StringConverter",
  "mongodb.collection": "aparat_hourly_watch",
  "transforms": "id_to_object",
  "transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
  "transforms.id_to_object.field": "id",
  "mongodb.document.id.strategy": "at.grahsl.kafka.connect.mongodb.processor.id.strategy.FullKeyStrategy"
}

PUT http://kfk1.saba-e.com:8083/connectors/aparat-daily-watch-mongo/config

{
  "connector.class": "at.grahsl.kafka.connect.mongodb.MongoDbSinkConnector",
  "topics": "APARAT_WATCH_DAILY_AGG",
  "mongodb.connection.uri": "mongodb://kafka:tHe51ODj1B0Y@els9.saba-e.com:37017/aparat?w=1&journal=true",
  "name": "aparat-daily-watch-mongo",
  "value.converter.schemas.enable": "false",
  "value.converter": "org.apache.kafka.connect.json.JsonConverter",
  "key.converter.schemas.enable": "false",
  "key.converter": "org.apache.kafka.connect.storage.StringConverter",
  "mongodb.collection": "aparat_daily_watch",
  "transforms": "id_to_object",
  "transforms.id_to_object.type": "org.apache.kafka.connect.transforms.HoistField$Key",
  "transforms.id_to_object.field": "id",
  "mongodb.document.id.strategy": "at.grahsl.kafka.connect.mongodb.processor.id.strategy.FullKeyStrategy"
}

To Calculate Top 10000 Active Users

Aparat

CREATE STREAM APARAT_RAW_ACTIVITY AS
  SELECT AFCN, 1 AS COUNT, 1 AS GROUPID
    FROM APARAT_WATCH
    PARTITION BY AFCN;
CREATE TABLE APARAT_AGGREGATED_ACTIVITY AS
  SELECT AFCN, SUM(COUNT) AS COUNT, GROUPID
    FROM APARAT_RAW_ACTIVITY
    WINDOW TUMBLING (SIZE 1 MINUTE)
    GROUP BY AFCN, GROUPID;
/* Not Used */
CREATE TABLE APARAT_TOP_AFCNS AS
  SELECT TOPKDISTINCT(AFCN, 10000) AS AFCNS, GROUPID
    FROM APARAT_RAW_ACTIVITY
    GROUP BY GROUPID;

FILIMO

CREATE STREAM FILIMO_RAW_ACTIVITY AS
  SELECT AFCN, 1 AS COUNT, 1 AS GROUPID
    FROM FILIMO_WATCH
    PARTITION BY AFCN;
CREATE TABLE FILIMO_AGGREGATED_ACTIVITY AS
  SELECT AFCN, SUM(COUNT) AS COUNT, GROUPID
    FROM FILIMO_RAW_ACTIVITY
    WINDOW TUMBLING (SIZE 1 MINUTE)
    GROUP BY AFCN, GROUPID;
/* Not Used */
CREATE TABLE FILIMO_TOP_AFCNS AS
  SELECT TOPKDISTINCT(AFCN, 10000) AS AFCNS, GROUPID
    FROM FILIMO_RAW_ACTIVITY
    GROUP BY GROUPID;

MongoDB Indexes

Aparat

MongoDB collections should have index for all fields

db.getCollection("aparat_daily_watch").createIndex({ "VID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "UID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "POSITION": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "TIMESTAMP": 1 }); // Do we need to set a 31536000 seconds (1 year) retention for it?
db.getCollection("aparat_daily_watch").createIndex({ "ID": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "AFCN": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("aparat_daily_watch").createIndex({ "CHUNKNAME": 1 });

Hourly collection shouldn't be kept longer than a month

db.getCollection("aparat_hourly_watch").createIndex({ "VID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "UID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "POSITION": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "TIMESTAMP": 1 }, { "expireAfterSeconds": 2592000 }); // 1 month retention
db.getCollection("aparat_hourly_watch").createIndex({ "ID": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "AFCN": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("aparat_hourly_watch").createIndex({ "CHUNKNAME": 1 });

Filimo

MongoDB collections should have index for all fields

db.getCollection("filimo_daily_watch").createIndex({ "VID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "UID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "POSITION": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "TIMESTAMP": 1 }); // Do we need to set a 31536000 seconds (1 year) retention for it?
db.getCollection("filimo_daily_watch").createIndex({ "ID": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "AFCN": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("filimo_daily_watch").createIndex({ "CHUNKNAME": 1 });

Hourly collection shouldn't be kept longer than a month

db.getCollection("filimo_hourly_watch").createIndex({ "VID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "UID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "SESSIONID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "POSITION": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "PROGRESS": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "TIMESTAMP": 1 }, { "expireAfterSeconds": 2592000 }); // 1 month retention
db.getCollection("filimo_hourly_watch").createIndex({ "ID": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "AFCN": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "DOWNLOAD": 1 });
db.getCollection("filimo_hourly_watch").createIndex({ "CHUNKNAME": 1 });

Analytics

CREATE STREAM ANALYTICS (afcn VARCHAR, source VARCHAR, user VARCHAR, action VARCHAR, session VARCHAR, id VARCHAR, section VARCHAR, type VARCHAR, url VARCHAR, TIMESTAMP VARCHAR, "PROPERTIES" MAP<VARCHAR, VARCHAR>) WITH (kafka_topic='analytics', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');


CREATE STREAM ANALYTICS_WITH_PROPER_KEY WITH(KAFKA_TOPIC='analytics_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
  SELECT (id + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(id AS BIGINT) AS videoid, source, action, afcn, user, session, section, type, url, TIMESTAMP, "PROPERTIES"
    FROM ANALYTICS
    PARTITION BY "ID";


CREATE STREAM ANALYTICS_ALL_EVENTS (SOURCE VARCHAR, VIDEOID BIGINT, TIMESTAMP VARCHAR, RECOMMENDATION_IMPRESSIONS INTEGER, RECOMMENDATION_PAGEVIEWS INTEGER, RECOMMENDATION_BOOSTED_PAGEVIEWS INTEGER, RECOMMENDATION_CLICKS INTEGER, RECOMMENDATION_RIGHT_CLICKS INTEGER, RECOMMENDATION_DISPLAYS INTEGER, RECOMMENDATION_DISPLAY_CANDIDATES INTEGER, RECOMMENDATION_BOOSTED_IMPRESSIONS INTEGER, RECOMMENDATION_BOOSTED_CLICKS INTEGER, RECOMMENDATION_BOOSTED_RIGHT_CLICKS INTEGER, RECOMMENDATION_BOOSTED_CTA_CLICKS INTEGER, RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS INTEGER, SEARCH_RESULT_IMPRESSIONS INTEGER, SEARCH_RESULT_CLICKS INTEGER, SEARCH_RESULT_RIGHT_CLICKS INTEGER, SEARCH_RESULT_BOOSTED_IMPRESSIONS INTEGER, SEARCH_RESULT_BOOSTED_CLICKS INTEGER, SEARCH_RESULT_BOOSTED_RIGHT_CLICKS INTEGER, SEARCH_RESULT_DISPLAYS INTEGER, SEARCH_RESULT_DISPLAY_CANDIDATES INTEGER, SEARCH_SUGGESTION_IMPRESSIONS INTEGER, SEARCH_SUGGESTION_CLICKS INTEGER, SEARCH_SUGGESTION_RIGHT_CLICKS INTEGER, SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS INTEGER, SEARCH_SUGGESTION_BOOSTED_CLICKS INTEGER, SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS INTEGER, SEARCH_SUGGESTION_DISPLAYS INTEGER, SEARCH_SUGGESTION_DISPLAY_CANDIDATES INTEGER) WITH (KAFKA_TOPIC='analytics_all_events', VALUE_FORMAT='json', KEY='VIDEOID');

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 1 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='impression' and type != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 1 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE action='pageview' and type != 'boosted' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 1 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE action='pageview' and type='boosted' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 1 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='click' and type != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 1 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='right_click' and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 1 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='display' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 1 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='display_candidate' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 1 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='impression' and type='boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 1 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 1 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='right_click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 1 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='click' and TYPE = 'boosted-cta' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 1 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='recommendation' and action='right_click' and TYPE = 'boosted-cta' PARTITION BY VIDEOID;


INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 1 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='impression' and type != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 1 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='click' and type != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 1 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='right_click' and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 1 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='impression' and type='boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 1 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 1 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action='right_click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;

/*
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 1 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action="..." PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 1 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_result' and action="..." PARTITION BY VIDEOID;
*/

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 1 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='impression' and type != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 1 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='click' and type != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 1 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='right_click' and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 1 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='impression' and type='boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 1 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 1 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action='right_click' and TYPE = 'boosted-brc' PARTITION BY VIDEOID;

/*
INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 1 as SEARCH_SUGGESTION_DISPLAYS, 0 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action="..." PARTITION BY VIDEOID;

INSERT INTO ANALYTICS_ALL_EVENTS SELECT SOURCE, VIDEOID, TIMESTAMP, 0 as RECOMMENDATION_IMPRESSIONS, 0 as RECOMMENDATION_PAGEVIEWS, 0 as RECOMMENDATION_BOOSTED_PAGEVIEWS, 0 as RECOMMENDATION_CLICKS, 0 as RECOMMENDATION_RIGHT_CLICKS, 0 as RECOMMENDATION_DISPLAYS, 0 as RECOMMENDATION_DISPLAY_CANDIDATES, 0 as RECOMMENDATION_BOOSTED_IMPRESSIONS, 0 as RECOMMENDATION_BOOSTED_CLICKS, 0 as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_CLICKS, 0 as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, 0 as SEARCH_RESULT_IMPRESSIONS, 0 as SEARCH_RESULT_CLICKS, 0 as SEARCH_RESULT_RIGHT_CLICKS, 0 as SEARCH_RESULT_BOOSTED_IMPRESSIONS, 0 as SEARCH_RESULT_BOOSTED_CLICKS, 0 as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_RESULT_DISPLAYS, 0 as SEARCH_RESULT_DISPLAY_CANDIDATES, 0 as SEARCH_SUGGESTION_IMPRESSIONS, 0 as SEARCH_SUGGESTION_CLICKS, 0 as SEARCH_SUGGESTION_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, 0 as SEARCH_SUGGESTION_BOOSTED_CLICKS, 0 as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, 0 as SEARCH_SUGGESTION_DISPLAYS, 1 as SEARCH_SUGGESTION_DISPLAY_CANDIDATES FROM ANALYTICS_WITH_PROPER_KEY WHERE section='search_suggestion' and action="..." PARTITION BY VIDEOID;
*/

/* Use these two instead of laters when MAX(BIGINT) is supported */
/*
CREATE TABLE ANALYTICS_APARAT_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='aparat' or SOURCE='aparat_web' or SOURCE='aparat_android' or SOURCE='aparat_ios') GROUP BY VIDEOID;

CREATE TABLE ANALYTICS_FILIMO_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='filimo' or SOURCE='filimo_web' or SOURCE='filimo_android' or SOURCE='filimo_ios') GROUP BY VIDEOID;
*/


CREATE TABLE ANALYTICS_APARAT_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(CAST(MAX(CAST(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')/1000 AS INT)) AS BIGINT) * 1000, 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='aparat' or SOURCE='aparat_web' or SOURCE='aparat_android' or SOURCE='aparat_ios') GROUP BY VIDEOID;

CREATE TABLE ANALYTICS_FILIMO_DAILY_AGGREGATED WITH (VALUE_FORMAT='AVRO') AS SELECT VIDEOID, TIMESTAMPTOSTRING(CAST(MAX(CAST(STRINGTOTIMESTAMP(TIMESTAMP, 'yyyy-MM-dd''T''HH:mm:ssX')/1000 AS INT)) AS BIGINT) * 1000, 'yyyy-MM-dd') AS TIMESTAMP, SUM(RECOMMENDATION_IMPRESSIONS) as RECOMMENDATION_IMPRESSIONS, SUM(RECOMMENDATION_PAGEVIEWS) as RECOMMENDATION_PAGEVIEWS, SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS) as RECOMMENDATION_BOOSTED_PAGEVIEWS, SUM(RECOMMENDATION_CLICKS) as RECOMMENDATION_CLICKS, SUM(RECOMMENDATION_RIGHT_CLICKS) as RECOMMENDATION_RIGHT_CLICKS, SUM(RECOMMENDATION_DISPLAYS) as RECOMMENDATION_DISPLAYS, SUM(RECOMMENDATION_DISPLAY_CANDIDATES) as RECOMMENDATION_DISPLAY_CANDIDATES, SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS) as RECOMMENDATION_BOOSTED_IMPRESSIONS, SUM(RECOMMENDATION_BOOSTED_CLICKS) as RECOMMENDATION_BOOSTED_CLICKS, SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_RIGHT_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS) as RECOMMENDATION_BOOSTED_CTA_CLICKS, SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS) as RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS, SUM(SEARCH_RESULT_IMPRESSIONS) as SEARCH_RESULT_IMPRESSIONS, SUM(SEARCH_RESULT_CLICKS) as SEARCH_RESULT_CLICKS, SUM(SEARCH_RESULT_RIGHT_CLICKS) as SEARCH_RESULT_RIGHT_CLICKS, SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS) as SEARCH_RESULT_BOOSTED_IMPRESSIONS, SUM(SEARCH_RESULT_BOOSTED_CLICKS) as SEARCH_RESULT_BOOSTED_CLICKS, SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS) as SEARCH_RESULT_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_RESULT_DISPLAYS) as SEARCH_RESULT_DISPLAYS, SUM(SEARCH_RESULT_DISPLAY_CANDIDATES) as SEARCH_RESULT_DISPLAY_CANDIDATES, SUM(SEARCH_SUGGESTION_IMPRESSIONS) as SEARCH_SUGGESTION_IMPRESSIONS, SUM(SEARCH_SUGGESTION_CLICKS) as SEARCH_SUGGESTION_CLICKS, SUM(SEARCH_SUGGESTION_RIGHT_CLICKS) as SEARCH_SUGGESTION_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS) as SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS, SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS) as SEARCH_SUGGESTION_BOOSTED_CLICKS, SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS) as SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS, SUM(SEARCH_SUGGESTION_DISPLAYS) as SEARCH_SUGGESTION_DISPLAYS, SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES) as SEARCH_SUGGESTION_DISPLAY_CANDIDATES, (SUM(RECOMMENDATION_IMPRESSIONS)+SUM(RECOMMENDATION_PAGEVIEWS)+SUM(RECOMMENDATION_BOOSTED_PAGEVIEWS)+SUM(RECOMMENDATION_CLICKS)+SUM(RECOMMENDATION_RIGHT_CLICKS)+SUM(RECOMMENDATION_DISPLAYS)+SUM(RECOMMENDATION_DISPLAY_CANDIDATES)+SUM(RECOMMENDATION_BOOSTED_IMPRESSIONS)+SUM(RECOMMENDATION_BOOSTED_CLICKS)+SUM(RECOMMENDATION_BOOSTED_RIGHT_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_CLICKS)+SUM(RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICKS)+SUM(SEARCH_RESULT_IMPRESSIONS)+SUM(SEARCH_RESULT_CLICKS)+SUM(SEARCH_RESULT_RIGHT_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_IMPRESSIONS)+SUM(SEARCH_RESULT_BOOSTED_CLICKS)+SUM(SEARCH_RESULT_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_RESULT_DISPLAYS)+SUM(SEARCH_RESULT_DISPLAY_CANDIDATES)+SUM(SEARCH_SUGGESTION_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_CLICKS)+SUM(SEARCH_SUGGESTION_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_IMPRESSIONS)+SUM(SEARCH_SUGGESTION_BOOSTED_CLICKS)+SUM(SEARCH_SUGGESTION_BOOSTED_RIGHT_CLICKS)+SUM(SEARCH_SUGGESTION_DISPLAYS)+SUM(SEARCH_SUGGESTION_DISPLAY_CANDIDATES)) as OVERALL FROM ANALYTICS_ALL_EVENTS WINDOW TUMBLING (SIZE 1 DAY) WHERE VIDEOID is not null and (SOURCE='filimo' or SOURCE='filimo_web' or SOURCE='filimo_android' or SOURCE='filimo_ios') GROUP BY VIDEOID;

Impression (overall, boosted)

CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_recommendation_impression', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');

CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION_WITH_PROPER_KEY WITH(KAFKA_TOPIC='analytics_aparat_recommendation_impression_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
  SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, TIMESTAMP
    FROM ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION
    PARTITION BY ID;
  
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_IMPRESSION_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_impression_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null
    GROUP BY ID, VIDEOID;

CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOST_IMPRESSION_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_impression_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and TYPE = 'boosted-brc'
    GROUP BY ID, VIDEOID;

Pageview

CREATE STREAM ANALYTICS_APARAT_PAGEVIEW (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_pageview', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');

CREATE STREAM ANALYTICS_APARAT_PAGEVIEW_WITH_PROPER_KEY
  WITH(KAFKA_TOPIC='analytics_aparat_pageview_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
  SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, TIMESTAMP
  FROM ANALYTICS_APARAT_PAGEVIEW
  PARTITION BY ID;

CREATE TABLE ANALYTICS_APARAT_PAGEVIEW_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_pageview_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null
    GROUP BY ID, VIDEOID;
  
CREATE TABLE ANALYTICS_APARAT_VIDEO_PAGEVIEW_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_pageview_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and ID != ''
    GROUP BY ID, VIDEOID;

Click (overall, boosted, boosted-cta)

CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_CLICK (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, x INT, y INT, target VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_recommendation_click', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');

CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_CLICK_WITH_PROPER_KEY
  WITH(KAFKA_TOPIC='analytics_aparat_recommendation_click_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
  SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, X, Y, TARGET, TIMESTAMP
  FROM ANALYTICS_APARAT_RECOMMENDATION_CLICK
  PARTITION BY ID;
  
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_click_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc'
    GROUP BY ID, VIDEOID;

CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_click_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and TYPE = 'boosted-brc'
    GROUP BY ID, VIDEOID;

CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_CTA_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_click_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and TYPE = 'boosted-cta'
    GROUP BY ID, VIDEOID;

Right Click (overall, boosted, boosted-cta)

CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK (afcn VARCHAR, user VARCHAR, session VARCHAR, id VARCHAR, type VARCHAR, url VARCHAR, x INT, y INT, target VARCHAR, timestamp VARCHAR) WITH (kafka_topic='analytics_aparat_recommendation_right_click', value_format='JSON', TIMESTAMP='timestamp', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX');

CREATE STREAM ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK_WITH_PROPER_KEY
  WITH(KAFKA_TOPIC='analytics_aparat_recommendation_right_click_with_proper_key', TIMESTAMP='TIMESTAMP', TIMESTAMP_FORMAT='yyyy-MM-dd''T''HH:mm:ssX') AS
  SELECT (ID + '-' + TIMESTAMPTOSTRING(ROWTIME, 'yyyy-MM-dd')) AS ID, CAST(ID AS BIGINT) AS VIDEOID, AFCN, SESSION, TYPE, URL, X, Y, TARGET, TIMESTAMP
  FROM ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK
  PARTITION BY ID;
  
CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_RIGHT_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_right_click_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and TYPE != 'boosted-cta' AND TYPE != 'boosted-brc'
    GROUP BY ID, VIDEOID;

CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_RIGHT_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_right_click_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and TYPE = 'boosted-brc'
    GROUP BY ID, VIDEOID;

CREATE TABLE ANALYTICS_APARAT_RECOMMENDATION_BOOSTED_CTA_RIGHT_CLICK_DAILY_AGG WITH (VALUE_FORMAT='AVRO') AS
  SELECT ID, VIDEOID, TIMESTAMPTOSTRING(MAX(STRINGTOTIMESTAMP(timestamp, 'yyyy-MM-dd''T''HH:mm:ssX')), 'yyyy-MM-dd') AS timestamp, COUNT(ID) AS count
    FROM analytics_aparat_recommendation_right_click_with_proper_key
    WINDOW TUMBLING (SIZE 1 DAY)
    WHERE VIDEOID is not null and TYPE = 'boosted-cta'
    GROUP BY ID, VIDEOID;

TODO:

  • KSQL Default retention is incorrect

Deletion

To delete a table with it's topic:

DROP TABLE table_name DELETE TOPIC;

To delete a stream with it's topic:

DROP STREAM stream_name DELETE TOPIC;

To delete a topic:

docker exec -it broker kafka-topics --zookeeper zookeeper:2181 --delete --topic TOPIC_NAME

To alter a topic (e.g. for changing partitions count):

docker exec -it broker kafka-topics --zookeeper zookeeper:2181 --alter --topic TOPIC_NAME --partitions NUMBER
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment