[comment]: # translation:outdated

[comment]: # aside:9

[comment]: # ({new-2ee88fb6})
# ClickHouse setup

Zabbix can store history data in [ClickHouse](https://clickhouse.com/docs/intro) as an alternative to a relational database.

This guide covers the setup for the [supported versions](/manual/installation/requirements#thirdparty-external-surrounding-software) of ClickHouse.
If you're using a different version, some functionality may not work as intended.

ClickHouse can store the following value types:

|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|

::: noteclassic
ClickHouse does not accept JSON arrays.
A JSON value must be either a single object, or a set of objects.
Additionally, ClickHouse [handles JSON keys with NULL](https://clickhouse.com/docs/sql-reference/data-types/newjson#handling-json-keys-with-nulls) the same as missing keys.
:::

[comment]: # ({/new-2ee88fb6})

[comment]: # ({new-b5845077})
### Important notes

-   The [housekeeper](/manual/web_interface/frontend_sections/administration/housekeeping) does not delete data from ClickHouse.
    To control how long data is kept, [configure ClickHouse data storage period](#configuring-clickhouse-data-storage-period) in ClickHouse.
-   Zabbix does **not** calculate or store trends in ClickHouse.
    Consider extending the [history storage period](/manual/config/items/history_and_trends) 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](https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/database/clickhouse/README).
-   ClickHouse is not supported for Zabbix proxies.

[comment]: # ({/new-b5845077})

[comment]: # ({new-0bd328ae})
### Configuring ClickHouse

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

This guide gives instructions for [Docker](https://clickhouse.com/docs/install/docker) or [package](https://clickhouse.com/docs/install/debian_ubuntu) installations of ClickHouse.

[comment]: # ({/new-0bd328ae})

[comment]: # ({new-151d3722})
#### Docker

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

```bash
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:

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

# 26.4.4.38
```

3\. Import the database schema using the [`history_all.sh`](https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/database/clickhouse/history_all.sh) script from your Zabbix directory:

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

::: noteclassic
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](#configuring-clickhouse-data-storage-period) it later.
:::

4\. Verify that tables were created:

```bash
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
```

[comment]: # ({/new-151d3722})

[comment]: # ({new-a9c51c55})
#### Packages

1\. Start ClickHouse:

```bash
sudo service clickhouse-server start
```

2\. Create and configure the Zabbix database and user:

```bash
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`](https://git.zabbix.com/projects/ZBX/repos/zabbix/browse/database/clickhouse/history_all.sh) script from your Zabbix directory:

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

::: noteclassic
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](#configuring-clickhouse-data-storage-period) it later.
:::

4\. Verify that tables were created:

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

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

[comment]: # ({/new-a9c51c55})

[comment]: # ({new-a0c081fc})
### Configuring Zabbix server

In your Zabbix server configuration file (`zabbix_server.conf`), set the [`HistoryProvider`](/manual/concepts/server/server_params#historyprovider) parameter.

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

```ini
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:

```bash
systemctl restart zabbix-server
```

[comment]: # ({/new-a0c081fc})

[comment]: # ({new-03deb2c5})
### Configuring Zabbix frontend

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

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

[comment]: # ({/new-03deb2c5})

[comment]: # ({new-117e6e95})
### Additional configuration

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

[comment]: # ({/new-117e6e95})

[comment]: # ({new-ee188a72})
#### 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):

```bash
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:

```bash
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:

```bash
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*](/manual/web_interface/frontend_sections/administration/housekeeping):

```bash
systemctl restart zabbix-server
```

[comment]: # ({/new-ee188a72})

[comment]: # ({new-2c91add0})
### 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`](/manual/concepts/server/server_params#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:

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

[comment]: # ({/new-2c91add0})