Igor Stryhar ã«ãã 2017 幎ã®ã¬ããŒããClickHouse - Tabix ã§èŠèŠçã«é«éãã€æ確ãªããŒã¿åæãã®ãã©ã³ã¹ã¯ãªãããèªãããšããå§ãããŸãã
Tabix ãããžã§ã¯ãã® ClickHouse ã® Web ã€ã³ã¿ãŒãã§ã€ã¹ã
äž»ãªæ©èœïŒ
- è¿œå ã®ãœãããŠã§ã¢ãã€ã³ã¹ããŒã«ããå¿ èŠããªãããã©ãŠã¶ããçŽæ¥ ClickHouse ãšé£æºããŸãã
- æ§æ匷調衚瀺ãåããã¯ãšãª ãšãã£ã¿ãŒã
- ã³ãã³ãã®ãªãŒãã³ã³ããªãŒãã
- ã¯ãšãªå®è¡ã®ã°ã©ãã£ã«ã«åæã®ããã®ããŒã«ã
- éžã¹ãã«ã©ãŒã¹ããŒã ã
ç§ã¯SMI2ã®ãã¯ãã«ã«ãã£ã¬ã¯ã¿ãŒã§ãã ç§ãã¡ã¯ãã¥ãŒã¹äº€æãã¥ãŒã¹ã¢ã°ãªã²ãŒã¿ãŒã§ãã åœç€Ÿã¯ããŒãããŒããåãåã£ã倧éã®ããŒã¿ãä¿åããClickHouse ã«ç»é²ããŸã (30 ç§ãããçŽ 000 ãªã¯ãšã¹ã)ã
ããã¯æ¬¡ã®ãããªããŒã¿ã§ãã
- ãã¥ãŒã¹ãã¯ãªãã¯ããŸãã
- ãã¥ãŒã¹ã¯ã¢ã°ãªã²ãŒã¿ãŒã«è¡šç€ºãããŸãã
- åœç€Ÿã®ãããã¯ãŒã¯äžã«ãããŒã衚瀺ãããŸãã
- ãããŠãYandex.Metrica ã«äŒŒãç¬èªã®ã«ãŠã³ã¿ãŒããã€ãã³ããç»é²ããŸãã ãããç§ãã¡ç¬èªã®ãã€ã¯ãåæã§ãã
ClickHouse ãç»å ŽãããŸã§ãç§ãã¡ã¯éåžžã«å€å¿ãªç掻ãéã£ãŠããŸããã ãã®ããŒã¿ãã©ããã«ä¿åããŠããªããšãåæããããšããŠãç§ãã¡ã¯éåžžã«èŠåŽããŸããã
ClickHouse 以åã®ç掻 â infiniDB
æåã«çšæããã®ã¯ infiniDB ã§ããã 圌女ã¯ç§ãã¡ãšäžç·ã«4幎éæ®ãããŸããã èŠåŽããŠç«ã¡äžããŸããã
- ã¯ã©ã¹ã¿ãªã³ã°ãã·ã£ãŒãã£ã³ã°ã¯ãµããŒããããŠããŸããã ããã©ã«ãã§ã¯ããã®ãããªè³¢ãæ©èœã¯ç®±ããåºãŠããŸããã
- 圌女ã¯ããŒã¿ãããŒãããã®ãå°é£ã§ãã CSV ãã¡ã€ã«ã®ã¿ããéåžžã«äžæ確ãªæ¹æ³ã§ã®ã¿ããŒãã§ããç¹å®ã®ã³ã³ãœãŒã« ãŠãŒãã£ãªãã£ã®ã¿ã
- ããŒã¿ããŒã¹ã¯ã·ã³ã°ã«ã¹ã¬ããã§ãã æžãããšãèªãããšãã§ããŸãã ããããããã«ãã£ãŠå€§éã®ããŒã¿ãåŠçã§ããããã«ãªããŸããã
- ãããŠåœŒå¥³ã¯èå³æ·±ãæŸèæãæã£ãŠããŸããã ãµãŒããŒã¯æ¯æ©åèµ·åããå¿ èŠããããåèµ·åããªããšæ©èœããŸããã§ããã
圌女㯠2016 幎æ«ãŸã§ç§ãã¡ã®ããã«åããŠãããŸãããããã®åŸãç§ãã¡ã¯ ClickHouse ã«å®å šã«åãæ¿ããŸããã
ClickHouse 以åã®ç掻 â Cassandra
infiniDB ã¯ã·ã³ã°ã«ã¹ã¬ããã ã£ãã®ã§ãåæã«å€ãã®ã¹ã¬ãããæžã蟌ãããšãã§ãããããçš®ã®ãã«ãã¹ã¬ãã ããŒã¿ããŒã¹ãå¿ èŠã§ãããšå€æããŸããã
ç§ãã¡ã¯ããããã®èå³æ·±ãããšãè©ŠããŠã¿ãŸããã 次ã«ãCassandra ãè©ŠããŠã¿ãããšã«ããŸããã ã«ãµã³ãã©ã«é¢ããŠã¯ãã¹ãŠãçŽ æŽãããã£ãã å ¥æããã 10 ç§ããã 000 ãªã¯ãšã¹ãã ã©ããã« 2 件ã®èªæžãªã¯ãšã¹ãããããŸãã
ãããã圌女ã«ã¯åœŒå¥³èªèº«ã®èå³ããããŸããã æã« XNUMX åããŸã㯠XNUMX ãæã« XNUMX åãããŒã¿ããŒã¹ã®éåæãçºçããŸããã ãããŠç§ã¯èµ·ããŠãã«ãµã³ãã©ãçŽãããã«èµ°ããªããã°ãªããŸããã§ããã ãµãŒããŒã¯ XNUMX ã€ãã€åèµ·åãããŸããã ãããŠãã¹ãŠãæ»ããã§çŸãããªããŸããã
ClickHouse 以åã®ç掻 â ãã«ã€ã
ãã®åŸãããã«å€ãã®ããŒã¿ãæžã蟌ãå¿ èŠãããããšãããããŸããã 2016 幎ã«ãã«ã€ããèŠå§ããŸããã
Druid 㯠Java ã§æžããããªãŒãã³ãœãŒã¹ ãœãããŠã§ã¢ã§ãã éåžžã«å ·äœçã§ãã ãŸããããçš®ã®ã€ãã³ã ã¹ããªãŒã ãä¿åããããã«å¯ŸããŠéèšãå®è¡ããããåæã¬ããŒããäœæãããããå¿ èŠãããå Žåã®ã¯ãªãã¯ã¹ããªãŒã ã«ãé©ããŠããŸããã
Druid ã®ããŒãžã§ã³ã¯ 0.9.X ã§ããã
ããŒã¿ããŒã¹èªäœã®å±éã¯éåžžã«å°é£ã§ãã ãããã€ã³ãã©ã¹ãã©ã¯ãã£ã®è€éãã§ãã ãããé åããã«ã¯ã倧éã®éãèšçœ®ããå¿ èŠããããŸããã ãããŠãåããŒããŠã§ã¢ã¯ç¬èªã®åœ¹å²ãæ ã£ãŠããŸããã
ããŒã¿ãããŒãããã«ã¯ãããçš®ã®ã·ã£ãŒãããºã ã䜿çšããå¿ èŠããããŸããã ãªãŒãã³ãœãŒã¹ ãããžã§ã¯ãã§ãã Tranquility ã¯ãã¹ããªãŒã å ã§ç§ãã¡ããã®ããŒã¿ã倱ã£ãŠããŸããã ããŒã¿ãããŒããããšãããŒã¿ã倱ãããŠããŸããŸããã
ããããã©ããããããç§ãã¡ã¯ãããå®è£ ãå§ããŸããã 麻è¬ãæçšããªãããµããã³ãé£ã¹ç¶ããããªããºãã®ããã«ãç§ãã¡ã¯ãããå°å ¥ãå§ããŸããã ãã¹ãŠã®ã€ã³ãã©ã¹ãã©ã¯ãã£ãæºåããã®ã«çŽ XNUMX ãæããããŸããã ã€ãŸãããµãŒããŒã泚æãã圹å²ãæ§æããå±éãå®å šã«èªååããŸãã ã€ãŸããã¯ã©ã¹ã¿ãŒã«é害ãçºçããå ŽåãXNUMX çªç®ã®ã¯ã©ã¹ã¿ãŒãèªåçã«ãããã€ãããŸãã
ããããã®åŸãå¥è·¡ãèµ·ãããŸããã ç§ã¯äŒæäžã ã£ãã®ã§ãããååã次ã®ãªã³ã¯ãéã£ãŠããŸããã
ãããŠæåéã 2 æ¥ã§ãClickHouse ãã¹ã ã¯ã©ã¹ã¿ãŒããããã€ããŸããã ããŒã¿ã®èªã¿èŸŒã¿ãéå§ããŸããã infiniDB ãšæ¯èŒãããšãããã¯åæ©çãªãã®ã§ãããDruid ãšæ¯èŒãããšãããã¯åæ©çãªãã®ã§ãã ã«ãµã³ãã©ãšæ¯ã¹ãã°ããããåæ©çã ã PHP ãã Cassandra ã«ããŒã¿ãããŒãããå Žåãããã¯åæ©çãªãã®ã§ã¯ãªãããã§ãã
äœãåŸãããã§ããããïŒ ã¹ããŒãã®ããã©ãŒãã³ã¹ã ããŒã¿ã¹ãã¬ãŒãžã®ããã©ãŒãã³ã¹ã ã€ãŸãã䜿çšããããã£ã¹ã¯å®¹éãå€§å¹ ã«å°ãªããªããŸãã ClickHouse ã¯é«éã§ãä»ã®è£œåãšæ¯ã¹ãŠãéåžžã«é«éã§ãã
Yandex ã ClickHouse ããªãŒãã³ãœãŒã¹ã§å ¬éããåœåã¯ãã³ã³ãœãŒã« ã¯ã©ã€ã¢ã³ããããããŸããã§ããã ç§ãã¡ SMI2 ã¯ã倧éã®ãªã¯ãšã¹ããæžãå§ããããããã©ãŠã¶ããããŒãžãéããŠãªã¯ãšã¹ããæžããŠçµæãååŸã§ããããã«ãWeb çšã®ãã€ãã£ã ã¯ã©ã€ã¢ã³ããäœæããŠã¿ãããšã«ããŸããã ã³ã³ãœãŒã«ã§æžãã®ã¯é£ããã§ãã ãããŠç§ãã¡ã¯æåã®ããŒãžã§ã³ãäœæããŸããã
ãããŠæšå¹Žã®å¬ã«è¿ã¥ããé ãClickHouse ãšé£æºããããã®ãµãŒãããŒã㣠ããŒã«ãç»å Žãå§ããŸããã ãããã¯æ¬¡ã®ãããªããŒã«ã§ãã
Redash.IO .ã¢ããããŒãããªã³ .ã¹ãŒããŒã»ããAirbnb ã ïŒä»https://github.com/apache/incubator-superset )Jetbrains IDE .Vertamedia/ã¯ãªãã¯ããŠã¹-ã°ã©ãã¡ã .
ãããã®ããŒã«ãã€ãŸãç§ã䜿çšããããŒã«ã®ããã€ããèŠãŠãããŸãã
è¯ãããŒã«ã§ããããã«ã€ãåãã§ãã Druid ãå®è£ ãããŠãããšããç§ã¯ SuperSet ããã¹ãããŠããŸããã ç§ã¯åœŒã奜ãã§ããã ãã«ã€ãã®å Žåãããã¯éåžžã«é«éã§ãã
ClickHouseã«ã¯é©ããŠããŸããã ã€ãŸããé©åããèµ·åããŸãããSELECT ã€ãã³ããGROUP BY ã€ãã³ããªã©ã®åºæ¬çãªã¯ãšãªã®ã¿ãåŠçããæºåãã§ããŠããŸãã ããè€é㪠ClickHouse æ§æã¯ãµããŒããããŠããŸããã
次ã®ããŒã«ã¯ Apache Zeppelin ã§ãã ããã¯è¯ãããšã§ãããèå³æ·±ãããšã§ãã åäœããŸãã ããŒãããã¯ãããã·ã¥ããŒãããµããŒãããå€æ°ããµããŒãããŸãã ClickHouse ã³ãã¥ããã£ã®èª°ããããã䜿çšããŠããããšãç¥ã£ãŠããŸãã
ãã ããClickHouse æ§æã¯ãµããŒããããŠããªããããã³ã³ãœãŒã«ãŸãã¯ä»ã®å Žæã«ã¯ãšãªãèšè¿°ããå¿ èŠããããŸãã 次ã«ããã¹ãŠãåäœããããšã確èªããŸãã ãã äžäŸ¿ã§ãã ãã ããããã·ã¥ããŒãã®ãµããŒãã¯åªããŠããŸãã
次ã®ããŒã«ã¯ Redash.IO ã§ãã Redash ã¯ã€ã³ã¿ãŒãããäžã§ãã¹ããããŸãã ã€ãŸãã以åã®ããŒã«ãšã¯ç°ãªããã€ã³ã¹ããŒã«ããå¿ èŠã¯ãããŸããã ããã¯ãããŸããŸãªããŒã¿ãœãŒã¹ããã®ããŒã¿ãçµ±åããæ©èœãåããããã·ã¥ããŒãã§ãã ã€ãŸããClickHouseãMySQLãPostgreSQLãããã³ãã®ä»ã®ããŒã¿ããŒã¹ããããŠã³ããŒãã§ããŸãã
ã¡ããã© 2017 ãæå (XNUMX 幎 XNUMX æ)ãGrafana ã«ãµããŒããç»å ŽããŸããã ããšãã°ãããŒããŠã§ã¢ã®ç¶æ ãäžéšã®ã¡ããªã¯ã¹ã«é¢ããã¬ããŒãã Grafana ã§äœæããå ŽåãClickHouse ããã®ããŒã¿ããåãã°ã©ããããçš®ã®ããã«ãçŽæ¥äœæã§ããããã«ãªããŸããã ããã¯ãšãŠã䟿å©ã§ãç§ãã¡ã䜿ã£ãŠããŸãã ããã«ãããç°åžžãèŠã€ããããšãã§ããŸãã ã€ãŸããäœããçºçããŠäžéšã®ããŒããŠã§ã¢ãèœã¡ãããè² æ ãããã£ããããå Žåããã®ããŒã¿ã ClickHouse ã«äŸµå ¥ã§ãããã©ããã調ã¹ãããšãã§ããŸãã
ãããã®ããŒã«ãã³ã³ãœãŒã«ã«æžã蟌ãã®ã¯éåžžã«é¢åã ãšæããŸããã ãããŠãæåã®ã€ã³ã¿ãŒãã§ãŒã¹ãæ¹åããããšã«ããŸããã ãã®ã¢ã€ãã¢ã¯ EventSQLãSeperSetãZeppelin ããåŸãŸããã
äœããããã£ãã®ã§ããïŒ ã°ã©ãã£ãã¯ã¹ãæ¹è¯ããããšãã£ã¿ãŒããããŠãã³ãèŸæžã®ãµããŒããå®è£ ããããšèããŠããŸããã ClickHouse ã«ã¯èŸæžãšããçŽ æŽãããæ©èœãããããã§ãã ããããæ ŒçŽãããå€ã®åœ¢åŒãã€ãŸãæ°å€ãªã®ãæååãªã®ããªã©ãèŠããŠããå¿ èŠããããããèŸæžãæ±ãã®ã¯å°é£ã§ãããŸããèŸæžãããŸããŸãªããªãšãŒã·ã§ã³ã§äœ¿çšããããšãå€ããããã¯ãšãªãäœæããã®ã¯éåžžã«å°é£ã§ããã
æåã®ããŒãžã§ã³ã®ãªãªãŒã¹ãã 3 ãæãçµéããŸããã ãã©ã€ããŒã ãã©ã³ãã«çŽ 330 件ã®ã³ããããè¡ã£ããšãããããã Tabix ã§ããããšãå€æããŸããã
ClickHouse-Frontend ãšããååã ã£ã以åã®ããŒãžã§ã³ãšã¯ç°ãªããã·ã³ãã«ãªååã«å€æŽããããšã«ããŸããã ãããŠTabixãå€æããŸããã
äœãçŸããïŒ
ã°ã©ããæç»ããŸãã ClickHouse SQL æ§æããµããŒãããŸãã æ©èœã«é¢ããã¢ããã€ã¹ãæäŸããèå³æ·±ãããšãããããã§ããŸãã
äžè¬ç㪠Tabix ã¹ããŒã ã¯æ¬¡ã®ããã«ãªããŸãã å·ŠåŽã«ã¯æšããããŸãã äžå€®ã«ã¯ã¯ãšãªãšãã£ã¿ããããŸãã ãããŠä»¥äžããã®ãªã¯ãšã¹ãã®çµæã§ãã
次ã«ãã¯ãšãª ãšãã£ã¿ãŒãã©ã®ããã«æ©èœãããã説æããŸãã
ããã§ã¯ããªãŒãã³ã³ããªãŒããããŒãã«ã«å¯ŸããŠèªåçã«åäœããããã«å¿ããŠãã£ãŒã«ãã®ãªãŒãã³ã³ããªãŒããæ±ããããã³ããã衚瀺ãããŸãã ãããŠæ©èœã«é¢ãããã³ãã Ctrl+Enter ãæŒããšããªã¯ãšã¹ããå®è¡ããããããšã©ãŒã§å€±æããŸãã æãåçŽãªãªã¯ãšã¹ãã Tabix ã«éä¿¡ãããçµæãååŸãããŸããã€ãŸããClickHouse ããã°ããæäœã§ããŸãã
ãã§ã«è¿°ã¹ãããã«ãèŸæžã¯éåžžã«èå³æ·±ããã®ã§ãããç§ãã¡ã¯ãã掻çšããŠããŸãã ãããŠãã®ãããã§ç§ãã¡ã¯å€ãã®ããšãã§ããããã«ãªããŸããã ãã¹ãŠã®éœåžãèŸæžã«ä¿åãããšããŸãã éœåžã®èå¥åãšéœåžåã緯床ãšçµåºŠãä¿åããŸãã ãããŠããŒã¿ããŒã¹ã«ã¯éœåžã®èå¥åã®ã¿ãä¿åãããŸãã ãããã£ãŠãããŒã¿ãéåžžã«åŒ·åã«å§çž®ããŸãã
ããã¯åçŽãªããšã®ããã«æããŸãããClickHouse ã§ã¯éåžžã«èå³æ·±ãæ¹æ³ã§åœ¹ã«ç«ã¡ãŸãã ClickHouse ã¯ãã¹ããããçµåã®ã¿ããµããŒãããŠãããããã¯ãšãªã¯äžæ¹ã«åãã£ãŠååã«åºããªããŸãã ãŸããæ¬åŒ§ãéããŠé·ãåŒãå ¥ã£ãŠããå Žåã¯ãã¯ãšãªãæãããããªã©ã®åçŽãªæäœã§ãã¯ãšãªèªäœã®æäœã容æã«ãªããŸãã ã¯ãšãªã®é·ââãã 200 ïœ 300 è¡ã§ãå¹ ãéåžžã«å€§ããå Žåã¯ãã¯ãšãªãæããããã§ãã©ããã®å ŽæãèŠã€ããããäœããã®æ¹æ³ã§ããŒã«ã©ã€ãºããããšãéåžžã«åœ¹ç«ã¡ãŸãã
ãªããžã§ã¯ã ããªãŒããã«ãã¯ãšãªãã¿ã (ãã㪠13:46)
次ã«ããªãŒãšã¿ãã«ã€ããŠèª¬æããŸãã å·ŠåŽã«ã¯ããªãŒããããäžéšã«ã¯è€æ°ã®ã¿ããäœæã§ããŸãã ã¿ãã¯ã¯ãŒã¯ã¹ããŒã¹ã®ãããªãã®ã§ãã è€æ°ã®ã¿ããäœæããããããã«ç°ãªãååãä»ããããšãã§ããŸãã ããã¯ã¬ããŒããäœæããããã®ããã·ã¹ãã ã®ãããªãã®ã§ãã
ã¿ãã¯èªåçã«ä¿åãããŸãã ãã©ãŠã¶ãåèµ·åããããTabix ãéãããéããšãããããã¹ãŠãä¿åãããŸãã
ãããã㌠- äŸ¿å© (ãã㪠14:39)
ãããããŒããããããªããããããããŸãã ããã§ã¯äŸãšããŠãã®äžéšãæç²ããŸããã ããã¯ãã¿ãã®åãæ¿ãããªã¯ãšã¹ãã®å®è¡ããŸãã¯è€æ°ã®ãªã¯ãšã¹ãã®å®è¡ã§ãã
çµæãæäœããæ¹æ³ã説æããŸãã ãªã¯ãšã¹ããéä¿¡ããŸãã ããã§ã¯sinãcosãtgãæããŠããŸãã çµæã匷調衚瀺ããããšãã§ããŸããã€ãŸããåã®å žåçãªããããæç»ã§ããŸãã æ£ãŸãã¯è² ã®å€ã匷調衚瀺ã§ããŸãã ãŸãã¯ãç¹å®ã®ããŒãã«èŠçŽ ã«è²ãä»ããã ãã§ãã ããã¯ãããŒãã«ã倧ãããç°åžžãç®ã§èŠã€ããå¿ èŠãããå Žåã«äŸ¿å©ã§ãã ç°åžžãæ¢ããŠãããšããããã€ãã®ç·ãšããã€ãã®èŠçŽ ãç·ãŸãã¯èµ€ã§åŒ·èª¿è¡šç€ºããŸããã
ããã«ã¯èå³æ·±ããã®ããããããããŸãã ããšãã°ãRedmine Markdown ã«ã³ããŒããæ¹æ³ã§ãã çµæãã©ããã«ã³ããŒããå¿ èŠãããå Žåãããã¯éåžžã«äŸ¿å©ã§ãã é åãéžæãããRedmine ã«ã³ããŒããšèšãã ãã§ãRedmine Markdown ã«ã³ããŒãããããWhere ã¯ãšãªãäœæãããŸãã
次ã«ã¯ãšãªã®æé©åã§ãã äžåºŠãæ¥ä»ããã£ãŒã«ããæå®ããã®ãå¿ããŠããŸããŸããã ãããŠãClickHouse ã§ã®ç§ã®ãªã¯ãšã¹ãã¯ãéåžžã«ããã«ã¯åŠçãããŸããã§ããããããã«ãã€ãŸã XNUMX ç§æªæºã§åŠçãããŸããã 圌ãäœè¡ãèµ°ã£ãŠããã®ãèŠããšããç§ã¯æããªã£ãã XNUMX æ¥ã«ãã®ããŒãã«ã«ããã»ã©å€ãã®è¡ãæžã蟌ãããšã¯ãããŸããã ãªã¯ãšã¹ãã®åæãéå§ãããšãããXNUMX ãæã§æ¥ä»ãèŠéããŠããããšãããããŸããã ã€ãŸããããŒãã«å šäœã®ããŒã¿ãå¿ èŠã§ã¯ãªããç¹å®ã®æéã®ããŒã¿ãå¿ èŠã§ããããšã瀺ãã®ãå¿ããŠããŸããã
Tabix ã«ã¯ãéä¿¡ããããªã¯ãšã¹ãã®å±¥æŽå šäœãä¿åããããçµ±èšãã¿ãããããŸããã€ãŸãããã®ãªã¯ãšã¹ãã«ãã£ãŠèªã¿åãããè¡æ°ãšå®è¡ã«ããã£ãæéã確èªã§ããŸãã ããã«ããæé©åãå¯èœã«ãªããŸãã
ã¯ãšãªçµæã«åºã¥ããŠãããã ããŒãã«ãæ§ç¯ã§ããŸãã ClickHouse ã«ãªã¯ãšã¹ããéä¿¡ããããŒã¿ãåãåããŸããã ãããŠããã®ããŒã¿ãããŠã¹ã§ç§»åããŠãããçš®ã®ãããã ããŒãã«ãæ§ç¯ã§ããŸãã
次ã«èå³æ·±ãã®ã¯ããããã§ãã 次ã®ãªã¯ãšã¹ãããããšããŸããsinãcos 㯠0 ïœ 299 ã§ãããããæç»ããã«ã¯ã[æç»] ã¿ããéžæããå¿ èŠããããŸãããããããšãsin ãš cos ã®ã°ã©ãã衚瀺ãããŸãã
ãããç°ãªã軞ã«åå²ããããšãã§ããŸããã€ãŸããXNUMX ã€ã®ã°ã©ããäžåºŠã«äžŠã¹ãŠæç»ã§ããŸãã XNUMX ã€ã®ã³ãã³ããš XNUMX ã€ç®ã®ã³ãã³ããæžã蟌ã¿ãŸãã
ãã¹ãã°ã©ã ãæãããšãã§ããŸãã
ãããã°ã©ãã®ãããªãã¯ã¹ã«å解ã§ããŸãã
ããŒãããããäœæã§ããŸãã
ãµãŒãã«ã«ã¬ã³ããŒãæ§ç¯ã§ããŸãã ã¡ãªã¿ã«ãããã¯ãXNUMX 幎ã«ãããç°åžžãåæããå¿ èŠãããå Žåãã€ãŸããã¹ãã€ã¯ãŸãã¯ãããããæ€åºããå¿ èŠãããå Žåã«éåžžã«äŸ¿å©ã§ãã ãã®ããŒã¿èŠèŠåã¯ããã«åœ¹ç«ã¡ãŸããã
次ã«ããªãŒãããã§ãã
ãµã³ããŒãºã¯èå³æ·±ããã£ãŒãã§ãã 圌ã¯ã¹ããªãŒã ã°ã©ãããªããŒã®ã©ã¡ããã§ãã ã§ãç§ã¯ãããå·ãšåŒãã§ããŸãã ãŸããç°åžžãæ¢ãããšãã§ããŸãã ãšãŠãå¿«é©ã§ãã æ€çŽ¢ã«äœ¿çšããããšããå§ãããŸãã
次ã«èå³æ·±ãã®ã¯ããã€ããã㯠ãããã®æç»ã§ãã 緯床ãšçµåºŠãããŒã¿ããŒã¹ã«ä¿åããããšãã°ãã©ãã¯èŒžéãé£è¡æ©ãé£ãã§ããå Žåã«ç®çå°ãä¿åãããšãç®çå°ã®çµè·¯ãæãããšãã§ããŸãã ãŸãããªããžã§ã¯ããé£ã³èŸŒãé床ãšãµã€ãºãèšå®ããããšãã§ããŸãã
ãããããã®å°å³ã®åé¡ã¯ãäžçå°å³ãæãããŠããã ãã§ã詳现ãæãããŠããªãããšã§ãã
åŸã§Googleããããè¿œå ããŸããã 緯床ãçµåºŠãä¿åãããšããã®çµæã Google ãããäžã«æç»ã§ããŸãããé£è¡æ©ã¯ãµããŒããããŸããã
Tabix ã§çµæãšã¯ãšãªãæäœããäž»ãªæ©èœã«ã€ããŠèª¬æããŸããã
次ã¯ãClickHouse ãµãŒããŒã®åæã§ãã åå¥ã®ãã¡ããªã¯ã¹ãã¿ãããããååã«ä¿åãããŠããããŒã¿ã®ãµã€ãºã確èªã§ããŸãã ã¹ã¯ãªãŒã³ã·ã§ããã¯ããã®ããªãã¡ã©ãŒããã£ãŒã«ããçŽ 730 GB ãå ããããšã瀺ããŠããŸãã ãã®ãã£ãŒã«ããæŸæ£ãããšããããã 700 GB ã®ã·ã£ãŒãã 2 ã€ãã€ãŸãäžèŠãªçŽ XNUMX TB ãä¿åãããŸãã
ãŸããæååã«ä¿åãããrequest_idããã£ãŒã«ãããããŸãã ããããæ°å€åœ¢åŒã§ä¿åãå§ãããšããã®åéã¯å€§å¹ ã«çž®å°ããŸãã
ãŸãããµãŒããŒæ§æãšã¯ã©ã¹ã¿ãŒå ã®ããŒãã®ãªã¹ãã衚瀺ãããŸãã
次ã®ã¿ãã¯ã¡ããªã¯ã¹ã§ãã ClickHouse ã䜿çšããŠãªã¢ã«ã¿ã€ã ã«ã¢ã¯ã»ã¹ããããšã§ããµãŒããŒã®ç¶æ ãåæãããµãŒããŒã«äœãèµ·ãã£ãŠããããç解ã§ããããã«ãªããŸãã ããã¯å®å šãª Grafana ã«ä»£ãããã®ã§ã¯ãããŸããã ããã¯è¿ éãªåæã«å¿ èŠã§ãã
次ã®ã¿ãã¯ããã»ã¹ã§ãã ãããããããµãŒããŒã§äœãèµ·ãã£ãŠããããç解ã§ããŸãã ããã§äœãèµ·ãã£ãŠããã®ãç解ããŠãã ããã æ¯å 200 GB ã®èªã¿åããæ¶è²»ãããªã¯ãšã¹ãããããŸããã ãã®ã€ã³ã¿ãŒãã§ã€ã¹ã®ãããã§ããã確èªã§ããŸããã ç§ã¯åœŒãæãŸããŠèšæ£ããŸããã ãããŠãããã¯çŽ30 GBãã€ãŸãæã ã®ããã©ãŒãã³ã¹ã§ããããšãå€æããŸããã
ããããšãïŒ ãããŠããã¯ãªãŒãã³ãœãŒã¹ã§ã
ç§ã¯çµããã ã¡ãªã¿ã«ãããã¯ãªãŒãã³ãœãŒã¹ã§ãããç¡æã§ããŠã³ããŒãããå¿ èŠãããããŸããã ãã©ãŠã¶ã§éããšãã¹ãŠãæ©èœããŸãã
質å
ã€ãŽãŒã«ã次ã¯äœã§ããïŒ ãã®ããŒã«ã¯ã©ãã§éçºããŸãã?
次ã«ãããã·ã¥ããŒãã衚瀺ãããŸããã€ãŸããããããããã·ã¥ããŒãã衚瀺ãããŸãã ä»ã®ããŒã¿ããŒã¹ãšã®çµ±åã ããã¯å®è¡ããŸãããããŸã ãªãŒãã³ãœãŒã¹ã§å ¬éããŠããŸããã ãã㯠MySQL ã§ãããå Žåã«ãã£ãŠã¯ PostgreSQL ã§ãã ã€ãŸããTabix ãã ClickHouse ã ãã§ãªããä»ã®ããŒã«ã«ããªã¯ãšã¹ããéä¿¡ã§ããããã«ãªããŸãã
èšå€§ãªéã®äœæ¥ãè¡ãããããšã¯æããã§ãã ããã¯ããªãå®å šãªã¢ã€ãã¢ã§ããããšãããããŸããã ããã¯æããã«ãããããçš®é¡ã®è»žã§ã®ç ©éãªäœæ¥ãæé€ããå šäœãçŽ æ©ããŸãšããããããã©ãŠã¶å ã§è¡ãããããã§ãã åºæŒããŠãããšèããŸãã PHP ãããã£ãŠããã©ãŠã¶ã«å ¥åããã®ãæãç°¡åãªæ¹æ³ã§ãããã©ãã§ãæ©èœããŸãã ããã«é¢ããŠã¯è³ªåã¯ãããŸããã 質åã¯ããã§ãã ããã§ã¯æ¬åœã«å€ãã®ããšãè¡ãããŠããŸããã äœäººã§ããã«åãçµã¿ãŸããã? ãããŠãå šéšã§ã©ããããæéãããããŸãããïŒ ã«ã¹ã¿ã ããŒã«ã«ã¯éåžžãããã»ã©å€ãã®æ©èœããªãããã§ãã
ç§ãã¡ã®ããŒã ã®330人ã¯å€ããç§ãŸã§åããŸããã ãããæåã®ããŒãžã§ã³ã§ããã ãã®åŸãäžäººã§ 3 件ã®ã³ããããè¡ããŸããã ã芧ã®ãšãããååãšç§ã¯ååã§ãããŸããã æåã®ããŒãžã§ã³ããæåŸã®ããŒãžã§ã³ãŸã§ãXNUMX ãæã§ã»ãšãã©äžäººã§äœããŸããã ããããç§ã¯ Javascript ãããç¥ããŸããã ããã¯ç§ãåãçµãã å¯äžã®ããããŠããããæåŸã® JavaScript ãããžã§ã¯ãã§ããã åãã£ããèŠãŠã¿ã - ãããæãããã ããããç§ã¯æ¬åœã«è£œåãå®æããããã£ãã®ã§ããããèµ·ãããŸããã
ãå ±åããããšãããããŸãïŒ ããã¯çŽ æŽãããããŒã«ã§ãã ãš ã¿ãã㌠æ¯èŒããŸãããïŒ
ããããšãã é æåãåããªã®ã§Tabixãšåä»ããŸããã
競äºããããïŒ
å€é¡ã®æè³ãè¡ãããã ããããæã ã¯ç«¶äºããã ããã
ãã®ããŒã«ãå®å šã«çœ®ãæããããããšã瀟å ã¢ããªã¹ãã«å£²ã蟌ãã«ã¯ã©ãããã°ããã§ãããã *ã¿ãããŒ*? è°è«ã¯äœã«ãªãã§ããããïŒ
ClickHouse ãšãã€ãã£ãã«é£æºããŸãã Tableau ãè©ŠããŠã¿ãŸããããããã«ã¯èŸæžãªã©ã®ãµããŒããæžãããšãã§ããŸããã ç§ã¯äººã ãã©ã®ããã« Tabix ã䜿ã£ãŠããããç¥ã£ãŠããŸãã ã¯ãšãªãäœæããCSV ã«ã¢ããããŒãããŠãBI ã«ã¢ããããŒãããŸãã ãããŠåœŒãã¯ãã§ã«ããã§äœããå§ããŠããŸãã ããããããã¯ã°ã©ãã£ã«ã«ãªããŒã«ãªã®ã§ããããã©ã®ããã«è¡ãã®ãæ³åããã®ãé£ããã§ãã 5 è¡ãæ倧 000 è¡ãã¢ã³ããŒãã§ããŸããããã以äžã¯ã¢ã³ããŒãã§ããŸãããããã§ãªãå Žåããã©ãŠã¶ãŒã¯å¯Ÿå¿ã§ããŸããã
ã€ãŸããããŒã¿éã«é倧ãªå¶éããããšããããšã§ããã
ã¯ãã ãã©ãŠã¶ç»é¢äžã®ããŒãã«ã« 10 è¡ãã¢ããããŒãããããšã¯æ³åã§ããŸããã äœã®ããã«ïŒ
ããŒã¿ãçŽ æ©ãèŠãããã®ã€ã³ã¿ãŒãã§ãŒã¹ãšããããšã§ããïŒ ã¡ãã£ãšã²ãã£ãŠãã²ãã£ãŠïŒ
ã¯ããä»çµã¿ãããã«ç¢ºèªããŠãæŠèŠã°ã©ããäœæããã ãã§ãã ãããŠãããã©ããã«æž¡ããŸãã åœç€Ÿã«ã¯ç¬èªã®å ±åã·ã¹ãã ãããããããããã®ãªã¯ãšã¹ããåãä»ããŸãã Tabix ã§æç»ããã¬ããŒãã«éä¿¡ããŸãã
ãããŠããäžã€ã®è³ªåã ã³ããŒãåæ?
ãªã¯ãšã¹ããããã°è¿œå ããŠãããŸãã
ãã€ãã䜿ãå§ãããã§ããïŒ ClickHouseãå®è£ ã«ã¯ã©ã®ãããæéãããããŸããã? ã¯ãªãã¯ããŠã¹ ãããŠæã£ãŠãã çç£ç¶æ ïŒ
å ã»ã©ãè¿°ã¹ãããã«ãç§ãã¡ã¯éåžžã«çæéã§ãã¹ã ã¯ã©ã¹ã¿ãŒãå®è£ ããŸããã 3 æ¥ã§å°å ¥ããŸããã ãããŠããã«æ°é±éãã¹ãããŸããã ãã㊠3 ãæã§æ¬çªç°å¢ã«å°éããŸããããç§ãã¡ã¯ç¬èªã® ETLãã€ãŸãããŒã¿ãèšé²ããããŒã«ãæã£ãŠããŸããã ãããŠåœŒã¯ã§ããéããã¹ãŠãæžããŸããã 圌㯠MongoDBãCassandraãMySQL ãæžãããšãã§ããŸãã 圌㫠ClickHouse ã§ã®æžãæ¹ãæããã®ã¯ç°¡åã§ããã åœç€Ÿã«ã¯ãè¿ éãªå®è£ ã®ããã®æ¢è£œã®ã€ã³ãã©ã¹ãã©ã¯ãã£ããããŸããã 6 ãæ以å ã«ãæåã®ã³ã³ããŒãã³ããå»æ£ãå§ããŸããã XNUMXãæã§ç§ãã¡ã¯ä»ã®ãã¹ãŠãå®å šã«æŸæ£ããŸããã ClickHouse 㯠XNUMX ã€ã ãæ®ã£ãŠããŸãã
ã€ãŽãŒã«ããããå ±åããããšãããããŸãã å°å³ã䜿çšããŠãã¹ãæ§ç¯ããæ©èœããšãŠãæ°ã«å ¥ããŸããã Yandex.Mapsãç¹ã«ã«ã¹ã¿ã Yandex.Maps ãšã®çµ±åã®èšç»ã¯ãããŸãã?
Googleãããã®ä»£ããã«çµ±åããããšããŸããããYandex.Mapsã«ããŒã¯ããŒããèŠã€ãããŸããã§ããã äžéšåãèšã£ãŠãªãã£ãã å·»ãæ»ããŠè¿œå ããŸãã
ã¹ã©ã€ã - Google ãããã ããã«å°å³ãæãã³ãã³ããDRAW_GMAPSãããããŸãã ã³ãã³ããDRAW_YMAPSãããããŸããã€ãŸããYandex.Mapãæç»ã§ããŸãã ãããå®éã«ã¯ããã®ã³ãã³ãã®äžã«ã¯ Javascript ããããŸããã€ãŸããClickHouse ããåãåã£ãããŒã¿ã¯ãããã§èšè¿°ãã Javascript ã«è»¢éã§ããŸãã ãããŠããããæç»ããåºåé åããããŸãã ä»»æã®ã°ã©ããã€ãŸãä»»æã®ã°ã©ããããããæç»ã§ããç¬èªã®ã³ã³ããŒãã³ããæç»ã§ããŸãã ãããŸã§ã¯ãã°ã©ãèªäœãæç»ããããã®å¥ã®ã©ã€ãã©ãªããããŸããã
ã€ãŸãã衚瀺æ©èœãã«ã¹ã¿ãã€ãºããããŒã«ã¯ãããŸãã?
ã©ãã§ãã ãããã®ããããååŸããŠè²ãå€æŽããèµ€ã§ã¯ãªãéãç·ã«ããããšãã§ããŸãã
ãå ±åããããšãããããŸãïŒ ä»£æ¿ã®ã¯ãšãª ããŒã«ã瀺ããã¹ã©ã€ãããããŸãã ã¯ãªãã¯ããŠã¹ ããã·ã¥ããŒããšåæã¬ããŒãã®æ§ç¯ã«äœ¿çšããŸãã ããªããä»äºãå§ããç¬éã«ããã¯ããããŸãã ClickHouseããããã®ããŒã«çšã®ã¢ããã¿ãŒã¯äœæãããŠããŸããã ãããŠããªãæ¢è£œã®ããŒã«çšã®ã¢ããã¿ãŒãäœæããã®ã§ã¯ãªããç¬èªã®ããŒã«ãäœæããããšã«ããã®ãçåã«æããŸãã ãã¹ããšãã£ã¿ãŒã調æŽããã®ãæ©ããšæããŸãã ãªããããªã«ããããã®ä»äºãããããšæã£ãã®ã§ããïŒ
ããã§èå³æ·±ãç¹ããããŸããå®éã®ãšãããç§ã¯ããŒã¿ãµã€ãšã³ãã£ã¹ãã§ã¯ãªããã¯ãã«ã«ãã£ã¬ã¯ã¿ãŒã§ãã Druid ã®å®è£ ãéå§ãããŸã§ã«ãç§ã®ããŒããããã«ã¯ã¿ã¹ã¯ã®çŽ 50% ãå«ãŸããŠããŸããããããèšç®ããŸãããããããèšç®ããŸãããããããåæããŸãããã ãããŠãClickHouseãå®è£ ããããšãå€æããŸããã ãããŠåœŒã¯ããã«ãã¹ãŠãæ§ç¯ãå§ããããã«ããŒãããããéããŸããã ãããŠãã®æãŸã§ã«ãããŒã¿ãµã€ãšã³ã¹ãšããŒã¿ããžã¥ã¢ã©ã€ãŒãŒã·ã§ã³ã®ç¥èãäžè¶³ããŠããããšã«æ°ã¥ããŸããã Tabix ã¯ãããŒã¿èŠèŠåãåŠã¶ããã®å®¿é¡ã®ãããªãã®ã§ãã ç§ã¯ãã§ãããªã³ãè£å®ããæ¹æ³ãæ€èšããŠããŸããã ç§ã¯åœŒã®ããã°ã©ãã³ã°ãå°ãå«ãã§ãã Redashã®è¿œå æ¹æ³ãèŠãŠã¿ãŸããããç§ã«ã¯æ®éã®ãšãã£ã¿ã§ååã§ããã ãŸããSuperSet ã¯ç§ãããŸã奜ãã§ã¯ãªãèšèªã§æžãããŠããŸãã ããã§ãµã€ã¯ãªã³ã°ããããšã«ããããããŠãããèµ·ãã£ãã
Igorããã«ãªã¯ãšã¹ããåãå ¥ããŸãã?
ã¯ãã
ãå ±åããããšãããããŸãïŒ ãããŠè³ªåãXNUMXã€ã ãŸããããªãã¯ããŸããäžèŸãèšããªã ãžã£ãã¹ã¯ãªããã 裞㮠Javascript ã§æžããŸããã? ãããšãäœããã®ãã¬ãŒã ã¯ãŒã¯ã§ãã?*
裞㮠Javascript ã®æ¹ãåªããŠããŸãã
ã§ã¯ãã©ã®ãããªæ çµã¿ã§ããããïŒ
è§åŒµã£ãã
ããã¯æããã ã ãããŠXNUMXã€ç®ã®è³ªåã æ€èšããŸããã R О *å æ²¢ã®ãã**?*
æ€èšããŠã¿ãŸããã éã³ãŸããã
åã«ã¢ããã¿ãŒãäœæããããšãã§ããŸãã
圌ã¯ã ã³ãã¥ããã£ãäœæããããã§ãããåã®è³ªåã«çããããã«ãèªåã§è©ŠããŠã¿ãããšæããŸããã
â»ãããèŠèŠåã«é¢ããŠã¯ãããããããŸãã
ãããããã®ããããšèšã£ãŠã°ã©ããæããŠãããŸãã ããŒã¿èŠèŠåã«é¢ããæ¬ãéããŸããã ãããŠç§ã¯ããæããŸãããããã®ããŒã¿ãèŠèŠåããŠã¿ãŸãããã ããŒã¿ãåæ§ç¯ã§ããããã«åœŒã«æçŽãæžããŸããã ãããŠãããŒã¿äŸçµŠãã¯ãããžãŒã«ã€ããŠãããæ·±ãç解ã§ããããã«ãªããŸããã ãããŠãããæ¢è£œã®ã³ã³ããŒãã³ãã䜿ã£ãŠããããç§å人ãšããŠã¯ããã®äœ¿çšæ³ãã€ãŸãèŠèŠåã®åŠç¿æ¹æ³ãããã«æªããªã£ãŠããã§ãããã ã§ããã¯ããRã¯å¥œãã§ãããããR for Dummiesããšããæ¬ã¯ãŸã èªãã§ããŸããã
ããããšãããããŸãïŒ
çŽ æŽãªçåã æšèãã¹ã±ãžã¥ãŒã«ããã°ããã¢ããããŒãããæ¹æ³ã¯ãããŸãã?
CSVãŸãã¯Excelã«ã¢ããããŒãã§ããŸãã
ããŒã¿ã§ã¯ãªããæ¢è£œã®ãã¬ãŒããæ¢è£œã®ã°ã©ãã§ãã? ããšãã°ãäžåžã«èŠããããã§ãã
ãã¢ããããŒãããã¿ã³ãšãã°ã©ããpngãjpgã§ã¢ããããŒãããã¿ã³ããããŸãã
ããããšãããããŸãïŒ
Tabix ãã€ã³ã¹ããŒã«ããããã® PS Mini æé
- ããŠã³ããŒã
ææ°ã®ãªãªãŒã¹ - 解åããŠãã£ã¬ã¯ããªãã³ããŒ
build
nginxã®root_pathå - nginxã®èšå®
åºæïŒ habr.com