こんにちは、なおにしです。
AWS WAF などが出力したアクセスログをAmazon S3に保存していて、それをAmazon Athenaで分析する際、特定の日時を指定して結果を表示したいことがあるかと思います。
保存されているログ量が少なければタイムスタンプの範囲を指定しつつ出力件数をLIMIT句で抑えるなどしながら目視確認することもできるかもしれませんが、大量に保存されているログを解析する際はより細かく時間指定をして調査したいこともあるかもしれません。そんな時に検討したことをご紹介します。
はじめに
S3バケットに保存されている大量のアクセスログに対して特に条件を設けずにクエリを実行すると、全てのアクセスログがAthenaによるスキャン対象となることで主に以下の問題が発生します。
- 想定外の課金が発生
- クエリの応答が遅い
Athena ではクエリ単位で課金が発生し、その大小はクエリによってスキャンされるデータ量に依存します。具体的には以下となります。
5.00USD per TB of data scanned.
なお、課金については以下の条件もあります。
バイト数はメガバイト単位で切り上げられ、クエリごとに最小 10 MB が課金されます。CREATE TABLE、ALTER TABLE、DROP TABLE などの Data Definition Language (DDL) ステートメント、パーティションを管理するステートメント、または正常に実行されなかったクエリに対しては課金されません。
例えば合計200GBのアクセスログに対して全データがスキャン対象となるクエリを100回実行した場合、以下のような計算になります。
200GB / 5TB(5000GB) × 5USD × 100回 = 20USD (150円/USDなら3,000円)
ある程度アクセス数がある環境で複数年のアクセスログを保存していて、そのログに対して対象期間を試行錯誤しながらクエリを実行するような状況であれば十分にあり得るパターンかと思います。
このようなパターンで推奨されているのがAthena でテーブルを作成する際に「Partition Projection (パーティション射影)」を使用することです。
Partition Projection を使用してパーティション化されたデータに対してクエリを実行することで、目的のパーティションに対してのみスキャンを行うことができるようになるため、クエリの応答速度の改善や課金の削減につながります。
Partition Projection の詳細は以下の記事をご参照ください。
やってみた
S3 に出力したAWS WAF のアクセスログに対してPartition Projection を使用したテーブルを作成してAthena でクエリを実行してみます。
前提
Athena でデータのパーティション化をする場合、データはS3で「Apache Hive スタイル」または「Apache Hive 以外のスタイルのパーティション化スキーム」のいずれかで格納されている必要があります。具体的には以下のような形式です。
| パーティションのスタイル | 例 |
|---|---|
| Apache Hive スタイル | country=us/... または year=2021/month=01/day=26/... など |
| Apache Hive 以外のスタイルのパーティション化スキーム | data/2021/01/26/us/6fc7845e.jsonなど |
つまり、スラッシュで区切られた箇所でパーティションを分けるので、スラッシュ区切りではなくオブジェクト名に日付が入っているパターンのアクセスログではパーティション化することができません。 このパターンは、例えばCloudFront のアクセスログが該当します。CloudFrontのアクセスログは以下の形式で出力されます。
/ .YYYY-MM-DD-HH.unique-ID.gz
その他の一般的なアクセスログをAthena でクエリするためのテーブル作成フォーマットは以下にまとまっています。
ありがたいことにPartition Projection に対応可能なログ出力を行うサービスについては、Partition Projection を用いたテーブルの作成方法も記載されています。AWS WAF のアクセスログの場合であれば以下になります。
上記を参考に実際にPartition Projection をAWS WAF のアクセスログに適用して〜…、と、この辺りまでまとめた後に既に先達の記事があることに気づきました。
なので、本記事では以下の観点を追加して改めてやってみます。
- パーティションを時間単位に変更してスキャンされるデータをより少なくするにはどうすればよいか
- より細かな時間を指定した結果を得るにはどのようにクエリすればよいか
時間単位でのパーティション作成
AWS WAF のロギング先としてS3を選択した場合のアクセスログの出力形式はこちらに記載されています。まとめると以下の形式で出力されます。
s3://(aws-waf-logs-で始まるバケット名)/AWSLogs/(AWSアカウントID)/WAFLogs/(リージョン名)/(web-acl名)/YYYY/MM/dd/HH/mm/(AWSアカウントID)_waflogs_(リージョン名)_(web-acl名)_YYYYMMddTHHmmZ_(ハッシュ文字列).log.gzしたがって、最も細かな粒度でパーティションを区切ろうとするのであれば分単位にすることもできそうでしたが、AWS WAF のウェブACL では5分間隔でログファイルをS3バケットに発行し、実際に以下のように5分間隔で区切られていたので今回は時間単位での分割を検討します。
Partition Projection でサポートされている型には以下の4つがあります。
- 列挙型(enum)
- 整数型(integer)
- 日付型(date)
- 挿入型(injected)
今回は日時に基づいてパーティション分割するため、日付型のプロパティを変更します。そこで、前述のAWS WAF 用のテーブル作成クエリのうち、以下の部分を変更します。
PARTITIONED BY (
`region` string,
`date` string)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://DOC-EXAMPLE-BUCKET/AWSLogs/accountID/WAFLogs/region/DOC-EXAMPLE-WEBACL/'
TBLPROPERTIES(
'projection.enabled' = 'true',
'projection.region.type' = 'enum',
'projection.region.values' = 'us-east-1,us-west-2,eu-central-1,eu-west-1',
'projection.date.type' = 'date',
'projection.date.range' = '2021/01/01,NOW',
'projection.date.format' = 'yyyy/MM/dd',
'projection.date.interval' = '1',
'projection.date.interval.unit' = 'DAYS',
'storage.location.template' = 's3://DOC-EXAMPLE-BUCKET/AWSLogs/accountID/WAFLogs/${region}/DOC-EXAMPLE-WEBACL/${date}/')簡略化のために、今回は東京リージョンのみで検証するため列挙型で定義されているリージョン名の要素を削除し、同様に今回は指定しなくても問題ない「STORED AS INPUTFORMAT 」と「OUTPUTFORMAT 」の表記を削除します(デフォルト値が使用されます)。すると以下のようになります。
PARTITIONED BY (
`date` string)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
LOCATION
's3://DOC-EXAMPLE-BUCKET/AWSLogs/accountID/WAFLogs/region/DOC-EXAMPLE-WEBACL/'
TBLPROPERTIES(
'projection.enabled' = 'true',
'projection.date.type' = 'date',
'projection.date.range' = '2021/01/01,NOW',
'projection.date.format' = 'yyyy/MM/dd',
'projection.date.interval' = '1',
'projection.date.interval.unit' = 'DAYS',
'storage.location.template' = 's3://DOC-EXAMPLE-BUCKET/AWSLogs/accountID/WAFLogs/ap-northeast-1/DOC-EXAMPLE-WEBACL/${date}/')だいぶすっきりしました。次にTBLPROPERTIESのprojectionから始まる箇所を変更します。直感的に修正できそうですが、それぞれのプロパティで設定可能な値はこちらを参考にしつつ、以下のプロパティを変更します。
| プロパティ名 | 変更前 | 変更後 |
|---|---|---|
| projection.date.range | 2021/01/01,NOW | NOW-1YEARS,NOW |
| projection.date.format | yyyy/MM/dd | yyyy/MM/dd/HH |
| projection.date.interval.unit | DAYS | HOURS |
rangeプロパティについてのみ補足します。
AthenaでPartition Projection を使用した場合、パーティションはクエリ実行時に動的に作成されます。例えば、上記の修正前のrange指定であれば、クエリ実行の都度2021/01/01から現在までのパーティションが作成されることになります。
しかし、多くの環境では設計時に特定のログ保存期間を設けているかと思います。S3へのアクセスログ出力であれば、ライフサイクル設定で例えば1年以上前のログについては削除するという運用になっているかもしれません。
そういうケースにおいて[2021/01/01,NOW] というように期間を静的に指定すると、不要なパーティションが作成されることで想定外の料金が請求される可能性もあります。
このため、rangeを[NOW-1YEARS,NOW]のように現在時刻を基準にした動的な指定にすることで、クエリが実行された時に実際に存在するログを対象としたパーティションが作成されるようになります。
テーブル作成のサンプルクエリと上記の変更内容をマージすると以下のとおりです。
CREATE EXTERNAL TABLE `waf_logs`(
`timestamp` bigint,
`formatversion` int,
`webaclid` string,
`terminatingruleid` string,
`terminatingruletype` string,
`action` string,
`terminatingrulematchdetails` array <
struct <
conditiontype: string,
sensitivitylevel: string,
location: string,
matcheddata: array < string >
>
>,
`httpsourcename` string,
`httpsourceid` string,
`rulegrouplist` array <
struct <
rulegroupid: string,
terminatingrule: struct <
ruleid: string,
action: string,
rulematchdetails: array <
struct <
conditiontype: string,
sensitivitylevel: string,
location: string,
matcheddata: array < string >
>
>
>,
nonterminatingmatchingrules: array <
struct <
ruleid: string,
action: string,
overriddenaction: string,
rulematchdetails: array <
struct <
conditiontype: string,
sensitivitylevel: string,
location: string,
matcheddata: array < string >
>
>,
challengeresponse: struct <
responsecode: string,
solvetimestamp: string
>,
captcharesponse: struct <
responsecode: string,
solvetimestamp: string
>
>
>,
excludedrules: string
>
>,
`ratebasedrulelist` array <
struct <
ratebasedruleid: string,
limitkey: string,
maxrateallowed: int
>
>,
`nonterminatingmatchingrules` array <
struct <
ruleid: string,
action: string,
rulematchdetails: array <
struct <
conditiontype: string,
sensitivitylevel: string,
location: string,
matcheddata: array < string >
>
>,
challengeresponse: struct <
responsecode: string,
solvetimestamp: string
>,
captcharesponse: struct <
responsecode: string,
solvetimestamp: string
>
>
>,
`requestheadersinserted` array <
struct <
name: string,
value: string
>
>,
`responsecodesent` string,
`httprequest` struct <
clientip: string,
country: string,
headers: array <
struct <
name: string,
value: string
>
>,
uri: string,
args: string,
httpversion: string,
httpmethod: string,
requestid: string
>,
`labels` array <
struct <
name: string
>
>,
`captcharesponse` struct <
responsecode: string,
solvetimestamp: string,
failureReason: string
>,
`challengeresponse` struct <
responsecode: string,
solvetimestamp: string,
failureReason: string
>,
`ja3Fingerprint` string
)
PARTITIONED BY (
`date` string)
ROW FORMAT SERDE
'org.openx.data.jsonserde.JsonSerDe'
LOCATION
's3://DOC-EXAMPLE-BUCKET/AWSLogs/accountID/WAFLogs/region/DOC-EXAMPLE-WEBACL/'
TBLPROPERTIES(
'projection.enabled' = 'true',
'projection.date.type' = 'date',
'projection.date.range' = 'NOW-1YEARS,NOW',
'projection.date.format' = 'yyyy/MM/dd/HH',
'projection.date.interval' = '1',
'projection.date.interval.unit' = 'HOURS',
'storage.location.template' = 's3://DOC-EXAMPLE-BUCKET/AWSLogs/accountID/WAFLogs/ap-northeast-1/DOC-EXAMPLE-WEBACL/${date}/')上記のクエリで作成したテーブルに対して特定の日時でデータを取得するクエリを実行してみます。例えば以下のようなクエリです。
SELECT
timestamp,
date_format(from_unixtime(timestamp/1000), '%Y/%m/%d %H:%i:%s') AS timestamp_converted,
action,
httprequest.country AS country,
NOW() AS date_now,
date
FROM
waf_logs_test
WHERE
date BETWEEN format_datetime(TIMESTAMP '2024-05-22 00:00:00', 'YYYY/MM/dd/HH') AND format_datetime(TIMESTAMP '2024-05-22 23:00:00', 'YYYY/MM/dd/HH')
ORDER BY timestamp DESCアクセスログに含まれているtimestampを分かりやすくするためにtimestamp_convertedカラムを、参考情報として現在時刻を表示するためにdate_nowカラムを追加しています。
Athenaのコンソール画面での実行結果は以下のとおりです。
「実行時間」は非常に短く、「スキャンしたデータ」に関しては最小課金単位である10MBを下回っています。 パーティション分割に用いたdateカラムについても、直近日時のパーティションからデータを取得できていることが確認できます。今回は時間単位でパーティションを分割しましたが、もしアクセスログの出力量がより多い環境の場合は分単位でパーティション分割の上、インターバルを5分刻みにしても良いかもしれません。
より細かな時間を指定してデータを取得するクエリ
前述のパーティション分割を有効にするためにWHERE句でdateカラムを指定する必要がありますが、時間単位でパーティション分割されたデータであるためより細かな粒度で時間を指定してデータを取得したい場合はもう少し工夫が必要になります。
具体的には、dateを指定して取得したデータに対してさらにtimestampの条件を絞ることができれば要件を満たすことができます。
このような時には副問い合わせ(サブクエリ)のためにWITH句を使用するとクエリとして見やすく、そして再利用もしやすく実装することが可能です。例えば以下のようなクエリです。
WITH
waf_logs_test_partition AS (
SELECT
*
FROM
waf_logs_test
WHERE
date BETWEEN format_datetime(TIMESTAMP '2024-05-22 00:00:00', 'YYYY/MM/dd/HH') AND format_datetime(TIMESTAMP '2024-05-22 23:00:00', 'YYYY/MM/dd/HH')
)
SELECT
timestamp,
date_format(from_unixtime(timestamp/1000), '%Y/%m/%d %H:%i:%s') AS timestamp_converted,
action,
httprequest.country AS country,
NOW() AS date_now,
date
FROM
waf_logs_test_partition
WHERE
timestamp BETWEEN (to_unixtime(date_parse('2024-05-22 07:30:00', '%Y-%m-%d %H:%i:%s')) * 1000) AND (to_unixtime(date_parse('2024-05-22 07:31:00', '%Y-%m-%d %H:%i:%s')) * 1000)
ORDER BY timestamp DESCWITH句によってwaf_logs_test_partitionという一時的なテーブルを作成して、そのテーブルに対してtimestampの条件を指定しています。また、今回はtimestampで7:30〜7:31(UTC)の1分間に絞ったデータを取得対象にしてみました。
Athenaのコンソール画面での実行結果は以下のとおりです。
データのスキャン自体はパーティションに対して行われるため、「実行時間」および「スキャンしたデータ」に関してはWITH句を使用しないパターンと変わりません。一方で、取得したデータに関してはtimestampで絞ることができています。
まとめ
AWS WAFのアクセスログに対してPartition Projection を活用しつつより詳細に時間指定を行う方法について確認しました。トラブルシューティングの際など、アクセスログを時間指定で頻繁にクエリするような場面などでこの記事が参考になれば幸いです。
2
