Why column-oriented?
How row-oriented systems work:
Author: Alexey Milovidov, 2017-06-30.
ClickHouse - distributed analytical column-oriented DBMS
How row-oriented systems work:
How column-oriented systems work:
Nothing ready-made was suitable.
So we made ClickHouse.
«Evolution of Data Structures in Yandex.Metrica»
* If you want to try ClickHouse, one server is enough.
We managed to make the system relatively user-friendly.
From the very beginning we had detailed documentation.
Within a couple of years, ClickHouse spread to other Yandex departments.
Mail, Market, Direct, Webmaster, AdFox, Infrastructure, Business Analytics...
There are cases when analysts independently installed ClickHouse on virtual machines and successfully used it without any questions.
Then we decided — ClickHouse is too good a system for us to keep it to ourselves.
To make it more fun, we need to get people outside hooked on ClickHouse, let them enjoy it. We decided to go open-source.
Apache 2.0 license — minimum restrictions.
Goal — maximum product distribution.
We want Yandex's product to be used worldwide.
See "Yandex Opens ClickHouse"
Well-structured, cleaned, immutable events.
Click stream. Web analytics. Ad networks. RTB. E-commerce.
Online game analytics. Sensor and monitoring data. Telecom data.
Financial transactions. Stock market analytics.
OLTP
ClickHouse has no UPDATE and full-fledged transactions.
Key-Value
If you need frequent update queries by key, use another solution.
Blob-store, document oriented
ClickHouse is designed for a large number of fine-grained data.
Over-normalized data
Better to make a wide fact table.
— out of desperation.
Yandex.Metrica must work.
Algorithmic optimization.
MergeTree, data locality on disk
— fast range queries.
Example: the uniqCombined function consists of a combination of three different data structures suitable for different cardinality ranges.
Low-level optimization.
Example: vectorized query execution.
Specialization and attention to detail.
Example: we have 17 different GROUP BY algorithms. The best one is chosen for your query.
Hundreds of companies in Russia and nearby
Yandex, Mail.ru, Rambler, SKB Kontur…
Dozens of companies in Europe, USA, China
Cloudflare, Wikimedia, Lifestreet, Vertamedia, Carto…
How do we analyze over O(100B) DNS requests daily.
"ClickHouse enables us and our customers to explore the the dataset in real time to get operational insights. Due to many of the optimizations built into ClickHouse we are able to store the data for a long time allowing us to look events is perspective and at historical trends."
"clickhouse is a columnar datastore that we are using as an aid to run complex SQL queries on the edit data "lake" that we have as a result of the edit reconstruction project. It is similar to Druid but faster for complex queries."
Search engine and analytics for Bitcoin transactions:
https://blockchair.com/
"Pretty large tables are running, only one server is used and everything works very quickly — with any filters and sorting, everything is almost instantaneous."
Bioinformatics - evolutionary genetics:
https://github.com/msestak/FindOrigin
"We are exploring evolution of novel genes in genomes because if seems that genomes are far from being static as previously believed and what actually happens is that new genes are constantly being added and old genes are lost."
LHCb experiment at LHC:
https://www.yandex.com/company/press_center/press_releases/2012/2012-04-10/
ClickHouse vs. Spark & MariaDB Column Store
https://www.percona.com/blog/2017/02/13/clickhouse-new-opensource-columnar-database/
ClickHouse vs. Redshift
https://www.altinity.com/blog/2017/6/20/clickhouse-vs-redshift
HTTP REST
clickhouse-client
JDBC (production), ODBC (beta)
Python, PHP, Perl, Go,
Node.js, Ruby, C++, .NET, Scala, R, Julia, Rust
Tabix (tabix.io) — developed specifically for ClickHouse.
And more:
Grafana, Redash, Apache Zeppelin,
Superset, Power BI…
Session support in HTTP interface:
curl 'http://host:port/?session_id=123&session_timeout=60' \
-d 'CREATE TEMPORARY TABLE t'
Table aliases
Qualified column names in queries:
SELECT t.column FROM table AS t
Quoted identifiers according to ANSI SQL:
SELECT "column" FROM "db"."table"
Type casting for Merge-type tables
input_format_allow_errors_* settings
Loading part of configuration from ZK
OPTIMIZE TABLE ... PARTITION ... DEDUPLICATE
ALTER of primary key: Enum, Date <-> UInt16, DateTime <-> UInt32
Distributed DDL queries:
CREATE TABLE table ON CLUSTER cluster ...
Disabling lagging replicas
Disabling replicas without the table
Original query source in system.processes, system.query_log
Dictionary table engine, Dictionary database engine:
CREATE DATABASE dictionaries ENGINE = Dictionary
Reloading dictionaries using user-defined state query
IP Trie dictionaries
Type casting in INSERT SELECT
INSERT SELECT: by positions instead of names
pointInEllipses
greatCircleDistance
-ForEach combinator
groupArrayInsertAt
topK (beta)
ODBC driver — build and functionality on Windows
HTTPS server
NULL for JOIN: join_use_nulls setting
NULLS FIRST, LAST for ORDER BY
NULL support in IN
NULL support in higher-order functions
if, multiIf, ifNull, nullIf, coalesce
toNullable, assumeNotNull
Nullable type support in aggregate functions
NULL as result of subquery returning empty set
ALTER ... CLEAR COLUMN ... IN PARTITION
preferred_block_size_bytes setting
KILL QUERY
LIMIT BY
SELECT INTO OUTFILE
clickhouse-local
cross-replication
UUID and MAC encoding functions
Proper HTTP response codes
Proper comparison operation logic
Progress in HTTP headers
Cache stampede fix
system.build_options, system.graphite
Distributed query tracing in system.processes, system.query_log
Ability to skip errors in text formats
fsync_metadata setting
timezone config parameter, timezone() function
decodeURLComponent
Faster gzip in HTTP interface
max_table_size_to_drop
proper build and packages
DISTINCT speedup
Buffer table optimization
FixedString optimization
RIGHT/FULL JOIN improvements
clickhouse --extract-from-config
system.parts - exact row count
system.columns - uncompressed size
system.part_log
SYSTEM queries
Limit on concurrent replica downloads
NULLs: fix almost all remaining issues
Custom partitioning key for MergeTree
Ability to write JOIN as in regular SQL
Resource pools (CPU, disk IO, network bandwidth) for queries
Initial UPDATE/DELETE support
Website: https://clickhouse.com/
Google groups: https://groups.google.com/forum/#!forum/clickhouse
Mailing list: [email protected]
Telegram chat: https://telegram.me/clickhouse_en and https://telegram.me/clickhouse_ru (already 778 participants)
GitHub: https://github.com/ClickHouse/ClickHouse/
+ meetings. Moscow, Saint Petersburg, Novosibirsk, San Francisco
Ekaterinburg, Minsk... Next: Nizhny Novgorod, Kazan, Berlin