【Athena：テーブル作成解説】集約保存されたS3サーバアクセスログをバケット単位で分析したい

  • 2024年6月20日
  • 2024年6月20日
  • AWS, IT
AWS

こんにちはますのです。
普段Athenaなど触らないインフラ系運用保守エンジニアですが、この度やらないといけないタイミングが出てきました。
S3バケットのアクセスログの分析について調べる機会があったためメモです。

なお、本記事はAthena初心者が試行錯誤の奮闘しながら対応した内容なので、用語やら間違えてる箇所があると思いますが温かい目で眺めてください。

Athena、ナニモワカラナイ。

目次

やりたいこと

1つのログバケット内に複数のS3サーバアクセスログが保存されています。
該当のログバケットに対して、アプリケーションごとにAthenaで分析を行っていきたい所存です。

もし何も考えずに全バケットに対して毎回クエリ実行をするとデメリットがあります。

  • パフォーマンス低下
  • コスト増加

つきましては、パフォーマンス効率の良い方法を模索したいという課題です。

概要図

ざっくりと図解です。やりたいことは至ってシンプル。

前提：S3サーバアクセスログ保存設定

今回のS3サーバアクセスログ保存設定で、ログオブジェクトキーの形式は下記を選択しています。

AthenaでS3バケット内を分析する際、Prefix単位でパーティション分割することが出来ます。
日付単位、SourceBucket単位でクエリ実行することが出来るため、Athenaを利用する際はプレフィックスが設定される形式を選択しましょう。

パーティション仕様には、データを読み取るときに使用する Amazon S3 プレフィックスを Athena に伝える LOCATION プロパティが含まれています。この場合、このプレフィックスに保存されたデータのみがスキャンされます。
引用：Amazon S3 のテーブルの場所 > テーブルの場所とパーティション

Athena：設定方法（テーブル1つで管理パターン）

手順の流れはAWSドキュメントを参照します。

テーブル1つで管理し、クエリの記述で分ける形式の設定方法です。
想定しているS3Bucket構成は下記の通り。

S3://S3AccessLogs/(Prefix)/(AWSアカウントID)/(リージョン)/(A or B or Capp)/(TimeStamp)/Logfiles

データベースを作成する

Athenaコンソールを開く > [クエリエディタ]から下記クエリを実行してデータベースを作成します。

