ClickHouseの「MergeTree」と「Summing MergeTree」を試してみた。

ClickHouseには多種多様なテーブルエンジンが用意してある。
https://clickhouse.yandex/docs/en/table_engines/
この中からWEBサーバのアクセスログを保存したいときに使えるものを今回はPickUp!

ということで「MergeTree」と「Summing MergeTree」を検証してみた。

MergeTree

公式ドキュメントから
MergeTreeエンジンは、プライマリキーと日付によるインデックスをサポートし、リアルタイムでデータを更新する可能性を提供します。
https://clickhouse.yandex/docs/en/table_engines/mergetree.html

※MergeTreeエンジンはMergeエンジンとセットで構成することでその力を発揮します。

解説

通常のRDBなどではテーブルを一つ作ってそこに生ログデータを流し込んでいくことが多いと思う。
ただ、あまりにデータ量が大きい場合などには日毎にテーブルを分けるなどしてJOINして結果を求めることもあると思う。

しかしClickHouseのMergeTreeは日毎にテーブルを分ける前提で考えられている様で、インデックス条件に日付データが必須となっている。

ということはJOINしたり、何かしら考慮する必要があるのかな？と考えてしまいがちだけど、
この「MergeTree」エンジンを利用することで、それらの日付ごとのテーブルをひとつのテーブルとして扱うことができるようになっている。

ENGINE = MergeTree(EventDate, (CounterID, EventDate), 8192)
ENGINE = MergeTree(日付カラム, (プライマリーキーのカラム群), インデックス粒度)

このエンジン「MergeTree」で作ったテーブルに生ログをどんどん登録していくことになる。
そしてMergeTreeのテーブルが日毎に追加されていく設計となる。

それらのテーブルをまとめるのが「Merge」というエンジンになる。
https://clickhouse.yandex/docs/en/table_engines/merge.html

ENGINE = Merge(hits, '^WatchLog')
ENGINE = Merge(データベース名, '^テーブル名')

この「Merge」テーブルは実データを持たずにテーブルの定義情報を持つだけのものです。
ENGINEの定義の部分で「データベース名」と「^テーブル名」とあるが、ここがポイントで、
「どのデータベースのどのテーブルをマージしますか？」という定義になっている。

そして「^テーブル名」の部分は正規表現で記述できるので、マージしたいテーブル群を一括で指定できます。

テーブル作成のサンプル

Mergeテーブル => テーブル定義のみをもつテーブル。

