첫 materialized view 만들기

ClickHouse에서 materialized view를 사용해 쿼리 결과를 다른 정렬 순서로 미리 계산해 저장하고, 프라이머리 키(primary key)에 포함되지 않은 컬럼에서도 빠르게 조회하는 방법을 알아봅니다.

모든 빠른 시작

실시간 분석데이터 웨어하우징관측성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.

또한 이 가이드는 해당 빠른 시작에서 생성한 uk_price_paid 테이블을 바탕으로 진행하므로, 첫 번째 MergeTree 테이블 만들기 빠른 시작을 완료해야 합니다.

구축할 내용

MergeTree 빠른 시작에서는 테이블이 (postcode, addr1, addr2)를 기준으로 정렬되어 있으므로 town 또는 county로 uk_price_paid를 쿼리하려면 전체 테이블 스캔이 필요하다는 점을 확인했습니다. 이 빠른 시작에서는 (town, date)를 기준으로 정렬된 동일한 데이터를 저장하는 materialized view를 생성해 원본 테이블을 변경하지 않고도 town별 빠른 조회가 가능하도록 이 문제를 해결합니다. 마치면 materialized view가 삽입 트리거로 작동하는 방식, 기존 데이터를 백필하는 방법, 그리고 데이터를 두 번 저장할 때의 디스크 공간 상의 절충점까지 이해하게 됩니다.

1. materialized view가 필요한 이유 이해하기

   uk_price_paid 테이블은 (postcode, addr1, addr2) 기준으로 정렬됩니다. 즉 postcode, addr1, addr2로 필터링할 때는 ClickHouse가 큰 데이터 블록을 건너뛸 수 있지만, town으로 필터링하는 쿼리는 3천만 개 전체 행을 모두 스캔해야 합니다.

   다른 ORDER BY를 사용하는 두 번째 테이블을 만들 수도 있지만, 그러면 새 데이터가 들어올 때마다 두 테이블 모두에 삽입해야 한다는 점을 계속 신경 써야 합니다. materialized view는 이 과정을 자동화합니다. 원본 테이블에 대한 삽입을 감지하고, 행을 변환한 다음, 결과를 대상 테이블에 자동으로 기록합니다.

   materialized view는 삽입 트리거처럼 생각하면 됩니다. 원본 테이블에 행이 삽입될 때마다 MV의 SELECT 쿼리가 새 행 블록에 대해 실행되고, 그 결과가 대상 테이블에 삽입됩니다.

