SELECT transform(
what,
[from1, from2, ...],
[to1, to2, ...],
default);
SELECT CASE what
WHEN from1 THEN to1
WHEN from2 THEN to2
ELSE default END;
23.5: implemented only for numbers, strings, and date-times; 23.6: implemented for everything;
Note: from and to values are required to be constants even in 23.6.
Developer: Alexey Milovidov.
SELECT ... INTO OUTFILE '...' TRUNCATE
Version 23.5 introduced the APPEND modifier.
Version 23.6 adds the TRUNCATE modifier.
:) SELECT * FROM numbers(10)
INTO OUTFILE 'numbers.tsv'
Ok.
:) SELECT * FROM numbers(10)
INTO OUTFILE 'numbers.tsv'
Cannot open file numbers.tsv, errno: 17,
strerror: File exists, consider using APPEND or TRUNCATE. -- this is fine
:) SELECT * FROM numbers(10)
INTO OUTFILE 'numbers.tsv' TRUNCATEOk.
Developer: Manas Alekar.
Skip Empty Files On Data Import
New settings: s3_skip_empty_files, hdfs_skip_empty_files,
engine_file_skip_empty_files, engine_url_skip_empty_files.
CREATE TABLE test_mongo ... ENGINE = MongoDB(
'mongo1:27017', 'test', 'simple_table', 'testuser', 'clickhouse');
SELECT * FROM test_mongo;
SELECT * FROM mongodb(
'mongo1:27017', 'test', 'simple_table', 'testuser', 'clickhouse',
'x String, y UInt64, ...');
Was: perfectly working until MongoDB 5.1.
But: they changed the protocol :(
Now: everything works up to MongoDB 6.
Developer: Nikolai Degterinskiy.
Performance Optimizations
"Too many parts" Is Relaxed
How many is too many?
— parts_to_delay_insert, parts_to_throw_insert;
How to perform backpressure?
— should it slow-down long running inserts?
"Too many parts" Is Relaxed
Example: inserting 400 billion records with 4 threads on a single machine, 7 million records/second, with concurrent SELECT queries for dashboards.
Decision: enable backpressure during insert and increase the default value of max parts to 3000.
Background Threads In ReplicatedMergeTree
Before 23.6:
— aggressive scheduling of background threads
(cleanup and merge selecting) was leading to
a high CPU usage and excessive requests to Keeper.
Release 23.6:
— introduce backoff for tables without inserts.
Result:
— moderate CPU usage on a server with 5000 ReplicatedMergeTree tables;
three times less the number of requests to Keeper and network traffic!
Developer: Alexander Tokmakov.
Sorting Almost Sorted Data
If the data is not assumed to be sorted,
but appears to be sorted in certain ranges — detect that and use.
Demo
Developer: Maksim Kita, Alexey Milovidov.
Reading Parquet From Local Files
clickhouse-local over partitioned Parquet files improved 2 times 🔥
clickhouse-local over a single Parquet file improved 4 times ❤️🔥
Demo.
Developer: Michael Kolupaev. Already included in the release 23.5.
Something Interesting
Redis Tables
ClickHouse + Redis = ❤
CREATE TABLE test_redis (...)
ENGINE = Redis('host:6379', 0, 'password')
PRIMARY KEY k;
SELECT * FROM test_redis;
SELECT * FROM test_redis WHERE k = 'whatever';
SELECT * FROM test_redis WHERE k IN ('a', 'b');
SELECT * FROM redis(
'host:6379', k, 'k String, v String', 0, 'password');
Developer: Jacky Woo
Redis Tables
ClickHouse + Redis = ❤
Supports SELECT and INSERT,
SELECT by a key, set of keys, or full scan,
JOIN with Redis tables,
ALTER UPDATE and DELETE!
What next? Support for hash data type in Redis?
Developer: Jacky Woo
A Secret Feature
It has a chance to ship in the release 23.6.
... but it is not guaranteed.
Overlay Databases
with Filesystem, S3, and HDFS database engines!
Integrations
ClickHouse is supported by Confluent Cloud:
The integration can be installed as a custom connector.
Updates for Metabase; Node.js, Python, and Go drivers.
