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インストラクター3年目でプロトタイプビルダーもやりだしたSoftware Engineerです。
質問はコメントかSNSなどからお気軽にどうぞ。
出来る限りなるべく答えます。

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

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

- AWS athena, AWS, cloudtrail

: Systems Manager パッチマネージャでベースラインを作成して適用する

ベースラインの作成 Systems Managerのパッチマネージャーでパッチベ …

: RDSリザーブドDBインスタンスを購入しました

リザーブドインスタンス推奨事項を確認したで確認した結果、購入したほうがよさそうで …

: Amazon Aurora Serverless 課金確認

Amazon Aurora Serverlessを使い始めてみましたの記事で書い …

: ブラウザからJavaScript SDKを使ってAmazon S3 への写真のアップロードチュートリアルをやってみました

ブラウザから Amazon S3 への写真のアップロードをやってみました。 HT …

: 試そうとしてたらSavings Plans買っちゃいました

Savings Plansの購入画面を確認していました。画面遷移も確認しようと …

: AWS AmplifyでTodoアプリを作るハンズオンをやってみました

【お手軽ハンズオンで AWS を学ぶ】AWS Amplify で Todo アプ …

: AWS Cost Explorerの設定で「EC2リソースの推奨事項を受け取る」を有効にしました

「EC2リソースの推奨事項を受け取る」という機能がAWS Cost Explor …

: AWSのサービス数を数えてみました(2020/5/23)

何をもってサービスという単位にするかというのはあるかもしれませんが、とりあえず情 …

: RDS + VPC + Lambda + API Gateway + CloudFront + WAF + ACMでAPIを構築する

RDSのMySQLの情報を与えられたリクエストをキーにしてjsonで返すAPIを …

: AlexaにAWSの最新Feedを読み上げてもらう(Lambda Python)

年末にAmazon Echo Dotを購入しましたので、練習がてらAlexaスキ …

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

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

CloudTrailからAthenaテーブル作成

DDLの編集

発生エラー

ad

ad

関連記事