2. 대상 테이블 생성

   materialized view는 출력을 저장할 곳이 필요합니다. 이는 일반적인 MergeTree 테이블일 뿐이며, 스키마, ORDER BY, PARTITION BY를 모두 직접 제어할 수 있습니다.

   도시 기반 쿼리에 필요한 컬럼만 포함하도록 (town, date) 기준으로 정렬된 테이블을 생성합니다:

   CREATE TABLE uk_price_paid_by_town
   (
       town       LowCardinality(String),
       date       Date,
       price      UInt32,
       type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (town, date);
   

   이 테이블은 특별할 것이 없는 표준 MergeTree 테이블입니다. 다음에 생성할 materialized view는 단순히 데이터를 이 테이블로 라우팅합니다.

   테이블이 생성되었는지 확인하세요:

   SHOW CREATE TABLE uk_price_paid_by_town;
   
3. materialized view 생성

   이제 원본 테이블(uk_price_paid)과 대상 테이블(uk_price_paid_by_town)을 연결할 materialized view를 생성합니다:

   CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
   TO uk_price_paid_by_town
   AS SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   TO uk_price_paid_by_town 절은 ClickHouse가 SELECT의 출력을 대상 테이블에 기록하도록 지정합니다. 이제부터 uk_price_paid에 행이 삽입될 때마다 이 MV가 실행되어 변환된 행을 uk_price_paid_by_town에 삽입합니다.

   중요한 주의 사항이 있습니다. materialized view는 삽입이 발생할 때만 실행됩니다. 원본 테이블의 행을 삭제하거나 업데이트해도 대상 테이블은 이를 인지하지 못합니다. 즉, MV는 삭제나 업데이트와 동기화된 상태를 유지하지 않습니다. 이런 동기화가 필요하다면 프로젝션 사용을 대신 고려하십시오.

4. 기존 데이터 백필

   materialized view는 향후 발생하는 삽입만 처리합니다. uk_price_paid에 이미 있는 3천만 개의 행은 MV가 생성되기 전에 삽입되었으므로 현재 대상 테이블은 비어 있습니다.

   수동으로 백필하세요:

   INSERT INTO uk_price_paid_by_town
   SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   이는 대상 테이블에 직접 삽입되며, 이 단계에는 MV가 관여하지 않습니다. 완료되면 행 수가 일치하는지 확인하십시오:

   SELECT
       'uk_price_paid' AS table,
       count() AS rows
   FROM uk_price_paid
   UNION ALL
   SELECT
       'uk_price_paid_by_town' AS table,
       count() AS rows
   FROM uk_price_paid_by_town;
   

   두 테이블의 행 수는 동일해야 합니다.

5. materialized view 대상 테이블 쿼리

   이제 대상 테이블에서 town을 기준으로 필터링하는 쿼리를 실행한 뒤, 원본 테이블에 직접 쿼리한 결과와 비교합니다.

   먼저, 원본 테이블을 쿼리합니다:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   쿼리 통계를 확인하세요. town이 원본 테이블의 ORDER BY에 포함되지 않으므로 3천만 개의 행이 모두 읽힙니다.

   이제 materialized view의 대상 테이블에서 동일한 쿼리를 실행하세요:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid_by_town
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   쿼리 통계를 다시 확인해 보세요. 대상 테이블이 (town, date) 순으로 정렬되어 있으므로 ClickHouse는 LONDON과 일치하지 않는 데이터를 모두 건너뛸 수 있어 읽는 행 수가 훨씬 줄어듭니다.

   무엇이 생성되었는지 확인하려면 SHOW TABLES를 실행하세요:

   SHOW TABLES;
   

   uk_price_paid_by_town(대상 테이블)과 uk_price_paid_by_town_mv(뷰)가 모두 표시됩니다. CREATE MATERIALIZED VIEW ... TO를 사용했기 때문에 대상 테이블 이름을 직접 지정할 수 있습니다. TO 절을 생략하면 ClickHouse가 암시적 이름이 지정된 대상 테이블(.inner.xxx)을 생성하는데, 이 테이블은 직접 다루기가 더 어렵습니다. 따라서 TO 절을 사용해 materialized view를 생성하는 것이 좋습니다.

6. 데이터가 두 번 저장되는 것을 확인

   구체화된 뷰(Materialized View)는 추가 디스크 공간을 사용하는 대신 더 빠른 읽기를 제공합니다. 각 테이블이 사용하는 공간을 확인하려면 system.parts를 쿼리하십시오:

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

   데이터는 물리적으로 두 번 저장됩니다. 한 번은 (postcode, addr1, addr2)로 정렬된 uk_price_paid에, 또 한 번은 (town, date)로 정렬된 uk_price_paid_by_town에 저장됩니다. 이것이 핵심적인 절충점입니다. 즉, 서로 다른 액세스 패턴에서 더 빠른 읽기 성능을 얻는 대신 더 많은 디스크 공간을 사용합니다.

   대상 테이블은 포함하는 컬럼이 더 적고 (town, date) 정렬 순서가 원본과는 다르게 압축될 수 있으므로, 디스크에서 차지하는 크기가 더 작을 수 있습니다.

다음 단계

이 빠른 시작에서는 원본 테이블을 수정하지 않고도 town 기준으로 빠르게 조회할 수 있도록, 정렬 순서가 다른 영국 부동산 거래 데이터를 저장하는 materialized view를 만들었습니다. 또한 MV가 insert trigger처럼 동작하며, 기존 데이터는 수동으로 백필해야 하고, 그에 따른 대가로 추가 디스크 공간이 필요하다는 점을 배웠습니다.

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

• 첫 번째 프로젝션 만들기

또는 참고 문서를 통해 더 자세히 알아보세요:

• materialized view 참고
• 증분형 materialized view
• 프로젝션

모든 빠른 시작