ClickHouse: Release 23.11 Webinar

ClickHouse Release 23.11

Release 23.11 Webinar

1. (50 min) What's new in ClickHouse 23.11.

2. (10 min) Q&A.

Release 23.11

ClickHouse November Release.

— 25 new features 🍁

— 24 performance optimizations 🧤

— 70 bug fixes 🦔

Small And Nice Features

ADD and MATERIALIZE INDEX

... in the same ALTER query

ALTER TABLE test ADD INDEX ix (col) TYPE minmax, MATERIALIZE INDEX ix

Was: DB::Exception: Unknown index: ix.

Now: It works!

Developer: Flynn.

concat with arbitrary types

:) SELECT concat('Hello ', [1, 2]) ┌─concat('Hello, ', [1, 2])─┐ │ Hello, [1,2] │ └───────────────────────────┘

:) SELECT concat('Hello ', [1, 2], 3) ┌─concat('Hello ', [1, 2], 3)─┐ │ Hello [1,2]3 │ └─────────────────────────────┘

Developer: Serge Klochkov.

concat with a single argument

:) SELECT concat('Hello') ┌─concat('Hello')─┐ │ Hello │ └─────────────────┘

Why do we need it?

Developer: Serge Klochkov.

_size virtual column

In external tables: s3, url, file, hdfs, azureBlobStorage...

In addition to the existing virtual columns _file and _path.

SELECT _file, _size, count(), uniq(UserID) FROM s3( 's3://clickhouse-public-datasets/hits_compatible/athena_partitioned/hits*.parquet') GROUP BY _file, _size ORDER BY _file

Demo

Developer: Pavel Kruglov.

fileCluster

A new table function and table engine, similar to
s3Cluster, urlCluster, hdfsCluster, azureBlobStorageCluster.

SELECT uniq(UserID) FROM fileCluster('partitioned/hits*.parquet')

Processes the set of files, spreading the work across the servers in a cluster.

Requires the usage of network filesystem,
mounted inside the user_files path,
so all the files are visible on every server of a cluster.

Developer: Andrey Zvonov.

preferred_optimize_projection_name

SET preferred_optimize_projection_name = 'summaries';

— prefer using the specified projection if it exists
  instead of automatic selection.

Acts like a hint.

Developer: Yarik Briukhovetskyi.

system.blob_storage_log

CREATE TABLE system.blob_storage_log ( event_date Date, event_time DateTime, event_time_microseconds DateTime64(6), event_type Enum8( 'Upload' = 1, 'Delete' = 2, 'MultiPartUploadCreate' = 3, 'MultiPartUploadWrite' = 4, 'MultiPartUploadComplete' = 5, 'MultiPartUploadAbort' = 6), query_id String, thread_id UInt64, thread_name String, disk_name LowCardinality(String), bucket String, remote_path String, local_path String, data_size UInt64, error String )

Developer: Vladimir Cherkasov.

Keeper Improvements

A four-letter command to yield leadership:

echo 'rqld' | nc localhost 2181

Allows to initiate a leader election and wait for a new leader
before removing the node.

Developer: Pradeep Chhetri.

Keeper Improvements

Compression in the network protocol.

Controlled on the client side:

zookeeper: node: host: example1 port: 2181 use_compression: true

Developer: Smita Kulkarni.

Keeper Improvements

Information about Availability Zone.

keeper_server: availability_zone: # option 1, explicit value: value: eu-central-1b # option 2, from the metadata service: enable_auto_detection_on_cloud: true

The info is published in /keeper/availability-zone

Developer: Jianfei Hu.

Performance Improvements

Index On system.numbers

:) SELECT * FROM system.numbers_mt WHERE number BETWEEN 3 AND 5 OR number IN (1000000000, 456)

Demo

Developer: JackyWoo.

Parallel Window Functions

Demo

Developer: Dmitriy Novik.

Asynchronous Loading Of Tables

How long does the server take to start up?

... if you have 10 000 replicated tables?

But having 10 000 tables is an anti-pattern...

Asynchronous Loading Of Tables

Enable with async_load_databases in the config.xml.

ClickHouse will start instantly.

It accepts connections and start loading the tables at the same time.

If a query requires a table, its initialization will be put in front,
and the query will wait for it.

Pros: it's amazing! Cons: caveats exist.

Developer: Sergei Trifonov.

Concurrency Control

Since version 23.11, the concurrency control is enabled by default.

It automatically adjusts the number of threads per query.

concurrent_threads_soft_limit_ratio_to_cores: 2

Result: high QPS without performance degradation.

