How do I use Athena to analyze my Amazon S3 server access logs?

Resolution

Amazon S3 stores server access logs as objects in an S3 bucket. Amazon S3 supports non-date-based partitioning and date-based partitioning object key formats for log objects.

Use Amazon Athena to query the logs and analyze access patterns, troubleshoot issues, and audit requests to your S3 buckets.

Note: It's a best practice to use date-based partitioning because it improves query performance and reduces the amount of data that Athena scans. Because Athena charges are based on the amount of data scanned, date-based partitioning also reduces query costs.

Use date-based partitioning

Turn on server access logging

Complete the following steps:

1. If it isn't already active, then turn on log delivery.
2. Note the values for the destination bucket and destination prefix. You need both values to specify the Amazon S3 location in your Athena query.
3. For the log object key format, choose date-based partitioning.

Note: When you choose date-based partitioning, select either event time or delivery time as the date source for the log format.

Create an Athena table for date-based partitioned logs

Complete the following steps:

1. Open the Athena console.
2. In the navigation pane, choose Query editor.
3. In the Athena query editor, use the following data definition language (DDL) statement to create a table:

   CREATE EXTERNAL TABLE s3_access_logs_db.mybucket_logs(
    `bucketowner` STRING,
    `bucket_name` STRING,
    `requestdatetime` STRING,
    `remoteip` STRING,
    `requester` STRING,
    `requestid` STRING,
    `operation` STRING,
    `key` STRING,
    `request_uri` 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,
    `accesspointarn` STRING,
    `aclrequired` STRING,
    `sourceregion` STRING)
    PARTITIONED BY (
      `timestamp` string)
   ROW FORMAT SERDE  'org.apache.hadoop.hive.serde2.RegexSerDe'
   WITH SERDEPROPERTIES (
    'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$')
   STORED AS INPUTFORMAT  'org.apache.hadoop.mapred.TextInputFormat'
   OUTPUTFORMAT  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
   LOCATION 's3://bucket-name/prefix-name/account-id/region/source-bucket-name/'
    TBLPROPERTIES (
     'projection.enabled'='true',
     'projection.timestamp.format'='yyyy/MM/dd',
     'projection.timestamp.interval'='1',
     'projection.timestamp.interval.unit'='DAYS',
     'projection.timestamp.range'='2024/01/01,NOW',
     'projection.timestamp.type'='date',
     'storage.location.template'='s3://bucket-name/prefix-name/account-id/region/source-bucket-name/${timestamp}')

   Note: Replace bucket-name with the S3 bucket where access logs are stored, prefix-name with the folder path for your logs, and account-id with your AWS account ID. Replace region with your AWS Region, source-bucket-name with the name of your source bucket, s3_access_logs_db with your Athena database name, and mybucket_logs with your table name.

For the up-to-date DDL statements, see Querying access logs for requests by using Amazon Athena.

When you query partitioned data, it’s a best practice to include partition columns such as year, month, and day in your WHERE clause. Including partition columns reduces the amount of data that Athena scans and lowers query costs.

Since this DDL uses partition projection, you don’t need to use the MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION commands to explicitly load the partitions.

Use non-date-based partitioned logs

If your S3 server access logs don't use date-based partitioning, use one of the following options.

Create a table for unpartitioned data

Create an Athena table that queries all logs without partitions.

Note: Every query scans all logs in the S3 bucket, which results in longer query times and higher costs.

To create a table for non-partitioned logs, use the following DDL statement:

CREATE EXTERNAL TABLE `s3_access_logs_db.mybucket_logs`(
  `bucketowner` STRING,
  `bucket_name` STRING,
  `requestdatetime` STRING,
  `remoteip` STRING,
  `requester` STRING,
  `requestid` STRING,
  `operation` STRING,
  `key` STRING,
  `request_uri` 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,
  `accesspointarn` STRING,
  `aclrequired` STRING,
  `sourceregion` STRING)
ROW FORMAT SERDE   'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$')
STORED AS INPUTFORMAT   'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION  's3://amzn-s3-demo-bucket1-logs/prefix/'

Note: Replace s3_access_logs_db with your database name. Replace mybucket_logs with your table name. Replace amzn-s3-demo-bucket1-logs with your bucket name. Replace prefix/ with the path for where the log files are located.

Partition existing logs

For better query performance and lower costs, partition your existing logs. Use an AWS Glue extract, transform, and load (ETL) job to reorganize your logs into a date-based partition structure. After you partition the data, use the DDL statement from the Create an Athena table for date-based partitioned logs section to create a table.

For more information on how to partition Amazon S3 access logs with AWS Glue, see How do I use a partitioned Amazon S3 access log to prevent an Athena query timeout?