Table with TTL to S3

ClickHouse has an ability to store part of any table in S3. An example ClickHouse database query, to create a table with automatic move of data that passed some specified time period.

Usually, the main rule to move some data to S3 is the date it was created. Even though other types of main fields are supported. For example, when you don't need logs older than 14 days to be accessed often and fast. So, you can move this data to S3, but still, have access to them as they were on the disk. Obviously, the speed is lower, but in cases like this, it's not a big deal. You can consider this as cold storage for your data.

Before that make sure you connected S3 bucket to your ClickHouse server:

Attach S3 as a storage engine to ClickHouse

Here is the query to create new table with TTL to S3 Volume:

CREATE TABLE table_name
(
    `someField` String,
    `value` String,
    `offset` UInt64,
    `partition` UInt16,
    `timestamp` DateTime64(3)
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY (offset, timestamp, someField)
TTL toStartOfDay(timestamp, 'UTC') + toIntervalDay(14) TO VOLUME 's3'
SETTINGS storage_policy = 'tiered', index_granularity = 8192

In the case above data that are older that 14 days (based on the timestamp field) are going to be moved to S3.

If you need to update existing table, go with next scenario.

First you need alter storage policy for existing table:

ALTER TABLE table_name MODIFY SETTING storage_policy = 'tiered';

And then alter table to enable S3 support:

ALTER TABLE table_name MODIFY TTL toStartOfDay(createdAt, 'UTC') + toIntervalDay(14) TO VOLUME 's3' ;