amazon-web-services - 如何从雅典娜获得过去一周的结果？

Question

我正在使用 Amazon Athena 获取过去一周发生的所有控制台登录信息，现在无论数据如何，我都可以获取所有控制台登录信息。我需要修改以下查询，以便此查询始终获取上周发生的所有 aws 控制台登录。

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,  
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin';

eventTime 看起来像“:


吨

Answer 1

如果该列以文本形式出现，您可以将其转换为时间戳。我发现 Amazon Athena 可以转换 '2016-05-03 05:46:00'成时间戳，所以使用 replace()将其转换为正确格式的函数:

select cast(replace(replace('2016-05-03T05:46:00Z', 'Z'), 'T', ' ') as timestamp)

因此，在您的 WITH部分，替换 event.eventType和:

cast(replace(replace(event.eventType, 'Z'), 'T', ' ') as timestamp) AS eventType,

然后您可以使用标准 WHERE针对日期的陈述，例如:

WHERE eventType > '2017-04-01'

或者过去一周(基于 Presto documentation ):

WHERE eventType > current_date - interval '7' day