ClickHouse

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


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":(略)
}