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