ClickHouse December 2022 Release – v22.12

Can Sayn ChistaDATA Performance, ClickHouse, Release Notes

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 15, 2022, ClickHouse version 22.12 was released, and this version contains the following;

  • 17 new features,
  • Eight performance optimizations,
  • 39 bug fixes.

For further details, please see the official ClickHouse docs here.

This article will look at the critical features of the ClickHouse 22.12 release.

Key features & improvements

1. SQL Language Features

Numeric Literals With Underscore

With this release, you can run the numeric literals with an underscore. For example;

SELECT 1_000_000;
SELECT 12.345_67;
SELECT 0xABCD_EF01;
SELECT 0b1111_0000_1010_0101;
SELECT 1_50_000, 1_00_00_000; -- 1.5 lakh, 1 crore
78a321632b12 :) SELECT 1_000_000;
                

SELECT 1000000

Query id: f698c528-3675-438d-b3b8-93e9a6013cdd

┌─1000000─┐
│ 1000000 │
└─────────┘

78a321632b12 :) SELECT 12.345_67;
                

SELECT 12.34567

Query id: 641237ea-68cf-4e30-96b4-55f87a3730d8

┌─12.34567─┐
│ 12.34567 │
└──────────┘


78a321632b12 :) SELECT 0xABCD_EF01;
                

SELECT 2882400001

Query id: b729081b-1960-4c86-9636-5590e383a5e1

┌─2882400001─┐
│ 2882400001 │
└────────────┘


78a321632b12 :) SELECT 0b1111_0000_1010_0101;
                

SELECT 61605

Query id: f8ba9235-c335-4508-9327-16778944f3f6

┌─61605─┐
│ 61605 │
└───────┘


78a321632b12 :) SELECT 1_50_000, 1_00_00_000;

SELECT
    150000,
    10000000

Query id: 667ff7b3-b304-411c-a5b8-5ec63ae4ea7d

┌─150000─┬─10000000─┐
│ 150000 │ 10000000 │
└────────┴──────────┘

FROM table SELECT columns

Add FROM table SELECT column the syntax is added into the new release. For example;

FROM github_events
SELECT created_at,
       'https://github.com/' || repo_name AS url,
       title
WHERE title LIKE '%🎄%'
ORDER BY created_at DESC
LIMIT 1 BY title
LIMIT 100

Output;

GROUP BY ALL

New users of ClickHouse, coming from OLTP databases such as Postgres, quickly find ClickHouse differs from ANSI SQL in a few ways. This is often deliberate as we feel these differences make analytical queries simpler and more succinct to write. In a few cases, however, we just have a few functional gaps which we’re eager to close. One of these is the ability to use the ALL clause in a GROUP BY. This simple feature means the user doesn’t need to repeat the columns from their SELECT clause, which aren’t aggregate functions, making queries even shorter and faster to write. Since we love speed, you can now utilize this feature in 22.12

Added GROUP BY ALL syntax:

SELECT
    county, town, district, street,
    median(price), count()
FROM uk_price_paid
WHERE toYear(date) = 2022
GROUP BY ALL
ORDER BY count() DESC
LIMIT 10

GROUP BY ALL (alternative)

SELECT
    county, town, district, street,
    median(price), count()
FROM uk_price_paid
WHERE toYear(date) = 2022
GROUP BY 1, 2, 3, 4
ORDER BY count() DESC
LIMIT 10

or

SELECT
    (county, town, district, street) AS k,
    median(price), count()
FROM uk_price_paid
WHERE toYear(date) = 2022
GROUP BY k
ORDER BY count() DESC
LIMIT 10

concatWithSeparator, concat_ws

Added function concatWithSeparator and concat_ws as an alias for Spark SQL compatibility. A function concatWithSeparatorAssumeInjective added as a variant to enable GROUP BY optimization, similar to concatAssumeInjective.

SELECT concatWithSeparator(' - ', 'Hello', 'world', 'goodbye.') AS x

┌─x────────────────────────┐
│ Hello - world - goodbye. │
└──────────────────────────┘

Decimal Operations With Specified Precision

Added multiplyDecimal and divideDecimal functions for decimal operations with fixed precision

SELECT
  1::Decimal(10, 5) AS a,
  3::Decimal(10, 5) AS b,
  a / b AS x,
  divideDecimal(a, b, 10) AS y

┌─a─┬─b─┬───────x─┬────────────y─┐
│ 1 │ 3 │ 0.33333 │ 0.3333333333 │
└───┴───┴─────────┴──────────────┘

SQL UDF in CREATE Queries

CREATE FUNCTION toBFloat16 AS (x) ->
  reinterpretAsFloat32(
    bitAnd(reinterpretAsUInt32(x), 0xFFFF0000));

