ALTER TABLE ... PROJECTION

このページでは、プロジェクションの概要、使用方法、およびプロジェクションを操作するための各種オプションについて説明します。

プロジェクションの概要

プロジェクションは、クエリ実行を最適化するフォーマットでデータを格納します。この機能は、次のような場合に役立ちます。

主キーに含まれないカラムに対してクエリを実行する
カラムを事前集計し、計算量と IO の両方を削減する

テーブルには 1 つ以上のプロジェクションを定義でき、クエリ解析時には、スキャン対象のデータ量が最も少ないプロジェクションが、ユーザーが指定したクエリを変更することなく 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 を使用すると、既存のテーブルにプロジェクションを追加できます。

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 で高速に絞り込めます。クエリ時には、データが user_name 順に並んでいるため、プロジェクションを使ったほうが処理されるデータ量が少なくなると ClickHouse が判断します。

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

projection を利用するには、事前集計と 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';

含意チェックは保守的です。正規化された式形式に対して、完全に一致する論理積の項だけを照合します。そのため、有効な最適化の機会 (たとえば範囲に関する含意) を一部見逃す可能性はありますが、誤った結果を生成することはありません。

プロジェクションの操作

プロジェクションに対しては、次の操作を実行できます:

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

以下のステートメントを使用すると、既存の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, ...)

PROJECTION索引の場合は、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, ...)

このステートメントではPROJECTION定義全体を再指定しますが、既存の定義と異なってよいのは WITH SETTINGS 句のみです。既存のPROJECTIONパーツにはこの定義に基づいて構築されたデータが格納されているため、PROJECTIONのクエリ自体 (PROJECTION索引の場合は索引式と型) は変更できません。変更するには、DROP PROJECTION の後に ADD PROJECTION を使用します。このコマンドはテーブルのメタデータのみを変更し、データの書き換えは行いません。既存のPROJECTIONパーツは書き込まれた時点の設定を保持し、以降の挿入およびマージで書き込まれるPROJECTIONパーツには新しい設定が使用されます。 MATERIALIZE PROJECTION を実行しても、既存のパーツに新しい設定は適用されません。このコマンドは、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

以下のステートメントを使用して、テーブルのメタデータからPROJECTIONの定義を削除し、ディスク上のPROJECTIONファイルを削除します。これはミューテーションとして実装されています。

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

MATERIALIZE PROJECTION

以下のステートメントを使用して、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ファイルを削除するには、以下のステートメントを使用します。これはミューテーションとして実装されています。

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 でテーブルにプロジェクションを作成する処理がデフォルトで「遅延的」だからです。つまり、新たに挿入されるデータにのみ影響し、既存のパーツは変更されません。いずれかのプロジェクションには事前計算済みの集約値がすでに含まれているため、ClickHouse はクエリ実行時に再度集約を行うことを避けるために、対応するプロジェクションパーツから読み取ろうとします。特定のパーツに対応するプロジェクションパーツがない場合、クエリ実行は元のパーツにフォールバックします。しかし、元のテーブル内の行が、単純ではない data パーツのバックグラウンドマージによって大きく変化した場合はどうなるでしょうか。たとえば、そのテーブルが ReplacingMergeTree テーブルエンジンを使用して保存されているとします。マージ中に同じ行が複数の入力パーツで検出された場合、もっとも新しい行バージョン (もっとも最近に挿入されたパーツのもの) だけが保持され、それ以前のすべてのバージョンは破棄されます。同様に、テーブルが AggregatingMergeTree テーブルエンジンを使用して保存されている場合、マージ操作によって、入力パーツ内の同じ行が主キーの値に基づいて 1 行にまとめられ、部分的な集約状態が更新されることがあります。 ClickHouse v24.8 より前では、プロジェクションパーツはメインデータと気づかれないまま同期がずれるか、あるいは更新や削除などの特定の操作をまったく実行できませんでした。これは、テーブルにプロジェクションがある場合、データベースが自動的に例外を送出していたためです。 v24.8 以降では、新しいテーブルレベル設定 deduplicate_merge_projection_mode により、前述の単純ではないバックグラウンドマージ操作が元のテーブルのパーツで発生した場合の動作を制御できます。 Delete mutations も、元のテーブルのパーツ内の行を削除するパーツ merge 操作の別の例です。v24.7 以降では、論理削除によってトリガーされる delete mutations に関する動作を制御する設定もあります: 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 カラムは物理的に保存されず、クエリ時にその場で計算されるため、ソート式が評価されるプロジェクションパートの書き込み処理では使用できません。代わりに、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

プロジェクションのマージ動作を制御する

制限事項

関連項目