BigQuery에서 ClickHouse Cloud로 마이그레이션
BigQuery에서 ClickHouse Cloud로 마이그레이션 (Migrating from BigQuery to ClickHouse Cloud)
BigQuery에서 ClickHouse Cloud로 마이그레이션하는 과정을 안내하는 가이드예요. Stack Overflow 데이터셋을 예시로 사용해 데이터 로드, 스키마 설계, 파티셔닝, 프로젝션, 쿼리 재작성 등을 다룹니다.
출처: 문서
본문
왜 BigQuery보다 ClickHouse Cloud를 사용할까? (Why use ClickHouse Cloud over BigQuery?)
TLDR: ClickHouse가 현대 데이터 분석에서 BigQuery보다 더 빠르고, 저렴하며, 더 강력하기 때문입니다:
BigQuery에서 ClickHouse Cloud로 데이터 로드하기 (Loading data from BigQuery to ClickHouse Cloud)
데이터셋 (Dataset)
BigQuery에서 ClickHouse Cloud로의 전형적인 마이그레이션을 보여주는 예시 데이터셋으로, 여기에 문서화된 Stack Overflow 데이터셋을 사용합니다. 이 데이터셋은 2008년부터 2024년 4월까지 Stack Overflow에서 발생한 모든 post, vote, user, comment, badge를 포함합니다. 이 데이터의 BigQuery 스키마는 아래에 나와 있습니다.
이 데이터셋을 마이그레이션 단계를 테스트하기 위해 BigQuery 인스턴스에 채우려는 사용자들을 위해, GCS 버킷에 Parquet 형식으로 이 테이블들의 데이터를 제공했으며 BigQuery에서 테이블을 만들고 로드하는 DDL 명령은 여기에서 사용할 수 있습니다.
데이터 마이그레이션 (Migrating data)
BigQuery와 ClickHouse Cloud 간 데이터 마이그레이션은 두 가지 주요 워크로드 유형으로 나뉩니다:
- 초기 대량 로드와 주기적 업데이트 — 초기 데이터셋을 주기적 업데이트(예: 일일)와 함께 마이그레이션해야 합니다. 여기서 업데이트는 변경된 행을 재전송해 처리됩니다 — 비교에 사용할 수 있는 컬럼(예: 날짜)으로 식별됩니다. 삭제는 데이터셋의 완전한 주기적 재로드로 처리됩니다.
- 실시간 복제 또는 CDC — 초기 데이터셋을 마이그레이션해야 합니다. 이 데이터셋에 대한 변경은 몇 초의 지연만 허용되는 준실시간으로 ClickHouse에 반영되어야 합니다. 이는 효과적으로 Change Data Capture (CDC) 프로세스로, BigQuery의 테이블이 ClickHouse와 동기화되어야 합니다. 즉, BigQuery 테이블의 삽입, 업데이트, 삭제가 ClickHouse의 동등한 테이블에 적용되어야 합니다.
Google Cloud Storage (GCS)를 통한 대량 로드 (Bulk loading via Google Cloud Storage (GCS))
BigQuery는 데이터를 Google의 객체 스토어(GCS)로 내보내는 것을 지원합니다. 예시 데이터셋의 경우:
- 7개 테이블을 GCS로 내보냅니다. 명령은 여기에서 사용할 수 있습니다.
- 데이터를 ClickHouse Cloud로 가져옵니다. 이를 위해 gcs 테이블 함수를 사용할 수 있습니다. DDL과 가져오기 쿼리는 여기에서 사용할 수 있습니다. ClickHouse Cloud 인스턴스가 여러 컴퓨팅 노드로 구성되므로
gcs테이블 함수 대신 s3Cluster 테이블 함수를 사용한다는 점을 기억하세요. 이 함수는 gcs 버킷에서도 동작하며 ClickHouse Cloud 서비스의 모든 노드를 활용해 병렬로 데이터를 로드합니다.
이 접근 방식은 여러 장점이 있습니다:
- BigQuery 내보내기 기능은 데이터 부분집합을 내보내기 위한 필터를 지원합니다.
- BigQuery는 Parquet, Avro, JSON, CSV 형식과 여러 압축 유형으로 내보내기를 지원합니다 — 모두 ClickHouse가 지원합니다.
- GCS는 객체 수명 주기 관리를 지원해, 내보내고 ClickHouse로 가져온 데이터를 지정된 기간 후에 삭제할 수 있습니다.
- Google은 하루 최대 50TB를 GCS로 무료로 내보낼 수 있게 허용합니다. 사용자는 GCS 스토리지에 대해서만 비용을 지불합니다.
- 내보내기는 여러 파일을 자동으로 생성하며, 각각 최대 1GB의 테이블 데이터로 제한합니다. 이는 가져오기를 병렬화할 수 있게 해 주므로 ClickHouse에 유리합니다.
다음 예시를 시도하기 전에, 사용자들이 내보내기에 필요한 권한과 지역 권장 사항을 검토해 내보내기와 가져오기 성능을 최대화할 것을 권장합니다.
예약 쿼리를 통한 실시간 복제 또는 CDC (Real-time replication or CDC via scheduled queries)
Change Data Capture (CDC)는 두 데이터베이스 간 테이블을 동기화 상태로 유지하는 프로세스입니다. 업데이트와 삭제를 준실시간으로 처리해야 한다면 훨씬 더 복잡합니다. 한 가지 접근 방식은 BigQuery의 예약 쿼리 기능을 사용해 주기적으로 내보내기를 예약하는 것입니다. ClickHouse에 삽입되는 데이터의 약간의 지연을 수용할 수 있다면, 이 접근 방식은 구현하고 유지하기 쉽습니다. 예시는 이 블로그 게시물에 있습니다.
스키마 설계하기 (Designing schemas)
Stack Overflow 데이터셋은 여러 관련 테이블을 포함합니다. 먼저 primary 테이블의 마이그레이션에 집중할 것을 권장합니다. 이것이 반드시 가장 큰 테이블일 필요는 없지만, 가장 많은 분석 쿼리를 받을 것으로 예상되는 테이블이어야 합니다. 이를 통해 주요 ClickHouse 개념에 익숙해질 수 있습니다. 추가 테이블이 추가될 때 ClickHouse 기능을 완전히 활용하고 최적의 성능을 얻으려면 이 테이블은 재모델링이 필요할 수 있습니다. 이 모델링 과정은 Data Modeling 문서에서 살펴봅니다.
이 원칙을 따라 메인 posts 테이블에 집중합니다. 이 테이블의 BigQuery 스키마는 아래에 나와 있습니다:
CREATE TABLE stackoverflow.posts (
id INTEGER,
posttypeid INTEGER,
acceptedanswerid STRING,
creationdate TIMESTAMP,
score INTEGER,
viewcount INTEGER,
body STRING,
owneruserid INTEGER,
ownerdisplayname STRING,
lasteditoruserid STRING,
lasteditordisplayname STRING,
lasteditdate TIMESTAMP,
lastactivitydate TIMESTAMP,
title STRING,
tags STRING,
answercount INTEGER,
commentcount INTEGER,
favoritecount INTEGER,
conentlicense STRING,
parentid STRING,
communityowneddate TIMESTAMP,
closeddate TIMESTAMP
);
타입 최적화 (Optimizing types)
여기에 설명된 과정을 적용하면 다음 스키마가 됩니다:
CREATE TABLE stackoverflow.posts
(
`Id` Int32,
`PostTypeId` Enum('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
`AcceptedAnswerId` UInt32,
`CreationDate` DateTime,
`Score` Int32,
`ViewCount` UInt32,
`Body` String,
`OwnerUserId` Int32,
`OwnerDisplayName` String,
`LastEditorUserId` Int32,
`LastEditorDisplayName` String,
`LastEditDate` DateTime,
`LastActivityDate` DateTime,
`Title` String,
`Tags` String,
`AnswerCount` UInt16,
`CommentCount` UInt8,
`FavoriteCount` UInt8,
`ContentLicense`LowCardinality(String),
`ParentId` String,
`CommunityOwnedDate` DateTime,
`ClosedDate` DateTime
)
ENGINE = MergeTree
ORDER BY tuple()
COMMENT 'Optimized types'
간단한 INSERT INTO SELECT로 이 테이블을 채울 수 있으며, gcs 테이블 함수를 사용해 gcs에서 내보낸 데이터를 읽습니다. ClickHouse Cloud에서는 gcs 호환 가능한 s3Cluster 테이블 함수를 사용해 여러 노드에 걸쳐 로드를 병렬화할 수도 있다는 점을 기억하세요:
INSERT INTO stackoverflow.posts SELECT * FROM gcs( 'gs://clickhouse-public-datasets/stackoverflow/parquet/posts/*.parquet', NOSIGN);
새 스키마에서 우리는 null을 유지하지 않습니다. 위의 삽입은 이들을 해당 타입의 기본값으로 암시적으로 변환합니다 — 정수는 0, 문자열은 빈 값입니다. ClickHouse는 또한 모든 숫자를 대상 정밀도로 자동 변환합니다.
ClickHouse Primary key는 어떻게 다른가요? (How are ClickHouse Primary keys different?)
여기에 설명된 대로, BigQuery와 마찬가지로 ClickHouse는 테이블의 primary key 컬럼 값의 고유성을 강제하지 않습니다.
BigQuery의 클러스터링과 유사하게, ClickHouse 테이블의 데이터는 primary key 컬럼으로 정렬되어 디스크에 저장됩니다. 쿼리 최적화기는 이 정렬 순서를 활용해 재정렬을 방지하고, 조인의 메모리 사용량을 최소화하고, limit 절에 대한 단락을 가능하게 합니다.
BigQuery와 달리 ClickHouse는 primary key 컬럼 값을 기반으로 (희소한) primary index를 자동으로 만듭니다. 이 인덱스는 primary key 컬럼에 필터가 포함된 모든 쿼리를 가속화하는 데 사용됩니다. 구체적으로:
- 메모리와 디스크 효율은 ClickHouse가 종종 사용되는 규모에 필수적입니다. 데이터는 파츠(parts)라는 청크로 ClickHouse 테이블에 기록되며, 백그라운드에서 파츠를 병합하는 규칙이 적용됩니다. ClickHouse에서 각 파츠는 자체 primary index를 가집니다. 파츠가 병합되면 병합된 파츠의 primary index도 병합됩니다. 이 인덱스들은 각 행에 대해 만들어지지 않는다는 점을 기억하세요. 대신 파츠에 대한 primary index는 행 그룹마다 하나의 인덱스 항목을 가집니다 — 이 기법을 희소 인덱싱(sparse indexing)이라고 합니다.
- 희소 인덱싱은 ClickHouse가 파츠의 행을 지정된 키로 정렬해 디스크에 저장하기 때문에 가능합니다. 단일 행을 직접 찾는 대신(B-Tree 기반 인덱스처럼), 희소 primary index는 (인덱스 항목에 대한 이진 검색으로) 쿼리와 일치할 수 있는 행 그룹을 빠르게 식별할 수 있게 합니다. 찾은 잠재적 일치 행 그룹은 일치 항목을 찾기 위해 병렬로 ClickHouse 엔진으로 스트리밍됩니다. 이 인덱스 설계는 primary index를 작게 유지하면서도(완전히 메인 메모리에 들어감) 쿼리 실행 시간을 크게 단축하게 해 주며, 특히 데이터 분석 사용 사례에서 전형적인 범위 쿼리에 유용합니다. 자세한 내용은 심층 가이드를 권장합니다.
ClickHouse에서 선택한 primary key는 인덱스뿐만 아니라 데이터가 디스크에 기록되는 순서도 결정합니다. 이 때문에 압축 수준에 큰 영향을 줄 수 있으며, 이는 다시 쿼리 성능에 영향을 줄 수 있습니다. 대부분 컬럼의 값이 연속적인 순서로 기록되게 하는 정렬 키는 선택된 압축 알고리즘(과 코덱)이 데이터를 더 효과적으로 압축하게 해 줍니다.
테이블의 모든 컬럼은 키에 포함되었는지와 무관하게 지정된 정렬 키의 값을 기준으로 정렬됩니다. 예를 들어
CreationDate가 키로 사용되면, 다른 모든 컬럼의 값 순서가CreationDate컬럼의 값 순서와 대응합니다. 여러 정렬 키를 지정할 수 있습니다 — 이는SELECT쿼리의ORDER BY절과 같은 의미로 정렬합니다.
정렬 키 선택하기 (Choosing an ordering key)
정렬 키 선택을 위한 고려 사항과 단계(예시로 posts 테이블 사용)는 여기를 참고하세요.
데이터 모델링 기법 (Data modeling techniques)
BigQuery에서 마이그레이션하는 사용자에게 ClickHouse에서 데이터 모델링 가이드를 읽을 것을 권장합니다. 이 가이드는 같은 Stack Overflow 데이터셋을 사용하고 ClickHouse 기능을 사용하는 여러 접근 방식을 탐구합니다.
파티션 (Partitions)
BigQuery에서 왔다면, 테이블을 파티션이라는 더 작고 관리하기 쉬운 조각으로 나누어 대규모 데이터베이스의 성능과 관리성을 향상시키는 테이블 파티셔닝 개념에 익숙할 것입니다. 이 파티셔닝은 지정된 컬럼(예: 날짜)의 범위, 정의된 목록, 또는 키의 해시를 통해 달성할 수 있습니다. 이를 통해 관리자는 날짜 범위나 지리적 위치 같은 특정 기준으로 데이터를 구성할 수 있습니다.
파티셔닝은 파티션 프루닝과 더 효율적인 인덱싱을 통한 더 빠른 데이터 접근을 가능하게 하여 쿼리 성능을 개선하는 데 도움을 줍니다. 또한 백업과 데이터 정리 같은 유지 관리 작업을 전체 테이블 대신 개별 파티션에서 수행할 수 있게 해 줍니다. 추가로 파티셔닝은 여러 파티션에 부하를 분산시켜 BigQuery 데이터베이스의 확장성을 크게 개선할 수 있습니다.
ClickHouse에서 파티셔닝은 PARTITION BY 절을 통해 테이블이 처음 정의될 때 지정됩니다. 이 절은 어떤 컬럼(들)에 대한 SQL 표현식을 포함할 수 있으며, 그 결과가 행이 어느 파티션으로 보내질지 정의합니다.
데이터 파츠는 디스크에서 각 파티션과 논리적으로 연관되며 따로 쿼리할 수 있습니다. 아래 예시에서 toYear(CreationDate) 표현식을 사용해 posts 테이블을 연 단위로 파티셔닝합니다. 행이 ClickHouse에 삽입되면 이 표현식이 각 행에 대해 평가됩니다 — 행은 그 결과 파티션으로 라우팅되어 그 파티션에 속한 새 데이터 파츠 형태가 됩니다.
CREATE TABLE posts
(
`Id` Int32 CODEC(Delta(4), ZSTD(1)),
`PostTypeId` Enum8('Question' = 1, 'Answer' = 2, 'Wiki' = 3, 'TagWikiExcerpt' = 4, 'TagWiki' = 5, 'ModeratorNomination' = 6, 'WikiPlaceholder' = 7, 'PrivilegeWiki' = 8),
`AcceptedAnswerId` UInt32,
`CreationDate` DateTime64(3, 'UTC'),
...
`ClosedDate` DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (PostTypeId, toDate(CreationDate), CreationDate)
PARTITION BY toYear(CreationDate)
응용 (Applications)
ClickHouse에서 파티셔닝은 BigQuery와 유사한 응용을 가지지만 몇 가지 미묘한 차이가 있습니다. 구체적으로:
- 데이터 관리 — ClickHouse에서 파티셔닝을 주로 데이터 관리 기능으로 간주해야 하며, 쿼리 최적화 기법으로 간주해서는 안 됩니다. 키를 기반으로 데이터를 논리적으로 분리하면 각 파티션을 독립적으로(예: 삭제) 조작할 수 있습니다. 이를 통해 파티션, 즉 부분집합을 스토리지 티어 사이에서 시간에 따라 효율적으로 이동하거나 데이터를 만료시키고/클러스터에서 효율적으로 삭제할 수 있습니다. 아래 예시에서 우리는 2008년의 posts를 제거합니다:
SELECT DISTINCT partition
FROM system.parts
WHERE `table` = 'posts'
┌─partition─┐
│ 2008 │
│ 2009 │
│ 2010 │
│ 2011 │
│ 2012 │
│ 2013 │
│ 2014 │
│ 2015 │
│ 2016 │
│ 2017 │
│ 2018 │
│ 2019 │
│ 2020 │
│ 2021 │
│ 2022 │
│ 2023 │
│ 2024 │
└───────────┘
17 rows in set. Elapsed: 0.002 sec.
ALTER TABLE posts
(DROP PARTITION '2008')
Ok.
0 rows in set. Elapsed: 0.103 sec.
- 쿼리 최적화 — 파티션이 쿼리 성능을 도울 수는 있지만, 이는 접근 패턴에 크게 의존합니다. 쿼리가 몇 개의 파티션(이상적으로는 하나)만 대상으로 하면 성능이 잠재적으로 개선될 수 있습니다. 이는 보통 파티셔닝 키가 primary key에 없고 그것으로 필터링할 때만 유용합니다. 그러나 많은 파티션을 다뤄야 하는 쿼리는 파티셔닝을 사용하지 않을 때보다 더 나쁠 수 있습니다(파티셔닝으로 인해 더 많은 파츠가 있을 수 있으므로). 파티셔닝 키가 이미 primary key의 초기 항목이라면 단일 파티션 대상 지정의 이점은 더욱 작아지거나 없어집니다. 파티셔닝은 각 파티션의 값이 고유하다면
GROUP BY쿼리를 최적화하는 데도 사용할 수 있습니다. 그러나 일반적으로 primary key가 최적화되어 있는지 확인하고, 접근 패턴이 하루 중 특정 예측 가능한 부분집합에 접근하는 특별한 경우(예: 일 단위 파티셔닝, 대부분 쿼리가 마지막 하루에)에만 파티셔닝을 쿼리 최적화 기법으로 고려해야 합니다.
권장 사항 (Recommendations)
파티셔닝을 데이터 관리 기법으로 고려해야 합니다. 시계열 데이터를 다룰 때 데이터를 클러스터에서 만료시켜야 하는 경우(예: 가장 오래된 파티션을 그냥 드롭할 수 있음) 이상적입니다.
중요: 파티셔닝 키 표현식이 고카디널리티 집합을 만들지 않도록 하세요. 즉, 100개 이상의 파티션을 만드는 것은 피해야 합니다. 예를 들어 클라이언트 식별자나 이름 같은 고카디널리티 컬럼으로 데이터를 파티셔닝하지 마세요. 대신 클라이언트 식별자나 이름을 ORDER BY 표현식의 첫 번째 컬럼으로 만드세요.
내부적으로 ClickHouse는 삽입된 데이터에 대해 파츠를 만듭니다. 더 많은 데이터가 삽입될수록 파츠 수가 증가합니다. 과도하게 많은 파츠 수(읽을 파일이 많아지므로 쿼리 성능을 저하시킴)를 방지하기 위해 파츠는 백그라운드 비동기 프로세스에서 병합됩니다. 파츠 수가 사전 구성된 제한을 초과하면 ClickHouse는 삽입 시 "too many parts" 오류로 예외를 던집니다. 이는 정상 동작 중에는 발생해서는 안 되며, ClickHouse가 잘못 구성되었거나 잘못 사용될 때(예: 많은 소규모 삽입)만 발생합니다. 파츠는 파티션별로 격리되어 생성되므로 파티션 수를 늘리면 파츠 수가 증가합니다(파티션 수의 배수). 따라서 고카디널리티 파티셔닝 키는 이 오류를 일으킬 수 있으므로 피해야 합니다.
머티리얼라이즈드 뷰 vs 프로젝션 (Materialized views vs projections)
ClickHouse의 프로젝션 개념을 통해 테이블에 여러 ORDER BY 절을 지정할 수 있습니다.
ClickHouse 데이터 모델링에서 머티리얼라이즈드 뷰를 사용해 집계를 사전 계산하고, 행을 변환하고, 서로 다른 접근 패턴에 대해 쿼리를 최적화하는 방법을 살펴봅니다. 후자의 경우, 머티리얼라이즈드 뷰가 삽입을 받는 원래 테이블과 다른 정렬 키를 가진 대상 테이블로 행을 보내는 예시를 제공했습니다.
예를 들어 다음 쿼리를 고려해 보세요:
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
┌──────────avg(Score)─┐
│ 0.18181818181818182 │
└─────────────────────┘
1 row in set. Elapsed: 0.040 sec. Processed 90.38 million rows, 361.59 MB (2.25 billion rows/s., 9.01 GB/s.)
Peak memory usage: 201.93 MiB.
UserId가 정렬 키가 아니므로 이 쿼리는 전체 9000만 행을 (빠르지만) 스캔해야 합니다. 이전에 우리는 PostId에 대한 조회 역할을 하는 머티리얼라이즈드 뷰로 이 문제를 해결했습니다. 같은 문제를 프로젝션으로 해결할 수 있습니다. 아래 명령은 ORDER BY user_id를 가진 프로젝션을 추가합니다.
ALTER TABLE comments ADD PROJECTION comments_user_id (
SELECT * ORDER BY UserId
)
ALTER TABLE comments MATERIALIZE PROJECTION comments_user_id
먼저 프로젝션을 만들고 그 다음 materialize해야 한다는 점을 기억하세요. 이 후자의 명령은 데이터가 두 개의 서로 다른 순서로 디스크에 두 번 저장되게 합니다. 아래와 같이 데이터 생성 시점에 프로젝션을 정의할 수도 있으며, 데이터가 삽입될 때 자동으로 유지됩니다.
CREATE TABLE comments
(
`Id` UInt32,
`PostId` UInt32,
`Score` UInt16,
`Text` String,
`CreationDate` DateTime64(3, 'UTC'),
`UserId` Int32,
`UserDisplayName` LowCardinality(String),
PROJECTION comments_user_id
(
SELECT *
ORDER BY UserId
)
)
ENGINE = MergeTree
ORDER BY PostId
프로젝션이 ALTER 명령으로 생성되면 MATERIALIZE PROJECTION 명령이 발행될 때 생성이 비동기적입니다. 다음 쿼리로 이 작업의 진행 상황을 확인할 수 있습니다. is_done=1을 기다리세요.
SELECT
parts_to_do,
is_done,
latest_fail_reason
FROM system.mutations
WHERE (`table` = 'comments') AND (command LIKE '%MATERIALIZE%')
┌─parts_to_do─┬─is_done─┬─latest_fail_reason─┐
1. │ 1 │ 0 │ │
└─────────────┴─────────┴────────────────────┘
1 row in set. Elapsed: 0.003 sec.
위 쿼리를 반복하면 추가 저장 공간을 대가로 성능이 크게 개선된 것을 볼 수 있습니다.
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
┌──────────avg(Score)─┐
1. │ 0.18181818181818182 │
└─────────────────────┘
1 row in set. Elapsed: 0.008 sec. Processed 16.36 thousand rows, 98.17 KB (2.15 million rows/s., 12.92 MB/s.)
Peak memory usage: 4.06 MiB.
EXPLAIN 명령으로 프로젝션이 이 쿼리를 제공하는 데 사용되었음을 확인합니다:
EXPLAIN indexes = 1
SELECT avg(Score)
FROM comments
WHERE UserId = 8592047
┌─explain─────────────────────────────────────────────┐
1. │ Expression ((Projection + Before ORDER BY)) │
2. │ Aggregating │
3. │ Filter │
4. │ ReadFromMergeTree (comments_user_id) │
5. │ Indexes: │
6. │ PrimaryKey │
7. │ Keys: │
8. │ UserId │
9. │ Condition: (UserId in [8592047, 8592047]) │
10. │ Parts: 2/2 │
11. │ Granules: 2/11360 │
└─────────────────────────────────────────────────────┘
11 rows in set. Elapsed: 0.004 sec.
언제 프로젝션을 사용할까 (When to use projections)
프로젝션은 데이터가 삽입될 때 자동으로 유지되므로 새 사용자에게 매력적인 기능입니다. 게다가 쿼리를 단일 테이블로 보내기만 하면, 가능한 곳에서 프로젝션이 활용되어 응답 시간을 단축합니다.
이는 머티리얼라이즈드 뷰와 대조적입니다. 머티리얼라이즈드 뷰에서는 필터에 따라 적절한 최적화된 대상 테이블을 선택하거나 쿼리를 재작성해야 합니다. 이는 사용자 애플리케이션에 더 큰 부담을 주고 클라이언트 측 복잡성을 증가시킵니다.
이러한 장점에도 불구하고 프로젝션은 몇 가지 내재된 제한이 있으므로, 이를 알고 있어야 하며 아껴서 배포해야 합니다. 자세한 내용은 "materialized views versus projections"를 참고하세요.
다음과 같은 경우 프로젝션 사용을 권장합니다:
- 데이터의 완전한 재정렬이 필요한 경우. 이론적으로 프로젝션의 표현식은
GROUP BY를 사용할 수 있지만, 집계를 유지하는 데는 머티리얼라이즈드 뷰가 더 효과적입니다. 쿼리 최적화기도 단순 재정렬(즉,SELECT * ORDER BY x)을 사용하는 프로젝션을 활용할 가능성이 더 높습니다. 이 표현식에서 컬럼 부분집합을 선택해 저장 공간을 줄일 수 있습니다. - 사용자가 잠재적인 스토리지 증가와 데이터를 두 번 쓰는 오버헤드를 받아들일 수 있는 경우. 삽입 속도에 미치는 영향을 테스트하고 스토리지 오버헤드를 평가하세요.
BigQuery 쿼리를 ClickHouse에서 재작성하기 (Rewriting BigQuery queries in ClickHouse)
다음은 BigQuery와 ClickHouse를 비교하는 예시 쿼리를 제공합니다. 이 목록은 ClickHouse 기능을 활용해 쿼리를 크게 단순화하는 방법을 보여주기 위한 것입니다. 여기 예시들은 전체 Stack Overflow 데이터셋(2024년 4월까지)을 사용합니다.
가장 많은 조회수를 받는 (10개 이상의 질문이 있는) 사용자: BigQuery
ClickHouse
SELECT
OwnerDisplayName,
sum(ViewCount) AS total_views
FROM stackoverflow.posts
WHERE (PostTypeId = 'Question') AND (OwnerDisplayName != '')
GROUP BY OwnerDisplayName
HAVING count() > 10
ORDER BY total_views DESC
LIMIT 5
┌─OwnerDisplayName─┬─total_views─┐
1. │ Joan Venge │ 25520387 │
2. │ Ray Vega │ 21576470 │
3. │ anon │ 19814224 │
4. │ Tim │ 19028260 │
5. │ John │ 17638812 │
└──────────────────┴─────────────┘
5 rows in set. Elapsed: 0.076 sec. Processed 24.35 million rows, 140.21 MB (320.82 million rows/s., 1.85 GB/s.)
Peak memory usage: 323.37 MiB.
어떤 태그가 가장 많은 조회수를 받나요: BigQuery
ClickHouse
-- ClickHouse
SELECT
arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tags,
sum(ViewCount) AS views
FROM stackoverflow.posts
GROUP BY tags
ORDER BY views DESC
LIMIT 5
┌─tags───────┬──────views─┐
1. │ javascript │ 8190916894 │
2. │ python │ 8175132834 │
3. │ java │ 7258379211 │
4. │ c# │ 5476932513 │
5. │ android │ 4258320338 │
└────────────┴────────────┘
5 rows in set. Elapsed: 0.318 sec. Processed 59.82 million rows, 1.45 GB (188.01 million rows/s., 4.54 GB/s.)
Peak memory usage: 567.41 MiB.
집계 함수 (Aggregate functions)
가능한 경우 ClickHouse 집계 함수를 활용해야 합니다. 아래에서는 argMax 함수를 사용해 각 연도의 가장 많이 조회된 질문을 계산하는 것을 보여줍니다.
BigQuery
ClickHouse
-- ClickHouse
SELECT
toYear(CreationDate) AS Year,
argMax(Title, ViewCount) AS MostViewedQuestionTitle,
max(ViewCount) AS MaxViewCount
FROM stackoverflow.posts
WHERE PostTypeId = 'Question'
GROUP BY Year
ORDER BY Year ASC
FORMAT Vertical
Row 1:
──────
Year: 2008
MostViewedQuestionTitle: How to find the index for a given item in a list?
MaxViewCount: 6316987
Row 2:
──────
Year: 2009
MostViewedQuestionTitle: How do I undo the most recent local commits in Git?
MaxViewCount: 13962748
...
Row 16:
───────
Year: 2023
MostViewedQuestionTitle: How do I solve "error: externally-managed-environment" every time I use pip 3?
MaxViewCount: 506822
Row 17:
───────
Year: 2024
MostViewedQuestionTitle: Warning "Third-party cookie will be blocked. Learn more in the Issues tab"
MaxViewCount: 66975
17 rows in set. Elapsed: 0.225 sec. Processed 24.35 million rows, 1.86 GB (107.99 million rows/s., 8.26 GB/s.)
Peak memory usage: 377.26 MiB.
조건문과 배열 (Conditionals and arrays)
조건 및 배열 함수는 쿼리를 훨씬 더 단순하게 만듭니다. 다음 쿼리는 2022년에서 2023년 사이에 가장 큰 백분율 증가를 보인 (10000개 이상의 발생이 있는) 태그를 계산합니다. 아래 ClickHouse 쿼리가 조건문, 배열 함수, HAVING과 SELECT 절에서 별칭을 재사용하는 능력 덕분에 얼마나 간결한지 주목하세요.
BigQuery
ClickHouse
SELECT
arrayJoin(arrayFilter(t -> (t != ''), splitByChar('|', Tags))) AS tag,
countIf(toYear(CreationDate) = 2023) AS count_2023,
countIf(toYear(CreationDate) = 2022) AS count_2022,
((count_2023 - count_2022) / count_2022) * 100 AS percent_change
FROM stackoverflow.posts
WHERE toYear(CreationDate) IN (2022, 2023)
GROUP BY tag
HAVING (count_2022 > 10000) AND (count_2023 > 10000)
ORDER BY percent_change DESC
LIMIT 5
┌─tag─────────┬─count_2023─┬─count_2022─┬──────percent_change─┐
│ next.js │ 13788 │ 10520 │ 31.06463878326996 │
│ spring-boot │ 16573 │ 17721 │ -6.478189718413183 │
│ .net │ 11458 │ 12968 │ -11.644046884639112 │
│ azure │ 11996 │ 14049 │ -14.613139725247349 │
│ docker │ 13885 │ 16877 │ -17.72826924216389 │
└─────────────┴────────────┴────────────┴─────────────────────┘
5 rows in set. Elapsed: 0.096 sec. Processed 5.08 million rows, 155.73 MB (53.10 million rows/s., 1.63 GB/s.)
Peak memory usage: 410.37 MiB.
이것으로 BigQuery에서 ClickHouse로 마이그레이션하는 기본 가이드를 마칩니다. 고급 ClickHouse 기능에 대해 더 배우려면 ClickHouse에서 데이터 모델링 가이드를 읽을 것을 권장합니다.