템플릿과 Regex 포맷
템플릿과 Regex 포맷
커스텀 텍스트 포맷의 데이터를 다뤄야 하는 경우가 많아요. 비표준 포맷, 유효하지 않은 JSON, 깨진 CSV 같은 것들이죠. CSVT나 JSON 같은 표준 파서는 그런 모든 경우에 동작하지 않아요. 하지만 ClickHouse에는 강력한 Template와 Regex 포맷이 있어 이런 경우를 해결해 줘요.
출처: 문서
본문
템플릿 기반 가져오기
다음 로그 파일에서 데이터를 가져오고 싶다고 가정해 볼게요.
head error.log
2023/01/15 14:51:17 [error] client: 7.2.8.1, server: example.com "GET /apple-touch-icon-120x120.png HTTP/1.1"
2023/01/16 06:02:09 [error] client: 8.4.2.7, server: example.com "GET /apple-touch-icon-120x120.png HTTP/1.1"
2023/01/15 13:46:13 [error] client: 6.9.3.7, server: example.com "GET /apple-touch-icon.png HTTP/1.1"
2023/01/16 05:34:55 [error] client: 9.9.7.6, server: example.com "GET /h5/static/cert/icon_yanzhengma.png HTTP/1.1"
이 데이터를 가져오는 데 Template 포맷을 쓸 수 있어요. 입력 데이터의 각 행에 대해 값 플레이스홀더가 있는 템플릿 문자열을 정의해야 해요.
<time> [error] client: <ip>, server: <host> "<request>"
데이터를 가져올 테이블을 만들어 볼게요.
CREATE TABLE error_log
(
`time` DateTime,
`ip` String,
`host` String,
`request` String
)
ENGINE = MergeTree
ORDER BY (host, request, time)
주어진 템플릿으로 데이터를 가져오려면 템플릿 문자열을 파일(row.template — 여기서는 이 파일)에 저장해야 해요.
${time:Escaped} [error] client: ${ip:CSV}, server: ${host:CSV} ${request:JSON}
${name:escaping} 포맷으로 컬럼 이름과 이스케이프 규칙을 정의해요. CSV, JSON, Escaped, Quoted 같은 여러 옵션이 있으며, 각각 해당 이스케이프 규칙을 구현해요. 이제 데이터를 가져올 때 format_template_row 설정 옵션의 인자로 주어진 파일을 사용할 수 있어요 (템플릿과 데이터 파일 끝에는 추가 \n 기호가 없어야 한다는 점을 기억하세요).
INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS format_template_row = 'row.template'
FORMAT Template
데이터가 테이블에 제대로 적재됐는지 확인할 수 있어요.
SELECT
request,
count(*)
FROM error_log
GROUP BY request
┌─request──────────────────────────────────────────┬─count()─┐
│ GET /img/close.png HTTP/1.1 │ 176 │
│ GET /h5/static/cert/icon_yanzhengma.png HTTP/1.1 │ 172 │
│ GET /phone/images/icon_01.png HTTP/1.1 │ 139 │
│ GET /apple-touch-icon-precomposed.png HTTP/1.1 │ 161 │
│ GET /apple-touch-icon.png HTTP/1.1 │ 162 │
│ GET /apple-touch-icon-120x120.png HTTP/1.1 │ 190 │
└──────────────────────────────────────────────────┴─────────┘
공백 건너뛰기
템플릿에서 구분자 사이의 공백을 건너뛸 수 있는 TemplateIgnoreSpaces를 사용하는 걸 고려해 보세요.
Template: --> "p1: ${p1:CSV}, p2: ${p2:CSV}"
TemplateIgnoreSpaces --> "p1:${p1:CSV}, p2:${p2:CSV}"
템플릿으로 데이터 내보내기
템플릿으로 어떤 텍스트 포맷으로든 데이터를 내보낼 수도 있어요. 이 경우 두 개의 파일을 만들어야 해요. 전체 결과 집합의 레이아웃을 정의하는 Result set 템플릿이에요.
== Top 10 IPs ==
${data}
--- ${rows_read:XML} rows read in ${time:XML} ---
여기서 rows_read와 time은 각 요청에 사용할 수 있는 시스템 메트릭이에요. data는 생성된 행을 의미하며 (${data}는 항상 이 파일에서 첫 번째 플레이스홀더로 와야 해요), 행 템플릿 파일에 정의된 템플릿을 기반으로 해요.
${ip:Escaped} generated ${total:Escaped} requests
이제 이 템플릿들로 다음 쿼리를 내보내 볼게요.
SELECT
ip,
count() AS total
FROM error_log GROUP BY ip ORDER BY total DESC LIMIT 10
FORMAT Template SETTINGS format_template_resultset = 'output.results',
format_template_row = 'output.rows';
== Top 10 IPs ==
9.8.4.6 generated 3 requests
9.5.1.1 generated 3 requests
2.4.8.9 generated 3 requests
4.8.8.2 generated 3 requests
4.5.4.4 generated 3 requests
3.3.6.4 generated 2 requests
8.9.5.9 generated 2 requests
2.5.1.8 generated 2 requests
6.8.3.6 generated 2 requests
6.6.3.5 generated 2 requests
--- 1000 rows read in 0.001380604 ---
HTML 파일로 내보내기
템플릿 기반 결과는 INTO OUTFILE 절로 파일로 내보낼 수도 있어요. 주어진 resultset과 row 포맷을 바탕으로 HTML 파일을 생성해 볼게요.
SELECT
ip,
count() AS total
FROM error_log GROUP BY ip ORDER BY total DESC LIMIT 10
INTO OUTFILE 'out.html'
FORMAT Template
SETTINGS format_template_resultset = 'html.results',
format_template_row = 'html.row'
XML로 내보내기
Template 포맷은 XML을 포함해 상상할 수 있는 모든 텍스트 포맷 파일을 생성하는 데 쓸 수 있어요. 관련 템플릿을 넣고 내보내기만 하면 돼요. 메타데이터를 포함한 표준 XML 결과를 얻으려면 XML 포맷도 고려해 보세요.
SELECT *
FROM error_log
LIMIT 3
FORMAT XML
<?xml version='1.0' encoding='UTF-8' ?>
<result>
<meta>
<columns>
<column>
<name>time</name>
<type>DateTime</type>
</column>
...
</columns>
</meta>
<data>
<row>
<time>2023-01-15 13:00:01</time>
<ip>3.5.9.2</ip>
<host>example.com</host>
<request>GET /apple-touch-icon-120x120.png HTTP/1.1</request>
</row>
...
</data>
<rows>3</rows>
<rows_before_limit_at_least>1000</rows_before_limit_at_least>
<statistics>
<elapsed>0.000745001</elapsed>
<rows_read>1000</rows_read>
<bytes_read>88184</bytes_read>
</statistics>
</result>
정규 표현식 기반 가져오기
Regexp 포맷은 입력 데이터를 더 복잡한 방식으로 파싱해야 하는 더 정교한 경우를 다뤄요. error.log 예시 파일을 파싱하는데, 이번에는 파일 이름과 프로토콜을 캡처해서 별도 컬럼에 저장해 볼게요. 먼저 이에 대한 새 테이블을 준비해요.
CREATE TABLE error_log
(
`time` DateTime,
`ip` String,
`host` String,
`file` String,
`protocol` String
)
ENGINE = MergeTree
ORDER BY (host, file, time)
이제 정규 표현식을 기반으로 데이터를 가져올 수 있어요.
INSERT INTO error_log FROM INFILE 'error.log'
SETTINGS
format_regexp = '(.+?) \\[error\\] client: (.+), server: (.+?) "GET .+?([^/]+\\.[^ ]+) (.+?)"'
FORMAT Regexp
ClickHouse는 각 캡처 그룹의 데이터를 순서에 따라 관련 컬럼에 삽입해요. 데이터를 확인해 볼게요.
SELECT * FROM error_log LIMIT 5
┌────────────────time─┬─ip──────┬─host────────┬─file─────────────────────────┬─protocol─┐
│ 2023-01-15 13:00:01 │ 3.5.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:01:40 │ 3.7.2.5 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:16:49 │ 9.2.9.2 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:21:38 │ 8.8.5.3 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
│ 2023-01-15 13:31:27 │ 9.5.8.4 │ example.com │ apple-touch-icon-120x120.png │ HTTP/1.1 │
└─────────────────────┴─────────┴─────────────┴──────────────────────────────┴──────────┘
기본적으로 ClickHouse는 일치하지 않는 행이 있으면 오류를 발생시켜요. 대신 일치하지 않는 행을 건너뛰려면 format_regexp_skip_unmatched 옵션으로 켤 수 있어요.
SET format_regexp_skip_unmatched = 1;
다른 포맷
ClickHouse는 다양한 시나리오와 플랫폼을 다루기 위해 텍스트·바이너리 여러 포맷을 지원해요. 다음 문서에서 더 많은 포맷과 작업 방법을 살펴보세요.
- CSV 및 TSV 포맷
- Parquet
- JSON 포맷
- Regex 및 템플릿
- Native 및 바이너리 포맷
- SQL 포맷
또한 clickhouse-local을 확인해 보세요. ClickHouse 서버 없이 로컬/원격 파일을 다룰 수 있는 휴대형 풀기능 도구예요.