create database `s3-access-logs-db`
手順のDB名では「_」となっているところを「-」を利用したところエラーが発生しました。
> mismatched input ‘EXTERNAL’. Expecting: ‘OR’, ‘SCHEMA’, ‘TABLE’, ‘VIEW’「-」が使えないとありましたが、試しにバッククオート(`)で囲ったところ作成が出来ました。
もし同じようなエラーが出た場合はバッククオートをお試しください。参考：Athena の mismatched input ‘EXTERNAL’. Expecting: ‘OR’, ‘SCHEMA… に終止符

テーブルを作成する

手順通りに作成すると、日付ごとのパーティション射影設定のみが実現できます。
今回のやりたいことは「日付」＋「SourceBucket」によるパーティション射影設定です。

手順から少し設定を変更し、テーブル作成のクエリを実行します。
下記のサンプルクエリから、自身の環境に応じた箇所を修正していきます。

CREATE EXTERNAL TABLE [DATABASE].[TABLE](
`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,
`accesspointarn` STRING,
`aclrequired` STRING)
PARTITIONED BY (
`timestamp` string,`sourcebucketname` 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://[DestinationPrefix]/[SourceAccountId]/[SourceRegion]/'
TBLPROPERTIES (
'projection.enabled'='true',
'projection.timestamp.format'='yyyy/MM/dd',
'projection.timestamp.interval'='1',
'projection.timestamp.interval.unit'='DAYS',
'projection.timestamp.range'='2024/01/01,NOW',
'projection.timestamp.type'='date',
'projection.sourcebucketname.values'='[S3バケット A],[S3バケット B],[S3バケット C]',
'projection.sourcebucketname.type'='enum',
'storage.location.template'='s3://[DestinationPrefix]/[SourceAccountId]/[SourceRegion]/${sourcebucketname}/${timestamp}')

テーブル作成クエリの修正箇所

1行目：CREATE EXTERNAL TABLE [DATABASE].[TABLE](

ここで設定する内容は以下の2つです。

  • 事前に作成したデータベース名
  • これから作成するテーブル名
【変更例】
CREATE EXTERNAL TABLE `s3-accesss-logs-db`.`mybucket_logs`(
38行目：LOCATION
's3://[DestinationPrefix]/[SourceAccountId]/[SourceRegion]/'
LOCATIONでは対象のS3バケットのパスを指定します。
S3サーバアクセスログを保存しているS3バケット配下を指定します。

今回は後述する「TBLPROPERTIES」で配下のバケット名とプレフィックスをパーティション列として定義するため、例のような設定をします。

【変更例】
LOCATION
 's3://S3AccessLogs/123456789012/ap-northeast-1/'

or
 's3://S3AccessLogs/(Prefix)/123456789012/ap-northeast-1/'
リージョンやAWSアカウントIDで絞り込みたい場合、LOCATIONへの記述内容はさらにパスを削っていきます。
ただし、LOCATION記述を広げるとパーティション分割されていない場合に利用可能なファイルをすべてスキャンするようになります。
WHERE句指定し忘れた際にやらかす可能性があるため、出来るだけここは絞ることを意識したほうが良さそうです。

> Athena がパーティション分割されていないテーブルに対して SQL クエリを実行するときは、テーブル定義からの LOCATION プロパティをリスト化するための基本的なパスとして使用してから、利用可能なすべてのファイルをスキャンします。
参考：Amazon S3 のテーブルの場所
40行目〜：TBLPROPERTIES (
'projection.sourcebucketname.values'='[S3バケット A],[S3バケット B],[S3バケット C]',
'projection.sourcebucketname.type'='enum',
'storage.location.template'='s3://[DestinationPrefix]/[SourceAccountId]/[SourceRegion]/${sourcebucketname}/${timestamp}')
「projection.sourcebucketname.values」でパーティション射影対象のS3バケット名を記載します。
「projection.sourcebucketname.type」はenumを設定します。
「storage.location.template」にパーティション射影設定した変数を登録します。

enum 参考情報：パーティション射影用にサポートされている型
> 列 columnName に使用する射影型です。列挙型の使用を通知するには、値を enum (大文字と小文字を区別しない) にする必要があります。
>enum ベースのパーティション射影の使用は数 10 個以下に制限することが推奨されます。enum 射影に固有の制限はありませんが、テーブルのメタデータを gzip に圧縮するときには、合計サイズが約 1MB という AWS Glue の制限を超えることはできません。

【変更例】
TBLPROPERTIES (
'projection.enabled'='true',
'projection.timestamp.format'='yyyy/MM/dd',
'projection.timestamp.interval'='1',
'projection.timestamp.interval.unit'='DAYS',
'projection.timestamp.range'='2024/01/01,NOW',
'projection.timestamp.type'='date',
'projection.sourcebucketname.values'='A-app,B-app,C-app',
'projection.sourcebucketname.type'='enum',
'storage.location.template'='s3://S3AccessLogs/(Prefix)/123456789012/ap-northeast-1/${sourcebucketname}/${timestamp}')

以上の3項目の設定値を変更することで、1つのAthenaテーブル内でWHERE句を指定してクエリパフォーマンスを上げることが可能となります。

注意事項

列挙型のパーティション列では、要素を数十個以下にすることを推奨されています。
enumで設定するパーティション射影が多くなる（＝対象のS3バケットが増える）場合は懸念があるため設計を見直しましょう。

値が列挙セット (空港コードや AWS リージョン など) のメンバーであるパーティション列には、enum 型を使用します。

—中略—

テーブルでパーティションプロパティを次のように定義します。
ベストプラクティスとして、enum ベースのパーティション射影の使用は数 10 個以下に制限することが推奨されます。enum 射影に固有の制限はありませんが、テーブルのメタデータを gzip に圧縮するときには、合計サイズが約 1MB という AWS Glue の制限を超えることはできません。この制限は、列名、場所、およびストレージ形式といったテーブルの主要部分全体で共有されることに注意してください。enum 射影で使用している一意の ID が数十個ある場合は、サロゲートフィールドにバケット化する一意の値を少なくするなど別のアプローチを検討してください。カーディナリティをトレードオフすることで、enum フィールドの一意の値の数を制御できます。
引用：パーティション射影用にサポートされている型

Athena：設定方法（SourceBucket毎にテーブル作成パターン）

先ほどの設定はテーブルを1つ、クエリ実行によって絞り込みを実現した方法です。
こちらの設定内容はSourceBucket毎にテーブルを作成してクエリ実行を行うパターンとなります。

色々調べました結果「AWSの手順通りの対応で良かった」という結論です。

設計思想は諸々あると思いますが、わたしはS3バケットの第一階層で分ける方が分かりやすいと思い下記で分割しました。

S3://S3AccessLogs/A-app/(AWSアカウントID)/(リージョン)/A-app/(TimeStamp)/Logfiles
S3://S3AccessLogs/B-app/(AWSアカウントID)/(リージョン)/B-app/(TimeStamp)/Logfiles
S3://S3AccessLogs/C-app/(AWSアカウントID)/(リージョン)/C-app/(TimeStamp)/Logfiles

テーブル作成のクエリ

CREATE EXTERNAL TABLE `s3-accesss-logs-db`.`A-app-bucket_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,
`accesspointarn` STRING,
`aclrequired` STRING)
PARTITIONED BY (
`timestamp` string,`sourcebucketname` 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://S3AccessLogs/A-app/123456789012/ap-northeast-1/A-app/'
TBLPROPERTIES (
'projection.enabled'='true',
'projection.timestamp.format'='yyyy/MM/dd',
'projection.timestamp.interval'='1',
'projection.timestamp.interval.unit'='DAYS',
'projection.timestamp.range'='2024/01/01,NOW',
'projection.timestamp.type'='date',
'storage.location.template'='s3://S3AccessLogs/A-app/123456789012/ap-northeast-1/A-app/${timestamp}')
Athenaテーブル1つで運用するメリットもあり、クエリ毎に分ける方法を当初は目指してました。
しかし、別にAthenaテーブル分けて運用しても良いのでは？という結論に至り、最終的にはAWS手順通りで良かったという結果に落ち着きました。

道中にパーティション分割やパーティション射影といった初めて出会う言葉も多かったため、学びになりました。

参考情報

最新情報をチェックしよう！

AWSの最新記事