Zabbix ClickHouse schema and history migration scripts

This collection of scripts facilitates creation of ClickHouse tables
and migration of Zabbix history data from MySQL or PostgreSQL to ClickHouse.

Scripts Overview
clickhouse.sh - helper script, not to be used directly

Schema Creation Scripts:
- history_all.sh - invokes all history schema creation scripts

- history_schema.sh
- history_log_schema.sh
- history_str_schema.sh
- history_text_schema.sh
- history_uint_schema.sh
- history_json_schema.sh

These scripts create ClickHouse table schemas for corresponding value types.

Usage:
./<history table>_schema.sh -s <ClickHouse URL> -d <database> [-u <username>] [-p <password>] [-t <TTL>] [-P <partition schema>] [-e <engine>] [-c <cluster>]

Parameters:
-s|--server: ClickHouse URL (default: http://localhost:8123)
-d|--db: Database name (default: zabbix)
-u|--user: ClickHouse username (default: empty, unauthenticated)
-p|--password: ClickHouse password (default: empty)
-t|--ttl: Housekeeping interval in seconds (default: 2678400 (31 days))
-P|--partition: Partitioning schema (default: toDate (by days))
-e|--engine: Table engine (default: MergeTree())
-c|--cluster: Cluster name, creates Distributed tables over local tables on all nodes (default: empty, no cluster)
-i|--import-dir: Directory with the exported CSV files (default: /tmp)
-h|--help: Display help message

Example:
./history_schema.sh -s http://localhost:8123 -d zabbix -u user -p password

The housekeeping interval (TTL) and partition schema are loosely linked.
When increasing housekeeping interval, it might be worth increasing also
partition schema, for example with year long housekeeping interval, it
might be better to set monthly partitioning:

./<history table>_schema.sh -s <ClickHouse URL> -d <database> -t 31536000 -P toYYYYMM

The engine is inserted into the CREATE TABLE statement as is, so it must be a
complete engine clause, including its parameters. For example, on a server
configured for replication ReplicatedMergeTree can be used:

./history_all.sh -s <ClickHouse URL> -d zabbix -e "ReplicatedMergeTree()"

If the engine takes a per table parameter, such as an explicit replica path,
run the schema scripts individually rather than through history_all.sh, which
passes the same arguments to all of them.

On a sharded cluster the -c|--cluster option creates the tables on all nodes
of the cluster defined in the server's remote_servers configuration. The data
is stored in <history table>_local tables with the selected engine, partitioning
and TTL, and <history table> is a Distributed table over them, sharded by item:

./history_all.sh -s <ClickHouse URL> -d zabbix -c <cluster> -e "ReplicatedMergeTree()"

The tables are created with ON CLUSTER queries, which require ClickHouse Keeper
(or ZooKeeper) to be configured even when the MergeTree engine is used.

All values of an item are stored on the same shard (sharding key intHash64(itemid)).
By default every query is still sent to all shards, to send queries only to the
shards holding the requested items enable optimize_skip_unused_shards=1 in the
settings profile of the Zabbix user. While it is enabled do not change the number
of shards or their weights: items would move to other shards and their older
values would no longer be returned until they expire.

The schema creation scripts never drop existing tables and fail if a table
already exists. To recreate a table, together with all its data, drop it first:

echo "DROP TABLE zabbix.history SYNC" | curl -u <user>:<password> <ClickHouse URL> --data-binary @-

With a cluster drop both the Distributed and the local table on all nodes:

echo "DROP TABLE zabbix.history ON CLUSTER <cluster> SYNC" | curl -u <user>:<password> <ClickHouse URL> --data-binary @-
echo "DROP TABLE zabbix.history_local ON CLUSTER <cluster> SYNC" | curl -u <user>:<password> <ClickHouse URL> --data-binary @-

Data Export Scripts:

MySQL:
- history_export_mysql.sql
- history_log_export_mysql.sql
- history_str_export_mysql.sql
- history_text_export_mysql.sql
- history_uint_export_mysql.sql
- history_json_export_mysql.sql

PostgreSQL:
- history_export_pgsql.sql
- history_log_export_pgsql.sql
- history_str_export_pgsql.sql
- history_text_export_pgsql.sql
- history_uint_export_pgsql.sql
- history_json_export_pgsql.sql

These SQL scripts export history data from MySQL or PostgreSQL databases to
/tmp/<history table>_clockns.csv files (the clock and ns columns are merged
into a single clock.ns timestamp column).

MySQL export note:
The MySQL export scripts use INTO OUTFILE, which writes files on the MySQL server
side. The output path is determined by the server's secure_file_priv setting
(check with: mysql -e "SELECT @@secure_file_priv"). If the path differs from
/tmp/, either copy the exported files to /tmp/ before running the import
scripts, or point the import scripts at that directory with the -i|--import-dir
option (e.g. -i /var/lib/mysql-files). When MySQL runs in Docker, files are
written inside the container and must be copied to the host:
docker cp <container>:<secure_file_priv_path>/<table>_clockns.csv /tmp/<table>_clockns.csv

MySQL export example:
mysql -u<user> -p<password> <database> < history_export_mysql.sql

PostgreSQL export example:
PGPASSWORD=<password> psql -U <user> -d <database> -h <host> -f history_export_pgsql.sql

Data Import Scripts:
- history_import.sh
- history_log_import.sh
- history_str_import.sh
- history_text_import.sh
- history_uint_import.sh
- history_json_import.sh

These scripts import exported history data from <import dir>/<history table>_clockns.csv
into corresponding ClickHouse tables. The import directory defaults to /tmp and
can be overridden with the -i|--import-dir option.

Usage:
./<history table>_import.sh -s <ClickHouse URL> -d <database> [-u <username>] [-p <password>] [-i <import dir>]

Migration Process:

1. Create ClickHouse schemas using the schema creation scripts.
2. Export data from MySQL or PostgreSQL using the export SQL scripts.
3. Import the exported data into ClickHouse using the import scripts.

PostgreSQL migration example (all tables):

PGPASSWORD=zabbix psql -U postgres -d zabbix -h 127.0.0.1 -f history_export_pgsql.sql
./history_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /tmp

PGPASSWORD=zabbix psql -U postgres -d zabbix -h 127.0.0.1 -f history_uint_export_pgsql.sql
./history_uint_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /tmp

PGPASSWORD=zabbix psql -U postgres -d zabbix -h 127.0.0.1 -f history_str_export_pgsql.sql
./history_str_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /tmp

PGPASSWORD=zabbix psql -U postgres -d zabbix -h 127.0.0.1 -f history_text_export_pgsql.sql
./history_text_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /tmp

PGPASSWORD=zabbix psql -U postgres -d zabbix -h 127.0.0.1 -f history_log_export_pgsql.sql
./history_log_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /tmp

PGPASSWORD=zabbix psql -U postgres -d zabbix -h 127.0.0.1 -f history_json_export_pgsql.sql
./history_json_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /tmp

MySQL migration example (all tables):

MySQL writes the exported files to the secure_file_priv directory (commonly
/var/lib/mysql-files), so the import scripts are pointed at it with -i. Adjust
the path to match your server's secure_file_priv setting.

mysql -uzabbix -pzabbix zabbix < history_export_mysql.sql
./history_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /var/lib/mysql-files

mysql -uzabbix -pzabbix zabbix < history_uint_export_mysql.sql
./history_uint_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /var/lib/mysql-files

mysql -uzabbix -pzabbix zabbix < history_str_export_mysql.sql
./history_str_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /var/lib/mysql-files

mysql -uzabbix -pzabbix zabbix < history_text_export_mysql.sql
./history_text_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /var/lib/mysql-files

mysql -uzabbix -pzabbix zabbix < history_log_export_mysql.sql
./history_log_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /var/lib/mysql-files

mysql -uzabbix -pzabbix zabbix < history_json_export_mysql.sql
./history_json_import.sh -s http://localhost:8123 -d zabbix -u zabbix -p zabbix -i /var/lib/mysql-files

Ensure you have the necessary permissions and ClickHouse is properly
configured before running these scripts.
