More than 3 years have passed since last update.

S3 のサーバアクセスログから、Athena を使って日毎のリクエスト数を取得する

Posted at 2020-03-11

DB、テーブルは作成済みの前提

GET、SELECT、他のすべてのリクエスト

SELECT regexp_extract(time, '\d{2}/\w+/\d{4}') AS by_day 
, count(*) AS cnt
FROM s3_accesslog
WHERE  (operation NOT LIKE '%PUT%'
        AND operation NOT LIKE '%POST%'
        AND operation NOT LIKE '%COPY%'
        AND  operation NOT LIKE '%LIST%')
GROUP BY regexp_extract(time,'\d{2}/\w+/\d{4}')
ORDER BY by_day

PUT、COPY、POST、LIST リクエスト

SELECT regexp_extract(time, '\d{2}/\w+/\d{4}') AS by_day 
, count(*) AS cnt
FROM s3_accesslog
WHERE  (operation LIKE '%PUT%'
        OR operation LIKE '%POST%'
        OR operation LIKE '%COPY%'
        OR  operation LIKE '%LIST%')
GROUP BY regexp_extract(time,'\d{2}/\w+/\d{4}')
ORDER BY by_day

参考サイト

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up