Skip to content

ClickHouse Current

Jump to bottom
Jaafar El Harouchi edited this page Apr 23, 2019 · 1 revision 
CREATE DATABASE IF NOT EXISTS BidRequests;

CREATE TABLE IF NOT EXISTS BidRequests.MediaPlanning
(
    event_date  Date,
    ssp         String,
    siteAppName String,
    country     FixedString(3),
    requestType Enum8 (
        'banner' = 1,
        'exadsBanner' = 2,
        'exadsPop' = 3,
        'native' = 4,
        'popunder' = 5),
    conType     UInt8,
    deviceOs    String,
    deviceType  UInt8,
    carrier     String,
    siteAppId   String,
    isApp       UInt8,
    browser     String,
    language    FixedString(2),
    iab         String,
    size        String,
    minCPM      AggregateFunction(avg, Float32),
    MTOTAL      UInt64,
    lastUpdated AggregateFunction(max, DateTime)
)
    engine = SummingMergeTree()
        PARTITION BY toYYYYMMDD(event_date)
        ORDER BY (event_date, country, requestType, conType, isApp, deviceOs, deviceType,
                  carrier, browser, size, ssp, siteAppName, siteAppId, language, iab);


CREATE TABLE IF NOT EXISTS BidRequests.statsByDay
(
    event_date  Date,
    server      String,
    ssp         String,
    visits      UInt64,
    lastUpdated AggregateFunction(max, DateTime)
)
    engine = SummingMergeTree()
        PARTITION BY toYYYYMM(event_date)
        ORDER BY (event_date, server, ssp);


CREATE DATABASE if not exists DMP;


CREATE TABLE IF NOT EXISTS DMP.users
(
    country     FixedString(3),
    userId      String,
    conType     UInt8,
    carrier     String,
    gender      AggregateFunction(max, FixedString(1)),
    yob         AggregateFunction(max, UInt16),
    visits      UInt64,
    created     AggregateFunction(min, datetime),
    lastUpdated AggregateFunction(max, datetime)
) ENGINE = SummingMergeTree()
      PARTITION BY substring(userId, 1, 3)
      ORDER BY (country, userId, conType, carrier);


CREATE TABLE IF NOT EXISTS DMP.interests
(
    country     FixedString(3),
    iab         String,
    userId      String,
    visits      UInt64,
    lastUpdated AggregateFunction(max, datetime)
) ENGINE = SummingMergeTree()
      PARTITION BY substring(userId, 1, 3)
      ORDER BY (country, iab, userId);


-- Incoming Kafka Queues

CREATE TABLE IF NOT EXISTS BidRequests.usQueue
(
    id             String,
    event_datetime DateTime,
    ssp            String,
    siteAppName    String,
    country        String,
    requestType    String,
    conType        UInt8,
    deviceOs       String,
    deviceType     UInt8,
    carrier        String,
    siteAppId      String,
    isApp          UInt8,
    server         String,
    userId         String,
    lat            Float32,
    long           Float32,
    browser        String,
    language       String,
    iab            String,
    size           String,
    minCPM         Float32,
    gender         String,
    yob            UInt16,
    url            String
)
    engine = Kafka
        SETTINGS kafka_broker_list = '192.168.6.27:9092,192.168.6.28:9092,192.168.6.29:9092',
            kafka_topic_list = 'dsp-BidRequests',
            kafka_group_name = 'clickhouse.a4g.com-BidRequests',
            kafka_skip_broken_messages = 100000,
            kafka_format = 'TSV',
            kafka_row_delimiter = '\n',
            kafka_num_consumers = 11,
            kafka_max_block_size = 131072;

CREATE TABLE IF NOT EXISTS BidRequests.euQueue
(
    id             String,
    event_datetime DateTime,
    ssp            String,
    siteAppName    String,
    country        String,
    requestType    String,
    conType        UInt8,
    deviceOs       String,
    deviceType     UInt8,
    carrier        String,
    siteAppId      String,
    isApp          UInt8,
    server         String,
    userId         String,
    lat            Float32,
    long           Float32,
    browser        String,
    language       String,
    iab            String,
    size           String,
    minCPM         Float32,
    gender         String,
    yob            UInt16,
    url            String
)
    engine = Kafka
        SETTINGS kafka_broker_list = '195.181.167.158:9092,195.181.167.142:9092,195.181.167.140:9092',
            kafka_topic_list = 'dsp-BidRequests',
            kafka_group_name = 'clickhouse.a4g.com-BidRequests',
            kafka_skip_broken_messages = 10000,
            kafka_format = 'TSV',
            kafka_row_delimiter = '\n',
            kafka_num_consumers = 3
;


-- Materialized Views : Processors


CREATE MATERIALIZED VIEW IF NOT EXISTS BidRequests.usTraffic
    (id String,
     event_datetime DateTime,
     ssp String,
     siteAppName String,
     country FixedString(3),
     requestType Enum8 (
         'banner' = 1,
         'exadsBanner' = 2,
         'exadsPop' = 3,
         'native' = 4,
         'popunder' = 5),
     conType UInt8,
     deviceOs String,
     deviceType UInt8,
     carrier String,
     siteAppId String,
     isApp UInt8,
     server String,
     userId String,
     browser String,
     language FixedString(2),
     iab String,
     size String,
     minCPM Float32,
     gender FixedString(1),
     yob UInt16,
     url String)
    ENGINE = MergeTree()
        PARTITION BY toYYYYMMDD(event_datetime)
        ORDER BY (event_datetime,
                  ssp,
                  siteAppName,
                  country,
                  requestType,
                  conType,
                  deviceOs,
                  deviceType,
                  carrier,
                  siteAppId,
                  server,
                  userId,
                  browser,
                  language,
                  iab,
                  size,
                  minCPM,
                  gender,
                  yob,
                  url)
AS
SELECT DISTINCT id,
                event_datetime,
                ssp,
                siteAppName,
                toFixedString(extract(upper(country), '\\w\\w\\w'), 3) as country,
                cast(requestType as Enum8 (
                    'banner' = 1,
                    'exadsBanner' = 2,
                    'exadsPop' = 3,
                    'native' = 4,
                    'popunder' = 5))                                  as requestType,
                conType,
                deviceOs,
                deviceType,
                carrier,
                siteAppId,
                isApp,
                server,
                extract(userId, '\\w{3,}:.{8,}')                      as userId,
                browser,
                toFixedString(extract(lower(language), '\\w\\w'), 2)  as language,
                iab,
                size,
                minCPM,
                toFixedString(extract(gender, 'M|F'), 1)              as gender,
                yob,
                url
FROM BidRequests.usQueue;


CREATE MATERIALIZED VIEW IF NOT EXISTS BidRequests.euTraffic
    (id String,
     event_datetime DateTime,
     ssp String,
     siteAppName String,
     country FixedString(3),
     requestType Enum8 (
         'banner' = 1,
         'exadsBanner' = 2,
         'exadsPop' = 3,
         'native' = 4,
         'popunder' = 5),
     conType UInt8,
     deviceOs String,
     deviceType UInt8,
     carrier String,
     siteAppId String,
     isApp UInt8,
     server String,
     userId String,
     browser String,
     language FixedString(2),
     iab String,
     size String,
     minCPM Float32,
     gender FixedString(1),
     yob UInt16,
     url String)
    ENGINE = MergeTree()
        PARTITION BY toYYYYMMDD(event_datetime)
        ORDER BY (event_datetime,
                  ssp,
                  siteAppName,
                  country,
                  requestType,
                  conType,
                  deviceOs,
                  deviceType,
                  carrier,
                  siteAppId,
                  server,
                  userId,
                  browser,
                  language,
                  iab,
                  size,
                  minCPM,
                  gender,
                  yob,
                  url)
AS
SELECT DISTINCT id,
                event_datetime,
                ssp,
                siteAppName,
                toFixedString(extract(upper(country), '\\w\\w\\w'), 3) as country,
                cast(requestType as Enum8 (
                    'banner' = 1,
                    'exadsBanner' = 2,
                    'exadsPop' = 3,
                    'native' = 4,
                    'popunder' = 5))                                  as requestType,
                conType,
                deviceOs,
                deviceType,
                carrier,
                siteAppId,
                isApp,
                server,
                extract(userId, '\\w{3,}:.{8,}')                      as userId,
                browser,
                toFixedString(extract(lower(language), '\\w\\w'), 2)  as language,
                iab,
                size,
                minCPM,
                toFixedString(extract(gender, 'M|F'), 1)              as gender,
                yob,
                url
FROM BidRequests.euQueue;


CREATE MATERIALIZED VIEW IF NOT EXISTS BidRequests.usMediaPlanning
    TO BidRequests.MediaPlanning
AS
SELECT toDate(event_datetime)   AS event_date,
       ssp,
       siteAppName,
       country,
       requestType,
       conType,
       deviceOs,
       deviceType,
       carrier,
       siteAppId,
       isApp,
       browser,
       language,
       iab,
       size,
       avgState(minCPM)         AS minCPM,
       count()                  AS MTOTAL,
       maxState(event_datetime) AS lastUpdated
FROM BidRequests.usTraffic
GROUP BY event_date, ssp, siteAppName, country, requestType, conType, deviceOs, deviceType, carrier, siteAppId, isApp,
         browser, language, size, iab;


CREATE MATERIALIZED VIEW IF NOT EXISTS BidRequests.euMediaPlanning
    TO BidRequests.MediaPlanning
AS
SELECT toDate(event_datetime)   AS event_date,
       ssp,
       siteAppName,
       country,
       requestType,
       conType,
       deviceOs,
       deviceType,
       carrier,
       siteAppId,
       isApp,
       browser,
       language,
       iab,
       size,
       avgState(minCPM)         AS minCPM,
       count()                  AS MTOTAL,
       maxState(event_datetime) AS lastUpdated
FROM BidRequests.euTraffic
GROUP BY event_date, ssp, siteAppName, country, requestType, conType, deviceOs, deviceType, carrier, siteAppId, isApp,
         browser, language, size, iab;


CREATE MATERIALIZED VIEW IF NOT EXISTS BidRequests.usStats
    TO BidRequests.statsByDay
AS
SELECT toDate(event_datetime)   AS event_date,
       server,
       ssp,
       count()                  AS visits,
       maxState(event_datetime) AS lastUpdated
FROM BidRequests.usTraffic
GROUP BY event_date, server, ssp;


CREATE MATERIALIZED VIEW IF NOT EXISTS BidRequests.euStats
    TO BidRequests.statsByDay
AS
SELECT toDate(event_datetime)   AS event_date,
       server,
       ssp,
       count()                  AS visits,
       maxState(event_datetime) AS lastUpdated
FROM BidRequests.euTraffic
GROUP BY event_date, server, ssp;


CREATE MATERIALIZED VIEW IF NOT EXISTS DMP.usUsers
    TO DMP.users
AS
SELECT country,
       userId,
       conType,
       carrier,
       maxState(gender)         as gender,
       maxState(yob)            as yob,
       count()                  as visits,
       minState(event_datetime) as created,
       maxState(event_datetime) as lastUpdated
FROM BidRequests.usTraffic
WHERE requestType != 'popunder'
  AND notEmpty(country)
  AND notEmpty(userId)
GROUP BY country, userId, conType, carrier;

CREATE MATERIALIZED VIEW IF NOT EXISTS DMP.euUsers
    TO DMP.users
AS
SELECT country,
       userId,
       conType,
       carrier,
       maxState(gender)         as gender,
       maxState(yob)            as yob,
       count()                  as visits,
       minState(event_datetime) as created,
       maxState(event_datetime) as lastUpdated
FROM BidRequests.euTraffic
WHERE requestType != 'popunder'
  AND notEmpty(country)
  AND notEmpty(userId)
GROUP BY country, userId, conType, carrier;


CREATE MATERIALIZED VIEW IF NOT EXISTS DMP.usInterests
    TO DMP.interests
AS
SELECT country,
       arrayJoin(splitByChar(',', iab)) as iab,
       userId,
       count()                          as visits,
       maxState(event_datetime)         as lastUpdated
FROM BidRequests.usTraffic
WHERE notEmpty(iab)
  AND notEmpty(userId)
  and notEmpty(country)
GROUP BY country, iab, userId
HAVING iab like 'IAB_%';


CREATE MATERIALIZED VIEW IF NOT EXISTS DMP.euInterests
    TO DMP.interests
AS
SELECT country,
       arrayJoin(splitByChar(',', iab)) as iab,
       userId,
       count()                          as visits,
       maxState(event_datetime)         as lastUpdated
FROM BidRequests.euTraffic
WHERE notEmpty(iab)
  AND notEmpty(userId)
  and notEmpty(country)
GROUP BY country, iab, userId
HAVING iab like 'IAB_%';
Clone this wiki locally