Edited at

今さらだけど、AWS ELBのログをAthenaで解析(2) 毎時/毎分/5分ごとの集計


はじめに

AWS ELB(CLB/ALB)のログを解析するのに、Athenaはとても役立ちます。

複雑な集計をするには、SQL関数を活用する必要がありますが、SQLの処理系によって関数が異なるため、慣れるまで理解しづらいところがありました。

そこで、備忘も兼ねて、実際に役立ったAthenaのSQL関数・演算子をまとめることにしました。

今回は、時刻に関するものです。


動作環境

AWS ELBにはApplication Load BalancerとClassic Load Balancerがあり、それぞれでアクセスログの項目は異なります。例にあげたクエリーはApplication Load Balancer用で、下記のDDLステートメント文で作成されたテーブルで動作します。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/application-load-balancer-logs.html

なお、実際に本番環境でクエリーを発行する際は、Athena破産を防ぐために、パーティションを設定するか、LOCATIONに年月日のプレフィックスを含めて、スキャン範囲を絞ってください。


時間ごとの集計


毎時・毎分ごとの集計

たとえば、毎時ごと・毎分ごとに、アクセス数を集計したい場合。

Application Load Balancerのログでは、時刻(time)は文字列として認識されます。

このため、substr()関数で、時刻(time)を時・分までで切り取ってGROUP BY節に指定します。


毎時ごと

SELECT substr(time,1,13) AS time_every_hour,

count(*) AS request_count
FROM alb_logs
WHERE time >= '2018-11-22T03'
AND time < '2018-11-22T06'
GROUP BY substr(time,1,13)
ORDER BY time_every_hour;


<結果>毎時ごと

Results

time_every_hour request_count
1 2018-11-22T03 59392
2 2018-11-22T04 60515
3 2018-11-22T05 61353


毎分ごと

SELECT substr(time,1,16) AS time_every_min,

count(*) AS request_count
FROM alb_logs
WHERE time >= '2018-11-22T03:00'
AND time < '2018-11-22T03:03'
GROUP BY substr(time,1,16)
ORDER BY time_every_min;


<結果>毎分ごと

Results

time_every_min request_count
1 2018-11-22T03:00 967
2 2018-11-22T03:01 951
3 2018-11-22T03:02 997


5分ごとの集計

毎時ごと・毎分ごと、あるいは10分ごとであれば、substr()関数による文字列分割で簡単に実現できますが、5分ごとなどの場合は、文字列を分割するだけでは対応できないため、ひと手間が必要です。

毎分ごとでは、GROUP BY節にsubstr(time,1,16)で時刻(time)の16文字目までを指定しますが、5分ごとでは、16文字目(分の一の位)を5分単位で丸める必要があります。そこで、SQLのCASE式を使って、16文字目(分の一の位)が0~4の場合は0に、5~9の場合は5にします(SQLでは文字列の結合に||を使います)。


5分ごと

SELECT CASE WHEN substr(time,16,1) < '5' 

THEN substr(time,1,15) || '0'
ELSE substr(time,1,15) || '5'
END AS time_every5min,
count(*) AS request_count
FROM alb_logs
WHERE time >= '2018-11-22T03:00'
AND time < '2018-11-22T03:15'
GROUP BY CASE WHEN substr(time,16,1) < '5'
THEN substr(time,1,15) || '0'
ELSE substr(time,1,15) || '5'
END
ORDER BY time_every5min;


<結果>5分ごと

Results

time_every5min request_count
1 2018-11-22T03:00 4950
2 2018-11-22T03:05 5121
3 2018-11-22T03:10 5006


WAFレートベースルールの事前調査

この5分ごとの集計は、AWS WAFのレートベースルールを設定する際の事前調査に必要でした。レートベースルールでは、同じIPからの5分間のアクセス数が閾値を超えると、アクセスを制限します。現状を確認するために、5分ごと×IPアドレスごとのアクセス数を集計するには、次のクエリーを実行します。


WAFレートベースルールの事前調査(5分ごと×IPアドレスごと)

SELECT CASE WHEN substr(time,16,1) < '5' 

THEN substr(time,1,15) || '0'
ELSE substr(time,1,15) || '5'
END AS time_every5min,
client_ip,
count(*) AS request_count
FROM alb_logs
GROUP BY CASE WHEN substr(time,16,1) < '5'
THEN substr(time,1,15) || '0'
ELSE substr(time,1,15) || '5'
END,
client_ip
ORDER BY request_count DESC limit 100;


