스키마 설계
스키마 설계 (Schema design)
로그와 트레이스용 ClickHouse 스키마를 직접 설계하는 방법과 최적화 기법을 상세히 다루는 가이드예요.
출처: 문서
본문
다음 이유로 로그와 트레이스용 자체 스키마를 항상 만들 것을 권장해요:
- 기본 키 선택 - 기본 스키마는 특정 접근 패턴에 최적화된
ORDER BY를 사용해요. 여러분의 접근 패턴이 이와 일치할 가능성은 낮아요. - 구조 추출 - 기존 컬럼(예:
Body)에서 새 컬럼을 추출하고 싶을 수 있어요. 이는 머티어리얼라이즈드 컬럼(복잡한 경우에는 머티어리얼라이즈드 뷰)으로 할 수 있어요. 이는 스키마 변경을 요구해요. - Maps 최적화 - 기본 스키마는 속성 저장에 Map 타입을 사용해요. 이 컬럼들은 임의 메타데이터 저장을 허용해요. 필수적인 기능이지만, 이벤트의 메타데이터가 사전에 정의되지 않는 경우가 많아 ClickHouse 같은 강타입 데이터베이스에 달리 저장할 수 없기 때문에, 맵 키와 그 값에 대한 접근은 일반 컬럼에 대한 접근만큼 효율적이지 않아요. 이는 스키마를 수정하고 가장 자주 접근하는 맵 키를 최상위 컬럼으로 만들면 해결돼요 — "SQL로 구조 추출"을 참고하세요. 이는 스키마 변경을 요구해요.
- 맵 키 접근 단순화 - 맵의 키에 접근하려면 더 장황한 구문이 필요해요. 별칭(alias)으로 이를 완화할 수 있어요. 쿼리를 단순화하려면 "Using Aliases"를 참고하세요.
- 보조 인덱스 - 기본 스키마는 Maps에 대한 접근을 빠르게 하고 텍스트 쿼리를 가속화하는 데 보조 인덱스를 사용해요. 이들은 일반적으로 필요하지 않고 추가 디스크 공간을 차지해요. 사용할 수는 있지만 필요하다는 것을 확인하기 위해 테스트해야 해요. "보조/데이터 건너뛰기 인덱스"를 참고하세요.
- 코덱 사용 - 예상되는 데이터를 이해하고 압축을 개선한다는 증거가 있다면 컬럼별 코덱을 커스터마이즈하고 싶을 수 있어요.
위 각 사용 사례를 아래에서 자세히 설명할게요. 중요: 최적의 압축과 쿼리 성능을 위해 스키마를 확장하고 수정하는 것이 권장되지만, 가능하면 핵심 컬럼에 대해 OTel 스키마 명명을 준수해야 해요. ClickHouse Grafana 플러그인은 쿼리 구축을 돕기 위해 Timestamp와 SeverityText 같은 몇 가지 기본 OTel 컬럼의 존재를 가정해요. 로그와 트레이스에 필요한 컬럼은 각각 [1][2] 그리고 여기에 문서화되어 있어요. 이 컬럼 이름을 변경하고 플러그인 구성의 기본값을 재정의할 수도 있어요.
ClickStack은 최적화된 기본 스키마를 제공해요 ClickStack은 로그, 트레이스, 메트릭용 기본 제공 스키마를 제공해요. 이 스키마는 최신 ClickHouse 기능(전문 및 맵 키 검색용 텍스트 인덱스, 직접 읽기 필터링용 머티어리얼라이즈드 컬럼과 ALIAS 배열, 블록 번호 행 조회)을 통합하고 로깅과 트레이스 워크로드에 강력한 기본 제공 성능을 제공하도록 벤치마킹되었어요. 여러분의 설계를 위한 참조점으로 사용하세요.
- 표준 DDL: ClickStack이 사용하는 테이블과 스키마.
- 최적화 레시피: ClickStack 성능 튜닝. 해당 페이지의 많은 권장 사항(머티어리얼라이즈드 컬럼, 건너뛰기 인덱스, 기본 키 선택, 프로젝션, 머티어리얼라이즈드 뷰)이 자체 구축 설정에 직접 적용돼요.
SQL로 구조 추출
구조화 또는 비구조화 로그를 수집하든, 사용자는 종종 다음 능력이 필요해요:
- 문자열 덩어리에서 컬럼 추출. 쿼리 시점에 문자열 연산을 사용하는 것보다 조회가 더 빠를 거예요.
- 맵에서 키 추출. 기본 스키마는 임의 속성을 Map 타입의 컬럼에 넣어요. 이 타입은 스키마리스 능력을 제공해서, 로그와 트레이스를 정의할 때 속성 컬럼을 미리 정의할 필요가 없게 해줘요 — Kubernetes에서 로그를 수집하고 pod 라벨이 나중에 검색되도록 유지되게 하려면 종종 불가능한 일이에요. 맵 키와 그 값에 접근하는 것은 일반 ClickHouse 컬럼에서 조회하는 것보다 느려요. 따라서 맵에서 키를 루트 테이블 컬럼으로 추출하는 것이 자주 바람직해요.
다음 쿼리를 고려해요. 구조화 로그를 사용해 어느 URL 경로가 가장 많은 POST 요청을 받는지 세고 싶다고 가정해요. JSON 덩어리는 String으로 Body 컬럼에 저장돼요. 또한 사용자가 collector에서 json_parser를 활성화했다면 Map(String, String)인 LogAttributes 컬럼에도 저장될 수 있어요.
SELECT LogAttributes
FROM otel_logs
LIMIT 1
FORMAT Vertical
Row 1:
──────
Body: {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27| 5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
LogAttributes: {'status':'200','log.file.name':'access-structured.log','request_protocol':'HTTP/1.1','run_time':'0','time_local':'2019-01-22 00:26:14.000','size':'30577','user_agent':'Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)','referer':'-','remote_user':'-','request_type':'GET','request_path':'/filter/27|13 ,27| 5 ,p53','remote_addr':'54.36.149.41'}
LogAttributes를 사용할 수 있다고 가정할 때, 사이트의 어느 URL 경로가 가장 많은 POST 요청을 받는지 세는 쿼리:
SELECT path(LogAttributes['request_path']) AS path, count() AS c
FROM otel_logs
WHERE ((LogAttributes['request_type']) = 'POST')
GROUP BY path
ORDER BY c DESC
LIMIT 5
┌─path─────────────────────┬─────c─┐
│ /m/updateVariation │ 12182 │
│ /site/productCard │ 11080 │
│ /site/productPrice │ 10876 │
│ /site/productModelImages │ 10866 │
│ /site/productAdditives │ 10866 │
└──────────────────────────┴───────┘
5 rows in set. Elapsed: 0.735 sec. Processed 10.36 million rows, 4.65 GB (14.10 million rows/s., 6.32 GB/s.)
Peak memory usage: 153.71 MiB.
여기서 맵 구문(예: LogAttributes['request_path'])과 URL에서 쿼리 매개변수를 제거하는 path 함수의 사용을 주목하세요. 사용자가 collector에서 JSON 파싱을 활성화하지 않았다면 LogAttributes가 비어 있어서, String Body에서 컬럼을 추출하려면 JSON 함수를 사용해야 해요.
ClickHouse가 파싱에 더 적합 구조화 로그의 JSON 파싱을 ClickHouse에서 수행하는 것을 일반적으로 권장해요. ClickHouse가 가장 빠른 JSON 파싱 구현이라고 확신해요. 하지만 로그를 다른 소스로 보내고 이 로직이 SQL에 있지 않기를 원할 수도 있다는 것을 인지하고 있어요.
SELECT path(JSONExtractString(Body, 'request_path')) AS path, count() AS c
FROM otel_logs
WHERE JSONExtractString(Body, 'request_type') = 'POST'
GROUP BY path
ORDER BY c DESC
LIMIT 5
┌─path─────────────────────┬─────c─┐
│ /m/updateVariation │ 12182 │
│ /site/productCard │ 11080 │
│ /site/productPrice │ 10876 │
│ /site/productAdditives │ 10866 │
│ /site/productModelImages │ 10866 │
└──────────────────────────┴───────┘
5 rows in set. Elapsed: 0.668 sec. Processed 10.37 million rows, 5.13 GB (15.52 million rows/s., 7.68 GB/s.)
Peak memory usage: 172.30 MiB.
이제 비구조화 로그에 대해 동일한 경우를 고려해요:
SELECT Body, LogAttributes
FROM otel_logs
LIMIT 1
FORMAT Vertical
Row 1:
──────
Body: 151.233.185.144 - - [22/Jan/2019:19:08:54 +0330] "GET /image/105/brand HTTP/1.1" 200 2653 "https://www.zanbil.ir/filter/b43,p56" "Mozilla/5.0 (Windows NT 6.1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36" "-"
LogAttributes: {'log.file.name':'access-unstructured.log'}
비구조화 로그에 대한 유사한 쿼리는 extractAllGroupsVertical 함수로 정규식을 사용해야 해요.
SELECT
path((groups[1])[2]) AS path,
count() AS c
FROM
(
SELECT extractAllGroupsVertical(Body, '(\\w+)\s([^\s]+)\sHTTP/\d\.\d') AS groups
FROM otel_logs
WHERE ((groups[1])[1]) = 'POST'
)
GROUP BY path
ORDER BY c DESC
LIMIT 5
┌─path─────────────────────┬─────c─┐
│ /m/updateVariation │ 12182 │
│ /site/productCard │ 11080 │
│ /site/productPrice │ 10876 │
│ /site/productModelImages │ 10866 │
│ /site/productAdditives │ 10866 │
└──────────────────────────┴───────┘
5 rows in set. Elapsed: 1.953 sec. Processed 10.37 million rows, 3.59 GB (5.31 million rows/s., 1.84 GB/s.)
비구조화 로그 파싱 쿼리의 복잡성과 비용 증가(성능 차이 주목)가 가능하면 항상 구조화 로그를 사용하라고 권장하는 이유예요.
사전(dictionaries) 고려 위 쿼리는 정규식 사전을 활용하도록 최적화할 수 있어요. 자세한 내용은 Using Dictionaries를 참고하세요.
이 두 사용 사례 모두 쿼리 로직을 삽입 시점으로 옮겨 ClickHouse에서 충족할 수 있어요. 아래에서 몇 가지 접근 방식을 탐구하고 각각 언제 적절한지 강조할게요.
OTel 또는 ClickHouse로 처리? 여기에 설명된 대로 OTel Collector processors와 operators로 처리를 수행할 수도 있어요. 대부분의 경우 ClickHouse가 collector의 processors보다 훨씬 리소스 효율적이고 빠르다는 것을 발견할 거예요. 모든 이벤트 처리를 SQL로 수행하는 주된 단점은 솔루션을 ClickHouse에 결합시키는 거예요. 예를 들어 OTel collector에서 처리된 로그를 S3 같은 대체 목적지로 보내고 싶을 수 있어요.
머티어리얼라이즈드 컬럼 (Materialized columns)
머티어리얼라이즈드 컬럼은 다른 컬럼에서 구조를 추출하는 가장 간단한 솔루션을 제공해요. 이 컬럼들의 값은 항상 삽입 시점에 계산되며 INSERT 쿼리에서 지정할 수 없어요.
오버헤드 머티어리얼라이즈드 컬럼은 값이 삽입 시점에 새 컬럼으로 디스크에 추출되므로 추가 저장 오버헤드를 발생시켜요.
머티어리얼라이즈드 컬럼은 모든 ClickHouse 표현식을 지원하고 문자열 처리(정규식 및 검색 포함)와 url용 분석 함수, 타입 변환 수행, JSON에서 값 추출, 수학 연산을 활용할 수 있어요. 기본 처리에 머티어리얼라이즈드 컬럼을 권장해요. 특히 맵에서 값을 추출해 루트 컬럼으로 승격하고 타입 변환을 수행하는 데 유용해요. 매우 기본적인 스키마에서 또는 머티어리얼라이즈드 뷰와 함께 사용할 때 가장 유용한 경우가 많아요. collector가 JSON을 LogAttributes 컬럼으로 추출한 로그용 스키마를 고려해요:
CREATE TABLE otel_logs
(
`Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
`TraceId` String CODEC(ZSTD(1)),
`SpanId` String CODEC(ZSTD(1)),
`TraceFlags` UInt32 CODEC(ZSTD(1)),
`SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
`SeverityNumber` Int32 CODEC(ZSTD(1)),
`ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
`Body` String CODEC(ZSTD(1)),
`ResourceSchemaUrl` String CODEC(ZSTD(1)),
`ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`ScopeSchemaUrl` String CODEC(ZSTD(1)),
`ScopeName` String CODEC(ZSTD(1)),
`ScopeVersion` String CODEC(ZSTD(1)),
`ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`RequestPage` String MATERIALIZED path(LogAttributes['request_path']),
`RequestType` LowCardinality(String) MATERIALIZED LogAttributes['request_type'],
`RefererDomain` String MATERIALIZED domain(LogAttributes['referer'])
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SeverityText, toUnixTimestamp(Timestamp), TraceId)
String Body에서 JSON 함수로 추출하는 동등한 스키마는 여기에서 찾을 수 있어요. 우리의 세 머티어리얼라이즈드 컬럼은 요청 페이지, 요청 타입, 리퍼러 도메인을 추출해요. 이들은 맵 키에 접근하고 그 값에 함수를 적용해요. 후속 쿼리가 훨씬 빨라요:
SELECT RequestPage AS path, count() AS c
FROM otel_logs
WHERE RequestType = 'POST'
GROUP BY path
ORDER BY c DESC
LIMIT 5
┌─path─────────────────────┬─────c─┐
│ /m/updateVariation │ 12182 │
│ /site/productCard │ 11080 │
│ /site/productPrice │ 10876 │
│ /site/productAdditives │ 10866 │
│ /site/productModelImages │ 10866 │
└──────────────────────────┴───────┘
5 rows in set. Elapsed: 0.173 sec. Processed 10.37 million rows, 418.03 MB (60.07 million rows/s., 2.42 GB/s.)
Peak memory usage: 3.16 MiB.
머티어리얼라이즈드 컬럼은 기본적으로 SELECT *에서 반환되지 않아요. 이는 SELECT *의 결과가 항상 INSERT로 테이블에 다시 삽입될 수 있다는 불변식을 보존하기 위해서예요. 이 동작은 asterisk_include_materialized_columns=1을 설정해 비활성화할 수 있고 Grafana에서 활성화할 수 있어요(데이터 소스 구성의 Additional Settings -> Custom Settings 참고).
머티어리얼라이즈드 뷰
머티어리얼라이즈드 뷰는 로그와 트레이스에 SQL 필터링과 변환을 적용하는 더 강력한 수단을 제공해요. 머티어리얼라이즈드 뷰는 계산 비용을 쿼리 시점에서 삽입 시점으로 옮길 수 있게 해줘요. ClickHouse 머티어리얼라이즈드 뷰는 테이블에 삽입되는 데이터 블록에 쿼리를 실행하는 트리거일 뿐이에요. 이 쿼리의 결과는 두 번째 "대상" 테이블에 삽입돼요.
실시간 업데이트 ClickHouse의 머티어리얼라이즈드 뷰는 데이터가 기반이 되는 테이블로 흐르면서 실시간으로 업데이트되어, 연속적으로 업데이트되는 인덱스처럼 기능해요. 반면 다른 데이터베이스에서 머티어리얼라이즈드 뷰는 보통 새로고침해야 하는 쿼리의 정적 스냅샷이에요(ClickHouse Refreshable Materialized Views와 유사).
머티어리얼라이즈드 뷰와 연관된 쿼리는 이론적으로 모든 쿼리가 될 수 있어요. 집계를 포함하더라도요(Joins에는 제한 존재). 로그와 트레이스에 필요한 변환과 필터링 워크로드에는 어떤 SELECT 문도 가능하다고 간주할 수 있어요. 쿼리는 테이블(소스 테이블)에 삽입되는 행에 대해 실행되는 트리거일 뿐이며, 결과가 새 테이블(대상 테이블)로 전송된다는 것을 기억해야 해요. 데이터를(소스와 대상 테이블에) 두 번 영속화하지 않도록 소스 테이블의 엔진을 Null 테이블 엔진으로 바꿔 원래 스키마를 보존할 수 있어요. OTel collector는 계속 이 테이블로 데이터를 보낼 거예요. 예를 들어 로그의 경우 otel_logs 테이블이 다음이 돼요:
CREATE TABLE otel_logs
(
`Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
`TraceId` String CODEC(ZSTD(1)),
`SpanId` String CODEC(ZSTD(1)),
`TraceFlags` UInt32 CODEC(ZSTD(1)),
`SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
`SeverityNumber` Int32 CODEC(ZSTD(1)),
`ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
`Body` String CODEC(ZSTD(1)),
`ResourceSchemaUrl` String CODEC(ZSTD(1)),
`ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`ScopeSchemaUrl` String CODEC(ZSTD(1)),
`ScopeName` String CODEC(ZSTD(1)),
`ScopeVersion` String CODEC(ZSTD(1)),
`ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1))
) ENGINE = Null
Null 테이블 엔진은 강력한 최적화예요. /dev/null로 생각해요. 이 테이블은 데이터를 저장하지 않지만, 첨부된 머티어리얼라이즈드 뷰는 삽입된 행이 버려지기 전에 그 행에 대해 여전히 실행돼요. 다음 쿼리를 고려해요. 이는 행을 보존하고 싶은 포맷으로 변환하고, LogAttributes에서 모든 컬럼을 추출하며(collector가 json_parser 연산자로 설정했다고 가정), SeverityText와 SeverityNumber를 설정해요(간단한 조건과 이 컬럼들의 정의에 기반). 이 경우 채워질 것이라고 아는 컬럼만 선택하고 TraceId, SpanId, TraceFlags 같은 컬럼은 무시해요.
SELECT
Body,
Timestamp::DateTime AS Timestamp,
ServiceName,
LogAttributes['status'] AS Status,
LogAttributes['request_protocol'] AS RequestProtocol,
LogAttributes['run_time'] AS RunTime,
LogAttributes['size'] AS Size,
LogAttributes['user_agent'] AS UserAgent,
LogAttributes['referer'] AS Referer,
LogAttributes['remote_user'] AS RemoteUser,
LogAttributes['request_type'] AS RequestType,
LogAttributes['request_path'] AS RequestPath,
LogAttributes['remote_addr'] AS RemoteAddr,
domain(LogAttributes['referer']) AS RefererDomain,
path(LogAttributes['request_path']) AS RequestPage,
multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs
LIMIT 1
FORMAT Vertical
Row 1:
──────
Body: {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27| 5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
Timestamp: 2019-01-22 00:26:14
ServiceName:
Status: 200
RequestProtocol: HTTP/1.1
RunTime: 0
Size: 30577
UserAgent: Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)
Referer: -
RemoteUser: -
RequestType: GET
RequestPath: /filter/27|13 ,27| 5 ,p53
RemoteAddr: 54.36.149.41
RefererDomain:
RequestPage: /filter/27|13 ,27| 5 ,p53
SeverityText: INFO
SeverityNumber: 9
위에서 Body 컬럼도 추출해요 — 나중에 SQL이 추출하지 않는 추가 속성이 추가되는 경우를 대비해서요. 이 컬럼은 ClickHouse에서 잘 압축되어야 하고 드물게 접근되므로 쿼리 성능에 영향을 미치지 않아요. 마지막으로 캐스트로 Timestamp를 DateTime으로 줄여요(공간 절약 — "타입 최적화" 참고).
조건부 함수 위에서 SeverityText와 SeverityNumber를 추출하는 데 조건부 함수를 사용한 것을 주목하세요. 이는 복잡한 조건을 구성하고 맵에서 값이 설정되었는지 확인하는 데 극히 유용해요 — 모든 키가 LogAttributes에 존재한다고 순진하게 가정해요. 이에 익숙해지기를 권장해요 — null 값 처리 함수와 함께 로그 파싱에서 여러분의 친구예요!
이 결과를 받을 테이블이 필요해요. 아래 대상 테이블은 위 쿼리와 일치해요:
CREATE TABLE otel_logs_v2
(
`Body` String,
`Timestamp` DateTime,
`ServiceName` LowCardinality(String),
`Status` UInt16,
`RequestProtocol` LowCardinality(String),
`RunTime` UInt32,
`Size` UInt32,
`UserAgent` String,
`Referer` String,
`RemoteUser` String,
`RequestType` LowCardinality(String),
`RequestPath` String,
`RemoteAddress` IPv4,
`RefererDomain` String,
`RequestPage` String,
`SeverityText` LowCardinality(String),
`SeverityNumber` UInt8
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)
여기 선택된 타입은 "타입 최적화"에서 논의된 최적화에 기반해요.
스키마가 어떻게 급격히 바뀌었는지 주목하세요. 실제로는 보존하고 싶은 Trace 컬럼과 ResourceAttributes 컬럼(보통 Kubernetes 메타데이터 포함)도 있을 거예요. Grafana는 트레이스 컬럼을 활용해 로그와 트레이스 사이의 연결 기능을 제공할 수 있어요 — "Grafana 사용"을 참고하세요.
아래에서 otel_logs 테이블에 대해 위 select를 실행하고 결과를 otel_logs_v2로 보내는 머티어리얼라이즈드 뷰 otel_logs_mv를 만들어요.
CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2 AS
SELECT
Body,
Timestamp::DateTime AS Timestamp,
ServiceName,
LogAttributes['status']::UInt16 AS Status,
LogAttributes['request_protocol'] AS RequestProtocol,
LogAttributes['run_time'] AS RunTime,
LogAttributes['size'] AS Size,
LogAttributes['user_agent'] AS UserAgent,
LogAttributes['referer'] AS Referer,
LogAttributes['remote_user'] AS RemoteUser,
LogAttributes['request_type'] AS RequestType,
LogAttributes['request_path'] AS RequestPath,
LogAttributes['remote_addr'] AS RemoteAddress,
domain(LogAttributes['referer']) AS RefererDomain,
path(LogAttributes['request_path']) AS RequestPage,
multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs
이제 "ClickHouse로 내보내기"에서 사용된 collector 구성을 재시작하면 데이터가 원하는 포맷으로 otel_logs_v2에 나타나요. 타입이 지정된 JSON 추출 함수의 사용을 주목하세요.
SELECT *
FROM otel_logs_v2
LIMIT 1
FORMAT Vertical
Row 1:
──────
Body: {"remote_addr":"54.36.149.41","remote_user":"-","run_time":"0","time_local":"2019-01-22 00:26:14.000","request_type":"GET","request_path":"\/filter\/27|13 ,27| 5 ,p53","request_protocol":"HTTP\/1.1","status":"200","size":"30577","referer":"-","user_agent":"Mozilla\/5.0 (compatible; AhrefsBot\/6.1; +http:\/\/ahrefs.com\/robot\/)"}
Timestamp: 2019-01-22 00:26:14
ServiceName:
Status: 200
RequestProtocol: HTTP/1.1
RunTime: 0
Size: 30577
UserAgent: Mozilla/5.0 (compatible; AhrefsBot/6.1; +http://ahrefs.com/robot/)
Referer: -
RemoteUser: -
RequestType: GET
RequestPath: /filter/27|13 ,27| 5 ,p53
RemoteAddress: 54.36.149.41
RefererDomain:
RequestPage: /filter/27|13 ,27| 5 ,p53
SeverityText: INFO
SeverityNumber: 9
JSON 함수로 Body 컬럼에서 컬럼을 추출하는 동등한 머티어리얼라이즈드 뷰는 아래와 같아요:
CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2 AS
SELECT Body,
Timestamp::DateTime AS Timestamp,
ServiceName,
JSONExtractUInt(Body, 'status') AS Status,
JSONExtractString(Body, 'request_protocol') AS RequestProtocol,
JSONExtractUInt(Body, 'run_time') AS RunTime,
JSONExtractUInt(Body, 'size') AS Size,
JSONExtractString(Body, 'user_agent') AS UserAgent,
JSONExtractString(Body, 'referer') AS Referer,
JSONExtractString(Body, 'remote_user') AS RemoteUser,
JSONExtractString(Body, 'request_type') AS RequestType,
JSONExtractString(Body, 'request_path') AS RequestPath,
JSONExtractString(Body, 'remote_addr') AS remote_addr,
domain(JSONExtractString(Body, 'referer')) AS RefererDomain,
path(JSONExtractString(Body, 'request_path')) AS RequestPage,
multiIf(Status::UInt64 > 500, 'CRITICAL', Status::UInt64 > 400, 'ERROR', Status::UInt64 > 300, 'WARNING', 'INFO') AS SeverityText,
multiIf(Status::UInt64 > 500, 20, Status::UInt64 > 400, 17, Status::UInt64 > 300, 13, 9) AS SeverityNumber
FROM otel_logs
타입 주의
위 머티어리얼라이즈드 뷰는 암시적 캐스팅에 의존해요 — 특히 LogAttributes 맵을 사용할 때요. ClickHouse는 추출된 값을 대상 테이블 타입으로 투명하게 캐스팅하는 경우가 많아 필요한 구문을 줄여줘요. 하지만 뷰를 항상 테스트할 것을 권장해요. 같은 스키마를 가진 대상 테이블에 대한 INSERT INTO 문과 함께 뷰의 SELECT 문을 사용해 테스트해요. 이는 타입이 올바르게 처리되는지 확인해야 해요. 다음 경우에 특별한 주의를 기울여요:
- 맵에 키가 없으면 빈 문자열이 반환돼요. 숫자의 경우 적절한 값으로 매핑해야 해요. 이는 조건부 함수로 달성할 수 있어요. 예:
if(LogAttributes['status'] = ", 200, LogAttributes['status'])또는 기본값이 허용되면 캐스트 함수 예:toUInt8OrDefault(LogAttributes['status'] ) - 일부 타입은 항상 캐스팅되지는 않아요. 예를 들어 숫자의 문자열 표현은 enum 값으로 캐스팅되지 않아요.
- JSON 추출 함수는 값을 찾지 못하면 해당 타입의 기본값을 반환해요. 이 값들이 말이 되는지 확인해 주세요!
Nullable 피하기 옵저버빌리티 데이터에 ClickHouse에서 Nullable 사용을 피해 주세요. 로그와 트레이스에서 빈 값과 null을 구별할 필요는 거의 없어요. 이 기능은 추가 저장 오버헤드를 발생시키고 쿼리 성능에 부정적 영향을 미쳐요. 자세한 내용은 여기를 참고하세요.
기본(정렬) 키 선택
원하는 컬럼을 추출했다면 정렬/기본 키 최적화를 시작할 수 있어요. 정렬 키 선택을 돕기 위한 몇 가지 간단한 규칙을 적용할 수 있어요. 다음은 때로 충돌할 수 있으므로 순서대로 고려해 주세요. 이 과정에서 여러 키를 식별할 수 있고, 4~5개면 보통 충분해요:
- 일반적인 필터와 접근 패턴에 맞는 컬럼을 선택해요. 보통 pod 이름 같은 특정 컬럼으로 옵저버빌리티 조사를 시작한다면, 이 컬럼은
WHERE절에서 자주 사용될 거예요. 덜 자주 사용되는 컬럼보다 키에 이 컬럼을 포함하는 것을 우선시해요. - 필터링할 때 전체 행의 큰 비율을 제외하는 데 도움이 되는 컬럼을 선호해요. 이렇게 하면 읽어야 할 데이터 양이 줄어들어요. 서비스 이름과 상태 코드가 좋은 후보인 경우가 많아요 — 후자의 경우 대부분의 행을 제외하는 값으로 필터링할 때만 그렇습니다. 예를 들어 200으로 필터링하면 대부분 시스템에서 대부분의 행과 일치하는 반면, 500 오류는 작은 부분집합에 해당해요.
- 테이블의 다른 컬럼과 상관성이 높을 가능성이 있는 컬럼을 선호해요. 이렇게 하면 이 값들도 연속적으로 저장되어 압축이 개선돼요.
- 정렬 키에 있는 컬럼에 대한
GROUP BY및ORDER BY연산은 메모리 효율적으로 만들 수 있어요.
정렬 키의 컬럼 부분집합을 식별하면 특정 순서로 선언해야 해요. 이 순서는 쿼리에서 보조 키 컬럼 필터링의 효율성과 테이블 데이터 파일의 압축 비율 모두에 크게 영향을 미칠 수 있어요. 일반적으로 카디널리티가 오름차순이 되도록 키를 정렬하는 것이 가장 좋아요. 이는 정렬 키에서 뒤에 나타나는 컬럼 필터링이 튜플에서 앞에 나타나는 컬럼 필터링보다 덜 효율적이라는 사실과 균형을 맞춰야 해요. 이 동작을 균형 있게 맞추고 접근 패턴을 고려해요. 가장 중요하게는 변형을 테스트해요. 정렬 키에 대한 더 깊은 이해와 최적화 방법은 이 문서를 권장해요.
먼저 구조화 로그를 구조화한 다음 정렬 키를 결정하는 것을 권장해요. 정렬 키에 속성 맵의 키나 JSON 추출 표현식을 사용하지 마세요. 정렬 키가 테이블의 루트 컬럼으로 있는지 확인해 주세요.
Maps 사용
앞선 예시는 Map(String, String) 컬럼의 값에 접근하는 맵 구문 map['key']의 사용을 보여줘요. 중첩 키에 접근하기 위해 맵 표기법을 사용하는 것 외에도, 이 컬럼들을 필터링하거나 선택하는 전용 ClickHouse 맵 함수가 있어요. 예를 들어 다음 쿼리는 mapKeys 함수와 이어지는 groupArrayDistinctArray 함수(컴비네이터)를 사용해 LogAttributes 컬럼에서 사용 가능한 고유 키를 모두 식별해요.
SELECT groupArrayDistinctArray(mapKeys(LogAttributes))
FROM otel_logs
FORMAT Vertical
Row 1:
──────
groupArrayDistinctArray(mapKeys(LogAttributes)): ['remote_user','run_time','request_type','log.file.name','referer','request_path','status','user_agent','remote_addr','time_local','size','request_protocol']
점 피하기 Map 컬럼 이름에 점(dot)을 사용하는 것은 권장하지 않으며 사용이 폐기될 수 있어요. _를 사용하세요.
별칭 사용 (Using aliases)
맵 타입을 조회하는 것은 일반 컬럼을 조회하는 것보다 느려요 — "쿼리 가속화"를 참고하세요. 또한 구문상 더 복잡하고 작성하기 번거로울 수 있어요. 이 후자 문제를 해결하기 위해 Alias 컬럼을 사용하는 것을 권장해요. ALIAS 컬럼은 쿼리 시점에 계산되며 테이블에 저장되지 않아요. 따라서 이 타입의 컬럼에 값을 INSERT하는 것은 불가능해요. 별칭을 사용하면 맵 키를 참조하고 구문을 단순화하며, 맵 항목을 일반 컬럼으로 투명하게 노출할 수 있어요. 다음 예시를 고려해요:
CREATE TABLE otel_logs
(
`Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
`TraceId` String CODEC(ZSTD(1)),
`SpanId` String CODEC(ZSTD(1)),
`TraceFlags` UInt32 CODEC(ZSTD(1)),
`SeverityText` LowCardinality(String) CODEC(ZSTD(1)),
`SeverityNumber` Int32 CODEC(ZSTD(1)),
`ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
`Body` String CODEC(ZSTD(1)),
`ResourceSchemaUrl` String CODEC(ZSTD(1)),
`ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`ScopeSchemaUrl` String CODEC(ZSTD(1)),
`ScopeName` String CODEC(ZSTD(1)),
`ScopeVersion` String CODEC(ZSTD(1)),
`ScopeAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`LogAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`RequestPath` String MATERIALIZED path(LogAttributes['request_path']),
`RequestType` LowCardinality(String) MATERIALIZED LogAttributes['request_type'],
`RefererDomain` String MATERIALIZED domain(LogAttributes['referer']),
`RemoteAddr` IPv4 ALIAS LogAttributes['remote_addr']
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, Timestamp)
맵 LogAttributes에 접근하는 몇 개의 머티어리얼라이즈드 컬럼과 ALIAS 컬럼 RemoteAddr가 있어요. 이제 이 컬럼을 통해 LogAttributes['remote_addr'] 값을 조회할 수 있어 쿼리가 단순해져요:
SELECT RemoteAddr
FROM default.otel_logs
LIMIT 5
┌─RemoteAddr────┐
│ 54.36.149.41 │
│ 31.56.96.51 │
│ 31.56.96.51 │
│ 40.77.167.129 │
│ 91.99.72.15 │
└───────────────┘
5 rows in set. Elapsed: 0.011 sec.
또한 ALTER TABLE 명령으로 ALIAS를 간단히 추가할 수 있어요. 이 컬럼들은 즉시 사용 가능해요:
ALTER TABLE default.otel_logs
(ADD COLUMN `Size` String ALIAS LogAttributes['size'])
SELECT Size
FROM default.otel_logs_v3
LIMIT 5
┌─Size──┐
│ 30577 │
│ 5667 │
│ 5379 │
│ 1696 │
│ 41483 │
└───────┘
5 rows in set. Elapsed: 0.014 sec.
별칭은 기본적으로 제외 기본적으로 SELECT *는 ALIAS 컬럼을 제외해요. 이 동작은 asterisk_include_alias_columns=1을 설정해 비활성화할 수 있어요.
타입 최적화
타입 최적화를 위한 일반 ClickHouse 모범 사례가 ClickHouse 사용 사례에 적용돼요.
코덱 사용
타입 최적화 외에도 ClickHouse 옵저버빌리티 스키마의 압축 최적화를 시도할 때 코덱의 일반 모범 사례를 따를 수 있어요. 일반적으로 ZSTD 코덱이 로깅과 트레이스 데이터셋에 매우 적합하다는 것을 발견할 거예요. 압축 값을 기본값 1에서 올리면 압축이 개선될 수 있어요. 하지만 높은 값은 삽입 시점에 더 큰 CPU 오버헤드를 발생시키므로 테스트해야 해요. 보통 이 값을 올려도 이득은 거의 없어요. 또한 타임스탬프는 압축과 관련해 delta 인코딩의 이점이 있지만, 이 컬럼이 기본/정렬 키에 사용되면 느린 쿼리 성능을 유발하는 것으로 나타났어요. 각각의 압축 대 쿼리 성능 트레이드오프를 평가할 것을 권장해요.
사전 사용 (Using dictionaries)
사전(Dictionaries)은 ClickHouse의 핵심 기능으로, 다양한 내부 및 외부 소스의 데이터를 메모리 내 키-값 표현으로 제공하며 초저지연 조회 쿼리에 최적화돼요. 이는 다양한 시나리오에서 유용한데, 수집 프로세스를 늦추지 않으면서 수집된 데이터를 즉석에서 강화하고 일반적으로 쿼리 성능을 개선하며 특히 JOIN이 혜택을 봐요. JOIN은 옵저버빌리티 사용 사례에서 거의 필요하지 않지만, 사전은 강화 목적으로 — 삽입 시점과 쿼리 시점 모두에서 — 여전히 유용할 수 있어요. 아래에 둘 다 예시를 제공할게요.
JOIN 가속화 사전으로 JOIN을 가속화하는 데 관심이 있는 사람은 자세한 내용을 여기에서 찾을 수 있어요.
삽입 시점 vs 쿼리 시점
사전은 쿼리 시점이나 삽입 시점에 데이터셋을 강화하는 데 사용할 수 있어요. 각 접근 방식에는 각각의 장단점이 있어요. 요약하면:
- 삽입 시점 - 강화 값이 변하지 않고 사전을 채우는 데 사용할 수 있는 외부 소스에 존재한다면 보통 적합해요. 이 경우 삽입 시점에 행을 강화하면 쿼리 시점의 사전 조회를 피할 수 있어요. 이는 삽입 성능과 추가 저장 오버헤드를 대가로 하는데, 강화된 값이 컬럼으로 저장되기 때문이에요.
- 쿼리 시점 - 사전의 값이 자주 변경되면 쿼리 시점 조회가 더 적합한 경우가 많아요. 이는 매핑된 값이 변경될 때 컬럼을 업데이트(그리고 데이터를 다시 쓰는)할 필요를 피할 수 있어요. 이 유연성은 쿼리 시점 조회 비용을 대가로 해요. 이 쿼리 시점 비용은 많은 행에 대해 조회가 필요할 때(예: 필터 절에서 사전 조회 사용) 보통 체감할 수 있어요. 결과 강화, 즉
SELECT에서는 이 오버헤드가 보통 체감되지 않아요.
사전의 기본 사항에 익숙해질 것을 권장해요. 사전은 전용 전문 함수로 값을 검색할 수 있는 인메모리 조회 테이블을 제공해요. 간단한 강화 예시는 사전에 대한 가이드 여기를 참고하세요. 아래에서는 일반적인 옵저버빌리티 강화 작업에 초점을 맞출게요.
IP 사전 사용 (Using IP dictionaries)
IP 주소로 로그와 트레이스에 위도와 경도 값을 지리 강화하는 것은 일반적인 옵저버빌리티 요구 사항이에요. ip_trie 구조화 사전으로 이를 달성할 수 있어요. DB-IP.com이 CC BY 4.0 라이선스 조건 하에 제공하는 공개 DB-IP city-level 데이터셋을 사용해요. 리드미에서 데이터가 다음과 같이 구조화되어 있음을 볼 수 있어요:
| ip_range_start | ip_range_end | country_code | state1 | state2 | city | postcode | latitude | longitude | timezone |
이 구조를 감안해 url() 테이블 함수로 데이터를 잠깐 살펴볼게요:
SELECT *
FROM url('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV', '\n \tip_range_start IPv4, \n \tip_range_end IPv4, \n \tcountry_code Nullable(String), \n \tstate1 Nullable(String), \n \tstate2 Nullable(String), \n \tcity Nullable(String), \n \tpostcode Nullable(String), \n \tlatitude Float64, \n \tlongitude Float64, \n \ttimezone Nullable(String)\n \t')
LIMIT 1
FORMAT Vertical
Row 1:
──────
ip_range_start: 1.0.0.0
ip_range_end: 1.0.0.255
country_code: AU
state1: Queensland
state2: ᴺᵁᴸᴸ
city: South Brisbane
postcode: ᴺᵁᴸᴸ
latitude: -27.4767
longitude: 153.017
timezone: ᴺᵁᴸᴸ
일을 쉽게 하기 위해 URL() 테이블 엔진을 사용해 필드 이름으로 ClickHouse 테이블 객체를 만들고 총 행 수를 확인할게요:
CREATE TABLE geoip_url(
ip_range_start IPv4,
ip_range_end IPv4,
country_code Nullable(String),
state1 Nullable(String),
state2 Nullable(String),
city Nullable(String),
postcode Nullable(String),
latitude Float64,
longitude Float64,
timezone Nullable(String)
) ENGINE=URL('https://raw.githubusercontent.com/sapics/ip-location-db/master/dbip-city/dbip-city-ipv4.csv.gz', 'CSV')
select count() from geoip_url;
┌─count()─┐
│ 3261621 │ -- 3.26 million
└─────────┘
ip_trie 사전은 IP 주소 범위가 CIDR 표기법으로 표현되어야 하므로 ip_range_start와 ip_range_end를 변환해야 해요. 각 범위의 CIDR은 다음 쿼리로 간결하게 계산할 수 있어요:
WITH
bitXor(ip_range_start, ip_range_end) AS xor,
if(xor != 0, ceil(log2(xor)), 0) AS unmatched,
32 - unmatched AS cidr_suffix,
toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) AS cidr_address
SELECT
ip_range_start,
ip_range_end,
concat(toString(cidr_address),'/',toString(cidr_suffix)) AS cidr
FROM
geoip_url
LIMIT 4;
┌─ip_range_start─┬─ip_range_end─┬─cidr───────┐
│ 1.0.0.0 │ 1.0.0.255 │ 1.0.0.0/24 │
│ 1.0.1.0 │ 1.0.3.255 │ 1.0.0.0/22 │
│ 1.0.4.0 │ 1.0.7.255 │ 1.0.4.0/22 │
│ 1.0.8.0 │ 1.0.15.255 │ 1.0.8.0/21 │
└────────────────┴──────────────┴────────────┘
위 쿼리에 많은 것이 담겨 있어요. 관심 있는 분은 이 훌륭한 설명을 읽어보세요. 그렇지 않으면 위가 IP 범위에 대한 CIDR을 계산한다는 것을 받아들이면 돼요.
우리 목적에는 IP 범위, 국가 코드, 좌표만 필요하므로 새 테이블을 만들고 Geo IP 데이터를 삽입할게요:
CREATE TABLE geoip
(
`cidr` String,
`latitude` Float64,
`longitude` Float64,
`country_code` String
)
ENGINE = MergeTree
ORDER BY cidr
INSERT INTO geoip
WITH
bitXor(ip_range_start, ip_range_end) as xor,
if(xor != 0, ceil(log2(xor)), 0) as unmatched,
32 - unmatched as cidr_suffix,
toIPv4(bitAnd(bitNot(pow(2, unmatched) - 1), ip_range_start)::UInt64) as cidr_address
SELECT
concat(toString(cidr_address),'/',toString(cidr_suffix)) as cidr,
latitude,
longitude,
country_code
FROM geoip_url
ClickHouse에서 저지연 IP 조회를 수행하려면 Geo IP 데이터를 메모리에 저장하는 키 -> 속성 매핑에 사전을 활용할 거예요. ClickHouse는 네트워크 프리픽스(CIDR 블록)를 좌표와 국가 코드로 매핑하는 ip_trie 사전 구조를 제공해요. 다음 쿼리는 이 레이아웃과 위 테이블을 소스로 사용하는 사전을 지정해요.
CREATE DICTIONARY ip_trie (
cidr String,
latitude Float64,
longitude Float64,
country_code String
)
primary key cidr
source(clickhouse(table 'geoip'))
layout(ip_trie)
lifetime(3600);
사전에서 행을 선택해 이 데이터셋이 조회에 사용 가능한지 확인할 수 있어요:
SELECT * FROM ip_trie LIMIT 3
┌─cidr───────┬─latitude─┬─longitude─┬─country_code─┐
│ 1.0.0.0/22 │ 26.0998 │ 119.297 │ CN │
│ 1.0.0.0/24 │ -27.4767 │ 153.017 │ AU │
│ 1.0.4.0/22 │ -38.0267 │ 145.301 │ AU │
└────────────┴──────────┴───────────┴──────────────┘
주기적 새로고침 ClickHouse의 사전은 기본 테이블 데이터와 위에서 사용한 lifetime 절에 따라 주기적으로 새로고침돼요. Geo IP 사전을 DB-IP 데이터셋의 최신 변경을 반영해 업데이트하려면, 변환을 적용해 geoip_url 원격 테이블의 데이터를 geoip 테이블에 다시 삽입하면 돼요.
이제 Geo IP 데이터가 ip_trie 사전에 로드되었으므로(편리하게도 ip_trie라고도 이름 지었음) IP 지리 위치에 사용할 수 있어요. 이는 다음과 같이 dictGet() 함수로 달성할 수 있어요:
SELECT dictGet('ip_trie', ('country_code', 'latitude', 'longitude'), CAST('85.242.48.167', 'IPv4')) AS ip_details
┌─ip_details──────────────┐
│ ('PT',38.7944,-9.34284) │
└─────────────────────────┘
1 row in set. Elapsed: 0.003 sec.
여기 검색 속도를 주목하세요. 이를 통해 로그를 강화할 수 있어요. 이 경우 쿼리 시점 강화를 수행하기로 해요. 원래 로그 데이터셋으로 돌아가 위를 사용해 로그를 국가별로 집계할 수 있어요. 다음은 앞선 머티어리얼라이즈드 뷰에서 만들어진 스키마(추출된 RemoteAddress 컬럼이 있음)를 사용한다고 가정해요.
SELECT dictGet('ip_trie', 'country_code', tuple(RemoteAddress)) AS country,
formatReadableQuantity(count()) AS num_requests
FROM default.otel_logs_v2
WHERE country != ''
GROUP BY country
ORDER BY count() DESC
LIMIT 5
┌─country─┬─num_requests────┐
│ IR │ 7.36 million │
│ US │ 1.67 million │
│ AE │ 526.74 thousand │
│ DE │ 159.35 thousand │
│ FR │ 109.82 thousand │
└─────────┴─────────────────┘
5 rows in set. Elapsed: 0.140 sec. Processed 20.73 million rows, 82.92 MB (147.79 million rows/s., 591.16 MB/s.)
Peak memory usage: 1.16 MiB.
IP에서 지리 위치 매핑은 변경될 수 있으므로, 사용자는 종종 현재 같은 주소의 지리 위치가 아니라 요청 당시의 위치를 알고 싶어할 거예요. 이런 이유로 여기서는 인덱스 시점 강화가 선호될 가능성이 높아요. 이는 아래처럼 머티어리얼라이즈드 컬럼이나 머티어리얼라이즈드 뷰의 select에서 수행할 수 있어요:
CREATE TABLE otel_logs_v2
(
`Body` String,
`Timestamp` DateTime,
`ServiceName` LowCardinality(String),
`Status` UInt16,
`RequestProtocol` LowCardinality(String),
`RunTime` UInt32,
`Size` UInt32,
`UserAgent` String,
`Referer` String,
`RemoteUser` String,
`RequestType` LowCardinality(String),
`RequestPath` String,
`RemoteAddress` IPv4,
`RefererDomain` String,
`RequestPage` String,
`SeverityText` LowCardinality(String),
`SeverityNumber` UInt8,
`Country` String MATERIALIZED dictGet('ip_trie', 'country_code', tuple(RemoteAddress)),
`Latitude` Float32 MATERIALIZED dictGet('ip_trie', 'latitude', tuple(RemoteAddress)),
`Longitude` Float32 MATERIALIZED dictGet('ip_trie', 'longitude', tuple(RemoteAddress))
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)
주기적 업데이트 ip 강화 사전이 새 데이터에 따라 주기적으로 업데이트되기를 원할 거예요. 이는 사전의 LIFETIME 절로 달성할 수 있으며, 이는 사전이 기본 테이블에서 주기적으로 다시 로드되게 해요. 기본 테이블을 업데이트하려면 "Refreshable Materialized views"를 참고하세요.
위 국가와 좌표는 국가별 그룹화와 필터링 너머의 시각화 능력을 제공해요. 영감을 얻으려면 "지리 데이터 시각화"를 참고하세요.
정규식 사전 사용 (사용자 에이전트 파싱)
사용자 에이전트 문자열 파싱은 고전적인 정규식 문제이며 로그와 트레이스 기반 데이터셋의 일반적인 요구 사항이에요. ClickHouse는 Regular Expression Tree Dictionaries를 사용해 사용자 에이전트 파싱을 효율적으로 제공해요. 정규식 트리 사전은 ClickHouse 오픈소스에서 정규식 트리를 포함하는 YAML 파일의 경로를 제공하는 YAMLRegExpTree 사전 소스 타입으로 정의돼요. 자체 정규식 사전을 제공하려면 필요한 구조의 세부 사항을 여기에서 찾을 수 있어요. 아래에서는 uap-core를 사용한 사용자 에이전트 파싱에 초점을 맞추고 지원되는 CSV 포맷으로 사전을 로드해요. 이 접근 방식은 OSS와 ClickHouse Cloud와 호환돼요.
아래 예시에서는 2024년 6월의 사용자 에이전트 파싱용 최신 uap-core 정규식 스냅샷을 사용해요. 가끔 업데이트되는 최신 파일은 여기에서 찾을 수 있어요. 여기의 단계를 따라 아래에서 사용하는 CSV 파일로 로드할 수 있어요.
다음 Memory 테이블들을 만들어요. 이들은 장치, 브라우저, 운영체제 파싱용 정규식을 보유해요.
CREATE TABLE regexp_os
(
id UInt64,
parent_id UInt64,
regexp String,
keys Array(String),
values Array(String)
) ENGINE=Memory;
CREATE TABLE regexp_browser
(
id UInt64,
parent_id UInt64,
regexp String,
keys Array(String),
values Array(String)
) ENGINE=Memory;
CREATE TABLE regexp_device
(
id UInt64,
parent_id UInt64,
regexp String,
keys Array(String),
values Array(String)
) ENGINE=Memory;
이 테이블들은 url 테이블 함수를 사용해 다음 공개 호스팅 CSV 파일들로 채울 수 있어요:
INSERT INTO regexp_os SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_os.csv', NOSIGN, 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')
INSERT INTO regexp_device SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_device.csv', NOSIGN, 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')
INSERT INTO regexp_browser SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/user_agent_regex/regexp_browser.csv', NOSIGN, 'CSV', 'id UInt64, parent_id UInt64, regexp String, keys Array(String), values Array(String)')
Memory 테이블이 채워지면 Regular Expression 사전을 로드할 수 있어요. 키 값을 컬럼으로 지정해야 하며, 이것들이 사용자 에이전트에서 추출할 수 있는 속성이 돼요.
CREATE DICTIONARY regexp_os_dict
(
regexp String,
os_replacement String default 'Other',
os_v1_replacement String default '0',
os_v2_replacement String default '0',
os_v3_replacement String default '0',
os_v4_replacement String default '0'
)
PRIMARY KEY regexp
SOURCE(CLICKHOUSE(TABLE 'regexp_os'))
LIFETIME(MIN 0 MAX 0)
LAYOUT(REGEXP_TREE);
CREATE DICTIONARY regexp_device_dict
(
regexp String,
device_replacement String default 'Other',
brand_replacement String,
model_replacement String
)
PRIMARY KEY(regexp)
SOURCE(CLICKHOUSE(TABLE 'regexp_device'))
LIFETIME(0)
LAYOUT(regexp_tree);
CREATE DICTIONARY regexp_browser_dict
(
regexp String,
family_replacement String default 'Other',
v1_replacement String default '0',
v2_replacement String default '0'
)
PRIMARY KEY(regexp)
SOURCE(CLICKHOUSE(TABLE 'regexp_browser'))
LIFETIME(0)
LAYOUT(regexp_tree);
이 사전들이 로드되면 샘플 사용자 에이전트를 제공하고 새 사전 추출 능력을 테스트할 수 있어요:
WITH 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:127.0) Gecko/20100101 Firefox/127.0' AS user_agent
SELECT
dictGet('regexp_device_dict', ('device_replacement', 'brand_replacement', 'model_replacement'), user_agent) AS device,
dictGet('regexp_browser_dict', ('family_replacement', 'v1_replacement', 'v2_replacement'), user_agent) AS browser,
dictGet('regexp_os_dict', ('os_replacement', 'os_v1_replacement', 'os_v2_replacement', 'os_v3_replacement'), user_agent) AS os
┌─device────────────────┬─browser───────────────┬─os─────────────────────────┐
│ ('Mac','Apple','Mac') │ ('Firefox','127','0') │ ('Mac OS X','10','15','0') │
└───────────────────────┴───────────────────────┴────────────────────────────┘
사용자 에이전트와 관련된 규칙은 거의 변하지 않고, 사전은 새 브라우저, 운영체제, 장치에 대응해 업데이트되어야만 하므로, 이 추출을 삽입 시점에 수행하는 것이 합리적이에요. 머티어리얼라이즈드 컬럼이나 머티어리얼라이즈드 뷰로 이 작업을 수행할 수 있어요. 아래에서 앞서 사용한 머티어리얼라이즈드 뷰를 수정할게요:
CREATE MATERIALIZED VIEW otel_logs_mv TO otel_logs_v2
AS SELECT
Body,
CAST(Timestamp, 'DateTime') AS Timestamp,
ServiceName,
LogAttributes['status'] AS Status,
LogAttributes['request_protocol'] AS RequestProtocol,
LogAttributes['run_time'] AS RunTime,
LogAttributes['size'] AS Size,
LogAttributes['user_agent'] AS UserAgent,
LogAttributes['referer'] AS Referer,
LogAttributes['remote_user'] AS RemoteUser,
LogAttributes['request_type'] AS RequestType,
LogAttributes['request_path'] AS RequestPath,
LogAttributes['remote_addr'] AS RemoteAddress,
domain(LogAttributes['referer']) AS RefererDomain,
path(LogAttributes['request_path']) AS RequestPage,
multiIf(CAST(Status, 'UInt64') > 500, 'CRITICAL', CAST(Status, 'UInt64') > 400, 'ERROR', CAST(Status, 'UInt64') > 300, 'WARNING', 'INFO') AS SeverityText,
multiIf(CAST(Status, 'UInt64') > 500, 20, CAST(Status, 'UInt64') > 400, 17, CAST(Status, 'UInt64') > 300, 13, 9) AS SeverityNumber,
dictGet('regexp_device_dict', ('device_replacement', 'brand_replacement', 'model_replacement'), UserAgent) AS Device,
dictGet('regexp_browser_dict', ('family_replacement', 'v1_replacement', 'v2_replacement'), UserAgent) AS Browser,
dictGet('regexp_os_dict', ('os_replacement', 'os_v1_replacement', 'os_v2_replacement', 'os_v3_replacement'), UserAgent) AS Os
FROM otel_logs
이렇게 하려면 대상 테이블 otel_logs_v2의 스키마를 수정해야 해요:
CREATE TABLE default.otel_logs_v2
(
`Body` String,
`Timestamp` DateTime,
`ServiceName` LowCardinality(String),
`Status` UInt8,
`RequestProtocol` LowCardinality(String),
`RunTime` UInt32,
`Size` UInt32,
`UserAgent` String,
`Referer` String,
`RemoteUser` String,
`RequestType` LowCardinality(String),
`RequestPath` String,
`remote_addr` IPv4,
`RefererDomain` String,
`RequestPage` String,
`SeverityText` LowCardinality(String),
`SeverityNumber` UInt8,
`Device` Tuple(device_replacement LowCardinality(String), brand_replacement LowCardinality(String), model_replacement LowCardinality(String)),
`Browser` Tuple(family_replacement LowCardinality(String), v1_replacement LowCardinality(String), v2_replacement LowCardinality(String)),
`Os` Tuple(os_replacement LowCardinality(String), os_v1_replacement LowCardinality(String), os_v2_replacement LowCardinality(String), os_v3_replacement LowCardinality(String))
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp, Status)
collector를 재시작하고 앞서 문서화한 단계에 따라 구조화 로그를 수집한 뒤, 새로 추출한 Device, Browser, Os 컬럼을 조회할 수 있어요.
SELECT Device, Browser, Os
FROM otel_logs_v2
LIMIT 1
FORMAT Vertical
Row 1:
──────
Device: ('Spider','Spider','Desktop')
Browser: ('AhrefsBot','6','1')
Os: ('Other','0','0','0')
복잡한 구조에는 튜플 사용자 에이전트 컬럼에 Tuples를 사용한 것을 주목하세요. 계층이 사전에 알려진 복잡한 구조에는 Tuples가 권장돼요. 하위 컬럼은(Map 키와 달리) 이질적인 타입을 허용하면서 일반 컬럼과 동일한 성능을 제공해요.
추가 읽기
사전에 대한 더 많은 예시와 세부 사항은 다음 문서를 권장해요:
쿼리 가속화
ClickHouse는 쿼리 성능 가속화를 위한 여러 기법을 지원해요. 다음은 가장 인기 있는 접근 패턴을 최적화하고 압축을 극대화하기 위해 적절한 기본/정렬 키를 선택한 후에만 고려해야 해요. 이것이 보통 적은 노력으로 성능에 가장 큰 영향을 미쳐요.
집계를 위한 머티어리얼라이즈드 뷰(증분) 사용
앞선 섹션에서 데이터 변환과 필터링에 머티어리얼라이즈드 뷰를 사용하는 것을 살펴봤어요. 하지만 머티어리얼라이즈드 뷰는 삽입 시점에 집계를 미리 계산하고 그 결과를 저장하는 데도 사용할 수 있어요. 이 결과는 후속 삽입의 결과로 업데이트될 수 있어, 집계가 삽입 시점에 효과적으로 미리 계산될 수 있게 해줘요. 여기서 핵심 아이디어는 결과가 보통 원본 데이터의 더 작은 표현(집계의 경우 부분 스케치)이라는 점이에요. 대상 테이블에서 결과를 읽는 더 간단한 쿼리와 결합하면, 원본 데이터에 같은 계산을 수행하는 것보다 쿼리 시간이 더 빨라요. 구조화 로그를 사용해 시간당 총 트래픽을 계산하는 다음 쿼리를 고려해요:
SELECT toStartOfHour(Timestamp) AS Hour,
sum(toUInt64OrDefault(LogAttributes['size'])) AS TotalBytes
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC
LIMIT 5
┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘
이것은 사용자가 Grafana로 그리는 일반적인 라인 차트일 수 있다고 상상할 수 있어요. 이 쿼리는 확실히 매우 빠르나 — 데이터셋이 1000만 행뿐이고 ClickHouse가 빠르니까요! 하지만 이것을 수십억, 수조 행으로 확장한다면 이 쿼리 성능을 유지하고 싶을 거예요.
이 쿼리는 앞선 머티어리얼라이즈드 뷰에서 나온 otel_logs_v2 테이블을 사용하면 10배 빨라질 거예요. 이 테이블은 LogAttributes 맵에서 size 키를 추출해요. 여기서는 설명 목적으로만 원시 데이터를 사용해요. 이게 일반적인 쿼리라면 앞선 뷰를 사용하는 것을 권장할게요.
머티어리얼라이즈드 뷰로 이를 삽입 시점에 계산하려면 결과를 받을 테이블이 필요해요. 이 테이블은 시간당 한 행만 유지해야 해요. 기존 시간에 대한 업데이트가 수신되면 다른 컬럼을 기존 시간의 행에 병합해야 해요. 이 증분 상태 병합이 일어나려면 다른 컬럼에 대한 부분 상태가 저장되어야 해요. 이는 ClickHouse의 특수 엔진 타입인 SummingMergeTree를 요구해요. 이것은 같은 정렬 키를 가진 모든 행을, 숫자 컬럼의 합산 값을 포함하는 한 행으로 대체해요. 다음 테이블은 같은 날짜를 가진 모든 행을 병합해 숫자 컬럼을 합산해요.
CREATE TABLE bytes_per_hour
(
`Hour` DateTime,
`TotalBytes` UInt64
)
ENGINE = SummingMergeTree
ORDER BY Hour
머티어리얼라이즈드 뷰를 보여주기 위해 bytes_per_hour 테이블이 비어 있고 아직 데이터를 받지 않았다고 가정해요. 우리의 머티어리얼라이즈드 뷰는 otel_logs에 삽입된 데이터에 대해 위 SELECT를(구성된 크기의 블록에 대해) 수행하고 결과를 bytes_per_hour로 보내요. 구문은 아래와 같아요:
CREATE MATERIALIZED VIEW bytes_per_hour_mv TO bytes_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
sum(toUInt64OrDefault(LogAttributes['size'])) AS TotalBytes
FROM otel_logs
GROUP BY Hour
여기 TO 절이 핵심이에요. 결과가 어디로 보내질지, 즉 bytes_per_hour를 나타내요. OTel Collector를 재시작하고 로그를 다시 보내면 bytes_per_hour 테이블이 위 쿼리 결과로 증분 채워질 거예요. 완료되면 bytes_per_hour의 크기를 확인할 수 있어요 — 시간당 한 행이 있어야 해요:
SELECT count()
FROM bytes_per_hour
FINAL
┌─count()─┐
│ 113 │
└─────────┘
여기서 행 수를(otel_logs의) 1000만에서 113으로 줄였어요. 쿼리 결과를 저장했기 때문이에요. 핵심은 otel_logs 테이블에 새 로그가 삽입되면 해당 시간에 새 값이 bytes_per_hour로 전송되고, 백그라운드에서 비동기적으로 자동 병합된다는 거예요 — 시간당 한 행만 유지함으로써 bytes_per_hour는 항상 작고 최신 상태가 돼요. 행 병합이 비동기이므로 사용자가 조회할 때 시간당 행이 하나 이상 있을 수 있어요. 쿼리 시점에 보류 중인 행이 병합되도록 하려면 두 가지 옵션이 있어요:
- 테이블 이름에
FINAL한정자 사용(위 count 쿼리에서 했던 것처럼). - 최종 테이블에서 사용한 정렬 키(즉 Timestamp)로 집계하고 메트릭을 합산.
보통 두 번째 옵션이 더 효율적이고 유연해요(테이블을 다른 용도로 사용할 수 있음), 하지만 첫 번째가 일부 쿼리에 더 단순할 수 있어요. 둘 다 아래에서 보여줄게요:
SELECT
Hour,
sum(TotalBytes) AS TotalBytes
FROM bytes_per_hour
GROUP BY Hour
ORDER BY Hour DESC
LIMIT 5
┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘
SELECT
Hour,
TotalBytes
FROM bytes_per_hour
FINAL
ORDER BY Hour DESC
LIMIT 5
┌────────────────Hour─┬─TotalBytes─┐
│ 2019-01-26 16:00:00 │ 1661716343 │
│ 2019-01-26 15:00:00 │ 1824015281 │
│ 2019-01-26 14:00:00 │ 1506284139 │
│ 2019-01-26 13:00:00 │ 1580955392 │
│ 2019-01-26 12:00:00 │ 1736840933 │
└─────────────────────┴────────────┘
이로 인해 쿼리가 0.6초에서 0.008초로 빨라졌어요 — 75배 이상!
이 절약은 더 큰 데이터셋과 더 복잡한 쿼리에서 훨씬 클 수 있어요. 예시는 여기를 참고하세요.
더 복잡한 예시
위 예시는 SummingMergeTree로 시간당 간단한 수를 집계해요. 단순한 합을 넘어서는 통계는 다른 대상 테이블 엔진인 AggregatingMergeTree가 필요해요. 일별 고유 IP 주소(또는 고유 사용자) 수를 계산하고 싶다고 가정해요. 이에 대한 쿼리:
SELECT toStartOfHour(Timestamp) AS Hour, uniq(LogAttributes['remote_addr']) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC
┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │ 4763 │
│ 2019-01-22 00:00:00 │ 536 │
└─────────────────────┴─────────────┘
113 rows in set. Elapsed: 0.667 sec. Processed 10.37 million rows, 4.73 GB (15.53 million rows/s., 7.09 GB/s.)
증분 업데이트용 카디널리티 수를 영속화하려면 AggregatingMergeTree가 필요해요.
CREATE TABLE unique_visitors_per_hour
(
`Hour` DateTime,
`UniqueUsers` AggregateFunction(uniq, IPv4)
)
ENGINE = AggregatingMergeTree
ORDER BY Hour
ClickHouse가 집계 상태가 저장될 것임을 알게 하려면 UniqueUsers 컬럼을 AggregateFunction 타입으로 정의하고, 부분 상태의 함수 소스(uniq)와 소스 컬럼 타입(IPv4)을 지정해요. SummingMergeTree와 마찬가지로 같은 ORDER BY 키 값을 가진 행은 병합돼요(위 예시에서는 Hour). 관련 머티어리얼라이즈드 뷰는 앞선 쿼리를 사용해요:
CREATE MATERIALIZED VIEW unique_visitors_per_hour_mv TO unique_visitors_per_hour AS
SELECT toStartOfHour(Timestamp) AS Hour,
uniqState(LogAttributes['remote_addr']::IPv4) AS UniqueUsers
FROM otel_logs
GROUP BY Hour
ORDER BY Hour DESC
집계 함수 끝에 State 접미사를 붙인 것을 주목하세요. 이는 최종 결과 대신 함수의 집계 상태가 반환되도록 보장해요. 이는 이 부분 상태가 다른 상태와 병합되도록 하는 추가 정보를 포함할 거예요. Collector 재시작을 통해 데이터가 다시 로드되면 unique_visitors_per_hour 테이블에 113행이 있음을 확인할 수 있어요.
SELECT count()
FROM unique_visitors_per_hour
FINAL
┌─count()─┐
│ 113 │
└─────────┘
최종 쿼리는(컬럼이 부분 집계 상태를 저장하므로) 함수에 Merge 접미사를 활용해야 해요:
SELECT Hour, uniqMerge(UniqueUsers) AS UniqueUsers
FROM unique_visitors_per_hour
GROUP BY Hour
ORDER BY Hour DESC
┌────────────────Hour─┬─UniqueUsers─┐
│ 2019-01-26 16:00:00 │ 4763 │
│ 2019-01-22 00:00:00 │ 536 │
└─────────────────────┴─────────────┘
여기서 FINAL 대신 GROUP BY를 사용한다는 점을 주목하세요.
빠른 조회를 위한 머티어리얼라이즈드 뷰(증분) 사용
ClickHouse 정렬 키를 선택할 때 필터와 집계 절에서 자주 사용되는 컬럼을 가진 접근 패턴을 고려해야 해요. 이는 사용자가 단일 컬럼 집합으로 캡슐화할 수 없는 더 다양한 접근 패턴을 가진 옵저버빌리티 사용 사례에서 제한적일 수 있어요. 이는 기본 OTel 스키마에 내장된 예시로 가장 잘 설명돼요. 트레이스의 기본 스키마를 고려해요:
CREATE TABLE otel_traces
(
`Timestamp` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
`TraceId` String CODEC(ZSTD(1)),
`SpanId` String CODEC(ZSTD(1)),
`ParentSpanId` String CODEC(ZSTD(1)),
`TraceState` String CODEC(ZSTD(1)),
`SpanName` LowCardinality(String) CODEC(ZSTD(1)),
`SpanKind` LowCardinality(String) CODEC(ZSTD(1)),
`ServiceName` LowCardinality(String) CODEC(ZSTD(1)),
`ResourceAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`ScopeName` String CODEC(ZSTD(1)),
`ScopeVersion` String CODEC(ZSTD(1)),
`SpanAttributes` Map(LowCardinality(String), String) CODEC(ZSTD(1)),
`Duration` Int64 CODEC(ZSTD(1)),
`StatusCode` LowCardinality(String) CODEC(ZSTD(1)),
`StatusMessage` String CODEC(ZSTD(1)),
`Events.Timestamp` Array(DateTime64(9)) CODEC(ZSTD(1)),
`Events.Name` Array(LowCardinality(String)) CODEC(ZSTD(1)),
`Events.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
`Links.TraceId` Array(String) CODEC(ZSTD(1)),
`Links.SpanId` Array(String) CODEC(ZSTD(1)),
`Links.TraceState` Array(String) CODEC(ZSTD(1)),
`Links.Attributes` Array(Map(LowCardinality(String), String)) CODEC(ZSTD(1)),
INDEX idx_trace_id TraceId TYPE bloom_filter(0.001) GRANULARITY 1,
INDEX idx_res_attr_key mapKeys(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
INDEX idx_res_attr_value mapValues(ResourceAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
INDEX idx_span_attr_key mapKeys(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
INDEX idx_span_attr_value mapValues(SpanAttributes) TYPE bloom_filter(0.01) GRANULARITY 1,
INDEX idx_duration Duration TYPE minmax GRANULARITY 1
)
ENGINE = MergeTree
PARTITION BY toDate(Timestamp)
ORDER BY (ServiceName, SpanName, toUnixTimestamp(Timestamp), TraceId)
이 스키마는 ServiceName, SpanName, Timestamp 필터링에 최적화되어 있어요. 트레이싱에서 사용자는 특정 TraceId로 조회하고 관련 트레이스의 스팬을 검색하는 능력도 필요해요. 이것이 정렬 키에 있지만 끝에 위치해 필터링이 그리 효율적이지 않을 것이며, 단일 트레이스를 검색할 때 상당한 양의 데이터를 스캔해야 할 가능성이 높아요. OTel collector는 이 문제를 해결하기 위해 머티어리얼라이즈드 뷰와 관련 테이블도 설치해요. 테이블과 뷰는 아래와 같아요:
CREATE TABLE otel_traces_trace_id_ts
(
`TraceId` String CODEC(ZSTD(1)),
`Start` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
`End` DateTime64(9) CODEC(Delta(8), ZSTD(1)),
INDEX idx_trace_id TraceId TYPE bloom_filter(0.01) GRANULARITY 1
)
ENGINE = MergeTree
ORDER BY (TraceId, toUnixTimestamp(Start))
CREATE MATERIALIZED VIEW otel_traces_trace_id_ts_mv TO otel_traces_trace_id_ts
(
`TraceId` String,
`Start` DateTime64(9),
`End` DateTime64(9)
)
AS SELECT
TraceId,
min(Timestamp) AS Start,
max(Timestamp) AS End
FROM otel_traces
WHERE TraceId != ''
GROUP BY TraceId
이 뷰는 otel_traces_trace_id_ts 테이블이 트레이스의 최소 및 최대 타임스탬프를 갖도록 효과적으로 보장해요. TraceId로 정렬된 이 테이블은 이 타임스탬프를 효율적으로 검색할 수 있게 해줘요. 이 타임스탬프 범위는 차례로 메인 otel_traces 테이블을 조회할 때 사용할 수 있어요. 구체적으로 id로 트레이스를 검색할 때 Grafana는 다음 쿼리를 사용해요:
WITH 'ae9226c78d1d360601e6383928e4d22d' AS trace_id,
(
SELECT min(Start)
FROM default.otel_traces_trace_id_ts
WHERE TraceId = trace_id
) AS trace_start,
(
SELECT max(End) + 1
FROM default.otel_traces_trace_id_ts
WHERE TraceId = trace_id
) AS trace_end
SELECT
TraceId AS traceID,
SpanId AS spanID,
ParentSpanId AS parentSpanID,
ServiceName AS serviceName,
SpanName AS operationName,
Timestamp AS startTime,
Duration * 0.000001 AS duration,
arrayMap(key -> map('key', key, 'value', SpanAttributes[key]), mapKeys(SpanAttributes)) AS tags,
arrayMap(key -> map('key', key, 'value', ResourceAttributes[key]), mapKeys(ResourceAttributes)) AS serviceTags
FROM otel_traces
WHERE (traceID = trace_id) AND (startTime >= trace_start) AND (startTime <= trace_end)
LIMIT 1000
여기 CTE는 트레이스 id ae9226c78d1d360601e6383928e4d22d의 최소 및 최대 타임스탬프를 식별하고, 이를 사용해 관련 스팬에 대한 메인 otel_traces를 필터링해요. 이 같은 접근 방식은 유사한 접근 패턴에 적용할 수 있어요. 유사한 예시를 데이터 모델링 여기에서 살펴봐요.
프로젝션 사용
ClickHouse 프로젝션은 테이블에 대해 여러 ORDER BY 절을 지정할 수 있게 해줘요. 앞선 섹션에서 머티어리얼라이즈드 뷰가 ClickHouse에서 집계를 미리 계산하고, 행을 변환하며, 다른 접근 패턴에 대해 옵저버빌리티 쿼리를 최적화하는 데 어떻게 사용되는지 살펴봤어요. 트레이스 id로 조회를 최적화하기 위해 원본 테이블과 다른 정렬 키를 가진 대상 테이블로 머티어리얼라이즈드 뷰가 행을 보내는 예시를 제공했어요. 프로젝션은 같은 문제를 해결하는 데 사용할 수 있고, 기본 키의 일부가 아닌 컬럼에 대한 쿼리를 최적화할 수 있게 해줘요. 이론적으로 이 기능은 테이블에 여러 정렬 키를 제공하는 데 사용할 수 있지만, 한 가지 뚜렷한 단점이 있어요: 데이터 중복. 구체적으로 데이터를 각 프로젝션에 지정된 순서에 더해 메인 기본 키 순서로도 써야 해요. 이는 삽입을 느리게 하고 더 많은 디스크 공간을 소비해요.
프로젝션 vs 머티어리얼라이즈드 뷰 프로젝션은 머티어리얼라이즈드 뷰와 많은 동일한 기능을 제공하지만, 후자가 자주 선호되므로 절약해서 사용해야 해요. 단점과 언제 적절한지 이해해야 해요. 예를 들어 프로젝션으로 집계를 미리 계산할 수 있지만 이 용도로는 머티어리얼라이즈드 뷰를 사용할 것을 권장해요.
otel_logs_v2 테이블을 500 오류 코드로 필터링하는 다음 쿼리를 고려해요. 이는 오류 코드로 필터링하려는 사용자에게 로깅의 일반적인 접근 패턴일 가능성이 높아요:
SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
FROM otel_logs_v2
WHERE Status = 500
FORMAT `Null`
Ok.
0 rows in set. Elapsed: 0.177 sec. Processed 10.37 million rows, 685.32 MB (58.66 million rows/s., 3.88 GB/s.)
Peak memory usage: 56.54 MiB.
성능 측정에 Null 사용 여기서 FORMAT Null로 결과를 출력하지 않아요. 이는 모든 결과가 읽히지만 반환되지 않도록 강제해서, LIMIT으로 인한 쿼리 조기 종료를 방지해요. 이는 1000만 행 전체를 스캔하는 데 걸리는 시간을 보여주기 위한 것이에요.
위 쿼리는 선택한 정렬 키 (ServiceName, Timestamp)로 선형 스캔을 요구해요. 정렬 키 끝에 Status를 추가해 위 쿼리의 성능을 개선할 수 있지만, 프로젝션을 추가할 수도 있어요.
ALTER TABLE otel_logs_v2 (
ADD PROJECTION status
(
SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent ORDER BY Status
)
)
ALTER TABLE otel_logs_v2 MATERIALIZE PROJECTION status
먼저 프로젝션을 만들고 머티어리얼라이즈해야 한다는 점에 유의하세요. 후자 명령은 데이터가 두 가지 다른 순서로 디스크에 두 번 저장되게 해요. 프로젝션은 아래처럼 데이터가 생성될 때 정의할 수도 있고, 데이터가 삽입됨에 따라 자동으로 유지돼요.
CREATE TABLE otel_logs_v2
(
`Body` String,
`Timestamp` DateTime,
`ServiceName` LowCardinality(String),
`Status` UInt16,
`RequestProtocol` LowCardinality(String),
`RunTime` UInt32,
`Size` UInt32,
`UserAgent` String,
`Referer` String,
`RemoteUser` String,
`RequestType` LowCardinality(String),
`RequestPath` String,
`RemoteAddress` IPv4,
`RefererDomain` String,
`RequestPage` String,
`SeverityText` LowCardinality(String),
`SeverityNumber` UInt8,
PROJECTION status
(
SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
ORDER BY Status
)
)
ENGINE = MergeTree
ORDER BY (ServiceName, Timestamp)
중요하게, 프로젝션이 ALTER로 생성되면 MATERIALIZE PROJECTION 명령이 발행될 때 그 생성이 비동기예요. 다음 쿼리로 이 작업의 진행 상황을 확인하고 is_done=1을 기다릴 수 있어요.
SELECT parts_to_do, is_done, latest_fail_reason
FROM system.mutations
WHERE (`table` = 'otel_logs_v2') AND (command LIKE '%MATERIALIZE%')
┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
│ 0 │ 1 │ │
└─────────────┴─────────┴────────────────────┘
위 쿼리를 반복하면 성능이 추가 저장(측정 방법은 "테이블 크기 & 압축 측정" 참고)의 대가로 크게 개선되었음을 볼 수 있어요.
SELECT Timestamp, RequestPath, Status, RemoteAddress, UserAgent
FROM otel_logs_v2
WHERE Status = 500
FORMAT `Null`
0 rows in set. Elapsed: 0.031 sec. Processed 51.42 thousand rows, 22.85 MB (1.65 million rows/s., 734.63 MB/s.)
Peak memory usage: 27.85 MiB.
위 예시에서는 프로젝션에 앞선 쿼리에서 사용한 컬럼을 지정해요. 이는 이 지정된 컬럼들만 프로젝션의 일부로 Status로 정렬되어 디스크에 저장된다는 뜻이에요. 대신 여기서 SELECT *를 사용했다면 모든 컬럼이 저장될 거예요. 이는 더 많은 쿼리(어떤 컬럼 부분집합이라도 사용)가 프로젝션의 이점을 누릴 수 있게는 하지만, 추가 저장이 발생해요. 디스크 공간과 압축 측정은 "테이블 크기 & 압축 측정"을 참고하세요.
보조/데이터 건너뛰기 인덱스
ClickHouse에서 기본 키가 아무리 잘 튜닝되어 있어도 일부 쿼리는 필연적으로 전체 테이블 스캔을 요구해요. 이는 머티어리얼라이즈드 뷰(일부 쿼리는 프로젝션)로 완화할 수 있지만, 이들은 추가 유지보수와 사용자가 활용되도록 보장하기 위해 가용성을 인지해야 해요. 전통적 관계형 데이터베이스는 보조 인덱스로 이를 해결하지만, 이것들은 ClickHouse 같은 컬럼형 데이터베이스에서는 비효율적이에요. 대신 ClickHouse는 "Skip" 인덱스를 사용하는데, 일치하는 값이 없는 큰 데이터 덩어리를 건너뛸 수 있게 해줘 쿼리 성능을 크게 개선할 수 있어요. 기본 OTel 스키마는 맵 접근을 가속화하기 위해 보조 인덱스를 사용해요. 이것들이 일반적으로 비효율적임을 발견했고 커스텀 스키마에 복사하는 것을 권장하지 않지만, 건너뛰기 인덱스는 여전히 유용할 수 있어요. 적용하기 전에 보조 인덱스 가이드를 읽고 이해해야 해요. 일반적으로 기본 키와 대상이 되는 비기본 컬럼/표현식 사이에 강한 상관관계가 있고 사용자가 희귀 값(즉 많은 granule에 발생하지 않는 값)을 조회할 때 효과적이에요.
전문 검색용 텍스트 인덱스
ClickHouse는 전문 검색을 위한 전문화된 텍스트 인덱스를 제공해요. 이 인덱스는 토큰화된 텍스트 데이터에 대한 역 인덱스를 구축해 빠른 토큰 기반 검색 쿼리를 가능하게 해요. 텍스트 인덱스는 ClickHouse 버전 26.2부터 사용할 수 있어요. MergeTree 테이블의 다음 컬럼 타입에 정의할 수 있어요: String, FixedString, Array(String), Array(FixedString), 그리고 Map(mapKeys와 mapValues 맵 함수를 통해) 컬럼. 텍스트 인덱스는 정의에 tokenizer 인자가 필요해요. 선택적으로 토큰화 전에 입력 문자열을 변환하는 preprocessor 함수를 지정할 수 있어요. 인덱스에서 검색할 권장 함수는 hasAnyTokens와 hasAllTokens예요. 일부 전통적인 문자열 검색 함수도 텍스트 인덱스가 있으면 자동으로 최적화돼요. 자세한 내용과 지원 함수는 여기와 여기의 문서를 참고하세요. 아래 예시에서는 구조화 로그 데이터셋을 사용해요.
CREATE TABLE otel_logs
(
`Body` String,
`Timestamp` DateTime,
`ServiceName` LowCardinality(String),
`Status` UInt16,
`RequestProtocol` LowCardinality(String),
`RunTime` UInt32,
`Size` UInt32,
`UserAgent` String,
`Referer` String,
`RemoteUser` String,
`RequestType` LowCardinality(String),
`RequestPath` String,
`RemoteAddress` IPv4,
`RefererDomain` String,
`RequestPage` String,
`SeverityText` LowCardinality(String),
`SeverityNumber` UInt8
)
ENGINE = MergeTree
ORDER BY Timestamp
SETTINGS index_granularity = 8192
hasAnyTokens를 텍스트 인덱스 없이도 사용할 수 있지만, 쿼리는 Body 컬럼의 느린 전체 스캔을 수행할 거예요:
SELECT count()
FROM otel_logs
WHERE hasAllTokens(Body, ['Connection', 'accepted'])
┌─count()─┐
1. │ 27281 │
└─────────┘
1 row in set. Elapsed: 0.584 sec. Processed 19.95 million rows, 3.08 GB (34.15 million rows/s., 5.27 GB/s.)
텍스트 인덱스 추가
Body 컬럼에 텍스트 인덱스를 테이블 생성 중에 추가할 수 있어요:
CREATE TABLE otel_logs_index_body
(
`Body` String,
`Timestamp` DateTime,
`ServiceName` LowCardinality(String),
`Status` UInt16,
`RequestProtocol` LowCardinality(String),
`RunTime` UInt32,
`Size` UInt32,
`UserAgent` String,
`Referer` String,
`RemoteUser` String,
`RequestType` LowCardinality(String),
`RequestPath` String,
`RemoteAddress` IPv4,
`RefererDomain` String,
`RequestPage` String,
`SeverityText` LowCardinality(String),
`SeverityNumber` UInt8,
INDEX idx_body Body TYPE text(tokenizer = splitByNonAlpha) GRANULARITY 100000000
)
ENGINE = MergeTree
ORDER BY Timestamp
SETTINGS index_granularity = 8192
또는 나중에 ALTER TABLE로 추가할 수 있어요:
ALTER TABLE otel_logs ADD INDEX idx_body Body TYPE text(tokenizer = splitByNonAlpha) GRANULARITY 100000000;
ALTER TABLE otel_logs MATERIALIZE INDEX idx_body;
같은 SELECT 쿼리를 다시 실행하면 텍스트 인덱스 조회를 수행할 거예요. 접근되는 데이터 양이 기가바이트에서 메가바이트로 줄고 성능이 약 45배 개선돼요.
SELECT count()
FROM otel_logs_index_body
WHERE hasAllTokens(Body, ['Connection', 'accepted'])
┌─count()─┐
1. │ 27281 │
└─────────┘
1 row in set. Elapsed: 0.013 sec. Processed 20.41 million rows, 20.41 MB (1.59 billion rows/s., 1.59 GB/s.)
Peak memory usage: 15.23 MiB.
preprocessor 사용
이 데이터셋에서 Body 컬럼은 여러 키-값 쌍(예: msg, id, ctx, attr 등)이 있는 JSON 형식 문자열을 포함해요. msg 필드 내에서만 검색하는 데 관심이 있다고 가정해요. 전체 JSON 문자열을 인덱싱하는 대신, 토큰화 전에 msg 값만 추출하는 preprocessor를 정의할 수 있어요. 예를 들어:
INDEX idx_text Body TYPE text(tokenizer = splitByNonAlpha,
preprocessor = JSONExtract(Body, 'msg', 'String'))
이 예시에서 preprocessor는:
- 토큰화되고 인덱싱되는 텍스트 양을 줄이고,
- 인덱스 크기를 줄이며,
- 오탐(false positive) 확률을 줄이고,
- 쿼리 성능을 개선해요.
SELECT count()
FROM otel_logs_text_body_preprocessed
WHERE hasAllTokens(Body, ['Connection', 'accepted'])
┌─count()─┐
1. │ 27281 │
└─────────┘
1 row in set. Elapsed: 0.006 sec. Processed 13.54 million rows, 13.54 MB (2.45 billion rows/s., 2.45 GB/s.)
Peak memory usage: 1.95 MiB.
preprocess하지 않은 인덱스에 비해 성능이 약 2배 개선돼요. preprocessor를 사용하면 인덱스 크기도 기가바이트에서 수백 킬로바이트로 줄어, 원본 크기의 약 0.01%가 돼요.
SELECT
`table`,
formatReadableSize(data_compressed_bytes) AS compressed_size,
formatReadableSize(data_uncompressed_bytes) AS uncompressed_size
FROM system.data_skipping_indices
WHERE startsWith(`table`, 'otel_logs')
┌─table───────────────────────────────────┬─compressed_size─┬─uncompressed_size─┐
1. │ otel_logs_text_index_body_preprocessed │ 423.98 KiB │ 424.29 KiB │
2. │ otel_logs_text_index_body │ 2.76 GiB │ 2.78 GiB │
└─────────────────────────────────────────┴─────────────────┴───────────────────┘
텍스트 검색용 다른 인덱스 보조 건너뛰기 인덱스에 대한 자세한 내용은 여기에서 찾을 수 있어요.
맵에서 추출
Map 타입은 OTel 스키마에서 널리 퍼져 있어요. 이 타입은 값과 키가 같은 타입이어야 해요 — Kubernetes 라벨 같은 메타데이터에 충분해요. Map 타입의 하위 키를 조회할 때 전체 부모 컬럼이 로드된다는 점을 인지해야 해요. 맵에 많은 키가 있으면, 키가 컬럼으로 존재할 때보다 디스크에서 더 많은 데이터를 읽어야 하므로 상당한 쿼리 패널티가 발생할 수 있어요. 특정 키를 자주 조회한다면 그 키를 루트의 전용 컬럼으로 옮기는 것을 고려해요. 이는 보통 일반적인 접근 패턴에 대응해 그리고 배포 후에 발생하는 작업이며, 프로덕션 전에 예측하기 어려울 수 있어요. 배포 후 스키마를 수정하는 방법은 "스키마 변경 관리"를 참고하세요.
테이블 크기 & 압축 측정
ClickHouse가 옵저버빌리티에 사용되는 주요 이유 중 하나는 압축이에요. 스토리지 비용을 극적으로 줄이는 것 외에도, 디스크의 데이터가 적다는 것은 I/O가 적고 쿼리와 삽입이 더 빠르다는 뜻이에요. I/O 감소는 CPU에 대한 모든 압축 알고리즘의 오버헤드를 능가할 거예요. 따라서 ClickHouse 쿼리가 빠르도록 하는 것에 집중할 때 데이터 압축 개선이 첫 번째 초점이어야 해요. 압축 측정에 대한 자세한 내용은 여기에서 찾을 수 있어요.