Developer: Roman Vasin.

Aggressive Retries Of Requests To S3

Slashing tail latencies.

Developer: Sema Checherinda.

Optimization For Reading From Cache

If the data resides in the cache on local disks, read it synchronously
and don't hesitate doing more seeks.

Developer: Nikita Taranov.

Something Interesting

Column Statistics

SET allow_experimental_statistic = 1; CREATE TABLE example ( a Int64 STATISTIC(TDigest), b String ) ENGINE = MergeTree ORDER BY b; ALTER TABLE example ADD STATISTIC a TYPE TDigest; ALTER TABLE example DROP|CLEAR STATISTIC a; ALTER TABLE example MATERIALIZE STATISTIC a;

Developer: Han Fei.

Column Statistics

SET allow_statistic_optimize = 1; SELECT ... WHERE a = 123 AND ...

Selectivity of conditions is automatically determined.

Then it is used for selecting the best order
in multi-stage filtering (PREWHERE) optimization,
in addition to the information of column sizes.

Developer: Han Fei.

Column Statistics

Similar to data-skipping indices
but accumulated in a single small file for each data part.

What's next?
— JOIN reordering;
— implicit LowCardinality.

Developer: Han Fei.

S3Queue Is Production Ready

A table engine for streaming consumption from S3:

CREATE TABLE queue ENGINE = S3Queue('https://clickhouse-public-datasets.s3.amazonaws.com/ hits_compatible/athena_partitioned/hits_*.parquet') SETTINGS mode = 'unordered'; CREATE TABLE data ENGINE = MergeTree ORDER BY () EMPTY AS SELECT * FROM queue; CREATE MATERIALIZED VIEW consumer TO data AS SELECT * FROM queue;

— Consumes data from a bunch of files on S3.
— Consumes new files as soon as they appear.
— Supports parallel and distributed consumption.

Developer: Sergei Katkovskiy, Kseniia Sumarokova.

S3Queue Example

Spot Instance Data Feed in AWS

aws ec2 create-spot-datafeed-subscription --bucket clickhouse-spot-instance-data-feed { "SpotDatafeedSubscription": { "Bucket": "clickhouse-spot-instance-data-feed", "OwnerId": "111111111111", "Prefix": "", "State": "Active" } }

S3Queue Example

Preview the data:

SELECT * FROM s3('https://clickhouse-spot-instance-data-feed.s3.amazonaws.com/*.gz', LineAsString) LIMIT 10 Query id: 78026e69-1cc2-4c82-8be1-c114b1a15a09 ┌─line────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ #Version: 1.0 │ │ #Fields: Timestamp UsageType Operation InstanceID MyBidID MyMaxPrice MarketPrice Charge Version │ │ 2023-09-01 03:31:20 UTC SpotUsage:c5a.8xlarge RunInstances:SV001 i-00b91fe65059b4880 sir-s2eemc1k 1.2320000000 USD 1.0130000000 USD 0.0368620570 USD 1 │ │ 2023-09-01 03:27:01 UTC SpotUsage:c5a.8xlarge RunInstances:SV001 i-02fd64d9c11effa34 sir-4ydpp3kj 1.2320000000 USD 1.0130000000 USD 0.0371436710 USD 1 │ │ 2023-09-01 03:11:06 UTC SpotUsage:c5a.8xlarge RunInstances:SV001 i-0a4c1c90ac125800e sir-haxppnmk 1.2320000000 USD 1.0130000000 USD 0.0371436710 USD 1 │ │ 2023-09-01 03:07:10 UTC SpotUsage:c5d.9xlarge RunInstances:SV006 i-04a2d1d907f42badc sir-haxpmkzg 1.7280000000 USD 0.6275000000 USD 0.2680818050 USD 1 │ │ 2023-09-01 03:15:17 UTC SpotUsage:c5d.9xlarge RunInstances:SV006 i-05c084023a8e866f6 sir-t54ppm5k 1.7280000000 USD 0.6275000000 USD 0.1809289575 USD 1 │ │ 2023-09-01 03:45:26 UTC SpotUsage:c5d.9xlarge RunInstances:SV006 i-08269560fb6a7444c sir-p6qyq3cg 1.7280000000 USD 0.6275000000 USD 0.6275000000 USD 1 │ │ 2023-09-01 03:41:11 UTC SpotUsage:c5d.9xlarge RunInstances:SV006 i-09d71d667650f5c9a sir-m9spnjsj 1.7280000000 USD 0.6275000000 USD 0.0261460425 USD 1 │ │ 2023-09-01 03:04:51 UTC SpotUsage:c5d.9xlarge RunInstances:SV006 i-0a94a38b2190e6717 sir-31feqd4g 1.7280000000 USD 0.6275000000 USD 0.0803551400 USD 1 │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

