Create the table for ALB connection logs

Copy and paste the following example CREATE TABLE statement into the query editor in the Athena console, and then modify it as necessary for your own log entry requirements. For information about getting started with the Athena console, see Get started. Replace the path in the LOCATION clause with your Amazon S3 connection log folder location. For more information about connection log file location, see Connection log files in the User Guide for Application Load Balancers. For information about each log file field, see Connection log entries.

CREATE EXTERNAL TABLE IF NOT EXISTS alb_connection_logs (
         time string,
         client_ip string,
         client_port int,
         listener_port int,
         tls_protocol string,
         tls_cipher string,
         tls_handshake_latency double,
         leaf_client_cert_subject string,
         leaf_client_cert_validity string,
         leaf_client_cert_serial_number string,
         tls_verify_status string,
         conn_trace_id string
         ) 
         ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
         WITH SERDEPROPERTIES (
         'serialization.format' = '1',
         'input.regex' =
          '([^ ]*) ([^ ]*) ([0-9]*) ([0-9]*) ([A-Za-z0-9.-]*) ([^ ]*) ([-.0-9]*) \"([^\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*) ?([^ ]*)?( .*)?'
         )
         LOCATION 's3://amzn-s3-demo-bucket/connection-log-folder-path/'

Run the query in the Athena console. After the query completes, Athena registers the alb_connection_logs table, making the data in it ready for you to issue queries.