generating_scripts_from_clickhouse.md

Sometimes you have to move data from one table to a different one. You usually use

insert into target select * from source

This works but have several problems:

1. materialized columns are not properly copied
2. it's slow
3. the new table needs to be merged again

Clickhouse has a way to do it really fast: copying partitions. Unfortunately there is no command to copy all the partitions from one table to another but it's pretty easy to run a SQL to generate an script to do that:

SELECT concat('ALTER TABLE target REPLACE PARTITION ID \'', partition_id, '\' FROM source;')
FROM system.parts
WHERE (table = 'source') AND active
INTO OUTFILE '/tmp/move_data.sql'
FORMAT TabSeparatedRaw

Then

cat /tmp/move_data.sql | clickhouse-client -mn

You have to be careful because if something changes in between you generate and run the script, the data will be lost in the new table. To avoid that you could setup a materialized view from source to target.