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.
