How to check your ClickHouse version

Learn how to check your ClickHouse version using SQL, HTTP, and Prometheus endpoints.

Photo: Propel

How to check your ClickHouse version

Whether you're troubleshooting, upgrading, or simply auditing your systems, knowing your ClickHouse version is crucial. There are significant differences between versions, including breaking changes and noticeable performance differences for certain types of queries. In this post, we'll explore three methods to check the ClickHouse version tailored for developers in SaaS companies operating ClickHouse in production or exploring introducing it in their stack.

If you are looking for a serverless alternative to ClickHouse, where you get the benefits of ClickHouse’s speed without the overhead of managing additional infrastructure, check out Propel.

What is ClickHouse?

ClickHouse is a fast open-source columnar database. It is designed to perform exceptionally well with large-scale analytical queries. It is capable of processing hundreds of millions to more than a billion rows and gigabytes of data per second. Its columnar storage allows running fast analytical queries against large datasets, making it a good choice for use cases that require sub-second query latencies.

However, running production-grade ClickHouse clusters does come with its complexities. It requires teams to design sharding, clustering, partitioning, and backup strategies that need to be fine-tuned to maintain performance as the data and query volumes scale.

How to check the ClickHouse version with SQL

For database administrators and developers, SQL is the go-to language for database interaction. To check your ClickHouse version via SQL, you can use a simple query:

SELECT version()

23.11.2.11

This command returns the version of the ClickHouse server you're currently connected to. It's straightforward, fast, and can be executed from any SQL client connected to your ClickHouse database.

You can also use the <span class="code-exp">system.build_options</span> table, which gives you not only the version but also the description and build information.

SELECT *
FROM system.build_options

VERSION_FULL	ClickHouse 23.9.2.47608
VERSION_DESCRIBE	v23.9.2.47608-stable
VERSION_INTEGER	23009002
SYSTEM	Linux
VERSION_GITHASH	5dcfa636185dc09dc2c477b58fc439b0aa1b4f29
VERSION_REVISION	54479
BUILD_TYPE	RelWithDebInfo
SYSTEM_PROCESSOR	x86_64
CMAKE_VERSION	3.27.7
C_COMPILER	/usr/bin/clang-17
C_COMPILER_VERSION	17.0.6
CXX_COMPILER	/usr/bin/clang++-17
CXX_COMPILER_VERSION	17.0.6
C_FLAGS	--gcc-toolchain=./cmake/linux/../../contrib/sysroot/linux-x86_64 -fdiagnostics-color=always -Xclang -fuse-ctor-homing -Wno-enum-constexpr-conversion -gdwarf-aranges -pipe -mssse3 -msse4.1 -msse4.2 -mpclmul -mpopcnt -fasynchronous-unwind-tables -ffile-prefix-map=.=. -ftime-trace -falign-functions=32 -mbranches-within-32B-boundaries -fdiagnostics-absolute-paths -Wframe-larger-than=65536 -Weverything -Wpedantic -Wno-zero-length-array -Wno-c++98-compat-pedantic -Wno-c++98-compat -Wno-c++20-compat -Wno-sign-conversion -Wno-implicit-int-conversion -Wno-implicit-int-float-conversion -Wno-ctad-maybe-unsupported -Wno-disabled-macro-expansion -Wno-documentation-unknown-command -Wno-double-promotion -Wno-exit-time-destructors -Wno-float-equal -Wno-global-constructors -Wno-missing-prototypes -Wno-missing-variable-declarations -Wno-padded -Wno-switch-enum -Wno-undefined-func-template -Wno-unused-template -Wno-vla -Wno-weak-template-vtables -Wno-weak-vtables -Wno-thread-safety-negative -Wno-enum-constexpr-conversion -Wno-unsafe-buffer-usage -O2 -g -DNDEBUG -O3 -g -gdwarf-4 -flto=thin -fwhole-program-vtables -fno-pie
CXX_FLAGS	--gcc-toolchain=./cmake/linux/../../contrib/sysroot/linux-x86_64 -fdiagnostics-color=always -Xclang -fuse-ctor-homing -Wno-enum-constexpr-conversion -fsized-deallocation -gdwarf-aranges -pipe -mssse3 -msse4.1 -msse4.2 -mpclmul -mpopcnt -fasynchronous-unwind-tables -ffile-prefix-map=.=. -ftime-trace -falign-functions=32 -mbranches-within-32B-boundaries -fdiagnostics-absolute-paths -fstrict-vtable-pointers -Wall -Wextra -Wframe-larger-than=65536 -Weverything -Wpedantic -Wno-zero-length-array -Wno-c++98-compat-pedantic -Wno-c++98-compat -Wno-c++20-compat -Wno-sign-conversion -Wno-implicit-int-conversion -Wno-implicit-int-float-conversion -Wno-ctad-maybe-unsupported -Wno-disabled-macro-expansion -Wno-documentation-unknown-command -Wno-double-promotion -Wno-exit-time-destructors -Wno-float-equal -Wno-global-constructors -Wno-missing-prototypes -Wno-missing-variable-declarations -Wno-padded -Wno-switch-enum -Wno-undefined-func-template -Wno-unused-template -Wno-vla -Wno-weak-template-vtables -Wno-weak-vtables -Wno-thread-safety-negative -Wno-enum-constexpr-conversion -Wno-unsafe-buffer-usage -O2 -g -DNDEBUG -O3 -g -gdwarf-4 -flto=thin -fwhole-program-vtables -fno-pie
LINK_FLAGS	--gcc-toolchain=./cmake/linux/../../contrib/sysroot/linux-x86_64 --ld-path=/usr/bin/ld.lld-17 -Wl,--no-export-dynamic -Wl,--gdb-index -Wl,--build-id=sha1 -no-pie -Wl,-no-pie -flto=thin -fwhole-program-vtables
BUILD_COMPILE_DEFINITIONS	
USE_EMBEDDED_COMPILER	1
USE_GLIBC_COMPATIBILITY	ON
USE_JEMALLOC	ON
USE_ICU	1
USE_H3	1
USE_MYSQL	1
USE_RDKAFKA	1
USE_CAPNP	1
USE_BASE64	1
USE_HDFS	1
USE_SNAPPY	1
USE_PARQUET	1
USE_PROTOBUF	1
USE_BROTLI	1
USE_SSL	1
OPENSSL_VERSION	1.1.1g
OPENSSL_IS_BORING_SSL	1
USE_VECTORSCAN	ON
USE_SIMDJSON	1
USE_ODBC	1
USE_GRPC	1
USE_LDAP	1
TZDATA_VERSION	2023c
USE_KRB5	1
USE_FILELOG	1
USE_BZIP2	1
USE_AMQPCPP	1
USE_ROCKSDB	1
USE_NURAFT	1
USE_NLP	1
USE_LIBURING	1
USE_SQLITE	1
USE_LIBPQXX	1
USE_AZURE_BLOB_STORAGE	1
USE_AWS_S3	1
USE_CASSANDRA	1
USE_YAML_CPP	1
USE_SENTRY	1
USE_DATASKETCHES	1
USE_AVRO	1
USE_ARROW	1
USE_ORC	1
USE_MSGPACK	1
USE_QPL	ON
GIT_HASH	5dcfa636185dc09dc2c477b58fc439b0aa1b4f29
GIT_BRANCH	release/2023-10-12
GIT_DATE	2023-12-13 11:08:55 +0000
GIT_COMMIT_SUBJECT	Merge pull request #4747 from ClickHouse/backport/release/2023-10-12/57743

