Amazon Athena で CloudFront ログを5分ごとで集計してみた

こんにちは、リサリサです。

AWS WAFのレートベースルールでは、以下のブログのようにしきい値を超えたスピードでリクエストを送るIPアドレスをブラックリストに追加して、アクセスを制限する事ができます。

5分間の間に指定した回数以上のアクセスが来たらブロックするようにできるのですが、その指定回数をどの程度で設定すればいいのか、どの程度で設定すると、どのアクセスがブロックされるのかの目安を確認するために、5分毎の集計をする必要がありました。

SQLで5分毎の集計をするには、少し工夫が必要だったので、記事にしてみます。

やりたいこと

date time client_ip
2022-01-24 9:00 1.1.1.1
2022-01-24 9:01 1.1.1.1
2022-01-24 9:05 1.1.1.1
2022-01-24 9:06 1.1.1.1
2022-01-24 9:06 1.1.1.2

というアクセスログを

date time client_ip count
2022-01-24 9:00-9:04 1.1.1.1 2回 
2022-01-24 9:05-9:09 1.1.1.1 2回
2022-01-24 9:05-9:09 1.1.1.2 1回

のように5分単位で集計して、アクセスの多いIPの集計がしたい

やってみた

こちらの通りにテーブルを作ってあることを前提とします。

ビューを作る

毎回 SELECT文に 5分毎に集計する文を書いてもいいのですが、ぐちゃぐちゃしてしまうので、このテーブルをラッピングする形でビューを作ってみました。

元の全項目「*」に、5分単位で時間を切り捨てた「trunc_datetime」を作って追加しています。

ビューにしてしまうと、SELECT文がすっきりして便利でした。※パーティションを考慮していません。パーティションを切りたい場合、別途考慮が必要だと思います。

CREATE VIEW view_cloudfront_logs AS
SELECT
*,
from_unixtime(floor(to_unixtime(date_parse(request_date || ' ' || request_time, '%Y/%m/%d %H:%i:%s'))/300)*300) trunc_datetime
FROM cloudfront_logs

5分なので、300(秒)です。10分単位なら300を600に、15分単位なら300を900にすればよいです。

1分単位、1時間単位の場合は date_trunc を使った方がお手軽でよいと思います。

ビューをSELECTしてみる

今作ったビューに対してSELECTしてみます。

SELECT trunc_datetime, client_ip, count(*) count
FROM view_cloudfront_logs 
GROUP BY trunc_datetime, client_ip
ORDER BY count(*) desc
limit 10;

5分単位で切り捨てられた日時で集計ができました。

上記ビュー作成SQLの解説

Athena は Presto というクエリエンジンに基づいているようで、私が慣れ親しんだ Oracle系や Postgres系の関数と違うところも多く苦労したので、 少し解説を置いておきます。

公式リファレンスは Amazon Athena の Presto 関数 を参照してください。

from_unixtime(floor(to_unixtime(date_parse(request_date || ' ' || request_time, '%Y/%m/%d %H:%i:%s'))/300)*300) trunc_datetime

では、時間を5分単位で切り落としています。中から外へ向けて、簡単な解説を置いておきます。

request_date || ' ' || request_time

STRING型の「request_date」、半角スペース、STRING型の「request_time」を文字列結合して、

date_parse(xxx, '%Y/%m/%d %H:%i:%s')

文字列を、フォーマット「%Y/%m/%d %H:%i:%s」で、TIMESTAMP型に変換し、

to_unixtime(xxx)

unixtime の DOUBLE型に変換し、

xxx/300

300(60秒×5分)秒で割って

floor(xxx)

切り落として、

xxx*300

300秒かけて、

from_unixtime(xxx)

unixtime から TIMESTAMP型に戻しています。

例

「2022-01-24 9:00:05」が unixtime「1642982405」になって、300で割られて「5476608.016666667」になって、切り捨てられて「5476608」になって、300かけて「1642982400」になって、「2022-01-24 9:00:00」のTIMESTAMPに戻ってます。

最後に

どなたかのお役に立てれば幸いです。