初めての 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 されます。

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;

次に、ソーステーブル (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 の使用を検討してください。

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;

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

次に、宛先テーブルに対して 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 句を使って作成することを推奨します。

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) のソート順では元のデータとは異なる圧縮が行われる可能性があるため、ディスク上のサイズが小さくなる場合があります。