6

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~

Alexander
  • 523
  • 5
  • 21

2 Answers2

1

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

Amos
  • 3,238
  • 4
  • 19
  • 41
  • 2
    How does `` look like? I tried `ALTER TABLE DELETE WHERE partition='20181001'` and `ALTER TABLE
    DELETE WHERE partition like '%201810%'`, but received exception from clickhouse: unkown identifier.
     – Alexander Mar 28 '19 at 02:40
  • 1
    Suppose your partition column has name `parcol`. You can write something like this `alter table db.table delete where parcol between toYYYYMMDD(toDate($startDate)) and toYYYYMMDD(toDate($endDate))` – Amos Mar 28 '19 at 03:17
  • 1
    Thanks~. Suppose I have a table column `date` whose type is `Date`, and the table is partitioned by `toYYYYMMDD(date)`. What you really mean is that I can use `delete` like this: `alter table db.table delete where date between '2018-10-01' and '2018-11-01'`. But how about partitioned by month, is this way efficient for dropping. – Alexander Mar 28 '19 at 03:48
  • `toYYYYMMDD` is monotonic so clickhouse will smartly pick up the correct partition pruning plan, same applies to tables partitioned by month. You can write `where toYYYYMMDD(date) between ... and ...` which resembles the partitioning scheme. I didn't do thorough tests on this topic, so please try it out and see if it's indeed efficient. – Amos Mar 28 '19 at 16:23
  • The method you proposed is based on the suppose that the table had a column `parcol`. But for all of our tables doesn't have this column. Anyway it's a good idea for higher version of ck to use `delete`. Thanks~ – Alexander Apr 11 '19 at 09:49
  • 1
    Is there any performance difference between using drop and delete where? – Jifri Valanchery Sep 17 '20 at 12:31
  • `DROP PARTITION` is more efficient since it doesn't read anything - only deletes. `ALTER DELETE` reads columns to check the condition. – tetafro Apr 02 '21 at 11:49
  • If the condition is a range of partitions, it would just delete the partition directories, so there would be no difference from using drop statement. Am I right? – ruxtain Nov 11 '22 at 08:22
1

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.

tetafro
  • 477
  • 1
  • 10
  • 15