Zenn
🐡

Amazon Athena パーティショニングプロジェクションと名前付きクエリをやってみた

2024/08/29に公開
AWS
Athena
Glue
tech

はじめに

本ページは個人の勉強で使用することを目的に作成しておりますが、記載の誤り等が含まれる場合がございます。
最新の情報についてはAWS公式ドキュメントをご参照ください。

やること

  • S3に保存したCloudTrail管理イベントに対してAmazon Athenaでデータのクエリをする。
    • イベント名ConsoleLoginに一致するイベントを探索する。
  • パーティショニングプロジェクションを行い、クエリの実行時間とスキャンするデータ量を低減する。
    • クエリの一部を名前付きクエリとして実行する。

前提条件

1. Athenaワークグループの作成

  1. Athenaコンソールからワークグループをクリックします。


  1. ワークグループを作成します。
  • ワークグループの詳細
    • ワークグループ名：mori-athena-workgroup
    • 説明 - オプション：My WorkGroup
  • 分析エンジン - 新規
    • エンジンのタイプを選択：Athena SQL
    • クエリエンジンをアップグレード：自動
  • 認証：IAM
  • クエリ結果の設定 - オプション
    • クエリ結果の場所 - オプション：s3://mori-s3-cloudtrail-management-event/athena-partition/
    • 予期されるバケット所有者 - オプション：未選択
    • バケット所有者にクエリ結果に対する完全なコントロールを割り当てる：選択
    • クエリ結果を暗号化：未選択
  • 設定
    • AWS CloudWatch にクエリメトリクスを発行：選択
    • クライアント側の設定を上書き：未選択
    • Amazon S3 のリクエスタ支払いバケットでクエリをオンにする：未選択
  • クエリごとのデータ使用状況の制御 - オプション
    • データ制限：1TB

2. データベース・テーブルを定義

  1. Athenaのクエリエディタで、ワークグループを先程作成したmori-athena-workgroupに切り替えます。
  2. データベースmori_athena_db_consoleloginを作成します。
CREATE DATABASE mori_athena_db_consolelogin


  1. データベースmori_athena_db_consoleloginに切り替えます。


  1. テーブルを定義します。
