Dzidziso uye tsika yekushandisa ClickHouse mumashandisirwo chaiwo. Alexander Zaitsev (2018)

Kunyangwe chokwadi chekuti ikozvino kwave nedata rakawanda kunenge kwese kwese, analytical dhatabhesi achiri akasarudzika. Izvo hazvizivikanwe uye zvakatoipisisa kukwanisa kuzvishandisa nemazvo. Vazhinji vanoramba "vachidya cactus" neMySQL kana PostgreSQL, iyo yakagadzirirwa mamwe mamiriro, kutambura neNoSQL, kana kubhadhara zvakanyanya kune zvekutengesa mhinduro. ClickHouse inoshandura mitemo yemutambo uye inoderedza zvakanyanya chikumbaridzo chekupinda munyika yekuongorora DBMS.

Chirevo kubva kuBackEnd Conf 2018 uye inoburitswa nemvumo yemutauri.

Ndini ani uye nei ndiri kutaura nezve ClickHouse? Ndiri mutungamiriri webudiriro kuLifeStreet, inoshandisa ClickHouse. Zvakare, ndini muvambi weAltinity. Ishamwari yeYandex inosimudzira ClickHouse uye inobatsira Yandex kuita kuti ClickHouse ibudirire. Zvakare wakagadzirira kugovera ruzivo nezve ClickHouse.

Uye ini handisi hama yaPetya Zaitsev. Ndinogara ndichibvunzwa nezvazvo. Kwete, hatisi hama.

"Wese munhu anoziva" iyo ClickHouse:

• Kukurumidza kwazvo,
• Very comfortable
• Inoshandiswa muYandex.

Zvishoma zvishoma zvinozivikanwa mune makambani uye mashandisirwo aanoitwa.

Ini ndichakuudza kuti sei, kupi uye sei ClickHouse inoshandiswa, kunze kweYandex.

Ini ndichakuudza kuti mabasa chaiwo anogadziriswa sei nerubatsiro rweClickHouse mumakambani akasiyana, ndeapi maturusi eClickHouse aunogona kushandisa kumabasa ako, uye kuti akashandiswa sei mumakambani akasiyana.

Ndakatora mienzaniso mitatu inoratidza ClickHouse kubva kumakona akasiyana. Ndinofunga zvichanakidza.

Mubvunzo wekutanga ndewekuti: "Nei tichida ClickHouse?". Zvinoita kunge mubvunzo uri pachena, asi kune mhinduro dzinopfuura imwe pauri.

• Mhinduro yekutanga ndeyekuita. ClickHouse inokurumidza chaizvo. Analytics paClickHouse zvakare inokurumidza. Inogona kushandiswa kazhinji pane chimwe chinhu chinononoka kana kuti chakashata.
• Mhinduro yechipiri imari. Uye chekutanga, mutengo wekuyera. Semuyenzaniso, Vertica ihombe dhatabhesi. Inoshanda chaizvo kana iwe usina akawanda materabytes edata. Asi kana zvasvika kumazana ematerabytes kana petabytes, mutengo werezinesi nerutsigiro unopinda muhuwandu hwakakosha. Uye inodhura. Uye ClickHouse ndeyemahara.
• Mhinduro yechitatu imari yekushandisa. Iyi inzira yakati siyanei. RedShift yakanaka analog. PaRedShift, unogona kuita sarudzo nekukurumidza. Ichashanda nemazvo, asi panguva imwe chete, awa yega, zuva rega rega, uye mwedzi wega wega, iwe uchabhadhara Amazon zvakanyanya, nekuti iri ibasa rinodhura zvakanyanya. Google BigQuery futi. Kana mumwe munhu akaishandisa, anobva aziva kuti ikoko unogona kumhanya zvikumbiro zvakawanda uye kuwana bhiri remazana emadhora kamwe kamwe.

ClickHouse haina matambudziko aya.

ClickHouse inoshandiswa kupi izvozvi? Pamusoro peYandex, ClickHouse inoshandiswa muboka remabhizinesi akasiyana nemakambani.

• Chokutanga pane zvose, iyi ndeye web application analytics, i.e. iyi inyaya yekushandisa yakabva kuYandex.
• Makambani mazhinji eAdTech anoshandisa ClickHouse.
• Makambani mazhinji anoda kuongorora transaction logs kubva kwakasiyana masosi.
• Makambani akati wandei anoshandisa ClickHouse kutarisa kuchengetedza matanda. Ivo vanoaisa kuClickHouse, kuita marepoti, uye kuwana mhedzisiro yavanoda.
• Makambani ari kutanga kuishandisa mukuongorora kwemari, i.e. zvishoma nezvishoma mabhizinesi makuru ari kusvikawo ClickHouse.
• Cloudflare. Kana mumwe munhu akatevera ClickHouse, saka vanogona kunge vakanzwa zita rekambani iyi. Ichi ndicho chimwe chezvakakosha zvekubatsira kubva munharaunda. Uye ivo vane yakakomba ClickHouse yekumisikidza. Semuenzaniso, vakagadzira Kafka Injini yeClickHouse.
• Makambani enharembozha akatanga kushandisa. Makambani akati wandei anoshandisa ClickHouse ingave humbowo pane pfungwa kana yatove mukugadzira.
• Imwe kambani inoshandisa ClickHouse kutarisa maitiro ekugadzira. Ivo vanoedza mamicrocircuits, nyora pasi boka rema paramita, pane anenge zviuru zviviri hunhu. Uye vobva vaongorora kuti mutambo wacho wakanaka here kana kuti wakaipa.
• Blockchain analytics. Kune yakadaro kambani yeRussia seBloxy.info. Uku ndiko kuongorora kweethereum network. Vakaitawo izvi paClickHouse.

Uye saizi haina basa. Kune makambani mazhinji anoshandisa imwe diki server. Uye anovabvumira kugadzirisa matambudziko avo. Uye kunyange makambani akawanda anoshandisa mapoka makuru emaseva akawanda kana akawanda emaseva.

Uye kana iwe ukatarisa marekodhi, zvino:

• Yandex: 500+ maseva, vanochengeta 25 bhiriyoni rekodhi pazuva ipapo.
• LifeStreet: 60 maseva, angangoita 75 bhiriyoni rekodhi pazuva. Kune mashoma maseva, akawanda marekodhi kupfuura muYandex.
• CloudFlare: maseva makumi matatu nematanhatu, anochengetedza 36 bhiriyoni rekodhi pazuva. Ivo vane maseva mashoma uye vanochengeta kunyange data rakawanda.
• Bloomberg: 102 maseva, anenge bhiriyoni ekupinda pazuva. Record chibatiso.

