ClickHouse 2021: New Features and Roadmap

ClickHouse 2021:
New Features and Roadmap

Window Functions

Does ClickHouse support Window Functions?

— Yes.

Window Functions

Initial support in version 21.1. Full support in 21.4.

SET allow_experimental_window_functions = 1

Implemented according to the SQL standard:

— aggregate functions over windows;
— WINDOW clause;
— frame specifications;
— non-aggregate window functions
  (rank, dense_rank, row_number, lagInFrame, leadInFrame, etc...);

Developer — Alexander Kuzmenkov.

Native Integration With PostgreSQL

You can query (read and write) PostgreSQL in ClickHouse queries*.

You can JOIN with PostgreSQL tables inside your queries to ClickHouse!

You can create a database that will have all the tables in PostgreSQL schema.

You can use PostgreSQL as a dictionary source.

And ClickHouse can also pretend to be PostgreSQL!

<!-- Compatibility with PostgreSQL protocol. ClickHouse will pretend to be PostgreSQL for applications connecting to this port. --> <postgresql_port>9005</postgresql_port>

* — earlier it was also available via odbc.

Native Integration With PostgreSQL

PostgreSQL storage engine;

postgresql table function; It supports shards and replicas!

SELECT * FROM postgresql(`postgres{1|2|3}:5432`, 'clickhouse', 'test_replicas', 'postgres', 'password');

postgresql dictionary source;

PostgreSQL database engine as a view to all tables in PG database;

Similar to MySQL integration... but for PostgreSQL.

Upcoming: replication from PostgreSQL.

Developer — Ksenia Sumarokova. Available since 21.2.

PostgreSQL-style Cast Operators

The problem:

SELECT CAST(0.1 AS Decimal(30, 10)) 0.1000000000 - Ok. SELECT CAST(0.1 AS Decimal(30, 30)) 0.099999999992113607938064642130 - not Ok. SELECT CAST('0.1' AS Decimal(30, 30)) 0.100000000000000000000000000000 - Ok.

The solution:

SELECT 0.1::Decimal(30, 30) 0.100000000000000000000000000000 - Ok.

Developer — Anton Popov. Available since 21.6.

Native Integration With PostgreSQL

ClickHouse 💖 PostgreSQL

ClickHouse 💖 MySQL equally well


And also ClickHouse is trying very hard to love
s3, HDFS, ODBC, JDBC, Mongo, Redis, Cassandra,
Arrow, GRPC, Kafka, RabbitMQ...

GRPC Protocol

Uncomment <grpc_port>9100</grpc_port> in config.

Feature parity with the native protocol:

— TLS, compression, query progress,
  query cancellation, sessions, external data...

Example: clickhouse-client with GRPC in Python.


Developer — Vitaly Baranov. Available since 21.1.

Extended Range For DateTime64

Was: 1970–2106 year.

Now: 1925–2283 year.

+222 years for free. No performance drawbacks.

Our grandchildren will use ClickHouse.

Developer — Vasily Nemkov, Alexey Milovidov. Available since 21.4.

Parsing HTML in ClickHouse

extractTextFromHTML function

— best effort text extraction from HTML and XHTML.

ClickHouse can parse HTML. It does this at 20 gigabytes per second.

On 40 cores Intel CascadeLake.

OpenTelemetry Support

Trace your ClickHouse queries within your infrastructure:

— enabled by default;

— supports opentelemetry HTTP headers;

— multiple spans are annotated inside ClickHouse;

— data is written to system.opentelemetry_log

Developer — Alexander Kuzmenkov. Available since 20.11.

New Compression Formats For Import/Export

Transparent import/export of compressed files:

— gz, brotli;

— xz, zstd;


CREATE TABLE github_events_url

Developer — Abi Palagashvili. Available since 21.1.

Map Data Type

Example: map Map(String, String)

SELECT map['hello']

Developer — Hexiaoting.

EmbeddedRocksDB Table Engine

— good for key-value queries;

— intended to use as dictionary source;

Developer — Sundy Li.

SQL Enhancements

— aggregate_functions_null_for_empty;
— Extended CTE;
— Type cast for IN subquery; type cast for JOIN;
— POSITION(needle IN haystack);
— DIV / MOD;

Semi-Duplicate Text Search

Text analysis with Min-Hash and Sim-Hash algorithms
to find similar / copy-pasted content.

Min-Hash algorithm:

1. Extract every N-word shingle from the text.

2. Calculate hashes of every shingle.

3. Select M hashes with minimum value and M hashes with maximum value.

4. Calculate two total hashes of min-hashes and max-hashes.

5. Texts are duplicates if at least one of their total hashes equals.

Developer — ucasFL. Available since 21.1.

Statistical Tests

Test that two samples have different means, useful for A/B testing.

— studentTTest;
— welchTTest;
— mannWitneyUTest;

Rank correlation:

— rankCorr.

Upcoming: Cramer's V, Theil's U.

Developer — Nikita Mikhailov et all. Available since 21.1.


Multiple data representations inside a single table.

— different data order;
— subset of columns;
— subset of rows;
— aggregation.

Difference to materialized views:

— projections data is always consistent;
— updated atomically with the table;
— replicated in the same way as the table;
— projection can be automatically used for SELECT query.

