첫 번째 MergeTree 테이블 만들기

출처: Create your first MergeTree table

본문

사전 요구사항 (Prerequisites)

이 가이드를 성공적으로 따라 하려면 다음이 필요해요:

실행 중인 ClickHouse Cloud 서비스. 아직 없다면 ClickHouse Cloud 퀵 스타트를 먼저 완료하세요.

만들게 될 것 (What you'll build)

이 퀵스타트에서는 UK 주거용 부동산 판매 기록(1995년부터)을 저장하는 MergeTree 테이블을 만들어요. 적절한 컬럼 타입으로 스키마를 설계하고, 의미 있는 ORDER BY와 PARTITION BY를 선택하고, S3에서 직접 데이터를 로드한 뒤 system.parts로 ClickHouse가 데이터를 디스크에서 물리적으로 구성하는 방식을 확인할 거예요. 끝나면 MergeTree 엔진이 거의 모든 ClickHouse 테이블의 기반이 되는 이유와, 정렬·파티셔닝 결정이 쿼리 성능을 어떻게 직접 결정하는지 이해하게 돼요.

1. MergeTree가 어떻게 동작하는지 이해하기

SQL을 작성하기 전에, MergeTree가 전통적인 데이터베이스 테이블과 무엇이 다른지 아는 게 도움이 돼요. MergeTree 테이블에 데이터를 삽입할 때 ClickHouse는 행을 하나씩 쓰지 않아요. 대신 데이터 파트(data part) — 작고 정렬되며 압축된 행 묶음 — 를 디스크에 직접 써요. 그런 다음 ClickHouse는 시간이 지남에 따라 백그라운드에서 이 파트들을 병합(merge)해요. 이름이 여기서 나온 거예요: merge(병합) + tree(트리).

모든 데이터 파트는 테이블의 ORDER BY 표현식으로 정렬돼요. 이 정렬 순서가 기본 키 인덱스(primary key index) 가 되어, ClickHouse가 쿼리 중 읽을 필요가 없는 큰 데이터 블록을 건너뛸 수 있게 해 줘요 (이를 데이터 프루닝, data pruning이라 불러요). 여러분의 가장 흔한 쿼리에 대해 ORDER BY 컬럼이 선택적(selective)일수록 ClickHouse가 읽는 데이터가 적어져요.

세 가지 절이 MergeTree가 데이터를 구성하는 방식을 제어해요:

절	역할
`ORDER BY`	각 파트 내에서 데이터를 물리적으로 정렬. 기본 키를 결정. 필수.
`PARTITION BY`	데이터를 별도 파티션으로 분할. 보통 날짜 범위로. 서로 다른 파티션의 파트는 절대 병합되지 않아 빠른 파티션 프루닝을 가능하게 함.
`PRIMARY KEY`	명시적으로 더 짧은 접두사를 설정하지 않으면 `ORDER BY`로 기본 설정. 스파스 인덱스가 여기서 만들어짐.

이제 데이터 파트, 기본 키, 쿼리 성능 사이의 관계를 MergeTree 테이블에서 설명할 수 있어야 해요.

2. 소스 데이터 미리 보기

테이블을 만들기 전에 s3 테이블 함수를 사용해 소스 파일을 검사해 보세요. 이렇게 하면 먼저 ClickHouse에 데이터를 쓰지 않고 S3를 직접 쿼리할 수 있어요. SQL 콘솔에서 다음을 실행해 보세요:

DESCRIBE s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

거의 모든 컬럼이 Nullable(String)으로 추론되는 걸 볼 수 있어요. ClickHouse가 원시 CSV를 읽고 있으므로 실제 데이터 타입을 모르는 거예요 — 다음 단계에서 테이블 스키마를 설계할 때 수정할 부분이죠. 몇 개 행을 미리 보세요:

SELECT *
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
)
LIMIT 5;

이 데이터셋에는 HM Land Registry에 등록된 잉글랜드·웨일스의 주거용 부동산 판매가 포함돼 있어요. 트랜잭션 id, 판매 price, date, 부동산 type, 주소 필드, 지리 식별자가 있어요. 또한 column15, column16이라는 두 개의 빈 트레일링 컬럼도 보일 텐데, 이건 무시해도 돼요. id, price, date, postcode, type, town, county 컬럼이 있는 행을 볼 수 있는지 확인해 보세요.

3. MergeTree 테이블 설계하고 만들기

