Clickhouse 통계 테이블 작성법
목차
1. 개요
대용량 Observability 데이터(Logs, Traces, Metrics)를 직접 원본 테이블에서 실시간 분석하면 쿼리 비용이 매우 커지고 대시보드나 알림 시스템의 응답 속도가 저하됩니다. 이를 해결하기 위해 ClickHouse에서는 Materialized View를 활용한 통계 집계 테이블 구조를 적용할 수 있습니다.
Materialized View는 원본 테이블에 데이터가 INSERT되는 시점에 자동으로 실행되는 SELECT이며, 그 결과를 통계용 테이블에 실시간으로 적재합니다. 이를 통해 대시보드·알림·통계 조회는 원본 테이블을 직접 조회하는 대신 이미 집계된 테이블을 조회할 수 있어 성능과 안정성을 확보할 수 있습니다.
2. 전체 데이터 흐름
원본 테이블 → Materialized View → 통계 테이블| 단계 | 역할 |
|---|---|
| 원본 테이블 | OTEL Collector로부터 유입되는 원본 로그/트레이스/메트릭 저장 |
| Materialized View | INSERT 이벤트마다 자동 실행, 집계·필터링 수행 |
| 통계 테이블 | 시간 버킷/서비스/상태 기반의 집계 결과 보관 |
이 구조는 실시간 스트림 처리 방식과 유사한 동작을 하며, Grafana, Alerting, API 분석 등 반복적인 조회 성능에 큰 이점을 제공합니다.
3. 구성 예시
3.1 로그(Log) 분당 발생량 집계
목적
- 서비스별 로그 발생량과 심각도 수준(Severity)을 분 단위로 집계
데이터 생성 흐름
otel_logs (raw) → mv_otel_logs_minute → otel_logs_minute (summary)Materialized View
SELECT
toStartOfMinute(Timestamp) AS bucket_min,
ServiceName AS service,
SeverityText AS sev,
count() AS cnt
FROM otel_logs
GROUP BY bucket_min, service, sev;통계 테이블
CREATE TABLE k6_52565.otel_logs_minute
(
`bucket_min` DateTime, -- 분 단위 버킷 시작 시간
`service` LowCardinality(String), -- 서비스 이름
`sev` LowCardinality(String), -- 로그 심각도 (INFO/WARN/ERROR 등)
`cnt` UInt64 -- 발생 건수
)
ENGINE = SummingMergeTree
PARTITION BY
toYYYYMM(bucket_min)
ORDER BY
(bucket_min, service, sev)
SETTINGS
index_granularity = 8192;통계 테이블 활용 예시
- 서비스별 오류 증가 트렌드 감지
- 알람 감지 기준으로 동작 (예: 최근 5분 ERROR 로그 수 급증)
3.2 트레이스(Trace) 루트 스팬 집계
목적
- 서비스별 성공/실패 요청 비율 분석 및 SLA 지표 구축
데이터 생성 흐름
otel_traces (raw) → mv_trace_root_counts_minute_by_service → agg_trace_root_counts_minute_by_service (summary)Materialized View
CREATE MATERIALIZED VIEW default.mv_trace_root_counts_minute_by_service
TO default.agg_trace_root_counts_minute_by_service
(
`bucket_min` DateTime,
`service` LowCardinality(String),
`total_state` AggregateFunction(uniqCombined, String),
`success_state` AggregateFunction(uniqCombined, String),
`error_state` AggregateFunction(uniqCombined, String)
)
AS
SELECT
toStartOfMinute(Timestamp) AS bucket_min,
ServiceName AS service,
-- 전체 루트 TraceId 집계 상태
uniqCombinedStateIf(
TraceId,
(StatusCode != '') AND (ParentSpanId = '')
) AS total_state,
-- 성공 상태(에러가 아닌) 루트 TraceId 집계 상태
uniqCombinedStateIf(
TraceId,
(StatusCode != '') AND (StatusCode != 'Error') AND (ParentSpanId = '')
) AS success_state,
-- 에러 상태 루트 TraceId 집계 상태
uniqCombinedStateIf(
TraceId,
(StatusCode = 'Error') AND (ParentSpanId = '')
) AS error_state
FROM k6_52565.otel_traces
GROUP BY
bucket_min,
service;통계 테이블
CREATE TABLE default.agg_trace_root_counts_minute_by_service
(
`bucket_min` DateTime, -- 분 단위 버킷
`service` LowCardinality(String), -- 서비스 이름
`total_state` AggregateFunction(uniqCombined, String),
`success_state` AggregateFunction(uniqCombined, String),
`error_state` AggregateFunction(uniqCombined, String)
)
ENGINE = AggregatingMergeTree
PARTITION BY
toYYYYMM(bucket_min)
ORDER BY
(bucket_min, service)
SETTINGS
index_granularity = 8192;특징
AggregatingMergeTree엔진을 사용하여 TraceId 고유 개수를 실제 집계가 아닌 집계 상태 저장 방식으로 유지- 조회 시
uniqCombinedMerge(...)연산을 통해 최종 uniq count 계산
통계 테이블 활용 예시
- 서비스별 Error Rate 시계열 분석
- SLA 및 실패율 기반 자동 알림
3.3 에러 로그 필터링 테이블
목적
- 원본 로그 테이블에서 Error 로그만 별도 저장
데이터 생성 흐름
otel_logs (raw) → mv_otel_logs_error → otel_logs_error (filtered)**Materialized View
CREATE MATERIALIZED VIEW default.mv_otel_logs_error
TO default.otel_logs_error
(
`Timestamp` DateTime64(9),
`TimestampTime` DateTime,
`TraceId` String,
`SpanId` String,
`TraceFlags` UInt8,
`SeverityText` LowCardinality(String),
`SeverityNumber` UInt8,
`ServiceName` LowCardinality(String),
`Body` String,
`ResourceSchemaUrl` LowCardinality(String),
`ResourceAttributes` Map(LowCardinality(String), String),
`ScopeSchemaUrl` LowCardinality(String),
`ScopeName` String,
`ScopeVersion` LowCardinality(String),
`ScopeAttributes` Map(LowCardinality(String), String),
`LogAttributes` Map(LowCardinality(String), String)
)
AS
SELECT *
FROM k6_52565.otel_logs
WHERE SeverityText = 'ERROR';통계 테이블
CREATE TABLE default.otel_logs_error
(
`Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
`TimestampTime` DateTime DEFAULT toDateTime(Timestamp),
`TraceId` String CODEC(ZSTD(1)),
`SpanId` String CODEC(ZSTD(1)),
`TraceFlags` UInt8,
`SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
`SeverityNumber` UInt8,
`ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
`Body` String CODEC(ZSTD(1)),
`ResourceSchemaUrl` LowCardinality(String) CODEC(ZSTD(1)),
`ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`ScopeSchemaUrl` LowCardinality(String) CODEC(ZSTD(1)),
`ScopeName` String CODEC(ZSTD(1)),
`ScopeVersion` LowCardinality(String) CODEC(ZSTD(1)),
`ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY
toDate(TimestampTime)
PRIMARY KEY
(ServiceName, TimestampTime)
ORDER BY
(ServiceName, TimestampTime, Timestamp)
TTL
TimestampTime + toIntervalDay(90)
SETTINGS
index_granularity = 8192,
ttl_only_drop_parts = 1;특징
- 통계가 아닌 서브셋 테이블 생성 패턴
- 특정 조건에 맞는 데이터만 별도 관리해 조회 비용 절감
통계 테이블 활용 예시
- 최근 Error 로그만 빠르게 조회
- Root Cause Analysis 전용 데이터소스 구성
4. 설계 시 고려 사항
| 항목 | 권장 설계 |
|---|---|
| 시간 버킷 | toStartOfMinute, toStartOfHour 등 명확한 고정 버킷 |
| 통계 테이블 엔진 | SummingMergeTree 또는 AggregatingMergeTree |
| GROUP BY 키 | 서비스명, 상태코드, 엔드포인트 등 |
| TTL 정책 | 원본 테이블과 동일 또는 더 긴 TTL 가능 |
| 조회 용도 | Grafana·알림·대시보드·지표 API 등 |
추가 기준:
- Materialized View는 반드시 대상 통계 테이블 생성 후 생성해야 합니다.
- Select 컬럼 순서·타입은 통계 테이블 스키마와 정확히 일치해야 합니다.
- 기존 데이터까지 반영하려면
POPULATE키워드를 사용합니다.
5. 적용 시 효과
| 항목 | Before (원본 테이블 조회) | After (통계 테이블 조회) |
|---|---|---|
| 쿼리 비용 | 매우 높음 | 낮음 |
| 조회 지연 | 수백 ms ~ 수 초 | 수 ms 수준 |
| 대시보드 안정성 | 데이터량 증가 시 불안정 | 데이터량 증가와 무관 |
| 알림 이벤트 | 지연 가능 | 실시간 처리 안정적 |
| ClickHouse 부하 | 증가 | 감소 |
결론적으로, Materialized View 기반 통계 테이블은 대용량 관측 데이터 분석 환경에서 필수적인 최적화 요소입니다.
6. 확장 방향
통계 테이블 설계는 다음과 같은 지표 확장에도 그대로 적용할 수 있습니다.
- API별 평균 응답 시간 및 p95/p99
- Pod / Node 단위 CPU·메모리 사용량 분당 평균
- 사용자/User-Agent 기반 요청 트래픽 추세
- 비정상 접근 패턴 탐지용 통계 테이블
새로운 요구사항이 발생해도 원본 테이블 스키마를 변경하지 않고 Materialized View + 통계 테이블을 추가하는 방식으로 확장이 가능합니다.