Clickhouse: Attach all detached partitions

[simPod]

Is there a way to attach all detached partitions?

Something like:

ALTER TABLE table1 ATTACH PARTITION <all_expr>

Thanks!

(tried ALTER TABLE table1 ATTACH PARTITION 1=1 but nope 😄 )

[den-crane]

No.

I use

cd detached
let i=1;for f in `ls -1` ; do echo $i $f;((i++)); echo "alter table T attach part '$f';"|clickhouse-client ;sleep 1; done

In modern CH versions probably a table system.detached_parts can be used for automation.

[filimonov]

But actually it can be quite useful, especially during migration from MergeTree to Replicated.
Let's change it to feature request.

[simPod]

Perfect! Thanks for the command btw, will use it right now meanwhile.

[filimonov]

clickhouse-client --format=TSVRaw -q"select 'ALTER TABLE ' || database || '.' || table || ' ATTACH PARTITION ID \'' || partition_id || '\';\n' from system.detached_parts group by database, table, partition_id order by database, table, partition_id;" | clickhouse-client -mn

[alexey-milovidov]

@den-crane Attaching parts one by one will lead to data duplication if there are old parts after merge along with merged parts. I don't recommend doing ATTACH PART in a loop.

@filimonov This is way better!

[alexey-milovidov]

@filimonov Though your script is technically incorrect because it doesn't escape names properly.

[simPod]

Do you plan to somehow make this operation easier on ClickHouse side?