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行の前で実行しようとしていて上記エラーが発生しました。

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

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

「AWS認定資格試験テキスト AWS認定クラウドプラクティショナー改訂第3版」という本を書きました。

「AWS認定資格試験テキスト　AWS認定AIプラクティショナー」という本を書きました。

「ポケットスタディ AWS認定デベロッパーアソシエイト［DVA-C02対応］」という本を書きました。

「要点整理から攻略するAWS認定ソリューションアーキテクト-アソシエイト」という本を書きました。

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

@yamamanx

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

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

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

- AWS athena, AWS, cloudtrail

: Amazon SNSサブスクリプションフィルターで優先度別のSQSキューにサブスクライブする

EC2のコンシューマーアプリケーションは優先度の高いキューのメッセージを先に処理 …

: AWS Summit 2016 Tokyoに参加してきました (Day3)

飛天3日目です。 JAWS-UGブースのすぐ前にあったこのお水がめちゃめちゃおい …

: AWS Managed Microsoft ADを構築してユーザー追加まで

事前準備 DNSホスト名と名前解決を有効にしたVPCを作成して、2つのAZにパブ …

: Amazon Connectで電話を転送する

かかってきた電話を転送するようにしました。問い合わせフローで[終了/転送]から …

: AWS Application Discovery Serviceのエージェントを実行

本来はオンプレミスのサーバーにエージェントをインストールして、自動でアプリケーシ …

: AWS App RunnerでGithubリポジトリからデプロイ

AWS App Runner開発者ガイドのチュートリアルをやってみました。 Gi …

: AWS Summit Tokyo 2017 Day3 開場~基調講演~Dev Day Night

AWS Summit Tokyo 2017 参加2日目のDay3です。基調講演 …

: ヤマムギvol.23 Amazon RDS for MySQLとAmazon Aurora Serverlessの起動のデモをしました

ゴールデンウィーク毎朝デモ勉強会は終わったのですが、土曜日にやれるときがあれば、 …

: AWSセルフマネージドAD環境にEC2 Windowsサーバーをドメイン参加する

AWSクイックスタートのActive Directory Domain Serv …

: EC2 Auto ScalingカスタムメトリクスのモニタリングにCloudWatch検索式が便利でした

EC2 Auto Scalingで起動したインスタンスのカスタムメトリクスこの …

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

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

CloudTrailからAthenaテーブル作成

DDLの編集

発生エラー

関連記事