CREATE TABLE access_logs (
  log_date Date,
  url String,
  referrer String,
  ip String,
  ua String,
  created_at DateTime
) ENGINE = Merge(\'hogehoge\', \'^access_log_\')

MergeTreeテーブル => 生ログを実際に取り込む用のテーブル。

CREATE TABLE access_log_{YYYYMMDD} (
  log_date Date,
  url String,
  referrer String,
  ip String,
  ua String,
  created_at DateTime
) ENGINE = MergeTree(log_date, (log_date, url, referrer, ip, ua, created_at), 8192)

実際のSELECT文のサンプル

SELECT * FROM access_logs

これだけで、access_log_{YYYYMMDD}のデータをすべて取得できる。JOINなどを考慮する必要はない。

SELECT * FROM access_log_{YYYYMMDD};

この取得結果は下記と同じとなる。

SELECT * FROM access_logs WHERE log_date = 'YYYY-MM-DD';

という感じで、複数のテーブルを１つのテーブルとして扱うことができるのでかなり便利。

Summing MergeTree

公式ドキュメントから
このエンジンは、マージ中にデータを合計する点でMergeTreeとは異なります。
https://clickhouse.yandex/docs/en/table_engines/summingmergetree.html

※Summing MergeTreeエンジンもMergeエンジンとセットで構成することでその力を発揮します。

解説

ClickHouseはデータの更新、削除をサポートしていません。
基本登録して参照するだけというスタンスですが、このSumming MergeTreeは特定カラムの数値を足し上げすることができます。
用途としてはレポート系のテーブルに最適なエンジンになっています。

ENGINE = SummingMergeTree(EventDate, (OrderID, EventDate, BannerID, ...), 8192)
ENGINE = SummingMergeTree(日付カラム, (プライマリーキー, ...), 8192)

数値の足し上げを行う特定カラムを明示的にする場合

ENGINE = SummingMergeTree(EventDate, (OrderID, EventDate, BannerID, ...), 8192, (Shows, Clicks, Cost, ...))
ENGINE = SummingMergeTree(日付カラム, (プライマリーキー, ...), 8192, (足し上げを実行するカラム))

このエンジンは同じプライマリーキーのデータが複数ある場合、後述するOPTIMIZE TABLEすることで、数値カラムをマージします。

テーブル作成のサンプル

Mergeテーブル => テーブル定義のみをもつもの。

CREATE TABLE summing_tests (
	date Date,
	uid Int32,
	url String,
	view Int8,
	cv Int8
) ENGINE = Merge(\'hogehoge\', \'^summing_test_\')

SummingMergeTree => 生ログを実際に取り込む用のテーブル。

CREATE TABLE summing_test_{YYYYMMDD} (
	date Date,
	uid Int32,
	url String,
	view Int8,
	cv Int8
) ENGINE = SummingMergeTree(date, (date, uid, url), 8192, (view, cv));

実際のINSERT分のサンプル

INSERT INTO summing_test_{YYYYMMDD} VALUES ('2000-01-01', 1234, 'http://hogehoge.com/page1.html', 10, 2);
INSERT INTO summing_test_{YYYYMMDD} VALUES ('2000-01-01', 1234, 'http://hogehoge.com/page1.html', 3, 1);
INSERT INTO summing_test_{YYYYMMDD} VALUES ('2000-01-01', 1234, 'http://hogehoge.com/page1.html', -5, 1);

実際のSELECT文のサンプル

まずは結果をみる

:) select * from summing_tests;

┌───────date─┬──uid─┬─url────────────────────────────┬─view─┬─cv─┐
│ 2000-01-01 │ 1234 │ http://hogehoge.com/page1.html │   10 │  2 │
└────────────┴──────┴────────────────────────────────┴──────┴────┘
┌───────date─┬──uid─┬─url────────────────────────────┬─view─┬─cv─┐
│ 2000-01-01 │ 1234 │ http://hogehoge.com/page1.html │    3 │  1 │
└────────────┴──────┴────────────────────────────────┴──────┴────┘
┌───────date─┬──uid─┬─url────────────────────────────┬─view─┬─cv─┐
│ 2000-01-01 │ 1234 │ http://hogehoge.com/page1.html │   -5 │  1 │
└────────────┴──────┴────────────────────────────────┴──────┴────┘

3 rows in set. Elapsed: 0.002 sec.

※上記を見るとわかるが、単純にINSERTしただけでは数値がマージされることはない。ここでOPTIMIZE TABLEを実行することでテーブルの最適化を行う必要がある。

テーブルの最適化を実施する

:) OPTIMIZE TABLE summing_test_{YYYYMMDD}

※最適化を行うのはMergeテーブルではなく、個別のSummingMergeTreeテーブルであることに注意する。

最適化後の結果をみる

:) select * from summing_tests;

┌───────date─┬──uid─┬─url────────────────────────────┬─view─┬─cv─┐
│ 2000-01-01 │ 1234 │ http://hogehoge.com/page1.html │    8 │  4 │
└────────────┴──────┴────────────────────────────────┴──────┴────┘

1 rows in set. Elapsed: 0.002 sec.

まとめ

「MergeTree」と「Summing MergeTree」のエンジンの使い分けは下記のようなイメージ。

• MergeTree
  生ログを単純にDBに取り込んで効率よくデータを参照したいときに使えるエンジン。

• Summing MergeTree
  データを集積し、レポート出力用として使えるエンジン。

どちらも日毎にテーブルを追加していく必要があるが、その他のRDBやデータウェアハウスにくらべデータサイズを小さく保持しておくことができ、さらにデータを参照するパフォーマンスがかなり期待できるものなので、日本語での情報は少ないもののかなり使えるデータウェアハウスではないかと思っています。

自分の拙い英語力で公式ドキュメントを読んでいるので、本当はもっとよい使い方ができるのではないかと思います。
またこの記事に関しても認識がまちがっている箇所があるかもなので、よりよい情報があればぜひコメントください。