Terlepas dari kenyataan bahwa ada banyak data sekarang hampir di mana-mana, database analitik masih cukup eksotis. Mereka kurang dikenal dan bahkan lebih buruk lagi tahu bagaimana menggunakannya secara efektif. Banyak yang terus "makan kaktus" dengan MySQL atau PostgreSQL, yang dirancang untuk skenario yang berbeda, berjuang dengan NoSQL, atau membayar lebih untuk solusi komersial. ClickHouse adalah pengubah permainan dan secara signifikan menurunkan ambang batas untuk memasuki dunia DBMS analitis.
Laporan dari BackEnd Conf 2018 dan diterbitkan dengan seizin pembicara.
Siapa saya dan mengapa saya berbicara tentang ClickHouse? Saya adalah Direktur Pengembangan di LifeStreet, yang menggunakan ClickHouse. Juga, saya adalah pendiri Altinity. Ini adalah mitra Yandex yang mempromosikan ClickHouse dan membantu Yandex menjadikan ClickHouse lebih sukses. Saya juga siap membagikan pengetahuan saya tentang ClickHouse.
. . , .
Β« Β», ClickHouse:
- ,
- ,
- .
, .
, , ClickHouse, .
, ClickHouse , ClickHouse , .
, ClickHouse . , .
: Β« ClickHouse?Β». , , .
- β . ClickHouse . ClickHouse . , - .
- β . . , Vertica β . , . , . . ClickHouse .
- β . - . RedShift β . RedShift . , , Amazon, . Google BigQuery . - , , .
ClickHouse .
ClickHouse ? ClickHouse .
- -, . . use case, .
- AdTech ClickHouse.
- , .
- ClickHouse . ClickHouse, , .
- , . . ClickHouse.
- CloudFlare. - ClickHouse , . community. ClickHouse-. , Kafka Engine ClickHouse.
- . ClickHouse proof on concept, production.
- ClickHouse . , , 2 000 . β .
- -. , Bloxy.info. ethereum-. ClickHouse.
. , . . .
, :
- : 500+ , 25 .
- LifeStreet: 60 , 75 . , , .
- CloudFlare: 36 , 200 . .
- Bloomberg: 102 , . .
β . heatmap, ClickHouse . , , . . 4 .
, . , Altinity, . , , , . . , . . . , . , ClickHouse β , , .
? , ClickHouse . , . , , , .
ClickHouse .
- β : Vertica ClickHouse. , Vertica .
- β ClickHouse. . , ClickHouse , . , . , .
- β ClickHouse. , ClickHouse Hadoop . , ClickHouse - map reduce , . , .
- LifeStreet β Ad Tech , , .
- , programmatic bidding.
- : 10 . .
- , , β , programmatic bidding.
. HighLoad. LifeStreet MySQL ( Oracle) Vertica. .
, , Vertica β . . . proof of concept performance testing , 13- 16- . HighLoad.
β Vertica , . . , . , , - , , . , , .
? , , , . , Vertica, . , .
, open source , , . . . , . .
, , , open source, β .
, , , ClickHouse. , : Β«?Β», .
2016- , ClickHouse. , Vertica. . , . . (join), ClickHouse Vertica .
. : ClickHouse Vertica, .
(join), . ClickHouse Vertica . - , . . .
, , LifeStreet ClickHouse.
16- , . , , . , . .
, , .
β :
- .
- . 10 , , LifeStreet 75 3 . , . SQL- , .
- , ClickHouse , Vertica, , SAS-. ClickHouse SATA. ? Vertica insert . , , , , . . . ClickHouse insert . , , , ClickHouse , . . SATA, RAID , .
- , . . 3 60 (20 β ) 6 . Vertica .
.
- β . .
- β SQL.
OLAP- β select. group by, . where, . group by . .
star-, , .
, , , . , . , . . - .
ClickHouse . :
- β ClickHouse (join), . . (join) , . .
- β , . , star-, - . , , . .
ClickHouse . :
- β . External Dictionaries β , 99 % star-, .
- β . (join) .
- (join).
- . 2018- ( ) , . . , . β .
- , (join) , , , , , , .
- (join).
- 1 .
- , . - .
. , .
, . :
- . - .
- key-value . - .
- , - .
. - , (join).
ClickHouse , string key-value.
β , , . , .
. has, , . , , .
subid - . , , , . .
, , , , -, . , -, , .
. , ID. . arrayMap. -. -. ID .
. , .
.
, , .
- ClickHouse . .
- . ?
- (join), . , , .
- β . , , . (join), .
- .
. , -5 . 2,5 , -. , - . , , (int). . . 2,5 1,5 . .
. . 5 . , , - , , .
. , , , , , . .
- .
- , .
- - (join), , , , (join) .
- .
, , . .
. . ?
:
- - .
- .
?
, , 20 , . - , , . , , - .
: Β« , ?Β» Β« , ?Β». . . , , , ?
, , - , . . - . DWH. , .
β , , - , , , , . , , , - , . , . .
.
β :
- Last Interaction, interaction β , .
- First Interaction, . . , .
- β .
- .
- .
? Runtime Cassandra. Cassandra transaction storage, . . . - Runtime, , - - , Cassandra β . , . .
, transaction id, . . . .
, . , , 10 , 300 , . Cassandra , , Runtime , 10-15 .
, . ? , 30 . , , 30 . , - . 500 , , , Runtime , - . , .
ClickHouse. ClickHouse? , .
- , , . . mutable, ClickHouse mutable-.
- , , visit id. point query, ClickHouse . ClickHouse β¦, . .
- , json, , json , , - . .
. . .
, .
? ClickHouse, , . attributed , ClickHouse . visit id , , . . , . , , . . ClickHouse, . . . , DWH, .
. ClickHouse , visit id, 1 000-2 000 visit id 1 000-2 000 . .
ClickHouse, 3 , .
, , .
. materialized view , attributed , . . . materialized view . . . materialized view , .
SQL. .
β ClickHouse json , . . . ClickHouse json. - .
visitParamExtractInt json , . . . transaction id visit id. .
β materialized . ? , , . . , . ClickHouse . json , .
materialized view β . . ? -, , . . visit id, .
β index_granularity. MergeTree, 8 192 index_granularity. ? . ClickHouse , . 8 192. , , , , overhead. index granularity, overhead. , . .
ClickHouse.
-, AggregatingMergeTree. AggregatingMergeTree argMax, . . , timestamp. . . ClickHouse. argMax .
- «» Runtime.
- 3 . , Cassandra, . . .
- 25 ClickHouse. 5 . , Cassandra , click based , impression based. . . , 30 , .
β Y, .
:
- 5 000 .
- 100 .
- 10 . , , .
- 100 .
.
. , , , . . , . , , , , . . , .
.
. ? 100 , : , . 100 runningDifference . RunningDifference β ClickHouse, .
, . 5 000 12,5 . , . . 12,5 .
- , Νx Νy β . . . . , . - 12,5 , . . 60 . .
-, - , , ClickHouse.
Hadoop , Spark, Greenplum. . . . - , .
ClickHouse .
, , . , , .
? . . . logReturn, .
, . , . . . . . , . . .
. Β«rΒ» , . . . β .
12,5 , , . . . 2 500 5 000 . ClickHouse-. , .
, . : , , . logReturn, . . , logReturn. , . . groupArray . . ClickHouse, . .
proof of concept β , . . . .
: Log_return .
- 50 . 50 β , . . , 70 .
, , . . . . .
- β . β ClickHouse.
- Summing/AggregatingMergeTrees β , state . .
- Materialized Views . , , , , attribute , . . , , . , . Materialized Views , , ClickHouse.
- .
- , . , , .
, , ClickHouse , open source , . . . . , , , ClickHouse, InfiniDB. , , , SQL. !
- ! ! - Apache Phoenix?
-, , - . ClickHouse . - ClickHouse, . .
( ) Apache Phoenix β SQL- Hbase. Hbase key-value. . Hbase, Cassandra. . , , ClickHouse.
-
! , . ClickHouse, , ClickHouse , mutable. - , ClickHouse, , ? , . , ?
. . , , mutable, , . . . ClickHouse Oracle, - . ClickHouse , , ClickHouse . . . , , . , - ClickHouse , .
! . . , -. , ? , , , - , ? . . - , ?
, ClickHouse . Hadoop. Hadoop β . Hadoop . β , ClickHouse , , . . , - , .
. , 50 . , ?
-.
, .
3- .
! ! . , ClickHouse . . . -, ? ClickHouse? , ? , , ClickHouse , - .
, . ClickHouse . , . ? , . - 2012-2013- ClickHouse. . . - , , . . ClickHouse β . . . .
! , . ? -. , , , - .
, , . . , Materialized View, , . . . ClickHouse, . . extract - , primary key, , Materialized View. , . Materialized View, . .
.