ClickHouseでマテリアライズド・ビューを使う

ClickHouse はカラム型データベースで、非常に集計が高速に行えるため、多くの場合は生データを直接集計することが可能ですが、データ量が多いと限界はあります。
マテリアライズド・ビューを使うと、事前に集計を進めておくことで高速化することが可能です。

ClickHouse のマテリアライズド・ビューには、以下の特徴があります。

• 元データに追加されたデータを、差分で集計を更新できる。（ただし、おそらく非同期実行）
• 全ての集計関数を使うことができる。合計・カウント・最大・最小などに限らず、ユニーク数なども利用可能。

全ての集計関数は、集計途中の状態を持つことができ、その途中状態を（バックグラウンドで）マージすることで差分集計処理を実現しています。

この記事では、AggregatingMergeTree テーブルエンジンを使ったマテリアライズド・ビューの使い方についてまとめています。
（他のテーブルエンジンでマテリアライズド・ビューを作ることも可能ですが、集計の方法は異なってきます）

テーブル定義

AggregatingMergeTree テーブルエンジンを使ったビューを作成します。
集計は、PRIMARY KEY ごとに行われ、必要に応じて同一の PRIMARY KEY の集計はバックグラウンドでマージされます。

つまり、集計時に GROUP BY する項目を、PRIMARY KEY に指定する必要があります。
複数のカラムの組み合わせがあるような場合は、複数のビューに分ける必要があります。

以下のような商品購入履歴（商品1個につき1レコード）を例にします。

CREATE TABLE purchase (
  userid        UInt32,
  event_time    DateTime,
  prodid        String,
  category      String,
  amount        UInt32
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY event_time;

この購入履歴を、日ごと、カテゴリごとに集計することにします。
売上の合計値、個数、金額のヒストグラムをビューにしてみます。
※同一商品で金額が複数あるのはあまりないと思いますが…

以下のように、マテリアライズド・ビューを作成します。

CREATE MATERIALIZED VIEW purchase_agg
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, category)
POPULATE
AS SELECT
    toDate(event_time) AS event_date,
    category,
    sumState(amount) AS total_amount,
    countState(amount) AS quantity,
    uniqExactState(userid) AS users
FROM purchase
GROUP BY toDate(event_time), category;

• ENGINE には AggregatingMergeTree を指定します。
• ORDER BY には、GROUP BY で指定したカラムと同じものを指定します。
• GROUP BY のカラムはそのまま、集計対象のものは、集計関数の末尾に State をつけた関数で集計します。
• AS SELECT の内部で、sumState(amount) AS amount のように、集計結果を元データと同じカラムにすることはできません。（エラーになります）
• POPULATE を付けると、テーブル作成と同時に既存のデータをマテリアライズド・ビューにも追加します。最初にデータが存在しない場合は、POPULATE を指定しなくても問題ありません。

sumState のような関数を使うと、集計の途中状態が返されます。
AS SELECT に記載したクエリで、このように集計結果ではなく、集計の途中状態を返すようにします。
元テーブルにデータが INSERT されるたびに、AS SELECT で指定したクエリが実行され、集計がマテリアライズド・ビュー側のテーブルに追加されていきます。そして、バックグラウンドで同一の主キー（ORDER BY で指定したキー）のデータは、集計の途中状態がマージされます。

参照方法

適当にデータをいれてみます。

INSERT INTO purchase VALUES (1, '2019-05-01 15:12:12', 'P0001', '書籍', 1200);
INSERT INTO purchase VALUES (1, '2019-05-01 15:12:12', 'P0002', '書籍', 2100);
INSERT INTO purchase VALUES (2, '2019-05-02 10:10:10', 'P0001', '書籍', 1200);
INSERT INTO purchase VALUES (3, '2019-05-02 14:00:31', 'P0001', '書籍', 1200);
INSERT INTO purchase VALUES (3, '2019-05-02 14:00:31', 'P0030', 'ゲーム', 6800);
INSERT INTO purchase VALUES (4, '2019-05-02 14:30:11', 'P0039', 'ゲーム', 2400);
INSERT INTO purchase VALUES (5, '2019-05-02 15:10:10', 'P0077', 'ゲーム', 7200);
INSERT INTO purchase VALUES (6, '2019-05-02 15:21:00', 'P0077', 'ゲーム', 7000);
INSERT INTO purchase VALUES (7, '2019-05-02 15:31:48', 'P0077', 'ゲーム', 5600);

マテリアライズド・ビューの中には、集計の途中状態の形でデータが入っており、また同一の主キーが複数存在することがあります。
そのため、参照するタイミングで再度 GROUP BY した上で、集計を最終的な結果にする必要があります。

以下のように、末尾に Merge をつけた関数を使い、集計の途中状態から集計を行います。

SELECT
    event_date,
    category,
    sumMerge(total_amount) AS total_amount,
    countMerge(quantity) AS quantity,
    uniqExactMerge(users) AS users
FROM purchase_agg
GROUP BY event_date, category
ORDER BY event_date, category;

┌─event_date─┬─category─┬─total_amount─┬─quantity─┬─users─┐
│ 2019-05-01 │ 書籍     │         3300 │        2 │     1 │
│ 2019-05-02 │ ゲーム   │        29000 │        5 │     5 │
│ 2019-05-02 │ 書籍     │         2400 │        2 │     2 │
└────────────┴──────────┴──────────────┴──────────┴───────┘

集計の途中状態がどのようなものかは、マテリアライズド・ビューを普通に SELECT すると確認できます。

SELECT * FROM purchase_agg FORMAT JSONCompact;

{
        "meta":（略）,
        "data":
        [
                ["2019-05-02", "ゲーム", "X\u001B\u0000\u0000\u0000\u0000\u0000\u0000", "\u0001", "\u0001\u0006\u0000\u0000\u0000"],
                ["2019-05-01", "書籍", "�\f\u0000\u0000\u0000\u0000\u0000\u0000", "\u0002", "\u0001\u0001\u0000\u0000\u0000"],
                ["2019-05-02", "ゲーム", "�#\u0000\u0000\u0000\u0000\u0000\u0000", "\u0002", "\u0002\u0004\u0000\u0000\u0000\u0003\u0000\u0000\u0000"],
                ["2019-05-02", "書籍", "`\t\u0000\u0000\u0000\u0000\u0000\u0000", "\u0002", "\u0002\u0002\u0000\u0000\u0000\u0003\u0000\u0000\u0000"],
                ["2019-05-02", "ゲーム", "�\u0015\u0000\u0000\u0000\u0000\u0000\u0000", "\u0001", "\u0001\u0007\u0000\u0000\u0000"],
                ["2019-05-02", "ゲーム", " \u001C\u0000\u0000\u0000\u0000\u0000\u0000", "\u0001", "\u0001\u0005\u0000\u0000\u0000"]
        ],
        "rows": 6,
        "statistics":（略）
}