代码之家 › 专栏 › 技术社区 › mohit

在aws athena中按datetime查询分析alb日志时出错

amazon-alb prestodb amazon-athena timestamp

1

mohit · 技术社区 · 6 年前

我已经按照链接中提到的步骤在Athena中创建了ALB表。我试图查询日志的基础上的日期时间,但我得到下面的错误。

查询

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" "-"

1 回复 | 直到 6 年前

1

3

mohit 6 年前

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;