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

: AWS Transfer Family S3向けのSFTP対応サーバー

S3バケットは作成済です。 IAMロールの作成 [crayon-6a5a5482 …

: AWS CLIからIAM Identity CenterへサインインしてCodeCommitのリポジトリを使用する

Macで操作しました。 AWS CLIバージョンアップ [crayon-6a5a …

: Rocket.ChatのOutGoingWebhookからのAPI GatewayからのDynamoDB

少し前に、Rocket.ChatからOut Going Webhookを設定して …

: 「JAWS-UG in AWS Cloud Roadshow 2017 大阪」で運営をしました

AWS Cloud Roadshow 2017 大阪のナイトイベントで、「JAW …

: AWS CloudWatch LogsエージェントでAmazon EC2上のNginxのaccess.log , error.log , php-fpm error.log , Linuxのmessages , secureログを収集する

参考ページクイックスタート: 実行中の EC2 インスタンスに CloudWa …

: Cloud9でAWS CDK環境

最初ローカルに作ろうと思ったのですが、環境依存がというか、Macbookの環境で …

: 新規アカウントでAWS Budgetsの設定をしました

新規で組織を作ってAWS Budgetsを久しぶりに設定しました。作成してすぐ …

: S3イベントのAWS Lambdaのテスト設定

S3イベントのLambda関数でよく使うのはこんなテスト設定です。なので覚書で …

: EC2 Linux ユーザーデータ実行ユーザーとカレントディレクトリの確認

きっと、rootなんだろうなあと認識してたのですが、自分の目で確認したことはなか …

: RDSスナップショットのS3エクスポート結果確認

RDSのスナップショットをS3へエクスポートが日本語マネジメントコンソールでもで …

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

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

CloudTrailからAthenaテーブル作成

DDLの編集

発生エラー

関連記事