How to change PARTITION in clickhouse

Question

version 18.16.1

CREATE TABLE traffic (
    `date` Date,
    ...
) ENGINE = MergeTree(date, (end_time), 8192);

I want to change as PARTITION BY toYYYYMMDD(date) without drop table how to do that.

Answer 1

Since ALTER query does not allow the partition alteration, the possible way is to create a new table

CREATE TABLE traffic_new
(
    `date` Date,
    ...
)
ENGINE = MergeTree(date, (end_time), 8192)
PARTITION BY toYYYYMMDD(date);

and to move your data

INSERT INTO traffic_new SELECT * FROM traffic WHERE column BETWEEN x and xxxx;

Rename the final table if necessary. And yes, this option involves deleting the old table (seems to be no way to skip this step)