ClickHouseとは？
オープンソースのカラム型データベース。
ClickHouse is an open source column-oriented database management system capable of real time generation of analytical data reports using SQL queries.
https://clickhouse.yandex/
SQLが使えて、データ更新や削除ができないけど、爆速で検索結果を返してくれるらしい。。
前置き
WEBサーバのアクセスログを解析するのにSQL使いたいんだけど、データ量多すぎてRDBじゃ使い物ならなくて、RedShiftはコスト高くてどうしようかなって時にみつけたデータウェアハウス。
日本語の情報まったくないけど、試してみたらウヒョーってなったので簡単にMySQLとのベンチマークだけ備忘録。
ちなみにClickHouseは基本的にはデータの更新、削除はできない。
入れて参照するだけ。
この割り切った感が素敵。。
※Summing MergeTreeというEngineを利用することで、例えばレポート用のテーブルなどでImpressionやCV数など数値の足し上げしたいような場合に数値を更新できるものもあります。
ClickHouseのインストールはこちらをどうぞ。
https://clickhouse.yandex/#quick-start
マニュアルはこちらから。
http://clickhouse.readthedocs.io/en/latest/reference_en.html
日本語の参考サイトがほとんどない状態で、これはとても参考になった。
http://tech.geniee.co.jp/entry/2017/07/20/160100
ベンチマークのための準備
###今回準備したサーバ
- AWS EC2: r4.large
- Ubuntu 16.04.2
比較したClickHouseとMySQLのバージョン
| ClickHouse | MySQL |
|---|---|
| 1.1.54292 | 5.7.20 |
検証用に準備したデータ
WEBサーバのアクセスログ
約2,600,000件
検証用テーブル
MySQL
CREATE TABLE access_logs (
log_date date DEFAULT NULL,
url varchar(255) DEFAULT NULL,
referrer varchar(255) DEFAULT NULL,
ip varchar(255) DEFAULT NULL,
ua text DEFAULT NULL,
created_at datetime DEFAULT NULL,
KEY idx_log_date (log_date),
KEY idx_url (url),
KEY idx_referrer (referrer),
KEY idx_ip (ip),
KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ClickHouse
日別で保存するテーブルを分け、ClickHouseのEngineのひとつであるMergeTreeを利用する構成とした。
※ClickHouseはデータの更新、削除ができないため、万が一データに不備があるとデータをすべて削除し、一から取り込み直す必要がある。
そのため日毎にテーブルを分けるようにし、万が一の際にはその日の分だけテーブルを作り直すことで対処できるようにしている。
検索用テーブル
CREATE TABLE access_logs (
log_date Date,
url String,
referrer String,
ip String,
ua String,
created_at DateTime
) ENGINE = Merge(\'hogehoge\', \'^access_log_\')
実データテーブル
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, ip, url), 8192)
実際のベンチマーク
※このベンチマークは３回クエリを投げた平均値を記載しています。
検証１ まずはシンプルに。
select count(*) from access_logs;
| ClickHouse | MySQL |
|---|---|
| 0.005 sec | 0.81 sec |
| すでに差がある。。 |
検証2 日付を絞ってみた。
select count(*) from access_logs where log_date = '2017-10-30';
| ClickHouse | MySQL |
|---|---|
| 0.003 sec | 0.13 sec |
| MySQLもIndexが効いているのでそこそこがんばっている。 |
検証3 時間別の集計を試す。
- ClickHouse
SELECT
count(*),
toHour(created_at) AS hour
FROM access_logs
WHERE log_date = '2017-10-30'
GROUP BY toHour(created_at);
- MySQL
SELECT
count(*),
date_format(created_at, '%H') AS hour
FROM access_logs
WHERE log_date = '2017-10-30'
GROUP BY hour
| ClickHouse | MySQL |
|---|---|
| 0.010 sec | 1.09 sec |
| GroupByになると差がでてきた。 |
検証4 リファラー×URLの上位50件を出してみる。
SELECT
count(*) AS cnt,
referrer,
url
FROM access_logs
GROUP BY
referrer, url
ORDER BY cnt DESC
LIMIT 50
| ClickHouse | MySQL |
|---|---|
| 0.558 sec | 16.27 sec |
| MySQLでもIndex貼っているにも関わらず、かなりの差が出てしまった。。 |
WEB上で公開されている他のミドルウェアとのベンチマーク結果
RedShiftとのベンチマーク
Apache Spark/MySQLとのベンチマーク
公式サイトで公開されているベンチマーク
ベンチマークしてみての考察
データ更新、削除できないというデメリットはあるものの、アクセスログなど更新、削除する必要のないものを集計したりすることに関しては、かなり有効に使えるデータウェアハウスであると言えるのではないかと。
レコード削除はできないが、日毎にデータテーブルを分けているので、過去日のテーブル自体を削除してしまえばデータサイズの節約も可能。
手軽にビッグデータ解析を行うには充分すぎるパフォーマンスが出せたので、しばらくはClickHouseでアクセスログの解析運用をしていきたいと思った今日この頃でした。
おまけ
ビッグデータをサーバ上に置くとHDDの容量の問題が常にでてきます。
なのでスケールアウトできる運用を心がける必要があるが、ClickHouseも容易にスケールアウトできる設計になっています。
この辺はまたいずれ検証したいと思いますが、その前に今回検証したデータの実容量をMySQLと比べてみました。
MySQL
# pwd
/var/lib/mysql/hogehoge
# du -h .
2.6G .
ClickHouse
# pwd
/var/lib/clickhouse/data/hogehoge
# du -h .
201M
| ClickHouse | MySQL |
|---|---|
| 201M | 2.6G |
データサイズでもMySQLに比べ1/10以下と、かなり節約できることも確認できました。
スケールアウトしなくても、しばらくは１台で運用していけそう^^
ということで財布にもやさしいClickHouseでした。