ALB Logs Athena

CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_logs (
  type string,
  time string,
  elb string,
  client_ip string,
  client_port int,
  target_ip string,
  target_port int,
  request_processing_time double,
  target_processing_time double,
  response_processing_time double,
  elb_status_code int,
  target_status_code string,
  received_bytes bigint,
  sent_bytes bigint,
  request_verb string,
  request_url string,
  request_proto string,
  user_agent string,
  ssl_cipher string,
  ssl_protocol string,
  target_group_arn string,
  trace_id string,
  domain_name string,
  chosen_cert_arn string,
  matched_rule_priority string,
  request_creation_time string,
  actions_executed string,
  redirect_url string,
  lambda_error_reason string,
  target_port_list string,
  target_status_code_list string,
  classification string,
  classification_reason string,
  conn_trace_id string
)
PARTITIONED BY (day STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  "serialization.format" = "1",
  "input.regex" = 
    '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) "([^ ]*) (.*) (- |[^ ]*)" "([^"]*)" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) "([^"]*)" "([^"]*)" "([^"]*)" ([-.0-9]*) ([^ ]*) "([^"]*)" "([^"]*)" "([^"]*)" "([^\s]+?)" "([^\s]+)" "([^ ]*)" "([^ ]*)" ?([^ ]*)?'
)
LOCATION 's3://example-co-in-aws-alb-logs/AWS-3AWS-Account-ID-NO5/alb-solution-example-co-in/AWSLogs/3AWS-Account-ID-NO5/elasticloadbalancing/ap-south-1/'
TBLPROPERTIES (
  "projection.enabled" = "true",
  "projection.day.type" = "date",
  "projection.day.range" = "2025/09/01,NOW",
  "projection.day.format" = "yyyy/MM/dd",
  "projection.day.interval" = "1",
  "projection.day.interval.unit" = "DAYS",
  "storage.location.template" = "s3://example-co-in-aws-alb-logs/AWS-3AWS-Account-ID-NO5/alb-solution-example-co-in/AWSLogs/3AWS-Account-ID-NO5/elasticloadbalancing/ap-south-1/${day}/"
);

MSCK REPAIR TABLE alb_access_logs;

SELECT time, elb_status_code, client_ip, request_url
FROM alb_access_logs
WHERE day = '2025/09/04'
LIMIT 10;

SELECT time, elb_status_code, client_ip, request_url
FROM alb_access_logs
WHERE elb_status_code BETWEEN 400 AND 599
  AND parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') >= current_timestamp - interval '5' hour
ORDER BY time DESC
LIMIT 50;