Outstandingly Fresh ClickHouse Features

ClickHouse Meetup Online East

Zoom (On Air):

Telegram chat: clickhouse_en

Wechat: 4 groups (ask your friend to invite)

YouTube: https://www.youtube.com/c/ClickHouseDB

Outstandingly Fresh ClickHouse Features

In Previous Episodes: Spring/Summer 2020

PostgreSQL wire protocol Geographical dictionaries Data coarsening with TTL expressions Parallel calculation of FINAL Insertion into ODBC and JDBC Cassandra dictionary source Dictionary layout for SSD Settings for data formats in dictionaries Removal of leader election for replicas Support for Distributed over Distributed queries Query metrics from PMU GROUP BY optimization on sorted data Apache Arrow import/export MsgPack format "direct" dictionary source

New Data Formats For MergeTree

— Compact data parts (since version 20.3).

— In-memory data parts (since version 20.6).

— Write-Ahead Log (since version 20.6).

— Durability settings (since version 20.10).

Developer — Anton Popov.

Three Formats For Data Parts

1. Wide — the classic format.

2. Compact — all columns in a single file.

3. Memory — all data in memory.

Three Formats For Data Parts

Managed by the following settings:

min_bytes_for_wide_part,
min_rows_for_wide_part:

  if the size is bigger — then use wide format.

min_bytes_for_compact_part,
min_rows_for_compact_part:

  if the size is bigger — then use compact format,
  if less — use memory format.

Wide > Compact > Memory

Wide Data Parts

The classic format.

Every column and index in its own files.

Optimal for reading from disk, especially from slow HDDs.

Zero-cost ALTER ADD/REMOVE COLUMN.

Compact Data Parts

All columns in a single file.

Optimal for data insertion.
Especially on slow filesystems.

Less optimal for reading.

It's recommended for small data parts.

Not recommended to enable for all data parts.

Available since version 20.3, but disabled by default.
Since version 20.10 enabled by default for data parts up to 10 MB.

In-Memory Data Parts

Data in RAM.

+ optional Write-Ahead Log.

— in_memory_parts_enable_wal;
— write_ahead_log_max_bytes.

Even better performance on insertion...
... if you did not enable write-ahead log.

Even better performance for reading...
... but data in memory is stored uncompressed.

All data parts are replicated as usual.

This is an experimental feature.

Durability

What if all your data is stored on a single server?

What if you don't use replication?

What if you do use replication but within a single region?

— so, we need to simply call fsync before answering to INSERT query?

Durability

Eat My Data (2007):
http://mirror.linux.org.au/pub/linux.conf.au/2007/video/talks/278.pdf

Files Are Hard (2015):
https://danluu.com/file-consistency/

PostgreSQL "Fsyncgate" (2018):
https://lwn.net/Articles/752063/

Can Applications Recover from fsync Failures? (2020):
https://www.usenix.org/conference/atc20/presentation/rebello

Durability

What if all your data is stored on a single server?

What if you don't use replication?

What if you do use replication but within a single region?

Then take this:

— min_rows_to_fsync_after_merge;
— min_compressed_bytes_to_fsync_after_merge;
— min_compressed_bytes_to_fsync_after_fetch;
— fsync_after_insert;
— fsync_part_directory;
— write_ahead_log_bytes_to_fsync;
— write_ahead_log_interval_ms_to_fsync;
— in_memory_parts_insert_sync.

Query Normalization and Obfuscation

The functions: normalizeQuery, normalizedQueryHash.

SELECT normalizeQuery(query) FROM system.query_log

— replaces literals to ?

— replaces lists of literals to ?..

— replaces complex aliases to `?`

Available since version 20.8.

Query Obfuscation

One my friend has a query...
... but he don't want to show it to me.

Solution:

clickhouse-format --obfuscate < query.sql

Available since version 20.10.

Data Obfuscation: https://presentations.clickhouse.tech/highload_spb_2019/#

 

Data Recompression

CREATE TABLE hits ( event_time DateTime, ... ) ENGINE MergeTree ORDER BY ... TTL event_time + INTERVAL 1 MONTH RECOMPRESS CODEC(ZSTD(1)), event_time + INTERVAL 1 YEAR RECOMPRESS CODEC(ZSTD(6))

Developer — Alexander Sapin. Available since version 20.10.

Additions to ALTER

CREATE TABLE hits ( event_time DateTime CODEC(Delta, Default), ... ) ENGINE MergeTree ORDER BY ... ALTER TABLE hits MODIFY COLUMN c REMOVE COMMENT|CODEC|TTL |DEFAULT|MATERIALIZED|ALIAS

Developer — Alexander Sapin. Available since version 20.10.

Column List Transformers

SELECT * EXCEPT(secret_column) FROM table; SELECT table.* REPLACE( (URL LIKE '%yandex%' ? '' : URL) AS URL) FROM table; SELECT COLUMNS('^packet_') FROM table; SELECT t.* APPLY(sum) FROM table AS t; SELECT COLUMNS(x, y, z) APPLY(sum) FROM table;

Developer — Amos Bird, mfridental. Available since version 20.10.

COLUMNS('regexp'): developer — mfridental. Available since version 19.12.

New ClickHouse versions

20.11 — testing.

20.10 — stable.

20.9 — stable.

20.8 — LTS until 2021-09-30.

20.7 ... 20.4 — obsolete.

20.3 — LTS until 2021-03-12.

...

19.14 — obsolete.

What else?

Streaming data import from RabbitMQ.

Kerberos authentication for Kafka.

WITH for subqueries with global scope (Amos Bird).

New formats: Regexp, RawBLOB, JSONAsString, LinesAsString.

Allow to run clickhouse without packages and configs.

system.crash_log table, Sentry integration.

Aggregate function for rank correlation statistic.

256bit Decimal.

What's next?

Backups.

GRPC interface.

Projections (Amos Bird).

Support for OpenTracing.

An alternative to ZooKeeper.

Hedged distributed requests.

Reading of subcolumns from tables.

Semi-duplicate text matching (ucasFL).

Functions for geographical data analysis.

.