CREATE DATABASE IF NOT EXISTS BidRequests;
CREATE TABLE IF NOT EXISTS BidRequests.MediaPlanning
(
event_date Date,
ssp LowCardinality(String),
siteAppName String,
country Enum16(
'' = 0,
'POL' = 1,
'FJI' = 2,
'UKR' = 3,
'NGA' = 4,
'GMB' = 5,
'GHA' = 6,
'TUR' = 7,
'SUR' = 8,
'JAM' = 9,
'MAF' = 10,
'CRI' = 11,
'TGO' = 12,
'URY' = 13,
'COL' = 14,
'VUT' = 15,
'FRO' = 16,
'VGB' = 17,
'GGY' = 18,
'NFK' = 19,
'KGZ' = 20,
'PRT' = 21,
'COG' = 22,
'TZA' = 23,
'ETH' = 24,
'CXR' = 25,
'COK' = 26,
'LBY' = 27,
'THA' = 28,
'KIR' = 29,
'LBR' = 30,
'TCA' = 31,
'JEY' = 32,
'PRI' = 33,
'BMU' = 34,
'GUY' = 35,
'VAT' = 36,
'KOR' = 37,
'MTQ' = 38,
'NER' = 39,
'RUS' = 40,
'CPV' = 41,
'SRB' = 42,
'ZAF' = 43,
'QAT' = 44,
'IRQ' = 45,
'MCO' = 46,
'BEL' = 47,
'IRL' = 48,
'IND' = 49,
'KAZ' = 50,
'CIV' = 51,
'AUS' = 52,
'AIA' = 53,
'KWT' = 54,
'JPN' = 55,
'SAU' = 56,
'NAM' = 57,
'AUT' = 58,
'DZA' = 59,
'IOT' = 60,
'ABW' = 61,
'SVK' = 62,
'KEN' = 63,
'SSD' = 64,
'LKA' = 65,
'ALB' = 66,
'EST' = 67,
'IDN' = 68,
'ARE' = 69,
'DJI' = 70,
'MWI' = 71,
'TTO' = 72,
'COM' = 73,
'MNE' = 74,
'PNG' = 75,
'BTN' = 76,
'LBN' = 77,
'DOM' = 78,
'LIE' = 79,
'CAN' = 80,
'SMR' = 81,
'MEX' = 82,
'PRY' = 83,
'ITA' = 84,
'PSE' = 85,
'TLS' = 86,
'SGS' = 87,
'NIU' = 88,
'TUN' = 89,
'HRV' = 90,
'BGR' = 91,
'EGY' = 92,
'HND' = 93,
'SLB' = 94,
'RWA' = 95,
'BLR' = 96,
'SPM' = 97,
'SLE' = 98,
'EUR' = 99,
'PAN' = 100,
'NIC' = 101,
'BGD' = 102,
'HKG' = 103,
'DNK' = 104,
'JOR' = 105,
'GNQ' = 106,
'GRC' = 107,
'CCK' = 108,
'NOR' = 109,
'AGO' = 110,
'ZWE' = 111,
'CMR' = 112,
'GRD' = 113,
'VNM' = 114,
'ISL' = 115,
'CHL' = 116,
'SOM' = 117,
'BOL' = 118,
'SYC' = 119,
'BRB' = 120,
'ERI' = 121,
'CUB' = 122,
'SYR' = 123,
'SWZ' = 124,
'BRN' = 125,
'BWA' = 126,
'GAB' = 127,
'PHL' = 128,
'BES' = 129,
'TKL' = 130,
'BRA' = 131,
'AZE' = 132,
'ATA' = 133,
'CUW' = 134,
'BEN' = 135,
'LVA' = 136,
'HUN' = 137,
'IRN' = 138,
'PLW' = 139,
'WLF' = 140,
'SJM' = 141,
'ISR' = 142,
'SVN' = 143,
'SDN' = 144,
'TON' = 145,
'UZB' = 146,
'GRL' = 147,
'FLK' = 148,
'GEO' = 149,
'SXM' = 150,
'VEN' = 151,
'DEU' = 152,
'MDG' = 153,
'VCT' = 154,
'SGP' = 155,
'WSM' = 156,
'YEM' = 157,
'BLZ' = 158,
'MAR' = 159,
'LTU' = 160,
'MDV' = 161,
'ZMB' = 162,
'MAC' = 163,
'BIH' = 164,
'NLD' = 165,
'NPL' = 166,
'MOZ' = 167,
'MSR' = 168,
'ROU' = 169,
'GUM' = 170,
'GLP' = 171,
'MYT' = 172,
'GIN' = 173,
'ARM' = 174,
'BFA' = 175,
'GUF' = 176,
'TCD' = 177,
'MRT' = 178,
'IMN' = 179,
'MYS' = 180,
'GIB' = 181,
'BHS' = 182,
'LUX' = 183,
'MNP' = 184,
'FRA' = 185,
'CAF' = 186,
'PRK' = 187,
'PER' = 188,
'VIR' = 189,
'OMN' = 190,
'MLT' = 191,
'BDI' = 192,
'MUS' = 193,
'ESP' = 194,
'HTI' = 195,
'CZE' = 196,
'KNA' = 197,
'GNB' = 198,
'CHE' = 199,
'DMA' = 200,
'MLI' = 201,
'FIN' = 202,
'TJK' = 203,
'ALA' = 204,
'CHN' = 205,
'SWE' = 206,
'TWN' = 207,
'AFG' = 208,
'TKM' = 209,
'LAO' = 210,
'MMR' = 211,
'NRU' = 212,
'SEN' = 213,
'LSO' = 214,
'PCN' = 215,
'MHL' = 216,
'ATG' = 217,
'UGA' = 218,
'SHN' = 219,
'ECU' = 220,
'ARG' = 221,
'COD' = 222,
'PYF' = 223,
'MNG' = 224,
'NCL' = 225,
'AND' = 226,
'GBR' = 227,
'MKD' = 228,
'BHR' = 229,
'NZL' = 230,
'USA' = 231,
'STP' = 232,
'GTM' = 233,
'PAK' = 234,
'TUV' = 235,
'CYM' = 236,
'FSM' = 237,
'ASM' = 238,
'KHM' = 239,
'REU' = 240,
'MDA' = 241,
'LCA' = 242,
'CYP' = 243,
'SLV' = 244,
'BLM' = 245
),
requestType Enum8 (
'banner' = 1,
'exadsBanner' = 2,
'exadsPop' = 3,
'native' = 4,
'popunder' = 5),
conType UInt8,
deviceOs Enum8(
'' = 1,
'unix' = 2,
'haiku' = 3,
'suse' = 4,
'freebsd' = 5,
'pclinuxos' = 6,
'mandriva' = 7,
'opensolaris' = 8,
'chromium os' = 9,
'qnx' = 10,
'opensuse' = 11,
'mageia' = 12,
'risc os' = 13,
'nintendo' = 14,
'aix' = 15,
'centos' = 16,
'symbian' = 17,
'android' = 18,
'debian' = 19,
'solaris' = 20,
'fedora' = 21,
'sailfish' = 22,
'dragonfly' = 23,
'firefox os' = 24,
'playstation' = 25,
'series40' = 26,
'rim tablet os' = 27,
'windows phone' = 28,
'gnu' = 29,
'slackware' = 30,
'ios' = 31,
'tizen' = 32,
'mint' = 33,
'joli' = 34,
'openbsd' = 35,
'netbsd' = 36,
'hurd' = 37,
'blackberry' = 38,
'gentoo' = 39,
'bada' = 40,
'os/2' = 41,
'amigaos' = 42,
'mac os' = 43,
'zenwalk' = 44,
'beos' = 45,
'meego' = 46,
'windows' = 47,
'webos' = 48,
'kubuntu' = 49,
'linux' = 50,
'windows phone os' = 51,
'redhat' = 52,
'ubuntu' = 53
),
deviceType UInt8,
carrier LowCardinality(String),
siteAppId String,
isApp UInt8,
browser Enum16(
'' = 1,
'silk' = 2,
'amaya' = 3,
'Edge' = 4,
'Camino' = 5,
'samsung browser' = 6,
'Opera' = 7,
'Arora' = 8,
'maxthon' = 9,
'polaris' = 10,
'vivaldi' = 11,
'Iron' = 12,
'Line' = 13,
'PaleMoon' = 14,
'Netfront' = 15,
'Mosaic' = 16,
'Maemo Browser' = 17,
'Midori' = 18,
'Phoenix' = 19,
'Doris' = 20,
'Chromium' = 21,
'2345Explorer' = 22,
'GoBrowser' = 23,
'Fennec' = 24,
'Iridium' = 25,
'TizenBrowser' = 26,
'NokiaBrowser' = 27,
'QQBrowser' = 28,
'Webkit' = 29,
'Chrome WebView' = 30,
'Lunascape' = 31,
'iemobile' = 32,
'rekonq' = 33,
'Skyfire' = 34,
'Palemoon' = 35,
'Yandex' = 36,
'slim' = 37,
'mozilla' = 38,
'Epiphany' = 39,
'Slim' = 40,
'qqbrowser' = 41,
'QQ' = 42,
'Iceape' = 43,
'K-Meleon' = 44,
'LBBROWSER' = 45,
'ie' = 46,
'ps4 web browser' = 47,
'Mobile Safari' = 48,
'Vivaldi' = 49,
'Silk' = 50,
'MIUI Browser' = 51,
'Comodo Dragon' = 52,
'PhantomJS' = 53,
'MetaSr' = 54,
'midori' = 55,
'safari' = 56,
'Lynx' = 57,
'icecat' = 58,
'Opera mini' = 59,
'chrome WebView' = 60,
'Bolt' = 61,
'Opera Coast' = 62,
'sLIm' = 63,
'webkit' = 64,
'k-meleon' = 65,
'Basilisk' = 66,
'Android Browser' = 67,
'baidu' = 68,
'KHTML' = 69,
'OPERA' = 70,
'Puffin' = 71,
'Brave' = 72,
'Sleipnir' = 73,
'IE' = 74,
'iceweasel' = 75,
'Flock' = 76,
'iCab' = 77,
'Quark' = 78,
'Minimo' = 79,
'Opera T' = 80,
'Chimera' = 81,
'NetSurf' = 82,
'Avant ' = 83,
'MAXTHON' = 84,
'jasmine' = 85,
'SLIM' = 86,
'Kindle' = 87,
'Netscape' = 88,
'BIDUBrowser' = 89,
'RockMelt' = 90,
'links' = 91,
'BOLT' = 92,
'Samsung Browser' = 93,
'NTENTBrowser' = 94,
'Safari' = 95,
'Waterfox' = 96,
'WeChat' = 97,
'IceWeasel' = 98,
'Iceweasel' = 99,
'conkeror' = 100,
'Dillo' = 101,
'w3m' = 102,
'Polaris' = 103,
'IceCat' = 104,
'Firebird' = 105,
'Swiftfox' = 106,
'QQBrowserLite' = 107,
'GSA' = 108,
'Konqueror' = 109,
'IceDragon' = 110,
'Opera Mi' = 111,
'Chrome Headless' = 112,
'Jasmine' = 113,
'Firefox' = 114,
'Oculus Browser' = 115,
'Blazer' = 116,
'Dolphin' = 117,
'Opera Touch' = 118,
'Firefox Focus' = 119,
'Opera Tablet' = 120,
'Maxthon' = 121,
'WebKit' = 122,
'iridium' = 123,
'Seamonkey' = 124,
'SeaMonkey' = 125,
'NetFront' = 126,
'UCBrowser' = 127,
'OmniWeb' = 128,
'firefox' = 129,
'MobileSafari' = 130,
'Mozilla' = 131,
'Links' = 132,
'Baidu' = 133,
'Facebook' = 134,
'Conkeror' = 135,
'Chrome' = 136,
'IEMobile' = 137,
'Opera Mobi' = 138,
'OviBrowser' = 139,
'chrome' = 140,
'Opera Mini' = 141
),
language Enum16(
'' = 0,
'he' = 1,
'eu' = 2,
'ni' = 3,
'mo' = 4,
'cy' = 5,
'nb' = 6,
'ii' = 7,
'cr' = 8,
'md' = 9,
'as' = 10,
'kh' = 11,
'bu' = 12,
'el' = 13,
'bg' = 14,
'aa' = 15,
'fj' = 16,
'fa' = 17,
'jv' = 18,
'ck' = 19,
'tt' = 20,
'us' = 21,
'qu' = 22,
'xh' = 23,
'rs' = 24,
'yo' = 25,
'pr' = 26,
'st' = 27,
'ra' = 28,
'vl' = 29,
'sm' = 30,
'pk' = 31,
'ua' = 32,
'sk' = 33,
'ug' = 34,
'tk' = 35,
'ru' = 36,
'sr' = 37,
'ty' = 38,
'pt' = 39,
'yi' = 40,
'tr' = 41,
'sy' = 42,
'yb' = 43,
'al' = 44,
'kw' = 45,
'jp' = 46,
'nv' = 47,
'mp' = 48,
'ag' = 49,
'hz' = 50,
'ba' = 51,
'ji' = 52,
'nd' = 53,
'io' = 54,
'bs' = 55,
'kn' = 56,
'le' = 57,
'ex' = 58,
'oc' = 59,
'au' = 60,
'es' = 61,
'ke' = 62,
'ln' = 63,
'bx' = 64,
'fu' = 65,
'mi' = 66,
'id' = 67,
'no' = 68,
'fr' = 69,
'ay' = 70,
'hd' = 71,
'et' = 72,
'kb' = 73,
'li' = 74,
'gu' = 75,
'mn' = 76,
'ds' = 77,
'cs' = 78,
'me' = 79,
'ar' = 80,
'fy' = 81,
'lb' = 82,
'ki' = 83,
'ca' = 84,
'dj' = 85,
'gl' = 86,
'nq' = 87,
'bm' = 88,
'ef' = 89,
'ak' = 90,
'da' = 91,
'jw' = 92,
'tu' = 93,
'ur' = 94,
'ps' = 95,
'zh' = 96,
'su' = 97,
'uk' = 98,
'zz' = 99,
'tl' = 100,
'sg' = 101,
'pa' = 102,
'sl' = 103,
'tg' = 104,
'wa' = 105,
'pl' = 106,
'ta' = 107,
'rm' = 108,
'sa' = 109,
'xo' = 110,
'ss' = 111,
'ts' = 112,
'wu' = 113,
'am' = 114,
'ff' = 115,
'ga' = 116,
'iw' = 117,
'mz' = 118,
'cg' = 119,
'af' = 120,
'lv' = 121,
'ek' = 122,
'in' = 123,
'ne' = 124,
'ko' = 125,
'br' = 126,
'ld' = 127,
'hi' = 128,
'at' = 129,
'lo' = 130,
'by' = 131,
'mh' = 132,
'du' = 133,
'gs' = 134,
'nn' = 135,
'ie' = 136,
'az' = 137,
'ka' = 138,
'ew' = 139,
'gv' = 140,
'nk' = 141,
'mm' = 142,
'mf' = 143,
'jm' = 144,
'la' = 145,
'kj' = 146,
'mt' = 147,
'nr' = 148,
'en' = 149,
'be' = 150,
'ac' = 151,
'bn' = 152,
'ks' = 153,
'ee' = 154,
'hu' = 155,
'gd' = 156,
'ir' = 157,
'ny' = 158,
'tv' = 159,
'xa' = 160,
'uz' = 161,
'sv' = 162,
'xx' = 163,
'vn' = 164,
'to' = 165,
'zy' = 166,
'sd' = 167,
'so' = 168,
've' = 169,
'po' = 170,
'si' = 171,
'rn' = 172,
'un' = 173,
'wo' = 174,
'ti' = 175,
'sb' = 176,
'rw' = 177,
'sp' = 178,
'tp' = 179,
'qz' = 180,
'an' = 181,
'hs' = 182,
'ku' = 183,
'bh' = 184,
'it' = 185,
'gb' = 186,
'my' = 187,
'co' = 188,
'mr' = 189,
'do' = 190,
'os' = 191,
'lu' = 192,
'eh' = 193,
'cv' = 194,
'lg' = 195,
'kl' = 196,
'aw' = 197,
'bz' = 198,
'ha' = 199,
'mk' = 200,
'dv' = 201,
'nm' = 202,
'om' = 203,
'lk' = 204,
'ia' = 205,
'ml' = 206,
'cz' = 207,
'na' = 208,
'mg' = 209,
'dz' = 210,
'hm' = 211,
'kk' = 212,
'mu' = 213,
'cc' = 214,
'gn' = 215,
'ns' = 216,
'bd' = 217,
'eo' = 218,
'ky' = 219,
'fi' = 220,
'ab' = 221,
'bo' = 222,
'kr' = 223,
'ht' = 224,
'ai' = 225,
'ch' = 226,
'is' = 227,
'ge' = 228,
'tw' = 229,
'za' = 230,
'sw' = 231,
'vo' = 232,
'se' = 233,
'tn' = 234,
'te' = 235,
'sn' = 236,
'yu' = 237,
'sh' = 238,
'zu' = 239,
'ro' = 240,
'ud' = 241,
'vi' = 242,
'th' = 243,
'sc' = 244,
'qp' = 245,
'sq' = 246,
'tz' = 247,
'zg' = 248,
'ya' = 249,
'hr' = 250,
'bi' = 251,
'eb' = 252,
'iu' = 253,
'de' = 254,
'mx' = 255,
'nu' = 256,
'ms' = 257,
'ce' = 258,
'hy' = 259,
'ad' = 260,
'or' = 261,
'fo' = 262,
'lt' = 263,
'ma' = 264,
'il' = 265,
'ng' = 266,
'km' = 267,
'av' = 268,
'kf' = 269,
'ok' = 270,
'ja' = 271,
'ig' = 272,
'nl' = 273
),
iab String,
size LowCardinality(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 Enum8(
'' = 0,
'endpoint1' = 1,
'endpoint10' = 10,
'endpoint11' = 11,
'endpoint12' = 12,
'endpoint2' = 2,
'endpoint3' = 3,
'endpoint4' = 4,
'endpoint5' = 5,
'endpoint6' = 6,
'endpoint7' = 7,
'endpoint8' = 8,
'endpoint9' = 9,
'eu1' = 31,
'eu2' = 32,
'eu3' = 33
),
ssp LowCardinality(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 Enum16(
'' = 0,
'POL' = 1,
'FJI' = 2,
'UKR' = 3,
'NGA' = 4,
'GMB' = 5,
'GHA' = 6,
'TUR' = 7,
'SUR' = 8,
'JAM' = 9,
'MAF' = 10,
'CRI' = 11,
'TGO' = 12,
'URY' = 13,
'COL' = 14,
'VUT' = 15,
'FRO' = 16,
'VGB' = 17,
'GGY' = 18,
'NFK' = 19,
'KGZ' = 20,
'PRT' = 21,
'COG' = 22,
'TZA' = 23,
'ETH' = 24,
'CXR' = 25,
'COK' = 26,
'LBY' = 27,
'THA' = 28,
'KIR' = 29,
'LBR' = 30,
'TCA' = 31,
'JEY' = 32,
'PRI' = 33,
'BMU' = 34,
'GUY' = 35,
'VAT' = 36,
'KOR' = 37,
'MTQ' = 38,
'NER' = 39,
'RUS' = 40,
'CPV' = 41,
'SRB' = 42,
'ZAF' = 43,
'QAT' = 44,
'IRQ' = 45,
'MCO' = 46,
'BEL' = 47,
'IRL' = 48,
'IND' = 49,
'KAZ' = 50,
'CIV' = 51,
'AUS' = 52,
'AIA' = 53,
'KWT' = 54,
'JPN' = 55,
'SAU' = 56,
'NAM' = 57,
'AUT' = 58,
'DZA' = 59,
'IOT' = 60,
'ABW' = 61,
'SVK' = 62,
'KEN' = 63,
'SSD' = 64,
'LKA' = 65,
'ALB' = 66,
'EST' = 67,
'IDN' = 68,
'ARE' = 69,
'DJI' = 70,
'MWI' = 71,
'TTO' = 72,
'COM' = 73,
'MNE' = 74,
'PNG' = 75,
'BTN' = 76,
'LBN' = 77,
'DOM' = 78,
'LIE' = 79,
'CAN' = 80,
'SMR' = 81,
'MEX' = 82,
'PRY' = 83,
'ITA' = 84,
'PSE' = 85,
'TLS' = 86,
'SGS' = 87,
'NIU' = 88,
'TUN' = 89,
'HRV' = 90,
'BGR' = 91,
'EGY' = 92,
'HND' = 93,
'SLB' = 94,
'RWA' = 95,
'BLR' = 96,
'SPM' = 97,
'SLE' = 98,
'EUR' = 99,
'PAN' = 100,
'NIC' = 101,
'BGD' = 102,
'HKG' = 103,
'DNK' = 104,
'JOR' = 105,
'GNQ' = 106,
'GRC' = 107,
'CCK' = 108,
'NOR' = 109,
'AGO' = 110,
'ZWE' = 111,
'CMR' = 112,
'GRD' = 113,
'VNM' = 114,
'ISL' = 115,
'CHL' = 116,
'SOM' = 117,
'BOL' = 118,
'SYC' = 119,
'BRB' = 120,
'ERI' = 121,
'CUB' = 122,
'SYR' = 123,
'SWZ' = 124,
'BRN' = 125,
'BWA' = 126,
'GAB' = 127,
'PHL' = 128,
'BES' = 129,
'TKL' = 130,
'BRA' = 131,
'AZE' = 132,
'ATA' = 133,
'CUW' = 134,
'BEN' = 135,
'LVA' = 136,
'HUN' = 137,
'IRN' = 138,
'PLW' = 139,
'WLF' = 140,
'SJM' = 141,
'ISR' = 142,
'SVN' = 143,
'SDN' = 144,
'TON' = 145,
'UZB' = 146,
'GRL' = 147,
'FLK' = 148,
'GEO' = 149,
'SXM' = 150,
'VEN' = 151,
'DEU' = 152,
'MDG' = 153,
'VCT' = 154,
'SGP' = 155,
'WSM' = 156,
'YEM' = 157,
'BLZ' = 158,
'MAR' = 159,
'LTU' = 160,
'MDV' = 161,
'ZMB' = 162,
'MAC' = 163,
'BIH' = 164,
'NLD' = 165,
'NPL' = 166,
'MOZ' = 167,
'MSR' = 168,
'ROU' = 169,
'GUM' = 170,
'GLP' = 171,
'MYT' = 172,
'GIN' = 173,
'ARM' = 174,
'BFA' = 175,
'GUF' = 176,
'TCD' = 177,
'MRT' = 178,
'IMN' = 179,
'MYS' = 180,
'GIB' = 181,
'BHS' = 182,
'LUX' = 183,
'MNP' = 184,
'FRA' = 185,
'CAF' = 186,
'PRK' = 187,
'PER' = 188,
'VIR' = 189,
'OMN' = 190,
'MLT' = 191,
'BDI' = 192,
'MUS' = 193,
'ESP' = 194,
'HTI' = 195,
'CZE' = 196,
'KNA' = 197,
'GNB' = 198,
'CHE' = 199,
'DMA' = 200,
'MLI' = 201,
'FIN' = 202,
'TJK' = 203,
'ALA' = 204,
'CHN' = 205,
'SWE' = 206,
'TWN' = 207,
'AFG' = 208,
'TKM' = 209,
'LAO' = 210,
'MMR' = 211,
'NRU' = 212,
'SEN' = 213,
'LSO' = 214,
'PCN' = 215,
'MHL' = 216,
'ATG' = 217,
'UGA' = 218,
'SHN' = 219,
'ECU' = 220,
'ARG' = 221,
'COD' = 222,
'PYF' = 223,
'MNG' = 224,
'NCL' = 225,
'AND' = 226,
'GBR' = 227,
'MKD' = 228,
'BHR' = 229,
'NZL' = 230,
'USA' = 231,
'STP' = 232,
'GTM' = 233,
'PAK' = 234,
'TUV' = 235,
'CYM' = 236,
'FSM' = 237,
'ASM' = 238,
'KHM' = 239,
'REU' = 240,
'MDA' = 241,
'LCA' = 242,
'CYP' = 243,
'SLV' = 244,
'BLM' = 245
),
userId String,
conType UInt8,
carrier LowCardinality(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 Enum16(
'' = 0,
'POL' = 1,
'FJI' = 2,
'UKR' = 3,
'NGA' = 4,
'GMB' = 5,
'GHA' = 6,
'TUR' = 7,
'SUR' = 8,
'JAM' = 9,
'MAF' = 10,
'CRI' = 11,
'TGO' = 12,
'URY' = 13,
'COL' = 14,
'VUT' = 15,
'FRO' = 16,
'VGB' = 17,
'GGY' = 18,
'NFK' = 19,
'KGZ' = 20,
'PRT' = 21,
'COG' = 22,
'TZA' = 23,
'ETH' = 24,
'CXR' = 25,
'COK' = 26,
'LBY' = 27,
'THA' = 28,
'KIR' = 29,
'LBR' = 30,
'TCA' = 31,
'JEY' = 32,
'PRI' = 33,
'BMU' = 34,
'GUY' = 35,
'VAT' = 36,
'KOR' = 37,
'MTQ' = 38,
'NER' = 39,
'RUS' = 40,
'CPV' = 41,
'SRB' = 42,
'ZAF' = 43,
'QAT' = 44,
'IRQ' = 45,
'MCO' = 46,
'BEL' = 47,
'IRL' = 48,
'IND' = 49,
'KAZ' = 50,
'CIV' = 51,
'AUS' = 52,
'AIA' = 53,
'KWT' = 54,
'JPN' = 55,
'SAU' = 56,
'NAM' = 57,
'AUT' = 58,
'DZA' = 59,
'IOT' = 60,
'ABW' = 61,
'SVK' = 62,
'KEN' = 63,
'SSD' = 64,
'LKA' = 65,
'ALB' = 66,
'EST' = 67,
'IDN' = 68,
'ARE' = 69,
'DJI' = 70,
'MWI' = 71,
'TTO' = 72,
'COM' = 73,
'MNE' = 74,
'PNG' = 75,
'BTN' = 76,
'LBN' = 77,
'DOM' = 78,
'LIE' = 79,
'CAN' = 80,
'SMR' = 81,
'MEX' = 82,
'PRY' = 83,
'ITA' = 84,
'PSE' = 85,
'TLS' = 86,
'SGS' = 87,
'NIU' = 88,
'TUN' = 89,
'HRV' = 90,
'BGR' = 91,
'EGY' = 92,
'HND' = 93,
'SLB' = 94,
'RWA' = 95,
'BLR' = 96,
'SPM' = 97,
'SLE' = 98,
'EUR' = 99,
'PAN' = 100,
'NIC' = 101,
'BGD' = 102,
'HKG' = 103,
'DNK' = 104,
'JOR' = 105,
'GNQ' = 106,
'GRC' = 107,
'CCK' = 108,
'NOR' = 109,
'AGO' = 110,
'ZWE' = 111,
'CMR' = 112,
'GRD' = 113,
'VNM' = 114,
'ISL' = 115,
'CHL' = 116,
'SOM' = 117,
'BOL' = 118,
'SYC' = 119,
'BRB' = 120,
'ERI' = 121,
'CUB' = 122,
'SYR' = 123,
'SWZ' = 124,
'BRN' = 125,
'BWA' = 126,
'GAB' = 127,
'PHL' = 128,
'BES' = 129,
'TKL' = 130,
'BRA' = 131,
'AZE' = 132,
'ATA' = 133,
'CUW' = 134,
'BEN' = 135,
'LVA' = 136,
'HUN' = 137,
'IRN' = 138,
'PLW' = 139,
'WLF' = 140,
'SJM' = 141,
'ISR' = 142,
'SVN' = 143,
'SDN' = 144,
'TON' = 145,
'UZB' = 146,
'GRL' = 147,
'FLK' = 148,
'GEO' = 149,
'SXM' = 150,
'VEN' = 151,
'DEU' = 152,
'MDG' = 153,
'VCT' = 154,
'SGP' = 155,
'WSM' = 156,
'YEM' = 157,
'BLZ' = 158,
'MAR' = 159,
'LTU' = 160,
'MDV' = 161,
'ZMB' = 162,
'MAC' = 163,
'BIH' = 164,
'NLD' = 165,
'NPL' = 166,
'MOZ' = 167,
'MSR' = 168,
'ROU' = 169,
'GUM' = 170,
'GLP' = 171,
'MYT' = 172,
'GIN' = 173,
'ARM' = 174,
'BFA' = 175,
'GUF' = 176,
'TCD' = 177,
'MRT' = 178,
'IMN' = 179,
'MYS' = 180,
'GIB' = 181,
'BHS' = 182,
'LUX' = 183,
'MNP' = 184,
'FRA' = 185,
'CAF' = 186,
'PRK' = 187,
'PER' = 188,
'VIR' = 189,
'OMN' = 190,
'MLT' = 191,
'BDI' = 192,
'MUS' = 193,
'ESP' = 194,
'HTI' = 195,
'CZE' = 196,
'KNA' = 197,
'GNB' = 198,
'CHE' = 199,
'DMA' = 200,
'MLI' = 201,
'FIN' = 202,
'TJK' = 203,
'ALA' = 204,
'CHN' = 205,
'SWE' = 206,
'TWN' = 207,
'AFG' = 208,
'TKM' = 209,
'LAO' = 210,
'MMR' = 211,
'NRU' = 212,
'SEN' = 213,
'LSO' = 214,
'PCN' = 215,
'MHL' = 216,
'ATG' = 217,
'UGA' = 218,
'SHN' = 219,
'ECU' = 220,
'ARG' = 221,
'COD' = 222,
'PYF' = 223,
'MNG' = 224,
'NCL' = 225,
'AND' = 226,
'GBR' = 227,
'MKD' = 228,
'BHR' = 229,
'NZL' = 230,
'USA' = 231,
'STP' = 232,
'GTM' = 233,
'PAK' = 234,
'TUV' = 235,
'CYM' = 236,
'FSM' = 237,
'ASM' = 238,
'KHM' = 239,
'REU' = 240,
'MDA' = 241,
'LCA' = 242,
'CYP' = 243,
'SLV' = 244,
'BLM' = 245
),
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 FixedString(1),
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 FixedString(1),
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 LowCardinality(String),
siteAppName String,
country Enum16(
'' = 0,
'POL' = 1,
'FJI' = 2,
'UKR' = 3,
'NGA' = 4,
'GMB' = 5,
'GHA' = 6,
'TUR' = 7,
'SUR' = 8,
'JAM' = 9,
'MAF' = 10,
'CRI' = 11,
'TGO' = 12,
'URY' = 13,
'COL' = 14,
'VUT' = 15,
'FRO' = 16,
'VGB' = 17,
'GGY' = 18,
'NFK' = 19,
'KGZ' = 20,
'PRT' = 21,
'COG' = 22,
'TZA' = 23,
'ETH' = 24,
'CXR' = 25,
'COK' = 26,
'LBY' = 27,
'THA' = 28,
'KIR' = 29,
'LBR' = 30,
'TCA' = 31,
'JEY' = 32,
'PRI' = 33,
'BMU' = 34,
'GUY' = 35,
'VAT' = 36,
'KOR' = 37,
'MTQ' = 38,
'NER' = 39,
'RUS' = 40,
'CPV' = 41,
'SRB' = 42,
'ZAF' = 43,
'QAT' = 44,
'IRQ' = 45,
'MCO' = 46,
'BEL' = 47,
'IRL' = 48,
'IND' = 49,
'KAZ' = 50,
'CIV' = 51,
'AUS' = 52,
'AIA' = 53,
'KWT' = 54,
'JPN' = 55,
'SAU' = 56,
'NAM' = 57,
'AUT' = 58,
'DZA' = 59,
'IOT' = 60,
'ABW' = 61,
'SVK' = 62,
'KEN' = 63,
'SSD' = 64,
'LKA' = 65,
'ALB' = 66,
'EST' = 67,
'IDN' = 68,
'ARE' = 69,
'DJI' = 70,
'MWI' = 71,
'TTO' = 72,
'COM' = 73,
'MNE' = 74,
'PNG' = 75,
'BTN' = 76,
'LBN' = 77,
'DOM' = 78,
'LIE' = 79,
'CAN' = 80,
'SMR' = 81,
'MEX' = 82,
'PRY' = 83,
'ITA' = 84,
'PSE' = 85,
'TLS' = 86,
'SGS' = 87,
'NIU' = 88,
'TUN' = 89,
'HRV' = 90,
'BGR' = 91,
'EGY' = 92,
'HND' = 93,
'SLB' = 94,
'RWA' = 95,
'BLR' = 96,
'SPM' = 97,
'SLE' = 98,
'EUR' = 99,
'PAN' = 100,
'NIC' = 101,
'BGD' = 102,
'HKG' = 103,
'DNK' = 104,
'JOR' = 105,
'GNQ' = 106,
'GRC' = 107,
'CCK' = 108,
'NOR' = 109,
'AGO' = 110,
'ZWE' = 111,
'CMR' = 112,
'GRD' = 113,
'VNM' = 114,
'ISL' = 115,
'CHL' = 116,
'SOM' = 117,
'BOL' = 118,
'SYC' = 119,
'BRB' = 120,
'ERI' = 121,
'CUB' = 122,
'SYR' = 123,
'SWZ' = 124,
'BRN' = 125,
'BWA' = 126,
'GAB' = 127,
'PHL' = 128,
'BES' = 129,
'TKL' = 130,
'BRA' = 131,
'AZE' = 132,
'ATA' = 133,
'CUW' = 134,
'BEN' = 135,
'LVA' = 136,
'HUN' = 137,
'IRN' = 138,
'PLW' = 139,
'WLF' = 140,
'SJM' = 141,
'ISR' = 142,
'SVN' = 143,
'SDN' = 144,
'TON' = 145,
'UZB' = 146,
'GRL' = 147,
'FLK' = 148,
'GEO' = 149,
'SXM' = 150,
'VEN' = 151,
'DEU' = 152,
'MDG' = 153,
'VCT' = 154,
'SGP' = 155,
'WSM' = 156,
'YEM' = 157,
'BLZ' = 158,
'MAR' = 159,
'LTU' = 160,
'MDV' = 161,
'ZMB' = 162,
'MAC' = 163,
'BIH' = 164,
'NLD' = 165,
'NPL' = 166,
'MOZ' = 167,
'MSR' = 168,
'ROU' = 169,
'GUM' = 170,
'GLP' = 171,
'MYT' = 172,
'GIN' = 173,
'ARM' = 174,
'BFA' = 175,
'GUF' = 176,
'TCD' = 177,
'MRT' = 178,
'IMN' = 179,
'MYS' = 180,
'GIB' = 181,
'BHS' = 182,
'LUX' = 183,
'MNP' = 184,
'FRA' = 185,
'CAF' = 186,
'PRK' = 187,
'PER' = 188,
'VIR' = 189,
'OMN' = 190,
'MLT' = 191,
'BDI' = 192,
'MUS' = 193,
'ESP' = 194,
'HTI' = 195,
'CZE' = 196,
'KNA' = 197,
'GNB' = 198,
'CHE' = 199,
'DMA' = 200,
'MLI' = 201,
'FIN' = 202,
'TJK' = 203,
'ALA' = 204,
'CHN' = 205,
'SWE' = 206,
'TWN' = 207,
'AFG' = 208,
'TKM' = 209,
'LAO' = 210,
'MMR' = 211,
'NRU' = 212,
'SEN' = 213,
'LSO' = 214,
'PCN' = 215,
'MHL' = 216,
'ATG' = 217,
'UGA' = 218,
'SHN' = 219,
'ECU' = 220,
'ARG' = 221,
'COD' = 222,
'PYF' = 223,
'MNG' = 224,
'NCL' = 225,
'AND' = 226,
'GBR' = 227,
'MKD' = 228,
'BHR' = 229,
'NZL' = 230,
'USA' = 231,
'STP' = 232,
'GTM' = 233,
'PAK' = 234,
'TUV' = 235,
'CYM' = 236,
'FSM' = 237,
'ASM' = 238,
'KHM' = 239,
'REU' = 240,
'MDA' = 241,
'LCA' = 242,
'CYP' = 243,
'SLV' = 244,
'BLM' = 245
),
requestType Enum8 (
'banner' = 1,
'exadsBanner' = 2,
'exadsPop' = 3,
'native' = 4,
'popunder' = 5),
conType UInt8,
deviceOs Enum8(
'' = 1,
'unix' = 2,
'haiku' = 3,
'suse' = 4,
'freebsd' = 5,
'pclinuxos' = 6,
'mandriva' = 7,
'opensolaris' = 8,
'chromium os' = 9,
'qnx' = 10,
'opensuse' = 11,
'mageia' = 12,
'risc os' = 13,
'nintendo' = 14,
'aix' = 15,
'centos' = 16,
'symbian' = 17,
'android' = 18,
'debian' = 19,
'solaris' = 20,
'fedora' = 21,
'sailfish' = 22,
'dragonfly' = 23,
'firefox os' = 24,
'playstation' = 25,
'series40' = 26,
'rim tablet os' = 27,
'windows phone' = 28,
'gnu' = 29,
'slackware' = 30,
'ios' = 31,
'tizen' = 32,
'mint' = 33,
'joli' = 34,
'openbsd' = 35,
'netbsd' = 36,
'hurd' = 37,
'blackberry' = 38,
'gentoo' = 39,
'bada' = 40,
'os/2' = 41,
'amigaos' = 42,
'mac os' = 43,
'zenwalk' = 44,
'beos' = 45,
'meego' = 46,
'windows' = 47,
'webos' = 48,
'kubuntu' = 49,
'linux' = 50,
'windows phone os' = 51,
'redhat' = 52,
'ubuntu' = 53
),
deviceType UInt8,
carrier LowCardinality(String),
siteAppId String,
isApp UInt8,
server Enum8(
'' = 0,
'endpoint1' = 1,
'endpoint10' = 10,
'endpoint11' = 11,
'endpoint12' = 12,
'endpoint2' = 2,
'endpoint3' = 3,
'endpoint4' = 4,
'endpoint5' = 5,
'endpoint6' = 6,
'endpoint7' = 7,
'endpoint8' = 8,
'endpoint9' = 9,
'eu1' = 31,
'eu2' = 32,
'eu3' = 33
),
userId String,
browser Enum16(
'' = 1,
'silk' = 2,
'amaya' = 3,
'Edge' = 4,
'Camino' = 5,
'samsung browser' = 6,
'Opera' = 7,
'Arora' = 8,
'maxthon' = 9,
'polaris' = 10,
'vivaldi' = 11,
'Iron' = 12,
'Line' = 13,
'PaleMoon' = 14,
'Netfront' = 15,
'Mosaic' = 16,
'Maemo Browser' = 17,
'Midori' = 18,
'Phoenix' = 19,
'Doris' = 20,
'Chromium' = 21,
'2345Explorer' = 22,
'GoBrowser' = 23,
'Fennec' = 24,
'Iridium' = 25,
'TizenBrowser' = 26,
'NokiaBrowser' = 27,
'QQBrowser' = 28,
'Webkit' = 29,
'Chrome WebView' = 30,
'Lunascape' = 31,
'iemobile' = 32,
'rekonq' = 33,
'Skyfire' = 34,
'Palemoon' = 35,
'Yandex' = 36,
'slim' = 37,
'mozilla' = 38,
'Epiphany' = 39,
'Slim' = 40,
'qqbrowser' = 41,
'QQ' = 42,
'Iceape' = 43,
'K-Meleon' = 44,
'LBBROWSER' = 45,
'ie' = 46,
'ps4 web browser' = 47,
'Mobile Safari' = 48,
'Vivaldi' = 49,
'Silk' = 50,
'MIUI Browser' = 51,
'Comodo Dragon' = 52,
'PhantomJS' = 53,
'MetaSr' = 54,
'midori' = 55,
'safari' = 56,
'Lynx' = 57,
'icecat' = 58,
'Opera mini' = 59,
'chrome WebView' = 60,
'Bolt' = 61,
'Opera Coast' = 62,
'sLIm' = 63,
'webkit' = 64,
'k-meleon' = 65,
'Basilisk' = 66,
'Android Browser' = 67,
'baidu' = 68,
'KHTML' = 69,
'OPERA' = 70,
'Puffin' = 71,
'Brave' = 72,
'Sleipnir' = 73,
'IE' = 74,
'iceweasel' = 75,
'Flock' = 76,
'iCab' = 77,
'Quark' = 78,
'Minimo' = 79,
'Opera T' = 80,
'Chimera' = 81,
'NetSurf' = 82,
'Avant ' = 83,
'MAXTHON' = 84,
'jasmine' = 85,
'SLIM' = 86,
'Kindle' = 87,
'Netscape' = 88,
'BIDUBrowser' = 89,
'RockMelt' = 90,
'links' = 91,
'BOLT' = 92,
'Samsung Browser' = 93,
'NTENTBrowser' = 94,
'Safari' = 95,
'Waterfox' = 96,
'WeChat' = 97,
'IceWeasel' = 98,
'Iceweasel' = 99,
'conkeror' = 100,
'Dillo' = 101,
'w3m' = 102,
'Polaris' = 103,
'IceCat' = 104,
'Firebird' = 105,
'Swiftfox' = 106,
'QQBrowserLite' = 107,
'GSA' = 108,
'Konqueror' = 109,
'IceDragon' = 110,
'Opera Mi' = 111,
'Chrome Headless' = 112,
'Jasmine' = 113,
'Firefox' = 114,
'Oculus Browser' = 115,
'Blazer' = 116,
'Dolphin' = 117,
'Opera Touch' = 118,
'Firefox Focus' = 119,
'Opera Tablet' = 120,
'Maxthon' = 121,
'WebKit' = 122,
'iridium' = 123,
'Seamonkey' = 124,
'SeaMonkey' = 125,
'NetFront' = 126,
'UCBrowser' = 127,
'OmniWeb' = 128,
'firefox' = 129,
'MobileSafari' = 130,
'Mozilla' = 131,
'Links' = 132,
'Baidu' = 133,
'Facebook' = 134,
'Conkeror' = 135,
'Chrome' = 136,
'IEMobile' = 137,
'Opera Mobi' = 138,
'OviBrowser' = 139,
'chrome' = 140,
'Opera Mini' = 141
),
language Enum16(
'' = 0,
'he' = 1,
'eu' = 2,
'ni' = 3,
'mo' = 4,
'cy' = 5,
'nb' = 6,
'ii' = 7,
'cr' = 8,
'md' = 9,
'as' = 10,
'kh' = 11,
'bu' = 12,
'el' = 13,
'bg' = 14,
'aa' = 15,
'fj' = 16,
'fa' = 17,
'jv' = 18,
'ck' = 19,
'tt' = 20,
'us' = 21,
'qu' = 22,
'xh' = 23,
'rs' = 24,
'yo' = 25,
'pr' = 26,
'st' = 27,
'ra' = 28,
'vl' = 29,
'sm' = 30,
'pk' = 31,
'ua' = 32,
'sk' = 33,
'ug' = 34,
'tk' = 35,
'ru' = 36,
'sr' = 37,
'ty' = 38,
'pt' = 39,
'yi' = 40,
'tr' = 41,
'sy' = 42,
'yb' = 43,
'al' = 44,
'kw' = 45,
'jp' = 46,
'nv' = 47,
'mp' = 48,
'ag' = 49,
'hz' = 50,
'ba' = 51,
'ji' = 52,
'nd' = 53,
'io' = 54,
'bs' = 55,
'kn' = 56,
'le' = 57,
'ex' = 58,
'oc' = 59,
'au' = 60,
'es' = 61,
'ke' = 62,
'ln' = 63,
'bx' = 64,
'fu' = 65,
'mi' = 66,
'id' = 67,
'no' = 68,
'fr' = 69,
'ay' = 70,
'hd' = 71,
'et' = 72,
'kb' = 73,
'li' = 74,
'gu' = 75,
'mn' = 76,
'ds' = 77,
'cs' = 78,
'me' = 79,
'ar' = 80,
'fy' = 81,
'lb' = 82,
'ki' = 83,
'ca' = 84,
'dj' = 85,
'gl' = 86,
'nq' = 87,
'bm' = 88,
'ef' = 89,
'ak' = 90,
'da' = 91,
'jw' = 92,
'tu' = 93,
'ur' = 94,
'ps' = 95,
'zh' = 96,
'su' = 97,
'uk' = 98,
'zz' = 99,
'tl' = 100,
'sg' = 101,
'pa' = 102,
'sl' = 103,
'tg' = 104,
'wa' = 105,
'pl' = 106,
'ta' = 107,
'rm' = 108,
'sa' = 109,
'xo' = 110,
'ss' = 111,
'ts' = 112,
'wu' = 113,
'am' = 114,
'ff' = 115,
'ga' = 116,
'iw' = 117,
'mz' = 118,
'cg' = 119,
'af' = 120,
'lv' = 121,
'ek' = 122,
'in' = 123,
'ne' = 124,
'ko' = 125,
'br' = 126,
'ld' = 127,
'hi' = 128,
'at' = 129,
'lo' = 130,
'by' = 131,
'mh' = 132,
'du' = 133,
'gs' = 134,
'nn' = 135,
'ie' = 136,
'az' = 137,
'ka' = 138,
'ew' = 139,
'gv' = 140,
'nk' = 141,
'mm' = 142,
'mf' = 143,
'jm' = 144,
'la' = 145,
'kj' = 146,
'mt' = 147,
'nr' = 148,
'en' = 149,
'be' = 150,
'ac' = 151,
'bn' = 152,
'ks' = 153,
'ee' = 154,
'hu' = 155,
'gd' = 156,
'ir' = 157,
'ny' = 158,
'tv' = 159,
'xa' = 160,
'uz' = 161,
'sv' = 162,
'xx' = 163,
'vn' = 164,
'to' = 165,
'zy' = 166,
'sd' = 167,
'so' = 168,
've' = 169,
'po' = 170,
'si' = 171,
'rn' = 172,
'un' = 173,
'wo' = 174,
'ti' = 175,
'sb' = 176,
'rw' = 177,
'sp' = 178,
'tp' = 179,
'qz' = 180,
'an' = 181,
'hs' = 182,
'ku' = 183,
'bh' = 184,
'it' = 185,
'gb' = 186,
'my' = 187,
'co' = 188,
'mr' = 189,
'do' = 190,
'os' = 191,
'lu' = 192,
'eh' = 193,
'cv' = 194,
'lg' = 195,
'kl' = 196,
'aw' = 197,
'bz' = 198,
'ha' = 199,
'mk' = 200,
'dv' = 201,
'nm' = 202,
'om' = 203,
'lk' = 204,
'ia' = 205,
'ml' = 206,
'cz' = 207,
'na' = 208,
'mg' = 209,
'dz' = 210,
'hm' = 211,
'kk' = 212,
'mu' = 213,
'cc' = 214,
'gn' = 215,
'ns' = 216,
'bd' = 217,
'eo' = 218,
'ky' = 219,
'fi' = 220,
'ab' = 221,
'bo' = 222,
'kr' = 223,
'ht' = 224,
'ai' = 225,
'ch' = 226,
'is' = 227,
'ge' = 228,
'tw' = 229,
'za' = 230,
'sw' = 231,
'vo' = 232,
'se' = 233,
'tn' = 234,
'te' = 235,
'sn' = 236,
'yu' = 237,
'sh' = 238,
'zu' = 239,
'ro' = 240,
'ud' = 241,
'vi' = 242,
'th' = 243,
'sc' = 244,
'qp' = 245,
'sq' = 246,
'tz' = 247,
'zg' = 248,
'ya' = 249,
'hr' = 250,
'bi' = 251,
'eb' = 252,
'iu' = 253,
'de' = 254,
'mx' = 255,
'nu' = 256,
'ms' = 257,
'ce' = 258,
'hy' = 259,
'ad' = 260,
'or' = 261,
'fo' = 262,
'lt' = 263,
'ma' = 264,
'il' = 265,
'ng' = 266,
'km' = 267,
'av' = 268,
'kf' = 269,
'ok' = 270,
'ja' = 271,
'ig' = 272,
'nl' = 273
),
iab String,
size LowCardinality(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,
toLowCardinality(ssp) as ssp,
siteAppName,
cast(extract(upper(country), '\\w{3}') as Enum16(
'' = 0,
'POL' = 1,
'FJI' = 2,
'UKR' = 3,
'NGA' = 4,
'GMB' = 5,
'GHA' = 6,
'TUR' = 7,
'SUR' = 8,
'JAM' = 9,
'MAF' = 10,
'CRI' = 11,
'TGO' = 12,
'URY' = 13,
'COL' = 14,
'VUT' = 15,
'FRO' = 16,
'VGB' = 17,
'GGY' = 18,
'NFK' = 19,
'KGZ' = 20,
'PRT' = 21,
'COG' = 22,
'TZA' = 23,
'ETH' = 24,
'CXR' = 25,
'COK' = 26,
'LBY' = 27,
'THA' = 28,
'KIR' = 29,
'LBR' = 30,
'TCA' = 31,
'JEY' = 32,
'PRI' = 33,
'BMU' = 34,
'GUY' = 35,
'VAT' = 36,
'KOR' = 37,
'MTQ' = 38,
'NER' = 39,
'RUS' = 40,
'CPV' = 41,
'SRB' = 42,
'ZAF' = 43,
'QAT' = 44,
'IRQ' = 45,
'MCO' = 46,
'BEL' = 47,
'IRL' = 48,
'IND' = 49,
'KAZ' = 50,
'CIV' = 51,
'AUS' = 52,
'AIA' = 53,
'KWT' = 54,
'JPN' = 55,
'SAU' = 56,
'NAM' = 57,
'AUT' = 58,
'DZA' = 59,
'IOT' = 60,
'ABW' = 61,
'SVK' = 62,
'KEN' = 63,
'SSD' = 64,
'LKA' = 65,
'ALB' = 66,
'EST' = 67,
'IDN' = 68,
'ARE' = 69,
'DJI' = 70,
'MWI' = 71,
'TTO' = 72,
'COM' = 73,
'MNE' = 74,
'PNG' = 75,
'BTN' = 76,
'LBN' = 77,
'DOM' = 78,
'LIE' = 79,
'CAN' = 80,
'SMR' = 81,
'MEX' = 82,
'PRY' = 83,
'ITA' = 84,
'PSE' = 85,
'TLS' = 86,
'SGS' = 87,
'NIU' = 88,
'TUN' = 89,
'HRV' = 90,
'BGR' = 91,
'EGY' = 92,
'HND' = 93,
'SLB' = 94,
'RWA' = 95,
'BLR' = 96,
'SPM' = 97,
'SLE' = 98,
'EUR' = 99,
'PAN' = 100,
'NIC' = 101,
'BGD' = 102,
'HKG' = 103,
'DNK' = 104,
'JOR' = 105,
'GNQ' = 106,
'GRC' = 107,
'CCK' = 108,
'NOR' = 109,
'AGO' = 110,
'ZWE' = 111,
'CMR' = 112,
'GRD' = 113,
'VNM' = 114,
'ISL' = 115,
'CHL' = 116,
'SOM' = 117,
'BOL' = 118,
'SYC' = 119,
'BRB' = 120,
'ERI' = 121,
'CUB' = 122,
'SYR' = 123,
'SWZ' = 124,
'BRN' = 125,
'BWA' = 126,
'GAB' = 127,
'PHL' = 128,
'BES' = 129,
'TKL' = 130,
'BRA' = 131,
'AZE' = 132,
'ATA' = 133,
'CUW' = 134,
'BEN' = 135,
'LVA' = 136,
'HUN' = 137,
'IRN' = 138,
'PLW' = 139,
'WLF' = 140,
'SJM' = 141,
'ISR' = 142,
'SVN' = 143,
'SDN' = 144,
'TON' = 145,
'UZB' = 146,
'GRL' = 147,
'FLK' = 148,
'GEO' = 149,
'SXM' = 150,
'VEN' = 151,
'DEU' = 152,
'MDG' = 153,
'VCT' = 154,
'SGP' = 155,
'WSM' = 156,
'YEM' = 157,
'BLZ' = 158,
'MAR' = 159,
'LTU' = 160,
'MDV' = 161,
'ZMB' = 162,
'MAC' = 163,
'BIH' = 164,
'NLD' = 165,
'NPL' = 166,
'MOZ' = 167,
'MSR' = 168,
'ROU' = 169,
'GUM' = 170,
'GLP' = 171,
'MYT' = 172,
'GIN' = 173,
'ARM' = 174,
'BFA' = 175,
'GUF' = 176,
'TCD' = 177,
'MRT' = 178,
'IMN' = 179,
'MYS' = 180,
'GIB' = 181,
'BHS' = 182,
'LUX' = 183,
'MNP' = 184,
'FRA' = 185,
'CAF' = 186,
'PRK' = 187,
'PER' = 188,
'VIR' = 189,
'OMN' = 190,
'MLT' = 191,
'BDI' = 192,
'MUS' = 193,
'ESP' = 194,
'HTI' = 195,
'CZE' = 196,
'KNA' = 197,
'GNB' = 198,
'CHE' = 199,
'DMA' = 200,
'MLI' = 201,
'FIN' = 202,
'TJK' = 203,
'ALA' = 204,
'CHN' = 205,
'SWE' = 206,
'TWN' = 207,
'AFG' = 208,
'TKM' = 209,
'LAO' = 210,
'MMR' = 211,
'NRU' = 212,
'SEN' = 213,
'LSO' = 214,
'PCN' = 215,
'MHL' = 216,
'ATG' = 217,
'UGA' = 218,
'SHN' = 219,
'ECU' = 220,
'ARG' = 221,
'COD' = 222,
'PYF' = 223,
'MNG' = 224,
'NCL' = 225,
'AND' = 226,
'GBR' = 227,
'MKD' = 228,
'BHR' = 229,
'NZL' = 230,
'USA' = 231,
'STP' = 232,
'GTM' = 233,
'PAK' = 234,
'TUV' = 235,
'CYM' = 236,
'FSM' = 237,
'ASM' = 238,
'KHM' = 239,
'REU' = 240,
'MDA' = 241,
'LCA' = 242,
'CYP' = 243,
'SLV' = 244,
'BLM' = 245
)) as country,
cast(requestType as Enum8 (
'banner' = 1,
'exadsBanner' = 2,
'exadsPop' = 3,
'native' = 4,
'popunder' = 5)) as requestType,
conType,
cast(deviceOs as Enum8(
'' = 1,
'unix' = 2,
'haiku' = 3,
'suse' = 4,
'freebsd' = 5,
'pclinuxos' = 6,
'mandriva' = 7,
'opensolaris' = 8,
'chromium os' = 9,
'qnx' = 10,
'opensuse' = 11,
'mageia' = 12,
'risc os' = 13,
'nintendo' = 14,
'aix' = 15,
'centos' = 16,
'symbian' = 17,
'android' = 18,
'debian' = 19,
'solaris' = 20,
'fedora' = 21,
'sailfish' = 22,
'dragonfly' = 23,
'firefox os' = 24,
'playstation' = 25,
'series40' = 26,
'rim tablet os' = 27,
'windows phone' = 28,
'gnu' = 29,
'slackware' = 30,
'ios' = 31,
'tizen' = 32,
'mint' = 33,
'joli' = 34,
'openbsd' = 35,
'netbsd' = 36,
'hurd' = 37,
'blackberry' = 38,
'gentoo' = 39,
'bada' = 40,
'os/2' = 41,
'amigaos' = 42,
'mac os' = 43,
'zenwalk' = 44,
'beos' = 45,
'meego' = 46,
'windows' = 47,
'webos' = 48,
'kubuntu' = 49,
'linux' = 50,
'windows phone os' = 51,
'redhat' = 52,
'ubuntu' = 53
)) as deviceOs,
deviceType,
toLowCardinality(carrier) as carrier,
siteAppId,
isApp,
cast(server as Enum8(
'' = 0,
'endpoint1' = 1,
'endpoint10' = 10,
'endpoint11' = 11,
'endpoint12' = 12,
'endpoint2' = 2,
'endpoint3' = 3,
'endpoint4' = 4,
'endpoint5' = 5,
'endpoint6' = 6,
'endpoint7' = 7,
'endpoint8' = 8,
'endpoint9' = 9,
'eu1' = 31,
'eu2' = 32,
'eu3' = 33
)) as server,
userId,
cast(browser as Enum16(
'' = 1,
'silk' = 2,
'amaya' = 3,
'Edge' = 4,
'Camino' = 5,
'samsung browser' = 6,
'Opera' = 7,
'Arora' = 8,
'maxthon' = 9,
'polaris' = 10,
'vivaldi' = 11,
'Iron' = 12,
'Line' = 13,
'PaleMoon' = 14,
'Netfront' = 15,
'Mosaic' = 16,
'Maemo Browser' = 17,
'Midori' = 18,
'Phoenix' = 19,
'Doris' = 20,
'Chromium' = 21,
'2345Explorer' = 22,
'GoBrowser' = 23,
'Fennec' = 24,
'Iridium' = 25,
'TizenBrowser' = 26,
'NokiaBrowser' = 27,
'QQBrowser' = 28,
'Webkit' = 29,
'Chrome WebView' = 30,
'Lunascape' = 31,
'iemobile' = 32,
'rekonq' = 33,
'Skyfire' = 34,
'Palemoon' = 35,
'Yandex' = 36,
'slim' = 37,
'mozilla' = 38,
'Epiphany' = 39,
'Slim' = 40,
'qqbrowser' = 41,
'QQ' = 42,
'Iceape' = 43,
'K-Meleon' = 44,
'LBBROWSER' = 45,
'ie' = 46,
'ps4 web browser' = 47,
'Mobile Safari' = 48,
'Vivaldi' = 49,
'Silk' = 50,
'MIUI Browser' = 51,
'Comodo Dragon' = 52,
'PhantomJS' = 53,
'MetaSr' = 54,
'midori' = 55,
'safari' = 56,
'Lynx' = 57,
'icecat' = 58,
'Opera mini' = 59,
'chrome WebView' = 60,
'Bolt' = 61,
'Opera Coast' = 62,
'sLIm' = 63,
'webkit' = 64,
'k-meleon' = 65,
'Basilisk' = 66,
'Android Browser' = 67,
'baidu' = 68,
'KHTML' = 69,
'OPERA' = 70,
'Puffin' = 71,
'Brave' = 72,
'Sleipnir' = 73,
'IE' = 74,
'iceweasel' = 75,
'Flock' = 76,
'iCab' = 77,
'Quark' = 78,
'Minimo' = 79,
'Opera T' = 80,
'Chimera' = 81,
'NetSurf' = 82,
'Avant ' = 83,
'MAXTHON' = 84,
'jasmine' = 85,
'SLIM' = 86,
'Kindle' = 87,
'Netscape' = 88,
'BIDUBrowser' = 89,
'RockMelt' = 90,
'links' = 91,
'BOLT' = 92,
'Samsung Browser' = 93,
'NTENTBrowser' = 94,
'Safari' = 95,
'Waterfox' = 96,
'WeChat' = 97,
'IceWeasel' = 98,
'Iceweasel' = 99,
'conkeror' = 100,
'Dillo' = 101,
'w3m' = 102,
'Polaris' = 103,
'IceCat' = 104,
'Firebird' = 105,
'Swiftfox' = 106,
'QQBrowserLite' = 107,
'GSA' = 108,
'Konqueror' = 109,
'IceDragon' = 110,
'Opera Mi' = 111,
'Chrome Headless' = 112,
'Jasmine' = 113,
'Firefox' = 114,
'Oculus Browser' = 115,
'Blazer' = 116,
'Dolphin' = 117,
'Opera Touch' = 118,
'Firefox Focus' = 119,
'Opera Tablet' = 120,
'Maxthon' = 121,
'WebKit' = 122,
'iridium' = 123,
'Seamonkey' = 124,
'SeaMonkey' = 125,
'NetFront' = 126,
'UCBrowser' = 127,
'OmniWeb' = 128,
'firefox' = 129,
'MobileSafari' = 130,
'Mozilla' = 131,
'Links' = 132,
'Baidu' = 133,
'Facebook' = 134,
'Conkeror' = 135,
'Chrome' = 136,
'IEMobile' = 137,
'Opera Mobi' = 138,
'OviBrowser' = 139,
'chrome' = 140,
'Opera Mini' = 141
)) as browser,
cast(extract(lower(language), '\\w{2}') as Enum16(
'' = 0,
'he' = 1,
'eu' = 2,
'ni' = 3,
'mo' = 4,
'cy' = 5,
'nb' = 6,
'ii' = 7,
'cr' = 8,
'md' = 9,
'as' = 10,
'kh' = 11,
'bu' = 12,
'el' = 13,
'bg' = 14,
'aa' = 15,
'fj' = 16,
'fa' = 17,
'jv' = 18,
'ck' = 19,
'tt' = 20,
'us' = 21,
'qu' = 22,
'xh' = 23,
'rs' = 24,
'yo' = 25,
'pr' = 26,
'st' = 27,
'ra' = 28,
'vl' = 29,
'sm' = 30,
'pk' = 31,
'ua' = 32,
'sk' = 33,
'ug' = 34,
'tk' = 35,
'ru' = 36,
'sr' = 37,
'ty' = 38,
'pt' = 39,
'yi' = 40,
'tr' = 41,
'sy' = 42,
'yb' = 43,
'al' = 44,
'kw' = 45,
'jp' = 46,
'nv' = 47,
'mp' = 48,
'ag' = 49,
'hz' = 50,
'ba' = 51,
'ji' = 52,
'nd' = 53,
'io' = 54,
'bs' = 55,
'kn' = 56,
'le' = 57,
'ex' = 58,
'oc' = 59,
'au' = 60,
'es' = 61,
'ke' = 62,
'ln' = 63,
'bx' = 64,
'fu' = 65,
'mi' = 66,
'id' = 67,
'no' = 68,
'fr' = 69,
'ay' = 70,
'hd' = 71,
'et' = 72,
'kb' = 73,
'li' = 74,
'gu' = 75,
'mn' = 76,
'ds' = 77,
'cs' = 78,
'me' = 79,
'ar' = 80,
'fy' = 81,
'lb' = 82,
'ki' = 83,
'ca' = 84,
'dj' = 85,
'gl' = 86,
'nq' = 87,
'bm' = 88,
'ef' = 89,
'ak' = 90,
'da' = 91,
'jw' = 92,
'tu' = 93,
'ur' = 94,
'ps' = 95,
'zh' = 96,
'su' = 97,
'uk' = 98,
'zz' = 99,
'tl' = 100,
'sg' = 101,
'pa' = 102,
'sl' = 103,
'tg' = 104,
'wa' = 105,
'pl' = 106,
'ta' = 107,
'rm' = 108,
'sa' = 109,
'xo' = 110,
'ss' = 111,
'ts' = 112,
'wu' = 113,
'am' = 114,
'ff' = 115,
'ga' = 116,
'iw' = 117,
'mz' = 118,
'cg' = 119,
'af' = 120,
'lv' = 121,
'ek' = 122,
'in' = 123,
'ne' = 124,
'ko' = 125,
'br' = 126,
'ld' = 127,
'hi' = 128,
'at' = 129,
'lo' = 130,
'by' = 131,
'mh' = 132,
'du' = 133,
'gs' = 134,
'nn' = 135,
'ie' = 136,
'az' = 137,
'ka' = 138,
'ew' = 139,
'gv' = 140,
'nk' = 141,
'mm' = 142,
'mf' = 143,
'jm' = 144,
'la' = 145,
'kj' = 146,
'mt' = 147,
'nr' = 148,
'en' = 149,
'be' = 150,
'ac' = 151,
'bn' = 152,
'ks' = 153,
'ee' = 154,
'hu' = 155,
'gd' = 156,
'ir' = 157,
'ny' = 158,
'tv' = 159,
'xa' = 160,
'uz' = 161,
'sv' = 162,
'xx' = 163,
'vn' = 164,
'to' = 165,
'zy' = 166,
'sd' = 167,
'so' = 168,
've' = 169,
'po' = 170,
'si' = 171,
'rn' = 172,
'un' = 173,
'wo' = 174,
'ti' = 175,
'sb' = 176,
'rw' = 177,
'sp' = 178,
'tp' = 179,
'qz' = 180,
'an' = 181,
'hs' = 182,
'ku' = 183,
'bh' = 184,
'it' = 185,
'gb' = 186,
'my' = 187,
'co' = 188,
'mr' = 189,
'do' = 190,
'os' = 191,
'lu' = 192,
'eh' = 193,
'cv' = 194,
'lg' = 195,
'kl' = 196,
'aw' = 197,
'bz' = 198,
'ha' = 199,
'mk' = 200,
'dv' = 201,
'nm' = 202,
'om' = 203,
'lk' = 204,
'ia' = 205,
'ml' = 206,
'cz' = 207,
'na' = 208,
'mg' = 209,
'dz' = 210,
'hm' = 211,
'kk' = 212,
'mu' = 213,
'cc' = 214,
'gn' = 215,
'ns' = 216,
'bd' = 217,
'eo' = 218,
'ky' = 219,
'fi' = 220,
'ab' = 221,
'bo' = 222,
'kr' = 223,
'ht' = 224,
'ai' = 225,
'ch' = 226,
'is' = 227,
'ge' = 228,
'tw' = 229,
'za' = 230,
'sw' = 231,
'vo' = 232,
'se' = 233,
'tn' = 234,
'te' = 235,
'sn' = 236,
'yu' = 237,
'sh' = 238,
'zu' = 239,
'ro' = 240,
'ud' = 241,
'vi' = 242,
'th' = 243,
'sc' = 244,
'qp' = 245,
'sq' = 246,
'tz' = 247,
'zg' = 248,
'ya' = 249,
'hr' = 250,
'bi' = 251,
'eb' = 252,
'iu' = 253,
'de' = 254,
'mx' = 255,
'nu' = 256,
'ms' = 257,
'ce' = 258,
'hy' = 259,
'ad' = 260,
'or' = 261,
'fo' = 262,
'lt' = 263,
'ma' = 264,
'il' = 265,
'ng' = 266,
'km' = 267,
'av' = 268,
'kf' = 269,
'ok' = 270,
'ja' = 271,
'ig' = 272,
'nl' = 273
)) as language,
iab,
toLowCardinality(size) as size,
minCPM,
gender,
yob,
url
FROM BidRequests.usQueue;
CREATE MATERIALIZED VIEW IF NOT EXISTS BidRequests.euTraffic
(id String,
event_datetime DateTime,
ssp LowCardinality(String),
siteAppName String,
country Enum16(
'' = 0,
'POL' = 1,
'FJI' = 2,
'UKR' = 3,
'NGA' = 4,
'GMB' = 5,
'GHA' = 6,
'TUR' = 7,
'SUR' = 8,
'JAM' = 9,
'MAF' = 10,
'CRI' = 11,
'TGO' = 12,
'URY' = 13,
'COL' = 14,
'VUT' = 15,
'FRO' = 16,
'VGB' = 17,
'GGY' = 18,
'NFK' = 19,
'KGZ' = 20,
'PRT' = 21,
'COG' = 22,
'TZA' = 23,
'ETH' = 24,
'CXR' = 25,
'COK' = 26,
'LBY' = 27,
'THA' = 28,
'KIR' = 29,
'LBR' = 30,
'TCA' = 31,
'JEY' = 32,
'PRI' = 33,
'BMU' = 34,
'GUY' = 35,
'VAT' = 36,
'KOR' = 37,
'MTQ' = 38,
'NER' = 39,
'RUS' = 40,
'CPV' = 41,
'SRB' = 42,
'ZAF' = 43,
'QAT' = 44,
'IRQ' = 45,
'MCO' = 46,
'BEL' = 47,
'IRL' = 48,
'IND' = 49,
'KAZ' = 50,
'CIV' = 51,
'AUS' = 52,
'AIA' = 53,
'KWT' = 54,
'JPN' = 55,
'SAU' = 56,
'NAM' = 57,
'AUT' = 58,
'DZA' = 59,
'IOT' = 60,
'ABW' = 61,
'SVK' = 62,
'KEN' = 63,
'SSD' = 64,
'LKA' = 65,
'ALB' = 66,
'EST' = 67,
'IDN' = 68,
'ARE' = 69,
'DJI' = 70,
'MWI' = 71,
'TTO' = 72,
'COM' = 73,
'MNE' = 74,
'PNG' = 75,
'BTN' = 76,
'LBN' = 77,
'DOM' = 78,
'LIE' = 79,
'CAN' = 80,
'SMR' = 81,
'MEX' = 82,
'PRY' = 83,
'ITA' = 84,
'PSE' = 85,
'TLS' = 86,
'SGS' = 87,
'NIU' = 88,
'TUN' = 89,
'HRV' = 90,
'BGR' = 91,
'EGY' = 92,
'HND' = 93,
'SLB' = 94,
'RWA' = 95,
'BLR' = 96,
'SPM' = 97,
'SLE' = 98,
'EUR' = 99,
'PAN' = 100,
'NIC' = 101,
'BGD' = 102,
'HKG' = 103,
'DNK' = 104,
'JOR' = 105,
'GNQ' = 106,
'GRC' = 107,
'CCK' = 108,
'NOR' = 109,
'AGO' = 110,
'ZWE' = 111,
'CMR' = 112,
'GRD' = 113,
'VNM' = 114,
'ISL' = 115,
'CHL' = 116,
'SOM' = 117,
'BOL' = 118,
'SYC' = 119,
'BRB' = 120,
'ERI' = 121,
'CUB' = 122,
'SYR' = 123,
'SWZ' = 124,
'BRN' = 125,
'BWA' = 126,
'GAB' = 127,
'PHL' = 128,
'BES' = 129,
'TKL' = 130,
'BRA' = 131,
'AZE' = 132,
'ATA' = 133,
'CUW' = 134,
'BEN' = 135,
'LVA' = 136,
'HUN' = 137,
'IRN' = 138,
'PLW' = 139,
'WLF' = 140,
'SJM' = 141,
'ISR' = 142,
'SVN' = 143,
'SDN' = 144,
'TON' = 145,
'UZB' = 146,
'GRL' = 147,
'FLK' = 148,
'GEO' = 149,
'SXM' = 150,
'VEN' = 151,
'DEU' = 152,
'MDG' = 153,
'VCT' = 154,
'SGP' = 155,
'WSM' = 156,
'YEM' = 157,
'BLZ' = 158,
'MAR' = 159,
'LTU' = 160,
'MDV' = 161,
'ZMB' = 162,
'MAC' = 163,
'BIH' = 164,
'NLD' = 165,
'NPL' = 166,
'MOZ' = 167,
'MSR' = 168,
'ROU' = 169,
'GUM' = 170,
'GLP' = 171,
'MYT' = 172,
'GIN' = 173,
'ARM' = 174,
'BFA' = 175,
'GUF' = 176,
'TCD' = 177,
'MRT' = 178,
'IMN' = 179,
'MYS' = 180,
'GIB' = 181,
'BHS' = 182,
'LUX' = 183,
'MNP' = 184,
'FRA' = 185,
'CAF' = 186,
'PRK' = 187,
'PER' = 188,
'VIR' = 189,
'OMN' = 190,
'MLT' = 191,
'BDI' = 192,
'MUS' = 193,
'ESP' = 194,
'HTI' = 195,
'CZE' = 196,
'KNA' = 197,
'GNB' = 198,
'CHE' = 199,
'DMA' = 200,
'MLI' = 201,
'FIN' = 202,
'TJK' = 203,
'ALA' = 204,
'CHN' = 205,
'SWE' = 206,
'TWN' = 207,
'AFG' = 208,
'TKM' = 209,
'LAO' = 210,
'MMR' = 211,
'NRU' = 212,
'SEN' = 213,
'LSO' = 214,
'PCN' = 215,
'MHL' = 216,
'ATG' = 217,
'UGA' = 218,
'SHN' = 219,
'ECU' = 220,
'ARG' = 221,
'COD' = 222,
'PYF' = 223,
'MNG' = 224,
'NCL' = 225,
'AND' = 226,
'GBR' = 227,
'MKD' = 228,
'BHR' = 229,
'NZL' = 230,
'USA' = 231,
'STP' = 232,
'GTM' = 233,
'PAK' = 234,
'TUV' = 235,
'CYM' = 236,
'FSM' = 237,
'ASM' = 238,
'KHM' = 239,
'REU' = 240,
'MDA' = 241,
'LCA' = 242,
'CYP' = 243,
'SLV' = 244,
'BLM' = 245
),
requestType Enum8 (
'banner' = 1,
'exadsBanner' = 2,
'exadsPop' = 3,
'native' = 4,
'popunder' = 5),
conType UInt8,
deviceOs Enum8(
'' = 1,
'unix' = 2,
'haiku' = 3,
'suse' = 4,
'freebsd' = 5,
'pclinuxos' = 6,
'mandriva' = 7,
'opensolaris' = 8,
'chromium os' = 9,
'qnx' = 10,
'opensuse' = 11,
'mageia' = 12,
'risc os' = 13,
'nintendo' = 14,
'aix' = 15,
'centos' = 16,
'symbian' = 17,
'android' = 18,
'debian' = 19,
'solaris' = 20,
'fedora' = 21,
'sailfish' = 22,
'dragonfly' = 23,
'firefox os' = 24,
'playstation' = 25,
'series40' = 26,
'rim tablet os' = 27,
'windows phone' = 28,
'gnu' = 29,
'slackware' = 30,
'ios' = 31,
'tizen' = 32,
'mint' = 33,
'joli' = 34,
'openbsd' = 35,
'netbsd' = 36,
'hurd' = 37,
'blackberry' = 38,
'gentoo' = 39,
'bada' = 40,
'os/2' = 41,
'amigaos' = 42,
'mac os' = 43,
'zenwalk' = 44,
'beos' = 45,
'meego' = 46,
'windows' = 47,
'webos' = 48,
'kubuntu' = 49,
'linux' = 50,
'windows phone os' = 51,
'redhat' = 52,
'ubuntu' = 53
),
deviceType UInt8,
carrier LowCardinality(String),
siteAppId String,
isApp UInt8,
server Enum8(
'' = 0,
'endpoint1' = 1,
'endpoint10' = 10,
'endpoint11' = 11,
'endpoint12' = 12,
'endpoint2' = 2,
'endpoint3' = 3,
'endpoint4' = 4,
'endpoint5' = 5,
'endpoint6' = 6,
'endpoint7' = 7,
'endpoint8' = 8,
'endpoint9' = 9,
'eu1' = 31,
'eu2' = 32,
'eu3' = 33
),
userId String,
browser Enum16(
'' = 1,
'silk' = 2,
'amaya' = 3,
'Edge' = 4,
'Camino' = 5,
'samsung browser' = 6,
'Opera' = 7,
'Arora' = 8,
'maxthon' = 9,
'polaris' = 10,
'vivaldi' = 11,
'Iron' = 12,
'Line' = 13,
'PaleMoon' = 14,
'Netfront' = 15,
'Mosaic' = 16,
'Maemo Browser' = 17,
'Midori' = 18,
'Phoenix' = 19,
'Doris' = 20,
'Chromium' = 21,
'2345Explorer' = 22,
'GoBrowser' = 23,
'Fennec' = 24,
'Iridium' = 25,
'TizenBrowser' = 26,
'NokiaBrowser' = 27,
'QQBrowser' = 28,
'Webkit' = 29,
'Chrome WebView' = 30,
'Lunascape' = 31,
'iemobile' = 32,
'rekonq' = 33,
'Skyfire' = 34,
'Palemoon' = 35,
'Yandex' = 36,
'slim' = 37,
'mozilla' = 38,
'Epiphany' = 39,
'Slim' = 40,
'qqbrowser' = 41,
'QQ' = 42,
'Iceape' = 43,
'K-Meleon' = 44,
'LBBROWSER' = 45,
'ie' = 46,
'ps4 web browser' = 47,
'Mobile Safari' = 48,
'Vivaldi' = 49,
'Silk' = 50,
'MIUI Browser' = 51,
'Comodo Dragon' = 52,
'PhantomJS' = 53,
'MetaSr' = 54,
'midori' = 55,
'safari' = 56,
'Lynx' = 57,
'icecat' = 58,
'Opera mini' = 59,
'chrome WebView' = 60,
'Bolt' = 61,
'Opera Coast' = 62,
'sLIm' = 63,
'webkit' = 64,
'k-meleon' = 65,
'Basilisk' = 66,
'Android Browser' = 67,
'baidu' = 68,
'KHTML' = 69,
'OPERA' = 70,
'Puffin' = 71,
'Brave' = 72,
'Sleipnir' = 73,
'IE' = 74,
'iceweasel' = 75,
'Flock' = 76,
'iCab' = 77,
'Quark' = 78,
'Minimo' = 79,
'Opera T' = 80,
'Chimera' = 81,
'NetSurf' = 82,
'Avant ' = 83,
'MAXTHON' = 84,
'jasmine' = 85,
'SLIM' = 86,
'Kindle' = 87,
'Netscape' = 88,
'BIDUBrowser' = 89,
'RockMelt' = 90,
'links' = 91,
'BOLT' = 92,
'Samsung Browser' = 93,
'NTENTBrowser' = 94,
'Safari' = 95,
'Waterfox' = 96,
'WeChat' = 97,
'IceWeasel' = 98,
'Iceweasel' = 99,
'conkeror' = 100,
'Dillo' = 101,
'w3m' = 102,
'Polaris' = 103,
'IceCat' = 104,
'Firebird' = 105,
'Swiftfox' = 106,
'QQBrowserLite' = 107,
'GSA' = 108,
'Konqueror' = 109,
'IceDragon' = 110,
'Opera Mi' = 111,
'Chrome Headless' = 112,
'Jasmine' = 113,
'Firefox' = 114,
'Oculus Browser' = 115,
'Blazer' = 116,
'Dolphin' = 117,
'Opera Touch' = 118,
'Firefox Focus' = 119,
'Opera Tablet' = 120,
'Maxthon' = 121,
'WebKit' = 122,
'iridium' = 123,
'Seamonkey' = 124,
'SeaMonkey' = 125,
'NetFront' = 126,
'UCBrowser' = 127,
'OmniWeb' = 128,
'firefox' = 129,
'MobileSafari' = 130,
'Mozilla' = 131,
'Links' = 132,
'Baidu' = 133,
'Facebook' = 134,
'Conkeror' = 135,
'Chrome' = 136,
'IEMobile' = 137,
'Opera Mobi' = 138,
'OviBrowser' = 139,
'chrome' = 140,
'Opera Mini' = 141
),
language Enum16(
'' = 0,
'he' = 1,
'eu' = 2,
'ni' = 3,
'mo' = 4,
'cy' = 5,
'nb' = 6,
'ii' = 7,
'cr' = 8,
'md' = 9,
'as' = 10,
'kh' = 11,
'bu' = 12,
'el' = 13,
'bg' = 14,
'aa' = 15,
'fj' = 16,
'fa' = 17,
'jv' = 18,
'ck' = 19,
'tt' = 20,
'us' = 21,
'qu' = 22,
'xh' = 23,
'rs' = 24,
'yo' = 25,
'pr' = 26,
'st' = 27,
'ra' = 28,
'vl' = 29,
'sm' = 30,
'pk' = 31,
'ua' = 32,
'sk' = 33,
'ug' = 34,
'tk' = 35,
'ru' = 36,
'sr' = 37,
'ty' = 38,
'pt' = 39,
'yi' = 40,
'tr' = 41,
'sy' = 42,
'yb' = 43,
'al' = 44,
'kw' = 45,
'jp' = 46,
'nv' = 47,
'mp' = 48,
'ag' = 49,
'hz' = 50,
'ba' = 51,
'ji' = 52,
'nd' = 53,
'io' = 54,
'bs' = 55,
'kn' = 56,
'le' = 57,
'ex' = 58,
'oc' = 59,
'au' = 60,
'es' = 61,
'ke' = 62,
'ln' = 63,
'bx' = 64,
'fu' = 65,
'mi' = 66,
'id' = 67,
'no' = 68,
'fr' = 69,
'ay' = 70,
'hd' = 71,
'et' = 72,
'kb' = 73,
'li' = 74,
'gu' = 75,
'mn' = 76,
'ds' = 77,
'cs' = 78,
'me' = 79,
'ar' = 80,
'fy' = 81,
'lb' = 82,
'ki' = 83,
'ca' = 84,
'dj' = 85,
'gl' = 86,
'nq' = 87,
'bm' = 88,
'ef' = 89,
'ak' = 90,
'da' = 91,
'jw' = 92,
'tu' = 93,
'ur' = 94,
'ps' = 95,
'zh' = 96,
'su' = 97,
'uk' = 98,
'zz' = 99,
'tl' = 100,
'sg' = 101,
'pa' = 102,
'sl' = 103,
'tg' = 104,
'wa' = 105,
'pl' = 106,
'ta' = 107,
'rm' = 108,
'sa' = 109,
'xo' = 110,
'ss' = 111,
'ts' = 112,
'wu' = 113,
'am' = 114,
'ff' = 115,
'ga' = 116,
'iw' = 117,
'mz' = 118,
'cg' = 119,
'af' = 120,
'lv' = 121,
'ek' = 122,
'in' = 123,
'ne' = 124,
'ko' = 125,
'br' = 126,
'ld' = 127,
'hi' = 128,
'at' = 129,
'lo' = 130,
'by' = 131,
'mh' = 132,
'du' = 133,
'gs' = 134,
'nn' = 135,
'ie' = 136,
'az' = 137,
'ka' = 138,
'ew' = 139,
'gv' = 140,
'nk' = 141,
'mm' = 142,
'mf' = 143,
'jm' = 144,
'la' = 145,
'kj' = 146,
'mt' = 147,
'nr' = 148,
'en' = 149,
'be' = 150,
'ac' = 151,
'bn' = 152,
'ks' = 153,
'ee' = 154,
'hu' = 155,
'gd' = 156,
'ir' = 157,
'ny' = 158,
'tv' = 159,
'xa' = 160,
'uz' = 161,
'sv' = 162,
'xx' = 163,
'vn' = 164,
'to' = 165,
'zy' = 166,
'sd' = 167,
'so' = 168,
've' = 169,
'po' = 170,
'si' = 171,
'rn' = 172,
'un' = 173,
'wo' = 174,
'ti' = 175,
'sb' = 176,
'rw' = 177,
'sp' = 178,
'tp' = 179,
'qz' = 180,
'an' = 181,
'hs' = 182,
'ku' = 183,
'bh' = 184,
'it' = 185,
'gb' = 186,
'my' = 187,
'co' = 188,
'mr' = 189,
'do' = 190,
'os' = 191,
'lu' = 192,
'eh' = 193,
'cv' = 194,
'lg' = 195,
'kl' = 196,
'aw' = 197,
'bz' = 198,
'ha' = 199,
'mk' = 200,
'dv' = 201,
'nm' = 202,
'om' = 203,
'lk' = 204,
'ia' = 205,
'ml' = 206,
'cz' = 207,
'na' = 208,
'mg' = 209,
'dz' = 210,
'hm' = 211,
'kk' = 212,
'mu' = 213,
'cc' = 214,
'gn' = 215,
'ns' = 216,
'bd' = 217,
'eo' = 218,
'ky' = 219,
'fi' = 220,
'ab' = 221,
'bo' = 222,
'kr' = 223,
'ht' = 224,
'ai' = 225,
'ch' = 226,
'is' = 227,
'ge' = 228,
'tw' = 229,
'za' = 230,
'sw' = 231,
'vo' = 232,
'se' = 233,
'tn' = 234,
'te' = 235,
'sn' = 236,
'yu' = 237,
'sh' = 238,
'zu' = 239,
'ro' = 240,
'ud' = 241,
'vi' = 242,
'th' = 243,
'sc' = 244,
'qp' = 245,
'sq' = 246,
'tz' = 247,
'zg' = 248,
'ya' = 249,
'hr' = 250,
'bi' = 251,
'eb' = 252,
'iu' = 253,
'de' = 254,
'mx' = 255,
'nu' = 256,
'ms' = 257,
'ce' = 258,
'hy' = 259,
'ad' = 260,
'or' = 261,
'fo' = 262,
'lt' = 263,
'ma' = 264,
'il' = 265,
'ng' = 266,
'km' = 267,
'av' = 268,
'kf' = 269,
'ok' = 270,
'ja' = 271,
'ig' = 272,
'nl' = 273
),
iab String,
size LowCardinality(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,
toLowCardinality(ssp) as ssp,
siteAppName,
cast(extract(upper(country), '\\w{3}') as Enum16(
'' = 0,
'POL' = 1,
'FJI' = 2,
'UKR' = 3,
'NGA' = 4,
'GMB' = 5,
'GHA' = 6,
'TUR' = 7,
'SUR' = 8,
'JAM' = 9,
'MAF' = 10,
'CRI' = 11,
'TGO' = 12,
'URY' = 13,
'COL' = 14,
'VUT' = 15,
'FRO' = 16,
'VGB' = 17,
'GGY' = 18,
'NFK' = 19,
'KGZ' = 20,
'PRT' = 21,
'COG' = 22,
'TZA' = 23,
'ETH' = 24,
'CXR' = 25,
'COK' = 26,
'LBY' = 27,
'THA' = 28,
'KIR' = 29,
'LBR' = 30,
'TCA' = 31,
'JEY' = 32,
'PRI' = 33,
'BMU' = 34,
'GUY' = 35,
'VAT' = 36,
'KOR' = 37,
'MTQ' = 38,
'NER' = 39,
'RUS' = 40,
'CPV' = 41,
'SRB' = 42,
'ZAF' = 43,
'QAT' = 44,
'IRQ' = 45,
'MCO' = 46,
'BEL' = 47,
'IRL' = 48,
'IND' = 49,
'KAZ' = 50,
'CIV' = 51,
'AUS' = 52,
'AIA' = 53,
'KWT' = 54,
'JPN' = 55,
'SAU' = 56,
'NAM' = 57,
'AUT' = 58,
'DZA' = 59,
'IOT' = 60,
'ABW' = 61,
'SVK' = 62,
'KEN' = 63,
'SSD' = 64,
'LKA' = 65,
'ALB' = 66,
'EST' = 67,
'IDN' = 68,
'ARE' = 69,
'DJI' = 70,
'MWI' = 71,
'TTO' = 72,
'COM' = 73,
'MNE' = 74,
'PNG' = 75,
'BTN' = 76,
'LBN' = 77,
'DOM' = 78,
'LIE' = 79,
'CAN' = 80,
'SMR' = 81,
'MEX' = 82,
'PRY' = 83,
'ITA' = 84,
'PSE' = 85,
'TLS' = 86,
'SGS' = 87,
'NIU' = 88,
'TUN' = 89,
'HRV' = 90,
'BGR' = 91,
'EGY' = 92,
'HND' = 93,
'SLB' = 94,
'RWA' = 95,
'BLR' = 96,
'SPM' = 97,
'SLE' = 98,
'EUR' = 99,
'PAN' = 100,
'NIC' = 101,
'BGD' = 102,
'HKG' = 103,
'DNK' = 104,
'JOR' = 105,
'GNQ' = 106,
'GRC' = 107,
'CCK' = 108,
'NOR' = 109,
'AGO' = 110,
'ZWE' = 111,
'CMR' = 112,
'GRD' = 113,
'VNM' = 114,
'ISL' = 115,
'CHL' = 116,
'SOM' = 117,
'BOL' = 118,
'SYC' = 119,
'BRB' = 120,
'ERI' = 121,
'CUB' = 122,
'SYR' = 123,
'SWZ' = 124,
'BRN' = 125,
'BWA' = 126,
'GAB' = 127,
'PHL' = 128,
'BES' = 129,
'TKL' = 130,
'BRA' = 131,
'AZE' = 132,
'ATA' = 133,
'CUW' = 134,
'BEN' = 135,
'LVA' = 136,
'HUN' = 137,
'IRN' = 138,
'PLW' = 139,
'WLF' = 140,
'SJM' = 141,
'ISR' = 142,
'SVN' = 143,
'SDN' = 144,
'TON' = 145,
'UZB' = 146,
'GRL' = 147,
'FLK' = 148,
'GEO' = 149,
'SXM' = 150,
'VEN' = 151,
'DEU' = 152,
'MDG' = 153,
'VCT' = 154,
'SGP' = 155,
'WSM' = 156,
'YEM' = 157,
'BLZ' = 158,
'MAR' = 159,
'LTU' = 160,
'MDV' = 161,
'ZMB' = 162,
'MAC' = 163,
'BIH' = 164,
'NLD' = 165,
'NPL' = 166,
'MOZ' = 167,
'MSR' = 168,
'ROU' = 169,
'GUM' = 170,
'GLP' = 171,
'MYT' = 172,
'GIN' = 173,
'ARM' = 174,
'BFA' = 175,
'GUF' = 176,
'TCD' = 177,
'MRT' = 178,
'IMN' = 179,
'MYS' = 180,
'GIB' = 181,
'BHS' = 182,
'LUX' = 183,
'MNP' = 184,
'FRA' = 185,
'CAF' = 186,
'PRK' = 187,
'PER' = 188,
'VIR' = 189,
'OMN' = 190,
'MLT' = 191,
'BDI' = 192,
'MUS' = 193,
'ESP' = 194,
'HTI' = 195,
'CZE' = 196,
'KNA' = 197,
'GNB' = 198,
'CHE' = 199,
'DMA' = 200,
'MLI' = 201,
'FIN' = 202,
'TJK' = 203,
'ALA' = 204,
'CHN' = 205,
'SWE' = 206,
'TWN' = 207,
'AFG' = 208,
'TKM' = 209,
'LAO' = 210,
'MMR' = 211,
'NRU' = 212,
'SEN' = 213,
'LSO' = 214,
'PCN' = 215,
'MHL' = 216,
'ATG' = 217,
'UGA' = 218,
'SHN' = 219,
'ECU' = 220,
'ARG' = 221,
'COD' = 222,
'PYF' = 223,
'MNG' = 224,
'NCL' = 225,
'AND' = 226,
'GBR' = 227,
'MKD' = 228,
'BHR' = 229,
'NZL' = 230,
'USA' = 231,
'STP' = 232,
'GTM' = 233,
'PAK' = 234,
'TUV' = 235,
'CYM' = 236,
'FSM' = 237,
'ASM' = 238,
'KHM' = 239,
'REU' = 240,
'MDA' = 241,
'LCA' = 242,
'CYP' = 243,
'SLV' = 244,
'BLM' = 245
)) as country,
cast(requestType as Enum8 (
'banner' = 1,
'exadsBanner' = 2,
'exadsPop' = 3,
'native' = 4,
'popunder' = 5)) as requestType,
conType,
cast(deviceOs as Enum8(
'' = 1,
'unix' = 2,
'haiku' = 3,
'suse' = 4,
'freebsd' = 5,
'pclinuxos' = 6,
'mandriva' = 7,
'opensolaris' = 8,
'chromium os' = 9,
'qnx' = 10,
'opensuse' = 11,
'mageia' = 12,
'risc os' = 13,
'nintendo' = 14,
'aix' = 15,
'centos' = 16,
'symbian' = 17,
'android' = 18,
'debian' = 19,
'solaris' = 20,
'fedora' = 21,
'sailfish' = 22,
'dragonfly' = 23,
'firefox os' = 24,
'playstation' = 25,
'series40' = 26,
'rim tablet os' = 27,
'windows phone' = 28,
'gnu' = 29,
'slackware' = 30,
'ios' = 31,
'tizen' = 32,
'mint' = 33,
'joli' = 34,
'openbsd' = 35,
'netbsd' = 36,
'hurd' = 37,
'blackberry' = 38,
'gentoo' = 39,
'bada' = 40,
'os/2' = 41,
'amigaos' = 42,
'mac os' = 43,
'zenwalk' = 44,
'beos' = 45,
'meego' = 46,
'windows' = 47,
'webos' = 48,
'kubuntu' = 49,
'linux' = 50,
'windows phone os' = 51,
'redhat' = 52,
'ubuntu' = 53
)) as deviceOs,
deviceType,
toLowCardinality(carrier) as carrier,
siteAppId,
isApp,
cast(server as Enum8(
'' = 0,
'endpoint1' = 1,
'endpoint10' = 10,
'endpoint11' = 11,
'endpoint12' = 12,
'endpoint2' = 2,
'endpoint3' = 3,
'endpoint4' = 4,
'endpoint5' = 5,
'endpoint6' = 6,
'endpoint7' = 7,
'endpoint8' = 8,
'endpoint9' = 9,
'eu1' = 31,
'eu2' = 32,
'eu3' = 33
)) as server,
userId,
cast(browser as Enum16(
'' = 1,
'silk' = 2,
'amaya' = 3,
'Edge' = 4,
'Camino' = 5,
'samsung browser' = 6,
'Opera' = 7,
'Arora' = 8,
'maxthon' = 9,
'polaris' = 10,
'vivaldi' = 11,
'Iron' = 12,
'Line' = 13,
'PaleMoon' = 14,
'Netfront' = 15,
'Mosaic' = 16,
'Maemo Browser' = 17,
'Midori' = 18,
'Phoenix' = 19,
'Doris' = 20,
'Chromium' = 21,
'2345Explorer' = 22,
'GoBrowser' = 23,
'Fennec' = 24,
'Iridium' = 25,
'TizenBrowser' = 26,
'NokiaBrowser' = 27,
'QQBrowser' = 28,
'Webkit' = 29,
'Chrome WebView' = 30,
'Lunascape' = 31,
'iemobile' = 32,
'rekonq' = 33,
'Skyfire' = 34,
'Palemoon' = 35,
'Yandex' = 36,
'slim' = 37,
'mozilla' = 38,
'Epiphany' = 39,
'Slim' = 40,
'qqbrowser' = 41,
'QQ' = 42,
'Iceape' = 43,
'K-Meleon' = 44,
'LBBROWSER' = 45,
'ie' = 46,
'ps4 web browser' = 47,
'Mobile Safari' = 48,
'Vivaldi' = 49,
'Silk' = 50,
'MIUI Browser' = 51,
'Comodo Dragon' = 52,
'PhantomJS' = 53,
'MetaSr' = 54,
'midori' = 55,
'safari' = 56,
'Lynx' = 57,
'icecat' = 58,
'Opera mini' = 59,
'chrome WebView' = 60,
'Bolt' = 61,
'Opera Coast' = 62,
'sLIm' = 63,
'webkit' = 64,
'k-meleon' = 65,
'Basilisk' = 66,
'Android Browser' = 67,
'baidu' = 68,
'KHTML' = 69,
'OPERA' = 70,
'Puffin' = 71,
'Brave' = 72,
'Sleipnir' = 73,
'IE' = 74,
'iceweasel' = 75,
'Flock' = 76,
'iCab' = 77,
'Quark' = 78,
'Minimo' = 79,
'Opera T' = 80,
'Chimera' = 81,
'NetSurf' = 82,
'Avant ' = 83,
'MAXTHON' = 84,
'jasmine' = 85,
'SLIM' = 86,
'Kindle' = 87,
'Netscape' = 88,
'BIDUBrowser' = 89,
'RockMelt' = 90,
'links' = 91,
'BOLT' = 92,
'Samsung Browser' = 93,
'NTENTBrowser' = 94,
'Safari' = 95,
'Waterfox' = 96,
'WeChat' = 97,
'IceWeasel' = 98,
'Iceweasel' = 99,
'conkeror' = 100,
'Dillo' = 101,
'w3m' = 102,
'Polaris' = 103,
'IceCat' = 104,
'Firebird' = 105,
'Swiftfox' = 106,
'QQBrowserLite' = 107,
'GSA' = 108,
'Konqueror' = 109,
'IceDragon' = 110,
'Opera Mi' = 111,
'Chrome Headless' = 112,
'Jasmine' = 113,
'Firefox' = 114,
'Oculus Browser' = 115,
'Blazer' = 116,
'Dolphin' = 117,
'Opera Touch' = 118,
'Firefox Focus' = 119,
'Opera Tablet' = 120,
'Maxthon' = 121,
'WebKit' = 122,
'iridium' = 123,
'Seamonkey' = 124,
'SeaMonkey' = 125,
'NetFront' = 126,
'UCBrowser' = 127,
'OmniWeb' = 128,
'firefox' = 129,
'MobileSafari' = 130,
'Mozilla' = 131,
'Links' = 132,
'Baidu' = 133,
'Facebook' = 134,
'Conkeror' = 135,
'Chrome' = 136,
'IEMobile' = 137,
'Opera Mobi' = 138,
'OviBrowser' = 139,
'chrome' = 140,
'Opera Mini' = 141
)) as browser,
cast(extract(lower(language), '\\w{2}') as Enum16(
'' = 0,
'he' = 1,
'eu' = 2,
'ni' = 3,
'mo' = 4,
'cy' = 5,
'nb' = 6,
'ii' = 7,
'cr' = 8,
'md' = 9,
'as' = 10,
'kh' = 11,
'bu' = 12,
'el' = 13,
'bg' = 14,
'aa' = 15,
'fj' = 16,
'fa' = 17,
'jv' = 18,
'ck' = 19,
'tt' = 20,
'us' = 21,
'qu' = 22,
'xh' = 23,
'rs' = 24,
'yo' = 25,
'pr' = 26,
'st' = 27,
'ra' = 28,
'vl' = 29,
'sm' = 30,
'pk' = 31,
'ua' = 32,
'sk' = 33,
'ug' = 34,
'tk' = 35,
'ru' = 36,
'sr' = 37,
'ty' = 38,
'pt' = 39,
'yi' = 40,
'tr' = 41,
'sy' = 42,
'yb' = 43,
'al' = 44,
'kw' = 45,
'jp' = 46,
'nv' = 47,
'mp' = 48,
'ag' = 49,
'hz' = 50,
'ba' = 51,
'ji' = 52,
'nd' = 53,
'io' = 54,
'bs' = 55,
'kn' = 56,
'le' = 57,
'ex' = 58,
'oc' = 59,
'au' = 60,
'es' = 61,
'ke' = 62,
'ln' = 63,
'bx' = 64,
'fu' = 65,
'mi' = 66,
'id' = 67,
'no' = 68,
'fr' = 69,
'ay' = 70,
'hd' = 71,
'et' = 72,
'kb' = 73,
'li' = 74,
'gu' = 75,
'mn' = 76,
'ds' = 77,
'cs' = 78,
'me' = 79,
'ar' = 80,
'fy' = 81,
'lb' = 82,
'ki' = 83,
'ca' = 84,
'dj' = 85,
'gl' = 86,
'nq' = 87,
'bm' = 88,
'ef' = 89,
'ak' = 90,
'da' = 91,
'jw' = 92,
'tu' = 93,
'ur' = 94,
'ps' = 95,
'zh' = 96,
'su' = 97,
'uk' = 98,
'zz' = 99,
'tl' = 100,
'sg' = 101,
'pa' = 102,
'sl' = 103,
'tg' = 104,
'wa' = 105,
'pl' = 106,
'ta' = 107,
'rm' = 108,
'sa' = 109,
'xo' = 110,
'ss' = 111,
'ts' = 112,
'wu' = 113,
'am' = 114,
'ff' = 115,
'ga' = 116,
'iw' = 117,
'mz' = 118,
'cg' = 119,
'af' = 120,
'lv' = 121,
'ek' = 122,
'in' = 123,
'ne' = 124,
'ko' = 125,
'br' = 126,
'ld' = 127,
'hi' = 128,
'at' = 129,
'lo' = 130,
'by' = 131,
'mh' = 132,
'du' = 133,
'gs' = 134,
'nn' = 135,
'ie' = 136,
'az' = 137,
'ka' = 138,
'ew' = 139,
'gv' = 140,
'nk' = 141,
'mm' = 142,
'mf' = 143,
'jm' = 144,
'la' = 145,
'kj' = 146,
'mt' = 147,
'nr' = 148,
'en' = 149,
'be' = 150,
'ac' = 151,
'bn' = 152,
'ks' = 153,
'ee' = 154,
'hu' = 155,
'gd' = 156,
'ir' = 157,
'ny' = 158,
'tv' = 159,
'xa' = 160,
'uz' = 161,
'sv' = 162,
'xx' = 163,
'vn' = 164,
'to' = 165,
'zy' = 166,
'sd' = 167,
'so' = 168,
've' = 169,
'po' = 170,
'si' = 171,
'rn' = 172,
'un' = 173,
'wo' = 174,
'ti' = 175,
'sb' = 176,
'rw' = 177,
'sp' = 178,
'tp' = 179,
'qz' = 180,
'an' = 181,
'hs' = 182,
'ku' = 183,
'bh' = 184,
'it' = 185,
'gb' = 186,
'my' = 187,
'co' = 188,
'mr' = 189,
'do' = 190,
'os' = 191,
'lu' = 192,
'eh' = 193,
'cv' = 194,
'lg' = 195,
'kl' = 196,
'aw' = 197,
'bz' = 198,
'ha' = 199,
'mk' = 200,
'dv' = 201,
'nm' = 202,
'om' = 203,
'lk' = 204,
'ia' = 205,
'ml' = 206,
'cz' = 207,
'na' = 208,
'mg' = 209,
'dz' = 210,
'hm' = 211,
'kk' = 212,
'mu' = 213,
'cc' = 214,
'gn' = 215,
'ns' = 216,
'bd' = 217,
'eo' = 218,
'ky' = 219,
'fi' = 220,
'ab' = 221,
'bo' = 222,
'kr' = 223,
'ht' = 224,
'ai' = 225,
'ch' = 226,
'is' = 227,
'ge' = 228,
'tw' = 229,
'za' = 230,
'sw' = 231,
'vo' = 232,
'se' = 233,
'tn' = 234,
'te' = 235,
'sn' = 236,
'yu' = 237,
'sh' = 238,
'zu' = 239,
'ro' = 240,
'ud' = 241,
'vi' = 242,
'th' = 243,
'sc' = 244,
'qp' = 245,
'sq' = 246,
'tz' = 247,
'zg' = 248,
'ya' = 249,
'hr' = 250,
'bi' = 251,
'eb' = 252,
'iu' = 253,
'de' = 254,
'mx' = 255,
'nu' = 256,
'ms' = 257,
'ce' = 258,
'hy' = 259,
'ad' = 260,
'or' = 261,
'fo' = 262,
'lt' = 263,
'ma' = 264,
'il' = 265,
'ng' = 266,
'km' = 267,
'av' = 268,
'kf' = 269,
'ok' = 270,
'ja' = 271,
'ig' = 272,
'nl' = 273
)) as language,
iab,
toLowCardinality(size) as size,
minCPM,
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 match(toString(country), '\\w{3}')
AND userId LIKE '___%:__________%'
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 match(toString(country), '\\w{3}')
AND userId LIKE '___%:__________%'
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 userId LIKE '___%:____________%'
and country in ('MAR', 'USA')
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 userId LIKE '___%:____________%'
and country in ('MAR', 'USA')
GROUP BY country, iab, userId
HAVING iab like 'IAB%';
-- Helper Views
create view if not exists BidRequests.serverStatus
as
select server, maxMerge(lastUpdated) as lastUpdated
from BidRequests.statsByDay
where server != 'endpoint12'
group by server
order by lastUpdated desc;
-- Tests
select server, maxMerge(lastUpdated) as lastUpdated, sum(visits) as visits
from BidRequests.statsByDay
where event_date >= toDate('2019-04-18')
group by server
order by lastUpdated desc, visits desc;