ALTER TABLE ... PROJECTION

이 페이지에서는 프로젝션의 개념, 사용 방법, 그리고 프로젝션을 관리하는 다양한 옵션을 설명합니다.

프로젝션 개요

프로젝션은 쿼리 실행을 최적화하는 포맷으로 데이터를 저장하며, 다음과 같은 경우에 유용합니다.

프라이머리 키(primary key)에 포함되지 않은 컬럼에서 쿼리를 실행할 때
컬럼을 사전 집계해 계산량과 IO를 모두 줄일 때

테이블에는 하나 이상의 프로젝션을 정의할 수 있으며, 쿼리 분석 과정에서 ClickHouse가 사용자 쿼리를 수정하지 않고 스캔할 데이터가 가장 적은 프로젝션을 선택합니다.

디스크 사용량프로젝션은 내부적으로 새로운 숨겨진 테이블을 생성하므로 더 많은 IO와 디스크 공간이 필요합니다. 예를 들어 프로젝션에 다른 프라이머리 키가 정의되어 있으면 원본 테이블의 모든 데이터가 복제됩니다.

프로젝션이 내부적으로 어떻게 동작하는지에 관한 더 자세한 기술 내용은 이 페이지에서 확인할 수 있습니다.

프로젝션 사용

프라이머리 키를 사용하지 않고 필터링하는 예시

테이블 생성:

CREATE TABLE visits_order
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent

ALTER TABLE을 사용하면 기존 테이블(table)에 Projection을 추가할 수 있습니다:

ALTER TABLE visits_order ADD PROJECTION user_name_projection (
    SELECT *
    ORDER BY user_name
)

ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection

데이터 삽입:

INSERT INTO visits_order SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

프로젝션을 사용하면 원본 테이블에서 user_name이 PRIMARY_KEY로 정의되지 않았더라도 user_name으로 빠르게 필터링할 수 있습니다. 쿼리 시점에 ClickHouse는 데이터가 user_name 순으로 정렬되어 있으므로 프로젝션을 사용하는 편이 처리해야 할 데이터가 더 적다고 판단합니다.

SELECT
    *
FROM visits_order
WHERE user_name='test'
LIMIT 2

쿼리가 해당 프로젝션을 사용하는지 확인하려면 system.query_log 테이블을 살펴볼 수 있습니다. projections 필드에는 사용된 프로젝션의 이름이 표시되며, 사용되지 않았다면 비어 있습니다:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

예시 사전 집계 쿼리

projection projection_visits_by_user가 포함된 테이블을 생성합니다:

CREATE TABLE visits
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String,
   PROJECTION projection_visits_by_user
   (
       SELECT
           user_agent,
           sum(pages_visited)
       GROUP BY user_id, user_agent
   )
)
ENGINE = MergeTree()
ORDER BY user_agent

데이터를 삽입하세요:

INSERT INTO visits SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

INSERT INTO visits SELECT
    number,
    'test',
    1. * (number / 2),
   'IOS'
FROM numbers(100, 500);

GROUP BY를 사용해 user_agent 필드로 첫 번째 쿼리를 실행합니다. 사전 집계 조건이 일치하지 않으므로 이 쿼리에서는 정의된 프로젝션을 사용하지 않습니다.

SELECT
    user_agent,
    count(DISTINCT user_id)
FROM visits
GROUP BY user_agent

프로젝션을 활용하려면 사전 집계 결과와 GROUP BY 필드의 일부 또는 전체를 선택하는 쿼리를 실행하면 됩니다:

SELECT
    user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent

SELECT
    user_agent,
    sum(pages_visited)
FROM visits
GROUP BY user_agent

앞서 언급했듯이 프로젝션이 사용되었는지 확인하려면 system.query_log 테이블을 검토할 수 있습니다. projections 필드는 사용된 프로젝션의 이름을 보여줍니다. 프로젝션이 사용되지 않은 경우 이 필드는 비어 있습니다:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

프로젝션 인덱스 생성 및 사용

프로젝션 인덱스 생성:

CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id INDEX user_id TYPE basic
)
ENGINE = MergeTree()
ORDER BY (event_id);

예시 데이터를 몇 개 삽입합니다:

INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;

_part_offset 필드는 머지와 뮤테이션 이후에도 값이 유지되므로 보조 인덱스에 유용합니다. 이를 쿼리에서 활용할 수 있습니다:

SELECT
    count()
FROM events
WHERE _part_starting_offset + _part_offset IN (
    SELECT _part_starting_offset + _part_offset
    FROM events
    WHERE user_id = 42
)
SETTINGS enable_shared_storage_snapshot_in_query = 1

`WHERE` 절이 있는 PROJECTION 예시

PROJECTION에는 일부 행만 저장하도록 WHERE 절을 포함할 수 있습니다. 이는 쿼리에서 정해진 프레디케이트를 기준으로 자주 필터링하는 경우에 유용합니다. PROJECTION은 일치하는 행만 구체화하여 저장하므로 스토리지 사용량을 줄이고 쿼리 성능을 향상합니다. 테이블을 생성하고 필터링된 PROJECTION을 추가합니다:

CREATE TABLE events
(
    `event_type` String,
    `time` DateTime,
    `message` String
)
ENGINE = MergeTree()
ORDER BY time;

ALTER TABLE events ADD PROJECTION proj_pageview (
    SELECT event_type, time, message
    WHERE event_type = 'pageview'
    ORDER BY time
);

ALTER TABLE events MATERIALIZE PROJECTION proj_pageview;

데이터 삽입:

INSERT INTO events VALUES
    ('pageview', '2024-01-01', 'homepage'),
    ('click', '2024-01-02', 'button'),
    ('pageview', '2024-01-03', 'about');

쿼리의 WHERE 절이 PROJECTION의 WHERE 절을 포함하는 경우(즉, PROJECTION 필터의 모든 조건이 쿼리 필터에도 포함된 경우), 옵티마이저는 이것이 유리하다고 판단하면 자동으로 해당 PROJECTION을 사용할 수 있습니다:

-- This query implies the projection's WHERE, so the projection may be used:
SELECT time, message FROM events WHERE event_type = 'pageview';

-- A stricter query also implies the projection's WHERE:
SELECT time, message FROM events WHERE event_type = 'pageview' AND time > '2024-01-01';

-- This query does NOT imply the projection, so the base table is scanned:
SELECT time, message FROM events WHERE event_type = 'click';

함의 검사(implication check)는 보수적으로 동작합니다 — 표현식의 정규 형식에서 각 conjunct를 정확히 일치시키는 방식을 사용합니다. 일부 유효한 최적화 기회(예: 범위 함의)는 놓칠 수 있지만, 잘못된 결과를 생성하지는 않습니다.

프로젝션 관리

다음과 같은 프로젝션 작업을 수행할 수 있습니다:

ADD PROJECTION

아래 문을 사용하여 테이블 메타데이터에 PROJECTION 설명을 추가합니다:

-- Normal projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

-- Aggregate projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

PROJECTION이 WHERE 절을 정의하면 프레디케이트를 만족하는 행만 구체화됩니다. 옵티마이저는 쿼리의 WHERE가 PROJECTION의 WHERE를 논리적으로 함의하고, 해당 PROJECTION이 쿼리 계획에 도움이 되는 경우 이러한 PROJECTION을 사용할 수 있습니다. 이는 일반 PROJECTION과 집계 PROJECTION 모두에 적용됩니다.

`WITH SETTINGS` 절

WITH SETTINGS는 프로젝션 수준 설정을 정의하며, 이를 통해 프로젝션이 데이터를 저장하는 방식을 조정할 수 있습니다(예: index_granularity, index_granularity_bytes). 이 설정은 MergeTree 테이블 설정과 직접 대응되지만, 이 프로젝션에만 적용됩니다. 예시:

ALTER TABLE t
ADD PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 4096,
    index_granularity_bytes = 1048576
);

프로젝션 설정은 검증 규칙에 따라 프로젝션에 적용되는 유효한 테이블 설정을 재정의합니다(예: 잘못되었거나 호환되지 않는 재정의는 거부됩니다).

MODIFY PROJECTION

아래 SQL 문을 사용하면 기존 PROJECTION의 데이터를 재구성하지 않고 WITH SETTINGS 절을 변경할 수 있습니다:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] [ORDER BY] ) WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

프로젝션 인덱스의 경우 SELECT 쿼리 대신 INDEX 선언을 다시 명시합니다:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name INDEX <index_expr> TYPE <index_type> WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