Geographically, izvi zvakare zvakawanda. Iyi mepu pano inoratidza kupisa kwepaClickHouse iri kushandiswa munyika. Russia, China, America zvinomira pachena pano. Kune nyika shoma dzeEurope. Uye kune 4 masumbu.

Uku ndiko kuenzanisa kuongorora, hapana chikonzero chekutsvaga nhamba dzakakwana. Uku ndiko kuongorora kwevashanyi vanoverenga zvinyorwa zvemutauro weChirungu pane webhusaiti yeAltinity, nekuti hapana vanotaura chiRussian ipapo. Uye Russia, Ukraine, Belarus, i.e. chikamu chinotaura chiRussia chenharaunda, ava ndivo vakawanda vashandisi. Ipapo kunouya US neCanada. China iri kushanda zvakanyanya. Pakanga pasina China ipapo mwedzi mitanhatu yapfuura, ikozvino China yakatopfuura Europe uye inoramba ichikura. Old Europe zvakare haisi kure kumashure, uye mutungamiri mukushandiswa kweClickHouse ari, zvisingaite, France.

Sei ndichitaura zvese izvi? Kuratidza kuti ClickHouse iri kuita mhinduro yakajairwa yekuongorora kukuru kwedata uye yatoshandiswa munzvimbo dzakawanda. Kana ukaishandisa, uri mugwara chairo. Kana iwe usati uchiri kuishandisa, saka iwe haugone kutya kuti iwe uchasara uri woga uye hapana achakubatsira, nekuti vazhinji vari kutoita izvi.

Iyi mienzaniso yekushandisa chaiyo ClickHouse mumakambani akati wandei.

• Muenzaniso wekutanga ndeye ad network: kutama kubva kuVertica kuenda kuClickHouse. Uye ini ndinoziva makambani mashoma akachinja kubva kuVertica kana ari munzira yekuchinja.
• Muenzaniso wechipiri ndeyekuchengetera kuchengetedza pa ClickHouse. Uyu muenzaniso wakavakirwa pane antipatterns. Zvese zvisingafanirwe kuitwa muClickHouse pane kuraira kwevagadziri zvinoitwa pano. Uye zvakaitwa zvinobudirira zvekuti zvinoshanda. Uye inoshanda zvirinani pane yakajairwa transactional solution.
• Muenzaniso wechitatu wakagoverwa komputa paClickHouse. Paive nemubvunzo wekuti ClickHouse inogona kubatanidzwa sei muHadoop ecosystem. Ini ndicharatidza muenzaniso wekuti kambani yakaita sei chimwe chinhu chakafanana nemepu yekudzikisa mudziyo paClickHouse, kuchengeta track yenzvimbo yedata, nezvimwe, kuverenga iro risiri-ridiki basa.

• LifeStreet ikambani yeAd Tech ine tekinoroji yese inouya neyekushambadza network.
• Ari kuita ad optimization, programmatic bidhi.
• Yakawanda data: nezve 10 bhiriyoni zviitiko pazuva. Panguva imwecheteyo, zviitiko ipapo zvinogona kukamurwa kuita akati wandei-zviitiko.
• Kune vakawanda vatengi veiyi data, uye ava havasi vanhu chete, zvakanyanya - aya akasiyana maalgorithms ari kuita chirongwa chekubhidha.

Iyo kambani yauya nzira refu uye ine minzwa. Uye ndakataura nezvazvo paHighLoad. Chekutanga, LifeStreet yatama kubva kuMySQL (nechimbo chipfupi paOracle) kuenda kuVertica. Uye unogona kuwana nyaya nezvazvo.

Uye zvese zvaive zvakanaka, asi zvakakurumidza kuve pachena kuti data iri kukura uye Vertica inodhura. Nokudaro, dzimwe nzira dzakasiyana-siyana dzakatsvakwa. Zvimwe zvacho zvakanyorwa pano. Uye kutaura zvazviri, takaita humbowo hwepfungwa kana dzimwe nguva bvunzo dzekuita kweanenge ese dhatabhesi aivepo pamusika kubva ku13th kusvika ku16th gore uye aive akakodzera maererano nekushanda. Uye ndakataurawo nezve zvimwe zvacho paHighLoad.

Basa racho raive rekutama kubva kuVertica pakutanga, nekuti data rakakura. Uye vakakura zvakanyanya nekufamba kwemakore. Vakabva vaenda pasherufu, asi zvakadaro. Uye kufanotaura kukura uku, bhizinesi rinodiwa nehuwandu hwe data pane imwe mhando yeanalytics yaifanira kuitwa, zvaive pachena kuti petabytes ichakurumidza kukurukurwa. Uye kubhadhara petabytes kwatova kudhura zvakanyanya, saka taitsvaga imwe nzira yekuenda nayo.

Kuenda kupi? Uye kwenguva yakareba yakanga isina kujeka kwainofanira kuenda, nokuti kune rumwe rutivi kune zvinyorwa zvekutengeserana, vanoita sekushanda zvakanaka. Vamwe vanoshanda zvakada kufanana neVertica, vamwe zvakanyanya. Asi ese anodhura, hapana chakachipa uye chiri nani hachina kuwanikwa.

Kune rumwe rutivi, kune yakavhurika sosi mhinduro, iyo isiri yakawanda kwazvo, i.e. yeanalytics, inogona kuverengerwa paminwe. Uye ivo vakasununguka kana zvakachipa, asi vanononoka. Uye kazhinji vanoshaya basa rinodiwa uye rinobatsira.

Uye pakanga pasina chekubatanidza chakanaka chiri mumadhata ekutengesa uye ese emahara ari muvhu rakavhurika.

Pakanga pasina chinhu kusvikira, zvisingatarisirwi, Yandex yaburitsa ClickHouse, semashiripiti kubva muheti, setsuro. Uye chaiva chisarudzo chisingatarisirwi, vachiri kubvunza mubvunzo: "Sei?", Asi zvakadaro.

