ClickHouse Release Notes – v23.5 (May 2023)

Table of Contents

Introduction

We greatly appreciate the ClickHouse team’s consistent efforts in delivering monthly releases packed with new features, improvements, and valuable bug fixes. At ChistaData, we diligently conduct Quick Peek analyses on each release and publish dedicated blog posts to keep our readers informed. Today, we are presenting our coverage of the June 2023 release of ClickHouse (version 23.5). Below, you will find the necessary links related to this release.

Source Code – GitHub Link
Installation Instructions – Official Documentation
v23.5 Release Webinar – Recording
v23.5 Release Webinar – Slides

Before diving into the details of the features, let’s explore how you can recreate the environment using the latest version of ClickHouse Keeper/Zookeeper. To do this, simply download the repository containing the docker-compose file we have created, allowing you to dockerize the entire setup. You can find step-by-step instructions in the accompanying readme document.

Here are the links to the repositories for two different setups:

Three Node ClickHouse cluster with ClickHouse Keeper: https://github.com/ashwini-ahire7/
Replicated ClickHouse cluster with Zookeeper: https://github.com/ashwini-ahire7/

Overview

The release happened on June 8th, 2023. It has the following aspects.

29 new features
25 performance optimization
83 bug fixes

Let’s look at the main features and improvements one by one.

ClickHouse Keeper Client is Available to introspect your Keeper or Zookeeper

The command line client for ClickHouse Keeper is readily available and comes bundled as part of the ClickHouse installation. It offers a convenient interface to interact with ClickHouse Keeper’s functionality.

ClickHouse Keeper is vital in providing a coordination system for data replication and distributed DDL (Data Definition Language) query execution. It ensures smooth operation and synchronization within the ClickHouse cluster. Notably, ClickHouse Keeper is fully compatible with ZooKeeper, offering an improved alternative to ZooKeeper itself. It has been thoroughly tested and deemed production-ready for reliable and scalable deployments.

clickhouse01:/etc/clickhouse-server/config.d# clickhouse keeper-client
/ :) ruok
imok
/ :) srvr
ClickHouse Keeper version: v23.5.2.7-stable-5751aa1ab9f2364dbed942a78c3083215fdbf11a
Latency min/avg/max: 0/0/15
Received: 3768
Sent: 3807
Connections: 2
Outstanding: 0
Zxid: 1993
Mode: leader
Node count: 349

/ :)
/ :) stat
ClickHouse Keeper version: v23.5.2.7-stable-5751aa1ab9f2364dbed942a78c3083215fdbf11a
Clients:
 127.0.0.1:59560(recved=0,sent=0)
 127.0.0.1:46794(recved=35,sent=35)
 127.0.0.1:35734(recved=3839,sent=3878)

Latency min/avg/max: 0/0/15
Received: 3874
Sent: 3913
Connections: 2
Outstanding: 0
Zxid: 2130
Mode: leader
Node count: 349

/ :) envi
Environment:
clickhouse.keeper.version=v23.5.2.7-stable-5751aa1ab9f2364dbed942a78c3083215fdbf11a
host.name=clickhouse01
os.name=Linux
os.arch=x86_64
os.version=5.19.0-1025-aws
cpu.count=1
user.name=
user.home=/var/lib/clickhouse/
user.dir=/
user.tmp=/tmp/

ATTACH… From Temporary Tables

Starting from version 23.4 of ClickHouse, an exciting new feature allows creating of temporary tables using any table type, including merge tree or any other desired type. This flexibility eliminates the restriction of using only memory-based tables for temporary data storage.

In version 23.5, ClickHouse introduces the handy ATTACH function, which further enhances the management of temporary tables. This function enables us to seamlessly transition data between different tables. For example, when ready, we can conveniently prepare and stage the data in a temporary table and then effortlessly move the partition to the main table. These enhancements give users more flexibility and control over their data processing workflows, allowing for efficient data preparation and seamless integration of temporary and permanent tables within ClickHouse.

clickhouse01 :) CREATE TEMPORARY TABLE USER_STATS (customer_id UInt64, num_page_views UInt32 , num_purchases UInt32 , total_amount Decimal(10, 2)) ENGINE = MergeTree ORDER BY customer_id


