Ability to restore schema by running attach statement

[tadus21]

Hey,
We have a use case where we store data in ReplicatedMergeTree table which keeps it in different disks according to the TTL.
We noticed that in scenario where:
DISK_A stores table metadata and data, DISK_B stores just data and by accident DISK_A is lost
we can just manually move sql file with the attach table statement to DISK_A/clickhouse/metadata directory (if we have it backed up). After db restart, the table will be attached to the ClickHouse with all the parts from DISK_B present without manually moving them to detach folder and reattaching. This saves time making replica healthy because then ZK only have to restore data from DISK_A from the another replica.
I noticed that in backup metadata folder, table creation query have UUID in it so in theory backup tool could recreate the same scenario by running ATTACH TABLE instead of CREATE TABLE? Would it make sense to provide additional flag to restore command which would define whether ATTACH or CREATE statement should be ran for schema restoration? I seen that something similar is happening when restore_as_attach is set to true, but its only relevant when on data restoration flow.
If this make sense I would gladly contribute on this feature.

[Slach]

This is not typical use case

we partially implemented this approach
we could use
RESTORE_AS_ATTACH=1 clickhouse-backup restore

but, currently it will DROP + CREATE + DETACH TABLE + ATTACH TABLE ... and have race condition if run it paralllel in multiple replicas

let's convert discussion to issue and move it to backlog

[tadus21]

Could you elaborate why you would want to use RESTORE_AS_ATTACH? In our case we would want to skip CREATE statement and use ATTACH because we still have files in our filesystem related to this table and CREATE statement will fail if there is a dir with given UUID in store dir. However in your example when RESTORE_AS_ATTACH is run, the filesystem is totally clean since DROP and CREATE statements are executed, so what do you get by detaching and attaching newly created table?

[Slach]

with RESTORE_AS_ATTACH
we can replace DROP TABLE + CREATE TABLE to

• DETACH TABLE
• overwrite/var/lib/clickhouse/metadata/database/table.sql
• ATTACH TABLE

i think you can restore manually in your non typical use case