S3Queue Example

Process it as is:

WITH parseDateTimeBestEffort(c1) AS time, extractGroups(c2, '^(\w+):(.+)$') AS item_type, item_type[1] AS item, item_type[2] AS type, c4 AS id, CAST(extract(c6, '^\S+') AS Decimal(10, 6)) AS max_price, CAST(extract(c7, '^\S+') AS Decimal(10, 6)) AS market_price, CAST(extract(c8, '^\S+') AS Decimal(10, 6)) AS charge, charge / market_price AS hours SELECT type, count() AS c, sum(hours) AS h, sum(charge) AS cost FROM s3('https://clickhouse-spot-instance-data-feed.s3.amazonaws.com/*.gz', TSV) GROUP BY type ORDER BY h DESC SETTINGS input_format_tsv_skip_first_lines = 2, schema_inference_make_columns_nullable = 0 ┌─type─────────────┬──────c─┬────────────h─┬─────────cost─┐ │ c6id.8xlarge │ 875 │ 379.432492 │ 345.468028 │ │ m7g.4xlarge │ 912 │ 362.599616 │ 120.957285 │ │ m6idn.2xlarge │ 712 │ 331.768934 │ 87.381505 │ │ m5d.2xlarge │ 693 │ 262.129137 │ 65.735759 │ │ r5b.2xlarge │ 483 │ 232.865149 │ 59.732247 │ │ r6g.2xlarge │ 647 │ 195.560188 │ 42.264367 │ │ c6a.4xlarge │ 343 │ 172.48896 │ 46.570093 │ │ r6id.4xlarge │ 247 │ 152.03035 │ 77.222521 │ │ m6i.8xlarge │ 338 │ 151.313531 │ 105.488635 │ │ m5zn.2xlarge │ 313 │ 146.439172 │ 37.66433 │ │ m7a.8xlarge │ 279 │ 128.709207 │ 131.700535 │ │ m5dn.2xlarge │ 206 │ 100.624052 │ 25.753932 │ │ r6gd.2xlarge │ 364 │ 84.635927 │ 23.849737 │ │ r6id.2xlarge │ 126 │ 78.014559 │ 19.587254 │ │ r5ad.2xlarge │ 143 │ 69.243293 │ 17.757915 │ │ m5ad.8xlarge │ 122 │ 67.560744 │ 69.316617 │ │ r5d.4xlarge │ 172 │ 58.593963 │ 29.710109 │ │ r5dn.2xlarge │ 112 │ 48.394754 │ 12.393548 │ │ x2gd.2xlarge │ 125 │ 45.796468 │ 14.833519 │ │ c6in.8xlarge │ 59 │ 23.07633 │ 23.274454 │ │ r6g.4xlarge │ 31 │ 16.225792 │ 5.742319 │ │ r5d.2xlarge │ 47 │ 16.11658 │ 4.121735 │ │ m5d.8xlarge │ 55 │ 14.517439 │ 14.38119 │ │ r6in.2xlarge │ 19 │ 13.441091 │ 3.444952 │ │ r5ad.4xlarge │ 40 │ 12.415226 │ 6.759386 │ │ c5n.9xlarge │ 28 │ 9.814967 │ 10.964834 │ │ m5n.8xlarge │ 27 │ 8.510248 │ 8.424644 │ │ m7i.8xlarge │ 33 │ 8.375798 │ 7.812783 │ │ m6in.8xlarge │ 14 │ 6.189432 │ 6.338852 │ │ m5a.8xlarge │ 19 │ 5.311926 │ 3.826214 │ │ c5ad.4xlarge │ 8 │ 5.29304 │ 1.427534 │ │ m6gd.4xlarge │ 39 │ 4.673265 │ 1.631628 │ │ r6idn.2xlarge │ 10 │ 3.247486 │ 0.831033 │ │ m5dn.8xlarge │ 7 │ 2.976105 │ 3.062444 │ │ m7i-flex.8xlarge │ 6 │ 1.698884 │ 1.173879 │ │ m6id.8xlarge │ 2 │ 0.27611 │ 0.28293 │ │ r7g.4xlarge │ 1 │ 0.254999 │ 0.134818 │ │ m6in.2xlarge │ 1 │ 0.148331 │ 0.037958 │ └──────────────────┴────────┴──────────────┴──────────────┘