CREATE TEMPORARY TABLE USER_STATS
(
    `customer_id` UInt64,
    `num_page_views` UInt32,
    `num_purchases` UInt32,
    `total_amount` Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY customer_id

Query id: 06409993-42d6-467c-a3cf-7195f28596b6

Ok.

0 rows in set. Elapsed: 0.009 sec.


clickhouse01 :) INSERT INTO USER_STATS SELECT
    pv.customer_id,
    COUNTDistinct(pv.id) AS num_page_views,
    COUNTDistinct(pe.id) AS num_purchases,
    SUM(pe.total_amount) AS total_amount_spent
FROM PageViews AS pv
LEFT JOIN PurchaseEvents AS pe ON pv.customer_id = pe.customer_id
GROUP BY pv.customer_id ;

INSERT INTO USER_STATS SELECT
    pv.customer_id,
    COUNTDistinct(pv.id) AS num_page_views,
    COUNTDistinct(pe.id) AS num_purchases,
    SUM(pe.total_amount) AS total_amount_spent
FROM PageViews AS pv
LEFT JOIN PurchaseEvents AS pe ON pv.customer_id = pe.customer_id
GROUP BY pv.customer_id

Query id: 401024e0-406a-4123-bbd2-38e620772778

Ok.

0 rows in set. Elapsed: 0.005 sec.

clickhouse01 :) SELECT count()
FROM USER_STATS

SELECT count()
FROM USER_STATS

Query id: 7c7d92ed-66a3-41cc-a82d-c7df8b6c1f08

┌─count()─┐
│     100 │
└─────────┘

1 row in set. Elapsed: 0.003 sec.

clickhouse01 :) CREATE TABLE USER_STATS_FINAL
(
    `customer_id` UInt64,
    `num_page_views` UInt32,
    `num_purchases` UInt32,
    `total_amount` Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY customer_id

CREATE TABLE USER_STATS_FINAL
(
    `customer_id` UInt64,
    `num_page_views` UInt32,
    `num_purchases` UInt32,
    `total_amount` Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY customer_id

Query id: f3de07ba-0e53-4e73-a400-f96cf9147fff

Ok.

0 rows in set. Elapsed: 0.008 sec.

clickhouse01 :)
clickhouse01 :)
clickhouse01 :) SELECT count()
FROM USER_STATS_FINAL;

SELECT count()
FROM USER_STATS_FINAL

Query id: 34b672bf-7f76-465e-949c-42a7a25993e6

┌─count()─┐
│       0 │
└─────────┘

1 row in set. Elapsed: 0.002 sec.

clickhouse01 :)

clickhouse01 :) ALTER TABLE USER_STATS_FINAL ATTACH PARTITION () FROM USER_STATS;


ALTER TABLE USER_STATS_FINAL
    ATTACH PARTITION tuple() FROM USER_STATS

Query id: ca708394-b3ff-472a-9185-3bc00df294ca

Ok.

0 rows in set. Elapsed: 0.002 sec.

clickhouse01 :) SELECT count()
FROM USER_STATS_FINAL

SELECT count()
FROM USER_STATS_FINAL

Query id: 894afb81-3706-4714-99b9-d7a0046123f9

┌─count()─┐
│     100 │
└─────────┘

1 row in set. Elapsed: 0.002 sec.


clickhouse01 :) select * from ecommerce_analytics.USER_STATS_FINAL limit 5;

SELECT *
FROM ecommerce_analytics.USER_STATS_FINAL
LIMIT 5

Query id: 48048daa-f92b-4850-9fc9-e35d325666d2

┌─────────customer_id─┬─num_page_views─┬─num_purchases─┬─total_amount─┐
│  241955477959865655 │              1 │             1 │           34 │ 
│  368093478975439187 │             10 │             2 │            0 │
│  543290884554963336 │              1 │             1 │            0 │
│  977885807659313775 │             86 │            10 │         7654 │
│ 1237924028540082397 │              1 │             1 │            0 │
└─────────────────────┴────────────────┴───────────────┴──────────────┘

5 rows in set. Elapsed: 0.002 sec.


Schema for tables used in joins ---

CREATE TABLE PageViews (
                    id UInt64,
                    timestamp DateTime,
                    page_url String,
                    customer_id UInt64,
                    device_type String
                ) ENGINE = MergeTree()
                ORDER BY timestamp;

CREATE TABLE PurchaseEvents
(
    `id` UInt64,
    `timestamp` DateTime,
    `customer_id` UInt64,
    `product_id` UInt64,
    `quantity` Int32,
    `total_amount` Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY timestamp

SELECT INTO OUTFILE …. APPEND

Starting from version 23.5, ClickHouse introduces a new feature known as the “APPEND” with OUTFILE. This feature simplifies the process of appending data to existing files within ClickHouse.

--- OlDER VERSION WE USE TO GET ERROR ON FILE EXIST

clickhouse01 :)  SELECT * FROM numbers(10) INTO OUTFILE 'numbers.tsv'

