Altinity Stable for ClickHouse 22.3.8
A few months ago we certified 21.8 as an Altinity Stable release. It was delivered together with the Altinity Stable build for ClickHouse. Since then many things have happened to ClickHouse. In Altinity we continued to work on newer releases and run them in-house. We completed several new features, and many more have been added by community contributors. We started testing the new ClickHouse LTS release 22.3 as soon as it was out in late March. It took us more than three months to confirm 22.3 is ready for production use and to make sure upgrades go smoothly. As of 22.3.8 we are confident in certifying 22.3 as an Altinity Stable release.
This release is a significant upgrade since the previous Altinity Stable release. It includes more than 3000 pull requests from 415 contributors. Please look below for detailed release notes.
Major new features in 22.3 since the previous stable release 21.8
A new release introduces a lot of changes and new functions. It is very hard to pick the most essential ones, so refer to the full list in the Appendix. The following major features are worth mentioning on the front page:
- SQL features:
- User defined functions as lambda expressions.
- User defined functions as external executables.
- Schema inference for
SELECTfrom external data sources.
- -Map combinator for Map data type.
WINDOW VIEWfor stream processing (experimental). See our blog article “Battle of Views” comparing it to
- Asynchronous inserts.
- Support expressions in
OPTIMIZE DEDUPLICATEon a subset of columns a).
COMMENTon schema objects a).
- Security features:
- Predefined named connections (or named collections) for external data sources. Can be used in table functions, dictionaries, table engines.
system.session_logtable that tracks connections and login attempts a).
- Disk-level encryption. See the meetup presentation for many interesting details.
- Support server-side encryption keys for S3 a).
- Support authentication of users connected via SSL by their X.509 certificate.
- Replication and Cluster improvements:
- ClickHouse Keeper – in-process ZooKeeper replacement – has been graduated to production-ready by the ClickHouse team. We also keep testing it on our side: the core functionality looks good and stable, some operational issues and edge cases still exist.
- Automatic replica discovery – no need to alter remote_servers anymore.
- Parallel reading from multiple replicas (experimental).
- Dictionary features:
- Array attributes, Nullable attributes.
hashed_arraydictionary layout that reduces RAM usage for big dictionaries (idea proposed by Altinity).
- Custom queries for dictionary source.
- SQLite table engine, table function, database engine.
- Executable function, storage engine and dictionary source.
- FileLog table engine.
- Huawei OBS storage support.
- Aliyun OSS storage support.
- Remote file system and object storage features:
- Zero-copy replication for HDFS.
- Partitioned writes into S3 a) , File, URL and HDFS storages.
- Local data cache for remote filesystems.
- Store user access management data in ZooKeeper.
- Production ready ARM support.
- Significant rework of clickhouse-local that is now as advanced as clickhouse-client .
- Projections and window functions are graduated and not experimental anymore.
As usual with ClickHouse, there are many performance and operational improvements in different server components.
a) - contributed by Altinity developers.
Backward Incompatible Changes
The following changes are backward incompatible and require user attention during an upgrade:
- Do not output trailing zeros in text representation of Decimal types. Example:
1.23will be printed instead of
1.230000for decimal with scale 6. Serialization in output formats can be controlled with the setting
- Now, scalar subquery always returns a Nullable result if its type can be Nullable.
- Introduce syntax for here documents. Example:
SELECT $doc$ VALUE $doc$. This change is backward incompatible if there are identifiers that contain $.
- Now indices can handle Nullable types, including
isNotNullfunctions. This required index file format change – idx2 file extension. ClickHouse 21.8 can not read those files, so a correct downgrade may not be possible.
- MergeTree table-level settings
replicated_max_parallel_fetches_for_tablewere replaced with
- Change the order of
json_pathand json arguments in SQL/JSON functions to be consistent with the standard.
- A “leader election” mechanism is removed from
ReplicatedMergeTree, because multiple leaders have been supported since 20.6. If you are upgrading from ClickHouse version older than 20.6, and some replica with an old version is a leader, then the server will fail to start after upgrade. Stop replicas with the old version to make the new version start. Downgrading to versions older than 20.6 is not possible.
- Change implementation specific behavior on overflow of the function
toDateTime. It will be saturated to the nearest min/max supported instant of
datetimeinstead of wraparound. This change is highlighted as “backward incompatible” because someone may unintentionally rely on the old behavior.
There were several changes between versions that may affect the rolling upgrade of big clusters. Upgrading only part of the cluster is not recommended.
- Data after merge is not byte-identical for tables with
- Data after merge is not byte-identical for tables created with the old syntax -
count.txtis added in 22.1
Rolling upgrade from 20.4 and older is impossible because the “leader election” mechanism is removed from ReplicatedMergeTree.
Known Issues in 22.3.x
The development team continues to improve the quality of the 22.3 release. The following issues still exist in the 22.3.8 version and may affect ClickHouse operation. Please inspect them carefully to decide if those are applicable to your applications.
- Some queries that worked in 21.8 may fail in 22.3:
- In some cases when using
WITHstatement aliases are not properly resolved: https://github.com/ClickHouse/ClickHouse/issues/37812
- In rare cases error ‘
not found column X in the block’ may occur: https://github.com/ClickHouse/ClickHouse/issues/36043
- Sometimes distributed queries with aggregation and alias may return ‘
Not found column’ errors: https://github.com/ClickHouse/ClickHouse/issues/37032
- In some cases when using
- Some queries may return incorrect results:
ORDER BYmay give unsorted results when there are duplicate rows in table and projection is used: https://github.com/ClickHouse/ClickHouse/issues/37673
- We have found a few performance regressions:
optimize_aggregation_in_ordermay result in memory overcommit in queries with filters: https://github.com/ClickHouse/ClickHouse/issues/38036
max_insert_threadsis not respected for materialized views: https://github.com/ClickHouse/ClickHouse/issues/37900 – this is already fixed, backport is in progress
- Primary key is not used when integer column is used with float
If you started using 22.3 from the earlier versions, please note that following important bugs have been fixed, especially related to
- Cannot convert column xxx because it is non constant in source stream but must be constant in result: https://github.com/ClickHouse/ClickHouse/issues/36300 - fixed in 22.3.8
- Argument at index 1 for function ilike must be constant: while executing. (
ILLEGAL_COLUMN): https://github.com/ClickHouse/ClickHouse/issues/36279 - fixed in 22.3.8
- unexpected errors with a clash of constant strings in aggregate function. and
PREWHEREand join: https://github.com/ClickHouse/ClickHouse/issues/36891 - fixed in 22.3.7
- When building a query using -If combinator and a subquery as a column, the trivial
WHEREclause may return incorrect results: https://github.com/ClickHouse/ClickHouse/issues/35336 - fixed in 22.3.8
- CTE with
arrayJoin(arrayEnumerate())may not work in
PREWHERE: https://github.com/ClickHouse/ClickHouse/issues/37190 – fixed in 22.3.8
You may also look into a GitHub issues using v22.3-affected label.
Other Important Changes
ClickHouse now recreates system tables if the Engine definition is different from the one in the config. (see https://github.com/ClickHouse/ClickHouse/pull/31824 + edge case https://github.com/ClickHouse/ClickHouse/issues/34929).
Behavior of some metrics has been changed. For example written_rows / result_rows may be reported differently, see https://gist.github.com/filimonov/c83fdf988398c062f6fe5b3344c35e80
BackgroundPoolTaskwas split into
background_merges_mutations_concurrency_ratio=2– that means ClickHouse can schedule two times more merges/mutations than
background_pool_size, which is still 16 by default. For some scenarios with stale replicas the behavior may be harder to predict / explain. If needed, you can return the old behavior by setting
Pool sizes should be now configured in config.xml (Fallback reading that from default profile of users.xml is still exists) .
In queries like
SELECTa, b, …
GROUP BY(a, b, …) ClickHouse does not untuple the
GROUP BYexpression anymore. The same is true for
When dropping and renaming schema objects ClickHouse now checks dependencies and throws an Exception if the operation may break the dependency:
Code: 630. DB::Exception: Cannot drop or rename X because some tables depend on it: Y
It may be disabled by setting:
ClickHouse embedded monitoring is since 21.8. It now collects host level metrics, and stores them every second in the table
system.asynchronious_metric_log. This can be visible as an increase of background writes, storage usage, etc. To return to the old rate of metrics refresh / flush, adjust those settings in
<asynchronous_metrics_update_period_s> 60 </asynchronous_metrics_update_period_s>
<asynchronous_metric_log> <flush_interval_milliseconds> 60000 </flush_interval_milliseconds> </asynchronous_metric_log>
Alternatively, metric_log and asynchronous_metric_log tables can be completely disabled:
<yandex> <asynchronous_metric_log remove="1"/> <metric_log remove="1"/> </yandex>
Some new ClickHouse features are now enabled by default. It may lead to a change in behavior, so review those carefully and disable features that may affect your system:
In the previous releases we recommended disabling
optimize_on_insert. This recommendation stays for 22.3 as well as inserts into
AggregatingMergeTree can slow down.
Changes Compared to Community Build
ClickHouse Altinity Stable builds are based on the community LTS versions. Altinity.Stable 126.96.36.199 is based on community 188.8.131.52-lts, but we have additionally backported several features we were working on for our clients:
- Fixes to GCS support for disk S3: https://github.com/ClickHouse/ClickHouse/pull/37882
- Performance optimization for window functions – it now respects table
ORDER BY: https://github.com/ClickHouse/ClickHouse/pull/34632
- Bugfix in exponential time decay functions: https://github.com/ClickHouse/ClickHouse/pull/36944
ClickHouse Altinity Stable releases are based on the community versions.
Note: The naming schema for Altinity.Stable build packages has been changed since 21.8.x.
Docker images for community versions have been moved from ‘yandex’ to ‘clickhouse’ organization, and should be referenced as ‘clickhouse/clickhouse-server:22.3’. Altinity stable build images are available as ‘altinity/clickhouse-server:22.3’.
Mac users are welcome to use Homebrew Formulae. Ready-to-use bottles are available for both M1 and Intel Macs running Monterey.
For more information on installing ClickHouse from either the Altinity Builds or the Community Builds, see the ClickHouse Altinity Stable Release Build Install Guide.
Please contact us at email@example.com if you experience any issues with the upgrade.
New table functions
New table engines
New aggregate function combinators
renames (geometrics functions)
- Basic / SQL compatibility
- left + leftUTF8
- right + rightUTF8
- NLP / text split
- text classification
- bitwise string processing
singleValueOrNull(if all values are equal it return the value, Null otherwise)
Map datatype related
aggregation & statistical functions
nothing(Aggregate function that takes arbitrary number of arbitrary arguments and does nothing. :) )
orNull casting variants
tuple to key-value
New system tables
New columns in system tables
New metrics and events
Also, please refer to the release notes from the development team available at the following URLs:
- 21.9 release notes: https://clickhouse.com/docs/en/whats-new/changelog/2021#clickhouse-release-v219-2021-09-09
- 21.10 release notes: https://clickhouse.com/docs/en/whats-new/changelog/2021#clickhouse-release-v2110-2021-10-16
- 21.11 release notes: https://clickhouse.com/docs/en/whats-new/changelog/2021#clickhouse-release-v2111-2021-11-09
- 21.12 release notes: https://clickhouse.com/docs/en/whats-new/changelog/2021#clickhouse-release-v2112-2021-12-15
- 22.1 release notes: https://clickhouse.com/docs/en/whats-new/changelog/#-clickhouse-release-v221-2022-01-18
- 22.2 release notes: https://clickhouse.com/docs/en/whats-new/changelog/#-clickhouse-release-v222-2022-02-17
- 22.3 release notes: https://clickhouse.com/docs/en/whats-new/changelog/#-clickhouse-release-v223-lts-2022-03-17