Uye ipapo muzhizha ra2016, takatanga kutarisa kuti ClickHouse chii. Uye zvakazoitika kuti dzimwe nguva inogona kukurumidza kupfuura Vertica. Takaedza mamiriro akasiyana pane zvikumbiro zvakasiyana. Uye kana mubvunzo wakashandisa tafura imwe chete, kureva kuti, pasina anojoinha (join), ipapo ClickHouse yakakurumidza zvakapetwa kaviri seVertica.

Ndakanga ndisina usimbe uye ndakatarisa Yandex bvunzo rimwe zuva. Zvakafanana ipapo: ClickHouse inomhanya zvakapetwa kaviri seVertica, saka vanowanzo taura nezvazvo.

Asi kana paine anojoinha mumibvunzo, ipapo zvese zvinobuda kwete zvisina kujeka. Uye ClickHouse inogona kunonoka zvakapetwa kaviri seVertica. Uye kana iwe ukagadzirisa zvishoma chikumbiro uye uchinyora patsva, zvino dzinenge dzakaenzana. Kusaipa. Uye vakasununguka.

Uye mushure mekugamuchira mhinduro dzebvunzo, uye nekuzvitarisa kubva kumakona akasiyana, LifeStreet yakaenda kuClickHouse.

Iri igore rechi16, ndinokuyeuchidzai. Zvaiita sejee pamusoro pembeva dzaichema nekuzvibaya, asi dzakaramba dzichidya cactus. Uye izvi zvakatsanangurwa zvakadzama, pane vhidhiyo nezve izvi, nezvimwe.

Naizvozvo, ini handisi kuzotaura nezvazvo zvakadzama, ini ndichangotaura nezve mhedzisiro uye zvishoma zvinonakidza zvinhu zvandisina kutaura nezvazvo ipapo.

Mibairo yacho ndeiyi:

• Kubudirira kutama uye kupfuura gore iyo system iri kutoshanda mukugadzira.
• Kubudirira uye kushanduka kwakawedzera. Pazvinyorwa zvemabhiriyoni gumi zvataigona kuchengeta pazuva uyezve kwenguva pfupi, LifeStreet ikozvino inochengetedza 10 bhiriyoni zvinyorwa pazuva uye inogona kuita izvi kwemwedzi mitatu kana kupfuura. Kana iwe ukaverenga pakakwirira, saka izvi zvinosvika miriyoni zviitiko pasekondi. Anopfuura miriyoni SQL mibvunzo pazuva inosvika mune ino sisitimu, kunyanya kubva kumarobhoti akasiyana.
• Zvisinei nekuti mamwe maseva akashandiswa kuClickHouse pane Vertica, ivo vakachengetedza pahardware, nekuti madhisiki eSAS anodhura akashandiswa muVertica. ClickHouse yakashandisa SATA. Uye nei? Nekuti muVertica isa inopindirana. Uye kuwiriranisa kunoda kuti madhisiki arege kuderera zvakanyanya, uye zvakare kuti network hainonoke zvakanyanya, ndiko kuti, kushanda kunodhura. Uye muClickHouse pinza ndeye asynchronous. Uyezve, iwe unogona kugara uchinyora zvese munharaunda, hapana mari yekuwedzera yeizvi, saka data inogona kuiswa muClickHouse nekukurumidza kupfuura muVertika, kunyangwe painononoka dhiraivha. Uye kuverenga kwakangofanana. Kuverenga paSATA, kana vari muRAID, saka izvi zvese nekukurumidza zvakakwana.
• Hazvina kuganhurirwa nerezinesi, kureva 3 petabytes yedata mumaseva makumi matanhatu (maseva makumi maviri ikopi imwe chete) uye 60 matiriyoni marekodhi mune chokwadi uye aggregations. Hapana chakadai chaigona kupihwa kuVertica.

Ini zvino ndinotendeukira kuzvinhu zvinoshanda mumuenzaniso uyu.

• Chekutanga chirongwa chinoshanda. Zvakawanda zvinoenderana ne schema.
• Yechipiri inobudirira SQL chizvarwa.

Yakajairika OLAP mubvunzo isarudzo. Mamwe makoramu anoenda kumapoka, mamwe emakoramu anoenda kuunganidza mabasa. Pane apo, iyo inogona kumiririrwa sechidimbu checube. Boka rose rinogona kutorwa sechiratidzo. Uye ndicho chikonzero ichinzi multivariate data analysis.

Uye kazhinji izvi zvinoenzanisirwa muchimiro chenyeredzi-chirongwa, kana paine chepakati chokwadi uye maitiro eichi chokwadi pamativi, pamwe nemwaranzi.

Uye maererano nekugadzirwa kwemuviri, kuti inokwana sei patafura, ivo vanowanzoita inomiririra yakajairwa. Iwe unogona kuita denormalize, asi inodhura padhisiki uye haina kunyatso shanda pamibvunzo. Naizvozvo, ivo vanowanzo gadzira yakajairwa inomiririra, kureva tafura yechokwadi uye akawanda, akawanda dimension matafura.

Asi hazvishande muClickHouse. Pane zvikonzero zviviri:

• Chekutanga ndechekuti ClickHouse haina kunyatso kujoinha, i.e. kune majoini, asi akaipa. Nepo zvakaipa.
• Chechipiri ndechekuti matafura haana kuvandudzwa. Kazhinji mumahwendefa aya, ayo akapoteredza nyeredzi-dunhu, chimwe chinhu chinoda kuchinjwa. Semuenzaniso, zita remutengi, zita rekambani, nezvimwe. Uye hazvishande.

Uye pane nzira yekubuda nayo muClickHouse. kunyange maviri:

• Chekutanga kushandiswa kwemaduramazwi. Ekunze Dictionaries ndiyo inobatsira 99% kugadzirisa dambudziko nenyeredzi-schema, ine zvigadziriso uye zvichingodaro.
• Chechipiri ndiko kushandiswa kwezvirongwa. Arrays inobatsirawo kubvisa majoini uye matambudziko ne normalization.

• Hapana kubatana kunodiwa.
• Upgradable. Kubva munaKurume 2018, mukana usina kunyorwa wakaonekwa (hauzowana izvi muzvinyorwa) kugadzirisa maduramazwi zvishoma, kureva izvo zvinyorwa zvachinja. Chaizvoizvo, yakafanana netafura.
• Nguva dzose mundangariro, saka majoini neduramazwi anoshanda nekukurumidza kupfuura dai iri tafura iri padhisiki uye haisati iri chokwadi chekuti iri mu cache, kazhinji kwete.

• Iwe haudiwo majoini.
• Iyi compact 1-kune-yakawanda inomiririra.
• Uye mumaonero angu, arrays akagadzirirwa geeks. Aya ndiwo mabasa e lambda uye zvichingodaro.

Izvi hazvisi zvemashoko matsvuku. Uku kuita kwakasimba kwazvo kunokubvumira kuti uite zvinhu zvakawanda nenzira yakapusa uye inoyevedza.

Mienzaniso yakajairika inobatsira kugadzirisa mitsara. Iyi mienzaniso iri nyore uye yakajeka zvakakwana:

• Tsvaga nema tags. Kana iwe uine hashtag ipapo uye uchida kutsvaga mamwe mapositi ne hashtag.
• Tsvaga nemakey-value pairs. Kune zvakare humwe hunhu hune kukosha.
• Kuchengeta mazita emakiyi aunoda kushandura kuita chimwe chinhu.

Ese aya mabasa anogona kugadziriswa pasina arrays. Mategi anogona kuiswa mune imwe mutsara uye anosarudzwa neakajairika kutaura kana mune yakaparadzana tafura, asi ipapo iwe unofanirwa kuita majoini.

Uye muClickHouse, haufanire kuita chero chinhu, zvinokwana kutsanangura tambo yakarongwa yehashtag kana kugadzira nested chimiro chekiyi-yakakosha masisitimu.

Nested chimiro chinogona kunge chisiri iro zita rakanakisa. Aya matinji maviri ane chikamu chakafanana muzita uye humwe hunhu hunoenderana.

Uye zviri nyore kutsvaga ne tag. Iva nebasa has, iyo inotarisa kuti rondedzero ine chinhu. Wese munhu akawana zvese zvine chekuita nemusangano wedu.

Kutsvaga ne subid kunonyanya kunetsa. Tinofanira kutanga tawana index yekiyi, tozotora chinhu neindekisi iyi totarisa kuti kukosha uku ndiko kwatinoda. Nekudaro, iri nyore uye compact.

Chirevo chenguva dzose chaungada kunyora kana ukachichengeta chiri mumutsara mumwe, chingava, chekutanga, chakaoma. Uye, chechipiri, yakashanda kwenguva yakareba kupfuura mitsara miviri.

Mumwe muenzaniso. Iwe une array paunochengeta ID. Uye iwe unogona kuzvishandura mumazita. Function arrayMap. Iri ndiro rakajairika lambda basa. Iwe unopfuura mazwi e lambda ipapo. Uye anoburitsa kukosha kwezita reID yega yega kubva muduramazwi.

Kutsvaga kunogona kuitwa nenzira imwechete. A predicate function inopfuudzwa inotarisa kuti zvinhu zvinopindirana nei.

Izvi zvinhu zvinorerutsa zvakanyanya dunhu uye kugadzirisa boka rematambudziko.

Asi dambudziko rinotevera ratiri kutarisana naro, uye randinoda kutaura, mibvunzo inoshanda.

• ClickHouse haina query planner. Kwete.
• Zvakadaro, mibvunzo yakaoma ichiri kufanira kurongwa. Muzviitiko zvipi?
• Kana paine akawanda anojoinha mumubvunzo, unoaputira mune subselects. Uye kurongeka kwavanoitwa nenhau.
• Uye yechipiri - kana chikumbiro ichigoverwa. Nekuti mumubvunzo wakagoverwa, iyo yemukati chete subselect ndiyo inoitwa yakagovaniswa, uye zvimwe zvese zvinopfuudzwa kune imwe sevha yawakabatanidza kwairi uye kuurayiwa ipapo. Naizvozvo, kana iwe wakagovera mibvunzo ine akawanda majoini (join), saka unofanirwa kusarudza iyo odha.

Uye kunyange mumatambudziko akareruka, dzimwe nguva zvinodiwawo kuita basa remugadziri uye kunyorazve mibvunzo zvishoma.

Heino muenzaniso. Kuruboshwe kune mubvunzo unoratidza nyika 5 dzepamusoro. Uye zvinotora 2,5 masekondi, mumaonero angu. Uye kurutivi rworudyi, mubvunzo mumwe chete, asi wakanyorwa zvishoma. Panzvimbo pekuunganidza netambo, takatanga kuunganidza nekiyi (int). Uye inokurumidza. Uye takabva tabatanidza duramazwi kune zvakabuda. Panzvimbo pe2,5 seconds, chikumbiro chinotora 1,5 seconds. Izvi zvakanaka.

Muenzaniso wakafanana nekunyora patsva mafirita. Heino chikumbiro cheRussia. Inomhanya kwemasekonzi mashanu. Kana tikanyora zvakare nenzira yekuti tinofananidza zvakare kwete tambo, asi nhamba dzine imwe seti yeaya makiyi ane chekuita neRussia, zvino ichave yakakurumidza.

Kune akawanda manomano akadaro. Uye ivo vanokutendera iwe kuti ikurumidze kumhanyisa mibvunzo yaunofunga kuti yave kutomhanya nekukurumidza, kana, neimwe nzira, ichimhanya zvishoma nezvishoma. Vanogona kugadzirwa kunyange nekukurumidza.

• Maximum basa mune yakagoverwa mode.
• Kuronga nemhando shoma, sezvandakaita nema ints.
• Kana paine chero majoini (join), maduramazwi, saka zviri nani kuzviita senge yekupedzisira sarudzo, kana iwe uchinge uchinge uchinge uine data rinenge rakakamurwa zvishoma, ipapo kujoinha kushanda kana kudana kweduramazwi kuchadaidzwa nguva shoma uye ichakurumidza kukurumidza. .
• Kutsiva mafirita.

Kune mamwe matekiniki, uye kwete iwo chete andakaratidza. Uye vese vanogona dzimwe nguva kukurumidzira kuita kwemibvunzo.

Ngatienderei kumuenzaniso unotevera. Kambani X kubva kuUSA. Arikuitei?

Paiva nebasa:

• Offline kubatanidza yekushambadzira kwekushambadzira.
• Kuenzanisira maitiro akasiyana anosunga.

Chii chiri chiitiko?

Mushanyiwo zvake anouya kusaiti, semuenzaniso, ka20 pamwedzi kubva kune akasiyana ads, kana kungodaro dzimwe nguva anouya asina chero ads, nekuti anorangarira ino saiti. Inotarisa zvimwe zvigadzirwa, inozviisa mudengu, inozvibudisa kubva mudengu. Uye, pakupedzisira, chimwe chinhu chinotenga.

