カスケードするマテリアライズドビュー

この例では、まずマテリアライズドビューの作成方法を示し、その後、2つ目のマテリアライズドビューを1つ目にカスケードさせる方法を説明します。このページでは、その手順、さまざまな活用方法、および制約について説明します。2つ目のマテリアライズドビューをソースとして使用してマテリアライズドビューを作成することで、さまざまなユースケースに対応できます。

例:

複数のドメイン名について、1時間ごとの閲覧数を含む架空のデータセットを使用します。

目的

各ドメイン名ごとに、データを月単位で集計する必要があります。
各ドメイン名ごとに、データを年単位で集計する必要もあります。

次のいずれかの選択肢を取ることができます。

SELECT クエリ実行時にデータを読み取り、集計するクエリを書く
データ取り込み時に、新しい形式に合うようデータを準備する
データ取り込み時に、特定の集計に合わせてデータを準備する

マテリアライズドビューを使ってデータを準備することで、ClickHouse が処理する必要のあるデータ量と計算量を抑え、SELECT クエリを高速化できます。

マテリアライズドビュー用のソーステーブル

ソーステーブルを作成します。今回の目的は個々の行ではなく集約されたデータに対してレポートすることなので、受信データをパースしてその情報をマテリアライズドビューに渡し、実際の入力データ自体は破棄して構いません。これにより目的を達成しつつストレージを節約できるため、Null テーブルエンジンを使用します。

CREATE DATABASE IF NOT EXISTS analytics;

CREATE TABLE analytics.hourly_data
(
    `domain_name` String,
    `event_time` DateTime,
    `count_views` UInt64
)
ENGINE = Null

注記

Null テーブルに対してマテリアライズドビューを作成できます。つまり、テーブルに書き込まれたデータはビューには反映されますが、元の生データそのものは破棄されます。

月次集計テーブルとマテリアライズドビュー

最初のマテリアライズドビューのために Target テーブルを作成する必要があります。この例では analytics.monthly_aggregated_data とし、月単位およびドメイン名単位でビュー数の合計を保存します。