日本時間への変換

SQLにはAT TIME ZONE演算子があるため、簡単に変換できると思ったのですが、実際に試してみると、データ型に注意が必要でした。

Application Load Balancerのログでは、時刻(time)は、あくまでも文字列として扱われます(表記はISO 8601形式)。一方、AT TIME ZONE演算子はtimestamp型に対してのみ実行できます。このため、単純にtime AT TIME ZONE 'Asia/Tokyo'とするとエラーになります。ISO 8601形式の文字列をtimestamp型に変換するfrom_iso8601_timestamp()関数を使って、from_iso8601_timestamp(time) AT TIME ZONE 'Asia/Tokyo'とする必要がありました。


毎時ごと(日本時間)

SELECT from_iso8601_timestamp(substr(time,1,13))

AT TIME ZONE 'Asia/Tokyo'
AS time_every_hour,
count(*) AS request_count
FROM alb_logs
WHERE time >= '2018-11-22T03'
AND time < '2018-11-22T06'
GROUP BY from_iso8601_timestamp(substr(time,1,13))
AT TIME ZONE 'Asia/Tokyo'
ORDER BY time_every_hour;


<結果>毎時ごと(日本時間)

Results

time_every_hour request_count
1 2018-11-22 12:00:00.000 Asia/Tokyo 59392
2 2018-11-22 13:00:00.000 Asia/Tokyo 60515
3 2018-11-22 14:00:00.000 Asia/Tokyo 61353

結果のところ、ちょっと時刻表示が長ったらしいです。調整するには、date_format()関数を使います。


毎時ごと(日本時間・時刻表示を短縮)

SELECT date_format(from_iso8601_timestamp(substr(time,1,13))

AT TIME ZONE 'Asia/Tokyo', '%Y-%m-%d %H:%i')
AS time_every_hour,
count(*) AS request_count
FROM alb_logs
WHERE time >= '2018-11-22T03'
AND time < '2018-11-22T06'
GROUP BY date_format(from_iso8601_timestamp(substr(time,1,13))
AT TIME ZONE 'Asia/Tokyo', '%Y-%m-%d %H:%i')
ORDER BY time_every_hour;


<結果>毎時ごと(日本時間・時刻表示を短縮)

Results

time_every_hour request_count
1 2018-11-22 12:00 59392
2 2018-11-22 13:00 60515
3 2018-11-22 14:00 61353

ちなみに、substr()関数ではなく、date_format()関数で分を丸めてしまえば、substr()関数は不要になります(結果も同じです)。


毎時ごと(日本時間・時刻表示を短縮・substrなし)

SELECT date_format(from_iso8601_timestamp(time)

AT TIME ZONE 'Asia/Tokyo', '%Y-%m-%d %H:00')
AS time_every_hour,
count(*) AS request_count
FROM alb_logs
WHERE time >= '2018-11-22T03'
AND time < '2018-11-22T06'
GROUP BY date_format(from_iso8601_timestamp(time)
AT TIME ZONE 'Asia/Tokyo', '%Y-%m-%d %H:00')
ORDER BY time_every_hour;

WHERE節の指定も日本時間にするなら、timestamp型同士で比較します。CAST('2018-11-22 12:00 Asia/Tokyo' AS timestamp)のほか、from_iso8601_timestamp('2018-11-22T12:00+09:00')という記述も可能です。


毎時ごと(日本時間・時刻表示を短縮・substrなし・WHEREも日本時間で指定)

SELECT date_format(from_iso8601_timestamp(time)

AT TIME ZONE 'Asia/Tokyo', '%Y-%m-%d %H:00')
AS time_every_hour,
count(*) AS request_count
FROM alb_logs
WHERE from_iso8601_timestamp(time) >=
CAST('2018-11-22 12:00 Asia/Tokyo' AS timestamp)
AND from_iso8601_timestamp(time) <
CAST('2018-11-22 15:00 Asia/Tokyo' AS timestamp)
GROUP BY date_format(from_iso8601_timestamp(time)
AT TIME ZONE 'Asia/Tokyo', '%Y-%m-%d %H:00')
ORDER BY time_every_hour;


参考資料

AthenaのSQL関数については、下記が参考になります。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/functions-operators-reference-section.html

S3 SELECTより、充実しています。

https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/dev/s3-glacier-select-sql-reference-sql-functions.html