Organizations形式のCloudTrailログをAthenaから参照

CloudTrailログをAthenaで参照

下記ドキュメントに、CloudTrailのログをAthenaで参照するためのCREATE文が存在しますが、こちらは1アカウントのみの形式です。Organizations形式のログについて、記載はありませんでした。

Organizations形式のログテーブル

下記CREATE文を実行することで、Organizations形式のCloudTrailログをAthenaから参照することができたため、ご活用ください。
パーティションも考慮してます。

CREATE EXTERNAL TABLE log_database.cloudtrail_log_table (
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: STRUCT<
               federatedProvider: STRING,
               attributes: 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,
eventCategory STRING,
tlsDetails struct<
  tlsVersion:string,
  cipherSuite:string,
  clientProvidedHostHeader:string>
)
PARTITIONED BY (
   `account` string,
   `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://【バケット名】/AWSLogs/【organizations id】/'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.account.values'='【アカウントid, アカウントid, アカウントid, アカウントid】', 
  'projection.account.type'='enum',
  'projection.region.values'='ap-east-1,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.region.type'='enum',
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2023/04/01,NOW', 
  'projection.timestamp.type'='date', 
  'storage.location.template'='s3://【バケット名】/AWSLogs/【organizations id】/${account}/CloudTrail/${region}/${timestamp}')