ClickHouse Winter 2024 Release – v24.2

Introduction

Each new release includes new features, improvements, and numerous bug fixes, and the ChistaDATA team is always on top of the latest releases. On 29th February 2024, ClickHouse version 24.2 (February 2024) was released, and this version contains 18 new features, 18 performance optimizations, and 49 bug fixes.

For further details, please see the official ClickHouse change log list here.

Let’s look at some important new features.

Key features & improvements

(1) Encapsulation Of Access Control In Views

Normally, if you want to access a view as a normal user, you need to have SELECT privileges on the view and the entire table. But now it’s enough to have SELECT privileges on the view only. It’s also possible to allow the user to insert into the table even if they don’t have explicit permission to insert into some of the MVs.

Also, views and materialised views are defined before use and, when referenced, are executed within a security context that determines their privileges. The privileges applicable to the execution of a stored object are controlled by its DEFINER attribute and SQL SECURITY characteristic.

Carmela has access to product and creates product_v:

CREATE VIEW product_v
DEFINER = Carmela SQL SECURITY DEFINER
AS SELECT c1, c2 FROM product WHERE user = 'Tony';

She gives access to product_v to Tony:

GRANT SELECT ON default.product_v TO Tony;

Tony does not have access to product but can use product_v, so product_v will SELECT from product on behalf of Carmela.

(2) Adaptive Asynchronous Inserts

In 24.2, you can enable the SET async_insert = true allow combining data for insert from many concurrent clients and with this version, it automatically adjusts to the frequency of inserts. Prior to this version, they will wait until the timeout (200ms by default); infrequent inserts have a higher latency.

(3) Added system.dns_cache table

Implemented the system.dns_cache table, which can be useful for debugging DNS issues. All information in the table is updated every 5 seconds. It contains information about cached DNS records.

SELECT *
FROM system.dns_cache
WHERE hostname LIKE '%local%'

┌─hostname──┬─ip_address─┬─ip_family─┬───────────cached_at─┐
│ localhost │ ::1        │ IPv6      │ 2024-03-03 18:01:33 │
│ localhost │ 127.0.0.1  │ IPv4      │ 2024-03-03 18:01:33 │
└───────────┴────────────┴───────────┴─────────────────────┘

(4) New Compression Level For LZ4

LZ4(2) has been redesigned. Faster than LZ4(3) and now the same as LZ4(1). A file is 107029405 byte on the Linux filesystem and here are the high compression results.

(5) Pretty Format Is Even Prettier

If the output format is Pretty format and a block consists of a single numerical value exceeding one million, a readable number will be printed on the right side of the table:

:) SELECT count() FROM table

┌────count()─┐
│ 2106592082 │ -- 2.11 billion
└────────────┘

(6) Negative Positional Arguments

In SQL, ORDER BY 1 means that the results are ordered by the first column in the query and now ClickHouse provides the ORDER BY -2 or GROUP BY -3 to count in reverse.

SELECT *
FROM system.dns_cache
ORDER BY -2 ASC

┌─hostname──┬─ip_address─┬─ip_family─┬───────────cached_at─┐
│ localhost │ 127.0.0.1  │ IPv4      │ 2024-03-03 22:05:36 │
│ localhost │ ::1        │ IPv6      │ 2024-03-03 22:05:36 │
└───────────┴────────────┴───────────┴─────────────────────┘

With this argument, we have sorted according to ip_family column.

(7) groupArrayIntersect

Added GroupArrayIntersect(array_column) function feature, which aggregates column of array type into array consting of unique elements present in each array of column.

Consider table `total`:

┌─t──────────────┐
│ [1,2,4]        │
│ [1,5,2,8,-1,0] │
│ [1,5,7,5,8,2]  │
└────────────────┘
Query with column name as argument:

SELECT groupArrayIntersect(t) as intersection FROM total;
Result:

┌─intersection──────┐
│ [1, 2]            │
└───────────────────┘

Conclusion

ClickHouse 24.2 brings many exciting new features that enhance its high-performance data analysis database management system capabilities. ClickHouse continues to evolve and provide users with powerful tools for processing, analyzing, and visualizing data. The performance enhancements and expanded integrations further solidify ClickHouse’s position as the first choice for high-speed data processing, real-time analytics, and advanced data analytics use cases. With its open-source nature and continuous innovation, ClickHouse remains a leading solution for organizations seeking fast, scalable, and feature-rich data analytics capabilities. Upgrade to ClickHouse 24.2 with ChistaDATA support to take advantage of these exciting new features and unlock the full potential of your data analysis workflows.

Learn about the improvements in the previous release (v24.1) in our release notes.

References

https://dev.mysql.com/doc/refman/8.0/en/stored-objects-security.html

About Ilkay 24 Articles
Ilkay has been administering databases including NoSQL and RDBMS for over 7 years. He is experienced working high-scale banking databases. He is currently working at ChistaDATA Inc. as Database Administrator.
Contact: Website