이 SQL 문은 전체 PROJECTION 정의를 다시 명시하지만, 기존 정의와 달라질 수 있는 부분은 WITH SETTINGS 절뿐입니다. 기존 프로젝션 파트에는 이를 기반으로 생성된 데이터가 저장되어 있으므로 PROJECTION 쿼리 자체(또는 프로젝션 인덱스의 경우 인덱스 표현식 및 유형)는 동일하게 유지해야 합니다. 이를 변경하려면 DROP PROJECTION 후 ADD PROJECTION을 사용하십시오. 이 명령은 테이블 메타데이터만 변경하며 데이터를 재작성하지 않습니다. 기존 프로젝션 파트는 기록될 당시의 설정을 유지하며, 이후 삽입 및 머지로 기록되는 프로젝션 파트에는 새 설정이 적용됩니다. MATERIALIZE PROJECTION을 실행해도 기존 파트에는 새 설정이 적용되지 않습니다. 이 명령은 PROJECTION이 누락되었거나 손상된 파트에서만 PROJECTION을 생성하며, 이미 존재하는 프로젝션 파트는 그대로 둡니다. 새 설정으로 기존 파트를 재구성하려면 CLEAR PROJECTION 후 MATERIALIZE PROJECTION을 실행하거나, OPTIMIZE TABLE ... FINAL 등으로 파트를 머지하십시오. 예시:

ALTER TABLE t
MODIFY PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 128
);

DROP PROJECTION

아래 SQL 문을 사용하면 테이블 메타데이터에서 프로젝션 설명을 제거하고 디스크에서 프로젝션 파일을 삭제할 수 있습니다. 이 작업은 뮤테이션으로 구현됩니다.

ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name

MATERIALIZE PROJECTION

아래 SQL 문을 사용하여 PROJECTION name이 없거나 손상된 파트에 해당 PROJECTION을 빌드합니다. 필요한 경우 파티션 partition_name에서만 수행하도록 지정할 수 있습니다. 이미 PROJECTION이 있는 파트는 기록된 이후에 MODIFY PROJECTION으로 PROJECTION 설정이 변경되었더라도 그대로 유지됩니다. 이러한 파트를 재구성하려면 먼저 CLEAR PROJECTION을 실행하십시오. 이 작업은 뮤테이션으로 수행됩니다.

ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

CLEAR PROJECTION

설명은 제거하지 않고 디스크에서 PROJECTION 파일만 삭제하려면 아래 SQL 문을 사용합니다. 이는 뮤테이션으로 구현됩니다.

ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

ADD, MODIFY, DROP, CLEAR 명령은 메타데이터만 변경하거나 파일만 제거하므로 경량입니다. 또한 이러한 명령은 복제되며, ClickHouse Keeper 또는 ZooKeeper를 통해 PROJECTION 메타데이터를 동기화합니다.

PROJECTION 조작은 *MergeTree 엔진을 사용하는 테이블(복제된 변형 포함)에서만 지원됩니다.

프로젝션 머지 동작 제어

