Data volume reduction

If you need to remove the entries older than the specified date, run the command:

docker-compose exec clickhouse clickhouse-client --query="ALTER TABLE collector.flows DELETE WHERE timestamp_sec < toUnixTimestamp('1970-01-01');"
docker-compose exec clickhouse clickhouse-client --query="ALTER TABLE collector.rate_metrics DELETE WHERE date < toDateTime('1970-01-01');"

1970-01-01 here is the required date.

If you need to change the flow retention time, run the (script) with the following command:

docker-compose exec -T clickhouse clickhouse-client -mn < volume_reduction.sql

This script example assumes further modification for your own needs. Current script version:

  • deletes all data older then the collected last month.
  • changes the data lifetime to 1 month (time to live, TTL).

The script is executed once, then records in the database older than one month will be deleted automatically. To change the flow retention time change the toIntervalMonth(1) value, where 1 is the number of months you need. You can optionally resize the partition with PARTITION BY toYYYYMMDD(date).