Organizations対応のAWS CloudTrailのAthenaテーブルでPartition Projectionを使用しました

2022/05/17

パーティション向けのAlterテーブルの定期実行が面倒だと思っていたら、Partition Projectionという機能があることを知りました。
ALTER TABLEの実行なしで、パーティションに区切ってくれるそうです。

CloudTrailのAthenaテーブル作成機能とこちらの記事【全リージョン対応】CloudTrailのログをAthenaのPartition Projectionなテーブルで作るを参考にしました。

CloudTrailからAthenaテーブル作成

CloudTrailのイベント履歴画面の[Athenaテーブルを作成]ボタンを押下しました。

CREATE EXTERNAL TABLEのDDLが生成されます。
バケットを選択すると、LOCATIONが自動設定されますが、Organizations対応になっていないので、s3://bucketname/AWSLogs/organizaiotnsid のようにOrganizationsのパスに変えました。

DDLの編集

PARTITIONED BY とTBLPROPERTIESを追加して次のDDLになりました。
パーティションは、アカウントID、リージョン、日付にしました。

CREATE EXTERNAL TABLE cloudtrail_events_db.cloudtrail_logs (
    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>>>,
    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
)
COMMENT 'CloudTrail table for cloudtrail bucket'
PARTITIONED BY ( 
  `account` string, 
  `region` string, 
  `date` string
  )
ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://cloudtrail-bucket/AWSLogs/o-abcdefghijk/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.date.type' = 'date',
    'projection.date.range' = 'NOW-1YEARS,NOW',
    'projection.date.format' = 'yyyy/MM/dd',
    'projection.date.interval' = '1',
    'projection.date.interval.unit' = 'DAYS',
    'projection.region.type' = 'enum',
    'projection.region.values'='ap-northeast-1,ap-northeast-2,ap-northeast-3,ap-south-1,ap-southeast-1,ap-southeast-2,ca-central-1,eu-central-1,eu-north-1,eu-west-1,eu-west-2,eu-west-3,sa-east-1,us-east-1,us-east-2,us-west-1,us-west-2',
    'projection.account.type' = 'enum',
    'projection.account.values' = '111111111111,222222222222,333333333333,444444444444,
    'storage.location.template' = 's3://cloudtrail-bucket/AWSLogs/o-abcdefghijk/${account}/CloudTrail/{region}/${date}',
'compressionType'='gzip',
'typeOfData'='file',
'classification'='cloudtrail'
);

CREATE EXTERNAL TABLE cloudtrail_events_db.cloudtrail_logs (

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>>>,

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

)

COMMENT 'CloudTrail table for cloudtrail bucket'

PARTITIONED BY (

`account` string,

`region` string,

`date` string

)

ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'

STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION 's3://cloudtrail-bucket/AWSLogs/o-abcdefghijk/'

TBLPROPERTIES (

'projection.enabled' = 'true',

'projection.date.type' = 'date',

'projection.date.range' = 'NOW-1YEARS,NOW',

'projection.date.format' = 'yyyy/MM/dd',

'projection.date.interval' = '1',

'projection.date.interval.unit' = 'DAYS',

'projection.region.type' = 'enum',

'projection.region.values'='ap-northeast-1,ap-northeast-2,ap-northeast-3,ap-south-1,ap-southeast-1,ap-southeast-2,ca-central-1,eu-central-1,eu-north-1,eu-west-1,eu-west-2,eu-west-3,sa-east-1,us-east-1,us-east-2,us-west-1,us-west-2',

'projection.account.type' = 'enum',

'projection.account.values' = '111111111111,222222222222,333333333333,444444444444,

'storage.location.template' = 's3://cloudtrail-bucket/AWSLogs/o-abcdefghijk/${account}/CloudTrail/{region}/${date}',

'compressionType'='gzip',

'typeOfData'='file',

'classification'='cloudtrail'

);

