Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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":(略)
}
ymir
独自のテクノロジーで、パフォーマンスを追求したメール配信システムを中心にメッセージングソリューションを提供するSaaSベンダーです。
https://www.ymir.co.jp/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした