Error on query parsing alb logs by datetime in aws athena

Question

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

Error:

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.

Logs Format:

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

mohit · Accepted Answer · 2018-09-03 10:51:52Z

4

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;

answered Sep 3, 2018 at 10:51

mohit

2,47933 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Piotr Findeisen Over a year ago

Operating on text directly will likely be more efficient. Note: you don't need % in your BETWEEN's values.

Collectives™ on Stack Overflow

Error on query parsing alb logs by datetime in aws athena

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related