ClickHouse in a few clicks

In one of the first articles on our blog, we talked about how we deploy not just vanilla databases, but ones prepared for working inside Kubernetes on the platform dBrain.cloud. It is possible to implement any database in dBrain. Today we want to talk about this process using the example of one case – the implementation of a NoSQL Clickhouse database.

We closely monitor trends in the development and architecture of microservice applications, and also focus on user requests, which is why Clickhouse has become part of the dBrain platform.

Clickhouse is a high-performance database for performing complex analytical queries. On one of our projects with a high-load real-time service in the field of online lotteries, a request appeared to analyze statistical data collected from client applications. Previously, most of this information was available from a PostgreSQL relational database (more than 25 TB in size). The analytical workload was having a negative impact on the overall performance of this database, as it was necessary to fulfill other requests from end users throughout the system.

Therefore, we considered several options for transferring the load on analytical queries to other systems. The client was choosing between Timesscaledb, Greenplum, Elasticsearch, Cassandra and Clickhouse. In terms of speed and efficiency of data compression, the Clickhouse database turned out to be superior to others. After which we received a request to implement a new database into the dBrain platform.

We looked at possible options for implementing this base inside Kubernetes. Delivery by operator from Altinity quite popular, most of the code there is available in open source, which is what interested us. We also considered other options for assemblies for Kubernetes, and Altinity turned out to be preferable for a number of indicators: a wide community and its quick response to most issues, a tool for backing up the database, and the operator’s own API.

API Implementation

On the frontend, we send settings to the backend using our service, which transfers the data to Clickhouse-operator. We build a description of the cluster, send it to Kubernetes – the operator deploys Clickhouse. Initially, we created the description of the database ourselves and managed it, now we do everything through the operator. It already takes into account many errors, and we do not have to go through the deployment path again. The Altinity implementation has a full set of services: ClickHouse-operator, Clickhouse-backup, ClickHouse Keeper.

To manage ClickHouse clusters in dBrain on the backend, we use ClickHouse-operator (version 0.23.3). First, we implemented Clickhouse on Zookeeper, because we already had Kafka implemented on this database, then we added the ability to deploy with ClickHouse Keeper based on STS, and this year we switched to CRD Clickhouse Keeper Installations from the new version of the operator. It is less resource-intensive compared to Zookeeper, starts up faster, and is more convenient to configure via CRD.

Currently dBrain supports several versions of Clickhouse. We have provided users with Clickhouse deployment options in both minimal and highly available configurations with cluster state persistence in both Zookeeper and ClickHouse Keeper to choose from.

dBrain has the ability to deploy different Clickhouse configurations, depending on the required number of replicas. When creating a Clickhouse cluster, you can select the number of replicas and shards. Depending on this, the cluster will be in failover mode or not. In HA (high availability) mode, we make sure that the database pods are dispersed to different nodes, and if one of the servers is disconnected, access to the cluster remains.

We also fully consider all stages of the database life cycle: not only the deployment of the cluster itself, but also backup to S3 and restoration of backups from storage. We added these functions to the platform, having worked through all the scenarios at the customer’s request: it became possible to choose which tables need to be restored, with what retention to store backups, how many copies to leave locally and whether to leave them.

What features are available in the dBrain console

Through the dBrain console you can also manage users and restrictions Databases, Profile, Quota, backups, view information on databases and nodes.

For backups in dBrain we use Clickhouse-backup (version 2.4.33). When restoring and partially during backup, you can set the following options: limit databases/tables, restore only the schema, on which shards to perform the operation.

Monitoring and logging

Logs of all containers are centrally collected in a single platform logging system (you can read it here). As for the logging level, in our case, due to the small number of logs, this is info.

In monitoring (article about our system), we developed a separate approach, added new dashboards: like from Altinity itself, which allow you to monitor Clickhouse, Zookeeper or ClickHouse Keeper, depending on the installation. We also tested the Altinity plugin for Grafana and a dashboard that displays query statistics using this extension as a data source.

Deploy

Clickhouse, like any other database, is deployed centrally from the console. To deploy a new cluster, you just need to click a few buttons on the console and specify the required parameters.

The cluster linked to the dBrain console is very easy to deploy. A few clicks – and everything you need is already there: images uploaded to the repository, CRDs, configmaps, services, secrets for connecting to the database, a set of necessary StatefulSet, job, PVC and PV on the selected StorageClass, etc.

As they say, appetite comes with eating, so we are now working on implementing an intra-cluster version of Airflow for the same customer, developing the most convenient tool for migrating data from PG to CH. In Clickhouse, it is optimal to insert more than one key-value at a time; a common scenario is transferring data in batches from PG operational tables, followed by reading and verifying checksums as a way to verify data integrity.

We will be glad if the article turns out to be useful. Share the material with your colleagues, and we will stay in touch with you in the comments.