Mibvunzo inonzwisisika: "Ndiani anofanira kubhadhara kushambadzira, kana zvichidiwa?" uye "Ndezvipi kushambadzira zvakamukurudzira, kana aripo?". Kureva kuti akatengerei uye kuti vanhu vakaita seuyu vatengewo sei?

Kuti ugadzirise dambudziko iri, unofanirwa kubatanidza zviitiko zvinoitika pawebhusaiti nenzira kwayo, ndiko kuti, neimwe nzira kuvaka hukama pakati pavo. Vanobva vatumirwa kunoongororwa kuDWH. Uye zvichibva pakuongorora uku, gadzira mhando dzekuti ndiani uye chii ads kuratidza.

A ad transaction seti yezviitiko zvine chekuita nemushandisi zvinotanga kubva pakuratidza kushambadzira, chimwe chinhu chinoitika, pamwe kutenga, uyezve panogona kunge paine kutenga mukati mekutenga. Semuenzaniso, kana iyi iri mobile application kana nharembozha, saka kazhinji kuiswa kwechikumbiro kunoitika mahara, uye kana chimwe chinhu chaitwa ipapo, mari inogona kudiwa pane izvi. Uye iyo yakawanda iyo munhu anopedza mukunyorera, iyo inonyanya kukosha. Asi nokuda kweizvi unoda kubatanidza zvose.

Kune akawanda anosunga modhi.

Anonyanya kufarirwa ndeaya:

• Yekupedzisira Kudyidzana, uko kupindirana kungave kudzvanya kana kufungidzira.
• Kutanga Kudyidzana, i.e. chinhu chekutanga chakaunza munhu kune saiti.
• Musanganiswa wemutsara - zvese zvakaenzana.
• Attenuation.
• Zvichingoenda zvakadaro.

Uye zvose zvakashanda sei pakutanga? Paiva neRuntime naCassandra. Cassandra yakashandiswa sekuchengetedza kwekutengeserana, i.e. zvese zvine chekuita nekutengeserana zvakachengetwa mairi. Uye kana chimwe chiitiko chikauya muRuntime, semuenzaniso, kuratidza imwe peji kana chimwe chinhu, ipapo chikumbiro chakaitwa kuna Cassandra - pane munhu akadaro here kana kwete. Ipapo kutengeserana kwakanangana nazvo kwakawanikwa. Uye kubatana kwakagadzirwa.

Uye kana iri rombo rakanaka kuti chikumbiro chine id yekutengeserana, saka zviri nyore. Asi kazhinji hapana rombo rakanaka. Naizvozvo, zvaive zvakafanira kuwana kutengeserana kwekupedzisira kana kutengeserana nekudzvanya kwekupedzisira, nezvimwe.

Uye zvese zvakashanda nemazvo chero kusungirirwa kwaive kusvika pakudzvanya kwekupedzisira. Nekuti pane, toti, miriyoni gumi yekudzvanya pazuva, 10 miriyoni pamwedzi, kana isu takaisa hwindo kwemwedzi. Uye sezvo muCassandra inofanirwa kunge iri mundangariro kuitira kuti imhanye nekukurumidza, nekuti iyo Runtime inoda kupindura nekukurumidza, yakatora nezve 300-10 maseva.

Uye pavakada kubatanidza kutengeserana kune chiratidziro, zvakabva zvaita kunge zvisina kunakidza. Uye nei? Zvinogona kuonekwa kuti ka30 zviitiko zvakawanda zvinoda kuchengetwa. Uye, maererano, unoda makumi matatu mamwe maseva. Uye zvinoitika kuti iyi imhando yechimiro chenyeredzi. Kuchengeta anosvika mazana mashanu maseva kuitira kuti aite kubatanidza, zvisinei nekuti kune mashoma mashoma maseva muRuntime, saka iyi imhando yemhando isiriyo. Uye vakatanga kufunga zvokuita.

Uye takaenda kuClickHouse. Uye kuti ungazviita sei paClickHouse? Pakutanga kuona, zvinoita sekunge iyi seti ye anti-patterns.

• Iko kutengeserana kunokura, isu tinokochekera zviitiko zvakawanda kwairi, i.e. inoshanduka, uye ClickHouse haishande zvakanyanya nezvinhu zvinochinjika.
• Kana mushanyi auya kwatiri, tinofanira kuburitsa kutengeserana kwake nekiyi, neid yake yekushanya. Uyu zvakare mubvunzo wepoindi, ivo havaite izvozvo muClickHouse. Kazhinji ClickHouse ine hombe ... scans, asi pano isu tinoda kuwana mamwe marekodhi. Uyewo antipattern.
• Mukuwedzera, kutengeserana kwaiva mujson, asi vakanga vasingadi kuinyora zvakare, saka vaida kuchengeta json nenzira isina kurongeka, uye kana zvichidiwa, dhonza chimwe chinhu kubva mairi. Uye ichi zvakare antipattern.

Ndiko kuti, seti ye antipatterns.

Asi zvakadaro zvakazoitika kuita system inoshanda chaizvo.

Chii chakaitwa? ClickHouse yakaonekwa, umo matanda akakandwa, akakamurwa kuita zvinyorwa. Sevhisi inonzi yakaonekwa yakagamuchira matanda kubva kuClickHouse. Mushure meizvozvo, pakupinda kwega kwega, nekushanya id, ndakagamuchira kutengeserana kungave kusati kwagadziriswa uye pamwe nemifananidzo, i.e. kutengeserana kwatobatana, zvinova mhedzisiro yebasa rapfuura. Ini ndakatogadzira pfungwa kubva kwavari, ndakasarudza chaiyo kutengeserana, yakabatanidza zviitiko zvitsva. Logged zvakare. Iyo log yakadzokera kuClickHouse, kureva kuti inogara ichitenderera system. Uyezve, ndakaenda kuDWH kunoiongorora ikoko.

Yakanga iri muchimiro ichi kuti haina kushanda zvakanaka. Uye kuita kuti zvive nyore kuClickHouse, pakange paine chikumbiro nekushanya id, vakaisa zvikumbiro izvi muzvivharo zve1-000 maID ekushanya uye vakaburitsa zvese zvekutengesa kwevanhu chiuru. Zvino zvose zvakashanda.