SELECT *
FROM numbers(10)
INTO OUTFILE 'numbers.tsv'

Query id: ff791f9e-a744-4282-b9d8-ceebe9e5108e

Ok.
Exception on client:
Code: 76. DB::Exception: Code: 76. DB::ErrnoException: Cannot open file numbers.tsv, errno: 17, strerror: File exists. (CANNOT_OPEN_FILE) (version 23.5.2.7 (official build)). (CANNOT_OPEN_FILE)


-- WITH LATEST VERSION 

clickhouse01 :)  SELECT version()

SELECT version()

Query id: 4ec61e3f-4dbf-42fb-b96f-f2b9e95bdf42

┌─version()─┐
│ 23.5.2.7  │
└───────────┘

1 row in set. Elapsed: 0.001 sec.

clickhouse01 :)  SELECT * FROM numbers(10) INTO OUTFILE 'numbers.tsv' APPEND

SELECT *
FROM numbers(10)
INTO OUTFILE 'numbers.tsv' APPEND

Query id: 6afca41e-34c3-4522-8852-78d9da81398f


10 rows in set. Elapsed: 0.001 sec.

Compatibility of the CASE expression

CASE WHEN feature is available in ClickHouse From a long with a tiny bug, which is fixed in this version.

clickhouse01 :) select version()

SELECT version()

Query id: eddc88ac-fe41-4730-a529-6afa48299778

┌─version()───┐
│ 23.3.1.2823 │
└─────────────┘

1 row in set. Elapsed: 0.001 sec.

clickhouse01 :)
clickhouse01 :) SELECT CASE number WHEN 0 THEN 'Customers'
                                   WHEN 1 THEN 'Products'
                                   WHEN 2 THEN 'Orders'
                                   WHEN 3 THEN 'Reviews'
                                   END
                        AS ecommerce FROM numbers(6)

SELECT caseWithExpression(number, 0, 'Customers', 1, 'Products', 2, 'Orders', 3, 'Reviews', NULL) AS ecommerce
FROM numbers(6)

Query id: a79f08e6-4ced-4bf9-ba4b-67619911e7e4

┌─ecommerce─┐
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
└───────────┘

6 rows in set. Elapsed: 0.002 sec.


clickhouse01_new :) select version()

SELECT version()

Query id: 9aa40116-a0ce-466a-8dcc-eb4f7b5cee06

┌─version()─┐
│ 23.5.2.7  │
└───────────┘

1 row in set. Elapsed: 0.001 sec.

clickhouse01_new :) SELECT CASE number WHEN 0 THEN 'Customers'
                   WHEN 1 THEN 'Products'
                   WHEN 2 THEN 'Orders'
                   WHEN 3 THEN 'Reviews'
                   END
        AS ecommerce FROM numbers(6)

SELECT caseWithExpression(number, 0, 'Customers', 1, 'Products', 2, 'Orders', 3, 'Reviews', NULL) AS ecommerce
FROM numbers(6)

Query id: cad549ba-b7b2-4168-852e-130a1fa93a39

┌─ecommerce─┐
│ Customers │
│ Products  │
│ Orders    │
│ Reviews   │
│ ᴺᵁᴸᴸ      │
│ ᴺᵁᴸᴸ      │
└───────────┘

6 rows in set. Elapsed: 0.001 sec.

Here you will see different ways of writing the same function: release-notes

generateRandom function is entirely self-sufficient

ClickHouse provides flexibility and ease of use when it comes to generating random data, Whether you choose to specify the data structure or rely on automatic schema detection. You can select the method that best suits your requirements and seamlessly generate the desired data for your analytical or testing purposes.

CREATE TABLE PurchaseEvents
(
    `id` UInt64,
    `timestamp` DateTime,
    `customer_id` UInt64,
    `product_id` UInt64,
    `quantity` Int32,
    `total_amount` Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY timestamp


clickhouse01 :) INSERT INTO PurchaseEvents SELECT * FROM generateRandom() LIMIT 100;

INSERT INTO PurchaseEvents SELECT *
FROM generateRandom()
LIMIT 100

Query id: 52cd62cd-5439-476e-aad8-8a5af9635d6d

Ok.

0 rows in set. Elapsed: 0.002 sec.


clickhouse01 :) select * from PurchaseEvents limit 2\G

