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