첫 번째 MergeTree 테이블 만들기

MergeTree 테이블을 만들고 영국 부동산 가격 데이터를 불러오면서, 파트와 머지가 저장소와 쿼리 성능에 어떤 영향을 미치는지 관찰해 ClickHouse의 주요 테이블 엔진이 어떻게 작동하는지 알아봅니다.

모든 빠른 시작

실시간 분석데이터 웨어하우징관측성AI/MLCloudOss

사전 요구사항

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

만들어 볼 내용

이 quickstart에서는 1995년부터의 영국 주거용 부동산 매매 기록을 저장하는 MergeTree 테이블을 만듭니다. 적절한 컬럼 타입으로 스키마를 설계하고, 의미 있는 ORDER BY와 PARTITION BY를 선택하고, S3에서 직접 데이터를 로드한 다음, system.parts를 쿼리해 ClickHouse가 디스크에 데이터를 물리적으로 어떻게 구성하는지 확인합니다. 이 과정을 마치면 MergeTree 엔진이 거의 모든 ClickHouse 테이블의 기반인 이유와, 정렬 및 파티셔닝 방식의 선택이 쿼리 성능에 어떤 영향을 주는지 이해하게 됩니다.

1. MergeTree의 작동 방식 이해하기

   SQL을 작성하기 전에 MergeTree가 기존 데이터베이스 테이블과 어떻게 다른지 알아두면 도움이 됩니다.

   MergeTree 테이블에 데이터를 삽입할 때 ClickHouse는 행을 하나씩 쓰지 않습니다. 대신 정렬되고 압축된 작은 행 묶음인 **데이터 파트(data part)**를 디스크에 직접 씁니다. 그런 다음 ClickHouse는 시간이 지나면서 백그라운드에서 이러한 파트들을 머지합니다. 이름도 여기서 유래했습니다: merge + tree.

   모든 데이터 파트는 테이블의 ORDER BY 표현식에 따라 정렬됩니다. 이 정렬 순서는 기본 키(primary key) 인덱스가 되며, 그 결과 ClickHouse는 쿼리 실행 중 읽을 필요가 없는 큰 데이터 블록을 건너뛸 수 있습니다(이를 데이터 프루닝(data pruning)이라고 합니다). 가장 자주 사용하는 쿼리에서 ORDER BY 컬럼의 선택성이 높을수록 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는 한 글자 코드를 사용하므로, insert 시 이 값을 매핑합니다.
   • 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, 자유보유권은 F, 신축은 Y/N), transform을 사용해 사람이 읽기 쉬운 레이블로 매핑하고 toUInt32/if를 사용해 숫자 컬럼을 형변환합니다. id, column15, column16 컬럼은 필요하지 않으므로 제외합니다.

   이 작업은 service 크기에 따라 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;
   

   각 행은 활성 상태인 데이터 파트 1개를 나타냅니다. 다음 사항에 유의하십시오:

   • partition - PARTITION BY 표현식에서 파생된 YYYYMM 값입니다. 각 월의 데이터는 서로 분리되어 저장됩니다.
   • name - 파트 이름에는 파티션, 블록 번호 범위, 머지 수준이 인코딩됩니다(예: 199501_1_4_2는 파티션 199501, 블록 1–4, 2번 머지되었음을 의미합니다).
   • marks - 인덱스 그래뉼 수입니다. 각 그래뉼은 기본적으로 8,192개의 행을 포함하며, 프라이머리 키 인덱스는 그래뉼마다 항목 1개를 저장합니다. 이 희소 인덱스가 메모리에 유지되어 빠른 데이터 스키핑이 가능합니다.
   • 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에서 머지가 작동하는 방식입니다. ClickHouse는 더 작은 파트를 백그라운드에서 지속적으로 더 큰 파트로 병합하여 파트 수를 줄입니다. active = true 필터를 사용하면 정리 대기 중인 이전 파트가 아니라 현재의 병합된 파트만 볼 수 있습니다.

6. 데이터를 쿼리해 기본 키(primary key) 동작 살펴보기

   이제 실제 분석용 쿼리를 실행합니다. 먼저, 지금까지 기록된 판매 내역 중 가장 고가의 판매를 찾아봅니다:

   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) 순으로 정렬되어 있으므로 postcode 접두사로 필터링하면 ClickHouse가 테이블 대부분을 건너뛸 수 있습니다. 여기서는 SW1A postcode 지역 부동산의 연도별 평균 매매가를 구합니다:

   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를 선택하는 것이 왜 중요한지 알 수 있습니다.

다음 단계

이 빠른 시작에서는 처음부터 MergeTree 테이블을 만들고, S3에서 영국 부동산 거래 기록 3천만 건을 적재하고, ClickHouse가 데이터를 정렬된 파트와 파티션으로 구성하는 방식을 살펴보고, 프라이머리 키 인덱스의 위력을 보여 주는 쿼리를 실행했습니다.

MergeTree 엔진은 그 기반입니다. 여기에서 이를 바탕으로 구축된 특화 엔진을 더 살펴보거나, 구체화된 뷰(Materialized View)가 이 패턴을 어떻게 한층 더 확장하는지 알아볼 수 있습니다.

다음 빠른 시작도 확인해 보세요:

• 구체화된 뷰(Materialized View) 소개

또는 참고 문서에서 더 자세히 알아보세요:

• MergeTree 엔진 참고
• system.parts 참고
• 적절한 컬럼 타입 선택

모든 빠른 시작