Athena Table for ALB Logs

alb_logs.sql

-- This creates an athena table that can parse ALB logs.
-- Advantage of this over others are this works when the log ends with a trailing space
-- plus it also breaks the http request into route and params for easier grouping

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (
    type string, 
    timestamp 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 string,
    target_status_code string,
    received_bytes bigint,
    sent_bytes bigint,
    request_verb string, 
    protocol string, 
    host string,
    port int,
    route string,
    params string,
    httpversion string, 
    user_agent string,
    ssl_cipher string,
    ssl_protocol string,
    target_group_arn string,
    trace_id string )
PARTITIONED BY(year string, month string, 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]*) ([-0-9]*) ([-0-9]*) "(.*?) (.*?)://(.*?):([0-9]+)([^? ]*)(\\x3f?.*?) (.*?)" "(.*?)" (.*?) (.*?) (.*?) "(.*?)" *$'
   ) 
LOCATION 's3://bucket/api-alb/external/AWSLogs/accountid/elasticloadbalancing/us-west-2/'

Thanks a lot

They were changed again (adding two fields for https) we ended up adding a 'logline_tail' column and just making it (.*) at the end.

@mpilar Would you mind sharing your version of the query? Thanks in advance!

https://docs.aws.amazon.com/athena/latest/ug/application-load-balancer-logs.html

doesn't work... i'm getting zero records... can someone advise on the possible problem? running the aws tutorial worked

Is there a way to import time as timestamp instead of string, this make queries slow as each record requires parsing.

doesn't work... i'm getting zero records... can someone advise on the possible problem? running the aws tutorial worked

I was facing the same issue. It's because the location does not hold any logs. Check the location