初投稿です。
今までQiitaにはだいぶお世話になっており、自分もすこしは貢献したいなと思ったので最近Athena使った時のこと書こうと思います。

apacheやfluentdを使ったAthenaでのアクセスログ解析はネットにたくさん落ちていたのだが、nginxのログをAthenaで解析する情報が少なかったので、同じように困っている人がいたら参考にしてください。

前置き

前職では、アクセスログを解析するDWHがあったのでアクセスログの集計は簡単にできたのだが、現在のシステムはそんな素晴らしいものはなく、今までバッチファイルで集計していた。ただ、特定の条件のアクセスを過去データまで集計したい場合にバッチだとつらくなったのでAmazon Athenaを利用して集計してみることにした。

システム環境

Webサーバはnginxを利用しており、アクセスログの形式は一般的なものを使用。具体的な形式は以下のとおり。

XXX.XXX.XXX.XXX - - [21/Oct/2017:03:08:31 +0900] "GET /js/service.js?cv1=&cv2=&cv3=&cv4= HTTP/1.1" 200 39 "https://clientA.co.jp" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36" "YYY.YYY.YYY.YYY"

やりたいこと

システムへのアクセスがリファラー別(ドメイン別)にどれくらいあるのかを調査したい

手順

アクセスログをS3に置く

各サーバのアクセスログをローカルにダウンロードしてきて、S3に置く。
今回は、月別で集計がしたかったので、バケット名はaraiguma47-bucketとし、その配下に日時のディレクトリを切ってアクセスログを置いた。この際、アクセスログは圧縮したままでOKとのこと！

araiguma47-bucket/athena/201705/XXX-access.log-20170501.web01.gz
araiguma47-bucket/athena/201705/XXX-access.log-20170501.web02.gz
araiguma47-bucket/athena/201705/XXX-access.log-20170502.web01.gz
....
araiguma47-bucket/athena/201705/XXX-access.log-20170531.web02.gz

Athenaの設定

テーブルの追加

Amazon Athenaのサービスへアクセスし、左カラムのTABLESのAdd Tableをクリック。

Step1: Name&Location

Dabase名やTable名、S3の位置を設定する

Create new database: access_log
Table Name : log_201705
Location of Input Data Set : s3://araiguma47-bucket/athena/201705/

とし、Nextをクリック

Step2: Data Format

アクセスログの形式を指定する。
今回はApache Web Logsを選択し、Regexには、

^(\S+) \S+ \S+ \[([^\[]+)\] "(\w+) (\S+) (\S+)" (\d+) (\d+) "([^"]+)" "([^"]+)".*

を設定し、Nextをクリック
※ Regexの最後に改行が入らないように注意してください。

Step3: Columns
データのそれぞれのカラムに名前をつける。その際、データのtypeも指定する。
複数カラムがある場合、一つづつ設定が大変なのと、同じ処理を何度もしなければいけないため、今回はBulk add columnsを使用する。Bulk add columnsをクリックしたら、以下を記載する。

ip                STRING,
time_local        STRING,
method            STRING,
uri               STRING,
protocol          STRING,
status            int,
bytes_sent        int,
referer           STRING,
user_agent        STRING

追加ができたら、Nextをクリック

Step4: Partitions
パーティションを設定することによって、検索範囲を絞れたりするらしいが、今回は設定なしでいくこととした。
完了したら、Create tableをクリックしtableを作成。Query successful.とでていればOK。作成されたtableの右の...をクリックし、preview tablesでデータが入っていることを確認。

いざQueryを実行！

Databaseを選択し、Queryを書いてみる。
今回使用したQueryは以下の通り。Athenaが思ったより早かったので、Queryの速度の最適化は行ってません。

SELECT
        count(referer LIKE '%www.clientA.co.jp%' OR null) AS clientA,
        count(referer LIKE '%www.clientB.co.jp%' OR null) AS clientB,
....
        count(referer LIKE '%www.clientC%' OR null) AS clientZ,
        count() AS total
FROM access_log."log_201705"
WHERE uri LIKE '/js/service.js%';

無事結果が取得できたらOKです。

結果

バッチファイルで数時間かかっていた処理が数分で返ってくるようになったので、さまざまな集計がこれでできるようになりました。さすがAmazonと言えるくらい、満足いく結果です。

Athenaの料金

Amazon AthenaはQueryによりスキャンしたデータ量によって課金されます。金額はそのクエリでスキャンされたデータ1TBごとに$5となっており、利用する前は金額が高くならないか慎重にQueryを投げてました。しかし、日に数百万,数千万件ほどのアクセスログくらいなら、圧縮したまま検索がかければ、日に1GB、月でも数10GBにしかならないため、バッチなどで何度もQueryを投げるような処理をしなければ、かなり料金は安いです。

実際、試行錯誤中、Athenaを使うためにQueryを何度も投げましたが、結果$1すらいってませんでした。ログの数年分をAthenaにいれて、スキャン範囲を大きくしたり、バッチなどで何度もQueryをなげたりしなければ、アクセスログくらいならほとんど金額を気にしないで利用できそうです。

一応注意としては、どんなにスキャン範囲がすくなくとも、10MB未満のクエリは10MBと計算されるらしいですが、個人で使えるくらい料金は安かったです。

課題

今回は月ごとにテーブルを作り集計したため、日時による検索が不要のため、日時のtypeをstgingとして読み込みました。ただ、アクセスログを数年分一つのテーブルに入れて一気に集計するなどしたい場合は、日時のtypeをtimestampやdateにする必要があるので、そこらへんを改善したいです。

また、partitionsを設定すれば金額を安くしたり、検索時間を短縮することも可能らしいので、時間があったらそこらへんの設定も追加したいです。

fluentdとか使って、アクセスログをS3に自動で転送したり、形式変えてAthenaでパースしやすくすれば、よりよくなるんだろうがそれはそのうち。。

参考にした、参考になるサイト

AWS Athena の詳細を調べてみた
https://qiita.com/Hachi_0601/items/930e4b6d1b84ce5d62e5
Amazon Athenaを使ってみた
https://qiita.com/kojiisd/items/3bd36a6b717db1fae81c
AWS Athenaを使ってみよう(KlabGames Tech Blog)
http://klabgames.tech.blog.jp.klab.com/archives/1062973904.html
FluentdのログフォーマットでS3に保存されたApacheのアクセスログをAmazon Athenaで集計する
http://fusic-kiban.hatenablog.com/entry/2016/12/15/000000
AWS AthenaでPartitionを作る
https://qiita.com/r4-keisuke/items/d3d339b76d4368b6b30a