CODEWITHSUNDEEP
clickhouse
Alexander
Alexander

Reputation: 533

Clickhouse: is there any way to drop multi-partition in one `alter table drop partition ****` query?

In clickhouse, the table is partitioned by day. Now I want to drop multi-partition in one alter query for convenience.

I've done this by shell using while loop:

while [[ $startDate < $endDate ]]
do
    clickhouse-client --query="alter table db.table drop partition toYYYYMMDD(toDate($startDate))"
    startDate=`date -d "+1 day $startDate" +%Y-%m-%d`
done

And I just want to find some way that can do this easily. Is there some way? Thanks~

Upvotes: 7

Views: 12740

Answers (2)

tetafro
tetafro

Reputation: 546

You can use this

ALTER TABLE my_table ON CLUSTER 'my-cluster'
    DROP PARTITION '2020-01-01',
    DROP PARTITION '2020-01-02',
    DROP PARTITION '2020-01-03'

Unfortunately, seems like there is nothing more convenient yet.

Upvotes: 3

Amos
Amos

Reputation: 3276

You can instead use ALTER TABLE <table> DELETE WHERE <partition-filters> to drop multiple partitions in one go.

Upvotes: 0

Related Questions