CREATE EXTERNAL TABLE mori_athena_table_consolelogin (
    eventVersion STRING,
    userIdentity STRUCT<
        type: STRING,
        principalId: STRING,
        arn: STRING,
        accountId: STRING,
        invokedBy: STRING,
        accessKeyId: STRING,
        userName: STRING,
        sessionContext: STRUCT<
            attributes: STRUCT<
                mfaAuthenticated: STRING,
                creationDate: STRING>,
            sessionIssuer: STRUCT<
                type: STRING,
                principalId: STRING,
                arn: STRING,
                accountId: STRING,
                username: STRING>,
            ec2RoleDelivery: STRING,
            webIdFederationData: MAP<STRING,STRING>>>,
    eventTime STRING,
    eventSource STRING,
    eventName STRING,
    awsRegion STRING,
    sourceIpAddress STRING,
    userAgent STRING,
    errorCode STRING,
    errorMessage STRING,
    requestParameters STRING,
    responseElements STRING,
    additionalEventData STRING,
    requestId STRING,
    eventId STRING,
    resources ARRAY<STRUCT<
        arn: STRING,
        accountId: STRING,
        type: STRING>>,
    eventType STRING,
    apiVersion STRING,
    readOnly STRING,
    recipientAccountId STRING,
    serviceEventDetails STRING,
    sharedEventID STRING,
    vpcEndpointId STRING,
    tlsDetails STRUCT<
        tlsVersion: STRING,
        cipherSuite: STRING,
        clientProvidedHostHeader: STRING>
)
COMMENT 'CloudTrail table for mori-s3-cloudtrail-management-event bucket'
PARTITIONED BY (
    `region` string,
    `timestamp` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://★CloudTrailイベントを保存しているS3バケット名/AWSLogs/★アカウントID/CloudTrail/'
TBLPROPERTIES (
    'classification'='cloudtrail',
    'projection.enabled' = 'true',
    'projection.timestamp.type' = 'date',
    'projection.timestamp.range' = '2024/03/16,NOW',
    'projection.timestamp.format' = 'yyyy/MM/dd',
    'projection.timestamp.interval' = '1',
    'projection.timestamp.interval.unit' = 'DAYS',
    'projection.region.type' = 'enum',
    'projection.region.values'='ap-south-1,eu-north-1,eu-west-3,eu-west-2,eu-west-1,ap-northeast-3,ap-northeast-2,ap-northeast-1,ca-central-1,sa-east-1,ap-southeast-1,ap-southeast-2,eu-central-1,us-east-1,us-east-2,us-west-1,us-west-2',
    'storage.location.template' = 's3://★CloudTrailイベントを保存しているS3バケット名/AWSLogs/★アカウントID/CloudTrail/${region}/${timestamp}'
);

PARTITIONED BYについて

  • パーティションキーを定義。
    • region：CloudTrailログが記録されたリージョンを文字列型で定義
    • timestamp：CloudTrailログが記録されが年月日を文字列型で定義。

TBLPROPERTIESについて

  • 'projection.enabled' = 'true',
    • プロジェクションをパーティションを有効化。
  • 'projection.timestamp.type' = 'date',
    • パーティションtimestampを日付型で定義。
  • 'projection.timestamp.format' = 'yyyy/MM/dd',
    • パーティションtimestampのフォーマットをyyyy/MM/ddで定義。※S3のフォルダ構造に従う。
  • 'projection.timestamp.range' = '2024/03/16,NOW',
    • クエリ可能なパーティションの幅を限定。
    • この場合、S3バケット作成日である2024/03/16から現在までを指定。
  • 'projection.timestamp.interval' = '1',
    • パーティションを1日単位で区切っているため1を指定。
  • 'projection.timestamp.interval.unit' = 'DAYS',
    • パーティションが1日単位で区切っているためDAYSを指定。
  • 'projection.region.type' = 'enum',
    • パーティションregionを列挙型で定義。
  • 'projection.region.values'='us-east-1,us-east-2,us-west-1,us-west-2…
    • アカウントで有効化されたリージョンを指定


  1. mori-query-1で名前を付けて保存し、実行をクリックします。
    ※これを 名前付きクエリ(Named Query) と言います。

3. ConsoleLoginイベントを検索

  1. 新しいクエリを追加して、クエリを作成します。
SELECT *
FROM mori_athena_table_consolelogin
WHERE 
    eventname = 'ConsoleLogin' 
AND
    timestamp between '2024/03/18' and '2024/03/30'
;

※作成したテーブルmori_athena_table_consoleloginをもとに2024/03/18~2024/03/30期間のConsoleLoginイベントを取得。


  1. mori-query-2で名前を付けて保存し、実行をクリックします。
  • スキャンしたデータ：5.40MB


  1. 手順1のワークグループで指定したS3バケットの/athena/配下にmori-query-1, mori-query-2, Unsavedというフォルダが作成されています。
  • クエリに名前を付けて保存(名前付きクエリ)した場合、クエリ名がプレフィックスとして追加されます。
  • クエリに名前を付けて保存していない場合、単にUnsavedというプレフィックスとなります。


  1. mori-query-2/yyyy/mm/dd/配下に、.csvファイルと.metadataファイルが作成されています。


  1. csvファイルのに2024/03/18~2024/03/30期間のConsoleLoginイベントが出力されています。


おわりに

  • AWSアカウントで有効化されたリージョンは下記AWS CLIコマンドで確認できる。
$ aws ec2 describe-regions \
    --filters "Name=opt-in-status,Values=opt*" \
    --query "Regions[*].RegionName" | tr -d "\n" | tr -d "[:space:]" | tr -d \"
[ap-south-1,eu-north-1,eu-west-3,eu-west-2,eu-west-1,ap-northeast-3,ap-northeast-2,ap-northeast-1,ca-central-1,sa-east-1,ap-southeast-1,ap-southeast-2,eu-central-1,us-east-1,us-east-2,us-west-1,us-west-2]


  • Athenaの実行権限に必要なアクションはクエリを実行するユーザーに対して許可する必要がある。

https://repost.aws/ja/knowledge-center/access-denied-athena


  • CloudTrailイベント保存先S3バケットが東京リージョンにある場合、yyyy/mm/ddフォルダは一見JSTに基づいているように見えるが、実際はUTCに基いていることに注意が必要。

※例えば、2024/05/01フォルダには日本時間で05/01 09:00 ~ 05/02 08:59のイベントが保存されているので、クエリを投げる時には注意が必要である。またAthenaのクエリエディタもUTCに基づいている。

参考

  • データベース・テーブル定義

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/cloudtrail-logs.html#create-cloudtrail-table

  • パーティショニングプロジェクション

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/partition-projection.html

  • 名前付きクエリ

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/saved-queries.html

  • Athena料金

https://aws.amazon.com/jp/athena/pricing/

Discussion