When there is too much data in a cluster instance, you can store some cold data in a COS bucket and hot data on a CDWCH local disk to save storage costs without compromising CDWCH's data query performance.
- This feature is applicable to CDWCH v126.96.36.199 or later. If your instance is not supported, upgrade it to v188.8.131.52 or later first.
- During tiering, the cluster will be restarted and become inaccessible.
- To use tiered storage, add the
SETTINGS storage_policy = 'hot_to_cold';statement to specify the storage policy during table creation or dynamically modify the storage policy of the table (which must be
defaultbefore the change).
- The storage policy cannot be modified once specified.
- The tiered storage feature cannot be disabled once enabled.
- Due to network restraints, you need to have a COS bucket in the same region as the cluster to perform tiered storage.
ALTER TABLE xx MODIFY SETTINGS storage_policy = 'hot_to_cold'.
After tiered storage is enabled, the parameters of the default storage policy are as described below:
|move_factor||When the proportion of the available storage space of the hot data disk is below this value, the oldest data written to the hot data disk will be automatically moved to the cold data disk. The valid value is a floating point number between 0 (exclusive) and 1 (inclusive). The default value is 0.1, indicating that data will be automatically moved when the proportion of available storage space is below 10%. This parameter can be modified through the tiered storage policy in the console.|
|prefer_not_to_merge|| Specifies whether to merge the data on the cold data disk. Valid values:
false (default): yes.
|max_data_part_size||Maximum part size. Parts on the hot data disk exceeding this size will be moved to the cold data disk. The default value is 0 (unlimited).|
Set the TTL-based tiered storage policy.
You can add a TTL statement to the default storage policy to automatically transfer all data older than the time interval to the cold data disk. You can also set the part expiration time by setting the TTL at the table level for data migration. The calculation result of the expression must be of the
Datetime type. The TTL needs to be expressed by the
INTERVAL operator of a
Date field. For the moving characteristics of a part, all rows in the part must meet the moving expression.
You can add a TTL statement according to the following syntax. The TTL can be set to
TO DISK 'cold_disk' or
TO VOLUME 'cold' (with the same effect), and the name can be viewed through the
system.storage_policies system tables.
TTL + INTERVAL TO DISK 'cold_disk'
|time_column||Column of `Date` or `Datetime` type .|
|number||Time interval in days, weeks, months, or years.|
CREATE TABLE ttl_test_tbl ( `f1` String, `f2` String, `f3` Int64, `f4` Float64, `date` Date ) ENGINE = MergeTree() PARTITION BY date ORDER BY f1 TTL date + INTERVAL 90 DAY TO DISK 'cold_disk' SETTINGS storage_policy = 'hot_to_cold';
Datetimetype and time interval of the TTL-based tiered storage policy.
- After the TTL-based tiered storage policy is modified, all existing and new data will be stored according to the new policy by default.
- If you don't want the change to the TTL-based tiered storage policy to take effect for existing data, run the
set materialize_ttl_after_modify=0;statement first before modifying the policy. In this way, only new data will be stored according to the new policy.
- After the TTL-based tiered storage policy is modified, data on the cold data disk will not be automatically moved to the hot data disk, but it can be manually moved.
You can modify the TTL-based tiered storage policy according to the following syntax:
ALTER TABLE MODIFY TTL + INTERVAL TO DISK 'cold_disk';
|time_column||Column of `Date` or `Datetime` type after the change.|
|number||Time interval in days, weeks, months, or years after the change.|
select partition,name,table,disk_name,database from system.parts where active=1
Find the part or partition to be moved by using the following syntax:
ALTER TABLE table_name MOVE PART｜PARTITION partition_expr TO VOLUME ' volume_name';
Move data from the hot data disk to the cold data disk.
ALTER TABLE MOVE PARTITION <partition> TO DISK 'cold_disk'; ALTER TABLE MOVE PARTITION <partition> TO VOLUME 'cold';
Move data from the cold data disk to the hot data disk.
ALTER TABLE ON CLUSTER default MOVE PARTITION TO DISK 'default'; ALTER TABLE ON CLUSTER default MOVE PARTITION TO VOLUME 'hot';