SELECT *
FROM PurchaseEvents
LIMIT 2

Query id: 82e8f84b-793d-41b7-80ab-fcd4e0ae5ca6

Row 1:
──────
id:           4786003595288786646
timestamp:    1972-03-27 08:44:09
customer_id:  6379148985809302147
product_id:   11948588072837058011
quantity:     1464542522
total_amount: -89169290.39

Row 2:
──────
id:           8287033284712517053
timestamp:    1973-07-18 12:47:20
customer_id:  3106658140213459646
product_id:   9516077320022188831
quantity:     487152642
total_amount: -44207962.79

2 rows in set. Elapsed: 0.002 sec.

clickhouse01 :) select count() from PurchaseEvents;

SELECT count()
FROM PurchaseEvents

Query id: 79f9651f-17bb-4ea6-9b3d-90ed7e5abd54

┌─count()─┐
│     100 │
└─────────┘
clickhouse01 :) SELECT * FROM generateRandom('x UInt8, y DateTime,  z String') LIMIT 10;

SELECT *
FROM generateRandom('x UInt8, y DateTime,  z String')
LIMIT 10

Query id: fe11b4de-8822-425b-b895-f7f937e62513

┌───x─┬───────────────────y─┬─z─────────┐
│  84 │ 2036-02-08 12:24:20 │ %gW       │
│ 248 │ 2057-05-27 04:58:19 │ ?~Y1^csv  │
│  61 │ 2079-08-21 08:20:25 │ [6        │
│ 127 │ 2030-06-28 22:07:38 │ \%2dk     │
│   7 │ 1991-08-31 16:14:09 │ gLt%dfg   │
│  23 │ 2074-01-03 10:12:18 │           │
│  51 │ 2028-06-11 17:15:33 │ o+ m_T=aw │
│ 163 │ 2087-04-14 01:44:43 │ o         │
│  63 │ 2006-08-15 01:08:00 │ I;Rf!R/   │
│  70 │ 2024-05-23 15:41:08 │ Wn<}iJDu  │
└─────┴─────────────────────┴───────────┘

10 rows in set. Elapsed: 0.001 sec.

clickhouse01 :)DESCRIBE (SELECT * FROM generateRandom());

SHOW INDEX is now possible for MySQL Compatibility

clickhouse01 :)  show index from ecommerce_analytics.PageViews\G

SHOW INDEXES FROM PageViews FROM ecommerce_analytics

Query id: cf0ff393-eb80-4178-9513-cbfa9752bfe7

Row 1:
──────
table:         PageViews
non_unique:    0
key_name:      PRIMARY
seq_in_index:  ᴺᵁᴸᴸ
column_name:   ᴺᵁᴸᴸ
collation:     A
cardinality:   ᴺᵁᴸᴸ
sub_part:      ᴺᵁᴸᴸ
packed:        ᴺᵁᴸᴸ
null:          ᴺᵁᴸᴸ
index_type:    primary
comment:       ᴺᵁᴸᴸ
index_comment: ᴺᵁᴸᴸ
visible:       YES
expression:    timestamp

1 row in set. Elapsed: 0.008 sec.

Addition to System Tables

Starting from ClickHouse version 23.5, the system schema has been expanded with the addition of two new tables, bringing the total number of tables in the system schema to 92. These additional tables support troubleshooting capabilities and provide users with valuable insights into their ClickHouse database.

clickhouse01 :) show create table system.user_processes\G

SHOW CREATE TABLE system.user_processes

Query id: 7021f9f6-dc86-4dc4-bdc4-21c105a6dbdd

Row 1:
──────
statement: CREATE TABLE system.user_processes
(
    `user` String,
    `memory_usage` Int64,
    `peak_memory_usage` Int64,
    `ProfileEvents` Map(String, UInt64),
    `ProfileEvents.Names` Array(String),
    `ProfileEvents.Values` Array(UInt64)
)
ENGINE = SystemUserProcesses
COMMENT 'SYSTEM TABLE is built on the fly.'

1 row in set. Elapsed: 0.001 sec.

clickhouse01 :) show create table system.zookeeper_connection\G

SHOW CREATE TABLE system.zookeeper_connection

Query id: a03107ba-9d2e-4653-8b09-ec563c17140e

Row 1:
──────
statement: CREATE TABLE system.zookeeper_connection
(
    `name` String,
    `host` String,
    `port` UInt16,
    `index` UInt8,
    `connected_time` DateTime,
    `is_expired` UInt8,
    `keeper_api_version` UInt8,
    `client_id` Int64
)
ENGINE = SystemZooKeeperConnection
COMMENT 'SYSTEM TABLE is built on the fly.'

