Amazon athena can't read S3 Access log files and Athena select query returns empty result sets for every column

user_3777574 0
Viewed 837

I successfully created database table in Athena. See the query below.

   CREATE EXTERNAL TABLE IF NOT EXISTS s3_access_logs_db.wafbucket_logs(
      BucketOwner STRING,
      Bucket STRING,
      RequestDateTime STRING,
      RemoteIP STRING,
      Requester STRING,
      RequestID STRING,
      Operation STRING,
      Key STRING,
      RequestURI_operation STRING,
      RequestURI_key STRING,
      RequestURI_httpProtoversion STRING,
      HTTPstatus STRING,
      ErrorCode STRING,
      BytesSent BIGINT,
      ObjectSize BIGINT,
      TotalTime STRING,
      TurnAroundTime STRING,
      Referrer STRING,
      UserAgent STRING,
      VersionId STRING,
      HostId STRING,
      SigV STRING,
      CipherSuite STRING,
      AuthType STRING,
      EndPoint STRING,
      TLSVersion STRING
  ) 
  ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
  WITH SERDEPROPERTIES (
               'serialization.format' = '1', 'input.regex' = '([^ ]*) ([^ ]*) 
               \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)
               \\\" (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*)
               (?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$' )
      LOCATION 's3://stb-aws-bucket-logging/logs/';

However, when I run the query against the table, it returned an empty result set. There are 20 rows but all empty!!!

SELECT * FROM s3_access_logs_db.wafbucket_logs limit 20;

Does anyone run into this issue before?

Thanks, Tuan

amazon-athena
2 Answers

I found my mistake in the parsing format. It had a linefeed! I copied the parser format for Access log from AWS documentation. I guess I inadvertently added a linefeed in the parser format! Here's the correct parser format:

'serialization.format' = '1', 'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\\"([^ ]*) ([^ ]*) (- |[^ ]*)\\\" (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*) (?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$'

It worked!!!

Your comments forced me to re-examine the parser format.

Thanks, Tuan

user_37775740

I had the same issue, and the problem is most probably in table config (regex, fields...). In my case, I was using the wrong docs for creating a table. I actually needed alb logs, but I thought that they are the same as the access logs. Check this out: https://docs.aws.amazon.com/athena/latest/ug/application-load-balancer-logs.html

You can also try to use your regex and download data manually from the s3 bucket and test it on that regex, that helped me a lot.

user_60913210
Related