How to drop all partitions of the table?

[Dmitriy Lazarenko]

Hello!

How can I drop all partitions of the table?

I played with system.parts:

ALTER TABLE my_table DROP PARTITION ID IN

(SELECT

    ID

FROM system.parts 

WHERE 

    active AND table = 'my_table')

or somthing like that.

but without success.

Thanks

[Denis Zhuravlev]

generate SQL

select concat('alter table `',table, '` drop partition ',partition, ';')
from system.parts
where active = 1 and table like '%my_table%'
group by database,table,partition
order by database,table,partition 

or truncate table my_table

[kriticar]

How about dropping paritions of ReplicatedMergeTree table on cluster?

For example if I have 10 nodes, 5 masters and 5 replicas, I should execute result of select you have provided on 5 master nodes.

Is there a better way?

Regards

[Denis Zhuravlev]

No better way.

But you can execute drop on cluster: alter table X on cluster segmented drop partition 201908

[kriticar]

Hi Denis,

it works only if tables are not replicated.

For example, looke at my example on https://groups.google.com/forum/#!topic/clickhouse/IKyVTK7mkfk

Regards.

[Denis Zhuravlev]

It was fixed in 19.10
https://github.com/yandex/ClickHouse/issues/5670

[kriticar]

At the moment I am on Altinity version:

ClickHouse client version 19.13.2.19.
Connected to ClickHouse server version 19.13.2 revision 54425.

and it doesn't work.

Regards.