ClickHouse setup

Zabbix can store history data in ClickHouse as an alternative to a relational database.

This guide covers the setup for the supported versions of ClickHouse. If you're using a different version, some functionality may not work as intended.

ClickHouse can store the following value types:

Important notes

• The housekeeper does not delete data from ClickHouse. To control how long data is kept, configure ClickHouse data storage period in ClickHouse.
• Zabbix does not calculate or store trends in ClickHouse. Consider extending the history storage period to preserve older data.
• If you want to migrate your history data from an existing Zabbix database (MySQL or PostgreSQL) to ClickHouse, please see the ClickHouse schema and history migration scripts.
• ClickHouse is not supported for Zabbix proxies.

Configuring ClickHouse

You need to create and configure a Zabbix database and user, and import the database schema.

This guide gives instructions for Docker or package installations of ClickHouse.

Docker

1. Create and configure the Zabbix database and user when running the ClickHouse container:

sudo docker run -d \
  --name clickhouse \
  -e CLICKHOUSE_DB=zabbix \
  -e CLICKHOUSE_USER=zabbix \
  -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
  -e CLICKHOUSE_PASSWORD=<password> \
  -p 8123:8123/tcp \
  -p 9000:9000/tcp \
  --cap-add=SYS_NICE \
  --cap-add=NET_ADMIN \
  --cap-add=IPC_LOCK \
  --ulimit nofile=262144:262144 \
  -v clickhouse_data:/var/lib/clickhouse \
  clickhouse/clickhouse-server:26.4

2. Confirm that ClickHouse is working and you can connect to it:

sudo docker exec -it clickhouse \
  clickhouse-client \
  --query "SELECT version()"

# 26.4.4.38

3. Import the database schema using the history_all.sh script from your Zabbix directory:

./usr/share/zabbix/sql-scripts/clickhouse/history_all.sh \
  --user zabbix \
  --password <password> \
  --db zabbix \
  --server http://localhost:8123

4. Verify that tables were created:

sudo docker exec -it clickhouse \
  clickhouse-client \
  --user zabbix \
  --password <password> \
  --query "SHOW TABLES FROM zabbix"

# history
# history_json
# history_log
# history_str
# history_text
# history_uint

Packages

1. Start ClickHouse:

sudo service clickhouse-server start

2. Create and configure the Zabbix database and user:

clickhouse-client --user default

localhost :) CREATE DATABASE IF NOT EXISTS zabbix
localhost :) CREATE USER IF NOT EXISTS zabbix IDENTIFIED WITH sha256_password BY '<password>'
localhost :) GRANT CREATE, ALTER, DROP, INSERT, SELECT, UPDATE, OPTIMIZE ON zabbix.* TO zabbix
localhost :) quit

3. Import the database schema using the history_all.sh script from your Zabbix directory:

./usr/share/zabbix/sql-scripts/clickhouse/history_all.sh \
  --user zabbix \
  --password <password> \
  --db zabbix \
  --server http://localhost:8123

4. Verify that tables were created:

clickhouse-client \
  --user zabbix \
  --password <password> \
  --query "SHOW TABLES FROM zabbix"

# history
# history_json
# history_log
# history_str
# history_text
# history_uint

Configuring Zabbix server

In your Zabbix server configuration file (zabbix_server.conf), set the HistoryProvider parameter.

For example, to store all supported types of values in ClickHouse:

HistoryProvider=clickhouse;value_types="uint,dbl,str,log,text,json",url=http://localhost:8123,db=zabbix,username=zabbix,password=<password>

After making changes, restart Zabbix server:

systemctl restart zabbix-server

Configuring Zabbix frontend

In your Zabbix frontend configuration file (zabbix.conf.php), set the $HISTORY_PROVIDERS variable to match the server configuration:

$HISTORY_PROVIDERS[] = [
  'types' => ['uint','dbl','str','log','text','json'],
  'provider' => 'clickhouse',
  'url' => 'http://localhost:8123',
  'db' => 'zabbix',
  'username' => 'zabbix',
  'password' => '<password>'
];

Additional configuration

The steps below are optional. You do not need them for a basic setup.

Configuring ClickHouse data storage period

The data storage period (Time-To-Live, or TTL) for ClickHouse is, by default, 31 days. To change it, run the commands below.

The examples below use Docker. If you installed ClickHouse using packages, run the queries in the ClickHouse client directly.

1. Alter the table (replace history_json and 3600 with the values you need):

sudo docker exec -it clickhouse \
  clickhouse-client \
  --user zabbix \
  --password <password> \
  --query "ALTER TABLE zabbix.history_json MODIFY TTL clock_ns + toIntervalSecond(3600)"

2. Apply the changes immediately:

sudo docker exec -it clickhouse \
  clickhouse-client \
  --user zabbix \
  --password <password> \
  --query "OPTIMIZE TABLE zabbix.history_json FINAL"

3. Verify that the data storage period changed:

sudo docker exec -it clickhouse \
  clickhouse-client \
  --user zabbix \
  --password <password> \
  --query "SHOW CREATE TABLE zabbix.history_json"

# CREATE TABLE zabbix.history_json
# (
#     `itemid` UInt64,
#     `clock_ns` DateTime64(9),
#     `value` JSON,
#     `value_str` String
# )
# ENGINE = MergeTree
# PARTITION BY toDate(clock_ns)
# PRIMARY KEY (itemid, clock_ns)
# ORDER BY (itemid, clock_ns)
# TTL clock_ns + toIntervalSecond(3600)
# SETTINGS index_granularity = 8192

4. Restart Zabbix server to refresh the data storage period in Administration > Housekeeping:

systemctl restart zabbix-server

Troubleshooting

The following steps may help you troubleshoot problems with your ClickHouse setup:

1. Check ClickHouse or Zabbix server logs for errors.

2. To identify slow queries, use the log_slow_queries option in the HistoryProvider Zabbix server configuration parameter.

3. Verify that ClickHouse allows access from Zabbix server and Zabbix frontend.

4. Query ClickHouse to see if the data collected by Zabbix is stored, for example:

sudo docker exec -it clickhouse \
  clickhouse-client \
  --user zabbix \
  --password <password> \
  --query "SELECT * FROM zabbix.history_uint WHERE itemid = 42269"