Skip to content

Instantly share code, notes, and snippets.

@davidpanofsky

davidpanofsky/alb.sql

Forked from duckworth/alb.sql
Created June 17, 2017 15:40
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save davidpanofsky/f09a1429cd8c202d7d2572f443b90933 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save davidpanofsky/f09a1429cd8c202d7d2572f443b90933 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @duckworth duckworth created this gist Dec 20, 2016.
    27 changes: 27 additions & 0 deletions alb.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,27 @@
    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,
    url string,
    protocol string,
    user_agent string,
    ssl_cipher string,
    ssl_protocol string,
    target_group_arn string,
    trace_id 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.-]*) ([^ ]*) ([^ ]*)$' )
    LOCATION 's3://somebucket/AWSLogs/';