ClickHouse 데이터 업데이트·삭제하기

ClickHouse 데이터 업데이트·삭제하기 (Mutation)

ClickHouse는 대용량 분석 워크로드에 최적화되어 있지만, 상황에 따라 기존 데이터를 수정하거나 삭제할 수도 있어요. 이런 작업을 "mutation(뮤테이션)"이라고 부르며 ALTER TABLE 명령으로 실행해요.

출처: Updating and deleting ClickHouse data

본문

ClickHouse는 대용량 분석 워크로드에 맞춰져 있지만, 상황에 따라서는 기존 데이터를 수정하거나 삭제하는 것도 가능해요. 이러한 작업을 mutation이라고 부르고, ALTER TABLE 명령으로 실행해요.

빈번한 업데이트가 필요하다면 mutation 이벤트를 만들지 않고도 행을 업데이트/삭제할 수 있는 deduplication(중복 제거)을 고려해 보세요. 대안으로 lightweight updates(경량 업데이트)나 lightweight deletes(경량 삭제)를 사용할 수도 있어요.

데이터 업데이트하기

테이블의 행을 업데이트하려면 ALTER TABLE...UPDATE 명령을 사용하세요:

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

<expression>은 <filter_expr> 조건을 만족하는 행의 컬럼에 들어갈 새 값이에요. <expression>은 컬럼과 동일한 데이터 타입이거나, CAST 연산자로 같은 타입으로 변환될 수 있어야 해요. <filter_expr>은 데이터의 각 행에 대해 UInt8(0 또는 0이 아닌 값)을 반환해야 해요. 하나의 ALTER TABLE 명령에 UPDATE <column> 문 여러 개를 쉼표로 구분해 조합할 수도 있어요.

예시:

아래 같은 mutation은 사전(dictionary) 조회를 이용해 visitor_ids를 새 값으로 교체해요:

ALTER TABLE website.clicks
UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
WHERE visit_date < '2022-01-01'

하나의 명령에서 여러 값을 수정하는 게 여러 명령을 쓰는 것보다 효율적일 수 있어요:

ALTER TABLE website.clicks
UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
WHERE visit_date < '2022-01-01'

샤딩된 테이블에서는 mutation을 ON CLUSTER로 실행할 수 있어요:

ALTER TABLE clicks ON CLUSTER main_cluster
UPDATE click_count = click_count / 2
WHERE visitor_id ILIKE '%robot%'

기본 키나 정렬 키의 일부인 컬럼은 업데이트할 수 없어요.

데이터 삭제하기

행을 삭제하려면 ALTER TABLE 명령을 사용하세요:

ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>

<filter_expr>은 각 데이터 행에 대해 UInt8 값을 반환해야 해요. 예시:

컬럼이 값 배열에 포함된 레코드를 모두 삭제하려면:

ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)

이 쿼리는 무엇을 바꿀까요?

ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'

테이블의 모든 데이터를 삭제하려면 TRUNCATE TABLE [<database].]<table> 명령을 사용하는 게 더 효율적이에요. 이 명령도 ON CLUSTER로 실행할 수 있어요.

자세한 내용은 DELETE 문 문서를 확인해 보세요.

경량 삭제 (Lightweight deletes)

행을 삭제하는 또 다른 방법은 DELETE FROM 명령을 사용하는 것이며, 이를 lightweight delete(경량 삭제) 라고 해요. 삭제된 행은 즉시 삭제된 것으로 표시되고 이후 모든 쿼리에서 자동으로 걸러지기 때문에, 파트 병합(merging of parts)을 기다리거나 FINAL 키워드를 사용할 필요가 없어요. 데이터 정리는 백그라운드에서 비동기로 이루어져요.

DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]

예를 들어 다음 쿼리는 hits 테이블에서 Title 컬럼에 hello 텍스트가 들어 있는 모든 행을 삭제해요:

DELETE FROM hits WHERE Title LIKE '%hello%';

경량 삭제에 대한 몇 가지 참고 사항이에요:

이 기능은 MergeTree 테이블 엔진 계열에서만 사용할 수 있어요.
경량 삭제는 기본적으로 동기(synchronous) 방식이라, 모든 레플리카가 삭제를 처리할 때까지 기다려요. 이 동작은 lightweight_deletes_sync 설정으로 제어해요.

ClickHouse 데이터 업데이트·삭제하기