{"id":89878,"date":"2020-07-27T01:42:42","date_gmt":"2020-07-26T23:42:42","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/teoriya-i-praktika-ispolzovaniya-clickhouse-v-realnyh-prilozheniyah-aleksandr-zajczev-2018g"},"modified":"2020-07-27T01:42:42","modified_gmt":"2020-07-26T23:42:42","slug":"teoriya-i-praktika-ispolzovaniya-clickhouse-v-realnyh-prilozheniyah-aleksandr-zajczev-2018g","status":"publish","type":"post","link":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/teoriya-i-praktika-ispolzovaniya-clickhouse-v-realnyh-prilozheniyah-aleksandr-zajczev-2018g","title":{"rendered":"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/b46b964324a3311157d5df737526b1d1.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>I kdy\u017e jsou dnes t\u00e9m\u011b\u0159 v\u0161ude k dispozici velk\u00e9 objemy dat, analytick\u00e9 datab\u00e1ze jsou st\u00e1le pom\u011brn\u011b exotick\u00e9. M\u00e1lokdo je zn\u00e1 a je\u0161t\u011b m\u00e9n\u011b lid\u00ed je um\u00ed efektivn\u011b vyu\u017e\u00edvat. Mnoz\u00ed nad\u00e1le \"jed\u00ed kaktus\" s MySQL nebo PostgreSQL, kter\u00e9 jsou navr\u017eeny pro jin\u00e9 sc\u00e9n\u00e1\u0159e, tr\u00e1p\u00ed se s NoSQL nebo plat\u00ed p\u0159ehnan\u00e9 ceny za komer\u010dn\u00ed \u0159e\u0161en\u00ed. ClickHouse m\u011bn\u00ed pravidla hry a v\u00fdrazn\u011b sni\u017euje bari\u00e9ry pro vstup do sv\u011bta analytick\u00fdch DBMS.<\/p>\n<p><\/p>\n<p>Prezentace z BackEnd Conf 2018 a byla publikov\u00e1na se souhlasem prezentuj\u00edc\u00edho.<\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\n<center><div class=\"youtube-placeholder\" data-id=\"9MwKE30aUPs\" onclick=\"loadVideo(this)\">\r\n        <img decoding=\"async\" src=\"https:\/\/img.youtube.com\/vi\/9MwKE30aUPs\/hqdefault.jpg\" alt=\"P\u0159ehr\u00e1t video\" loading=\"lazy\" width=\"480\" height=\"360\" style=\"width:100%;height:auto;\">\r\n        <div class=\"play-button\"><\/div>\r\n    <\/div><\/center><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/6c55b639d9703f460d99e0b0b1194507.png\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nKdo jsem a pro\u010d mluv\u00edm o ClickHouse? Jsem \u0159editel pro v\u00fdvoj ve spole\u010dnosti LifeStreet, kter\u00e1 pou\u017e\u00edv\u00e1 ClickHouse. Krom\u011b toho jsem zakladatel Altinity. To je partner Yandexu, kter\u00fd propaguje ClickHouse a pom\u00e1h\u00e1 Yandexu ud\u011blat ClickHouse \u00fasp\u011b\u0161n\u011bj\u0161\u00edm. Tak\u00e9 se chci pod\u011blit o znalosti o ClickHouse. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/36af6acba62271f7aa19dda2eb470870.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A je\u0161t\u011b nejsem bratr Peti Zajceva. Na to se m\u011b \u010dasto ptaj\u00ed. Ne, nejsme brat\u0159i.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/6ca605f97619a1373bb8a92f88d480d5.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u201eV\u0161ichni v\u011bd\u00ed\u201c, \u017ee ClickHouse:<\/p>\n<p><\/p>\n<ul>\n<li>Je velmi rychl\u00fd,<\/li>\n<li>Je velmi pohodln\u00fd, <\/li>\n<li>Pou\u017e\u00edv\u00e1 se v Yandexu. <\/li>\n<\/ul>\n<p><\/p>\n<p>Trochu m\u00e9n\u011b je zn\u00e1mo, v jak\u00fdch spole\u010dnostech a jak se pou\u017e\u00edv\u00e1. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/c097b003bf8ea13b8a3bb765270e99c8.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pov\u00edm v\u00e1m, k \u010demu, kde a jak se ClickHouse pou\u017e\u00edv\u00e1, krom\u011b Yandexu. <\/p>\n<p><\/p>\n<p>Pov\u00edm v\u00e1m, jak konkr\u00e9tn\u00ed \u00fakoly jsou \u0159e\u0161eny pomoc\u00ed ClickHouse v r\u016fzn\u00fdch spole\u010dnostech, jak\u00e9 prost\u0159edky ClickHouse m\u016f\u017eete pou\u017e\u00edt pro sv\u00e9 \u00fakoly a jak byly pou\u017eity v r\u016fzn\u00fdch firm\u00e1ch.<\/p>\n<p><\/p>\n<p>Vybral jsem t\u0159i p\u0159\u00edklady, kter\u00e9 ukazuj\u00ed ClickHouse z r\u016fzn\u00fdch \u00fahl\u016f. Mysl\u00edm, \u017ee to bude zaj\u00edmav\u00e9.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/d3cab92ceccd6c7d01ca1f28b028803f.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Prvn\u00ed ot\u00e1zka: \u201ePro\u010d pot\u0159ebujeme ClickHouse?\u201c. Na prvn\u00ed pohled je ot\u00e1zka dost z\u0159ejm\u00e1, ale odpov\u011bd\u00ed na ni je v\u00edce ne\u017e jedna. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/1a4e14cc041a3c01ad0fd2a305aeeab0.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Prvn\u00ed odpov\u011b\u010f \u2013 kv\u016fli v\u00fdkonu. ClickHouse je velmi rychl\u00fd. Anal\u00fdza na ClickHouse je tak\u00e9 velmi rychl\u00e1. M\u016f\u017ee se \u010dasto pou\u017e\u00edvat tam, kde n\u011bco jin\u00e9ho funguje velmi pomalu nebo velmi \u0161patn\u011b. <\/li>\n<li>Druh\u00e1 odpov\u011b\u010f \u2013 to je cena. A p\u0159edev\u0161\u00edm cena \u0161k\u00e1lov\u00e1n\u00ed. Nap\u0159\u00edklad, Vertica je naprosto vynikaj\u00edc\u00ed datab\u00e1ze. Funguje velmi dob\u0159e, pokud m\u00e1te jen n\u011bkolik terabajt\u016f dat. Ale kdy\u017e se jedn\u00e1 o stovky terabajt\u016f nebo petabajty, cena licence a podpory se vy\u0161plh\u00e1 na pom\u011brn\u011b zna\u010dnou \u010d\u00e1stku. A to je drah\u00e9. A ClickHouse je zdarma. <\/li>\n<li>T\u0159et\u00ed odpov\u011bd\u00ed jsou provozn\u00ed n\u00e1klady. Tento p\u0159\u00edstup se d\u00edv\u00e1 na probl\u00e9m z trochu jin\u00e9ho \u00fahlu. RedShift je skv\u011bl\u00e1 alternativa. Na RedShift lze velmi rychle vytvo\u0159it \u0159e\u0161en\u00ed. Bude dob\u0159e fungovat, ale ka\u017edou hodinu, ka\u017ed\u00fd den a ka\u017ed\u00fd m\u011bs\u00edc budete platit Amazonu pom\u011brn\u011b hodn\u011b, proto\u017ee je to zna\u010dn\u011b drah\u00e1 slu\u017eba. Google BigQuery tak\u00e9. Kdo ho pou\u017eil, v\u00ed, \u017ee lze spustit n\u011bkolik dotaz\u016f a n\u00e1hle dostat \u00fa\u010det na stovky dolar\u016f. <\/li>\n<\/ul>\n<p><\/p>\n<p>V ClickHouse tyto probl\u00e9my nejsou. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/3084a8bd9271f14726d73989946816c5.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kde se ClickHouse nyn\u00ed pou\u017e\u00edv\u00e1? Krom\u011b Yandextu se ClickHouse pou\u017e\u00edv\u00e1 v mnoha r\u016fzn\u00fdch podnic\u00edch a spole\u010dnostech. <\/p>\n<p><\/p>\n<ul>\n<li>Na prvn\u00edm m\u00edst\u011b je to anal\u00fdza webov\u00fdch aplikac\u00ed, t. j. to je p\u0159\u00edpad pou\u017eit\u00ed, kter\u00fd p\u0159i\u0161el z Yandextu.<\/li>\n<li>Mnoho spole\u010dnosti AdTech pou\u017e\u00edv\u00e1 ClickHouse. <\/li>\n<li>Mnoh\u00e9 firmy, kter\u00e9 pot\u0159ebuj\u00ed analyzovat provozn\u00ed logy z r\u016fzn\u00fdch zdroj\u016f.<\/li>\n<li>N\u011bkolik firem pou\u017e\u00edv\u00e1 ClickHouse pro sledov\u00e1n\u00ed bezpe\u010dnostn\u00edch log\u016f. Tyto logy nahr\u00e1vaj\u00ed do ClickHouse, vytv\u00e1\u0159ej\u00ed zpr\u00e1vy a z\u00edsk\u00e1vaj\u00ed pot\u0159ebn\u00e9 v\u00fdsledky.<\/li>\n<li>Firmy za\u010d\u00ednaj\u00ed pou\u017e\u00edvat ClickHouse i pro finan\u010dn\u00ed anal\u00fdzy, t. j. postupn\u011b se k ClickHouse tak\u00e9 p\u0159ibli\u017euje velk\u00fd byznys. <\/li>\n<li>CloudFlare. Kdo sleduje ClickHouse, ur\u010dit\u011b sly\u0161el n\u00e1zev t\u00e9to spole\u010dnosti. Je to jeden z v\u00fdznamn\u00fdch p\u0159isp\u011bvatel\u016f z komunity. A maj\u00ed velmi robustn\u00ed instalaci ClickHouse. Nap\u0159\u00edklad vytvo\u0159ili Kafka Engine pro ClickHouse. <\/li>\n<li>Telekomunika\u010dn\u00ed spole\u010dnosti za\u010daly pou\u017e\u00edvat. N\u011bkolik firem ClickHouse pou\u017e\u00edv\u00e1 bu\u010f jako proof of concept, nebo ji\u017e v produk\u010dn\u00edm prost\u0159ed\u00ed.<\/li>\n<li>Jedna spole\u010dnost pou\u017e\u00edv\u00e1 ClickHouse pro sledov\u00e1n\u00ed v\u00fdrobn\u00edch proces\u016f. Testuj\u00ed \u010dipy, shroma\u017e\u010fuj\u00ed spoustu parametr\u016f, asi 2 000 charakteristik. A pot\u00e9 analyzuj\u00ed, zda je d\u00e1vka dobr\u00e1 nebo \u0161patn\u00e1.<\/li>\n<li>Blockchainov\u00e1 analytika. Existuje rusk\u00e1 spole\u010dnost Bloxy.info. To je anal\u00fdza s\u00edt\u011b ethereum. I to ud\u011blali na ClickHouse. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/8a0c1924a20ed2d15cd0f959e9b3e499.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A p\u0159itom nez\u00e1le\u017e\u00ed na velikosti. Existuje mnoho spole\u010dnost\u00ed, kter\u00e9 pou\u017e\u00edvaj\u00ed jeden mal\u00fd server. A ten jim umo\u017e\u0148uje vy\u0159e\u0161it jejich probl\u00e9my. A je\u0161t\u011b v\u00edce firem pou\u017e\u00edv\u00e1 velk\u00e9 clustery z mnoha <a class=\"wpil_keyword_link\" href=\"https:\/\/prohoster.info\/cs\/server\/\"   title=\"servery\" data-wpil-keyword-link=\"linked\"  data-wpil-monitor-id=\"1513\">servery<\/a> nebo des\u00edtek server\u016f. <\/p>\n<p><\/p>\n<p>A pokud se pod\u00edv\u00e1me na rekordy, tak:<\/p>\n<p><\/p>\n<ul>\n<li>Yandex: 500+ server\u016f, denn\u011b tam uchov\u00e1vaj\u00ed 25 miliard z\u00e1znam\u016f.<\/li>\n<li>LifeStreet: 60 server\u016f, p\u0159ibli\u017en\u011b 75 miliard z\u00e1znam\u016f denn\u011b. Server\u016f m\u00e9n\u011b, z\u00e1znam\u016f v\u00edce ne\u017e v Yandextu. <\/li>\n<li>CloudFlare: 36 server\u016f, 200 miliard z\u00e1znam\u016f denn\u011b uchov\u00e1vaj\u00ed. Maj\u00ed je\u0161t\u011b m\u00e9n\u011b server\u016f a je\u0161t\u011b v\u00edce dat, kter\u00e1 uchov\u00e1vaj\u00ed. <\/li>\n<li>Bloomberg: 102 <a class=\"wpil_keyword_link\" href=\"https:\/\/prohoster.info\/cs\/server\/dts-los-angeles\/\"   title=\"serveru\" data-wpil-keyword-link=\"linked\"  data-wpil-monitor-id=\"3643\">serveru<\/a>, p\u0159ibli\u017en\u011b trilion z\u00e1znam\u016f denn\u011b. Rekordman v po\u010dtu z\u00e1znam\u016f.<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/49194030b6586221cb17029889ce33da.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Geograficky je to tak\u00e9 hodn\u011b. Tato mapa ukazuje heatmapu, kde se ClickHouse pou\u017e\u00edv\u00e1 ve sv\u011bt\u011b. Zde se v\u00fdrazn\u011b vyzna\u010duje Rusko, \u010c\u00edna, Amerika. Evropsk\u00fdch zem\u00ed je m\u00e1lo. A lze vy\u010dlenit 4 klastry.<\/p>\n<p><\/p>\n<p>Toto je srovn\u00e1vac\u00ed anal\u00fdza, zde nen\u00ed t\u0159eba hledat absolutn\u00ed \u010d\u00edsla. Jde o anal\u00fdzu n\u00e1v\u0161t\u011bvn\u00edk\u016f, kte\u0159\u00ed \u010dtou anglicky psan\u00e9 materi\u00e1ly na webu Altinity, proto\u017ee rusky mluv\u00edc\u00ed tam nejsou. A Rusko, Ukrajina, B\u011blorusko, tedy rusky mluv\u00edc\u00ed \u010d\u00e1st komunity, jsou nejpo\u010detn\u011bj\u0161\u00edmi u\u017eivateli. Pot\u00e9 n\u00e1sleduj\u00ed USA a Kanada. \u010c\u00edna velmi rychle doh\u00e1n\u00ed. P\u0159ed p\u016fl rokem t\u00e9m\u011b\u0159 \u017e\u00e1dn\u00e1 \u010c\u00edna, nyn\u00ed u\u017e \u010c\u00edna p\u0159ed\u010dila Evropu a nad\u00e1le roste. Star\u00fd kontinent tak\u00e9 nez\u016fst\u00e1v\u00e1 pozadu, p\u0159i\u010dem\u017e l\u00eddrem v pou\u017e\u00edv\u00e1n\u00ed ClickHouse je, jak p\u0159ekvapiv\u00e9, Francie. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/3a8305c51b1bd62f3467b795402df4ed.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><strong>Pro\u010d to v\u0161e \u0159\u00edk\u00e1m? Abych uk\u00e1zal, \u017ee ClickHouse se st\u00e1v\u00e1 standardn\u00edm \u0159e\u0161en\u00edm pro anal\u00fdzu velk\u00fdch dat a ji\u017e se velmi \u0161iroce pou\u017e\u00edv\u00e1.<\/strong> Pokud ho pou\u017e\u00edv\u00e1te, jste v spr\u00e1vn\u00e9m trendu. Pokud ho je\u0161t\u011b nepou\u017e\u00edv\u00e1te, nemus\u00edte se ob\u00e1vat, \u017ee z\u016fstanete sami a nikdo v\u00e1m nepom\u016f\u017ee, proto\u017ee u\u017e mnoho lid\u00ed se t\u00edm zab\u00fdv\u00e1.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/6aa635f3a35034d3af837c78b2c8fe60.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>To jsou p\u0159\u00edklady skute\u010dn\u00e9ho pou\u017eit\u00ed ClickHouse v n\u011bkolika spole\u010dnostech. <\/p>\n<p><\/p>\n<ul>\n<li>Prvn\u00ed p\u0159\u00edklad \u2013 reklamn\u00ed s\u00ed\u0165: migrace z Vertica na ClickHouse. A zn\u00e1m n\u011bkolik spole\u010dnost\u00ed, kter\u00e9 p\u0159e\u0161ly z Vertica nebo jsou v procesu p\u0159echodu. <\/li>\n<li>Druh\u00fd p\u0159\u00edklad \u2013 transak\u010dn\u00ed \u00falo\u017ei\u0161t\u011b na ClickHouse. To je p\u0159\u00edklad postaven\u00fd na antipatterny. V\u0161e, co se nedoporu\u010duje d\u011blat v ClickHouse podle rad v\u00fdvoj\u00e1\u0159\u016f, zde bylo provedeno. A p\u0159itom bylo ud\u011bl\u00e1no tak efektivn\u011b, \u017ee to funguje. A funguje to mnohem l\u00e9pe ne\u017e typick\u00e9 transak\u010dn\u00ed \u0159e\u0161en\u00ed. <\/li>\n<li>T\u0159et\u00ed p\u0159\u00edklad \u2013 distribuovan\u00e9 v\u00fdpo\u010dty na ClickHouse. Byla ot\u00e1zka, jak lze ClickHouse integrovat do ekosyst\u00e9mu Hadoop. Uk\u00e1\u017eu p\u0159\u00edklad, jak spole\u010dnost ud\u011blala na ClickHouse n\u011bco jako analog map reduce kontejner, sleduj\u00edc lokalizaci dat atd., aby spo\u010d\u00edtala velmi netrivi\u00e1ln\u00ed \u00falohu. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/e08ab5418500599f08d40324ccdfbcd0.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>LifeStreet \u2013 to je Ad Tech spole\u010dnost, kter\u00e1 m\u00e1 v\u0161echny technologie pot\u0159ebn\u00e9 k reklamn\u00ed s\u00edti. <\/li>\n<li>Zab\u00fdv\u00e1 se optimalizac\u00ed reklam, programmatic bidding. <\/li>\n<li>Hodn\u011b dat: p\u0159ibli\u017en\u011b 10 miliard ud\u00e1lost\u00ed denn\u011b. P\u0159itom se ud\u00e1losti mohou d\u011blit na n\u011bkolik pod-ud\u00e1lost\u00ed.<\/li>\n<li>Hodn\u011b klient\u016f t\u011bchto dat, a to nejen lid\u00e9, ale tak\u00e9 r\u016fzn\u00e9 algoritmy, kter\u00e9 se zab\u00fdvaj\u00ed programatick\u00fdm nab\u00edzen\u00edm. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/2da99b53eb25f675a2431027f86db934.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Spole\u010dnost pro\u0161la dlouhou a trnitou cestou. O tom jsem mluvil na HighLoad. Nejprve LifeStreet p\u0159e\u0161la z MySQL (s kr\u00e1tkou p\u0159est\u00e1vkou na Oracle) na Vertica. A o tom se m\u016f\u017eete do\u010d\u00edst. <\/p>\n<p><\/p>\n<p>A v\u0161echno bylo velmi dobr\u00e9, ale brzy se uk\u00e1zalo, \u017ee data rostou a Vertica je drah\u00e1. Proto jsme hledali r\u016fzn\u00e9 alternativy. N\u011bkter\u00e9 z nich jsou zde uvedeny. A ve skute\u010dnosti jsme provedli proof of concept nebo n\u011bkdy performance testing t\u00e9m\u011b\u0159 v\u0161ech datab\u00e1z\u00ed, kter\u00e9 byly na trhu dostupn\u00e9 od roku 2013 do 2016 a kter\u00e9 p\u0159ibli\u017en\u011b odpov\u00eddaly po\u017eadovan\u00e9 funk\u010dnosti. A o n\u011bkter\u00fdch z nich jsem tak\u00e9 mluvil na HighLoad.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/4bebd2f43892cbef9249ddded1b8a70a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Bylo t\u0159eba migrovat z Vertica, proto\u017ee data rostla. A rostla exponenci\u00e1ln\u011b n\u011bkolik let. Pak se stabilizovala, ale p\u0159esto. A p\u0159i predikci tohoto r\u016fstu, po\u017eadavky podnik\u00e1n\u00ed na objem dat, na kter\u00fdch je pot\u0159eba prov\u00e1d\u011bt n\u011bjakou anal\u00fdzu, bylo jasn\u00e9, \u017ee brzy dojde na petabyty. A za petabyty se u\u017e plat\u00ed velmi draze, tak\u017ee jsme hledali alternativu, kam p\u0159ej\u00edt. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/f170a4307168e8560122966eab85c24e.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kam p\u0159ej\u00edt? A dlouho nebylo jasn\u00e9, kam se vydat, proto\u017ee na jedn\u00e9 stran\u011b existuj\u00ed komer\u010dn\u00ed datab\u00e1ze, kter\u00e9 vypadaly, \u017ee funguj\u00ed docela dob\u0159e. N\u011bkter\u00e9 funguj\u00ed t\u00e9m\u011b\u0159 tak stejn\u011b dob\u0159e jako Vertica, n\u011bkter\u00e9 h\u016f\u0159e. Ale v\u0161echny jsou drah\u00e9, nic levn\u011bj\u0161\u00edho a lep\u0161\u00edho jsme nenalezli. <\/p>\n<p><\/p>\n<p>Na druh\u00e9 stran\u011b existuj\u00ed open source \u0159e\u0161en\u00ed, kter\u00fdch nen\u00ed mnoho, to znamen\u00e1, \u017ee pro anal\u00fdzu se daj\u00ed spo\u010d\u00edtat na prstech jedn\u00e9 ruky. A jsou zdarma nebo levn\u00e9, ale funguj\u00ed pomalu. A \u010dasto jim chyb\u00ed pot\u0159ebn\u00e1 a u\u017eite\u010dn\u00e1 funk\u010dnost.<\/p>\n<p><\/p>\n<p>A tak, aby se to spojilo, co je dobr\u00e9ho v komer\u010dn\u00edch datab\u00e1z\u00edch a v\u0161e to zdarma, co je v open source, \u2014 nic takov\u00e9ho nebylo. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/abbefbf0d9578b554f81579b87213a50.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nic takov\u00e9ho nebylo, dokud n\u00e1hle Yandex nevyt\u00e1hl, jako kouzeln\u00edk kr\u00e1l\u00edka z klobouku, ClickHouse. A to bylo ne\u010dekan\u00e9 \u0159e\u0161en\u00ed, dodnes se kladou ot\u00e1zky: \"Pro\u010d?\", ale p\u0159esto. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/c88981aace9d5925396ca5171241b7d2.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A v l\u00e9t\u011b 2016 jsme za\u010dali zkoumat, co to je ClickHouse. A uk\u00e1zalo se, \u017ee n\u011bkdy m\u016f\u017ee b\u00fdt rychlej\u0161\u00ed ne\u017e Vertica. Testovali jsme r\u016fzn\u00e9 sc\u00e9n\u00e1\u0159e na r\u016fzn\u00fdch dotazech. A pokud dotaz vyu\u017e\u00edval pouze jednu tabulku, tedy bez jak\u00fdchkoli spojen\u00ed (join), byl ClickHouse dvakr\u00e1t rychlej\u0161\u00ed ne\u017e Vertica. <\/p>\n<p><\/p>\n<p>Nejprve jsem se pod\u00edval na dal\u0161\u00ed testy Yandexu, kter\u00e9 prob\u011bhly ned\u00e1vno. Tam to bylo stejn\u00e9: ClickHouse byl dvakr\u00e1t rychlej\u0161\u00ed ne\u017e Vertica, proto o tom \u010dasto mluv\u00ed. <\/p>\n<p><\/p>\n<p>Ale pokud jsou v dotazech spojen\u00ed (join), v\u011bci nejsou tak jednozna\u010dn\u00e9. A ClickHouse m\u016f\u017ee b\u00fdt dvakr\u00e1t pomalej\u0161\u00ed ne\u017e Vertica. Pokud lehce dotaz uprav\u00edte a p\u0159ep\u00ed\u0161ete, pak jsou p\u0159ibli\u017en\u011b na stejn\u00e9 \u00farovni. Ne \u0161patn\u00e9. A zdarma. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/188bf56b72123175a18d2cb97d989a47.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A po z\u00edsk\u00e1n\u00ed v\u00fdsledk\u016f test\u016f a pohledu na to z r\u016fzn\u00fdch \u00fahl\u016f se LifeStreet rozhodl pro ClickHouse.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/1183b9b7681916686c52786fc95ccb45.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pamatujte, \u017ee je to rok 2016. Bylo to jako v anekdot\u011b o my\u0161\u00edch, kter\u00e9 plakaly a bodaly se, ale st\u00e1le jedly kaktus. O tom bylo podrobn\u011b mluveno, existuj\u00ed o tom videa atd. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/993052ed83dc295c3777e614c7703456.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Proto o tom nebudu mluvit podrobn\u011b, jen se zm\u00edn\u00edm o v\u00fdsledc\u00edch a n\u011bkolika zaj\u00edmav\u00fdch v\u011bcech, o kter\u00fdch jsem tehdy nemluvil. <\/p>\n<p><\/p>\n<p>V\u00fdsledky jsou n\u00e1sleduj\u00edc\u00ed:<\/p>\n<p><\/p>\n<ul>\n<li>\u00dasp\u011b\u0161n\u00e1 migrace a syst\u00e9m ji\u017e v\u00edce ne\u017e rok funguje v produk\u010dn\u00edm prost\u0159ed\u00ed. <\/li>\n<li>V\u00fdkon a flexibilita vzrostly. Z 10 miliard z\u00e1znam\u016f, kter\u00e9 jsme si mohli dovolit uchov\u00e1vat b\u011bhem dne a to pouze kr\u00e1tkodob\u011b, nyn\u00ed LifeStreet uchov\u00e1v\u00e1 75 miliard z\u00e1znam\u016f za den a m\u016f\u017ee to d\u011blat po dobu 3 m\u011bs\u00edc\u016f a v\u00edce. Pokud po\u010d\u00edt\u00e1me v \u0161pi\u010dk\u00e1ch, ukl\u00e1d\u00e1 se a\u017e milion ud\u00e1lost\u00ed za sekundu. V\u00edce ne\u017e milion SQL dotaz\u016f denn\u011b doraz\u00ed do tohoto syst\u00e9mu, p\u0159ev\u00e1\u017en\u011b od r\u016fzn\u00fdch robot\u016f. <\/li>\n<li>I kdy\u017e pro ClickHouse za\u010dalo b\u00fdt pou\u017eito v\u00edce server\u016f ne\u017e pro Vertica, \u00faspora na hardware byla, proto\u017ee ve Vertice byly pou\u017e\u00edv\u00e1ny pom\u011brn\u011b drah\u00e9 SAS disky. V ClickHouse byly pou\u017eity SATA. A pro\u010d? Proto\u017ee ve Vertice je insert synchronn\u00ed. A synchronizace vy\u017eaduje, aby disky nepomalovaly a tak\u00e9, aby s\u00ed\u0165 nebyla pomal\u00e1, tedy celkem drah\u00e1 operace. A v ClickHouse je insert asynchronn\u00ed. Nav\u00edc, v\u0161echno m\u016f\u017eete zapisovat lok\u00e1ln\u011b, zde nejsou \u017e\u00e1dn\u00e9 dodate\u010dn\u00e9 n\u00e1klady, a proto lze data v ClickHouse vkl\u00e1dat mnohem rychleji ne\u017e do Vertica, i na ne nejrychlej\u0161\u00edch disc\u00edch. Co se t\u00fd\u010de \u010dten\u00ed, to je p\u0159ibli\u017en\u011b stejn\u00e9. \u010cten\u00ed na SATA, pokud jsou v RAID, je dostate\u010dn\u011b rychl\u00e9. <\/li>\n<li>Neomezen\u00e9 licenc\u00ed, tj. 3 petabajty dat na 60 serverech (20 server\u016f je jedna replikace) a 6 bilion\u016f z\u00e1znam\u016f ve faktech a agreg\u00e1tech. Nic takov\u00e9ho si na Vertice nemohli dovolit. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/f461cdc12917ee655655dd7eb9c32bb2.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nyn\u00ed se p\u0159esunu k praktick\u00fdm v\u011bcem v tomto p\u0159\u00edkladu.<\/p>\n<p><\/p>\n<ul>\n<li>Prvn\u00ed je efektivn\u00ed sch\u00e9ma. Od sch\u00e9matu z\u00e1vis\u00ed velmi mnoho. <\/li>\n<li>Druh\u00e9 je generov\u00e1n\u00ed efektivn\u00edho SQL.<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/e7dc2a217e77c8794ff6e8a754e78c85.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Typick\u00fd OLAP dotaz je SELECT. \u010c\u00e1st sloupc\u016f jde do GROUP BY, \u010d\u00e1st sloupc\u016f jde do agreg\u00e1tn\u00edch funkc\u00ed. Je tam WHERE, kter\u00e9 lze p\u0159edstavit jako \u0159ez kubu. Cel\u00fd GROUP BY lze p\u0159edstavit jako projekci. A proto se tomu \u0159\u00edk\u00e1 v\u00edcerozm\u011brn\u00e1 anal\u00fdza dat. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/a03a2d7fd28905ae83183346f0c673e2.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A \u010dasto je to modelov\u00e1no ve form\u011b star-schematu, kdy je centr\u00e1ln\u00ed fakt a charakteristiky tohoto faktu po stran\u00e1ch, po paprsc\u00edch. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/d9671af4b39cc83e1ae49e6fd75d0e02.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A z pohledu fyzick\u00e9ho designu, jak se to p\u0159ev\u00e1d\u00ed na tabulku, se obvykle d\u011bl\u00e1 normalizovan\u00e9 zobrazen\u00ed. M\u016f\u017eete denormalizovat, ale je to drah\u00e9 v diskov\u00e9m prostoru a ne p\u0159\u00edli\u0161 efektivn\u00ed p\u0159i dotazech. Proto se obvykle d\u011bl\u00e1 normalizovan\u00e9 zobrazen\u00ed, tj. tabulka fakt\u016f a mnoho dal\u0161\u00edch tabulek dimenz\u00ed. <\/p>\n<p><\/p>\n<p>Ale v ClickHouse to \u0161patn\u011b funguje. Jsou dva d\u016fvody: <\/p>\n<p><\/p>\n<ul>\n<li>Prvn\u00ed je, \u017ee v ClickHouse nejsou p\u0159\u00edli\u0161 dobr\u00e9 JOINy, tj. JOINy existuj\u00ed, ale jsou \u0161patn\u00e9. Zat\u00edm \u0161patn\u00e9. <\/li>\n<li>Druh\u00fd je, \u017ee tabulky se neaktualizuj\u00ed. Obvykle je pot\u0159eba v t\u011bchto tabulk\u00e1ch kolem star-schematu n\u011bco m\u011bnit. Nap\u0159\u00edklad n\u00e1zev klienta, n\u00e1zev firmy a podobn\u011b. A to nefunguje. <\/li>\n<\/ul>\n<p><\/p>\n<p>A \u0159e\u0161en\u00ed pro to v ClickHouse existuje. dokonce dv\u011b: <\/p>\n<p><\/p>\n<ul>\n<li>Prvn\u00ed je pou\u017eit\u00ed slovn\u00edk\u016f. External Dictionaries je n\u011bco, co pom\u00e1h\u00e1 vy\u0159e\u0161it probl\u00e9m se star-schematem, s aktualizacemi a dal\u0161\u00edmi probl\u00e9my na 99 %. <\/li>\n<li>Druh\u00e1 je pou\u017eit\u00ed pol\u00ed. Pole tak\u00e9 pom\u00e1haj\u00ed zbavit se JOIN\u016f a probl\u00e9m\u016f s normalizac\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/775972cc412651600799173407384492.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Nepot\u0159ebujete JOINy. <\/li>\n<li>Aktualizovateln\u00e9. Od b\u0159ezna 2018 se objevila nedokumentovan\u00e1 funkce (v dokumentaci to nenajdete) pro \u010d\u00e1ste\u010dn\u00e9 aktualizace slovn\u00edk\u016f, tj. t\u011bch z\u00e1znam\u016f, kter\u00e9 se zm\u011bnily. Prakticky \u2013 je to jako tabulka.<\/li>\n<li>V\u017edy v pam\u011bti, tak\u017ee JOINy se slovn\u00edkem funguj\u00ed rychleji, ne\u017e kdyby to byla tabulka, kter\u00e1 le\u017e\u00ed na disku a je\u0161t\u011b nen\u00ed jist\u00e9, \u017ee je v cache, pravd\u011bpodobn\u011b ne. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/f73176a650b6d885011e3265c2af2fc3.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Tak\u00e9 nepot\u0159ebujete JOINy.<\/li>\n<li>Je to kompaktn\u00ed zobrazen\u00ed 1 k mnoha. <\/li>\n<li>A z m\u00e9ho pohledu, pole jsou ur\u010den\u00e1 pro geeky. Jsou to lambda funkce a dal\u0161\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p>To nen\u00ed jen tak pro slovo. To je velmi siln\u00e1 funk\u010dnost, kter\u00e1 umo\u017e\u0148uje d\u011blat mnoho v\u011bc\u00ed velmi jednodu\u0161e a elegantn\u011b. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/5d813952ca8f3da7a74c6f2a15d80a9b.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Typick\u00e9 p\u0159\u00edklady, kter\u00e9 pom\u00e1haj\u00ed \u0159e\u0161it pole. Tyto p\u0159\u00edklady jsou jednoduch\u00e9 a dostate\u010dn\u011b n\u00e1zorn\u00e9:<\/p>\n<p><\/p>\n<ul>\n<li>Hled\u00e1n\u00ed podle tag\u016f. Pokud tam m\u00e1te hashtagy a chcete naj\u00edt n\u011bjak\u00e9 z\u00e1znamy podle hashtagu. <\/li>\n<li>Hled\u00e1n\u00ed podle p\u00e1r\u016f key-value. Tak\u00e9 m\u00e1te n\u011bjak\u00e9 atributy s hodnotou. <\/li>\n<li>Ukl\u00e1d\u00e1n\u00ed seznam\u016f kl\u00ed\u010d\u016f, kter\u00e9 pot\u0159ebujete p\u0159ev\u00e9st na n\u011bco jin\u00e9ho.<\/li>\n<\/ul>\n<p><\/p>\n<p>V\u0161echny tyto \u00fakoly lze vy\u0159e\u0161it bez pol\u00ed. Tagy m\u016f\u017eete ulo\u017eit do jednoho \u0159et\u011bzce a vyb\u00edrat je pomoc\u00ed regul\u00e1rn\u00edho v\u00fdrazu, nebo do samostatn\u00e9 tabulky, ale pak budete muset prov\u00e1d\u011bt spojen\u00ed (join).<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/dbbf90eee098c4564206532f24156823.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A v ClickHouse nen\u00ed pot\u0159eba nic d\u011blat, sta\u010d\u00ed popsat pole string pro hashtagy nebo vytvo\u0159it vno\u0159enou strukturu pro syst\u00e9my typu key-value.<\/p>\n<p><\/p>\n<p>Vno\u0159en\u00e1 struktura \u2013 to mo\u017en\u00e1 nen\u00ed to nejlep\u0161\u00ed pojmenov\u00e1n\u00ed. Jsou to dv\u011b pole, kter\u00e9 maj\u00ed spole\u010dnou \u010d\u00e1st v n\u00e1zvu a n\u011bkter\u00e9 souvisej\u00edc\u00ed charakteristiky. <\/p>\n<p><\/p>\n<p>A hled\u00e1n\u00ed podle tagu je velmi jednoduch\u00e9. Existuje funkce <code>has<\/code>, kter\u00e1 kontroluje, zda je v poli prvek. A m\u00e1me v\u0161echna z\u00e1znamy, kter\u00e9 se vztahuj\u00ed k na\u0161\u00ed konferenci.<\/p>\n<p><\/p>\n<p>Hled\u00e1n\u00ed podle subid je o n\u011bco slo\u017eit\u011bj\u0161\u00ed. Mus\u00edme nejprve naj\u00edt index kl\u00ed\u010de a pak vz\u00edt prvek s t\u00edmto indexem a zkontrolovat, zda m\u00e1 po\u017eadovanou hodnotu. Ale i tak je to velmi jednoduch\u00e9 a kompaktn\u00ed.<\/p>\n<p><\/p>\n<p>Regul\u00e1rn\u00ed v\u00fdraz, kter\u00fd byste cht\u011bli napsat, kdybyste to v\u0161e uchov\u00e1vali v jednom \u0159et\u011bzci, by byl za prv\u00e9 zkrpruhlan\u00fd. A zadruh\u00e9 by pracoval mnohem d\u00e9le ne\u017e dv\u011b pole. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/17664c3121b2b0d33188adbfaa653d4e.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dal\u0161\u00ed p\u0159\u00edklad. M\u00e1te pole, ve kter\u00e9m uchov\u00e1v\u00e1te ID. A m\u016f\u017eete je p\u0159ev\u00e9st na n\u00e1zvy. Funkce <code>arrayMap<\/code>. To je typick\u00e1 lambda funkce. P\u0159ed\u00e1v\u00e1te j\u00ed lambda v\u00fdrazy. A ona ka\u017ed\u00e9mu ID ze slovn\u00edku vytahuje hodnotu n\u00e1zvu.<\/p>\n<p><\/p>\n<p>Podobn\u00fdm zp\u016fsobem lze prov\u00e9st i hled\u00e1n\u00ed. P\u0159ed\u00e1v\u00e1 se funkce predik\u00e1tu, kter\u00e1 kontroluje, \u010demu odpov\u00eddaj\u00ed prvky. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/146917b71a35d38a550cd9accf6ab2f7.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Tyto v\u011bci zna\u010dn\u011b zjednodu\u0161uj\u00ed sch\u00e9ma a \u0159e\u0161\u00ed spoustu probl\u00e9m\u016f. <\/p>\n<p><\/p>\n<p>Ale n\u00e1sleduj\u00edc\u00ed probl\u00e9m, se kter\u00fdm jsme se setkali a o kter\u00e9m bych cht\u011bl zm\u00ednit, jsou efektivn\u00ed dotazy. <\/p>\n<p><\/p>\n<ul>\n<li>V ClickHouse nen\u00ed pl\u00e1nova\u010d dotaz\u016f. Opravdu nen\u00ed. <\/li>\n<li>Ale i tak je pot\u0159eba pl\u00e1novat slo\u017eit\u00e9 dotazy. Kdy? <\/li>\n<li>Pokud v dotazu existuje n\u011bkolik join\u016f, kter\u00e9 obalujete do poddotaz\u016f, pak z\u00e1le\u017e\u00ed na po\u0159ad\u00ed, v jak\u00e9m jsou vykon\u00e1v\u00e1ny. <\/li>\n<li>A za druh\u00e9 \u2013 pokud je dotaz distribuovan\u00fd. Proto\u017ee v distribuovan\u00e9m dotazu se vykon\u00e1v\u00e1 pouze nejvnit\u0159n\u011bj\u0161\u00ed poddotaz, zat\u00edmco v\u0161e ostatn\u00ed se p\u0159ed\u00e1v\u00e1 na jeden server, ke kter\u00e9mu jste p\u0159ipojeni, a vykon\u00e1v\u00e1 se tam. Proto pokud m\u00e1te distribuovan\u00e9 dotazy se spoustou join\u016f, je t\u0159eba vybrat po\u0159ad\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p>A dokonce i v jednodu\u0161\u0161\u00edch p\u0159\u00edpadech je n\u011bkdy dobr\u00e9 vykonat pr\u00e1ci pl\u00e1nova\u010de a dotazy m\u00edrn\u011b p\u0159epracovat. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/217d422b429e8b4bc13abbf0efdf6be1.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Tady je p\u0159\u00edklad. Na lev\u00e9 stran\u011b dotaz, kter\u00fd ukazuje top 5 zem\u00ed. A trv\u00e1 to 2,5 sekundy, mysl\u00edm. A na prav\u00e9 stran\u011b je stejn\u00fd dotaz, ale m\u00edrn\u011b p\u0159epracovan\u00fd. M\u00edsto toho, abychom seskupovali podle \u0159et\u011bzce, jsme za\u010dali seskupovat podle kl\u00ed\u010de (int). A to je rychlej\u0161\u00ed. A pak jsme p\u0159ipojili slovn\u00edk k v\u00fdsledku. M\u00edsto 2,5 sekundy dotaz trv\u00e1 1,5 sekundy. To je dobr\u00e9. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/f19fb7d7f0397a3b38b2a3bd179c156b.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Podobn\u00fd p\u0159\u00edklad s p\u0159epracov\u00e1n\u00edm filtr\u016f. Tady je dotaz na Rusko. Trv\u00e1 to 5 sekund. Pokud jej p\u0159ep\u00ed\u0161eme tak, \u017ee budeme op\u011bt porovn\u00e1vat ne \u0159et\u011bzce, ale \u010d\u00edsla s n\u011bjakou sadou t\u011bch kl\u00ed\u010d\u016f, kter\u00e9 se vztahuj\u00ed k Rusku, bude to mnohem rychlej\u0161\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/0c1dd1f317133d1d106ffef85f337079.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Existuje mnoho t\u011bchto trik\u016f. A ty umo\u017e\u0148uj\u00ed v\u00fdrazn\u011b urychlit dotazy, kter\u00e9 si mysl\u00edte, \u017ee ji\u017e funguj\u00ed rychle, nebo naopak funguj\u00ed pomalu. Mohou b\u00fdt je\u0161t\u011b rychlej\u0161\u00ed.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/25b132790b03f239e38237af5e92ab63.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Maxim\u00e1ln\u00ed pr\u00e1ce v distribuovan\u00e9m re\u017eimu. <\/li>\n<li>\u0158azen\u00ed podle minim\u00e1ln\u00edch typ\u016f, jak jsem to d\u011blal podle int. <\/li>\n<li>Pokud existuj\u00ed n\u011bjak\u00e9 joiny, slovn\u00edky, je lep\u0161\u00ed je prov\u00e1d\u011bt a\u017e na sam\u00e9m konci, kdy\u017e m\u00e1te alespo\u0148 \u010d\u00e1ste\u010dn\u011b seskupen\u00e1 data, pak bude operace joinu nebo vol\u00e1n\u00ed slovn\u00edku vyvol\u00e1na m\u00e9n\u011bkr\u00e1t a bude to rychlej\u0161\u00ed. <\/li>\n<li>Nahrazen\u00ed filtr\u016f. <\/li>\n<\/ul>\n<p><\/p>\n<p>Existuj\u00ed i dal\u0161\u00ed techniky, nejen ty, kter\u00e9 jsem demonstroval. A v\u0161echny umo\u017e\u0148uj\u00ed n\u011bkdy v\u00fdrazn\u011b urychlit vykon\u00e1v\u00e1n\u00ed dotaz\u016f.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/175832796bee525909d8875bd723a881.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u0159ejdeme k dal\u0161\u00edmu p\u0159\u00edkladu. Spole\u010dnost X z USA. Co d\u011bl\u00e1? <\/p>\n<p><\/p>\n<p>Byl zde \u00fakol: <\/p>\n<p><\/p>\n<ul>\n<li>Offline spojov\u00e1n\u00ed reklamn\u00edch transakc\u00ed. <\/li>\n<li>Modelov\u00e1n\u00ed r\u016fzn\u00fdch model\u016f spojov\u00e1n\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/0136b71a1205512d86f13e86761146f4.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V \u010dem spo\u010d\u00edv\u00e1 sc\u00e9n\u00e1\u0159?<\/p>\n<p><\/p>\n<p>Obvykl\u00fd n\u00e1v\u0161t\u011bvn\u00edk nav\u0161t\u00edv\u00ed web nap\u0159\u00edklad 20kr\u00e1t za m\u011bs\u00edc z r\u016fzn\u00fdch reklam nebo ob\u010das p\u0159ich\u00e1z\u00ed bez jak\u00fdchkoli inzer\u00e1t\u016f, proto\u017ee si na tuto str\u00e1nku vzpom\u00edn\u00e1. Prohl\u00ed\u017e\u00ed si produkty, d\u00e1v\u00e1 je do ko\u0161\u00edku, vytahuje je z ko\u0161\u00edku. A nakonec n\u011bco koup\u00ed. <\/p>\n<p><\/p>\n<p>Rozumn\u00e9 ot\u00e1zky: \u201eKomu zaplatit za reklamu, pokud je to pot\u0159eba?\u201c a \u201eJak\u00e1 reklama ho ovlivnila, pokud ho ovlivnila?\u201c. Tedy pro\u010d koupil a jak zajistit, aby lid\u00e9 podobn\u00ed tomuto \u010dlov\u011bku tak\u00e9 kupovali?<\/p>\n<p><\/p>\n<p>Aby bylo mo\u017en\u00e9 tento \u00fakol vy\u0159e\u0161it, je t\u0159eba spr\u00e1vn\u011b spojovat ud\u00e1losti, kter\u00e9 se na webu odehr\u00e1vaj\u00ed, tedy n\u011bjak\u00fdm zp\u016fsobem vytv\u00e1\u0159et souvislost mezi nimi. Pot\u00e9 je p\u0159edat k anal\u00fdze do DWH. A na z\u00e1klad\u011b t\u00e9to anal\u00fdzy vytv\u00e1\u0159et modely, komu a jakou reklamu uk\u00e1zat.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/e53ec81ce89150697d0eca4d72886638.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Reklamn\u00ed transakce je soubor vz\u00e1jemn\u011b souvisej\u00edc\u00edch ud\u00e1lost\u00ed u\u017eivatele, kter\u00e9 za\u010d\u00ednaj\u00ed zobrazen\u00edm reklamy, pot\u00e9 se n\u011bco d\u011bje, pak mo\u017en\u00e1 n\u00e1kup a n\u00e1sledn\u011b mohou b\u00fdt dal\u0161\u00ed n\u00e1kupy. Nap\u0159\u00edklad pokud se jedn\u00e1 o mobiln\u00ed aplikaci nebo mobiln\u00ed hru, obvykle je instalace aplikace zdarma, ale pokud se tam d\u011bje n\u011bco dal\u0161\u00edho, mohly by b\u00fdt pot\u0159eba pen\u00edze. A \u010d\u00edm v\u00edce \u010dlov\u011bk utrat\u00ed v aplikaci, t\u00edm je cenn\u011bj\u0161\u00ed. Ale pro to je pot\u0159eba v\u0161e spojit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/a2213613f1ef660409124ef8bcdd4fe3.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Existuje mnoho model\u016f spojov\u00e1n\u00ed. <\/p>\n<p><\/p>\n<p>Nejobl\u00edben\u011bj\u0161\u00ed jsou:<\/p>\n<p><\/p>\n<ul>\n<li>Last Interaction, kde interaction je bu\u010f kliknut\u00ed, nebo zobrazen\u00ed.<\/li>\n<li>First Interaction, tedy to prvn\u00ed, co p\u0159ivedlo \u010dlov\u011bka na web.<\/li>\n<li>Line\u00e1rn\u00ed kombinace \u2013 v\u0161em rovnom\u011brn\u011b. <\/li>\n<li>Z\u00e1nik.<\/li>\n<li>A dal\u0161\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/9523307d557e094d981da5b525ed0b59.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A jak to cel\u00e9 fungovalo p\u016fvodn\u011b? Byl Runtime a Cassandra. Cassandra byla pou\u017e\u00edv\u00e1na jako transak\u010dn\u00ed \u00falo\u017ei\u0161t\u011b, tedy v n\u00ed byly uchov\u00e1v\u00e1ny v\u0161echny souvisej\u00edc\u00ed transakce. A kdy\u017e p\u0159i\u0161la n\u011bjak\u00e1 ud\u00e1lost do Runtime, nap\u0159\u00edklad zobrazen\u00ed n\u011bjak\u00e9 str\u00e1nky nebo n\u011bco jin\u00e9ho, provedla se \u017e\u00e1dost do Cassandry \u2013 existuje tento \u010dlov\u011bk nebo ne. Pot\u00e9 se vyt\u00e1hly transakce, kter\u00e9 se k n\u011bmu vztahovaly. A provedlo se spojen\u00ed.<\/p>\n<p><\/p>\n<p>A pokud byl \u0161\u0165astn\u00fd, \u017ee ve dotazu byl transaction id, bylo to snadn\u00e9. Ale obvykle to \u0161t\u011bst\u00ed nep\u0159ich\u00e1z\u00ed. Proto bylo t\u0159eba naj\u00edt posledn\u00ed transakci nebo transakci s posledn\u00edm kliknut\u00edm atd. <\/p>\n<p><\/p>\n<p>A to v\u0161echno fungovalo velmi dob\u0159e, dokud byl sm\u011brn\u00edk nastaven na posledn\u00ed klik. Proto\u017ee kliknut\u00ed, dejme tomu, 10 milion\u016f denn\u011b, 300 milion\u016f m\u011bs\u00ed\u010dn\u011b, pokud nastav\u00edme okno na m\u011bs\u00edc. A proto\u017ee v Cassand\u0159e mus\u00ed b\u00fdt v\u0161echno v pam\u011bti, aby to fungovalo rychle, proto\u017ee je pot\u0159eba, aby Runtime odpov\u00eddal rychle, bylo pot\u0159eba p\u0159ibli\u017en\u011b 10-15 server\u016f. <\/p>\n<p><\/p>\n<p>Ale kdy\u017e jsme cht\u011bli p\u0159i\u0159azovat transakce k zobrazen\u00ed, hned to nebylo tak z\u00e1bavn\u00e9. Pro\u010d? Je jasn\u00e9, \u017ee je t\u0159eba ulo\u017eit 30kr\u00e1t v\u00edce ud\u00e1lost\u00ed. A podle toho je pot\u0159eba 30kr\u00e1t v\u00edce server\u016f. A vypad\u00e1 to jako astronomick\u00e9 \u010d\u00edslo. M\u00edt a\u017e 500 server\u016f na to, abychom d\u011blali vazbu, p\u0159i\u010dem\u017e v Runtime je server\u016f v\u00fdrazn\u011b m\u00e9n\u011b, to je n\u011bjak\u00e9 nespr\u00e1vn\u00e9 \u010d\u00edslo. A za\u010dali jsme p\u0159em\u00fd\u0161let, co d\u011blat. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/7af35f1ade680232cc83a6887891bc5c.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A dostali jsme se k ClickHouse. Jak to ud\u011blat na ClickHouse? Na prvn\u00ed pohled to vypad\u00e1 jako soubor antipattern\u016f. <\/p>\n<p><\/p>\n<ul>\n<li>Transakce rostou, p\u0159ipojujeme k nim st\u00e1le nov\u00e9 a nov\u00e9 ud\u00e1losti, tj. jsou m\u011bniteln\u00e9, a ClickHouse nepracuje p\u0159\u00edli\u0161 dob\u0159e s m\u011bniteln\u00fdmi objekty. <\/li>\n<li>Kdy\u017e k n\u00e1m p\u0159ijde n\u00e1v\u0161t\u011bvn\u00edk, mus\u00edme z\u00edskat jeho transakce podle kl\u00ed\u010de, podle jeho visit id. To je tak\u00e9 point query, v ClickHouse se to takhle ned\u011bl\u00e1. Obvykle se v ClickHouse prov\u00e1d\u011bj\u00ed velk\u00e9 skeny, a my pot\u0159ebujeme z\u00edskat n\u011bkolik z\u00e1znam\u016f. Tak\u00e9 antipattern. <\/li>\n<li>Nav\u00edc byla transakce v JSON, ale p\u0159episovat jsme necht\u011bli, tak\u017ee jsme cht\u011bli uchov\u00e1vat JSON nestrukturovan\u011b, a pokud bylo pot\u0159eba, n\u011bco z n\u011bj vyt\u00e1hnout. A to je tak\u00e9 antipattern. <\/li>\n<\/ul>\n<p><\/p>\n<p>Tj. soubor antipattern\u016f. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/2473129a5880ee3172b8ab7bf4afc376.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale i tak se poda\u0159ilo vytvo\u0159it syst\u00e9m, kter\u00fd fungoval velmi dob\u0159e. <\/p>\n<p><\/p>\n<p>Co bylo ud\u011bl\u00e1no? Objevil se ClickHouse, kam se nahr\u00e1valy logy rozd\u011blen\u00e9 na z\u00e1znamy. Objevil se attributed servis, kter\u00fd z\u00edsk\u00e1val logy z ClickHouse. Pot\u00e9 pro ka\u017ed\u00fd z\u00e1znam podle visit id z\u00edsk\u00e1val transakce, kter\u00e9 mohly b\u00fdt je\u0161t\u011b neopraven\u00e9, plus snapshoty, tj. ji\u017e spojen\u00e9 transakce, konkr\u00e9tn\u011b v\u00fdsledek p\u0159edchoz\u00ed pr\u00e1ce. Z nich byl navr\u017een logiku, vybr\u00e1na spr\u00e1vn\u00e1 transakce, p\u0159ipojeny nov\u00e9 ud\u00e1losti. Znovu zapsaly do logu. Log se pos\u00edlal zp\u011bt do ClickHouse, tj. to byla neust\u00e1le cyklick\u00e1 soustava. A krom\u011b toho se odes\u00edlal do DWH, aby se tam analyzoval. <\/p>\n<p><\/p>\n<p>Takto to nefungovalo p\u0159\u00edli\u0161 dob\u0159e. Aby byl ClickHouse efektivn\u011bj\u0161\u00ed p\u0159i dotazech podle visit ID, seskupovali jsme tyto dotazy do blok\u016f po 1 000\u20132 000 visit ID a vytahovali jsme pro 1 000\u20132 000 lid\u00ed v\u0161echny transakce. A pak to v\u0161echno fungovalo.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/d920ce231e6e4b5fd5b27ab28f848aed.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pokud se pod\u00edv\u00e1te do ClickHouse, zjist\u00edte, \u017ee tam jsou pouze 3 hlavn\u00ed tabulky, kter\u00e9 to v\u0161echno obsluhuj\u00ed. <\/p>\n<p><\/p>\n<p>Prvn\u00ed tabulka, do kter\u00e9 se ukl\u00e1daj\u00ed logy, p\u0159i\u010dem\u017e logy se ukl\u00e1daj\u00ed prakticky bez dal\u0161\u00edho zpracov\u00e1n\u00ed.<\/p>\n<p><\/p>\n<p>Druh\u00e1 tabulka. P\u0159es materializovan\u00fd pohled se z t\u011bchto log\u016f vytahovaly je\u0161t\u011b neatribovan\u00e9 ud\u00e1losti, tj. nesouvisej\u00edc\u00ed. A prost\u0159ednictv\u00edm materializovan\u00e9ho pohledu se z t\u011bchto log\u016f vytahovaly transakce pro sestaven\u00ed snapshotu. Tj. speci\u00e1ln\u00ed materializovan\u00fd pohled vytv\u00e1\u0159el snapshot, konkr\u00e9tn\u011b posledn\u00ed ulo\u017een\u00fd stav transakce. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/12b0adb691f483923a6c35bb6e4a5017.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zde je naps\u00e1n k\u00f3d v SQL. R\u00e1d bych v n\u011bm komentoval n\u011bkolik d\u016fle\u017eit\u00fdch v\u011bc\u00ed. <\/p>\n<p><\/p>\n<p>Prvn\u00ed d\u016fle\u017eit\u00e1 v\u011bc je mo\u017enost v ClickHouse vyb\u00edrat sloupce z JSON. Tj. ClickHouse m\u00e1 n\u011bkolik metod pro pr\u00e1ci s JSON. Jsou velmi primitivn\u00ed. <\/p>\n<p><\/p>\n<p>visitParamExtractInt umo\u017e\u0148uje vyb\u00edrat atributy z JSON, tj. prvn\u00ed shoda se pou\u017e\u00edv\u00e1. A t\u00edmto zp\u016fsobem je mo\u017en\u00e9 vybrat transaction ID nebo visit ID. To je prvn\u00ed v\u011bc. <\/p>\n<p><\/p>\n<p>Druh\u00e1 v\u011bc \u2013 zde bylo pou\u017eito mazan\u00e9 materializovan\u00e9 pole. Co to znamen\u00e1? To znamen\u00e1, \u017ee ho nem\u016f\u017eete vlo\u017eit do tabulky, tj. nen\u00ed vlo\u017eeno, je vypo\u010d\u00edt\u00e1v\u00e1no a uchov\u00e1v\u00e1no p\u0159i vkl\u00e1d\u00e1n\u00ed. P\u0159i vkl\u00e1d\u00e1n\u00ed ClickHouse tuto pr\u00e1ci ud\u011bl\u00e1 za v\u00e1s. A jakmile se vytahuj\u00ed z JSON ty v\u011bci, kter\u00e9 pozd\u011bji pot\u0159ebujete.<\/p>\n<p><\/p>\n<p>V tomto p\u0159\u00edpad\u011b materializovan\u00fd pohled slou\u017e\u00ed pro neupraven\u00e9 \u0159\u00e1dky. A p\u0159esn\u011b se pou\u017e\u00edv\u00e1 prvn\u00ed tabulka se t\u00e9m\u011b\u0159 syrov\u00fdmi logy. A co d\u011bl\u00e1? Za prv\u00e9, m\u011bn\u00ed po\u0159ad\u00ed, tj. nyn\u00ed je po\u0159ad\u00ed podle visit ID, proto\u017ee pot\u0159ebujeme rychle vyb\u00edrat transakci konkr\u00e9tn\u00ed osoby. <\/p>\n<p><\/p>\n<p>Druh\u00e1 d\u016fle\u017eit\u00e1 v\u011bc je index_granularity. Pokud jste vid\u011bli MergeTree, obvykle je index_granularity ve v\u00fdchoz\u00edm nastaven\u00ed 8 192. Co to znamen\u00e1? Je to parametr \u0159\u00eddkosti indexu. V ClickHouse je index \u0159\u00eddk\u00fd, nikdy neindexuje ka\u017edou z\u00e1znam. D\u011bl\u00e1 to ka\u017ed\u00fdch 8 192. A to je dobr\u00e9, kdy\u017e je t\u0159eba spo\u010d\u00edtat velk\u00e9 mno\u017estv\u00ed dat, ale \u0161patn\u00e9, kdy\u017e jen trochu, proto\u017ee je to velk\u00fd overhead. A pokud sn\u00ed\u017e\u00edte index granularity, sni\u017eujete overhead. Sn\u00ed\u017eit na jedni\u010dku nen\u00ed mo\u017en\u00e9, proto\u017ee m\u016f\u017ee b\u00fdt nedostatek pam\u011bti. Index je v\u017edy uchov\u00e1v\u00e1n v pam\u011bti. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/5fe303061977cad64e6c0333b125d595.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>SnapShot vyu\u017e\u00edv\u00e1 tak\u00e9 n\u011bkter\u00e9 zaj\u00edmav\u00e9 funkce ClickHouse.<\/p>\n<p><\/p>\n<p>Za prv\u00e9, je to AggregatingMergeTree. A v AggregatingMergeTree se ukl\u00e1d\u00e1 argMax, tj. je to stav transakce odpov\u00eddaj\u00edc\u00ed posledn\u00edmu timestampu. Transakce se neust\u00e1le generuj\u00ed pro tohoto n\u00e1v\u0161t\u011bvn\u00edka. A do posledn\u00edho stavu t\u00e9to transakce jsme p\u0159idali ud\u00e1lost a m\u00e1me nov\u00fd stav. Ten op\u011bt p\u0159ist\u00e1l v ClickHouse. A prost\u0159ednictv\u00edm argMax v t\u00e9to materializovan\u00e9 reprezentaci v\u017edy m\u016f\u017eeme z\u00edskat aktu\u00e1ln\u00ed stav.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/d1b87301ab5206a36bf035679f79d35a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Vazba je \"odpojena\" od Runtime. <\/li>\n<li>Ukl\u00e1d\u00e1 a zpracov\u00e1v\u00e1 a\u017e 3 miliardy transakc\u00ed m\u011bs\u00ed\u010dn\u011b. To je o \u0159\u00e1d v\u00edce, ne\u017e bylo v Cassand\u0159e, tj. v typick\u00e9m transak\u010dn\u00edm syst\u00e9mu. <\/li>\n<li>Kluster 2x5 server\u016f ClickHouse. 5 server\u016f a ka\u017ed\u00fd server m\u00e1 replikaci. To je dokonce m\u00e9n\u011b, ne\u017e bylo v Cassand\u0159e, aby se ud\u011blala click based atribuce, zde m\u00e1me atribuci zalo\u017eenou na zobrazen\u00ed. Tj. m\u00edsto zvy\u0161ov\u00e1n\u00ed po\u010dtu server\u016f t\u0159icetkr\u00e1t, poda\u0159ilo se je sn\u00ed\u017eit. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/583779fe9e133df519411e2256401eb3.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A posledn\u00ed p\u0159\u00edklad - to je finan\u010dn\u00ed spole\u010dnost Y, kter\u00e1 analyzovala korelace zm\u011bn cen akci\u00ed. <\/p>\n<p><\/p>\n<p>A \u00fakol byl n\u00e1sleduj\u00edc\u00ed:<\/p>\n<p><\/p>\n<ul>\n<li>Je p\u0159ibli\u017en\u011b 5 000 akci\u00ed. <\/li>\n<li>Ceny jsou zn\u00e1my ka\u017ed\u00fdch 100 milisekund. <\/li>\n<li>Data se hromadila za 10 let. O\u010dividn\u011b pro n\u011bkter\u00e9 spole\u010dnosti v\u00edce, pro n\u011bkter\u00e9 m\u00e9n\u011b. <\/li>\n<li>Celkem p\u0159ibli\u017en\u011b 100 miliard \u0159\u00e1dk\u016f. <\/li>\n<\/ul>\n<p><\/p>\n<p>A bylo pot\u0159eba spo\u010d\u00edtat korelaci zm\u011bn. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/b02051e6cadec8c51e7f6e351ef903f9.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zde jsou dv\u011b akcie a jejich ceny. Pokud jedna roste a druh\u00e1 tak\u00e9 roste, jde o pozitivn\u00ed korelaci, tj. jedna roste, a druh\u00e1 roste. Pokud jedna roste, jako na konci grafu, a druh\u00e1 kles\u00e1, jde o negativn\u00ed korelaci, tj. kdy\u017e jedna roste, druh\u00e1 kles\u00e1. <\/p>\n<p><\/p>\n<p>Analyzov\u00e1n\u00edm t\u011bchto vz\u00e1jemn\u00fdch zm\u011bn je mo\u017en\u00e9 prov\u00e1d\u011bt predikce na finan\u010dn\u00edm trhu. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/31329cf7523eef6a167632d281a634ce.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale \u00fakol je slo\u017eit\u00fd. Co se k tomu d\u011bl\u00e1? M\u00e1me 100 miliard z\u00e1znam\u016f, ve kter\u00fdch je: \u010das, akcie a cena. Nejprve mus\u00edme spo\u010d\u00edtat 100 miliardkr\u00e1t runningDifference algoritmu ceny. RunningDifference je funkce v ClickHouse, kter\u00e1 postupn\u011b po\u010d\u00edt\u00e1 rozd\u00edl mezi dv\u011bma \u0159\u00e1dky. <\/p>\n<p><\/p>\n<p>A pot\u00e9 je t\u0159eba spo\u010d\u00edtat korelaci, p\u0159i\u010dem\u017e korelaci je t\u0159eba spo\u010d\u00edtat pro ka\u017edou dvojici. Pro 5 000 akci\u00ed je to 12,5 milionu p\u00e1r\u016f. A to je hodn\u011b, tj. 12,5kr\u00e1t je t\u0159eba vypo\u010d\u00edtat tuto funkci korelace. <\/p>\n<p><\/p>\n<p>A pokud n\u011bkdo zapomn\u011bl, x a y \u2013 to jsou pr\u016fm\u011brn\u00e1 o\u010dek\u00e1v\u00e1n\u00ed podle vzorku. Tedy je pot\u0159eba spo\u010d\u00edtat nejen ko\u0159eny a sou\u010dty, ale tak\u00e9 uvnit\u0159 t\u011bchto sou\u010dt\u016f dal\u0161\u00ed sou\u010dty. Mus\u00edme prov\u00e9st spoustu v\u00fdpo\u010dt\u016f 12,5 milionkr\u00e1t a je\u0161t\u011b to v\u0161echno seskupit podle hodin. A hodin m\u00e1me tak\u00e9 hodn\u011b. A to v\u0161e mus\u00edme stihnout za 60 sekund. To je vtip. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/567a07f586a824c91671820fe21ec305.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Museli jsme to n\u011bjak stihnout, proto\u017ee to cel\u00e9 fungovalo velmi pomalu, ne\u017e p\u0159i\u0161el ClickHouse.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/3eca1af4c3f25ff4b23fce9b84e16659.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zkou\u0161eli to vypo\u010d\u00edtat na Hadoopu, na Sparku, na Greenplum. A v\u0161echno to bylo bu\u010f velmi pomal\u00e9, nebo drah\u00e9. Tedy bylo mo\u017en\u00e9 n\u011bco spo\u010d\u00edtat, ale pak to bylo n\u00e1ro\u010dn\u00e9. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/37608c7e87ddb9a7d54854643044c369.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A pak p\u0159i\u0161el ClickHouse a v\u0161echno se v\u00fdrazn\u011b zlep\u0161ilo. <\/p>\n<p><\/p>\n<p>P\u0159ipom\u00edn\u00e1m, \u017ee probl\u00e9m m\u00e1me s lokalizac\u00ed dat, proto\u017ee korelace nelze lokalizovat. Nem\u016f\u017eeme \u010d\u00e1st dat ulo\u017eit na jeden server, \u010d\u00e1st na jin\u00fd a spo\u010d\u00edtat, v\u0161echny data mus\u00ed b\u00fdt v\u0161ude. <\/p>\n<p><\/p>\n<p>Co ud\u011blali? P\u016fvodn\u00ed data jsou lokalizovan\u00e1. Na ka\u017ed\u00e9m ze server\u016f jsou ulo\u017eena data o cenov\u00e9 nab\u00eddce ur\u010dit\u00e9ho souboru akci\u00ed. A ty se nep\u0159ekr\u00fdvaj\u00ed. Proto je mo\u017en\u00e9 paraleln\u011b a nez\u00e1visle spo\u010d\u00edtat logReturn, to v\u0161e prob\u00edh\u00e1 zat\u00edm paraleln\u011b a rozlo\u017een\u011b. <\/p>\n<p><\/p>\n<p>D\u00e1le se rozhodli tato data zmen\u0161it, p\u0159i\u010dem\u017e neztratili na v\u00fdrazu. Zmen\u0161it pomoc\u00ed pol\u00ed, tedy pro ka\u017ed\u00e9 \u010dasov\u00e9 obdob\u00ed vytvo\u0159it pole akci\u00ed a pole cen. T\u00edmto zp\u016fsobem zab\u00edraj\u00ed data mnohem m\u00e9n\u011b m\u00edsta. A s nimi se pracuje pon\u011bkud pohodln\u011bji. To jsou t\u00e9m\u011b\u0159 paraleln\u00ed operace, t. j. paraleln\u011b \u010d\u00e1ste\u010dn\u011b po\u010d\u00edt\u00e1me a potom zapisujeme na server. <\/p>\n<p><\/p>\n<p>Pot\u00e9 je mo\u017en\u00e9 je replikovat. P\u00edsmeno \u201er\u201c znamen\u00e1, \u017ee tato data jsme replikovali. Tedy m\u00e1me na v\u0161ech t\u0159ech serverech stejn\u00e1 data \u2013 tyto pole. <\/p>\n<p><\/p>\n<p>A d\u00e1le pomoc\u00ed speci\u00e1ln\u00edho skriptu z tohoto souboru 12,5 milionu korelac\u00ed, kter\u00e9 je t\u0159eba spo\u010d\u00edtat, lze vytvo\u0159it bal\u00ed\u010dky. Tedy 2 500 \u00faloh po 5 000 p\u00e1rech korelac\u00ed. A tuto \u00falohu po\u010d\u00edtat na konkr\u00e9tn\u00edm serveru ClickHouse. V\u0161echna data m\u00e1, proto\u017ee data jsou stejn\u00e1 a on je m\u016f\u017ee vypo\u010d\u00edtat postupn\u011b. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/587c6d587a48f2407dd2687f5423d44a.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Je\u0161t\u011b jednou, jak to vypad\u00e1. Nejprve m\u00e1me v\u0161echna data v takov\u00e9 struktu\u0159e: \u010das, akcie, cena. Pak jsme spo\u010d\u00edtali logReturn, tzn. data ve stejn\u00e9 struktu\u0159e, jen m\u00edsto ceny m\u00e1me u\u017e logReturn. Pot\u00e9 jsme je p\u0159epracovali, tzn. m\u00e1me \u010das a groupArray podle akci\u00ed a podle cen. Zreplikovali jsme. A po tom jsme vygenerovali spoustu \u00faloh a nasadili je do ClickHouse, aby je zpracoval. A to funguje. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/434e7385aebcffcc03d1bd1a2020c8bf.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Na proof of concept \u00fakol \u2013 to byla pod\u00faloha, tzn. vzali jsme m\u00e9n\u011b dat. A celkem na t\u0159ech serverech.<\/p>\n<p><\/p>\n<p>Prvn\u00ed tyto dv\u011b f\u00e1ze: v\u00fdpo\u010det Log_return a zabalen\u00ed do pol\u00ed zabraly p\u0159ibli\u017en\u011b hodinu ka\u017ed\u00e1. <\/p>\n<p><\/p>\n<p>Ale v\u00fdpo\u010det korelace zabral kolem 50 hodin. Ale 50 hodin \u2013 to je m\u00e1lo, proto\u017ee d\u0159\u00edve to u nich trvalo t\u00fddny. To byl velk\u00fd \u00fasp\u011bch. A pokud to spo\u010d\u00edt\u00e1me, tak 70kr\u00e1t za sekundu na tomto clusteru bylo v\u0161echno spo\u010d\u00edt\u00e1no.<\/p>\n<p><\/p>\n<p>Ale nejd\u016fle\u017eit\u011bj\u0161\u00ed je, \u017ee tento syst\u00e9m je prakticky bez \u00fazk\u00fdch m\u00edst, tzn. \u0161k\u00e1luje se prakticky line\u00e1rn\u011b. A oni to ov\u011b\u0159ili. \u00dasp\u011b\u0161n\u011b to \u0161k\u00e1lovali. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/5a595a84897e492b49dccfb0f058b1b8.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Spr\u00e1vn\u00e1 sch\u00e9ma \u2013 polovina \u00fasp\u011bchu. A spr\u00e1vn\u00e1 sch\u00e9ma \u2013 to je pou\u017eit\u00ed v\u0161ech pot\u0159ebn\u00fdch technologi\u00ed ClickHouse. <\/li>\n<li>Summing\/AggregatingMergeTrees \u2013 to jsou technologie, kter\u00e9 umo\u017e\u0148uj\u00ed agregovat nebo po\u010d\u00edtat snapshot stavu jako zvl\u00e1\u0161tn\u00ed p\u0159\u00edpad. A to v\u00fdznamn\u011b zjednodu\u0161uje mnoho v\u011bc\u00ed. <\/li>\n<li>Materializovan\u00e9 pohledy umo\u017e\u0148uj\u00ed obej\u00edt omezen\u00ed na jeden index. Mo\u017en\u00e1 jsem to ne\u0159ekl \u00fapln\u011b jasn\u011b, ale kdy\u017e jsme nahr\u00e1vali logy, tak surov\u00e9 logy byly v tabulce s jedn\u00edm indexem, zat\u00edmco logy atribut\u016f byly v jin\u00e9m tabulce, tzn. stejn\u00e9 data, jen filtrovan\u00e9, ale index byl \u00fapln\u011b jin\u00fd. A\u010dkoli byly t\u00e9m\u011b\u0159 stejn\u00e9 \u00fadaje, m\u011bly jinou strukturu. A materializovan\u00e9 pohledy umo\u017e\u0148uj\u00ed, pokud je to pot\u0159eba, obej\u00edt toto omezen\u00ed ClickHouse. <\/li>\n<li>Sni\u017ete granularitu indexu pro dotazy na konkr\u00e9tn\u00ed data. <\/li>\n<li>A inteligentn\u011b rozd\u011blujte data, sna\u017ete se co nejv\u00edce lokalizovat data uvnit\u0159 serveru. A usilujte o to, aby dotazy tak\u00e9 vyu\u017e\u00edvaly lokalizaci tam, kde je to maxim\u00e1ln\u011b mo\u017en\u00e9. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/c0fc1737986e3f18ed1d3c53df9f0252.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A shrnut\u00ed tohoto kr\u00e1tk\u00e9ho vystoupen\u00ed lze \u0159\u00edci, \u017ee ClickHouse si nyn\u00ed pevn\u011b zajistil m\u00edsto jak na poli komer\u010dn\u00edch datab\u00e1z\u00ed, tak na poli open source datab\u00e1z\u00ed, tedy konkr\u00e9tn\u011b pro analytiku. V\u00fdborn\u011b zapadl do t\u00e9to krajiny. A nav\u00edc, pomalu za\u010d\u00edn\u00e1 vytla\u010dovat ostatn\u00ed, proto\u017ee kdy\u017e m\u00e1te ClickHouse, nepot\u0159ebujete InfiniDB. Vertica m\u016f\u017ee b\u00fdt brzy zbyte\u010dn\u00e1, pokud ud\u011blaj\u00ed norm\u00e1ln\u00ed podporu SQL. Pou\u017e\u00edvejte ho!<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Teorie a praxe pou\u017eit\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexandr Zajcev (2018)\" src=\"\/wp-content\/uploads\/2020\/07\/6e9a65d32eb6de7f9f139a30e7ab0d25.png\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>&#8212;<em>D\u011bkuji za prezentaci! Bylo to velmi zaj\u00edmav\u00e9! Byly n\u011bjak\u00e9 srovn\u00e1n\u00ed s Apache Phoenix?<\/em><\/p>\n<p><\/p>\n<p>- Ne, nesly\u0161el jsem, \u017ee by n\u011bkdo d\u011blil srovn\u00e1n\u00ed. My a Yandex se sna\u017e\u00edme sledovat v\u0161echna srovn\u00e1n\u00ed ClickHouse s r\u016fzn\u00fdmi datab\u00e1zemi. Proto\u017ee pokud se n\u00e1hodou uk\u00e1\u017ee, \u017ee n\u011bco je rychlej\u0161\u00ed ne\u017e ClickHouse, Alexej Milovidov nesp\u00ed v noci a za\u010d\u00edn\u00e1 ho rychle zrychlovat. O takov\u00e9m srovn\u00e1n\u00ed jsem nesly\u0161el. <\/p>\n<p><\/p>\n<ul>\n<li>\n<p><em>(Alexej Milovidov) Apache Phoenix je SQL engine na Hbase. Hbase je p\u0159ev\u00e1\u017en\u011b ur\u010deno pro sc\u00e9n\u00e1\u0159e pr\u00e1ce typu key-value. V ka\u017ed\u00e9m \u0159\u00e1dku m\u016f\u017ee b\u00fdt libovoln\u00fd po\u010det sloupc\u016f s libovoln\u00fdmi n\u00e1zvy. To se d\u00e1 \u0159\u00edct o takov\u00fdch syst\u00e9mech, jako jsou Hbase, Cassandra. A na t\u011bchto syst\u00e9mech t\u011b\u017ek\u00e9 analytick\u00e9 dotazy norm\u00e1ln\u011b nebudou fungovat. Nebo si m\u016f\u017eete myslet, \u017ee funguj\u00ed norm\u00e1ln\u011b, pokud nem\u00e1te \u017e\u00e1dn\u00e9 zku\u0161enosti s ClickHouse.<\/em><\/p>\n<p>\n<\/li>\n<li>\n<p>D\u011bkuji<\/p>\n<p><\/p>\n<ul>\n<li>\n<p><em>Dobr\u00fd den! U\u017e se touto t\u00e9matikou pom\u011brn\u011b dlouho zaj\u00edm\u00e1m, proto\u017ee m\u00e1m analytick\u00fd subsyst\u00e9m. Ale kdy\u017e se pod\u00edv\u00e1m na ClickHouse, m\u00e1m pocit, \u017ee ClickHouse je velmi dob\u0159e vhodn\u00fd pro anal\u00fdzu ud\u00e1lost\u00ed, kter\u00e9 se m\u011bn\u00ed. A pokud pot\u0159ebuji analyzovat spoustu obchodn\u00edch dat s velk\u00fdmi tabulkami, pak, pokud jsem spr\u00e1vn\u011b pochopil, ClickHouse mi p\u0159\u00edli\u0161 nevyhovuje? Zejm\u00e9na pokud se m\u011bn\u00ed. Je to spr\u00e1vn\u011b, nebo existuj\u00ed p\u0159\u00edklady, kter\u00e9 by to mohly vyvr\u00e1tit?<\/em><\/p>\n<p>\n<\/li>\n<li>\n<p>To je pravda. A toto plat\u00ed pro v\u011bt\u0161inu specializovan\u00fdch analytick\u00fdch datab\u00e1z\u00ed. Jsou navr\u017eeny tak, aby fungovaly s jednou nebo v\u00edce velk\u00fdmi tabulkami, kter\u00e9 jsou m\u011bniteln\u00e9, a mnoha mal\u00fdmi, kter\u00e9 se pomalu m\u011bn\u00ed. Tedy ClickHouse neslou\u017e\u00ed jako Oracle, kde m\u016f\u017eete ulo\u017eit v\u0161echno a prov\u00e1d\u011bt velmi slo\u017eit\u00e9 dotazy. Aby bylo mo\u017en\u00e9 ClickHouse efektivn\u011b pou\u017e\u00edvat, je nutn\u00e9 navrhnout sch\u00e9ma tak, aby dob\u0159e fungovalo v ClickHouse. Tedy se vyh\u00fdbat nadm\u011brn\u00e9 normalizaci, pou\u017e\u00edvat slovn\u00edky a sna\u017eit se m\u00edt co nejm\u00e9n\u011b dlouh\u00fdch vazeb. A pokud se sch\u00e9ma takto navrhne, tak podobn\u00e9 obchodn\u00ed \u00fakoly v ClickHouse mohou b\u00fdt vy\u0159e\u0161eny mnohem efektivn\u011bji ne\u017e v tradi\u010dn\u00ed rela\u010dn\u00ed datab\u00e1zi. <\/p>\n<p>\n<\/li>\n<\/ul>\n<p>\n<\/li>\n<\/ul>\n<p><\/p>\n<p><em>D\u011bkuji za prezentaci! M\u00e1m ot\u00e1zku k posledn\u00edmu finan\u010dn\u00edmu p\u0159\u00edpadu. M\u011bli analytiku. Bylo t\u0159eba porovnat, jak se vyv\u00edj\u00ed nahoru a dol\u016f. A ch\u00e1pu to spr\u00e1vn\u011b, \u017ee jste syst\u00e9m postavili pr\u00e1v\u011b pod tuto analytiku? Pokud by z\u00edtra pot\u0159ebovali n\u011bjakou jinou zpr\u00e1vu z t\u011bchto dat, mus\u00ed znovu postavit sch\u00e9ma a na\u010d\u00edst data? Tedy prov\u00e1d\u011bt n\u011bjakou p\u0159edzpracov\u00e1n\u00ed, abychom z\u00edskali dotaz?<\/em><\/p>\n<p><\/p>\n<p>Samoz\u0159ejm\u011b, to je vyu\u017eit\u00ed ClickHouse pro velmi konkr\u00e9tn\u00ed \u00fakol. Ten by tradi\u010dn\u011b mohl b\u00fdt \u0159e\u0161en v r\u00e1mci Hadoopu. Pro Hadoop je to ide\u00e1ln\u00ed \u00fakol. Ale v Hadoopu je to velmi pomal\u00e9. M\u00fdm c\u00edlem je demonstrovat, \u017ee na ClickHouse lze \u0159e\u0161it \u00fakoly, kter\u00e9 se obvykle \u0159e\u0161\u00ed zcela jin\u00fdmi prost\u0159edky, ale mnohem efektivn\u011bji. Je to specificky zam\u011b\u0159en\u00e9 na konkr\u00e9tn\u00ed \u00fakol. Je jasn\u00e9, \u017ee pokud je \u00fakol podobn\u00fd, m\u016f\u017eeme ho \u0159e\u0161it podobn\u00fdm zp\u016fsobem. <\/p>\n<p><\/p>\n<p><em>Rozum\u00edm. \u0158ekl jste, \u017ee zpracov\u00e1n\u00ed trvalo 50 hodin. To za\u010d\u00edn\u00e1 od samotn\u00e9ho za\u010d\u00e1tku, kdy\u017e jste na\u010detli data nebo dostali v\u00fdsledky?<\/em><\/p>\n<p><\/p>\n<p>Ano, ano.<\/p>\n<p><\/p>\n<p><em>Dob\u0159e, d\u011bkuji mnohokr\u00e1t.<\/em><\/p>\n<p><\/p>\n<p>To je na t\u0159\u00edserverov\u00e9m clustru. <\/p>\n<p><\/p>\n<p><em>Zdrav\u00edm! D\u011bkuji za prezentaci! V\u0161echno je velmi zaj\u00edmav\u00e9. Nept\u00e1m se moc na funkce, ale na pou\u017e\u00edv\u00e1n\u00ed ClickHouse z hlediska stability. Do\u0161lo u v\u00e1s k n\u011bjak\u00fdm probl\u00e9m\u016fm, museli jste obnovit? Jak se ClickHouse p\u0159i tom choval? A stalo se, \u017ee v\u00e1m vypadla i replikace? My jsme se nap\u0159\u00edklad setkali s probl\u00e9mem, kdy ClickHouse p\u0159ece jen p\u0159ekro\u010dil sv\u016fj limit a spadl.<\/em><\/p>\n<p><\/p>\n<p>Samoz\u0159ejm\u011b, neexistuj\u00ed dokonal\u00e9 syst\u00e9my. A ClickHouse m\u00e1 tak\u00e9 sv\u00e9 probl\u00e9my. Ale sly\u0161eli jste n\u011bkdy o tom, \u017ee by Yandex.Metrica dlouho nefungovala? Pravd\u011bpodobn\u011b ne. Funguje spolehliv\u011b od roku 2012\u20132013 na ClickHouse. M\u016f\u017eu tak\u00e9 \u0159\u00edct n\u011bco o sv\u00e9 zku\u0161enosti. Nikdy jsme nem\u011bli \u00fapln\u00e9 selh\u00e1n\u00ed. N\u011bjak\u00e9 \u010d\u00e1ste\u010dn\u00e9 probl\u00e9my se mohly objevit, ale nikdy nebyly tak kritick\u00e9, aby v\u00e1\u017en\u011b ovlivnily podnik\u00e1n\u00ed. To se nikdy nestalo. ClickHouse je dost spolehliv\u00fd a nespad\u00e1 n\u00e1hodn\u011b. O to se nemus\u00edte ob\u00e1vat. Nen\u00ed to nic syrov\u00e9ho. To bylo prok\u00e1z\u00e1no mnoha firmami. <\/p>\n<p><\/p>\n<p><em>Dobr\u00fd den! \u0158ekl jste, \u017ee je pot\u0159eba hned dob\u0159e promyslet datov\u00fd model. A co kdy\u017e se to stalo? Data se mi nahr\u00e1vaj\u00ed. Ub\u011bhlo p\u016fl roku a j\u00e1 si uv\u011bdomuji, \u017ee takto \u017e\u00edt nemohu, mus\u00edm data znovu nahr\u00e1t a n\u011bco s nimi ud\u011blat.<\/em> <\/p>\n<p><\/p>\n<p>To samoz\u0159ejm\u011b z\u00e1vis\u00ed na va\u0161\u00ed soustav\u011b. Existuje n\u011bkolik zp\u016fsob\u016f, jak to ud\u011blat prakticky bez p\u0159eru\u0161en\u00ed. Nap\u0159\u00edklad m\u016f\u017eete vytvo\u0159it Materialized View, ve kter\u00e9m vytvo\u0159\u00edte jinou strukturu dat, pokud ji lze jednozna\u010dn\u011b mapovat. Tedy pokud to umo\u017e\u0148uje mapov\u00e1n\u00ed pomoc\u00ed ClickHouse, tj. extrakce n\u011bkter\u00fdch v\u011bc\u00ed, zm\u011bna prim\u00e1rn\u00edho kl\u00ed\u010de, zm\u011bna parti\u0161cionov\u00e1n\u00ed, pak lze vytvo\u0159it Materialized View. Tam p\u0159ep\u00ed\u0161ete sv\u00e9 star\u00e9 data, nov\u00e9 se budou zapisovat automaticky. A pak se jednodu\u0161e p\u0159epnete na pou\u017e\u00edv\u00e1n\u00ed Materialized View, pot\u00e9 p\u0159epnete z\u00e1pis a starou tabulku odstran\u00edte. To je zp\u016fsob bez zastaven\u00ed. <\/p>\n<p><\/p>\n<p><em>D\u011bkuji.<\/em><\/p>\n<p>Zdroj: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/512304\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041d\u0435\u0441\u043c\u043e\u0442\u0440\u044f \u043d\u0430 \u0442\u043e, \u0447\u0442\u043e \u0434\u0430\u043d\u043d\u044b\u0445 \u0441\u0435\u0439\u0447\u0430\u0441 \u043c\u043d\u043e\u0433\u043e \u043f\u043e\u0447\u0442\u0438 \u0432\u0435\u0437\u0434\u0435, \u0430\u043d\u0430\u043b\u0438\u0442\u0438\u0447\u0435\u0441\u043a\u0438\u0435 \u0411\u0414 \u0432\u0441\u0435 \u0435\u0449\u0435 \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u044d\u043a\u0437\u043e\u0442\u0438\u0447\u043d\u044b. \u0418\u0445 \u043f\u043b\u043e\u0445\u043e \u0437\u043d\u0430\u044e\u0442 \u0438 \u0435\u0449\u0435 \u0445\u0443\u0436\u0435 \u0443\u043c\u0435\u044e\u0442 \u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c. \u041c\u043d\u043e\u0433\u0438\u0435 \u043f\u0440\u043e\u0434\u043e\u043b\u0436\u0430\u044e\u0442 &quot;\u0435\u0441\u0442\u044c \u043a\u0430\u043a\u0442\u0443\u0441&quot; \u0441 MySQL \u0438\u043b\u0438 PostgreSQL, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0441\u043f\u0440\u043e\u0435\u043a\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u044b \u043f\u043e\u0434 \u0434\u0440\u0443\u0433\u0438\u0435 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0438, \u043c\u0443\u0447\u0438\u0442\u044c\u0441\u044f \u0441 NoSQL \u0438\u043b\u0438 \u043f\u0435\u0440\u0435\u043f\u043b\u0430\u0447\u0438\u0432\u0430\u0442\u044c \u0437\u0430 \u043a\u043e\u043c\u043c\u0435\u0440\u0447\u0435\u0441\u043a\u0438\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u044f. ClickHouse \u043c\u0435\u043d\u044f\u0435\u0442 \u043f\u0440\u0430\u0432\u0438\u043b\u0430 \u0438\u0433\u0440\u044b \u0438 \u0437\u043d\u0430\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0441\u043d\u0438\u0436\u0430\u0435\u0442 \u043f\u043e\u0440\u043e\u0433 \u0432\u0445\u043e\u0436\u0434\u0435\u043d\u0438\u044f \u0432 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":89879,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-89878","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.3 - aioseo.com -->\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/teoriya-i-praktika-ispolzovaniya-clickhouse-v-realnyh-prilozheniyah-aleksandr-zajczev-2018g\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0422\u0435\u043e\u0440\u0438\u044f \u0438 \u043f\u0440\u0430\u043a\u0442\u0438\u043a\u0430 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f ClickHouse \u0432 \u0440\u0435\u0430\u043b\u044c\u043d\u044b\u0445 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445. \u0410\u043b\u0435\u043a\u0441\u0430\u043d\u0434\u0440 \u0417\u0430\u0439\u0446\u0435\u0432 (2018\u0433) | ProHoster\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/teoriya-i-praktika-ispolzovaniya-clickhouse-v-realnyh-prilozheniyah-aleksandr-zajczev-2018g\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-07-26T23:42:42+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-07-26T23:42:42+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Teorie a praxe pou\u017e\u00edv\u00e1n\u00ed ClickHouse v re\u00e1ln\u00fdch aplikac\u00edch. Alexander Zaytsev (2018) | ProHoster","description":"","canonical_url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/teoriya-i-praktika-ispolzovaniya-clickhouse-v-realnyh-prilozheniyah-aleksandr-zajczev-2018g","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"cs_CZ","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0422\u0435\u043e\u0440\u0438\u044f \u0438 \u043f\u0440\u0430\u043a\u0442\u0438\u043a\u0430 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f ClickHouse \u0432 \u0440\u0435\u0430\u043b\u044c\u043d\u044b\u0445 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445. \u0410\u043b\u0435\u043a\u0441\u0430\u043d\u0434\u0440 \u0417\u0430\u0439\u0446\u0435\u0432 (2018\u0433) | ProHoster","og:url":"https:\/\/prohoster.info\/cs\/blog\/administrirovanie\/teoriya-i-praktika-ispolzovaniya-clickhouse-v-realnyh-prilozheniyah-aleksandr-zajczev-2018g","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-07-26T23:42:42+00:00","article:modified_time":"2020-07-26T23:42:42+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"89878","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 13:02:37","updated":"2026-02-22 15:29:33","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/89878","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=89878"}],"version-history":[{"count":2,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/89878\/revisions"}],"predecessor-version":[{"id":162171,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/89878\/revisions\/162171"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/89879"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=89878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=89878"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=89878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}