ClickHouse 분석 패턴

고성능 분석 및 데이터 엔지니어링을 위한 ClickHouse 전용 패턴.

활성화 시점

• ClickHouse 테이블 스키마 설계 시 (MergeTree 엔진 선택)
• 분석 쿼리 작성 시 (집계, 윈도우 함수, 조인)
• 쿼리 성능 최적화 시 (파티션 프루닝, 프로젝션, 구체화된 뷰)
• 대량 데이터 수집 시 (배치 삽입, Kafka 통합)
• PostgreSQL/MySQL에서 ClickHouse로 분석 마이그레이션 시
• 실시간 대시보드 또는 시계열 분석 구현 시

개요

ClickHouse는 온라인 분석 처리(OLAP)를 위한 컬럼 지향 데이터베이스 관리 시스템(DBMS)입니다. 대규모 데이터셋에 대한 빠른 분석 쿼리에 최적화되어 있습니다.

주요 특징:

• 컬럼 지향 저장소
• 데이터 압축
• 병렬 쿼리 실행
• 분산 쿼리
• 실시간 분석

테이블 설계 패턴

MergeTree 엔진 (가장 일반적)

CREATE TABLE markets_analytics (
    date Date,
    market_id String,
    market_name String,
    volume UInt64,
    trades UInt32,
    unique_traders UInt32,
    avg_trade_size Float64,
    created_at DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, market_id)
SETTINGS index_granularity = 8192;

ReplacingMergeTree (중복 제거)

-- 중복이 있을 수 있는 데이터용 (예: 여러 소스에서 수집된 경우)
CREATE TABLE user_events (
    event_id String,
    user_id String,
    event_type String,
    timestamp DateTime,
    properties String
) ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_id, timestamp)
PRIMARY KEY (user_id, event_id);

AggregatingMergeTree (사전 집계)

-- 집계 메트릭을 유지하기 위한 용도
CREATE TABLE market_stats_hourly (
    hour DateTime,
    market_id String,
    total_volume AggregateFunction(sum, UInt64),
    total_trades AggregateFunction(count, UInt32),
    unique_users AggregateFunction(uniq, String)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(hour)
ORDER BY (hour, market_id);

-- 집계된 데이터 조회
SELECT
    hour,
    market_id,
    sumMerge(total_volume) AS volume,
    countMerge(total_trades) AS trades,
    uniqMerge(unique_users) AS users
FROM market_stats_hourly
WHERE hour >= toStartOfHour(now() - INTERVAL 24 HOUR)
GROUP BY hour, market_id
ORDER BY hour DESC;

쿼리 최적화 패턴

효율적인 필터링

-- PASS: 좋음: 인덱스된 컬럼을 먼저 사용
SELECT *
FROM markets_analytics
WHERE date >= '2025-01-01'
  AND market_id = 'market-123'
  AND volume > 1000
ORDER BY date DESC
LIMIT 100;

-- FAIL: 나쁨: 비인덱스 컬럼을 먼저 필터링
SELECT *
FROM markets_analytics
WHERE volume > 1000
  AND market_name LIKE '%election%'
  AND date >= '2025-01-01';

집계

-- PASS: 좋음: ClickHouse 전용 집계 함수를 사용
SELECT
    toStartOfDay(created_at) AS day,
    market_id,
    sum(volume) AS total_volume,
    count() AS total_trades,
    uniq(trader_id) AS unique_traders,
    avg(trade_size) AS avg_size
FROM trades
WHERE created_at >= today() - INTERVAL 7 DAY
GROUP BY day, market_id
ORDER BY day DESC, total_volume DESC;

-- PASS: 백분위수에는 quantile 사용 (percentile보다 효율적)
SELECT
    quantile(0.50)(trade_size) AS median,
    quantile(0.95)(trade_size) AS p95,
    quantile(0.99)(trade_size) AS p99
FROM trades
WHERE created_at >= now() - INTERVAL 1 HOUR;

윈도우 함수

-- 누적 합계 계산
SELECT
    date,
    market_id,
    volume,
    sum(volume) OVER (
        PARTITION BY market_id
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_volume
FROM markets_analytics
WHERE date >= today() - INTERVAL 30 DAY
ORDER BY market_id, date;

데이터 삽입 패턴

배치 삽입 (권장)

import { ClickHouse } from 'clickhouse'

const clickhouse = new ClickHouse({
  url: process.env.CLICKHOUSE_URL,
  port: 8123,
  basicAuth: {
    username: process.env.CLICKHOUSE_USER,
    password: process.env.CLICKHOUSE_PASSWORD
  }
})

// PASS: 배치 삽입 (효율적)
async function bulkInsertTrades(trades: Trade[]) {
  const rows = trades.map(trade => ({
    id: trade.id,
    market_id: trade.market_id,
    user_id: trade.user_id,
    amount: trade.amount,
    timestamp: trade.timestamp.toISOString()
  }))

  await clickhouse.insert('trades', rows)
}

// FAIL: 개별 삽입 (느림)
async function insertTrade(trade: Trade) {
  // 루프 안에서 이렇게 하지 마세요!
  await clickhouse.query(`
    INSERT INTO trades VALUES ('${trade.id}', ...)
  `).toPromise()
}

스트리밍 삽입

// 연속적인 데이터 수집용

…