1 row in set. Elapsed: 0.001 sec.

clickhouse01 :) select * from system.zookeeper_connection;

SELECT *
FROM system.zookeeper_connection

Query id: ec4d0efe-c5ca-485a-b088-8837ede98275

┌─name────┬─host───────┬─port─┬─index─┬──────connected_time─┬─is_expired─┬─keeper_api_version─┬─────────client_id─┐
│ default │ 172.30.0.7 │ 2181 │     0 │ 1970-01-01 07:38:08 │          0 │                  0 │ 72057610779820034 │
└─────────┴────────────┴──────┴───────┴─────────────────────┴────────────┴────────────────────┴───────────────────┘

1 row in set. Elapsed: 0.002 sec.

urlClusrer Function

ClickHouse introduces another interesting feature called urlCluster function, which builds upon the existing url function. It functions similarly s3Cluster by leveraging the resources from all nodes within the cluster, thereby significantly accelerating data processing. By using the urlCluster function, you can observe noticeable performance improvements compared to the url function.

For instance, in the query below, the url function takes approximately 12.404 seconds, while the urlCluster function completes the same task in just 9.517 seconds. This time difference becomes even more pronounced when dealing with larger datasets.

clickhouse01 :) CREATE TABLE covid19 (
    date Date,
    location_key LowCardinality(String),
    new_confirmed Int32,
    new_deceased Int32,
    new_recovered Int32,
    new_tested Int32,
    cumulative_confirmed Int32,
    cumulative_deceased Int32,
    cumulative_recovered Int32,
    cumulative_tested Int32
)
ENGINE = MergeTree
ORDER BY (location_key, date);

Query id: 5b4be598-737e-4c43-b9d7-82228871c064


clickhouse01 :) INSERT INTO covid19
   SELECT *
   FROM
      url(
        'https://storage.googleapis.com/covid19-open-data/v3/epidemiology.csv',
        CSVWithNames,
        'date Date,
        location_key LowCardinality(String),
        new_confirmed Int32,
        new_deceased Int32,
        new_recovered Int32,
        new_tested Int32,
        cumulative_confirmed Int32,
        cumulative_deceased Int32,
        cumulative_recovered Int32,
        cumulative_tested Int32'
    );

Query id: feb967c0-410d-4572-b336-4e741f72b01a

Ok.

0 rows in set. Elapsed: 12.404 sec. Processed 12.53 million rows, 450.53 MB (1.01 million rows/s., 36.32 MB/s.)

clickhouse01 :) INSERT INTO covid19
   SELECT *
   FROM
      urlCluster('cluster_demo_ash',
        'https://storage.googleapis.com/covid19-open-data/v3/epidemiology.csv',
        CSVWithNames,
        'date Date,
        location_key LowCardinality(String),
        new_confirmed Int32,
        new_deceased Int32,
        new_recovered Int32,
        new_tested Int32,
        cumulative_confirmed Int32,
        cumulative_deceased Int32,
        cumulative_recovered Int32,
        cumulative_tested Int32'
    );

Query id: e7d6ab57-0158-47bd-9fd5-83daf64f15ac

→ Progress: 2.49 million rows, 86.99 MB (1.11 million rows/s., 38.77 MB/s.) █████▎                           16%
Ok.

0 rows in set. Elapsed: 9.517 sec. Processed 12.53 million rows, 439.12 MB (1.32 million rows/s., 46.14 MB/s.)

clickhouse01 :) select cluster,shard_num,replica_num,host_name,host_address,is_active from system.clusters WHERE cluster = 'cluster_demo_ash';

SELECT
    cluster,
    shard_num,
    replica_num,
    host_name,
    host_address,
    is_active
FROM system.clusters
WHERE cluster = 'cluster_demo_ash'

Query id: 8a324fd1-e3cd-4ed3-9449-269d3372b2ed

┌─cluster──────────┬─shard_num─┬─replica_num─┬─host_name────┬─host_address─┬─is_active─┐
│ cluster_demo_ash │         1 │           1 │ clickhouse01 │ 172.30.0.2   │      ᴺᵁᴸᴸ │
│ cluster_demo_ash │         1 │           2 │ clickhouse02 │ 172.30.0.3   │      ᴺᵁᴸᴸ │
│ cluster_demo_ash │         2 │           1 │ clickhouse03 │ 172.30.0.4   │      ᴺᵁᴸᴸ │
│ cluster_demo_ash │         2 │           2 │ clickhouse04 │ 172.30.0.5   │      ᴺᵁᴸᴸ │
└──────────────────┴───────────┴─────────────┴──────────────┴──────────────┴───────────┘

