Querying Classic Load Balancer Logs: 504 log entries are omitted

[awsdocs]

Querying Classic Load Balancer Logs: 504 log entries are omitted

[gilly3]

Here's how I had to adjust the schema and regex in order to get the table to include log entries containing 504 responses:

CREATE EXTERNAL TABLE IF NOT EXISTS default.elb_logs (
  `request_timestamp` string,
  `elb_name` string,
  `request_ip` string,
  `request_port` int,
  `backend_ip_port` string,
  `request_processing_time` double,
  `backend_processing_time` double,
  `client_response_time` double,
  `elb_response_code` string,
  `backend_response_code` string,
  `received_bytes` bigint,
  `sent_bytes` bigint,
  `request_verb` string,
  `url` string,
  `protocol` string,
  `user_agent` string,
  `ssl_cipher` string,
  `ssl_protocol` string 
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*) (-1|[.0-9]*) (-1|[.0-9]*) (-1|[.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) ([^ ]*) (- |[^ ]*)\" (\"[^\"]*\") ([A-Z0-9-]+) ([A-Za-z0-9.-]*)$'
) LOCATION 's3://your_log_bucket/prefix/AWSLogs/AWS_account_ID/elasticloadbalancing/'
TBLPROPERTIES ('has_encrypted_data'='false')

[JuliaMalkin]

Hi gilly3!
Sorry for the delays in responding. Thank you for reaching out. You raised absolutely valid points and we are looking into it. I will post here once I have more information, hopefully shortly. Thanks again for writing to us! Will keep it open for now.

[JuliaMalkin]

Hi Ivan,
I consulted with the team and they agreed with your issue. Thank you for reporting it. They suggested a slightly different REGEX. Please see the updated topic with the example query. I will close this request without accepting a pull request, only because their solution is different, even though it addresses the same issue. Please do write back to us and let us know if you notice other issues! Thank you, as always. Also, I am sorry for the delays!