create_cloudfront_athena_table.sql

CREATE EXTERNAL TABLE cloudfront_logs (
    date date COMMENT '',
    time string COMMENT '',
    location string COMMENT '',
    bytes bigint COMMENT '',
    requestip string COMMENT '',
    method string COMMENT '',
    host string COMMENT '',
    uri string COMMENT '',
    status int COMMENT '',
    referrer string COMMENT '',
    useragent string COMMENT '',
    querystring string COMMENT '',
    cookie string COMMENT '',
    resulttype string COMMENT '',
    requestid string COMMENT '',
    hostheader string COMMENT '',
    requestprotocol int COMMENT '',
    requestbytes bigint COMMENT '',
    timetaken double COMMENT '',
    xforwardedfor string COMMENT '',
    sslprotocol string COMMENT '',
    sslcipher string COMMENT '',
    responseresulttype string COMMENT '',
    protocol_version string COMMENT '',
    fle_status string COMMENT '',
    fle_encrypted_fields string COMMENT ''
  )
  PARTITIONED BY (
    distributionId string, year string, month string, day string, hour string
  )
  ROW FORMAT SERDE
    'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
    'input.regex'='^(?!#)([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)\\s+([^ \\t]+)$')
  STORED AS INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
  OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
  LOCATION
    's3://bucket/logs/'