初めてのPROJECTIONを作成する

ClickHouse でPROJECTIONを使用して、同じテーブル内にデータの別のソート済みコピーを格納し、主キーに含まれないカラムに対する高速なルックアップを可能にする方法を学びます。

すべてのクイックスタート

リアルタイム分析データウェアハウジングオブザーバビリティAI/MLCloudOSS

前提条件

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

このガイドは uk_price_paid テーブルと、そこで導入された概念を直接土台としているため、以下のクイックスタートも完了しておいてください。

• 最初のMergeTreeテーブルを作成する
• 最初のmaterialized viewを作成する

作成するもの

MergeTree のクイックスタートでは、テーブルが (postcode, addr1, addr2) でソートされているため、town または county で uk_price_paid をクエリすると、テーブル全体のスキャンが必要になることを見てきました。 このクイックスタートでは、PROJECTION を作成してこの問題を解決します。PROJECTION は、同じテーブルの内部に保存される、追加のソート済みデータ表現です。materialized view とは異なり、PROJECTION では別個の宛先テーブルは不要で、mutation (削除や更新) とも同期が保たれ、クエリオプティマイザによって透過的に利用されるため、引き続き同じテーブル名に対してクエリできます。 最後には、PROJECTION を追加して materialize する方法、ClickHouse がそれをどのように自動選択するか、そして materialized view ではなく PROJECTION を選ぶべき場面を理解できるようになります。

1. projection が必要な理由を理解する

   uk_price_paid テーブルは (postcode, addr1, addr2) でソートされています。つまり、postcode、addr1、addr2 でフィルタする場合、ClickHouse は大きなデータブロックをスキップできますが、town でフィルタするクエリでは 3,000 万行すべてをスキャンしなければなりません。

   projection は、同じテーブル内に (一部またはすべての) カラムの追加のソート済みコピーを保持します。テーブルにクエリを実行すると、クエリオプティマイザは projection から読み取ったほうがベースデータより少ない granule で済むかどうかを自動的に確認し、該当する場合は透過的にそれを使用します。

   materialized view との主な違い:

   • 別のテーブルは不要 - projection は uk_price_paid 自体の中にあります
   • 透過的なクエリ最適化 - 通常どおり uk_price_paid をクエリするだけで、ClickHouse が自動的に projection を選択します
   • mutation と同期された状態を維持 - テーブルに適用された削除や更新は projection に反映されます

   詳しくは、projection のリファレンスドキュメントを参照してください。

2. テーブルにプロジェクションを追加する

   uk_price_paid に、town、date、price、type を (town, date) でソートして保存するプロジェクションを定義します。

   ALTER TABLE uk_price_paid
       ADD PROJECTION uk_price_paid_by_town
       (
           SELECT town, date, price, type
           ORDER BY (town, date)
       );
   

   これにより、projection はテーブルのメタデータに登録されますが、既存データに対してはマテリアライズされません。反映されるのは今後の insert のみです。

   projection がテーブル定義に表示されていることを確認します:

   SHOW CREATE TABLE uk_price_paid;
   

   出力に PROJECTION uk_price_paid_by_town ブロックが表示されるはずです。

3. 既存データのプロジェクションをマテリアライズする

   materialized view と同様に、新しく追加したプロジェクションが適用されるのは今後の insert のみです。テーブルにすでにある 3,000 万行にも適用するには、明示的にマテリアライズします:

   ALTER TABLE uk_price_paid
       MATERIALIZE PROJECTION uk_price_paid_by_town;
   

   これはバックグラウンドミューテーションとして実行されます。進捗は次のように確認できます:

   SELECT
       mutation_id,
       command,
       is_done
   FROM system.mutations
   WHERE table = 'uk_price_paid'
   ORDER BY create_time DESC
   LIMIT 5;
   

   is_done = 1 になれば、プロジェクションのマテリアライズは完了です。system.projection_parts を確認して確かめることもできます:

   SELECT
       name,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
   FROM system.projection_parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY name;
   
4. テーブルにクエリを実行し、自動プロジェクションの使用を確認する

   では、これまでクエリしてきた同じテーブルに対して、town で絞り込むクエリを実行します:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   クエリ統計を確認すると、プロジェクション作成前に比べて読み取られる行数が大幅に減っていることがわかります。これは、ClickHouse がベースデータをスキャンする代わりに、uk_price_paid_by_town プロジェクションから読み取るよう自動的に選択したためです。

   EXPLAIN を使えば、プロジェクションが使用されたことを確認できます。

   EXPLAIN
   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   出力内で、プロジェクション名を参照している ReadFromMergeTree を探します。パフォーマンスを明確に比較したい場合は、単一のクエリに対してプロジェクションの最適化を無効にできます。

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC
   SETTINGS optimize_use_projections = 0;
   

   これによりテーブル全体がスキャンされるため、読み取られる行数の違いを確認できます。

5. プロジェクションとmaterialized viewの比較

   プロジェクションとmaterialized viewは、どちらも同じ課題、つまり異なるアクセスパターンでの読み取りを高速化するという課題を解決しますが、そのためのトレードオフは異なります。要するに、プロジェクションは同じデータに対して別のソート順が必要なだけの場合に最適です。一方、materialized viewは、データの変換や集約、あるいは別のスキーマへの振り分けが必要な場合に、より柔軟に対応できます。詳しい比較については、materialized view とプロジェクションの比較を参照してください。

6. ストレージのオーバーヘッドを確認する

   プロジェクションは、選択したカラムのコピーを同じテーブル内にもう1つ保存するため、ディスク使用量が増加します。system.parts をクエリして、uk_price_paid の合計サイズ (プロジェクションのデータも含まれるようになっています) を確認します。

   SELECT
       table,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS compressed_size
   FROM system.parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY table;
   

   プロジェクション専用のストレージも確認できます。

   SELECT
       name,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS projection_size
   FROM system.projection_parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY name;
   

   これはmaterialized viewと同じ基本的なトレードオフです。読み取りを高速化する代わりに、より多くのディスク領域が必要になります。プロジェクションは、選択した4つのカラムのみを含み、圧縮率もソート順によって変わるため、完全なコピーより小さくなる場合があります。

次のステップ

このクイックスタートでは、uk_price_paid に (town, date) でソートされたデータを格納するPROJECTIONを追加し、別のテーブルを作成せずに town による高速なルックアップを可能にしました。また、PROJECTIONはクエリオプティマイザによって透過的に選択され、ミューテーションとの同期が保たれ、ディスク容量を消費する代わりに読み取り性能を向上させることを学びました。

次に、以下のクイックスタートもご覧ください。

または、リファレンスドキュメントでさらに詳しく確認できます。

• materialized view とPROJECTIONの比較
• PROJECTIONのリファレンス
• MergeTree テーブルエンジン

すべてのクイックスタート