Introduction
Every new release includes new features, enhancements, and numerous bug fixes, and the ChistaDATA team always stays on top of the latest releases. On December 29, 2023, ClickHouse version 23.12 was released, and this version contains the following;
- 21 new features,
- 18 performance optimizations,
- 37 bug fixes.
For further details, please see the official ClickHouse docs here.
- v23.12 Source Code : GitHub Link
- v23.12 Release Webinar : Slides
- Installation: ClickHouse Docs
This article will look at the critical features of the ClickHouse 23.12 release.
Key features & improvements
1. ORDER BY ALL
The ORDER BY clause has been enhanced to include the option of specifying ALL. This indicates that ClickHouse will arrange the results based on all columns listed in the SELECT clause. For instance: SELECT col1, col2 FROM tab WHERE […] ORDER BY ALL.
SELECT * FROM github_events ORDER BY ALL SELECT * FROM github_events ORDER BY ALL DESC SELECT * FROM github_events ORDER BY ALL DESC COLLATE 'tr'
2. PASTE JOIN
Introducing PASTE JOIN, a feature that enables users to join tables without an ON clause, simply using row numbers. For example: SELECT * FROM (SELECT number AS a FROM numbers(2)) AS t1 PASTE JOIN (SELECT number AS a FROM numbers(2) ORDER BY a DESC) AS t2.
SELECT * FROM
(
SELECT
toYear(date),
medianExact(price) AS m,
bar(m, 0, max(m) OVER (), 20) AS b
FROM uk_price_paid
GROUP BY ALL
ORDER BY ALL ASC
) AS t
PASTE JOIN
(
SELECT *
FROM numbers(100)
) AS u
3. SQID
Introduced a new SQL function, sqid, to generate Sqids (https://sqids.org/). For example: SELECT sqid(125, 126).
:) SELECT sqid(123); UKk :) SELECT sqid(123456789); rDOOFW :) SELECT sqid(123, 456, 789); eVH6til6J
4. APPLY DELETED MASK
Implemented a new mutation command: ALTER TABLE <table> APPLY DELETED MASK. This command enables the enforcement of applying a mask generated by lightweight delete, effectively removing rows marked as deleted from the disk.
Batch DELETE: ALTER TABLE t DELETE WHERE user = 'ChistaDATA' — a full rewrite of data parts by copying all the records except the deleted ones. Lightweight DELETE: DELETE FROM t WHERE user = 'ChistaDATA' — a lazy deletion by creating a mask of records to filter out on SELECT and during merges. ALTER TABLE t APPLY DELETED MASK — cleans the deleted records after a lightweight delete operation, without the need for OPTIMIZE query.
ALTER TABLE t APPLY DELETED MASK — cleans the deleted records after a lightweight delete operation, without the need for OPTIMIZE query. Essentially transforms lightweight DELETEs to a batch operation.
5. _part_offset Column
The primary key analysis in MergeTree tables has been extended to include predicates that involve the virtual column _part_offset, optionally with _part. This enhancement allows for leveraging this feature as a unique type of secondary index.
SELECT count(), uniq(_part) FROM github_events WHERE _part_offset < 100 ┌─count()─┬─uniq(_part)─┐ │ 1700 │ 17 │ └─────────┴─────────────┘
Conclusion
The ClickHouse 23.12 release introduces significant enhancements, including new SQL functions like ORDER BY ALL and SQID, as well as features such as PASTE JOIN and APPLY DELETED MASK for improved query flexibility and data management. Additionally, performance optimizations and bug fixes enhance overall system efficiency and responsiveness, making ClickHouse more adept at handling concurrent activities.
These are the ClickHouse 23.12 features. To find out more details, please visit the official ClickHouse Docs.
To know more about the ClickHouse releases, do visit the following articles:
