ALTER TABLE ... PROJECTION

ALTER TABLE ... PROJECTION

이 페이지는 프로젝션(projection)이 무엇인지, 어떻게 사용하는지, 그리고 프로젝션을 조작하는 다양한 옵션을 다룹니다.

출처: 문서

본문

Overview of projections

프로젝션은 쿼리 실행을 최적화하는 형식으로 데이터를 저장해요. 이 기능은 다음에 유용합니다:

• 기본 키의 일부가 아닌 컬럼에 대한 쿼리 실행
• 컬럼 사전 집계(pre-aggregation) — 계산과 IO를 모두 줄여줌

테이블에 하나 이상의 프로젝션을 정의할 수 있고, 쿼리 분석 중에 ClickHouse는 사용자가 제공한 쿼리를 수정하지 않고 스캔할 데이터가 가장 적은 프로젝션을 선택해요.

Disk usage

프로젝션은 내부적으로 새 숨겨진 테이블을 만들며, 이는 더 많은 IO와 디스크 공간이 필요함을 의미해요. 예를 들어 프로젝션이 다른 기본 키를 정의했다면 원래 테이블의 모든 데이터가 복제됩니다.

프로젝션이 내부적으로 어떻게 동작하는지에 대한 더 기술적 세부 사항은 이 page에서 볼 수 있어요.

Using projections

Example filtering without using primary keys

테이블 만들기:

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을 사용해 기존 테이블에 프로젝션을 추가할 수 있어요.

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>'

Example pre-aggregation query

프로젝션 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);

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

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>'

Creating and using projection indexes

프로젝션 인덱스 만들기:

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 필드는 병합과 mutation을 통해 값을 유지하므로 보조 인덱싱에 유용합니다. 쿼리에서 이를 활용할 수 있어요:

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

Example projection with WHERE clause

프로젝션은 행의 일부만 저장하도록 WHERE 절을 포함할 수 있어요. 쿼리가 알려진 술어로 자주 필터링할 때 유용합니다 — 프로젝션은 일치하는 행만 매터리얼라이즈해 저장 공간을 줄이고 쿼리 성능을 개선합니다.

테이블을 만들고 필터링된 프로젝션 추가:

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 절이 프로젝션의 WHERE 절을 함의하면(즉, 프로젝션 필터의 모든 조건이 쿼리 필터에도 존재하면), 옵티마이저는 그것이 이로울 때 자동으로 프로젝션을 사용할 수 있어요:

-- 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';

함의 검사는 보수적입니다 — 표준 표현식 형태의 정확한 결합(conjunct) 매칭을 사용합니다. 일부 유효한 최적화 기회(예: 범위 함의)를 놓칠 수 있지만, 잘못된 결과를 만들지는 않습니다.

Manipulating projections

프로젝션과 관련된 다음 연산을 사용할 수 있어요:

ADD 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, ...)]

프로젝션이 WHERE 절을 정의하면 술어와 일치하는 행만 매터리얼라이즈됩니다. 옵티마이저는 쿼리의 WHERE가 논리적으로 프로젝션의 WHERE를 함의하고 프로젝션이 쿼리 계획에 유익할 때 그러한 프로젝션을 사용할 수 있어요. 이는 일반 및 집계 프로젝션 모두에 적용됩니다.

WITH SETTINGS Clause

WITH SETTINGS는 프로젝션 수준 설정을 정의하는데, 이는 프로젝션이 데이터를 저장하는 방식을 사용자 정의합니다(예: index_granularity 또는 index_granularity_bytes). 이들은 MergeTree 테이블 설정에 직접 대응하지만 이 프로젝션에만 적용됩니다.

Example:

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

프로젝션 설정은 검증 규칙에 따라 프로젝션에 대한 유효 테이블 설정을 덮어씁니다(예: 유효하지 않거나 호환되지 않는 덮어쓰기는 거부됩니다).

MODIFY 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, ...)

이 문은 전체 프로젝션 정의를 다시 진술하지만, WITH SETTINGS 절만 기존 정의와 다를 수 있어요. 프로젝션 쿼리 자체(또는 프로젝션 인덱스의 경우 인덱스 표현식과 타입)는 같아야 합니다. 기존 프로젝션 파트가 그것으로부터 만들어진 데이터를 저장하기 때문입니다. 그것을 변경하려면 DROP PROJECTION 후 ADD PROJECTION을 사용하세요.

이 명령은 테이블 메타데이터만 변경하고 데이터를 다시 쓰지 않습니다. 기존 프로젝션 파트는 쓰여졌던 설정을 유지하고, 미래의 삽입과 병합이 쓰는 프로젝션 파트는 새 설정을 사용합니다. 기존 파트를 새 설정으로 다시 빌드하려면 MATERIALIZE PROJECTION을 실행하세요.

Example:

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

DROP PROJECTION

아래 문으로 테이블 메타데이터에서 프로젝션 설명을 제거하고 디스크에서 프로젝션 파일을 삭제해요. 이것은 mutation으로 구현됩니다.

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

