ClickHouseを利用して、CloudFrontのログ分析のコスパをカイゼンしてみた
クラウドサーカスが提供するCMS BlueMonkey（ブルーモンキー、以下、BM）にClickHouseを利用してみたことについてお伝えします！
ClickHouseを採用した理由
これまでBMでは数千のCloudFront Distributionを利用しており、毎日数千万レコードのアクセスログが発生する状況でした。CloudFrontのログをAthenaで集計分析していたのですが、全てのログをAthenaで分析するにはレコード量が膨大すぎて、処理時間とクエリを実行するごとに発生するコストが膨れ上がっていました。
そこで、集計分析に特化したデータベースにログを入れて分析することで、時間とコストの削減を図れるのではないかと考え、ClickHouseというサービスに注目し、技術検証として今回のログを導入に至りました。
ClickHouseとは
主な特徴
OSSの列指向型データベース。
オンライン分析処理（OLAP）に特化。
大量データの集計が得意、行単位の更新処理が苦手。
同様なサービスとして、AWSのRedShiftやGCPのBigQueryがある。
データの圧縮率がすごく良い🙆
ClickHouseの構築
構築方法
今回は、ClickHouseをEC2（AmazonLinux2023）上で構築しました。インストール方法は非常に簡単で、公式ドキュメントにある「From RPM Packages」を参考にしながら構築しました。
# Setup the RPM repository
sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
# Install ClickHouse server and client
sudo yum install -y clickhouse-server clickhouse-client
# Start ClickHouse server
sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server
sudo systemctl status clickhouse-server
# Connect to ClickHouse
clickhouse-client👆で、EC2上にClickHouseのサーバが構築され、同じサーバからClickHouseのクエリを実行できるようになります。
ClickHouseを自前のサーバで構築する際の注意点として、サーバスペックを気にする必要があり、特に今回のように大量のデータを読み取るクエリを流す場合はメモリを多めに用意する必要があります。
公式では32GB以上のメモリを推奨しているため、今回はメモリ最適化インスタンスタイプのRシリーズを採用しました。
※ただメモリが32GB以下でも動作するし、設定をいじれば低スペでも動く…らしい…
テーブル構成
AWSのドキュメントにAthenaのためのCloudFrontログ用テーブルを作成するクエリがあるので、それを参考にClickHouseのテーブルを作成します。
CREATE TABLE cloudfront_logs
(
`date` Date,
`time` String,
`location` String,
`bytes` Int64,
`request_ip` String,
`method` String,
`host` String,
`uri` String,
`status` String,
`referrer` String,
`user_agent` String,
`query_string` String,
`cookie` String,
`result_type` String,
`request_id` String,
`host_header` String,
`request_protocol` String,
`request_bytes` Int64,
`time_taken` Float32,
`xforwarded_for` String,
`ssl_protocol` String,
`ssl_cipher` String,
`response_result_type` String,
`http_version` String,
`fle_status` String,
`fle_encrypted_fields` Int32,
`c_port` Int32,
`time_to_first_byte` Float32,
`x_edge_detailed_result_type` String,
`sc_content_type` String,
`sc_content_len` Int64,
`sc_range_start` Int64,
`sc_range_end` Int64
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(date)
ORDER BY (host_header, date)こちら👆がClickHouseのCloudFrontログテーブル作成クエリの例
ENGINE = MergeTree👆は、テーブルのエンジンタイプで、MergeTreeはClickHouseで使用される最も基本なエンジンです。
PARTITION BY toYYYYMMDD(date)👆は、テーブルのパーティショニングです。
ClickHouseにおけるパーティションはRDSと異なり、性能改善のためではなく、データ操作のためのものです。例えば、レコードを物理削除するためには、パーティションドロップを行ってデータ削除をする必要があります。ここでは date をパーティショニングキーにして、YYYYMMDD のフォーマットで1日ずつのパーティションを作成しています。
ORDER BY (host_header, date)👆はテーブルのソートキーです。クエリ速度に影響するため、クエリでよく使われるカラムをキーに指定します。
`status` String,👆についてです。ステータスコードのカラムである status に1つ注意点があります。
AthenaではINT型になっていましたが、ClickHouseでは String型で指定していることです。CloudFrontログの仕様として、「サーバがレスポンスを返す前にクライアント側がコネクションを切断した場合を表すステータスコード」として000 があります。（ClickHouseでは 000 をINT型にできないためです。※Athenaではここをうまく変換してくれている？）
CloudFrontログの導入方法
次にログの導入方法についてです。CloudFrontのログは、S3に保存されているため、ClickHouseだとS3からログを取得し、作成したテーブルにデータをINSERTする必要があります。
また、BMでは、すべてのCloudFrontログを同じS3バケットに入れており、各CloudFrontに対応するドメイン名でディレクトリ分けを行っています。
上記を踏まえて、CllickHouseでのINSERTクエリは以下👇のようになります。
INSERT INTO cloudfront_logs
SELECT *
FROM s3('https://cloudfront-logs-example-bucket.s3.ap-northeast-1.amazonaws.com/abc.example.com/*2024-08-*.gz', 'TabSeparated')
SETTINGS
input_format_tsv_skip_first_lines = 2,
format_tsv_null_representation = '-'FROM s3('https://cloudfront-logs-example-bucket.s3.ap-northeast-1.amazonaws.com/abc.example.com/*2024-08-*.gz', 'TabSeparated')CloudFrontは様々なデータソースと連携することができ、例えば、S3の場合、FROM s3(ファイルURL) というように簡単に連携することができます。ここでは例として、バケット cloudfront-logs-example-bucket にあるドメイン名 abc.example.com のCloudFrontログに対してアクセスを行います。ファイルURLでは正規表現を使うことができ、ここでは *2024-08-*.gz で2024年8月分の全てのログを取得している。TabSeparated でファイルフォーマットをTSVに指定しています。
input_format_tsv_skip_first_lines = 2,👆は、各ファイルの最初2行を無視します。ログファイルの仕様上、最初2行はカラム名等の情報が入っているためです。
format_tsv_null_representation = '-'👆は、文字列 - をNull文字として認識します。
👇は、あるドメインの実行結果の一例です。
0 rows in set. Elapsed: 228.146 sec. Processed 1.61 million rows, 1.24 GB (7.05 thousand rows/s., 5.45 MB/s.)
Peak memory usage: 976.18 MiB.これで abc.example.com のCloudFrontの2024年8月分のログをClickHouseに導入することができます。あとは同じように、好きなドメイン・好きな期間のログを入れればOKです！
ログ導入の自動化
1つのINSERTクエリでS3にあるログをClickHouseに導入できるようになりましたが、ログは常に生成されているので、新しいログを分析したいときはその度にログのINSERTをする必要があります。毎回手動でクエリを走らせても問題ないですが、ここではバッチを定期実行することでログ導入の自動化を行いました。
実行フロー
EventBridgeでECSのTaskを定期的に実行。
ドメイン名がS3バケットでのprefixになっているので、そのprefix（ドメイン）一覧を取得。
取得してきたドメインを複数のTaskに分散させ、それぞれのTaskでログの挿入を並列実行。
自動化のポイント
ログ挿入を並列実行する理由は、大量のログデータを一気に挿入すると相当な時間が掛かってしまうため。
並列実行することで、一気に挿入することで数時間要する処理が、30分程度で完了。
ただし、並列実行の問題点は、同時にいくつものクエリを投げるため、ClickHouse側の負荷が急増してしまう可能性があることです。ClickHouseの場合、同時接続数に設定上の制限はない代わりに、同時実行されるクエリ数に比例してメモリの使用率が増加します。利用しているインスタンスタイプが耐えられる分の並列実行数にする必要があります。
感想
少なくとも数千クエリで同時Insert処理はやめた方がいいかも…汗
自動的にログの導入が可能になりましたが、依然としてログをリアルタイムに分析できていません。これはバッチの定期実行のタイミング見直しなどで改善の余地はあると考えています。
コストパフォーマンスの比較
AthenaとClickHouseでは、テーブル構成に違いがあります。
テーブル構成、コストと性能の比較
Athena
データ量と実行速度を鑑みて、ドメインごとにテーブルを作成しています
クエリごとにスキャンしたデータ量だけお金がかかります
ドメインごとでしか分析できません
例えば、全ドメインに対してURIごとのPV集計する際に、クエリの実行に数時間かかるので、1回の実行で数万円も掛かる見積もりとなります
ClickHouse
全てのドメインのログを1つのテーブルに入れています
EC2インスタンスとEBSの料金がかかります
全てのドメインのログを統括的に分析が可能です
例えば、Athenaと同様に、全ドメインに対してURLごとのPV集計する際に、クエリの実行が数十秒で終わります
まとめ
CloudFrontのログ分析をAthenaからClickHouseに移行したことで、ログ分析における時間とコストの削減を実現することができました。特に毎回のクエリ実行速度が速くなったのはもちろん、クエリ実行の度にお金の心配をしなくて良くなったのがデカかったです！（※もちろん、Athenaでの設定やログのパーティショニング方法を見直し最適化することで、クエリ速度の改善にはつながります。今回はOLAP特化のデータベースで分析するというアプローチを試しました。）
今後は、社内の他のプロダクトでもClickHouseを導入を目指しています。まだまだClickHouse機能を使いきれていないので、今後は複数台構成による分散処理や冗長化構成などに挑戦してみたいです！