4 rows in set. Elapsed: 0.006 sec.

Some more Production-ready features

After fixing bugs and implementing missing parts, these experimental features are now becoming Production Ready.

-> Query Results Cache

— controllable in size for each element or in total;
— can be enabled for active and passive usage;
— production-ready since 23.5!

-> Geographical Data Types

— Point, Ring, Polygon, and MultiPolygon data types;
— distance, area, perimeter, union, intersection, convex hull, etc. functions;
— production-ready since 23.5!

Example of query cache in ClickHouse.

SELECT
    repo_name,
    toDate(created_at) AS day,
    count() AS stars
FROM github_events
WHERE event_type = 'CommitCommentEvent'
GROUP BY
    repo_name,
    day
ORDER BY count() DESC
LIMIT 50
SETTINGS use_query_cache = 1

50 rows in set. Elapsed: 1.803 sec. Processed 9.96 million rows, 69.70 MB (5.52 million rows/s., 38.66 MB/s.)


clickhouse01 :) SELECT *
FROM system.query_cache

SELECT *
FROM system.query_cache

Query id: a56a49b2-bb2a-4ac3-81e9-00d8b0fce504

┌─query─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─result_size─┬─stale─┬─shared─┬─compressed─┬──────────expires_at─┬─────────────key_hash─┐
│ SELECT repo_name, toDate(created_at) AS day, count() AS stars FROM github_events WHERE event_type = 'CommitCommentEvent' GROUP BY repo_name, day ORDER BY count() DESC LIMIT 50 SETTINGS  │        5152 │     1 │      0 │          1 │ 2023-06-13 05:35:53 │ 14109952190568756893 │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────┴───────┴────────┴────────────┴─────────────────────┴──────────────────────┘

1 row in set. Elapsed: 0.003 sec.

SELECT
    repo_name,
    toDate(created_at) AS day,
    count() AS stars
FROM github_events
WHERE event_type = 'CommitCommentEvent'
GROUP BY
    repo_name,
    day
ORDER BY count() DESC
LIMIT 50
SETTINGS use_query_cache = 1

Query id: 4594b60b-68fa-4522-8fa3-02f356cee41b

50 rows in set. Elapsed: 0.034 sec.

Other interesting improvements

Table ENGINE / Table Function = AzureBlobStorage – To import/export data on the fly in Azure blog storage, it’s the same as AWS s3.
Password Hashing – Security features for default password hashing
Backup and Restore work with encryption at Rest without decryption and re-encryption.
Introduced a new Space function, which is much faster than repeat functions.

Features Enabled by Default

-> Compression for marks and indices on disk

— Available since 22.9
— Improve the speed of queries after server startup.
— Enabled by default in 23.5!

Optimization for memory usage

-> New server settings to control memory usage: Merges and mutations will not start if more than half of server memory is already consumed by background operations.
merges_mutations_memory_usage_soft_limit and
merges_mutations_memory_usage_to_ram_ratio = 0.5

-> New table settings to lower memory usage: To save memory when merging tables with large rows
merge_max_block_size_bytes and
vertical_merge_algorithm_min_bytes_to_activate

Integrations

The official Tableau connector: https://github.com/ClickHouse/clickhouse-tableau-connector-jdbc

ClickHouse 23.5 brings exciting new features for enhancing its high-performance data analytics capabilities. Being open-source and constantly innovating, ClickHouse remains at the forefront and preferred choice of solutions for organizations seeking rapid, scalable, and feature-rich data analytics capabilities. Upgrade to unlock its full potential for your workflows. For further details, please see the official ClickHouse change log list here.

Conclusion

The June 2023 release of ClickHouse (v23.5) introduces significant enhancements, including ClickHouse Keeper improvements, data manipulation upgrades, and system schema expansions. These updates empower users with greater flexibility and insight into real-time analytics workflows.

To know more about ClickHouse releases, do visit the following articles:

References

https://clickhouse.com/docs/en/whats-new/changelog#-clickhouse-release-2210-2022-10-25

https://presentations.clickhouse.com/release_23.5/

ChistaDATA Inc.

Enterprise-class 24*7 ClickHouse Consultative Support and Managed Services