MATERIALIZE PROJECTION

아래 문으로 partition_name 파티션에서 프로젝션 name을 다시 빌드해요. 이것은 mutation으로 구현됩니다.

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

CLEAR PROJECTION

아래 문으로 설명을 제거하지 않고 디스크에서 프로젝션 파일을 삭제해요. 이것은 mutation으로 구현됩니다.

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

ADD, MODIFY, DROP, CLEAR 명령은 메타데이터만 변경하거나 파일만 제거한다는 점에서 가볍습니다. 또한 ClickHouse Keeper 또는 ZooKeeper를 통해 프로젝션 메타데이터를 동기화하므로 복제됩니다.

프로젝션 조작은 *MergeTree 엔진의 테이블(복제(replicated) 변형 포함)에서만 지원됩니다.

Controlling projection merge behavior

쿼리를 실행할 때 ClickHouse는 원래 테이블 또는 그 프로젝션 중 하나에서 읽을지 선택합니다. 원래 테이블 또는 프로젝션 중 하나에서 읽을지 결정은 각 테이블 파트마다 개별적으로 이루어집니다. ClickHouse는 일반적으로 가능한 한 적은 데이터를 읽는 것을 목표로 하며, 파트의 기본 키 샘플링 같은 몇 가지 방법으로 읽을 최상의 파트를 식별합니다. 어떤 경우에는 소스 테이블 파트에 대응하는 프로젝션 파트가 없습니다. 이것은 예를 들어 SQL로 테이블에 프로젝션을 만드는 것이 기본적으로 "lazy"이기 때문에 일어날 수 있습니다. 새로 삽입된 데이터에만 영향을 주고 기존 파트는 그대로 둡니다.

프로젝션 중 하나가 이미 계산된 집계 값을 포함하므로, ClickHouse는 쿼리 실행 시 다시 집계하는 것을 피하기 위해 대응하는 프로젝션 파트에서 읽으려고 합니다. 특정 파트에 대응하는 프로젝션 파트가 없으면 쿼리 실행은 원래 파트로 폴백합니다.

그런데 원래 테이블의 행이 비자명한 데이터 파트 백그라운드 병합에 의해 비자명한 방식으로 변경되면 어떻게 될까요? 예를 들어 테이블이 ReplacingMergeTree 테이블 엔진으로 저장되었다고 가정해요. 병합 중 여러 입력 파트에서 같은 행이 감지되면 가장 최근 행 버전(가장 최근에 삽입된 파트에서)만 유지되고, 모든 이전 버전은 버려집니다.

마찬가지로 테이블이 AggregatingMergeTree 테이블 엔진으로 저장되면 병합 연산이 입력 파트의 같은 행(기본 키 값 기반)을 단일 행으로 접어 부분 집계 상태를 업데이트할 수 있어요.

ClickHouse v24.8 이전에는 프로젝션 파트가 메인 데이터와 조용히 동기화되지 않거나, 테이블에 프로젝션이 있으면 데이터베이스가 자동으로 예외를 던져 update/delete 같은 특정 연산이 전혀 실행될 수 없었습니다.

v24.8 이후, 새 테이블 수준 설정 deduplicate_merge_projection_mode이 원래 테이블의 파트에서 앞서 언급한 비자명한 백그라운드 병합 연산이 발생할 때의 동작을 제어합니다.

Delete mutation은 원래 테이블의 파트에서 행을 버리는 파트 병합 연산의 또 다른 예입니다. v24.7 이후, 경량 삭제로 트리거된 delete mutation에 대한 동작을 제어하는 설정도 있습니다: lightweight_mutation_projection_mode.

아래는 deduplicate_merge_projection_mode와 lightweight_mutation_projection_mode 둘 다의 가능한 값입니다:

• throw (기본값): 예외가 던져져 프로젝션 파트가 동기화에서 벗어나는 것을 방지합니다.
• drop: 영향을 받은 프로젝션 테이블 파트가 버려집니다. 쿼리는 영향을 받은 프로젝션 파트에 대해 원래 테이블 파트로 폴백합니다.
• rebuild: 영향을 받은 프로젝션 파트가 원래 테이블 파트의 데이터와 일관되도록 다시 빌드됩니다.

Limitations

프로젝션의 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;
-- Fails with UNKNOWN_IDENTIFIER

ALIAS 컬럼은 물리적으로 저장되지 않고 쿼리 시점에 즉시 계산되므로, 정렬 표현식이 평가될 때 프로젝션 파트 쓰기 경로에서 사용할 수 없습니다.

대신 MATERIALIZED 컬럼을 사용하거나 표현식을 직접 인라인하세요:

-- using MATERIALIZED column
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;

-- using an inline expression
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;

See also

• "Control Of Projections During Merges" (blog post)
• "Projections" (guide)
• "Materialized Views versus Projections"

더 알아보기 (Learn more)

• 프로젝션 가이드
• MergeTree 프로젝션