이제 적절한 스키마로 영구 테이블을 만들어요. 아래 컬럼 타입들은 의도적으로 선택된 거예요:

LowCardinality(String)은 고유 값이 제한적인 컬럼(우편번호, 도시 이름, 카운티 이름)에 사용돼요. 내부적으로 사전 인코딩을 사용해서 저장 공간을 크게 줄이고 이 컬럼들의 그룹핑·필터링 성능을 개선해요.
Enum8은 type과 duration 컬럼을 디스크에 작은 정수로 인코딩하면서 쿼리에서는 사람이 읽을 수 있는 문자열 라벨을 유지해요. 소스 CSV는 단일 문자 코드를 사용하므로 삽입 중에 매핑할 거예요.
PARTITION BY toYYYYMM(date)은 달력 월당 하나의 파티션을 만들어, WHERE 절이 date로 필터링할 때 ClickHouse가 전체 월을 건너뛸 수 있게 해요.
ORDER BY (postcode, addr1, addr2)는 부동산 주소로 빠른 조회를 지원하도록 데이터를 정렬해요 — 이 데이터셋에 가장 자연스러운 접근 패턴이죠.

CREATE TABLE uk_price_paid
(
price      UInt32,
date       Date,
postcode   LowCardinality(String),
type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0),
is_new     UInt8,
duration   Enum8('freehold' = 1, 'leasehold' = 2, 'unknown' = 0),
addr1      String,
addr2      String,
street     LowCardinality(String),
locality   LowCardinality(String),
town       LowCardinality(String),
district   LowCardinality(String),
county     LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (postcode, addr1, addr2);

테이블이 만들어졌는지 다음을 실행해 확인하세요:

SHOW CREATE TABLE uk_price_paid;

결과 셀을 더블클릭해서 전체 출력을 검사하세요. ENGINE = MergeTree를 지정했지만 ClickHouse Cloud가 SharedMergeTree('/clickhouse/tables/{uuid}/{shard}', '{replica}')로 테이블을 만들었다는 걸 볼 수 있어요. 이것은 예상된 동작이에요 — Cloud는 자동으로 MergeTree를 SharedMergeTree로 변환하면서 복제와 공유 스토리지 지원을 추가하거든요. 동작과 쿼리 인터페이스는 동일해요.

4. S3에서 데이터 로드하기

s3() 테이블 함수에서 직접 선택해서 전체 데이터셋을 삽입해요. ClickHouse는 압축 파일을 S3에서 스트리밍해서 정렬된 파트로 테이블에 써요.

INSERT INTO uk_price_paid
SELECT
    toUInt32(price),
    date,
    postcode,
    transform(type, ['T', 'S', 'D', 'F', 'O'],
        ['terraced', 'semi-detached', 'detached', 'flat', 'other'], 'other') AS type,
    if(is_new = 'Y', 1, 0) AS is_new,
    transform(duration, ['F', 'L', 'U'],
        ['freehold', 'leasehold', 'unknown'], 'unknown') AS duration,
    addr1,
    addr2,
    street,
    locality,
    town,
    district,
    county
FROM s3(
'https://learn-clickhouse.s3.us-east-2.amazonaws.com/uk_property_prices/uk_prices.csv.zst'
);

소스 CSV가 모든 것을 단일 문자 코드(예: T는 terraced, F는 freehold, Y/N은 신축) 문자열로 저장하므로, transform으로 읽을 수 있는 라벨에 매핑하고 toUInt32/if로 숫자 컬럼을 캐스팅해요. id, column15, column16 컬럼은 필요 없으므로 제외했어요.

서비스 크기에 따라 1~2분 걸릴 거예요. 완료되면 행 수를 확인하세요:

SELECT formatReadableQuantity(count())
FROM uk_price_paid;

약 3천만 행이 로드된 게 보일 거예요.

5. system.parts로 파트 검사하기

여기서 MergeTree의 내부가 보이기 시작해요. system.parts 테이블은 서비스의 모든 MergeTree 테이블에 대해 디스크의 모든 데이터 파트를 추적해요.

SELECT
partition,
name,
rows,
bytes_on_disk,
marks
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true
ORDER BY partition
LIMIT 20;

각 행은 하나의 활성 데이터 파트를 나타내요. 다음을 주목하세요:

partition — PARTITION BY 표현식에서 파생된 YYYYMM 값. 각 월의 데이터는 격리돼 있어요.
name — 파트 이름은 파티션, 블록 번호 범위, 병합 레벨을 인코딩해요 (예: 199501_1_4_2는 파티션 199501, 블록 1–4, 두 번 병합됨을 의미).
marks — 인덱스 granule 수. 각 granule은 기본적으로 행 8,192개를 덮고, 기본 키 인덱스는 granule당 항목 하나를 저장해요. 이 스파스 인덱스가 메모리에 유지되며 빠른 데이터 건너뛰기를 가능하게 해요.
bytes_on_disk — ClickHouse는 각 파트를 기본적으로 LZ4로 컬럼별로 압축해요. 이를 원시 크기와 비교해서 압축률을 확인해 보세요.

테이블의 총 파트 수와 전체 압축 크기를 보려면 실행하세요:

SELECT
count()          AS parts,
sum(rows)        AS total_rows,
formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table = 'uk_price_paid'
AND active = true;

나중에 이 쿼리를 다시 실행하면 파트 수가 줄어든 걸 볼 수 있어요. 이것이 MergeTree의 merge 가 동작하는 거예요 — ClickHouse가 백그라운드에서 작은 파트들을 큰 파트로 계속 병합해서 파트 수를 줄여요. active = true 필터는 정리 대기 중인 이전 파트가 아니라 현재의 병합된 파트만 보게 해 줘요.

6. 데이터를 쿼리하고 기본 키 동작 관찰하기

이제 실제 분석 쿼리를 몇 개 실행해 볼게요. 첫째, 기록된 가장 비싼 판매를 찾아보세요:

SELECT
addr1,
addr2,
town,
county,
price,
date
FROM uk_price_paid
ORDER BY price DESC
LIMIT 5;

SQL 콘솔에서 쿼리 통계를 확인해 보세요 — 30,033,199개 행 전부가 읽힌 걸 볼 수 있어요. price가 ORDER BY 키의 일부가 아니므로 ClickHouse가 기본 인덱스를 사용해 데이터를 건너뛸 수 없고 전체 테이블 스캔을 해야 하거든요.

다음으로 카운티별 평균 판매 가격을 찾아보세요:

SELECT
county,
round(avg(price)) AS avg_price,
count()           AS sales
FROM uk_price_paid
GROUP BY county
ORDER BY avg_price DESC;

이번에도 30,033,199개 행 전부가 읽혀요 — county는 ORDER BY나 PARTITION BY에 없으므로 ClickHouse가 전체 테이블을 스캔하거든요.

이제 집계를 ORDER BY와 결합한 쿼리를 실행해 보세요. 데이터가 (postcode, addr1, addr2)로 정렬되어 있으므로 우편번호 접두사로 필터링하면 ClickHouse가 테이블 대부분을 건너뛸 수 있어요. 여기서는 SW1A 우편번호 지역 부동산의 연도별 평균 판매 가격을 찾아볼게요:

SELECT
toYear(date) AS year,
round(avg(price)) AS avg_price,
count() AS sales,
min(price) AS cheapest,
max(price) AS most_expensive
FROM uk_price_paid
WHERE postcode LIKE 'SW1A%'
GROUP BY year
ORDER BY year DESC;

각 쿼리 후 SQL 콘솔에서 쿼리 통계를 확인해 보세요. postcode 필터가 적용된 집계는 테이블 행의 일부만 읽어야 해요 — 기본 키 인덱스가 동작하는 모습이죠. 더 넓게 스캔하는 이전 쿼리와 비교해 보세요 — 그 차이가 올바른 ORDER BY를 선택하는 것이 왜 중요한지 보여 줘요.

다음 단계 (Next steps)

이 퀵스타트에서 MergeTree 테이블을 처음부터 만들고, S3에서 UK 부동산 판매 기록 3천만 건을 로드하고, ClickHouse가 데이터를 정렬된 파트와 파티션으로 구성하는 방식을 탐색하고, 기본 키 인덱스의 힘을 보여 주는 쿼리를 실행했어요. MergeTree 엔진이 기반이에요 — 여기서부터 그 위에 구축된 특수 엔진을 탐색하거나, 매터리얼라이즈드 뷰가 이 패턴을 어떻게 확장하는지 배울 수 있어요. 다음 퀵스타트를 계속 진행하세요:

매터리얼라이즈드 뷰 소개

또는 레퍼런스 문서로 더 깊이 들어가 보세요:

첫 번째 MergeTree 테이블 만들기