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

今さらだけど、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