如何僅從 Amazon Athena 獲取過去一周的記錄

我正在編寫查詢以獲取僅過去一周的 Amazon Athena 記錄。這是我到目前為止寫的：

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';

但我不知道如何編寫它來提取過去 1 週的記錄。

@Philᵀᴹ 的答案幾乎就在那裡。我只是在我的查詢中使用它並找到了修復。我會發表評論，但沒有足夠的分數，所以這就是答案。

您必須使用current_timestamp然後將其轉換為 iso8601 格式。像這樣：

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'
and eventTime > to_iso8601(current_timestamp - interval '7' day);

您可以通過執行如下測試查詢來測試您實際需要的格式：

SELECT to_iso8601(current_date - interval '7' day);

返回：‘2018-06-05’

SELECT to_iso8602(current_timestamp - interval '7' day);

返回：‘2018-06-05T19:25:21.331Z’，與 event.eventTime 的格式相同，並且有效。

Amazon Athena 使用Presto，因此您可以使用Presto 提供的任何日期函式。你會想要使用current_date - interval '7' day, 或類似的。

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'
and eventTime > current_date - interval '7' day;

未經測試，我無權訪問數據庫進行測試。

引用自：https://dba.stackexchange.com/questions/171614