A DDL to convert S3 access logs into an AWS Athena table.

ddl-athena-s3-logs.sql

CREATE EXTERNAL TABLE IF NOT EXISTS azavea_datahub_logs.azavea_datahub (
  bucket_owner string,
  bucket string,
  requested_at string,
  remote_ip string,
  requester string,
  request_id string,
  operation string,
  key string,
  request_uri_operation string,
  request_uri_key string,
  request_uri_http_version string,
  http_status string,
  error_code string,
  bytes_sent string,
  object_size string,
  total_time string,
  turn_around_time string,
  referrer string,
  user_agent string,
  version_id string 
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*)$' )
LOCATION 's3://azavea-datahub-logs/azavea-datahub/';