Transfer data between storage devices in Managed ClickHouse®'s tiered storage
Moving data from network-attached block storage to object storage allows you to size down your block storage by selecting a service plan with less capacity. You can move the data back to network-attached block storage anytime.
You can transfer data between storage devices in Managed ClickHouse® using SQL statements against your tables directly.
Prerequisites
- At least one Managed ClickHouse service
- Command line tool (ClickHouse client) installed
Transfer data from network-attached block storage to object storage
Automatic data transfer
If you enable the tiered storage feature on your table, by default your data is moved from network-attached block storage to object storage as soon as it reaches 80% of its capacity.
Connect to your Managed ClickHouse service using, for example, the ClickHouse client.
Run the following query:
ALTER TABLE database_name.table_name MODIFY SETTING storage_policy = 'tiered'
Now, with the tiered storage feature enabled, your data is moved from network-attached block storage to object storage when it reaches 80% of its capacity. You can adjust that threshold at the service level.
Note
You can also configure your tiered storage so that data is moved to object storage at a specific time.
Manual data transfer
To move data manually from network-attached block storage to object storage, first list the partitions of your table:
SELECT partition, formatReadableSize(sum(data_compressed_bytes)) AS size
FROM system.parts
WHERE database = 'default' AND table = 'trips' AND active
GROUP BY partition
ORDER BY partitionThen move a partition by its value. On the quick-start trips table, partitioned by
toYYYYMM(pickup_date), a partition value looks like 201507:
ALTER TABLE default.trips MOVE PARTITION 201507 TO VOLUME 'remote'To configure data retention thresholds to automatically move data from network-attached block storage to object storage, see Configure data retention thresholds in Managed ClickHouse®’s tiered storage.
Transfer data from object storage to network-attached block storage
Use the MOVE PARTITION|PART statement to transfer data to network-attached block storage.
Connect to your Managed ClickHouse service using, for example, the ClickHouse client.
Select a database for operations you intend to perform.
USE database_nameRun the following query:
ALTER TABLE table_name MOVE PARTITION partition_expr TO VOLUME 'default'
Your data has been moved to network-attached block storage.