Is it possible to delete old records from clickhouse table?

Question

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 1

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

...unless

you change mutations_sync settings to

• 1 so it synchronously waits for current server
• 2 so it waits for all replicas

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/

Answer 2

Example to create and delete partition

    CREATE TABLE test.partitioned_by_month(d Date, x UInt8) ENGINE = MergeTree 

PARTITION BY toYYYYMM(d) ORDER BY x;

    INSERT INTO test.partitioned_by_month VALUES ('2000-01-01', 1), ('2000-01-02', 2), ('2000-01-03', 3);

INSERT INTO test.partitioned_by_month VALUES ('2000-02-03', 4), ('2000-02-03', 5);

INSERT INTO test.partitioned_by_month VALUES ('2000-03-03', 4), ('2000-03-03', 5);

SELECT * FROM test.partitioned_by_month;

---d------------|-------x-----

 2000-02-03 | 4 

 2000-02-03 | 5 


---d------------|-------x-----

 2000-03-03 | 4 

 2000-03-03 | 5 

---d------------|-------x-----

 2000-01-01 | 1 

 2000-01-02 | 2

 2000-01-03 | 3 

ALTER TABLE test.partitioned_by_month DROP PARTITION 200001;

select * from partitioned_by_month;


---d------------|-------x-----

 2000-03-03 | 4 

 2000-03-03 | 5 

---d------------|-------x-----


 2000-02-03 | 4 

 2000-02-03 | 5