はじめに

普段個人でDiscord Bot「MaidBot」を開発・運用しています。サーバー内の発言数やロール別のアクティビティを集計する機能を作ったとき、実装は素朴にJavaScriptの配列をfilterやreduceで回すだけのものでした。件数が少ないうちは全く問題ないのですが、「これがもっと大量のログになったらどうなるんだろう」「そもそも分析用のデータベースを使うとどれくらい速くなるものなんだろう」と気になっていました。

ちょうどClickHouseの記事投稿キャンペーンを見かけたので、これを機に実際に手を動かして確かめてみることにしました。この記事は「触ってみた」系の入門記事で、実際に自分の環境で計測した数字だけを載せています。

ClickHouseとは

ClickHouseは列指向(カラムナ)のOLAP(分析用途)データベースです。行志向のMySQLやPostgreSQLと違い、同じ列のデータをまとめて保存・圧縮・スキャンする構造になっているため、「特定のカラムだけを対象に大量の行を集計する」ようなクエリを得意としています。まさに今回やりたい「ロール別に何件発言があったか」のような集計にぴったりです。

やったこと

MaidBotが将来もっと大量のメッセージログを記録するようになったと仮定して、疑似データを作りました。

200万件の「メッセージイベント」(ギルドID・チャンネル・ユーザー・ロール・文字数・タイムスタンプ)
5ギルド分・5チャンネル・5000ユーザー・5ロールの組み合わせでランダム生成
直近30日分のタイムスタンプを付与

const roles = ['新規','一般','常連','VIP','モデレーター'];
const channels = ['general','雑談','質問','告知','ゲーム'];
const N = 2000000;
// guildId, channelId, userId, roleId, contentLength, ts をランダム生成して
// JSON Lines形式(1行1レコード)で書き出す

これをまず「今のMaidBotがやっているのと同じ方法」、つまりNode.jsで1行ずつ読んでJSのオブジェクトで集計する方法と、ClickHouseにテーブルを作ってSQLで集計する方法の両方で試しました。

セットアップ

今回は手元の検証環境で試したかったので、Ubuntuの標準リポジトリに入っているclickhouse-serverをそのまま使いました。

sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start

これだけで動きます。ただし今回の環境ではUbuntu標準リポジトリ経由だったため、入ったバージョンは18.16.1とかなり古めでした(ClickHouse自体は年月ベースのバージョニングで非常に活発に更新されているプロダクトなので、実運用では公式サイトのインストールスクリプトかDockerイメージから最新版を入れるのがおすすめです)。とはいえ、基本的なテーブル作成・集計クエリの検証には十分でした。

テーブルはこんな感じで作成しました。

CREATE TABLE messages (
    guildId String,
    channelId String,
    userId String,
    roleId String,
    contentLength UInt16,
    ts DateTime
) ENGINE = MergeTree ORDER BY ts

ENGINE = MergeTreeがClickHouseの標準的なテーブルエンジンで、ORDER BYで指定したカラム(今回はタイムスタンプ)順に物理的にデータを並べて保存してくれます。

データ投入はJSON Lines形式のファイルをそのままパイプするだけでした。

clickhouse-client --query "INSERT INTO messages FORMAT JSONEachRow" < messages.jsonl

200万件の投入にかかった時間は約1.9秒でした。

集計速度を比較してみた

その1: ロール別の単純な件数集計

JavaScriptで1行ずつ読んで集計

const rl = readline.createInterface({ input: fs.createReadStream('messages.jsonl') });
const counts = {};
for await (const line of rl) {
  const row = JSON.parse(line);
  counts[row.roleId] = (counts[row.roleId] || 0) + 1;
}

結果: 3571 ms

ClickHouseでSQL集計

SELECT roleId, count() AS cnt FROM messages GROUP BY roleId ORDER BY cnt DESC

結果: 132 ms

同じ200万件・同じ集計内容で、約27倍の差が出ました。

その2: チャンネル別×ロール別の平均文字数(もう少し複雑な集計)

JavaScript版(オブジェクトをキーにして手動でグルーピング)は4058 ms、ClickHouse版のSQLは212 msでした。こちらも約19倍の差です。

SELECT channelId, roleId, count() AS cnt, round(avg(contentLength),1) AS avgLen
FROM messages
GROUP BY channelId, roleId
ORDER BY channelId, cnt DESC

その3: 日別の推移(直近30日)

ClickHouse側だけ試しましたが、toDate()で日付に丸めてグループ化するクエリが57 msで返ってきました。

SELECT toDate(ts) AS day, count() AS cnt
FROM messages
GROUP BY day
ORDER BY day

注意点

このJavaScript側の処理時間には、JSON文字列のパース処理も含まれているので、完全に「同じ処理を違うエンジンで実行した比較」というよりは、「今のMaidBotが実際にやる可能性のある方法 vs ClickHouseにやらせる方法」という、実務的な比較になっている点は補足しておきます。それでも、体感としてはっきり差が出るレベルだというのは実測して初めて分かりました。

おまけ: ディスク使用量も圧縮されていた

元のJSON Linesファイルは218MBでしたが、ClickHouseに取り込んだあとの実際のディスク使用量を見てみると:

SELECT formatReadableSize(sum(bytes)) FROM system.parts WHERE table = 'messages' AND active

29.13 MiBでした。列指向の圧縮効率の高さもあわせて実感できました。

ClickHouseの始め方(自分がやった手順まとめ)

お試しなら公式のClickHouse Cloudの無料トライアルが手軽です(サインアップだけでブラウザからSQLを叩けます)
自分のサーバーで試したい場合は、curl https://clickhouse.com/から公式インストールスクリプトを使うか、Dockerイメージ(clickhouse/clickhouse-server)を使うのが最新版を使えて確実です(今回私が使ったUbuntu標準リポジトリ版はかなり古いバージョンだったので、実運用にはおすすめしません)
テーブルを作る(CREATE TABLE ... ENGINE = MergeTree)
データを入れる(CSVでもJSON Lines形式でも、FORMATを指定してそのまま流し込める)
普通のSQLで集計する

MySQLやPostgreSQLを触ったことがあれば、SQLの書き方自体はほぼ同じ感覚で書けるので、学習コストは低いと感じました。

まとめ

200万件規模のログ集計で、JavaScriptでの素朴な実装とClickHouseのSQL集計を比べたら、約20〜27倍の速度差が実測できた
データ投入も200万件で約1.9秒と高速
ディスク使用量も元データの1/7程度に圧縮されていた
個人開発Botのログ分析のような小規模な用途でも、「気になったら実際に試してみる」だけの価値は十分にあった

次はMaidBotの実際のロール別アクティビティ集計(/roleactivityコマンド)や、サーバー成長グラフのデータを、今のメモリ内集計・JSONファイルではなく実際にClickHouseに置き換えてみたいと考えています。AI/LLM周りのログ分析基盤としても注目されている理由が、今回の実測を通して少しわかった気がします。