Is it possible to delete old records from clickhouse table?
As far as I know, clickhouse allows only inserting new data. But is it possible to delete block older then some period to avoid overflow of HDD?
Answer
Altering data using Mutations
See the docs on Mutations feature https://clickhouse.yandex/docs/en/query_language/alter/#mutations.
The feature was implemented in Q3 2018.
Delete data
ALTER TABLE <table> DELETE WHERE <filter expression>
"Dirty" delete all
You always have to specify a filter expression. If you want to delete all the data through Mutation, specify something that's always true, eg.:
ALTER TABLE <table> DELETE WHERE 1=1
Update data
It's also possible to mutate (UPDATE) the similar way
ALTER TABLE <table> UPDATE column1 = expr1 [, ...] WHERE <filter expression>
Mind it's async
Please note that all commands above do not execute the data mutation directly (in sync). Instead they schedule ClickHouse Mutation that is executed independently (async) on background. That is the reason why ALTER TABLE syntax was chosen instead of typical SQL UPDATE/DELETE. You can check unfinished Mutations' progress via
SELECT *
FROM system.mutations
WHERE is_done = 0
Altering data without using Mutations
Theres's TRUNCATE TABLE statement with syntax as follows:
TRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]
This synchronously truncates the table. It will check for table size so won't allow you to delete if table size exceeds max_table_size_to_drop. See docs here:
https://clickhouse.tech/docs/en/sql-reference/statements/truncate/