ClickHouse: Intro & Scalability

Presented at Software Circus Amsterdam meetup "The shiny new and the old and trusted - databases in the cloud era"
https://www.meetup.com/Software-Circus/events/244273409/

Transcript

1. CLICKHOUSE OPEN-SOURCE COLUMN-ORIENTED DATABASE MANAGEMENT SYSTEM THAT ALLOWS GENERATING ANALYTICAL DATA REPORTS IN REAL TIME Created by Yegor Andreenko / @f1yegor
2. WHOAMI Yegor Andreenko @f1yegor Software Engineer at Crobox Data Storages lover
3. CROBOX combines consumer psychology with machine-learning to influence (purchase) decisions In one line of code
4. HISTORY Yandex development MyISAM 2008-2011 Metrage 2010-now OLAPServer 2009-2013 ClickHouse 2013-now
5. REQUIREMENTS Be able to work with Big Data - 11,4 trillion rows(2015) Linear scalability - from 60 to 394 in 2 years, 1 Tb/s of uncompressed data High performance Enough functionality for tools of web analytics. SQL dialect (benchmarks)
6. COMPETITORS Commercial OLAP systems on-premise HP Vertica, Actian Vector, Actian Matrix, EXASol, Sybase IQ, etc. Difference: open-source and free Cloud solutions Amazon Redshift and Google BigQuery. Difference: can run you on own hardware
7. COMPETITORS Hadoop solutions Cloudera Impala, Spark SQL, Facebook Presto, Apache Drill. Difference: web user-service, hadoop-less, geo- distribution Open-source OLAP DBMS InfiniDB, MonetDB, LucidDB. Difference: they are inmature or abandoned, non- distributed
8. COMPETITORS Open-source analytical systems that is not Relational OLAP DBMS Metamarkets Druid, Apache Kylin. Difference: ClickHouse doesn't require preaggregation, has SQL support
9. WHAT IS CLICKHOUSE? ClickHouse is a columnar DBMS for OLAP. In a "normal" row-oriented DBMS, data is stored in this order 5123456789123456789 1 Eurobasket - Greece - Bosnia and Herzegovina 1 2011-09-01 01:03:02 6274717 5234985259563631958 0 Consulting, Tax assessment, Accounting, Law 13 2011-09-02 01:03:20 6320881 5234985259563623423 2 Accounting, Tax assessment 3 2011-09-04 01:03:445 6320890
10. WHAT IS CLICKHOUSE? In a column-oriented DBMS, data is stored like this: WatchID: 5385521489354350662 5385521490329509958 538552148995 3706054 5385521490476781638 5385521490583269446 5385521490218 868806 5385521491437850694 5385521491090174022 53855214907926 69254 5385521490420695110 5385521491532181574 538552149155969 4406 5385521491459625030 5385521492275175494 53855214927813182 14 5385521492710027334 5385521492955615302 53855214937087591 10 5385521494506434630 5385521493104611398 JavaEnable: 1 0 1 0 0 0 1 0 1 1 1 1 1 1 0 1 0 0 1 1 Title: Yandex Announcements - Investor Relations - Yandex Yandex — Con tact us — Moscow Yandex — Mission Ru Yandex — History — History of Yandex Yandex Financial Releases - Investor Relations - Yandex Yandex — Loc ations Yandex Board of Directors - Corporate Governance - Yandex Yande x — Technologies GoodEvent: 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 EventTime: 2016-05-18 05:19:20 2016-05-18 08:10:20 2016-05-18 07:38:0 0 2016-05-18 01:13:08 2016-05-18 00:04:06 2016-05-18 04:21:30 2016 -05-18 00:34:16 2016-05-18 07:35:49 2016-05-18 11:41:59 2016-05-18 01 :13:32 ...
11. DISTINCTIVE FEATURES OF CLICKHOUSE True column-oriented DBMS. Data compression. Disk storage of data(not only RAM). Parallel processing on multiple cores. Distributed processing on multiple servers. Indexes. Vector engine.
12. DISTINCTIVE FEATURES OF CLICKHOUSE SQL support. NULLs are not supported. All the functions have different names. However, this is a declarative query language based on SQL that can't be differentiated from SQL in many instances. JOINs are supported. Subqueries are supported in FROM, IN, JOIN clauses.
13. DISTINCTIVE FEATURES OF CLICKHOUSE Real-time data updates. Suitable for online queries. Support for approximated calculations. Support for nested data structures. Support for arrays as data types. Support for restrictions on query complexity, along with quotas. Data replication and support for data integrity on replicas. Uses asynchronous multimaster replication.
14. INTERFACES 1. HTTP 2. JDBC driver 3. Third-party client libraries There exist third-party client libraries for Scala, Python, PHP, Go, Node.js, Perl 4. Native interface (TCP) 5. Command-line client $ curl 'http://localhost:8123/' $ wget -O- -q 'http://localhost:8123/?query=SELECT 1'
15. TABLE ENGINES Memory stores data in RAM, in uncompressed form. .... Replicated SummingMergeTree AggregatingMergeTree ReplacingMergeTree ...the power in table engines
16. TABLE ENGINES MergeTree family Example without sampling support: MergeTree(EventDate, (CounterID, EventDate), 8192) Example with sampling support: MergeTree(EventDate, intHash32(UserID), (CounterID, EventDate, intHash32(UserID)), 8192)
17. DISTRIBUTED <remote_servers> <logs> <shard> <weight>1</weight> <internal_replication>false</internal_replication> <replica> <host>example01-01-1</host> <port>9000</port> </replica> <replica> <host>example01-01-2</host> <port>9000</port> </replica> </shard> <shard> <weight>2</weight> <internal_replication>false</internal_replication> <replica> <host>example01-02-1</host> <port>9000</port> </replica> <replica> <host>example01-02-2</host>
18. DISTRIBUTED DISTRIBUTED COORDINATOR <!-- ZooKeeper is used to store metadata about replicas, when using Replicated tables. Optional. If you don't use replicated tables, you could omit that. See https://clickhouse.yandex/reference_en.html#Data%20replication --> <zookeeper> <node> <host>zookeeper.service.consul</host> <port>2181</port> </node> </zookeeper>
19. DISTRIBUTED ⚠ Worth to notice that the approach given above wouldn't fit for sharding of large tables. Please use built-in sharding feature. TUTORIAL CREATE TABLE ontime_local (...) ENGINE = MergeTree(FlightDate, (Year, FlightDate), 8192); CREATE TABLE ontime_all AS ontime_local ENGINE = Distributed(logs, default, ontime_local, rand()); INSERT INTO ontime_all SELECT * FROM ontime;
20. DISTRIBUTED RESHARDING ALTER TABLE t RESHARD [COPY] [PARTITION partition] TO cluster description USING sharding key ALTER TABLE merge.hits RESHARD PARTITION 201501 TO '/clickhouse/tables/01-01/hits' WEIGHT 1, '/clickhouse/tables/01-02/hits' WEIGHT 2, '/clickhouse/tables/01-03/hits' WEIGHT 1, '/clickhouse/tables/01-04/hits' WEIGHT 1 USING UserID
21. DISTRIBUTED <!-- Substitutions for parameters of replicated tables. Optional. If you don't use replicated tables, you could omit that. See https://clickhouse.yandex/reference_en.html#Creating%20replicated%20tables --> <macros> <shard>01</shard> <replica>01</replica> </macros>
22. EXPORT/IMPORT FORMATS Native CSV JSON JSONEachRow TSV
23. DEMO ... PROBABLY ANOTHER TIME
24. DOCKER docker run -d --net=host --restart=always \ -v `pwd`/config:/etc/clickhouse-server \ -v `pwd`/data:/opt/clickhouse \ yandex/clickhouse-server:1.1.54310 docker run -it --rm --net=host \ yandex/clickhouse-client clickhouse-client -m
25. DOCUMENTATION Tutorial Reference Forum Chat Benchmarks
26. QA WANT TO HEAR THE FULL STORY? Yegor Andreenko @f1yegor