BCollector > Maintenance > Data volume reduction

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.netflow_v5 DELETE WHERE timestamp_sec < toUnixTimestamp('1970-01-01');"
docker-compose exec clickhouse clickhouse-client --query="ALTER TABLE collector.netflow_v9 DELETE WHERE timestamp_sec < toUnixTimestamp('1970-01-01');"
docker-compose exec clickhouse clickhouse-client --query="ALTER TABLE collector.ipfix DELETE WHERE export_time < toUnixTimestamp('1970-01-01');"
docker-compose exec clickhouse clickhouse-client --query="ALTER TABLE collector.sflow DELETE WHERE timestamp_sec < toUnixTimestamp('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).