Athena queries

athenaqueries.md

ALB

SELECT *
FROM alb_logs_UAT
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
     BETWEEN parse_datetime('2020-07-21-05:20:00','yyyy-MM-dd-HH:mm:ss') 
     AND parse_datetime('2020-07-21-05:45:00','yyyy-MM-dd-HH:mm:ss')
     AND elb_status_code like '4%'

Cloudfront

SELECT *
FROM cloudfront_logs_prod
WHERE (
  parse_datetime(concat(cast(date AS varchar),'T', cast(time AS varchar)), 'yyyy-MM-dd''T''HH:mm:ss')
  BETWEEN 
    parse_datetime('2020-07-14T01:20:00', 'yyyy-MM-dd''T''HH:mm:ss') AND
    parse_datetime('2020-07-14T01:30:40', 'yyyy-MM-dd''T''HH:mm:ss')
  
) AND
status > 400 AND status < 600
LIMIT 1000

SELECT date,time,location,bytes,request_ip,method,uri,status
FROM cloudfront_logs_prod
WHERE (
  parse_datetime(concat(cast(date AS varchar),'T', cast(time AS varchar)), 'yyyy-MM-dd''T''HH:mm:ss')
  BETWEEN 
    parse_datetime('2020-07-14T01:20:00', 'yyyy-MM-dd''T''HH:mm:ss') AND
    parse_datetime('2020-07-14T01:40:40', 'yyyy-MM-dd''T''HH:mm:ss')
)
LIMIT 100;