在AWS Athena中按日期时间查询解析ALB日志的错误 [英] Error on query parsing alb logs by datetime in aws athena

问题描述

我已按照链接中提到的步骤在雅典娜中创建ALB表。我试图根据日期时间查询日志，但出现错误。

I have followed the steps mentioned in the link to create the ALB table in Athena. I am trying to query the logs on the basis of datetime but I am getting below error.

Query

SELECT client_ip, sum(received_bytes)
FROM default.alb_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSS''Z')
  BETWEEN 
    parse_datetime('2018-08-27-12:00:00','yyyy-MM-dd-HH:mm:ss')
    AND
    parse_datetime('2018-08-28-12:00:00','yyyy-MM-dd-HH:mm:ss')
GROUP BY client_ip

错误：

Your query has the following error(s):

INVALID_FUNCTION_ARGUMENT: Invalid format: "2018-09-02T23:55:01.238332Z" is malformed at "32Z"

This query ran against the "default" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 1889af6f-a24a-41ef-82e9-c831901bbdb9.

日志格式：

https 2018-08-27T23:55:10.036929Z app/production-webapp/9933369922d15f48 104.130.170.6:47816 172.16.14.220:32772 0.007 0.035 0.000 400 400 511 757 "POST https://www.hostname.com:443/api/1.1/example/ HTTP/1.1" "-" ECDHE-RSA-AES128-GCM-SHA256 TLSv1.2 arn:aws:elasticloadbalancing:us-east-1:686140181923:targetgroup/production-webapp-alb-tg/858d5a2066999d75 "Root=1-5b848f5d-a88af308f712a2a8eb67d408" "www.hostname.com" "session-reused" 3 2018-08-27T23:55:09.994000Z "waf,forward" "-"

推荐答案

下面的查询到目前为止对我有用。

Below query worked for me as of now.

SELECT client_ip, count(*) as count FROM "default"."alb_logs" where time BETWEEN '2018-08-28T00:00:%' AND '2018-08-28T23:59:%' group by client_ip order by count DESC;

这篇关于在AWS Athena中按日期时间查询解析ALB日志的错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！