To successfully follow this guide, you'll need the following:
* A running ClickHouse Cloud service. If you don't have one yet, complete the [ClickHouse Cloud quick start](/docs/get-started/setup/cloud) first.
また、このガイドはそこで作成した `uk_price_paid` テーブルをそのまま使用するため、[Create your first MergeTree table](/docs/ja/get-started/quickstarts/create-your-first-mergetree-table) クイックスタートも完了しておく必要があります。
## 作成するもの
MergeTree の クイックスタート では、テーブルが `(postcode, addr1, addr2)` でソートされているため、`town` または `county` で `uk_price_paid` をクエリするにはテーブル全体のスキャンが必要になることを確認しました。
この クイックスタート では、その問題を解決するために、同じデータを `(town, date)` でソートして保存する **materialized view** を作成し、元のテーブルを変更せずに town による高速なルックアップを可能にします。
最後には、materialized view が insert trigger としてどのように機能するか、既存データをどのようにバックフィルするか、そしてデータを 2 回保存することによるディスク容量のトレードオフを理解できるようになります。
`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 されます。
materialized view では、出力の保存先が必要です。これは通常の MergeTree テーブルであり、スキーマ、`ORDER BY`、`PARTITION BY` を完全に自由に制御できます。
町単位のクエリに必要なカラムだけを含む、`(town, date)` でソートされたテーブルを作成します:
```sql theme={null}
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は、単にデータをこのテーブルにルーティングするだけです。
テーブルが作成されたことを確認します:
```sql theme={null}
SHOW CREATE TABLE uk_price_paid_by_town;
```
次に、ソーステーブル (`uk_price_paid`) と宛先テーブル (`uk_price_paid_by_town`) を結び付ける materialized view を作成します。
```sql theme={null}
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](/docs/ja/reference/statements/alter/projection) の使用を検討してください。
materialized view が処理するのは *今後の* 挿入のみです。`uk_price_paid` にすでに存在する 3,000 万行は、MV が作成される前に挿入されていたため、宛先テーブルは現在空のままです。
手動でバックフィルします:
```sql theme={null}
INSERT INTO uk_price_paid_by_town
SELECT
town,
date,
price,
type
FROM uk_price_paid;
```
これは宛先テーブルに直接挿入されます。このステップでは MV は関与しません。完了したら、行数が一致していることを確認してください:
```sql theme={null}
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;
```
どちらのテーブルも、行数は同じである必要があります。
次に、宛先テーブルに対して `town` で絞り込むクエリを実行し、ソーステーブルを直接クエリした結果と比較します。
まず、ソーステーブルをクエリします。
```sql theme={null}
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 の宛先テーブルに対して同じクエリを実行します。
```sql theme={null}
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` を実行します。
```sql theme={null}
SHOW TABLES;
```
`uk_price_paid_by_town` (宛先テーブル) と `uk_price_paid_by_town_mv` (ビュー) の両方が表示されます。`CREATE MATERIALIZED VIEW ... TO` を使用しているため、宛先テーブル名は自分で指定できます。`TO` 句を省略すると、ClickHouse は暗黙的な名前の宛先テーブル (`.inner.xxx`) を作成しますが、これは直接扱いにくくなります。
そのため、materialized view は `TO` 句を使って作成することを推奨します。
materialized view を使うと、追加のディスク容量と引き換えに、より高速に読み取れます。各テーブルがどれだけの容量を使用しているかを確認するには、`system.parts` をクエリします。
```sql theme={null}
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)` のソート順では元のデータとは異なる圧縮が行われる可能性があるため、ディスク上のサイズが小さくなる場合があります。
