ClickHouse – MergeTree, ORDER BY, PARTITION BY, TTL và materialized view

CREATE DATABASE IF NOT EXISTS telemetry;
 
CREATE TABLE telemetry.interfaces
(
    time        DateTime CODEC(DoubleDelta, ZSTD),
    source      LowCardinality(String),
    name        LowCardinality(String),
    in_octets   UInt64 CODEC(Delta, ZSTD),
    out_octets  UInt64 CODEC(Delta, ZSTD),
    in_errors   UInt64 CODEC(Delta, ZSTD)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(time)
ORDER BY (source, name, time)
TTL time + INTERVAL 90 DAY
SETTINGS ttl_only_drop_parts = 1;

-- Đổi TTL bảng có sẵn. Mặc định (materialize_ttl_after_modify = 1) lệnh này
-- còn chạy MATERIALIZE TTL trên toàn bộ dữ liệu cũ: nặng IO với bảng lớn
ALTER TABLE telemetry.interfaces MODIFY TTL time + INTERVAL 30 DAY;
 
-- TTL theo cột: xoá nội dung message sau 7 ngày, giữ dòng
ALTER TABLE logs.syslog MODIFY COLUMN message String TTL toDateTime(timestamp) + INTERVAL 7 DAY;
 
-- Ép áp TTL cho dữ liệu cũ khi đã tắt materialize_ttl_after_modify
ALTER TABLE telemetry.interfaces MATERIALIZE TTL;

CREATE TABLE logs.syslog_1m
(
    t         DateTime,
    source    LowCardinality(String),
    mnemonic  LowCardinality(String),
    cnt       UInt64
)
ENGINE = SummingMergeTree
ORDER BY (source, mnemonic, t)
TTL t + INTERVAL 1 YEAR;
 
CREATE MATERIALIZED VIEW logs.syslog_1m_mv TO logs.syslog_1m AS
SELECT toStartOfMinute(timestamp) AS t, source, mnemonic, count() AS cnt
FROM logs.syslog
GROUP BY t, source, mnemonic;
 
-- Backfill dữ liệu đã có trước khi tạo MV
INSERT INTO logs.syslog_1m
SELECT toStartOfMinute(timestamp) AS t, source, mnemonic, count() AS cnt
FROM logs.syslog
GROUP BY t, source, mnemonic;
 
-- Query: luôn sum() lại vì merge gộp dòng không tức thời
SELECT t, source, sum(cnt) AS n
FROM logs.syslog_1m
WHERE t > now() - INTERVAL 1 DAY
GROUP BY t, source ORDER BY t;

CREATE TABLE telemetry.interfaces_5m
(
    t          DateTime,
    source     LowCardinality(String),
    name       LowCardinality(String),
    in_octets_max  SimpleAggregateFunction(max, UInt64),
    out_octets_max SimpleAggregateFunction(max, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (source, name, t)
TTL t + INTERVAL 2 YEAR;
 
CREATE MATERIALIZED VIEW telemetry.interfaces_5m_mv TO telemetry.interfaces_5m AS
SELECT toStartOfFiveMinutes(time) AS t, source, name,
       max(in_octets) AS in_octets_max, max(out_octets) AS out_octets_max
FROM telemetry.interfaces
GROUP BY t, source, name;