CREATE TABLE feature_store
(
  time DateTime DEFAULT now(),
  vec Array(Float32) CODEC(ZSTD) TTL time + INTERVAL 1 DAY,
  vec16 Array(Float32)
    DEFAULT arrayMap(x -> toBFloat16(x), vec) CODEC(ZSTD),
  text String
) ENGINE = MergeTree ORDER BY time;

 

:) INSERT INTO feature_store (vec) VALUES ([0.1, 0.2, 0.3])

Ok.

:) SELECT vec, vec16 FROM feature_store

┌─vec───────────┬─vec16───────────────────────────────┐
│ [0.1,0.2,0.3] │ [0.099609375,0.19921875,0.29882812] │
└───────────────┴─────────────────────────────────────┘

:) SELECT L2Distance(vec, vec16) FROM feature_store

┌─L2Distance(vec, vec16)─┐
│           0.0014615965 │
└────────────────────────┘

2. Operations & Monitoring

system.moves table

Added system.moves table with a list of currently moving parts. This table simply show you what data parts are currently being moved between disk and volumes.

DESCRIBE TABLE system.moves

┌─name─────────────┬─type────┐
│ database         │ String  │
│ table            │ String  │
│ elapsed          │ Float64 │
│ target_disk_name │ String  │
│ target_disk_path │ String  │
│ part_name        │ String  │
│ part_size        │ UInt64  │
│ thread_id        │ UInt64  │
└──────────────────┴─────────┘

Prometheus Endpoint For ClickHouse Keeper

ClickHouse have considered ClickHouse Keeper to be production ready for some time and would encourage all of our users to migrate from Zookeeper where possible. For some users, however, the ability to monitor ClickHouse Keeper in their deployments using the same approach as their legacy Zookeeper instances represented a blocker to migration. As well as improving write performance at high request rates with this release, we have therefore also added a Prometheus endpoint to ClickHouse Keeper to allow monitoring of this critical piece of software in your ClickHouse cluster. Hopefully, this unblocks some migrations and more users can benefit from more stable cluster coordination under load.

Add support for embedded Prometheus endpoint for ClickHouse Keeper.

$ cat /etc/clickhouse-keeper/config.d/prometheus.yaml

prometheus:
    port: 9369
    endpoint: /metrics

Constraints For MergeTree Settings

Added support to define constraints for merge tree settings. For example, you can forbid overriding the storage_policy by users.

CREATE TABLE ... ENGINE = MergeTree
  ORDER BY ...
  SETTINGS storage_policy = 'local', ...

 

cat /etc/clickhouse-server/users.d/table_constraints.yaml

profiles:
    default:
        constraints:
            merge_tree_storage_policy:
                const:
            merge_tree_parts_to_throw_insert:
                max: 1000

3. Security

Password Complexity Rules

ClickHouse considers security to be a first-class citizen. Prior to 22.12, users could create passwords with no enforcement of complexity. While we trust our users to be responsible, mistakes and oversights happen, and weak passwords could be created. We needed to close this for our own needs with ClickHouse Cloud, but also something our community needed.

Password enforcement can be set by adding a password_complexity config key to your server configuration. An example of 4 rules enforcing a strong standard:

Allow configuring password complexity rules and checks for creating and changing users.

$ cat /etc/clickhouse-server/config.d/rules.yaml

password_complexity:
    - rule:
        pattern: '.{12}'
        message: 'be at least 12 characters long'
    - rule:
        pattern: '\p{N}'
        message: contain at least 1 numeric character
    - rule:
        pattern: '\p{Lu}'
        message: contain at least 1 uppercase character
    - rule:
        pattern: '[^\p{L}\p{N}]'
        message: contain at least 1 special character

:) CREATE USER vasyan
   IDENTIFIED WITH sha256_password BY 'qwerty123'

DB::Exception: Invalid password. The password should:
be at least 12 characters long,
contain at least 1 uppercase character,
contain at least 1 special character.

This version has made valuable updates for SQL functions, monitoring, and security. Future releases will allow us to use ClickHouse more efficiently.

These are the ClickHouse 22.12 features. To find out more details, please visit the official ClickHouse Docs.

Conclusion

ClickHouse version 22.12, released on December 15, 2022, brings significant SQL improvements, advanced monitoring features, and enhanced security measures. From numeric literals to password complexity rules, these updates enhance data management and analysis for users, promising more efficient use in the future.

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

About Can Sayn 41 Articles
Can Sayın is experienced Database Administrator in open source relational and NoSql databases, working in complicated infrastructures. Over 5 years industry experience, he gain managing database systems. He is working at ChistaDATA Inc. His areas of interest are generally on open source systems.
Contact: Website

256 Chapman Road STE 105-4, Newark, New Castle, Delaware, 19702, United States | ©2024 ChistaDATA Inc. All rights reserved. | ClickHouse is a trademark of ClickHouse Corporation. All other trademarks are property of their respective owners. Other product or company names mentioned may be trademarks or trade names of their respective owner.