How to check the ClickHouse version using the HTTP endpoint

ClickHouse also provides an HTTP interface, which can be particularly useful for remote checks or automated scripts. To check the ClickHouse version using this method, you can send a GET request to the HTTP endpoint with the <span class="code-exp">version()</span> function. In this example, replace the "<span class="ce">user</span>" with your username and "<span class="ce">password</span>" with your password.

curl --user '<user>:<password>' 'https://your_clickhouse_server:8123/?query=SELECT+version()'

23.9.2.47608

This command will return the version of ClickHouse running on the server. It's an effective way to integrate version checks into your CI/CD pipeline or monitoring systems.

You can also use the HTTP interface to get the ClickHouse version even if you don’t have access to the server. By sending a wrong query and incorrect credentials to the standard HTTP endpoint, you can find the version at the end of the response. The “Authentication Failed” error will give you the ClickHouse version of any server, even if you don’t have a valid username or password. The version in the example below is “23.9.2.47608 “.

curl --user 'INCORRECT_USER:INCORRECT_PASSWORD' --data-binary 'SELECT 1' 'https://<your_clickhouse_server>:<port>'

Code: 516. DB::Exception: default: Authentication failed: password is incorrect, or there is no user with such name.

If you have installed ClickHouse and forgot password you can reset it in the configuration file.
The password for default user is typically located at /etc/clickhouse-server/users.d/default-password.xml
and deleting this file will reset the password.
See also /etc/clickhouse-server/users.xml on the server where ClickHouse is installed.

. (AUTHENTICATION_FAILED) (version 23.9.2.47608 (official build))

How to check the version using the Prometheus endpoint

For organizations using Prometheus for monitoring, ClickHouse provides a Prometheus endpoint. You can query this endpoint to retrieve various metrics, including the ClickHouse version.

To check the ClickHouse version using Prometheus, you can query the relevant metric. This metric includes the version.

curl --user '<user>:<password>' 'http://<your_clickhouse_server>:<port>/metrics' | grep clickhouse_build_info

...
ClickHouseMetrics_VersionInteger 20003012
...

NOTE: This method only works when you run your own ClickHouse, not in ClickHouse Cloud.

Conclusion

Understanding how to check the ClickHouse version using SQL, HTTP, and Prometheus endpoints is essential for its operation. These methods provide flexibility and efficiency, particularly for teams responsible for operating ClickHouse deployments in production.

Reminder: If you are looking for a serverless alternative to ClickHouse, where you get the benefits of ClickHouse’s speed without the overhead of managing additional infrastructure, check out Propel.

Related posts

Start shipping today

Deliver the analytics your customers have been asking for.