Developer — Amos Bird. Available since 21.6.

Hedged Requests

Send distributed query to multiple replicas — to mitigate tail latencies.

This is needed for distributed queries on large clusters (with large "fanout").

* The largest ClickHouse cluster in Yandex is 630+ servers,
but there are many larger clusters in other companies.

Available in 21.4. Developer — Pavel Kruglov and Nikolai Kochetov.

Upcoming Features

ClickHouse Roadmap is publicly available on GitHub:

I will show you only some highlights and examples.

Main Tasks

Provide alternative for ZooKeeper Nested and semistructured data Enable JIT compilation by default Limited support for transactions Backups Separation of storage and compute Short-circuit evaluation Lightweight DELETE/UPDATE Workload management User Defined Functions Simplify replication JOIN improvements Embedded documentation Pluggable auth with tokens YAML configuration Full disk encryption

Support For Semistructured Data

A working prototype:

CREATE TABLE test (id UInt64, data Object('JSON')) ENGINE = MergeTree; INSERT INTO test FORMAT JSONEachRow {"id": 1, "data": {"k1": "aa", "k2": {"k3": "bb", "k4": 123}}} {"id": 2, "data": {"k1": "ee", "k5": "ff"}}; SELECT id, data.k1, data.k2.*, data.k2.k4::String FROM test;

Nested JSON is automatically mapped to dynamic subcolumns
and is stored in efficient columar format.

Bonus: multiple levels of Nested types is already supported.

Developer — Anton Popov.

Enable JIT Compilation By Default

How to process analytical queries not slowly?

There are two techniques:

1. Vectorized query execution.
2. Runtime code generation (JIT).

ClickHouse implements both.

Most of its performance comes from (1) — vectorized processing.

The JIT (2) — was available since version 1.1.54388 in 2018.
But it is disabled by default.

Developers — Denis Skorobogatov, Alexander Sapin, Alexey Milovidov, Maksim Kita.
By default since 21.6.

Enable JIT Compilation By Default

Synthetic example: murmur hash in SQL.

WITH bitXor(number, 5544725790478674055) AS x0, bitXor(x0, bitShiftRight(x0, 33)) AS x1, x1 * 18397679294719823053 AS x2, bitXor(x2, bitShiftRight(x2, 33)) AS x3, x3 * 14181476777654086739 AS x4, bitXor(x4, bitShiftRight(x4, 33)) AS x5 SELECT count() FROM numbers_mt(1000000000) WHERE NOT ignore(x5)

Before: 34.96 GB/s. After: 87.23 GB/s.
2.5 times performance improvement.

Alternative to ZooKeeper

Work in Progress.

— ZooKeeper network protocol is implemented;
— Abstraction layer over ZooKeeper is used;
— ZooKeeper data model is implemented for testing;
— TestKeeperServer: a server with ZooKeeper data model for testing;
— Jepsen tests continuously run in CI with various failure models;

— less operational complexity;
— fix "zxid overflow" issue; — fix the issue with max packet size;
— fix "session expired" due to gc pauses; — improve memory usage;
— allow compressed snapshots; — allow embedding into clickhouse-server.

Developer — Alexander Sapin.

Short-circuit Evaluation

SELECT IF(number = 0, 0, 123 % number) FROM numbers(10)

— division by zero.

SELECT * FROM numbers(10) WHERE number > 0 AND 10 % number > 0

— division by zero.

— both branches of IF, AND, OR are always evaluated.

    SELECT * FROM numbers(10)
    WHERE number > 0
WHERE 10 % number > 0

— division by zero.

User Defined Functions

We are considering five ways to implement UDF, two of them are mandatory:

1. UDF as SQL expressions.


2. UDF as executable script.

Interaction via pipes, data is serialized using supported formats.

Replicated Databases

Before: you have to CREATE every replicated table on every replica.

After: you can CREATE DATABASE ... ENGINE = Replicated(
  '/clickhouse/databases/test1', 'shard', 'replicaN')

and every table operation is automatically replicated on all replicas.

Available since 21.3 as experimental feature. Developer — Alexander Tokmakov.

Distributed Processing Of External Datasets

SELECT SearchPhrase, count() FROM s3Cluster('cluster_name', 'http://minio1:9001/root/data/{2010..2021}/*.csv', 'user', 'password', 'CSV', 'SearchPhrase String, ...')

The query will fetch and process data from s3
in parallel and distributed fashion using compute power
and network on all servers in a cluster.

Upcoming: MergeTree tables on external storage
(available as experimental feature).

Upcoming: processing shared MergeTree tables
with dynamically allocated compute nodes.

Available since 21.5. Developer — Nikita Mikhailov.

YAML configuration

I heard you don't like XML...

Then ClickHouse will support YAML as an alternative for you.

logger: level: trace log: /var/log/clickhouse-server/clickhouse-server.log errorlog: /var/log/clickhouse-server/clickhouse-server.err.log size: 1000M count: 10 http_port: 8123 # Don't worry, YAML configuration tcp_port: 9000 # will also contain comments. mysql_port: 9004 # postgresql_port: 9005 # It's not JSON after all. max_connections: 4096

Developer — Denis Bolonin.


Read the official roadmap and ask your questions: