S3 サーバーアクセスログを Athena + シンボリックリンクを利用して分析してみた

村上紗敏

2023.08.15

アノテーション、テクニカルサポートチームの村上です。
Athena を利用して S3 上のログを分析したいというお問い合わせいただくことがあります。
S3 サーバーアクセスログのようにフォルダが作成されずログファイルがバケット内にフラットに配置されるログの場合は、クエリの対象となるデータのスキャン範囲がバケット全体となってしまう為に、クエリ時間が長くなる、データスキャンに対する課金額が大きくなるといった課題があります。

Athena ではパーティションという概念があり、パーティションを指定することでスキャン対象のデータを絞ることができクエリの実行時間とサービス利用料（スキャンしたデータに対しての従量課金）を削減することができます。

以上のような理由があり、バケット内にフラットに配置されたログについてのお問い合わせがあると、下記ブログも併せてご案内していました。　　

ご案内している以上、一度は自分でも実装しておかないとというのが本記事執筆のモチベーションとなりました。実際に実装してみると、「アレ？」と悩むポイントがいくつかあったので、私なりに嚙み砕いた内容でお伝えしますので、ぜひお付き合いください。

Athena パーティションとは？

普段使いの言葉で言うとフォルダのようなものでしょうか。Athena でクエリを実行する際に、あるフォルダ配下にあるデータのみをクエリの対象データとして指定することができます。S3 と Athena において適切なパーティション設定がされている場合、クエリの WHERE 句で WHERE dt=2023-07-02 等と指定して S3 内の全データのうち特定のデータのみスキャンの対象とすることができます。

シンボリックリンクとは？

シンボリックリンク【symbolic link】 symlink

シンボリックリンクとは、オペレーティングシステム（OS）のファイルシステムの機能の一つで、特定のファイルやディレクトリを指し示す別のファイルを作成し、それを通じて本体を参照できるようにする仕組み。

Windows だと、ショートカットにあたります。
ディスクトップ上に配置しておいたショートカットをダブルクリックするとリンクが設定してあるファイルに跳ぶことができますが、ショートカットを消してもリンクが消えるだけでリンクのファイルには影響がありません。

実現したいこと

本記事では、ログファイルが存在する S3（バケット 1 ）とは別の S3 （バケット 2 ）上にシンボリックリンクを作成して、バケット 1 に存在する全ログファイルの内の一部のログファイルのみをデータスキャン対象とする方法をご紹介します。
Athena からクエリを実行する際に Loacation としてはバケット 2 を指定するのですが、Athena がバケット 2 の指定したパーティションを読み込みにいくと、オブジェクト URL が記述されたテキストファイル（マニフェストファイル）が格納されており、その URL に従ってバケット 1 内のオブジェクトを読み込みにいくようなイメージとなります。

やってみた

下準備

S3 のサーバーアクセスのログ記録を有効にした S3 バケット
test-bucket20230702

S3 サーバーアクセスのログ記録が配信されるターゲットバケット（バケット 1 ）
s3-accesslog-source-bucket

シンボリックリンクを設定する S3 バケット（バケット 2 ）
bucket-for-athena-symboliclink-query

S3（s3-accesslog-source-bucket バケット 1 ）内に配置されている S3 サーバーアクセスのログファイル
※ 実際には、2023-07-02 以外にも、2023-07-06、2023-07-07、2023-07-08、2023-08-11 についてのサーバーアクセスログが配置してあります。