S3Queue Example

Subscribe to a queue:

CREATE TABLE queue ENGINE = S3Queue('https://clickhouse-spot-instance-data-feed.s3.amazonaws.com/*.gz', TSV) SETTINGS mode = 'unordered', s3queue_enable_logging_to_s3queue_log = 1, input_format_tsv_skip_first_lines = 2, schema_inference_make_columns_nullable = 0; CREATE TABLE feed ENGINE = MergeTree ORDER BY time EMPTY AS WITH parseDateTimeBestEffort(c1) AS time, extractGroups(c2, '^(\w+):(.+)$') AS item_type, item_type[1] AS item, item_type[2] AS type, c4 AS id, extract(c6, '^\S+')::Decimal(10, 6) AS max_price, extract(c7, '^\S+')::Decimal(10, 6) AS market_price, extract(c8, '^\S+')::Decimal(10, 6) AS charge, charge / market_price AS hours SELECT time, item, type, id, max_price, market_price, charge, hours FROM queue; CREATE MATERIALIZED VIEW consumer TO feed AS WITH parseDateTimeBestEffort(c1) AS time, extractGroups(c2, '^(\w+):(.+)$') AS item_type, item_type[1] AS item, item_type[2] AS type, c4 AS id, extract(c6, '^\S+')::Decimal(10, 6) AS max_price, extract(c7, '^\S+')::Decimal(10, 6) AS market_price, extract(c8, '^\S+')::Decimal(10, 6) AS charge, charge / market_price AS hours SELECT time, item, type, id, max_price, market_price, charge, hours FROM queue;

S3Queue Example

Monitor the queue:

:) SELECT sum(rows_processed) FROM system.s3queue_log ┌─sum(rows_processed)─┐ │ 1849211 │ └─────────────────────┘ :) DESCRIBE system.s3queue_log ┌─name──────────────────┬─type─────────────────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐ │ event_date │ Date │ │ │ │ │ │ │ event_time │ DateTime │ │ │ │ │ │ │ table_uuid │ String │ │ │ │ │ │ │ file_name │ String │ │ │ │ │ │ │ rows_processed │ UInt64 │ │ │ │ │ │ │ status │ Enum8('Processed' = 0, 'Failed' = 1) │ │ │ │ │ │ │ processing_start_time │ Nullable(DateTime) │ │ │ │ │ │ │ processing_end_time │ Nullable(DateTime) │ │ │ │ │ │ │ ProfileEvents │ Map(String, UInt64) │ │ │ │ │ │ │ exception │ String │ │ │ │ │ │ └───────────────────────┴──────────────────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

S3Queue Example

The data is continuously streamed into the table:

:) SELECT * FROM feed LIMIT 25 ┌────────────────time─┬─item──────┬─type──────────┬─id──────────────────┬─max_price─┬─market_price─┬───charge─┬────hours─┐ │ 2023-09-01 03:00:16 │ SpotUsage │ r6in.4xlarge │ i-03eaee0ffa54380c1 │ 1.394 │ 0.5143 │ 0.47187 │ 0.917499 │ │ 2023-09-01 03:01:57 │ SpotUsage │ m7a.2xlarge │ i-0c63ae42ec8e55a2e │ 0.463 │ 0.2565 │ 0.2565 │ 1 │ │ 2023-09-01 03:02:22 │ SpotUsage │ c5d.9xlarge │ i-0e299eb8d2d6b6fc5 │ 1.728 │ 0.6275 │ 0.157921 │ 0.251666 │ │ 2023-09-01 03:03:03 │ SpotUsage │ i4g.2xlarge │ i-0d66a79dc81c7ae43 │ 0.617 │ 0.2563 │ 0.020361 │ 0.079442 │ │ 2023-09-01 03:03:10 │ SpotUsage │ m6a.4xlarge │ i-0d04c29fff9fa827f │ 0.691 │ 0.496 │ 0.374893 │ 0.755832 │ │ 2023-09-01 03:03:16 │ SpotUsage │ r5dn.4xlarge │ i-08d6d2e38b6cc1d8f │ 1.336 │ 0.5 │ 0.207916 │ 0.415832 │ │ 2023-09-01 03:04:11 │ SpotUsage │ m6a.4xlarge │ i-08015b849286b0d88 │ 0.691 │ 0.496 │ 0.347475 │ 0.700554 │ │ 2023-09-01 03:04:12 │ SpotUsage │ m6a.4xlarge │ i-031d72c1f42ef8d3d │ 0.691 │ 0.509 │ 0.509 │ 1 │ │ 2023-09-01 03:04:12 │ SpotUsage │ r5dn.4xlarge │ i-0dd7c5f1e4ae54981 │ 1.336 │ 0.5017 │ 0.014911 │ 0.02972 │ │ 2023-09-01 03:04:13 │ SpotUsage │ m7a.4xlarge │ i-0da7bb5b5cad27dfb │ 0.927 │ 0.5134 │ 0.5134 │ 1 │ │ 2023-09-01 03:04:13 │ SpotUsage │ r6in.4xlarge │ i-09677fc629e7bb177 │ 1.394 │ 0.5143 │ 0.5143 │ 1 │ │ 2023-09-01 03:04:14 │ SpotUsage │ r6in.4xlarge │ i-0be80b35d91b112fc │ 1.394 │ 0.5143 │ 0.126289 │ 0.245555 │ │ 2023-09-01 03:04:51 │ SpotUsage │ c5d.9xlarge │ i-0a94a38b2190e6717 │ 1.728 │ 0.6275 │ 0.080355 │ 0.128055 │ │ 2023-09-01 03:05:13 │ SpotUsage │ m6a.4xlarge │ i-04c756e5bc4550fdc │ 0.691 │ 0.509 │ 0.509 │ 1 │ │ 2023-09-01 03:05:14 │ SpotUsage │ m7a.4xlarge │ i-09a01aba465ddaf89 │ 0.927 │ 0.5134 │ 0.5134 │ 1 │ │ 2023-09-01 03:05:14 │ SpotUsage │ r5n.4xlarge │ i-0f895dcb4e96a20d4 │ 1.192 │ 0.5096 │ 0.5096 │ 1 │ │ 2023-09-01 03:05:22 │ SpotUsage │ r5n.4xlarge │ i-057e425617cdc86b2 │ 1.192 │ 0.4871 │ 0.017725 │ 0.036388 │ │ 2023-09-01 03:05:32 │ SpotUsage │ r5dn.4xlarge │ i-02c2a1a327e967b5d │ 1.336 │ 0.5017 │ 0.041111 │ 0.081943 │ │ 2023-09-01 03:05:52 │ SpotUsage │ m6a.4xlarge │ i-0f05728d26088e256 │ 0.691 │ 0.509 │ 0.022056 │ 0.043332 │ │ 2023-09-01 03:06:10 │ SpotUsage │ r5n.4xlarge │ i-0fa61797331b03cde │ 1.192 │ 0.4871 │ 0.213512 │ 0.438332 │ │ 2023-09-01 03:06:18 │ SpotUsage │ m6idn.4xlarge │ i-0a9883f45dddac9dc │ 1.272 │ 0.5118 │ 0.457918 │ 0.89472 │ │ 2023-09-01 03:07:10 │ SpotUsage │ c5d.9xlarge │ i-04a2d1d907f42badc │ 1.728 │ 0.6275 │ 0.268081 │ 0.42722 │ │ 2023-09-01 03:07:15 │ SpotUsage │ r5dn.4xlarge │ i-0fcd109ec0a5d72c9 │ 1.336 │ 0.4926 │ 0.026271 │ 0.053331 │ │ 2023-09-01 03:08:00 │ SpotUsage │ m5ad.2xlarge │ i-0d03ba891a7be45ef │ 0.412 │ 0.2576 │ 0.010232 │ 0.03972 │ │ 2023-09-01 03:08:14 │ SpotUsage │ r5n.4xlarge │ i-07cf673d6efc02bb8 │ 1.192 │ 0.4871 │ 0.017995 │ 0.036943 │ └─────────────────────┴───────────┴───────────────┴─────────────────────┴───────────┴──────────────┴──────────┴──────────┘

Bonus

Lower The Binary Size

Single binary installation:

curl https://clickhouse.com/ | sh

Was: over 500 MB :(

Now: 107 MB :)

Integrations

dbt:
— compatibility with v1.5 and v1.6;
— added a macro for Materialized View;

Tableau:
— Tableau Online works with ClickHouse!
  using the MySQL compatibility protocol;

Kafka Connector:
— a new StringConverter to support text-based formats (CSV, TSV);
— added support for Bytes and Decimal data types;
— recognize retriable error codes

Updates for Python, Node.js, and Go clients.

Reading Corner

https://clickhouse.com/blog/

— RAG pipelines with ClickHouse;
— Unbundling the Cloud Data Warehouse;
— Security data platform in ClickHouse;
— Enhancing Google Analytics;

Video Recordings: https://www.youtube.com/c/ClickHouseDB

Q&A