SQL for ClickHouse DBAs

The DataBase Administrator (DBA) uses particular SQL statements to extract the most interesting information from the databases he administers.
This article describes some useful SQL commands a DBA must know to manage a ClickHouse database.

ClickHouse is a recent and very fast Open Source columnar database suitable for analytical queries. Even if ClickHouse has unique capabilities DBA's tasks are similar to other relational database administration tasks.

The document is deliberately short and practical with working examples of SQL statements.

Introduction

ClickHouse is a SQL Columnar Database, distributed and Open Source with outstanding performances on OLAP (On-Line Analytical Processing) activities. ClickHouse is simple to install, to manage and to use with a standard SQL.
In ClickHouse data is not stored by rows but organized by columns and partitioned. In addition to column organization and partitioning, ClickHouse uses compression and vectorized parallel algorithms. With queries, ClickHouse divides the work into multiple threads and performs operations in memory using all the available CPUs and all the needed RAM. This makes queries incredibly faster than a traditional RDBMS, especially when all data has to be analyzed. ClickHouse is also extremely fast on simple queries giving a nearly real time response whereas noSQL databases tipically have high latencies.

On this page we will make an overview of the most useful and interesting SQL statements for the ClickHouse DBAs.

Sessions

A first overview of the database usage is the list of the current and active connections. From the operating system the sessions connected to the database are not visibile since ClickHouse uses only one process and one thread for each connection: DBAs need an SQL statement!

SHOW PROCESSLIST; SELECT query_id, user, address, elapsed, query FROM system.processes ORDER BY query_id;

The first command is the simplest one [NdA similar to MySQL, isn't it?], the second SQL command allows you to put conditions and sort the results. Both commands extract active queries ony, not all connections to the database.
To get the number of total connections the query is:

SELECT * FROM system.metrics WHERE metric LIKE '%Connection';

This query returns the number of connections only, not the list as the previous queries; however the total includes all the connections to the ClickHouse database.

As an experienced DBA should notice last two queries used the system database: system it is the most important database for the DBA since it contains views for both data dictionary, current and logged activities.

Lock

There are not locks in ClickHouse!
ClickHouse performs INSERT asynchronously: the MergeTree Engine collects and inserts the data in Parts, that can be merged later. New data is visible only at the end of the process. UPDATE and DELETE are not DML clauses in ClickHouse. This particular behavior makes locks infrequent and short-lived.
Anyway it is still possible that there are long-lasting queries... these can be checked with SHOW PROCESSLIST command and can be interrupted with KILL command:

SHOW PROCESSLIST; KILL QUERY WHERE query_id='trx_id';

Recent ClickHouse versions introduced UPDATE and DELETE commands as DDLs. They are called mutations and they are performed asynchronously. It is not possible to rollback mutations... but it is possible to interrupt them when they hangs. The commands are similar to the prevoius ones:

SELECT * FROM system.mutations; KILL MUTATION mutation_id = 'trx_id';

Ethical note: before killing someone... it is important to be sure of what You are doing!

Space Usage

ClickHouse space management is very important. Tipical data warehouse databases are very large. Even if ClickHouse uses sofisticated data compression algorithms it is important for a ClickHouse DBA too.
Let's start with the used space in the database:

SELECT database, table, partition, name part_name, active, bytes_on_disk FROM system.parts ORDER BY database, table, partition, name;

The previous query is very detailed since reports every Part, Partition, Table, ... but you can use GROUP BY to get summary values by database, by table or by partition.
During data entry, parts are not immediately consolidated into partitions with a merge step. To force the merge the following command can be used:
OPTIMIZE TABLE table [PARTITION partition] [FINAL]

The compression factor is very important and this is different for each column. Here is the query to check the space usage for each column:

SELECT database, table, column, any(type), sum(column_data_compressed_bytes) compressed, sum(column_data_uncompressed_bytes) uncompressed, sum(rows) FROM system.parts_columns WHERE active AND database <> 'system' GROUP BY database, table, column ORDER BY database, table, column;

A recent feature in ClickHouse is the TTL (Time To Live) clause. TTL allows to define the data retention. It is particularly useful for Time Series data and simplifies space management.
Here is the syntax: alter table mytable MODIFY TTL time_column + interval 3 MONTH DELETE;

Performance

Performance optimization is the leitmotiv of each DBA.
By default CH does not trace the queries performed but it is possible to do so by setting the parameter log_queries = 1 at the session level or in the users.xml configuration file, I strongly suggest to set it.

SELECT user, client_hostname AS host, client_name AS client, formatDateTime(query_start_time, '%T') AS started, query_duration_ms/1000 AS sec, round(memory_usage/1048576) AS MEM_MB, result_rows AS RES_CNT, toDecimal32(result_bytes/1048576, 6) AS RES_MB, read_rows AS R_CNT, round(read_bytes/1048576) AS R_MB, written_rows AS W_CNT, round(written_bytes/1048576) AS W_MB, query FROM system.query_log WHERE type=2 ORDER BY query_duration_ms DESC LIMIT 10;

In ClickHouse there aren't indexes but You can specify ORDER BY columns: a correct design of the database is very important.
A particularly useful feature for performance are Materialized Views which allow you to define alternative views to the data. Materized Views can consolidate data or use different order. Analyzing the heaviest and most recurring queries allows you to address the design of materialized views.

If You use a recent ClickHouse version You can use the following intresting query:

SELECT toStartOfMinute(event_time) AS time, sum(ProfileEvent_UserTimeMicroseconds) AS user_time, bar(user_time, 0, 60000000, 80) AS bar FROM system.metric_log WHERE event_date = today() GROUP BY time ORDER BY time;

Replication

ClickHouse clusters allow both Replication for HA and concurrent access and Sharding for distribuite queries and for high INSERT throughput. The configuration is very flexible since replication and sharding can be defined for a single table.

ClickHouse replication is asynchronous and multimaster (internally it uses ZooKeeper for Quorum). Its main goal is HA (High Availability) but if something goes wrong... here's how to check for failed or delayed replicas:

SELECT database, table, is_leader, total_replicas, active_replicas FROM system.replicas WHERE is_readonly OR is_session_expired OR future_parts > 20 OR parts_to_check > 10 OR queue_size > 20 OR inserts_in_queue > 10 OR log_max_index - log_pointer > 10 OR total_replicas < 2 OR active_replicas < total_replicas;

/etc

In this article we presented some useful SQL statements for ClickHouse DBAs.
We used tipical DBA SQL statements but can also be seen as a simple introduction on how ClickHouse works...

Title: SQL for ClickHouse DBAs
Date: 1st May 2020
Version: 1.0.1 - 1st May 2020
Author: mail [AT] meo.bogliolo.name