ClickHouse 실시간 OLAP 분석 엔진: MergeTree 최적화부터 프로덕션 운영까지

행 지향 (PostgreSQL):
┌──────┬─────────┬─────────┬──────────┐
│ id   │ country │ browser │ duration │  ← 행 1
├──────┼─────────┼─────────┼──────────┤
│ id   │ country │ browser │ duration │  ← 행 2
└──────┴─────────┴─────────┴──────────┘

컬럼 지향 (ClickHouse):
┌──────────────────────┐
│ id: 1, 2, 3, 4, ...  │  ← 컬럼 파일 1
├──────────────────────┤
│ country: KR, US, ... │  ← 컬럼 파일 2
├──────────────────────┤
│ browser: Chrome, ... │  ← 컬럼 파일 3
├──────────────────────┤
│ duration: 42, 17, ...│  ← 컬럼 파일 4
└──────────────────────┘

CREATE TABLE events
(
    event_time DateTime CODEC(DoubleDelta, LZ4),
    user_id    UInt64   CODEC(Delta, ZSTD(3)),
    country    LowCardinality(String) CODEC(ZSTD(1)),
    duration   Float32  CODEC(Gorilla, LZ4),
    raw_json   String   CODEC(ZSTD(5))
)
ENGINE = MergeTree()
ORDER BY (event_time, user_id);