$ aws s3 ls s3://s3-accesslog-source-bucket/  
2023-07-02 14:12:45        677 2023-07-02-05-12-44-EB21F0A3B3CDD1EF
2023-07-02 14:14:57       1376 2023-07-02-05-14-56-B7288123B8BDF900
2023-07-02 14:15:26        509 2023-07-02-05-15-25-E8D85A5E5ACDBD78
2023-07-02 14:17:52        677 2023-07-02-05-17-51-BF8C47DE12611DB5
2023-07-02 14:18:33        693 2023-07-02-05-18-32-F94F74FF881870E8
2023-07-02 14:18:37        678 2023-07-02-05-18-36-ECC55D4A154829CC
2023-07-02 14:18:50        747 2023-07-02-05-18-49-7FE07D47A43CB8BA
2023-07-02 14:19:23        677 2023-07-02-05-19-22-F4780C55164E285A
2023-07-02 14:21:32        693 2023-07-02-05-21-31-2262A829D5A5C9BA
2023-07-02 14:21:43        728 2023-07-02-05-21-42-AAECB13C180BC7A1
2023-07-02 14:21:53        682 2023-07-02-05-21-52-0614D40B68E86295
2023-07-02 14:22:01        686 2023-07-02-05-22-00-DE584B588E1ED953
2023-07-02 14:22:40        512 2023-07-02-05-22-39-E923759804A062B0
2023-07-02 14:22:46        748 2023-07-02-05-22-45-ED828BB1D159CFF5
2023-07-02 14:23:41        699 2023-07-02-05-23-40-0D8A5784109EF7D5
2023-07-02 14:25:20        728 2023-07-02-05-25-19-52C5486F963ADD9E
2023-07-02 14:25:59        677 2023-07-02-05-25-58-BF6151D2C4898D81
2023-07-02 14:26:17        416 2023-07-02-05-26-16-A523E076B85356D4
2023-07-02 14:26:30        693 2023-07-02-05-26-29-2054FF7E47C36D65
2023-07-02 14:27:13        512 2023-07-02-05-27-12-BD44F9F1E1C2FE42
2023-07-02 14:27:47        522 2023-07-02-05-27-46-DC45D72E301EE620
2023-07-02 14:28:08       8534 2023-07-02-05-28-07-14AF6E0EC9ABD460
2023-07-02 14:28:23        414 2023-07-02-05-28-21-954749608DAACB80
2023-07-02 14:30:17       1405 2023-07-02-05-30-16-3378BFF81B9DC05A
2023-07-02 14:30:24       8584 2023-07-02-05-30-23-2F64CB302AD85982
2023-07-02 14:30:39        449 2023-07-02-05-30-38-637DA1BF95BBCE42
2023-07-02 14:31:43       1373 2023-07-02-05-31-42-5738117A15DDB8BE
2023-07-02 14:32:01       8288 2023-07-02-05-32-00-A6771930B857B8B9
2023-07-02 14:32:37        693 2023-07-02-05-32-36-90D9A9321ACA734A
2023-07-02 14:32:54        728 2023-07-02-05-32-53-3D99E291BC4EBF76
2023-07-02 14:33:24        677 2023-07-02-05-33-23-6C179A01B8BCB0C2
2023-07-02 14:34:24       2127 2023-07-02-05-34-23-22FA4380C2CB1619
2023-07-02 14:35:41        677 2023-07-02-05-35-40-0F9DE86D27272CEF
2023-07-02 14:36:02       8763 2023-07-02-05-36-01-486832ADE22D5055
2023-07-02 14:36:50        677 2023-07-02-05-36-49-370BC142DEC62B0D

パーティションが設定されてない状態で Athena からクエリを実行してみる

S3 サーバーアクセスログに対してクエリを実行する為に、Athena テーブルを作成します。Athena テーブル作成にあたり、re:Post 記載の手順をそのまま実行しました。

Athena を使用して Amazon S3 サーバーアクセスログを分析する方法を教えてください。

create database s3_access_logs_db

CREATE EXTERNAL TABLE `s3_access_logs_db.mybucket_logs`(
  `bucketowner` STRING,
  `bucket_name` STRING,
  `requestdatetime` STRING,
  `remoteip` STRING,
  `requester` STRING,
  `requestid` STRING,
  `operation` STRING,
  `key` STRING,
  `request_uri` STRING,
  `httpstatus` STRING,
  `errorcode` STRING,
  `bytessent` BIGINT,
  `objectsize` BIGINT,
  `totaltime` STRING,
  `turnaroundtime` STRING,
  `referrer` STRING,
  `useragent` STRING,
  `versionid` STRING,
  `hostid` STRING,
  `sigv` STRING,
  `ciphersuite` STRING,
  `authtype` STRING,
  `endpoint` STRING,
  `tlsversion` STRING)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://s3-accesslog-source-bucket/'

試しに、2 つのクエリを実行してみましたが、データを区切る仕切り（パーティション）がない為、どのようなクエリを実行しても S3 内のデータがフルスキャンされてからクエリが実行されます。

S3（バケット 2 ）上に Apache Hive 形式でフォルダを作成する

bucket-for-athena-symboliclink-query（バケット 2 ）において、dt=2023-07-02 のように Hive 形式でフォルダを作成します。フォルダの名前が、dt=2023-07-02 のようにキーと値で作成されているので、Athena からのクエリ実行時に WHERE 句において dt=2023-07-02 や dt=2023-08-11 のようにパーティションを指定したデータスキャンが可能となります。

Athena でのデータのパーティション化

Athena では Apache Hive スタイルのパーティションを使用できます。このパーティションのデータパスには、等号で連結されたキーと値のペア (例えば country=us/... または year=2021/month=01/day=26/...) が含まれています。つまり、それぞれのパスにより、パーティションのキーと値、両方の名前が表されます。

bucket-for-athena-symboliclink-query（バケット 2 ）上に以下のようにフォルダを作成しました。

※ year=2023/month=07/day=02 のようなフォルダ構成でパーティションを作成することも可能ですが、本記事ではシンプルな構成にしています。

S3（バケット 2 ）上のフォルダにシンボリックリンクで利用するマニフェストファイルを配置する

S3（バケット 1 ）上のログファイルのオブジェクト URL を日付別に取得して、マニフェストファイルに記述します。日付別でのオブジェクト URL の取得は、以下の手順で実行しました。

1. AWS CLI (s3 ls) コマンドの出力結果に対して、awk コマンドで指定したフィールドの値（オブジェクト URL）のみを取得してからテキストファイルへリダイレクト

aws s3 ls s3://s3-accesslog-source-bucket --recursive | awk '{print "s3://s3-accesslog-source-bucket/"$4}' > locations.txt

2. 1.で作成したファイルに対して日付ごとに grep して、日付別のオブジェクト URL が記述されたマニフェストファイルを作成

cat locations.txt | grep 2023-07-02 > 2023-07-02.txt

以下のようなマニフェストファイルを作成しました。

$ cat 2023-07-02.txt
s3://s3-accesslog-source-bucket/2023-07-02-05-12-44-EB21F0A3B3CDD1EF
s3://s3-accesslog-source-bucket/2023-07-02-05-14-56-B7288123B8BDF900
s3://s3-accesslog-source-bucket/2023-07-02-05-15-25-E8D85A5E5ACDBD78
s3://s3-accesslog-source-bucket/2023-07-02-05-17-51-BF8C47DE12611DB5
s3://s3-accesslog-source-bucket/2023-07-02-05-18-32-F94F74FF881870E8
s3://s3-accesslog-source-bucket/2023-07-02-05-18-36-ECC55D4A154829CC
s3://s3-accesslog-source-bucket/2023-07-02-05-18-49-7FE07D47A43CB8BA
s3://s3-accesslog-source-bucket/2023-07-02-05-19-22-F4780C55164E285A
s3://s3-accesslog-source-bucket/2023-07-02-05-21-31-2262A829D5A5C9BA
s3://s3-accesslog-source-bucket/2023-07-02-05-21-42-AAECB13C180BC7A1
s3://s3-accesslog-source-bucket/2023-07-02-05-21-52-0614D40B68E86295
s3://s3-accesslog-source-bucket/2023-07-02-05-22-00-DE584B588E1ED953
s3://s3-accesslog-source-bucket/2023-07-02-05-22-39-E923759804A062B0
s3://s3-accesslog-source-bucket/2023-07-02-05-22-45-ED828BB1D159CFF5
s3://s3-accesslog-source-bucket/2023-07-02-05-23-40-0D8A5784109EF7D5
s3://s3-accesslog-source-bucket/2023-07-02-05-25-19-52C5486F963ADD9E
s3://s3-accesslog-source-bucket/2023-07-02-05-25-58-BF6151D2C4898D81
s3://s3-accesslog-source-bucket/2023-07-02-05-26-16-A523E076B85356D4
s3://s3-accesslog-source-bucket/2023-07-02-05-26-29-2054FF7E47C36D65
s3://s3-accesslog-source-bucket/2023-07-02-05-27-12-BD44F9F1E1C2FE42
s3://s3-accesslog-source-bucket/2023-07-02-05-27-46-DC45D72E301EE620

上記手順を考えるのに苦労しました。もっとスマートな方法もあるかと思いますが、今回はこの手順で日付ごとのマニフェストファイルを作成していきます。
日付ごとに作成したマニフェストファイル（2023-07-02.txt）を、対応するフォルダ（dt=2023-07-02）へ以下のように配置しました。

$ aws s3 ls s3://bucket-for-athena-symboliclink-query --recursive
2023-08-13 11:15:23          0 dt=2023-07-02/
2023-08-13 11:16:55       8678 dt=2023-07-02/2023-07-02.txt
2023-08-13 11:15:36          0 dt=2023-07-06/
2023-08-13 11:17:10       1932 dt=2023-07-06/2023-07-06.txt
2023-08-13 11:15:51          0 dt=2023-07-07/
2023-08-13 11:17:23        414 dt=2023-07-07/2023-07-07.txt
2023-08-13 11:16:03          0 dt=2023-07-08/
2023-08-13 11:17:38        759 dt=2023-07-08/2023-07-08.txt
2023-08-13 11:16:17          0 dt=2023-08-11/
2023-08-13 11:17:50        828 dt=2023-08-11/2023-08-11.txt

パーティション化された Athena テーブルを作成する

パーティションとして dt を追加した mybucket_logs_with_partition テーブルを作成します。

