Item value type	Database table	ClickHouse type
Numeric (unsigned)	history_uint	uint
Numeric (float)	history	dbl
Character	history_str	str
Log	history_log	log
Text	history_text	text
Binary	history_bin	not supported by Zabbix
JSON	history_json	json

ClickHouse 不接受 JSON 数组。 JSON 值必须是单个对象，或者是一组对象。此外，ClickHouse 处理带有 NULL 的 JSON 键时，与缺失键的处理方式相同。

重要说明

housekeeper 不会从 ClickHouse 中删除数据。要控制数据保留时长，请在 ClickHouse 中配置 ClickHouse 数据存储周期。
Zabbix 不会在 ClickHouse 中计算或存储趋势数据。请考虑延长历史数据存储周期，以保留更早的数据。
如果您希望将现有 Zabbix 数据库（MySQL 或 PostgreSQL）中的历史数据迁移到 ClickHouse，请参阅 ClickHouse schema 和历史数据迁移脚本。
Zabbix proxy 不支持 ClickHouse。

配置 ClickHouse

您需要创建并配置 Zabbix 数据库和用户，并导入数据库结构。

本指南提供了有关 ClickHouse 的 Docker 或 package 安装的说明。

Docker

1. 在运行 ClickHouse 容器时，创建并配置 Zabbix 数据库和用户：

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. 确认 ClickHouse 正常工作，并且可以连接到它：

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

# 26.4.4.38

3. 使用 Zabbix 目录中的 history_all.sh 脚本导入数据库模式：

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

ClickHouse 的数据存储期限（Time-To-Live，TTL）默认是 31 天。如需更改，请在导入数据库模式时使用 --ttl 选项（例如，--ttl 604800 表示 7 天），或者稍后再配置。

4. 验证表是否已创建：

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

软件包

1. 启动 ClickHouse：

sudo service clickhouse-server start

2. 创建并配置 Zabbix 数据库和用户：

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. 使用 Zabbix 目录中的 history_all.sh 脚本导入数据库模式：

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

ClickHouse 的数据存储期限（Time-To-Live，TTL）默认是 31 天。如需更改，请在导入数据库模式时使用 --ttl 选项（例如，--ttl 604800 表示 7 天），或者稍后再进行配置。

4. 验证表是否已创建：

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

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

配置 Zabbix 服务器

在 Zabbix 服务器配置文件（zabbix_server.conf）中，设置 HistoryProvider 参数。

例如，要将所有支持的值类型存储到 ClickHouse 中：

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

完成更改后，重启 Zabbix 服务器：

systemctl restart zabbix-server

配置 Zabbix 前端

在你的 Zabbix 前端配置文件（zabbix.conf.php）中，将 $HISTORY_PROVIDERS 变量设置为与服务器配置一致：

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

其他配置

以下步骤是可选的。基础设置不需要这些步骤。

配置 ClickHouse 数据存储周期

ClickHouse 的数据存储周期（Time-To-Live，TTL）默认是 31 天。如需更改，请运行以下命令。

下面的示例使用 Docker。如果您是通过软件包安装的 ClickHouse，请直接在 ClickHouse 客户端中运行这些查询。

1. 修改表（将 history_json 和 3600 替换为您需要的值）：

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

2. 立即应用更改：

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

3. 验证数据存储周期是否已更改：

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. 重启 Zabbix 服务器，以在管理 > Housekeeping 中刷新数据存储周期：

systemctl restart zabbix-server

Configuring ClickHouse clusters

This section provides configuration steps for ClickHouse clusters with replication and sharding.

The steps below use Docker and the ClickHouse cluster_2S_2R from ClickHouse documentation (running ClickHouse 26.4.4.38 with ClickHouse Keeper).

Before you start, it is recommended to put a load balancer in front of your ClickHouse cluster for Zabbix. Zabbix server and frontend configuration supports only a single ClickHouse URL, so it can only point to one node. If that node goes down, a load balancer will redirect Zabbix to a healthy node instead, through that same URL.

1. Create the Zabbix database:

sudo docker exec -it clickhouse-01 \
  clickhouse-client \
  --user default \
  --query "CREATE DATABASE IF NOT EXISTS zabbix ON CLUSTER cluster_2S_2R ENGINE = Replicated('/clickhouse/databases/zabbix', '{shard}', '{replica}')"

The ON CLUSTER clause reaches only nodes that are part of the cluster when you run the command. If you add nodes to your cluster later, run this command again.

2. Create and configure the Zabbix user:

sudo docker exec -it clickhouse-01 \
  clickhouse-client \
  --user default \
  --query "CREATE USER IF NOT EXISTS zabbix ON CLUSTER cluster_2S_2R IDENTIFIED WITH sha256_password BY '<password>'"

sudo docker exec -it clickhouse-01 \
  clickhouse-client \
  --user default \
  --query "GRANT ON CLUSTER cluster_2S_2R CREATE, ALTER, DROP, INSERT, SELECT, UPDATE, OPTIMIZE ON zabbix.* TO zabbix"

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 \
  --engine "ReplicatedMergeTree()"

The data storage period (Time-To-Live, or TTL) for ClickHouse is, by default, 31 days. To change it, use the --ttl option when importing the database schema (e.g., --ttl 604800 for 7 days), or configure it later.

If your ClickHouse cluster requires explicit replica paths (for example, if your ClickHouse server configuration does not set default paths for replicated tables), run the schema scripts (history_schema.sh, history_uint_schema.sh, etc.) individually and set a different path for each table:

./usr/share/zabbix/sql-scripts/clickhouse/history_schema.sh \
  --user zabbix \
  --password <password> \
  --db zabbix \
  --server http://localhost:8123 \
  --engine "ReplicatedMergeTree('/clickhouse/tables/{shard}/history', '{replica}')"

For more details on ClickHouse replication, see Replicating data and Replicated* table engines in ClickHouse documentation.

4. Configure sharding for each table type. Rename the tables and create a Distributed table with the original name, pointing to the renamed table. This Distributed table is what Zabbix actually reads from and writes to; the table splits data across shards automatically. For example, for the history table:

sudo docker exec -it clickhouse-01 \
  clickhouse-client \
  --user default \
  --query "RENAME TABLE zabbix.history TO zabbix.history_local"

sudo docker exec -it clickhouse-01 \
  clickhouse-client \
  --user default \
  --query "CREATE TABLE zabbix.history AS zabbix.history_local ENGINE = Distributed('cluster_2S_2R', 'zabbix', 'history_local', itemid)"

For more details on ClickHouse sharding, see Distributed table engine in ClickHouse documentation.

5. Configure Zabbix server.

6. Configure Zabbix frontend.

7. Verify that Zabbix data is spread across shards. The row count from zabbix.history_local may change between repeated checks, since Zabbix server continuously inserts new data. Run this command on each node:

sudo docker exec -it clickhouse-01 \
  clickhouse-client \
  --user default \
  --query "SELECT count(*) FROM zabbix.history_local"

# 2446 (clickhouse-01)
# 2471 (clickhouse-02)
# 2494 (clickhouse-03)

8. Check that replication is in sync (active_replicas should match total_replicas):

sudo docker exec -it clickhouse-01 \
  clickhouse-client \
  --user default \
  --query "SELECT database, table, is_leader, total_replicas, active_replicas FROM system.replicas WHERE database = 'zabbix'"

# zabbix  history_json_local  1 2 2
# zabbix  history_local       1 2 2
# zabbix  history_log_local   1 2 2
# zabbix  history_str_local   1 2 2
# zabbix  history_text_local  1 2 2
# zabbix  history_uint_local  1 2 2

故障排除

以下步骤可能有助于你排查 ClickHouse 配置中的问题：

检查 ClickHouse 或 Zabbix 服务器日志中的错误。
要识别慢查询，请在 HistoryProvider Zabbix 服务器配置参数中使用 log_slow_queries 选项。
验证 ClickHouse 是否允许来自 Zabbix 服务器和 Zabbix 前端的访问。
查询 ClickHouse，查看 Zabbix 收集的数据是否已存储，例如：

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

What’s next?

为 Zabbix 配置 Nginx 的发行版特定说明

Docs

ClickHouse 设置