Skip to main content

​
ALB S3 Log View in Athena

Purpose: Create an Athena table for ALB access logs with partition projection so you can query logs efficiently without manually managing partitions. This avoids errors like “No location was specified for table.”

​
1. Create Athena Table with Partition Projection

Use the following SQL in 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}/"
);
This follows the AWS official recommendation using Partition Projection.

​
2. Why This Works

  • LOCATION points to the S3 bucket prefix.
  • Regex SerDe parses fields correctly, even when user_agent or request_url contain spaces.
  • Partition Projection automatically manages partitions using the day string (yyyy/MM/dd).
  • No need for manual ALTER TABLE ADD PARTITION or repeated MSCK REPAIR TABLE.
  • Athena computes partition locations using storage.location.template.

​
3. Optional: Repair Table (if needed)

MSCK REPAIR TABLE alb_access_logs;

​
4. Quick Verification Queries

  1. Test query for a specific day:
SELECT time, elb_status_code, client_ip, request_url
FROM alb_access_logs
WHERE day = '2025/09/04'
LIMIT 10;
  1. Check 4xx/5xx errors in the last 5 hours:
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;

​
✅ Summary

  • ALB logs from S3 are automatically parsed with correct field mapping.
  • Daily partitions are handled automatically via partition projection.
  • Queries on specific days or error ranges work without manual partition updates.
  • This setup is clean, efficient, and fully compatible with Athena best practices.