CREATE EXTERNAL TABLE `s3_access_logs_db.mybucket_logs_with_partition`(
  `bucketowner` STRING,
  `bucket_name` STRING,
  `requestdatetime` STRING,
  `remoteip` STRING,
  `requester` STRING,
  `requestid` STRING,
  `operation` STRING,
  `key` STRING,
  `request_uri` STRING,
  `httpstatus` STRING,
  `errorcode` STRING,
  `bytessent` BIGINT,
  `objectsize` BIGINT,
  `totaltime` STRING,
  `turnaroundtime` STRING,
  `referrer` STRING,
  `useragent` STRING,
  `versionid` STRING,
  `hostid` STRING,
  `sigv` STRING,
  `ciphersuite` STRING,
  `authtype` STRING,
  `endpoint` STRING,
  `tlsversion` STRING
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$')
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION
  's3://bucket-for-athena-symboliclink-query/'

Athena クエリエディタで MSCK REPAIR TABLE コマンドを実行し、パーティションをロードします。

※ 既に dt=2023-07-02 のパーティションがある状態で他の全パーティションを追加した結果が表示されています。

パーティションを指定してクエリを実行してみた

意図したようにパーティションの追加ができたようなので、パーティション dt=2023-07-02 を指定してクエリを実行してみます。

パーティション dt=2023-07-02 を指定してクエリを実行しているので、スキャンしたデータ:142.67 KB となり、パーティションが無い状態でクエリを実行した場合（208.90 KB）と比べて 66 KB 程スキャン量を削減することができました。

他日付のパーティションも有効となっているか確認してみます。パーティション dt=2023-08-11 を指定してクエリを実行してみます。

2023-08-11 は、S3 サーバーアクセスログのファイル数が少ない為、15.02 KB のデータスキャン量でクエリを実行することができました。
ここで注意深い読者の方だと、バケット 1 に対してデータを全件取得した場合と比べて、バケット 2 上のパーティションを指定したデータ全件取得の方がクエリ実行時間が長くなっていることに気がついたかと思います。あくまで推測ですが、シンボリックリンクを利用すると個々のオブジェクト URL に対して Athena がデータを読み込みに行く必要があり、その処理の累積による結果と考えられます。

運用面から S3 に保存されたログへのシンボリックリンクの作成と利用について考えてみる

結論から言うと、特定日付のログのみを重点的に調べたいような場合は、シンボリックリンクを作成・利用してみてもいいかと思います。ただし、シンボリックリンク作成の手間を考えると以下の re:Post に記載があるように S3 のライフサイクルポリシーでスキャンするデータ量を削減するだけでいいように思いました。ログをずっと S3 標準においておく必要もないのですから。

Athena を使用して Amazon S3 サーバーアクセスログを分析する方法を教えてください。

サーバーアクセスログのバケット用のライフサイクルポリシーを作成するのがベストプラクティスです。ログファイルを定期的に削除するようにライフサイクルポリシーを設定できます。これにより、各クエリで Athena が分析するデータの量を減らすことができます。

まとめ

先人のブログでは、シンボリックリンクを作成するオブジェクト数が多い場合のマニフェストファイル作成方法への言及が無かったので、そこを補完できるブログになったかなと思います。手を動かしてブログ化したことで、自分の中でもいろいろと腹落ちしました。

この記事がどなたかのお役に立てば幸いです。

S3 サーバーアクセスログを Athena + シンボリックリンクを利用して分析してみた

Athena パーティションとは？

シンボリックリンクとは？

実現したいこと

やってみた

下準備

パーティションが設定されてない状態で Athena からクエリを実行してみる

S3（バケット 2 ）上に Apache Hive 形式でフォルダを作成する

S3（バケット 2 ）上のフォルダにシンボリックリンクで利用するマニフェストファイルを配置する

パーティション化された Athena テーブルを作成する

パーティションを指定してクエリを実行してみた

運用面から S3 に保存されたログへのシンボリックリンクの作成と利用について考えてみる

まとめ

参考資料

イベント

EVENT【6/5（水）】QuickSightとTableauのデモで営業分析を解説！アクションに繋げるダッシュボード設計

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

S3 サーバーアクセスログを Athena + シンボリックリンクを利用して分析してみた

Athena パーティションとは？

シンボリックリンクとは？

実現したいこと

やってみた

下準備

パーティションが設定されてない状態で Athena からクエリを実行してみる

S3（バケット 2 ）上に Apache Hive 形式でフォルダを作成する

S3（バケット 2 ）上のフォルダにシンボリックリンクで利用するマニフェストファイルを配置する

パーティション化された Athena テーブルを作成する

パーティションを指定してクエリを実行してみた

運用面から S3 に保存されたログへのシンボリックリンクの作成と利用について考えてみる

まとめ

参考資料

イベント

EVENT【6/5（水）】QuickSightとTableauのデモで営業分析を解説！アクションに繋げるダッシュボード設計

EVENT【5/29（水）大阪】Alteryxの使い方から導入メリットまですべてがわかるセミナー＆ワークショップ

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

関連記事

SQL JOIN について調べてみた

SQL COUNT 関数について調べてみた

Amazon CloudFront のアクセスログを集計して TLS バージョンの使用状況をカウントしてみる

Amazon AthenaのクエリをAmazon S3 Object Lambdaアクセスポイントに対して実行してみました

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！