AWS Athena分区投影支持多存储模板吗？单表查新旧CloudTrail日志方案

AWS Control Tower 管理的 CloudTrail 日志在 Landing Zone 3.0 更新前后路径发生变化：

• 更新前（account-trail-logs）：S3 路径为 /<org-id>/AWSLogs/<account-id>/CloudTrail/<region>/<date>/
• 更新后（organization-trail logs）：S3 路径变为 /<org-id>/AWSLogs/<org-id>/<account-id>/CloudTrail/<region>/<date>/

原 Athena 分区投影 DDL 语句如下：

CREATE EXTERNAL TABLE cloudtrail_logs_partition_projected( 
    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, 
    readOnly STRING, 
    resources ARRAY<STRUCT< arn: STRING, accountId: STRING, type: STRING>>,
    eventType STRING, 
    apiVersion STRING, 
    recipientAccountId STRING, 
    serviceEventDetails STRING, 
    sharedEventID STRING, 
    vpcEndpointId STRING )
PARTITIONED BY ( `accountid` string, `region` string, `date_created` 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://<s3-bucket>/<org-id>/AWSLogs/'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.accountid.type'='injected',
  'projection.region.type'='enum', 
  'projection.region.values'='eu-north-1,ap-south-1,eu-west-3,eu-west-2,eu-west-1,ap-northeast-3,ap-northeast-2,ap-northeast-1,sa-east-1,ca-central-1,ap-southeast-1,ap-southeast-2,eu-central-1,us-east-1,us-east-2,us-west-1,us-west-2', 
  'projection.date_created.format'='yyyy/MM/dd', 
  'projection.date_created.interval'='1', 
  'projection.date_created.interval.unit'='DAYS', 
  'projection.date_created.range'='2021/01/01,NOW', 
  'projection.date_created.type'='date', 
  'storage.location.template'='s3://<s3-bucket-name>/<org-id>/AWSLogs/${accountid}/CloudTrail/${region}/${date_created}')

现咨询两个问题：

1. AWS Athena 分区投影是否支持多个 storage.location.template？
2. 如何用单个 Athena 表查询新旧两种日志？

1. 关于多 storage.location.template 的支持 #

Athena 分区投影不支持直接配置多个 storage.location.template，每个表仅能指定一个存储位置模板。

2. 单个表查询新旧日志的方案 #

方案一：扩展分区字段兼容双路径 #

通过新增可选分区字段，让storage.location.template同时匹配新旧路径：

1. 修改表结构，新增optional_org_segment分区字段，并为新旧路径分别添加分区：

-- 为旧日志路径添加分区
ALTER TABLE cloudtrail_logs_partition_projected ADD PARTITION (optional_org_segment='', accountid='<account-id>', region='<region>', date_created='<date>')
LOCATION 's3://<s3-bucket>/<org-id>/AWSLogs/<account-id>/CloudTrail/<region>/<date>/';

-- 为新日志路径添加分区
ALTER TABLE cloudtrail_logs_partition_projected ADD PARTITION (optional_org_segment='<org-id>', accountid='<account-id>', region='<region>', date_created='<date>')
LOCATION 's3://<s3-bucket>/<org-id>/AWSLogs/<org-id>/<account-id>/CloudTrail/<region>/<date>/';

2. 更新表的分区投影配置，修改storage.location.template并添加新字段的投影规则：

ALTER TABLE cloudtrail_logs_partition_projected SET TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.optional_org_segment.type'='injected',
  'projection.accountid.type'='injected',
  'projection.region.type'='enum', 
  'projection.region.values'='eu-north-1,ap-south-1,eu-west-3,eu-west-2,eu-west-1,ap-northeast-3,ap-northeast-2,ap-northeast-1,sa-east-1,ca-central-1,ap-southeast-1,ap-southeast-2,eu-central-1,us-east-1,us-east-2,us-west-1,us-west-2', 
  'projection.date_created.format'='yyyy/MM/dd', 
  'projection.date_created.interval'='1', 
  'projection.date_created.interval.unit'='DAYS', 
  'projection.date_created.range'='2021/01/01,NOW', 
  'projection.date_created.type'='date', 
  'storage.location.template'='s3://<s3-bucket>/<org-id>/AWSLogs/${optional_org_segment}/${accountid}/CloudTrail/${region}/${date_created}'
);

利用injected类型的分区字段，允许手动指定optional_org_segment为空（匹配旧路径）或org-id（匹配新路径），实现单个表兼容两种日志路径。

方案二：创建视图合并两个独立表 #

如果不想修改原表结构，可分别为新旧日志创建分区投影表，再通过视图合并数据：

1. 保留原表cloudtrail_logs_partition_projected用于读取旧日志，为新日志创建结构一致的新表：

CREATE EXTERNAL TABLE cloudtrail_logs_new( 
    -- 与原表结构完全一致
    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, 
    readOnly STRING, 
    resources ARRAY<STRUCT< arn: STRING, accountId: STRING, type: STRING>>,
    eventType STRING, 
    apiVersion STRING, 
    recipientAccountId STRING, 
    serviceEventDetails STRING, 
    sharedEventID STRING, 
    vpcEndpointId STRING )
PARTITIONED BY ( `accountid` string, `region` string, `date_created` 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://<s3-bucket>/<org-id>/AWSLogs/<org-id>/'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.accountid.type'='injected',
  'projection.region.type'='enum', 
  'projection.region.values'='eu-north-1,ap-south-1,eu-west-3,eu-west-2,eu-west-1,ap-northeast-3,ap-northeast-2,ap-northeast-1,sa-east-1,ca-central-1,ap-southeast-1,ap-southeast-2,eu-central-1,us-east-1,us-east-2,us-west-1,us-west-2', 
  'projection.date_created.format'='yyyy/MM/dd', 
  'projection.date_created.interval'='1', 
  'projection.date_created.interval.unit'='DAYS', 
  'projection.date_created.range'='2021/01/01,NOW', 
  'projection.date_created.type'='date', 
  'storage.location.template'='s3://<s3-bucket-name>/<org-id>/AWSLogs/<org-id>/${accountid}/CloudTrail/${region}/${date_created}')

2. 创建视图合并两个表的数据：

CREATE VIEW cloudtrail_logs_combined AS
SELECT * FROM cloudtrail_logs_partition_projected
UNION ALL
SELECT * FROM cloudtrail_logs_new;

后续直接查询cloudtrail_logs_combined即可获取新旧所有日志。

内容的提问来源于stack exchange，提问作者Pal Ramasamy