SELECT * FROM "cloudtrail_logs"
where account='111111111111' 
and region='ap-northeast-1' 
and date='2022/05/16';

SELECT * FROM "cloudtrail_logs"

where account='111111111111'

and region='ap-northeast-1'

and date='2022/05/16';

テーブル作成後、パーティションを検索条件にしてクエリを実行しました。
スキャン対象のデータが絞られていました。

発生エラー

mismatched input ‘EXTERNAL’. Expecting: ‘OR’, ‘SCHEMA’, ‘TABLE’, ‘VIEW’

PARTITIONED BY行をCOMMENT行の前で実行しようとしていて上記エラーが発生しました。

最後までお読みいただきましてありがとうございました！

【PR】「AWS認定試験対策 AWS クラウドプラクティショナー」という本を書きました。

【PR】「AWSではじめるLinux入門ガイド」という本を書きました。

【PR】「ポケットスタディ AWS認定デベロッパーアソシエイト」という本を書きました。

【PR】「AWS認定資格試験テキスト＆問題集　AWS認定ソリューションアーキテクト - プロフェッショナル」という本を書きました。

@yamamanx

開発ベンダー5年、ユーザ企業システム部門通算9年、ITインストラクター5年目でプロトタイプビルダーもやりだしたSoftware Engineerです。
質問はコメントかSNSなどからお気軽にどうぞ。
出来る限りなるべく答えます。

このブログの内容/発言の一切は個人の見解であり、所属する組織とは関係ありません。
このブログは経験したことなどの共有を目的としており、手順や結果などを保証するものではありません。
ご参考にされる際は、読者様自身のご判断にてご対応をお願いいたします。

また、勉強会やイベントのレポートは自分が気になったことをメモしたり、聞いて思ったことを書いていますので、登壇者の意見や発表内容ではありません。

- AWS athena, AWS, cloudtrail

: 「雲勉第1回【勉強会：新技術好き!】AWSマネージドサービス勉強会」に行ってきました

「雲勉第1回【勉強会：新技術好き!】AWSマネージドサービス勉強会」に行ってき …

: Amazon CloudSearchにAWS Lambda(Python)からデータをアップロードする

このブログはゆるっとアドベントカレンダー Advent Calendar 201 …

: AWS Transit GatewayのVPN接続

上記のような構成で、オンプレミス側は東京リージョンのVPCでVyOSを起動して接 …

: AWS CLIを使用したIAMロールの引き受けコマンドのメモ

よく忘れて調べるのでメモです。公式のこちらAWS CLI を使用して IAM …

: iPad ProのWorking CopyでAWS CodeCommitのリポジトリを使う

iPad Proを導入しましたので、原稿執筆や校正でフル活用しようと思いまして。 …

: AWS CDKでリージョンをまたいだクロススタックリファレンスはできなかった

例えばこんなコードが実行できるかというと、 [crayon-6424e5b456 …

: Amazon Data Lifecycle Manager(DLM)が東京リージョンで使えるようになったのでLambdaでAMI自動取得から乗り換えた

EBSのスナップショットを自動で作成してくれるAmazon Data Lifec …

: AWS EC2 インスタンスステータスのチェックで失敗して起動しなくなり復旧

EC2のインスタンスに接続出来なくなったので、AMIから作成してElastic …

: NATインスタンスを作成する

プライベートサブネットのEC2インスタンスからカスタムメトリクスとCloudWa …

: Feedlyのフィードを自動でSlackへ投稿する(AWS Lambda , Amazon DynamoDB)

やりたいこと Feedlyで共有したいフィードに特定のタグを付けます。特定のタ …

PREV: EC2スポットインスタンスの中断通知を受ける
NEXT: CloudTrailのログファイルの整合性検証をAWS CLIで実行しました

Organizations対応のAWS CloudTrailのAthenaテーブルでPartition Projectionを使用しました

CloudTrailからAthenaテーブル作成

DDLの編集

発生エラー

ad

ad

関連記事