Kana iwe ukatarisa mukati meClickHouse, saka kune matatu chete matafura makuru anoshanda zvese izvi.

Tafura yekutanga iyo matanda anoiswa, uye matanda anoiswa anenge asina kugadziridzwa.

Tafura yechipiri. Kuburikidza nekuona kwenyama, zviitiko zvisati zvave zvichinzi zvakaitwa, kureva, zvisingabatanidzi, zvakarumwa kubva mumatanda aya. Uye kuburikidza nekuona kwenyama, kutengeserana kwakaburitswa kubva mumatanda aya kuvaka mufananidzo. Kureva kuti, akakosha akaitwa maonero akagadzira snapshot, inova iyo yekupedzisira yakaunganidzwa mamiriro ekutengeserana.

Heino chinyorwa chakanyorwa muSQL. Ndinoda kutaura pamusoro pezvinhu zvishoma zvakakosha mariri.

Chinhu chekutanga chakakosha kugona kuburitsa makoramu neminda kubva json muClickHouse. Kureva kuti, ClickHouse ine dzimwe nzira dzekushanda nejson. Ivo zvikuru, zvikuru vekare.

shanyiraParamExtractInt inokutendera kuti utore hunhu kubva kujson, i.e. yekutanga kurova inoshanda. Uye nenzira iyi unogona kuburitsa id yekutengeserana kana kushanyira id. Nguva ino.

Chechipiri, munda unonyengera wakagadzirwa unoshandiswa pano. Zvinorevei? Izvi zvinoreva kuti haugone kuiisa mutafura, i.e. haina kuiswa, inoverengwa uye inochengetwa pakuiswa. Kana uchinamira, ClickHouse inokuitira basa racho. Uye izvo zvaunoda gare gare zvakatoburitswa kunze kwejson.

Muchiitiko ichi, maonero enyama ndeyemitsetse yakasvibirira. Uye tafura yekutanga ine matanda angangoita mbishi inongoshandiswa. Uye anoitei? Chekutanga, inoshandura marongero, i.e. kuronga ikozvino kunoenda nekushanya id, nekuti isu tinofanirwa kukurumidza kuburitsa kutengeserana kwake kune mumwe munhu.

Chechipiri chakakosha chinhu index_granularity. Kana iwe wakaona MergeTree, inowanzoita 8 neiyo default index_granularity. Chii? Iyi ndiyo index sparseness parameter. MuClickHouse index yacho ishoma, haimbofa yakanyora zvese zvinopinda. Inoita izvi zvose 192 8. Uye izvi zvakanaka kana data yakawanda inotarisirwa kuverengwa, asi yakaipa kana zvishoma, nokuti kune hukuru hwepamusoro. Uye kana tikaderedza index granularity, saka tinoderedza pamusoro. Haikwanisi kuderedzwa kusvika kune imwe, nokuti panogona kunge pasina chiyeuchidzo chakakwana. Iyo index inogara yakachengetwa mundangariro.

Snapshot inoshandisawo zvimwe zvinonakidza ClickHouse maficha.

Kutanga, iri AggregatingMergeTree. Uye AggregatingMergeTree zvitoro argMax, kureva kuti iyi ndiyo mamiriro ekutengeserana anoenderana nekupedzisira nguva. Transactions inogadzirwa nguva dzese kune akapihwa mushanyi. Uye mune yekupedzisira mamiriro ekutengeserana uku, takawedzera chiitiko uye isu tine nyika itsva. Yakarova ClickHouse zvakare. Uye kuburikidza ne argMax mune iyi yenyama maonero, tinogona kugara tichiwana mamiriro aripo.

• Iko kusungirirwa "kwakaparadzaniswa" kubva kuRuntime.
• Anosvika mabhiriyoni matatu ekutengeserana pamwedzi anochengetwa uye anogadziriswa. Uku kurongeka kwehukuru kupfuura zvazvaive muCassandra, i.e. mune yakajairika transaction system.
• Cluster ye2x5 ClickHouse maseva. 5 maseva uye sevha yega yega ine replica. Izvi zvakatoderera pane zvazvaive muCassandra kuitira kuti tinya-based attribution, uye pano isu tine fungidziro yakavakirwa. Ndiko kuti, pachinzvimbo chekuwedzera huwandu hwemaseva ne30 nguva, vakakwanisa kudzideredza.

Uye muenzaniso wekupedzisira ikambani yezvemari Y, iyo yakaongorora kuwirirana kwekuchinja kwemitengo.

Uye basa raive:

• Kune masheya angangoita 5.
• MaQuotes ega ega 100 milliseconds anozivikanwa.
• Iyo data yakaunganidzwa pamusoro pemakore gumi. Sezviri pachena, kune mamwe makambani akawanda, kune mamwe mashoma.
• Kune mitsara inosvika mabhiriyoni zana pamwe chete.

Uye zvaive zvakafanira kuverenga kuwirirana kwekuchinja.

Heano mastock maviri uye makotesheni avo. Kana imwe ichikwira uye imwe ichikwira, zvino uku ndiko kuwirirana kwakanaka, kureva kuti imwe inokwira uye imwe inokwira. Kana imwe ichikwira, sepakupera kwegirafu, uye imwe ichidzika, zvino uku ndiko kuwirirana kusina kunaka, i.e. kana imwe ichisimuka, imwe inowira.

Kuongorora shanduko idzi, munhu anogona kuita fungidziro mumusika wemari.

Asi basa racho rakaoma. Chii chiri kuitirwa izvi? Tine 100 bhiriyoni marekodhi ane: nguva, stock, uye mutengo. Isu tinofanirwa kuverenga yekutanga 100 bhiriyoni nguva iyo inomhanyaKusiyana kubva kune mutengo algorithm. RunningDifference ibasa riri muClickHouse iro rinoverengera mutsauko pakati petambo mbiri.

Uye mushure meizvi, iwe unoda kuverenga kuwirirana, uye kuwirirana kunofanira kuverengerwa kune imwe neimwe. Pazvikamu zve5, vaviri vaviri vane mamiriyoni gumi nemaviri. Uye izvi zvakawanda, i.e. 000 nguva dzinofanira kuverenga chete yakadaro yekubatanidza basa.

