TTL, 디스크 티어링, 데이터 보존 (S3 콜드 스토리지, 삭제와 롤업)

CREATE TABLE logs.events
(
    ts DateTime,
    service LowCardinality(String),
    level LowCardinality(String),
    message String,
    raw String TTL ts + INTERVAL 7 DAY      -- 원문은 일주일만, 그 뒤엔 빈 문자열
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (service, ts)
TTL ts + INTERVAL 30 DAY TO VOLUME 'cold',
    ts + INTERVAL 180 DAY DELETE
SETTINGS storage_policy = 'tiered', ttl_only_drop_parts = 1;

SELECT partition, name, rows, formatReadableSize(bytes_on_disk) AS size,
       delete_ttl_info_min, delete_ttl_info_max, disk_name
FROM system.parts
WHERE table = 'events' AND active
ORDER BY partition;

<clickhouse>
    <storage_configuration>
        <disks>
            <s3_cold>
                <type>s3</type>
                <endpoint>https://my-bucket.s3.ap-northeast-2.amazonaws.com/ch/</endpoint>
                <use_environment_credentials>true</use_environment_credentials>
                <metadata_path>/var/lib/clickhouse/disks/s3_cold/</metadata_path>
            </s3_cold>
            <s3_cold_cache>
                <type>cache</type>
                <disk>s3_cold</disk>
                <path>/var/lib/clickhouse/disks/s3_cold_cache/</path>
                <max_size>100Gi</max_size>
            </s3_cold_cache>
        </disks>
        <policies>
            <tiered>
                <volumes>
                    <hot>
                        <disk>default</disk>
                        <max_data_part_size_bytes>10737418240</max_data_part_size_bytes>
                    </hot>
                    <cold>
                        <disk>s3_cold_cache</disk>
                        <prefer_not_to_merge>false</prefer_not_to_merge>
                    </cold>
                </volumes>
                <move_factor>0.1</move_factor>
            </tiered>
        </policies>
    </storage_configuration>
</clickhouse>

SELECT policy_name, volume_name, volume_priority, disks, max_data_part_size, move_factor
FROM system.storage_policies;

SELECT name, path, formatReadableSize(free_space) AS free,
       formatReadableSize(total_space) AS total, type
FROM system.disks;

-- 파트를 손으로 옮기기
ALTER TABLE logs.events MOVE PARTITION '202605' TO VOLUME 'cold';

CREATE TABLE metrics.requests
(
    ts DateTime,
    service LowCardinality(String),
    status UInt16,
    cnt UInt64,
    latency_sum Float64
)
ENGINE = MergeTree
ORDER BY (service, toStartOfHour(ts), status, ts)
TTL ts + INTERVAL 30 DAY
    GROUP BY service, toStartOfHour(ts), status
    SET cnt = sum(cnt), latency_sum = sum(latency_sum), ts = min(ts);

ALTER TABLE logs.events MODIFY TTL
    ts + INTERVAL 7 DAY DELETE WHERE level = 'DEBUG',
    ts + INTERVAL 90 DAY DELETE WHERE level IN ('INFO', 'WARN'),
    ts + INTERVAL 365 DAY;