Is it possible to restore data to a table after updating the table schema.

[LalJohnGeon]

I need to apply a few compression and type changes on a table (a table having a size of around 88 GB), so I am planning the following steps.

1. backup of the existing data,
2. drop and create the table using a modified schema
3. restore the backup

But I got the below error:

Prepare data for restoring 'cmp_int_teraflow.sim_records'
2021/10/13 04:26:27 ALTER TABLE mytable ATTACH PART '0-20211012_3_3_0'
can't attach partitions for table 'mytable': code: 246, message: While loading part /var/lib/clickhouse/data/mytable/detached/attaching_0-20211012_3_3_0/: calculated partition ID: 527ffff429a1f97fb06bd02b19dadafa differs from partition ID in part name: 0-20211012

Can anyone suggest an ideal way to handle the scenario?

[Slach]

Why do you need backup for this use-case?

You can just use following SQL scenario

CREATE TABLE new_table ... ;

INSERT INTO new_table SELECT * FROM old_table;
DROP TABLE new_table SYNC;

[LalJohnGeon]

got the below exception while running the INSERT query.

DB::Exception: Cannot parse string 'in' as UInt64: syntax error at begin of string. Note: there are toUInt64OrZero and toUInt64OrNull functions, which returns zero/NULL instead of throwing exception.: while converting source column NetDirection to destination column device_pk: while executing 'FUNCTION _CAST(NetDirection :: 6, UInt64 :: 29) -> _CAST(NetDirection, UInt64) UInt64 : 28'. (CANNOT_PARSE_TEXT) (version 21.9.4.35 (official build)).

This might be due to the schema changes in the new table.

[Slach]

If you change the table schema
you should convert data inside SELECT clause within INSERT INTO ... SELECT ...