Managed Service for ClickHouse

ClickHouse is a columnar database management system optimized for online analytical query processing (OLAP).

ClickHouse was originally developed to support the Yandex.Metrics web analytics platform, and then it was separated into a separate open-source project. You can evaluate ClickHouse's capabilities by Yandex's claims that their database can successfully handle 13 trillion records and 20 billion events per day, generating customized reports on the fly.

The data in ClickHouse has a columnar (columnar) organization, in which values of a single attribute are grouped together. This allows you to efficiently obtain masses of values of specific attributes, analyze their mutual influence and patterns. Queries that require access only to specific columns (attributes) can be executed very quickly and efficiently with columnar organization of data.

On the other hand, in a traditional row-based database organization, data is stored row-by-row, where each record groups all attribute values for a particular object. Such data organization is effective for operations that require access to all attributes of a particular object at once, but is less effective when performing analytical queries that operate with masses of data on individual attributes.

Along with columnar data organization, ClickHouse implements a number of measures aimed at improving performance:

• No unnecessary data

ClickHouse supports constant-length values so that values of type "number" don't have to be stored next to their length.

• Support for data compression

Data compression implemented in ClickHouse plays an important role in ensuring good performance.

• Storing data on a regular hard disk drive

Many columnar DBMSs can only run in RAM. ClickHouse allows you to use hard disks for data storage.

• Parallel query processing

ClickHouse implements efficient query parallelization, maximizing the resources available on the server.

• Distributed query processing

In ClickHouse, a query can be executed on all distributed shards (database segments) in parallel.

• SQL support

ClickHouse has its own SQL-based query language and in many cases its syntax is the same as SQL.

• Vector engine

Data in ClickHouse can be processed by vectors, column fragments. This results in high processing efficiency.

• Real-time data update

ClickHouse supports primary key tables to speed up the execution of primary key range queries. Continuous addition of data to the table without locking is available.

• Index availability

Physical sorting of data by primary key allows you to retrieve data for its specific values or their ranges with low latency.

• Suitable for online queries

Low latency allows you to respond online instead of delaying the query execution.

Support for approximate calculations

ClickHouse gives various ways to lower the precision of computation when it is not needed, in return getting a performance boost.

The disadvantages of ClickHouse or its features include:

• Lack of implementation of full-fledged transactions.
• Deleting and modifying specific data has high latency, but there are efficient means of mass deletion and modification of data.
• The sparse index makes ClickHouse ill-suited for point reads of single rows.
• Does not support ANSI SQL 2008 or PostgreSQL.
• Does not know how to do local and distributed JOINs.

ClickHouse is not suitable for operations on key-value data, such operations in ClickHouse can be performed, but with high latency and low performance. However, ClickHouse would be a good option for time series databases, providing high query execution speed. The purpose of ClickHouse is primarily analytics, and for other purposes it is probably better to use other DBMSs.

Typical tasks for which ClickHouse is used are:

• Online real-time analytics

ClickHouse allows you to run analytic queries in real-time, providing low latency in query response. At the same time, ClickHouse offers powerful aggregation, grouping, filtering and sorting capabilities, making it effective for performing complex analytical queries, including multivariate analysis, data segmentation, calculation of statistical indicators, web traffic analytics, financial analysis, etc.

• Big Data Analytics

ClickHouse is capable of processing and analyzing huge amounts of data. It efficiently handles terabyte-sized datasets and provides high performance for queries that require access to huge amounts of data.

• Trend detection and behavior prediction

ClickHouse is widely used to process event logs, logs, audits and other event-driven data. It enables real-time analysis of this data, identifying trends and system issues, predicting system behavior, etc.

• IIoT (Industrial Internet of Things) analytics

ClickHouse is used to process and analyze data generated by various IIoT devices, sensors and controllers. It is capable of processing streaming data, performing real-time aggregation and analytics, and storing historical data for later analysis. For example, use it for production planning, assessing equipment performance, identifying bottlenecks and predicting faults.

• Analyze marketing performance

ClickHouse can be a useful tool for analyzing marketing performance. It can be used to track impressions, clicks, conversions, etc., segment data, calculate marketing performance indicators based on them, and create reports.

Managed Service for ClickHouse is a service that provides a ClickHouse database management system ready for use, with a cluster that uses cloud architecture for hosting.

You can focus on working with the database, enjoy all its benefits, and we will take care of the technical issues of organizing the database cluster and its operation.

Our area of responsibility includes:

• database cluster deployment and its preliminary configuration;
• monitoring and management of the cluster;
• scaling of the cluster;
• ensuring high availability and fault tolerance of the database;
• data backup;
• ensuring data security and restricting access through authorization and encryption;
• maintaining and repairing the infrastructure on which the cluster is hosted;
• providing technical support and access to technical documentation on the service.

By default, automatic cluster backups are performed every 24 hours between 1:00 AM and 5:00 AM GMT+4. During the backup, access to the clusters is preserved. Backups are stored for 7 days after creation. You can restore data to any saved backup.

The following release versions are available from ClickHouse:

• Stable is a stable version of the product, released about once a month, which opens access to new features. The developer maintains the last three stable releases, with bug fixes and enhancements.
• LTS is a product version with long-term support, released twice a year. LTS releases are supported for one year from the date of release. This version uses only solutions that have been repeatedly tested in practice. LTS is preferred if you use complex ClickHouse tools and mechanisms, or if you do not have the resources to frequently update your ClickHouse products.

If the DBMS version you are using is no longer supported by the developer, you will still have access to your data. However, in this case, the creation of new cluster nodes will not be available and other features may be restricted. You will be notified by e-mail and in your personal cabinet about automatic upgrade to the nearest supported version, about the dates of work and database availability. Such update will be performed even if you have disabled automatic update.

The client is notified about Stable version upgrade at least 7 days in advance, about LTS version upgrade at least 1 month before the automatic upgrade. Upon receipt of such notification, the customer should check the software interfacing with ClickHouse for compatibility with the new version. If necessary, the customer should take steps to ensure compatibility with the new version.

It means that the database management system has online analytical processing functionality (OLAP - online analytical processing). This means that the DBMS has an architecture and tools optimized for analytical operations on the data stored in the database. As a rule, such DBMSs can be effectively used only for analytical tasks and have limited efficiency for traditional transactional OLTP tasks.

Our service is compatible with any applications and tools that have been developed for ClickHouse DBMS. There is a web client, console client, HTTP interface, ODBC and JDBC drivers, as well as ready integration libraries with popular programming languages and libraries for working with the databa

Sharding is a horizontal cluster scaling strategy in which the ClickHouse database is partitioned and hosted in different segments, otherwise known as shards. Each shard is hosted on a separate host or can have multiple replica hosts. A read or write request to a shard can be sent to any of its replicas. Data written to one of the replica hosts will be replicated to the other replica hosts of the shard.

Sharding solves the following problems when used:

• Increases the fault tolerance of the database

Since the database is divided into segments, the failure of one segment will not lead to the failure of the entire database, but only that part of the data that is hosted in the failed segment (shard). If a shard has multiple replicas, the failure of one of its replicas will only reduce system performance and will not result in loss of data availability.

• Increases the speed of query execution

Sharding reduces competition for computational resources during query processing. In a sharded database, queries for the same table can be executed simultaneously.

• Increases the scale of the system

Sharding allows you to overcome the physical limitations of the computing infrastructure, split the database into multiple servers and place it in several data centers.

• Load balancing

Sharding distributes the load between shards as well as between their replicas.

Sharding is usually used when a high frequency of database queries is expected, when the amount of data is expected to grow rapidly, when the database has exceeded such a size that it cannot be hosted on a single host.