AWS CloudFrontのログをRDS(MySQL)に蓄積する

Amazon Web Services

大規模なWEBサイトを運営しているのであればアクセス解析のためだけでなくデータ分析用のシステムが揃っていたり、それこそデータ分析のスペシャリストがいたりといいのでしょうが、そういうところへのリソースはなかなか確保できないことの方が多いわけです

ちょっと楽したいけど、大掛かりな準備はできない。けどもうちょっとましな分析ができるようにしたい。そんな葛藤でもんもんとするシステム管理者の人も多いのではないでしょうか

AWSのCloudFrontを使い出して3ヶ月が経過し、CloudFrontのログファイルをS3からローカルに保存してQlikViewで分析するようにはしていたのですが、ファイルは溜まっていくし、ファイルサーバーの容量は減り続けるし、このままでは過去に遡りたくても限界が早いうちにくるなと感じていました

それならばデータベースに保存してしまえばかなり改善できるのではと考えた次第です
EC2にMySQLを立ててログ蓄積を実施する方が楽な気もするけど、せっかくRDSがあるのでRDSを活用して構築することにしました
そうすればDBの容量やスペックも後から簡単に変更できるというのが利点です。インスタンスを専用で準備しておけば分析で負荷がかかってもEC2側には影響がでないのでWEBサイトサーバーの運用も気にしなくて構いませんしね

何よりもRDSにデータを保存しておくことによって、現在の規模ではオーバースペックなRedshiftや、今後楽しみなQuickSightの活用に向けて準備もしておけるとも考えています

【構成】

・EC2(既存のインスタンスでもOK)
・RDS:MySQL(ログ保存目的用で新インスタンス)
スペック:db.t2.small、10GB

EC2にS3からログを移動するフォルダを作る

S3からログファイルを移動

CloudFrontのログはgz形式で保存されているので解凍

ログを保存するテーブル作成

必要な項目やindexなどは適宜変更して構いません

CloudFrontのログファイルに記載されるアクセス日時は協定世界時(UTC)となります。その都度日本時間に変更するのは面倒なので日本時間に変換した値を保存する用のカラム『jp-access-date』と『jp-access-time』を追加しています
INSERT_TIMEは自動実行されているかを確認する際に見たいだけで追加したので不要であれば消して大丈夫です

ログファイルの名前修正

EC2のcronで自動処理を実施するため、MySQLにデータをインサートさせる処理はバッチ処理の流れになります。
そのため、『mysqlimport』コマンドを使用するのですがインサートするデータベース名は指定できるものの、テーブル名はファイル名から自動算出されることになりますので、先ほど解凍したgzファイルの名前へをテーブル名付きに変更します

これで
E3G5AGCZT47A1.2015-10-12-05.b33ce0d27 だったファイル名が
CloudFrontLog.E3G5AGCZT47A1.2015-10-12-05.b33ce0d27 に変更されます

データのINSERT

ログファイルからMySQLへデータをインサートする処理です

CloudFrontのログはタブ区切りなので
–fields-terminated-by=’\t’

CloudFrontのログは改行コードがLFになっているので
–lines-terminated-by=’\n’

EC2内に取り込んだローカルファイルを使用するので
–local

頭の2行目は飛ばして読み込むので
–ignore-lines=2
※先にsedを使用して2行目までを削除して、このオプションをつけなくても構いませんが2度手間になるのでここで指定しています

というオプションを付けています

処理済みファイルの後始末

『x-edge-request-id』をユニークにしているので、取り込み済みのファイルを何度取り込んでも重複したデータが発生することはありませんが、CloudFrontのログは1日で170個ぐらいできあがるので無駄な取り込み時間が発生します。
取り込み済みのファイルは不要であればフォルダ内のファイルを一括削除

念のためにとっておきたいのであれば、EC2内でデータ移動すると容量の圧迫がきになりますのでS3へバックしてもいいでしょう

ここまででCloudFrontのログファイルをMySQLへ取り込む作業は終了です。

UTC日時の変更

最後にテーブルの作成時に追加した日本時間を更新する処理を追加します

文字列として取り込んだ日付と時間のデータをCONCAT関数で結合します。ログファイルに記載される日時は協定世界時(UTC)となるので日本時間にするためにADDTIME関数で9時間を加算し、日付データとして『jp-access-date 』にセットします。
同じように時間帯もTIME_FORMAT関数を使って2桁の文字列にして『jp-access-time 』にセットするようにします。

バッチ処理

これをバッチファイル化してcronで1時間ごとに更新していきます

後はcronの設定を入れれば自動で取り込み処理が実施されることになります

#CloudFrontのログを取り込む処理(3時間ごとに設定)

こういう処理をLambdaを使ってできたりするのかな
できたらAWSがより便利に使えるようになるんだろうな