Example Code

This section describes the commands for separating cold data from hot data in CloudTable ClickHouse and the automated transfer of cold data into OBS storage buckets..

Sample Code

Create the ClickHouse cold and hot data separation table test_table.

CREATE TABLE IF NOT EXISTS test_table
(  

   `timestamp` DATETIME NOT NULL COMMENT " Log time",
    `type` INT NOT NULL COMMENT " log type",
    `error_code` INT COMMENT "Error code",
    `error_msg` VARCHAR(1024) COMMENT "Error details",
    `op_id` BIGINT COMMENT "Operator ID",
    `op_time` DATETIME COMMENT "Operation time"
 
) 
ENGINE = MergeTree()
PARTITION BY timestamp
ORDER BY timestamp

TTL timestamp + INTERVAL 1 DAY TO DISK 'cold_disk'
SETTINGS storage_policy = 'hot_to_cold';

Insert data for verification.

insert into test_table values('2024-06-04 10:36:00','1','404','Resource Not Found','998756','2024-06-04 11:36:00');  -- hot data
insert into test_table values('2024-06-04 10:35:00','1','404','Resource Not Found','998756','2024-06-04 11:35:00');  -- hot data
insert into test_table values('2024-06-03 10:33:00','1','404','Resource Not Found','998756','2024-06-03 11:33:00');  -- cold data
insert into test_table values('2024-03-27 09:10:00','1','200','ok','998756','2024-03-27 10:10:00');  -- cold data
insert into test_table values('2024-03-25 11:08:00','1','404','Resource Not Found','998756','2024-03-25 12:08:00');  -- cold data

Query the inserted data.
Query data.
```
select * from test_table FORMAT CSV;
```
Query the partition fields, partition name, and storage path of the partitioned table used for data storage.
```
SELECT name,partition,active,path FROM system.parts WHERE database = 'default' and table = 'test_table' and active = 1;
```
Figure 1 Querying data

The current system time is 22:00 on June 4, 2024. Data in the timestamp column of the test_table table that has been stored for more than one day is transferred to the OBS bucket cold_disk for storage.