쿼리를 실행하면 ClickHouse는 원본 테이블 또는 해당 프로젝션 중 어디에서 읽을지 선택합니다. 원본 테이블과 프로젝션 중 어느 쪽에서 읽을지는 각 테이블 파트별로 개별적으로 결정됩니다. 일반적으로 ClickHouse는 가능한 한 적은 양의 데이터만 읽으려고 하며, 예를 들어 파트의 프라이머리 키를 샘플링하는 등 읽기에 가장 적합한 파트를 식별하기 위해 몇 가지 기법을 사용합니다. 경우에 따라 원본 테이블 파트에 대응하는 프로젝션 파트가 없을 수 있습니다. 예를 들어 SQL에서 테이블의 프로젝션 생성은 기본적으로 “lazy”하게 동작하므로, 새로 삽입된 데이터에만 영향을 주고 기존 파트는 변경하지 않습니다. 프로젝션 중 하나에는 이미 사전 계산된 집계 값이 들어 있으므로, ClickHouse는 쿼리 런타임에 다시 집계하지 않도록 대응하는 프로젝션 파트에서 읽으려고 합니다. 특정 파트에 대응하는 프로젝션 파트가 없으면 쿼리 실행은 원본 파트로 대체됩니다. 그렇다면 원본 테이블의 행이 단순하지 않은 데이터 파트의 백그라운드 머지로 인해 의미 있는 방식으로 변경되면 어떻게 될까요? 예를 들어 테이블이 ReplacingMergeTree 테이블 엔진을 사용한다고 가정해 보겠습니다. 머지 중 여러 입력 파트에서 동일한 행이 발견되면 가장 최신 행 버전(가장 최근에 삽입된 파트의 행)만 유지되고, 이전 버전은 모두 삭제됩니다. 마찬가지로 테이블이 AggregatingMergeTree 테이블 엔진을 사용하는 경우, 머지 작업은 입력 파트의 동일한 행을(프라이머리 키 값을 기준으로) 하나의 행으로 합쳐 부분 집계 상태를 갱신할 수 있습니다. ClickHouse v24.8 이전에는 프로젝션 파트가 메인 데이터와 조용히 불일치 상태가 되거나, 테이블에 프로젝션이 있으면 데이터베이스가 자동으로 예외를 발생시켰기 때문에 UPDATE 및 삭제와 같은 특정 작업을 아예 실행할 수 없었습니다. v24.8부터는 새로운 테이블 수준 설정 deduplicate_merge_projection_mode으로 앞서 설명한 비단순한 백그라운드 머지 작업이 원본 테이블의 파트에서 발생할 때의 동작을 제어할 수 있습니다. 삭제 뮤테이션도 원본 테이블의 파트에서 행을 제거하는 파트 병합 작업의 또 다른 예입니다. v24.7부터는 경량한 삭제에 의해 트리거된 삭제 뮤테이션의 동작을 제어하는 설정 lightweight_mutation_projection_mode도 제공됩니다. 아래는 deduplicate_merge_projection_mode와 lightweight_mutation_projection_mode에 사용할 수 있는 값입니다:

throw (기본값): 예외를 발생시켜 프로젝션 파트가 원본 데이터와 불일치 상태가 되는 것을 방지합니다.
drop: 영향을 받는 프로젝션 테이블 파트가 삭제됩니다. 해당 프로젝션 파트에 대한 쿼리는 원본 테이블 파트로 대체됩니다.
rebuild: 영향을 받은 프로젝션 파트를 다시 빌드하여 원본 테이블 파트의 데이터와 일관성을 유지합니다.

제한 사항

프로젝션의 ORDER BY 절에서는 ALIAS 컬럼을 사용할 수 없습니다. 예시는 다음과 같습니다.

CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 ALIAS a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;
-- UNKNOWN_IDENTIFIER 오류 발생

ALIAS 컬럼은 물리적으로 저장되지 않고 쿼리 시점에 즉시 계산되므로, 정렬 표현식을 평가하는 projection part 쓰기 경로에서는 사용할 수 없습니다. 대신 MATERIALIZED 컬럼을 사용하거나 표현식을 직접 인라인으로 작성하십시오:

-- MATERIALIZED 컬럼 사용
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 MATERIALIZED a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;

-- 인라인 표현식 사용
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;

SQL 참고

데이터 타입

엔진

함수

포맷

설정

시스템 테이블

데이터 레이크

프로젝션 개요

프로젝션 사용

프라이머리 키를 사용하지 않고 필터링하는 예시

예시 사전 집계 쿼리

프로젝션 인덱스 생성 및 사용

`WHERE` 절이 있는 PROJECTION 예시

프로젝션 관리

ADD PROJECTION

`WITH SETTINGS` 절

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

프로젝션 머지 동작 제어

제한 사항

관련 항목

​프로젝션 개요

​프로젝션 사용

​프라이머리 키를 사용하지 않고 필터링하는 예시

​예시 사전 집계 쿼리

​프로젝션 인덱스 생성 및 사용

​WHERE 절이 있는 PROJECTION 예시

​프로젝션 관리

​ADD PROJECTION

​WITH SETTINGS 절

​MODIFY PROJECTION

​DROP PROJECTION

​MATERIALIZE PROJECTION

​CLEAR PROJECTION

​프로젝션 머지 동작 제어

​제한 사항

​관련 항목

프로젝션 개요

프로젝션 사용

프라이머리 키를 사용하지 않고 필터링하는 예시

예시 사전 집계 쿼리

프로젝션 인덱스 생성 및 사용

`WHERE` 절이 있는 PROJECTION 예시

프로젝션 관리

ADD PROJECTION

`WITH SETTINGS` 절

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

프로젝션 머지 동작 제어

제한 사항

관련 항목