|Initial release||June 15, 2016|
v18.14.15 / November 21, 2018
|Operating system||Linux, macOS|
|License||Apache License 2.0|
ClickHouse was developed by the Russian IT company Yandex for the Yandex.Metrica web analytics service. ClickHouse allows analysis of data that is updated in real time. The system is marketed for high performance.
ClickHouse is used by the Yandex.Tank load testing tool. Yandex.Market uses ClickHouse to monitor site accessibility and KPIs. ClickHouse was also implemented at CERN’s LHCb experiment to store and process metadata on 10 billion events with over 1000 attributes per event, and Tinkoff Bank uses ClickHouse as a data store for a project.
Yandex.Metrica previously used a classical approach, when raw data was stored in aggregated form. This approach can help reduce the amount of stored data. However, it has several limitations and disadvantages:
- The list of available reports must be pre-determined, and there is no way to make a custom report.
- The volume of data can increase after aggregation. This happens when data is aggregated by a large number of keys or using keys with high cardinality (such as URLs).
- It's difficult to support logical consistency around reports with different aggregations.
A different approach is to store unaggregated data. Processing raw data requires a high-performance system, since all calculations are made in real time. To solve this problem, a column-oriented DBMS is needed that can handle analytical data on the scale of the entire Internet. Yandex began developing its own. The first ClickHouse prototype appeared in 2009. By the end of 2014, Yandex.Metrica version 2.0 was released. The new version has an interface for creating custom reports and uses ClickHouse for storing and processing data.
The main features of the ClickHouse DBMS are:
- True column-oriented DBMS. Nothing is stored with the values. For example, constant-length values are supported to avoid storing their length "number" next to the values.
- Linear scalability. It's possible to extend a cluster by adding servers.
- Fault tolerance. The system is a cluster of shards, where each shard is a group of replicas. ClickHouse uses asynchronous multimaster replication. Data is written to any available replica, then distributed to all the remaining replicas. ZooKeeper is used for coordinating processes, but it's not involved in query processing and execution.
- Capability to store and process petabytes of data.
- SQL support. ClickHouse supports an extended SQL-like language that includes arrays and nested data structures, approximate and URI functions, and the availability to connect an external key-value store.
- High performance.
- Data compression.
- Hard disk drive (HDD) optimization. The system can process data that doesn't fit in random-access memory (RAM).
- Clients for database (DB) connectivity. Database connection options include the console client, the HTTP API, or one of the wrappers (wrappers are available for Python, PHP, NodeJS, Perl, Ruby and R). A JDBC driver is also available for ClickHouse.
- Detailed documentation.
ClickHouse has some features that can be considered disadvantages:
- There is no support for transactions.
- By default when performing aggregations the query intermediate states must fit in the RAM on a single server. However ClickHouse can be configured to spill on the disk in such case.
- Lack of full-fledged UPDATE/DELETE implementation.
- It works with a small number of tables that contain a large number of columns.
- Queries can use a large number of rows extracted from the DB, but only a small subset of columns.
- Queries are relatively rare (usually around 100 RPS per server).
- For simple queries, latencies of about 50 ms are allowed.
- Column values are fairly small, usually consisting of numbers and short strings (for example, 60 bytes per URL).
- High throughput is required when processing a single query (up to billions of rows per second per server).
- A query result is mostly filtered or aggregated.
- Data update uses a simple scenario (usually batch-only, without complicated transactions).
One of the common cases for ClickHouse is server log analysis. After setting regular data uploads to ClickHouse (it's recommended to insert data in fairly large batches with more than 1000 rows), it's possible to analyze incidents with instant queries or monitor a service's metrics, such as error rates, response times, and so on.
ClickHouse can also be used as an internal data warehouse for in-house analysts. ClickHouse can store data from different systems (such as Hadoop or certain logs) and analysts can build internal dashboards with the data or perform real-time analysis for business purposes.
- "Usage Statistics and Market Share of Traffic Analysis Tools for Websites, November 2016". w3techs.com. Retrieved 2016-11-10.
- Datanyze. "Analytics Market Share Report | Competitor Analysis | Google Analytics, Google Universal Analytics, Yandex Metrica". Datanyze. Retrieved 2016-11-10.
- Wappalyzer (2011-12-30). "Analytics". wappalyzer.com. Retrieved 2016-11-10.
- "Analytics - SEOMON.com". seomon.com. Retrieved 2016-11-10.
- "ClickHouse: High-Performance Distributed DBMS for Analytics | Percona Live Amsterdam - Open Source Database Conference 2016". www.percona.com. Retrieved 2016-11-10.
- "Яндекс открывает ClickHouse". Retrieved 2016-11-10.
- "Здоровье Маркета: как мы превращаем логи в графики, Дмитрий Андреев (Яндекс) — События Яндекса". events.yandex.ru. Retrieved 2016-11-10.
- "Yandex — Yandex Launches Search Tool for LHC Events at CERN". Yandex. Retrieved 2016-11-10.
- "Сравнение аналитических in-memory баз данных". Retrieved 2016-11-10.
- "Эволюция структур данных в Яндекс.Метрике". Retrieved 2016-11-10.
- "ClickHouse Guide". clickhouse.yandex. Retrieved 2016-11-10.
- "Performance comparison of analytical DBMS". clickhouse.yandex. Retrieved 2016-11-10.
- "smi2/phpClickHouse". GitHub. Retrieved 2016-11-10.
- "apla/node-clickhouse". GitHub. Retrieved 2016-11-10.
- "elcamlost/perl-DBD-ClickHouse". GitHub. Retrieved 2016-11-10.
- "archan937/clickhouse". GitHub. Retrieved 2016-11-10.
- "hannesmuehleisen/clickhouse-r". GitHub. Retrieved 2016-11-10.
- "yandex/clickhouse-jdbc". GitHub. Retrieved 2016-11-10.