初めての materialized view を作成する

ClickHouse の materialized view を使って、別のソート順でクエリ結果を事前計算して保存し、主キーに含まれないカラムに対する高速なルックアップを可能にする方法を学びます。

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

リアルタイムアナリティクスデータウェアハウジングオブザーバビリティ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 テーブルをそのまま使用するため、Create your first MergeTree table クイックスタートも完了しておく必要があります。

作成するもの

MergeTree の クイックスタート では、テーブルが (postcode, addr1, addr2) でソートされているため、town または county で uk_price_paid をクエリするにはテーブル全体のスキャンが必要になることを確認しました。 この クイックスタート では、その問題を解決するために、同じデータを (town, date) でソートして保存する materialized view を作成し、元のテーブルを変更せずに town による高速なルックアップを可能にします。 最後には、materialized view が insert trigger としてどのように機能するか、既存データをどのようにバックフィルするか、そしてデータを 2 回保存することによるディスク容量のトレードオフを理解できるようになります。

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

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

   別の ORDER BY を持つ 2 つ目のテーブルを作成することもできますが、その場合は新しいデータが届くたびに、両方のテーブルに insert する必要があります。materialized view を使えば、これを自動化できます。source table への insert を監視し、行を変換して、宛先テーブルへ自動的に書き込みます。

   materialized view は insert trigger のようなものと考えてください。source table に行が insert されるたびに、MV の SELECT クエリが新しい block に対して実行され、その結果が宛先テーブルに insert されます。

2. 宛先テーブルを作成する

   materialized view では、出力の保存先が必要です。これは通常の MergeTree テーブルであり、スキーマ、ORDER BY、PARTITION BY を完全に自由に制御できます。

   町単位のクエリに必要なカラムだけを含む、(town, date) でソートされたテーブルを作成します:

   CREATE TABLE uk_price_paid_by_town
   (
       town       LowCardinality(String),
       date       Date,
       price      UInt32,
       type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (town, date);
   

   このテーブルに特別な点はなく、標準的なMergeTreeテーブルです。次に作成するmaterialized viewは、単にデータをこのテーブルにルーティングするだけです。

   テーブルが作成されたことを確認します:

   SHOW CREATE TABLE uk_price_paid_by_town;
   
3. materialized view を作成する

   次に、ソーステーブル (uk_price_paid) と宛先テーブル (uk_price_paid_by_town) を結び付ける materialized view を作成します。

   CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
   TO uk_price_paid_by_town
   AS SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   TO uk_price_paid_by_town 句は、SELECT の出力を宛先テーブルに書き込むよう ClickHouse に指示します。以後、uk_price_paid に行が挿入されるたびに、この MV がトリガーされ、変換後の行を uk_price_paid_by_town に挿入します。

   重要な注意点があります。materialized view がトリガーされるのは inserts のときだけです。ソーステーブルの行を削除または更新しても、宛先テーブルはそれを認識しません。つまり、MV は削除や更新とは同期されません。そのような同期が必要な場合は、代わりに projections の使用を検討してください。

4. 既存データをバックフィルする

   materialized view が処理するのは 今後の 挿入のみです。uk_price_paid にすでに存在する 3,000 万行は、MV が作成される前に挿入されていたため、宛先テーブルは現在空のままです。

   手動でバックフィルします:

   INSERT INTO uk_price_paid_by_town
   SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   これは宛先テーブルに直接挿入されます。このステップでは MV は関与しません。完了したら、行数が一致していることを確認してください:

   SELECT
       'uk_price_paid' AS table,
       count() AS rows
   FROM uk_price_paid
   UNION ALL
   SELECT
       'uk_price_paid_by_town' AS table,
       count() AS rows
   FROM uk_price_paid_by_town;
   

   どちらのテーブルも、行数は同じである必要があります。

5. materialized viewの宛先テーブルをクエリする

   次に、宛先テーブルに対して 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;
   

   クエリ統計を確認してください。town がソーステーブルの ORDER BY に含まれていないため、3,000万行すべてが読み込まれます。

   次に、materialized view の宛先テーブルに対して同じクエリを実行します。

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

   クエリ統計をもう一度確認してください。宛先テーブルは (town, date) でソートされているため、ClickHouse は LONDON に一致しないデータをすべてスキップでき、読み取る行数が大幅に減ります。

   作成されたものを確認するには、SHOW TABLES を実行します。

   SHOW TABLES;
   

   uk_price_paid_by_town (宛先テーブル) と uk_price_paid_by_town_mv (ビュー) の両方が表示されます。CREATE MATERIALIZED VIEW ... TO を使用しているため、宛先テーブル名は自分で指定できます。TO 句を省略すると、ClickHouse は暗黙的な名前の宛先テーブル (.inner.xxx) を作成しますが、これは直接扱いにくくなります。 そのため、materialized view は TO 句を使って作成することを推奨します。

6. データが二重に保存されていることを確認する

   materialized view を使うと、追加のディスク容量と引き換えに、より高速に読み取れます。各テーブルがどれだけの容量を使用しているかを確認するには、system.parts をクエリします。

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

   データは物理的に2回保存されます。1つは (postcode, addr1, addr2) でソートされた uk_price_paid に、もう1つは (town, date) でソートされた uk_price_paid_by_town に保存されます。これが基本的なトレードオフです。つまり、異なるアクセスパターンでの読み取りを高速化する代わりに、より多くのディスク容量を使用します。

   宛先テーブルは、含まれるカラム数が少なく、(town, date) のソート順では元のデータとは異なる圧縮が行われる可能性があるため、ディスク上のサイズが小さくなる場合があります。

次のステップ

このクイックスタートでは、元のテーブルを変更することなく、異なるソート順で英国の不動産売買データを保存する materialized view を作成し、町ごとの高速なルックアップを可能にしました。また、MV は insert trigger として機能すること、既存データは手動でバックフィルする必要があること、そしてそのトレードオフとして追加のディスク容量が必要になることも学びました。

続けて、以下のクイックスタートをご覧ください。

• 最初のプロジェクションを作成する

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

• materialized view のリファレンス
• インクリメンタルmaterialized view
• プロジェクション

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