Complete a 3 Question Survey and Earn a re:Post Badge

Help improve AWS Support Official channel in re:Post and share your experience - complete a quick three-question survey to earn a re:Post badge!

如何使用分区投影为 Athena 查询创建 CloudTrail 表？

3 分钟阅读

当我使用 Amazon Athena 查询我的 AWS CloudTrail 数据时，查询需要很长时间才能运行完毕，或者会超时。

解决方法

即使您对 CloudTrail 表进行分区以减少查询的运行时间，CloudTrail 日志的大小也会随着时间的推移而增加。针对高度分区表的查询会有较长的计划时间，无法快速完成。

要解决超时问题，请使用分区投影手动创建 CloudTrail 表。这使 Athena 能够动态计算 CloudTrail 表的值，从而减少查询运行时间。使用分区投影，您无需管理分区，因为分区值和位置是根据配置计算得出的。

要使用分区投影创建按时间戳分区的 CloudTrail 表，请参阅使用分区投影在 Athena 中为 CloudTrail 日志创建表。

要使用分区投影从多个账户创建按年、月和日分区的 CloudTrail 表，请使用类似于以下内容的命令：

CREATE EXTERNAL TABLE ctrail_pp_ymd (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
)
PARTITIONED BY (account string, region string, year string, month string, day 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://doc_example_bucket/AWSLogs/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.day.type'='integer',
  'projection.day.range'='01,31',
  'projection.day.digits'='2',
  'projection.month.type'='integer',
  'projection.month.range'='01,12',
  'projection.month.digits'='2',
  'projection.region.type'='enum',
  'projection.region.values'='us-east-1,us-east-2,us-west-2',
  'projection.year.type'='integer',
  'projection.year.range'='2015,2021',
  'projection.account.type'='enum',
  'projection.account.values'='111122223334444,5555666677778888',  
  'storage.location.template'='s3://doc_example_bucket/AWSLogs/${account}/CloudTrail/${region}/${year}/${month}/${day}'
)

务必替换前面命令中的以下值：

将 ctrail_pp_ymd 替换为 CloudTrail 表名称。
将 doc_example_bucket 替换为您要在其中创建 CloudTrail 表的 Amazon Simple Storage Service（Amazon S3）存储桶的名称。
将 1111222233334444 和 5555666677778888 替换为您想要为其创建 CloudTrail 表的账户的账户 ID。
将 us-east-1、us-east-2、us-west-2 替换为您要为其创建 CloudTrail 表的 AWS 区域。
基于您的用例修改表属性。
投影范围取决于您的用例。例如，如果您的 CloudTrail 数据仅从 2018 年开始可用，则将分区列年份的投影范围替换为 '2018,2021'。

要为同一组织下的多个账户创建 CloudTrail 表，请使用类似于以下内容的命令：

CREATE EXTERNAL TABLE ctrail_pp_ymd_org (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
)
PARTITIONED BY (account string, region string, year string, month string, day 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://doc_example_bucket/AWSLogs/doc_example_orgID/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.day.type'='integer',
  'projection.day.range'='01,31',
  'projection.day.digits'='2',
  'projection.month.type'='integer',
  'projection.month.range'='01,12',
  'projection.month.digits'='2',
  'projection.region.type'='enum',
  'projection.region.values'='us-east-1,us-east-2,us-west-2',
  'projection.year.type'='integer',
  'projection.year.range'='2010,2100',
  'projection.account.type'='enum',
  'projection.account.values'='111122223334444,5555666677778888',  
  'storage.location.template'='s3://doc_example_bucket/AWSLogs/doc_example_orgID/${account}/CloudTrail/${region}/${year}/${month}/${day}'
)

**注意：**如果您需要查询 2010 年之前的 CloudTrail 数据，请务必在 projection.year.range 属性中更新年份范围。

务必替换前面命令中的以下值：

将 ctrail_pp_ymd_org 替换为 CloudTrail 表名称。
将 doc_example_bucket 替换为您要在其中创建 CloudTrail 表的 Amazon S3 存储桶的名称。
将 doc_example_orgID 替换为您的组织 ID。
将 1111222233334444 和 5555666677778888 替换为您想要为其创建 CloudTrail 表的账户的账户 ID
将 us-east-1、us-east-2 和 us-west-2 替换为您要为其创建 CloudTrail 表的区域。
基于您的用例修改表属性。
投影范围取决于您的用例。例如，如果您的 CloudTrail 数据仅从 2018 年开始可用，则将分区列年份的投影范围替换为 '2018,2021'。

运行查询时，请务必在查询中包含分区列限制条件。这使得 Athena 可以扫描更少的数据并缩短查询处理时间。

例如，您可以运行类似于以下内容的命令来找出哪个用户向 S3 存储桶发出了 GetObject 请求。此查询中的表使用年、月和日的分区格式。

**注意：**请务必在 CloudTrail 中激活 Amazon S3 的事件日志。

SELECT useridentity.arn, eventtime  FROM "ctrail_pp_ymd"where eventname = 'GetObject'
and year = '2021'
and month = '05'
and region = 'us-east-1'
and cast(json_extract(requestparameters, '$.bucketName')as varchar) ='doc_example_bucket'

务必替换前面命令中的以下值：

将 ctrail_pp_ymd 替换为 CloudTrail 表名称。
将 doc_example_bucket 替换为您要在其中创建 CloudTrail 表的 S3 存储桶的名称。
基于您的用例的限制条件。

如果您遇到超时问题，请参阅我如何解决使用 Athena 查询 CloudTrail 数据时出现的超时问题？

有关更多信息，请参阅查询 AWS CloudTrail 日志和如何在 Athena 中创建和使用分区表？

如何使用分区投影为 Athena 查询创建 CloudTrail 表？

解决方法

相关信息

相关内容