Uye kana mumwe munhu akanganwa, saka ͞x uye ͞y ichekimate. sampuli tarisiro. Kureva kuti, zvinodikanwa kwete chete kuverenga midzi uye sums, asiwo imwe summary mukati mezviverengero izvi. Boka rekuverenga rinoda kuitwa 12,5 miriyoni nguva, uye kunyange kuunganidzwa nemaawa. Tinewo maawa akawanda. Uye iwe unofanirwa kuzviita mumasekonzi makumi matanhatu. Ijoke.

Zvaive zvakakodzera kuve nenguva neimwe nzira, nekuti zvese izvi zvakashanda zvakanyanya, zvishoma nezvishoma ClickHouse isati yauya.

Vakaedza kuzviverenga paHadoop, paSpark, paGreenplum. Uye zvese izvi zvainonoka kana kudhura. Ndiko kuti, zvaigoneka neimwe nzira kuverenga, asi zvino yaidhura.

Uye ipapo ClickHouse akauya uye zvinhu zvakava nani.

Ndinokuyeuchidza kuti isu tine dambudziko nenharaunda yedata, nekuti kuwirirana hakugone kuiswa munharaunda. Hatigone kuisa imwe data pane imwe sevha, imwe pane imwe uye kuverenga, isu tinofanirwa kuve nedata rese kwese kwese.

Vakaitei? Pakutanga, iyo data inowanikwa munzvimbo. Sevha yega yega inochengeta data pamitengo yeimwe seti yemashare. Uye hazvipindirane. Naizvozvo, zvinokwanisika kuverenga logReturn mune parallel uye zvakasununguka, zvese izvi zvinoitika kusvika zvino zvakaenzana uye zvakagoverwa.

Ipapo takasarudza kuderedza idzi data, tisingarasikirwe nekutaura. Deredza kushandisa arrays, i.e. kwenguva imwe neimwe yenguva, gadzira hurongwa hwemasheya uye huwandu hwemitengo. Naizvozvo, zvinotora nzvimbo shoma yedata. Uye ivo vari nyore zvishoma kushanda navo. Aya anenge maoperation akafanana, i.e. isu tinoverenga zvishoma mune parallel tozonyorera kune server.

Mushure meizvozvo, inogona kudzokororwa. Vara "r" rinoreva kuti isu takadzokorora iyi data. Ndiko kuti, isu tine data yakafanana pane ese matatu maseva - aya ndiwo arrays.

Uyezve neyakakosha script kubva pane iyi seti ye12,5 miriyoni correlations inoda kuverengerwa, unogona kugadzira mapakeji. Ndiko kuti, zviuru zviviri nemazana mashanu emabasa ane zviuru zvishanu peya dzekubatanidza. Uye basa iri rinofanira kuverengerwa pane chaiyo ClickHouse server. Ane data rese, nekuti iyo data yakafanana uye anogona kuiverenga sequentially.

Zvekare, izvi ndizvo zvinotaridzika. Kutanga, isu tine data yese muchimiro ichi: nguva, migove, mutengo. Ipapo takaverenga logReturn, i.e. data yechimiro chimwe chete, asi panzvimbo yemutengo tatova nelogReturn. Vakabva vaitwa patsva, kureva kuti takawana nguva negroupArray yemastock nemitengo. Sreplicated. Uye mushure meizvozvo, takagadzira boka remabasa uye tikaapa kuClickHouse kuti iverenge. Uye inoshanda.

Pauchapupu hwepfungwa, basa racho rakanga riri basa duku, kureva kuti, data shoma yakatorwa. Uye maseva matatu chete.

Matanho maviri ekutanga: kuverenga Log_return uye kuputira muzvikamu zvakatora inenge awa.

Uye kuverenga kwekuwirirana kunenge maawa makumi mashanu. Asi maawa makumi mashanu haana kukwana, nokuti vaimboshanda kwemavhiki. Yakanga iri budiriro huru. Uye kana ukaverenga, saka ka50 pasekondi zvese zvakaverengerwa pasumbu iri.

Asi chinonyanya kukosha ndechekuti iyi sisitimu inenge isina mabhodhoro, i.e., inoyera inenge mutsara. Uye vakazviongorora. Zvakabudirira kuwedzera.

• Chirongwa chakakodzera hafu yehondo. Uye chirongwa chakakodzera ndiko kushandiswa kweese anodiwa ClickHouse matekinoroji.
• Summing/AggregatingMergeTrees matekinoroji anokutendera kuti uunganidze kana kufunga nezvemamiriro ekunze seyakakosha kesi. Uye inorerutsa zvikuru zvinhu zvakawanda.
• Materialized Views anokutendera kuti upfuure imwe index muganho. Zvichida handina kuzvitaura zvakajeka, asi patakatakura matanda, matanda akasvibirira akanga ari patafura neine index imwe, uye magwaro ezvinyorwa akanga ari mutafura, kureva data yakafanana, yakasvibiswa chete, asi index yacho yakanga yakakwana. vamwe. Inoratidzika kunge yakafanana data, asi yakasiyana kuronga. Uye Materialized Maonero anobvumidza iwe, kana uchiida, kupfuura iyo ClickHouse kuganhurirwa.
• Deredza index granularity yemibvunzo yemapoint.
• Uye govera iyo data zvine hungwaru, edza kuisa iyo data mukati me server zvakanyanya sezvinobvira. Uye edza kuona kuti zvikumbiro zvinoshandisawo nharaunda pazvinogoneka zvakanyanya sezvinobvira.

Uye muchidimbu kutaura kupfupi uku, tinogona kutaura kuti ClickHouse ikozvino yanyatso gara munzvimbo yezvese zvekutengesa dhatabhesi uye yakavhurika sosi dhatabhesi, kureva, zvakanangana neanalytics. Anokwana zvakakwana munzvimbo iyi. Uye zvakanyanya, zvishoma nezvishoma zvinotanga kuunganidza vamwe kunze, nekuti kana uine ClickHouse, haudi InfiniDB. Vertika inogona kunge isingadikanwe nekukurumidza kana ikaita yakajairika SQL rutsigiro. Enjoy!

-Ndatenda nemushumo! Zvinonakidza kwazvo! Paive paine kuenzanisa neApache Phoenix?

Kwete, handina kumbonzwa mumwe munhu achienzanisa. Isu neYandex tinoedza kuchengeta zvese zveClickHouse kuenzanisa nemadhatabhesi akasiyana. Nekuti kana chimwe chinhu chikangoerekana chave kukurumidza kupfuura ClickHouse, ipapo Lesha Milovidov haagone kurara husiku uye anotanga kukurumidza kukurumidza. Handisati ndanzwa nezvekuenzanisa kwakadaro.

