Commands Related to ClickHouse Cold and Hot Data Separation

This section describes the commands related to CloudTable ClickHouse cold and hot data separation. For details about how to use basic ClickHouse commands, see Using Clickhouse Commands.

Create a cold-hot separation table.

CREATE TABLE hot_cold_table
(
`f1` String,
`f2` String,
`f3` Int64,
`f4` Float64,
`date` Date
)
ENGINE = MergeTree()
PARTITION BY date
ORDER BY f1
SETTINGS storage_policy = 'hot_to_cold';

Set the cold data storage duration.

CREATE TABLE hot_cold_table
(
`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';

Parameters:

storage_policy: Specifies the storage policy. The value is hot_to_cold. Once a storage policy is specified, it cannot be modified later.

Update the TTL.
- Modify metadata:
```
ALTER TABLE hot_cold_table MODIFY TTL toDate(_cw_raw_time/ 1000) +toIntervalDay(29) TO VOLUME 'volum1_cold' SETTINGS materialize_ttl_after_modify=0;
```
- Manually moving data: You can run the alter table hot_cold_table move partition 'partition_name' to volume'volume_name' command to move a specified partition to a specified volume or disk.