CREATE TABLE analytics.monthly_aggregated_data
(
    `domain_name` String,
    `month` Date,
    `sumCountViews` AggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (domain_name, month)

ターゲットテーブルにデータを転送するマテリアライズドビューは、以下のようになります。

CREATE MATERIALIZED VIEW analytics.monthly_aggregated_data_mv
TO analytics.monthly_aggregated_data
AS
SELECT
    toDate(toStartOfMonth(event_time)) AS month,
    domain_name,
    sumState(count_views) AS sumCountViews
FROM analytics.hourly_data
GROUP BY
    domain_name,
    month

年次集計テーブルとマテリアライズドビュー

次に、先ほど作成したターゲットテーブル monthly_aggregated_data に関連付けられる 2つ目のマテリアライズドビューを作成します。

まず、各ドメイン名ごとに年単位で集計された views の合計値を保存する、新しいターゲットテーブルを作成します。

CREATE TABLE analytics.year_aggregated_data
(
    `domain_name` String,
    `year` UInt16,
    `sumCountViews` UInt64
)
ENGINE = SummingMergeTree()
ORDER BY (domain_name, year)

このステップではカスケードを定義します。FROM ステートメントは monthly_aggregated_data テーブルを使用します。これは、データフローが次のようになることを意味します。

データは hourly_data テーブルに入ります。
ClickHouse は受信したデータを、最初のマテリアライズドビューである monthly_aggregated_data テーブルに転送します。
最後に、ステップ 2 で受信したデータが year_aggregated_data テーブルに転送されます。

CREATE MATERIALIZED VIEW analytics.year_aggregated_data_mv
TO analytics.year_aggregated_data
AS
SELECT
    toYear(toStartOfYear(month)) AS year,
    domain_name,
    sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
    domain_name,
    year

注記

マテリアライズドビューを扱う際によくある誤解として、「テーブルからデータが読み出される」と考えてしまうことがあります。マテリアライズドビュー はそのようには動作しません。フォワードされるデータはテーブル内の最終結果ではなく、「挿入されたブロック」です。

この例で monthly_aggregated_data で使用されているエンジンが CollapsingMergeTree だとします。この場合、2 つ目のマテリアライズドビュー year_aggregated_data_mv にフォワードされるデータは、コラップス後のテーブルの最終結果ではなく、SELECT ... GROUP BY で定義されたフィールドを持つデータブロックになります。

もし CollapsingMergeTree、ReplacingMergeTree、あるいは SummingMergeTree を使用していて、カスケード構成のマテリアライズドビューを作成する予定がある場合は、ここで説明している制限事項を理解しておく必要があります。

サンプルデータ

ここで、カスケードマテリアライズドビューをテストするために、いくつかのデータを挿入します。

INSERT INTO analytics.hourly_data (domain_name, event_time, count_views)
VALUES ('clickhouse.com', '2019-01-01 10:00:00', 1),
       ('clickhouse.com', '2019-02-02 00:00:00', 2),
       ('clickhouse.com', '2019-02-01 00:00:00', 3),
       ('clickhouse.com', '2020-01-01 00:00:00', 6);

analytics.hourly_data の内容を SELECT すると、テーブルエンジンが Null でありながらデータ自体は処理されているため、次のような結果が表示されます。

SELECT * FROM analytics.hourly_data

OK。

結果セット 0 行。経過時間: 0.002 秒。

ここでは、期待どおりの結果と突き合わせて検証しやすいように、小さなデータセットを使用しています。小さなデータセットでフローが正しく動作することを確認できたら、その設定のまま大規模なデータに切り替えることができます。

結果

ターゲットテーブルに対して sumCountViews フィールドを選択するクエリを実行すると、一部のターミナルではバイナリ表現が表示されます。これは、その値が数値ではなく AggregateFunction 型として保存されているためです。集計の最終結果を取得するには、-Merge サフィックスを使用する必要があります。

次のクエリで、AggregateFunction に保存されている特殊な文字列（バイト列）を確認できます。

SELECT sumCountViews FROM analytics.monthly_aggregated_data

┌─sumCountViews─┐
│               │
│               │
│               │
└───────────────┘

3 行の結果。実行時間: 0.003 秒。

代わりに、Merge サフィックスを使用して sumCountViews の値を取得してみましょう。

SELECT
   sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data;

┌─sumCountViews─┐
│            12 │
└───────────────┘

1 行が返されました。経過時間: 0.003 秒。

AggregatingMergeTree では、AggregateFunction を sum として定義しているため、sumMerge を使用できます。AggregateFunction に対して関数 avg を使用する場合は、avgMerge を使用します。他の関数についても同様です。

SELECT
    month,
    domain_name,
    sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
    domain_name,
    month

これで、マテリアライズドビューが定義した目的を満たしていることを確認できます。

ターゲットテーブル monthly_aggregated_data にデータが保存されたので、各ドメイン名ごとに月単位で集計されたデータを取得できます。

SELECT
   month,
   domain_name,
   sumMerge(sumCountViews) AS sumCountViews
FROM analytics.monthly_aggregated_data
GROUP BY
   domain_name,
   month

┌──────month─┬─domain_name────┬─sumCountViews─┐
│ 2020-01-01 │ clickhouse.com │             6 │
│ 2019-01-01 │ clickhouse.com │             1 │
│ 2019-02-01 │ clickhouse.com │             5 │
└────────────┴────────────────┴───────────────┘

結果セットに 3 行が含まれます。経過時間: 0.004 秒。

各ドメイン名ごとの年次集計データ:

SELECT
   year,
   domain_name,
   sum(sumCountViews)
FROM analytics.year_aggregated_data
GROUP BY
   domain_name,
   year

┌─year─┬─domain_name────┬─sum(sumCountViews)─┐
│ 2019 │ clickhouse.com │                  6 │
│ 2020 │ clickhouse.com │                  6 │
└──────┴────────────────┴────────────────────┘

2 行が取得されました。経過時間: 0.004 秒。

複数のソーステーブルを単一のターゲットテーブルに結合する

マテリアライズドビューは、複数のソーステーブルを 1 つのターゲットテーブルに結合するためにも使用できます。これは、UNION ALL のロジックに近いマテリアライズドビューを作成する際に有用です。

まず、異なるメトリクスのセットを表す 2 つのソーステーブルを作成します。

CREATE TABLE analytics.impressions
(
    `event_time` DateTime,
    `domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time)
;

CREATE TABLE analytics.clicks
(
    `event_time` DateTime,
    `domain_name` String
) ENGINE = MergeTree ORDER BY (domain_name, event_time)
;

次に、結合済みのメトリクスセットを格納する Target テーブルを作成します。

CREATE TABLE analytics.daily_overview
(
    `on_date` Date,
    `domain_name` String,
    `impressions` SimpleAggregateFunction(sum, UInt64),
    `clicks` SimpleAggregateFunction(sum, UInt64)
) ENGINE = AggregatingMergeTree ORDER BY (on_date, domain_name)

同じ Target テーブルを参照するマテリアライズドビューを 2 つ作成します。不足しているカラムを明示的に指定する必要はありません。

CREATE MATERIALIZED VIEW analytics.daily_impressions_mv
TO analytics.daily_overview
AS
SELECT
    toDate(event_time) AS on_date,
    domain_name,
    count() AS impressions,
    0 clicks         ---<<<--- これを書かなくても 0 のままです
FROM
    analytics.impressions
GROUP BY
    toDate(event_time) AS on_date,
    domain_name
;

CREATE MATERIALIZED VIEW analytics.daily_clicks_mv
TO analytics.daily_overview
AS
SELECT
    toDate(event_time) AS on_date,
    domain_name,
    count() AS clicks,
    0 impressions    ---<<<--- これを書かなくても 0 のままです
FROM
    analytics.clicks
GROUP BY
    toDate(event_time) AS on_date,
    domain_name
;

これで値を挿入すると、その値は Target テーブルの対応する列ごとに集計されます。

INSERT INTO analytics.impressions (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
       ('clickhouse.com', '2019-01-01 12:00:00'),
       ('clickhouse.com', '2019-02-01 00:00:00'),
       ('clickhouse.com', '2019-03-01 00:00:00')
;

INSERT INTO analytics.clicks (domain_name, event_time)
VALUES ('clickhouse.com', '2019-01-01 00:00:00'),
       ('clickhouse.com', '2019-01-01 12:00:00'),
       ('clickhouse.com', '2019-03-01 00:00:00')
;

Target テーブルには、インプレッションとクリックを統合したデータが含まれます。

SELECT
    on_date,
    domain_name,
    sum(impressions) AS impressions,
    sum(clicks) AS clicks
FROM
    analytics.daily_overview
GROUP BY
    on_date,
    domain_name
;

このクエリを実行すると、次のような結果が得られます。

┌────on_date─┬─domain_name────┬─impressions─┬─clicks─┐
│ 2019-01-01 │ clickhouse.com │           2 │      2 │
│ 2019-03-01 │ clickhouse.com │           1 │      1 │
│ 2019-02-01 │ clickhouse.com │           1 │      0 │
└────────────┴────────────────┴─────────────┴────────┘

3 行が選択されました。経過時間: 0.018 秒。

マテリアライズドビュー用のソーステーブル​

月次集計テーブルとマテリアライズドビュー​

年次集計テーブルとマテリアライズドビュー​

サンプルデータ​

結果​

複数のソーステーブルを単一のターゲットテーブルに結合する​

マテリアライズドビュー用のソーステーブル

月次集計テーブルとマテリアライズドビュー

年次集計テーブルとマテリアライズドビュー

サンプルデータ

結果

複数のソーステーブルを単一のターゲットテーブルに結合する