• (Aleksey Milovidov) Apache Phoenix injini yeSQL inofambiswa neHbase. Hbase inonyanya kukoshesa-kukosha basa mamiriro. Ikoko, mumutsara wega wega, panogona kuve nenhamba isingaverengeki yemakoramu ane mazita akasarudzika. Izvi zvinogona kutaurwa nezve masisitimu akadai seHbase, Cassandra. Uye ichokwadi inorema yekuongorora mibvunzo isingashande zvakajairwa kwavari. Kana iwe unogona kufunga kuti vanoshanda zvakanaka kana usati wambove neruzivo neClickHouse.

• Спасибо

  • Masikati akanaka Ini ndatove kufarira nyaya iyi, nekuti ndine analytical subsystem. Asi kana ndikatarisa paClickHouse, ndinowana manzwiro ekuti ClickHouse yakanyatso kukodzera kuongororwa kwechiitiko, inoshanduka. Uye kana ndichida kuongorora data rakawanda rebhizinesi neboka rematafura makuru, ipapo ClickHouse, sekunzwisisa kwandinoita, haina kunyanyondikodzera? Kunyanya kana vakachinja. Izvi ndizvo here kana kuti pane mienzaniso inogona kuramba izvi?

  • Izvi ndizvozvo. Uye izvi ndezvechokwadi kune akawanda akanyanya nyanzvi yekuongorora dhatabhesi. Izvo zvakagadzirirwa chokwadi chekuti kune imwe kana akawanda matafura makuru anochinjika, uye kune akawanda madiki anochinja zvishoma nezvishoma. Ndokunge, ClickHouse haina kuita seOracle, kwaunogona kuisa zvese uye kuvaka mimwe mibvunzo yakaoma kwazvo. Kuti ushandise ClickHouse zvinobudirira, unofanirwa kuvaka chirongwa nenzira inoshanda muClickHouse. Kureva kuti, dzivisa yakawandisa normalization, shandisa maduramazwi, edza kuita mashoma marefu malink. Uye kana chirongwa ichi chikavakwa nenzira iyi, saka mabasa akafanana ebhizinesi anogona kugadziriswa paClickHouse zvakanyanya zvakanyanya kupfuura pane yechinyakare hukama dhatabhesi.

Ndatenda neshumo! Ndine mubvunzo nezvenyaya yemari ichangoburwa. Vaive neanalytics. Zvaive zvakakodzera kuenzanisa kuti vanokwira nekudzika sei. Uye ini ndinonzwisisa kuti iwe wakavaka iyo sisitimu yakananga iyi analytics? Kana mangwana, semuenzaniso, ivo vachida imwe rondedzero pane iyi data, ivo vanofanirwa kuvakazve schema uye kurodha iyo data? Kureva, kuita imwe mhando preprocessing kuwana chikumbiro?

Ehe, uku ndiko kushandiswa kweClickHouse yebasa chairo. Inogona kugadziriswa zvakanyanya mukati meHadoop. Kune Hadoop, iri ibasa rakanaka. Asi paHadoop inononoka. Uye chinangwa changu ndechekuratidza kuti ClickHouse inogona kugadzirisa mabasa anowanzo gadziriswa nenzira dzakasiyana, asi panguva imwe chete zviite zvakanyanya. Izvi zvakagadzirirwa basa chairo. Zviri pachena kuti kana pane dambudziko nechinhu chakafanana, chinogona kugadziriswa nenzira yakafanana.

Zviri pachena. Mati maawa makumi mashanu akagadziriswa. Kubva pakutanga, wakaisa data riinhi kana kuwana mhinduro?

Hongu Hongu.

OK ndinokutendai zvikuru.

Izvi zviri pa3 server cluster.

Kwaziwai! Ndatenda nemushumo! Zvose zvinonakidza zvikuru. Ini handisi kuzobvunza zvishoma nezve mashandiro, asi nezve kushandiswa kweClickHouse maererano nekugadzikana. Ndiko kuti, waive nazvo here, waifanira kudzorera? ClickHouse inoita sei mune iyi kesi? Uye zvakaitika here kuti iwe une replica zvakare? Semuenzaniso, takasangana nedambudziko neClickHouse kana ichiri kubuda muganho uye ichidonha.

Zvechokwadi, hapana maitiro akanaka. Uye ClickHouse inewo matambudziko ayo. Asi wakambonzwa nezve Yandex.Metrica isingashande kwenguva yakareba? Zvimwe kwete. Yanga ichishanda zvakavimbika kubvira 2012-2013 pa ClickHouse. Ndinogona kutaura zvimwe chete nezvechiitiko changu. Hatina kumbotadza zvachose. Zvimwe zvinhu zvidiki zvinogona kuitika, asi hazvina kumbobvira zvakatsoropodza zvakakwana kukanganisa bhizinesi. Hazvina kumboitika. ClickHouse inovimbika chaizvo uye haiparare zvisina tsarukano. Iwe haufaniri kunetseka pamusoro pazvo. Haisi chinhu mbishi. Izvi zvakaratidzwa nemakambani mazhinji.

Mhoro! Iwe wati iwe unofanirwa kufunga pamusoro peiyo data schema ipapo. Ko kana zvikaitika? Data yangu iri kuyerera uye ichideuka. Mwedzi mitanhatu inopfuura, uye ndinonzwisisa kuti hazvibviri kurarama seizvi, ndinofanira kuisa zvakare data uye kuita chimwe chinhu navo.

Izvi zvinoenderana nehurongwa hwako. Pane nzira dzakawanda dzekuita izvi pasina kumira. Semuenzaniso, unogona kugadzira iyo Materialized View umo yekugadzira yakasiyana data chimiro kana ichikwanisa kuve yakasarudzika mepu. Ndokunge, kana ichibvumira mepu uchishandisa ClickHouse, kureva kubvisa zvimwe zvinhu, shandura kiyi yekutanga, shandura kupatsanura, ipapo unogona kuita Materialized View. Nyora data rako rekare ipapo, matsva anozonyorwa otomatiki. Uye wobva wangochinja kushandisa iyo Materialized View, wobva wachinja rekodhi uye uuraye tafura yekare. Iyi inowanzova nzira isina kumira.

Спасибо.

Source: www.habr.com