Truncate and Insert in ClickHouse Database

Question

I have a particular scenario where I need to truncate and batch insert into a Table in ClickHouse DBMS for every 30 minutes or so. I could find no reference of truncate option in ClickHouse.

However, I could find suggestions that we can indirectly achieve this by dropping the old table, creating a new table with same name and inserting data into it.

With respect to that, I have a few questions.

1. How is this achieved ? What is the sequence of steps in this process ?
2. What happens to other queries such as Select during the time when the table is being dropped and recreated ?
3. How long does it usually take for a table to be dropped and recreated in ClickHouse ?
4. Is there a better and clean way this can be achieved ?

Answer 1

How is this achieved ? What is the sequence of steps in this process ?

TRUNCATE is supported. There is no need to drop and recreate the table now.

What happens to other queries such as Select during the time when the table is being dropped and recreated ?

That depends on which table engine you use. For merge-tree family you get a snapshot-like behavior for SELECT.

How long does it usually take for a table to be dropped and recreated in ClickHouse ?

I would assume it relies on how fast the underlying file system can handle file deletions. For large tables it might contain millions of data part files which leads to slow truncation. However in your case I wouldn't worry much.

Is there a better and clean way this can be achieved ?

I suggest using partitons with a (DateTime / 60) column (per minute) along with a user script that constantly do partition harvest for out of date partitions.