CREATE TABLE user_profiles
(
    user_id    UInt64,
    name       String,
    email      String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

-- 동일 user_id로 여러 번 INSERT 가능
INSERT INTO user_profiles VALUES (1, '김영주', 'yj@example.com', '2026-03-01 10:00:00');
INSERT INTO user_profiles VALUES (1, '김영주', 'yj_new@example.com', '2026-03-06 09:00:00');

-- 병합 전에는 두 행이 모두 보일 수 있음
-- FINAL 키워드로 최신 버전만 조회
SELECT * FROM user_profiles FINAL WHERE user_id = 1;

-- 잘못된 설계: 고카디널리티 컬럼이 앞에
CREATE TABLE events_bad
(
    event_id   UUID,
    user_id    UInt64,
    event_type LowCardinality(String),
    event_time DateTime
)
ENGINE = MergeTree()
ORDER BY (event_id);  -- UUID가 첫 번째: sparse index 효율 극히 낮음

-- 올바른 설계: 필터 패턴에 맞춘 정렬
CREATE TABLE events_good
(
    event_id   UUID,
    user_id    UInt64,
    event_type LowCardinality(String),
    event_time DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, user_id, event_time);

-- LowCardinality 적용 전후 비교
CREATE TABLE logs_v1 (level String) ENGINE = MergeTree() ORDER BY tuple();
CREATE TABLE logs_v2 (level LowCardinality(String)) ENGINE = MergeTree() ORDER BY tuple();

-- 데이터 삽입 후 크기 비교
SELECT
    table,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed
FROM system.columns
WHERE database = currentDatabase() AND table IN ('logs_v1', 'logs_v2')
GROUP BY table;

-- 1단계: 원본 이벤트 테이블
CREATE TABLE raw_events
(
    event_time DateTime,
    event_type LowCardinality(String),
    user_id    UInt64,
    revenue    Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

-- 2단계: 사전 집계 대상 테이블 (AggregatingMergeTree)
CREATE TABLE hourly_stats
(
    hour       DateTime,
    event_type LowCardinality(String),
    user_count AggregateFunction(uniq, UInt64),
    total_rev  AggregateFunction(sum, Float64),
    p99_rev    AggregateFunction(quantile(0.99), Float64)
)
ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (event_type, hour);

-- 3단계: Materialized View 생성
CREATE MATERIALIZED VIEW hourly_stats_mv
TO hourly_stats
AS SELECT
    toStartOfHour(event_time) AS hour,
    event_type,
    uniqState(user_id)    AS user_count,
    sumState(revenue)     AS total_rev,
    quantileState(0.99)(revenue) AS p99_rev
FROM raw_events
GROUP BY hour, event_type;

-- 4단계: 조회 시 -Merge 콤비네이터 사용
SELECT
    hour,
    event_type,
    uniqMerge(user_count)    AS unique_users,
    sumMerge(total_rev)      AS total_revenue,
    quantileMerge(0.99)(p99_rev) AS revenue_p99
FROM hourly_stats
WHERE hour >= '2026-03-01'
GROUP BY hour, event_type
ORDER BY hour;

-- PREWHERE를 명시적으로 사용
SELECT user_id, raw_json
FROM raw_events
PREWHERE event_type = 'purchase'
WHERE revenue > 100.0;

-- 테이블 생성 시 SAMPLE BY 지정 필요
CREATE TABLE events_sampled
(
    event_time DateTime,
    user_id    UInt64,
    event_type LowCardinality(String),
    revenue    Float64
)
ENGINE = MergeTree()
ORDER BY (event_type, sipHash64(user_id))
SAMPLE BY sipHash64(user_id);

-- 전체 데이터의 10%만 샘플링하여 조회
SELECT
    event_type,
    count() * 10 AS estimated_count,  -- 10배 보정
    avg(revenue) AS avg_revenue
FROM events_sampled
SAMPLE 0.1
GROUP BY event_type;

-- 쿼리 레벨 리소스 제한
SET max_threads = 8;                    -- 쿼리당 최대 스레드 수
SET max_memory_usage = 10000000000;     -- 쿼리당 최대 메모리 (10GB)
SET max_execution_time = 30;            -- 최대 실행 시간 (초)
SET max_rows_to_read = 1000000000;      -- 최대 읽기 행 수

-- 사용자/프로필 단위 제한 (config.xml 또는 SQL)
CREATE SETTINGS PROFILE 'analyst' SETTINGS
    max_threads = 4,
    max_memory_usage = 5000000000,
    max_execution_time = 60
TO analyst_role;

# 쿼리 실행 통계 포함 실행
clickhouse-client --query "
    SELECT event_type, count()
    FROM raw_events
    WHERE event_time >= '2026-03-01'
    GROUP BY event_type
    FORMAT PrettyCompactMonoBlock
    SETTINGS send_logs_level = 'trace'
"

# system.query_log에서 느린 쿼리 분석
clickhouse-client --query "
    SELECT
        query_duration_ms,
        read_rows,
        formatReadableSize(read_bytes) AS read_size,
        formatReadableSize(memory_usage) AS peak_memory,
        query
    FROM system.query_log
    WHERE type = 'QueryFinish'
      AND query_duration_ms > 1000
    ORDER BY query_duration_ms DESC
    LIMIT 10
"

CREATE TABLE embeddings
(
    doc_id   UInt64,
    content  String,
    vector   Array(Float32),
    INDEX vec_idx vector TYPE usearch(256) GRANULARITY 1
)
ENGINE = MergeTree()
ORDER BY doc_id;

-- 코사인 유사도 기반 검색
SELECT doc_id, content,
       cosineDistance(vector, [0.1, 0.2, ...]) AS distance
FROM embeddings
ORDER BY distance ASC
LIMIT 10;

# /etc/clickhouse-server/config.d/storage.yaml
# 멀티 디스크(핫/콜드) 스토리지 정책
storage_configuration:
  disks:
    hot:
      type: local
      path: /data/clickhouse/hot/
    cold:
      type: s3
      endpoint: https://s3.ap-northeast-2.amazonaws.com/my-bucket/clickhouse/
      access_key_id: '${S3_ACCESS_KEY}'
      secret_access_key: '${S3_SECRET_KEY}'
  policies:
    tiered:
      volumes:
        hot_volume:
          disk: hot
          max_data_part_size_bytes: 10737418240 # 10GB
        cold_volume:
          disk: cold
      move_factor: 0.8 # 핫 디스크 80% 차면 콜드로 이동

ALTER TABLE raw_events
    MODIFY TTL
        event_time + INTERVAL 30 DAY TO VOLUME 'cold_volume',
        event_time + INTERVAL 365 DAY DELETE;

-- Replicated 테이블 생성
CREATE TABLE raw_events ON CLUSTER 'production'
(
    event_time DateTime,
    event_type LowCardinality(String),
    user_id    UInt64,
    revenue    Float64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/raw_events', '{replica}')
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time);

# 내장 BACKUP 명령어 (ClickHouse 22.8+)
clickhouse-client --query "
    BACKUP TABLE raw_events
    TO S3('https://s3.ap-northeast-2.amazonaws.com/my-backup-bucket/raw_events_20260306/',
           '${S3_ACCESS_KEY}', '${S3_SECRET_KEY}')
    SETTINGS compression_method = 'lz4'
"

# 복원
clickhouse-client --query "
    RESTORE TABLE raw_events
    FROM S3('https://s3.ap-northeast-2.amazonaws.com/my-backup-bucket/raw_events_20260306/',
            '${S3_ACCESS_KEY}', '${S3_SECRET_KEY}')
"

# clickhouse-backup 도구 사용 (더 세밀한 제어)
clickhouse-backup create --tables="default.raw_events" daily_20260306
clickhouse-backup upload daily_20260306
clickhouse-backup list remote

-- async_insert 활성화
SET async_insert = 1;
SET wait_for_async_insert = 1;  -- INSERT 응답 전 실제 기록 대기
SET async_insert_max_data_size = 10485760;  -- 10MB마다 플러시
SET async_insert_busy_timeout_ms = 1000;    -- 최대 1초 대기

-- 파티션별 파트 수 확인
SELECT
    partition,
    count() AS part_count,
    formatReadableSize(sum(bytes_on_disk)) AS total_size
FROM system.parts
WHERE table = 'raw_events' AND active
GROUP BY partition
ORDER BY part_count DESC;

-- 머지 진행 상황 모니터링
SELECT
    table, partition_id,
    progress, elapsed,
    formatReadableSize(total_size_bytes_compressed) AS size
FROM system.merges
WHERE table = 'raw_events';

-- 레플리카 상태 확인
SELECT
    database, table,
    is_leader, is_readonly, is_session_expired,
    future_parts, parts_to_check,
    queue_size, inserts_in_queue, merges_in_queue,
    log_pointer, total_replicas, active_replicas
FROM system.replicas
WHERE table = 'raw_events';

-- 복제 재시작 시도
SYSTEM RESTART REPLICA raw_events;

-- 그래도 안 되면 레플리카를 정상 복제본에서 재동기화
-- (주의: 해당 레플리카의 로컬 데이터가 삭제됨)
SYSTEM RESTORE REPLICA raw_events;

-- 파티션별 디스크 사용량 확인
SELECT
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS disk_usage,
    min(min_time) AS oldest_data,
    max(max_time) AS newest_data,
    count() AS part_count
FROM system.parts
WHERE table = 'raw_events' AND active
GROUP BY partition
ORDER BY sum(bytes_on_disk) DESC;

-- 오래된 파티션 즉시 삭제 (비가역적!)
ALTER TABLE raw_events DROP PARTITION '202501';

-- MV 대상 테이블 초기화 후 백필
TRUNCATE TABLE hourly_stats;

INSERT INTO hourly_stats
SELECT
    toStartOfHour(event_time) AS hour,
    event_type,
    uniqState(user_id)    AS user_count,
    sumState(revenue)     AS total_rev,
    quantileState(0.99)(revenue) AS p99_rev
FROM raw_events
WHERE event_time >= '2026-01-01' AND event_time < '2026-02-01'
GROUP BY hour, event_type;
-- 월별로 반복 실행