Skip to content

How do I use a partitioned Amazon S3 access log to prevent an Athena query timeout?

7 minute read
0

When I run Amazon Athena queries for Amazon Simple Storage Service (Amazon S3) access logs, the query times out. I want to use a partitioned Amazon S3 access log to prevent this timeout.

Short description

Amazon S3 access logs share the same prefix. If there's a large amount of data, then Athena queries might time out before Athena reads all the data. To prevent this issue, partition your Amazon S3 access logs and then run Athena queries on limited partitions.

To partition your access logs, configure Amazon S3 to deliver logs in a date-based partitioned format (recommended). Or, use an AWS Glue ETL job to partition existing logs that you configured before date-based partitioning was available.

Resolution

Configure date-based partitioning for Amazon S3 access logs

To deliver access logs in a partitioned format, configure the log object key format to use date-based partitioning.

Complete the following steps:

  1. Open the Amazon S3 console.
  2. In the left navigation pane, choose General purpose buckets.
  3. Choose the name of the bucket that you want to activate server access logging for.
  4. Choose Properties.
  5. In the Server access logging section, choose Edit.
  6. Under Server access logging, choose Enable.
  7. Under Destination bucket, specify a bucket and an optional prefix.
  8. Under Log object key format, choose [DestinationPrefix][SourceAccountId]/[SourceRegion]/[SourceBucket]/[YYYY]/[MM]/[DD]/[YYYY]-[MM]-[DD]-[hh]-[mm]-[ss]-[UniqueString], and then choose S3 event time or Log file delivery time.
  9. Choose Save changes.

After you activate date-based partitioning, create the following table in Athena to query the partitioned logs. This table uses partition projection to automatically resolve partitions at query time without a crawler or manual partition management.

CREATE EXTERNAL TABLE IF NOT EXISTS s3_access_logs_db.s3_access_logs_partitioned (
 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,
 turnaround_time STRING,
 referrer STRING,
 useragent STRING,
 versionid STRING,
 hostid STRING,
 signatureversion STRING,
 ciphersuite STRING,
 authtype STRING,
 endpoint STRING,
 tlsversion STRING,
 accesspointarn STRING,
 aclrequired STRING
)
PARTITIONED BY (
 account STRING,
 region STRING,
 source_bucket STRING,
 `timestamp` STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
 'serialization.format' = '1',
 'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ("[^"]*"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ("[^"]*"|-) ("[^"]*"|-) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*)')
LOCATION 's3://my-s3-access-logs-bucket/access-logs/'
TBLPROPERTIES (
 'projection.enabled' = 'true',
 'projection.account.type' = 'injected',
 'projection.region.type' = 'injected',
 'projection.source_bucket.type' = 'injected',
 'projection.timestamp.type' = 'date',
 'projection.timestamp.format' = 'yyyy/MM/dd',
 'projection.timestamp.range' = '2024/01/01,NOW',
 'projection.timestamp.interval' = '1',
 'projection.timestamp.interval.unit' = 'DAYS',
 'storage.location.template' = 's3://my-s3-access-logs-bucket/access-logs/${account}/${region}/${source_bucket}/${timestamp}/');

Note: Replace my-s3-access-logs-bucket/access-logs/ with the path to your log destination bucket and prefix and 123456789012 with your AWS account ID. Also, replace 2024/01/01 with the date you activated date-based partitioning.

To query the partitioned table, run the following command in Athena:

SELECT * FROM s3_access_logs_db.s3_access_logs_partitioned
WHERE account = '123456789012'
AND region = 'us-east-1'
AND source_bucket = 'my-source-bucket'
AND timestamp = '2024/03/04'

Note: Replace 123456789012 with your AWS account ID, us-east-1 with your AWS Region, my-source-bucket with your source bucket name, and 2024/03/04 with your date.

Use an AWS Glue ETL job to partition existing logs

Use this method for older access log exports that you configured before date-based partitioning was available.

Create a table in Athena

Create the following table in Athena:

CREATE EXTERNAL TABLE `s3_access_logs_db.s3_access_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` string,
 `objectsize` string,
 `totaltime` string,
 `turnaround_time` string,
 `referrer` string,
 `useragent` string,
 `version_id` string,
 `hostid` string,
 `sigv` string,
 `ciphersuite` string,
 `authtype` string,
 `endpoint` string,
 `tlsversion` string,
 `accesspoint_arn` string,
 `aclrequired` string)
ROW FORMAT SERDE
 'com.amazonaws.glue.serde.GrokSerDe'
WITH SERDEPROPERTIES (
 'input.format'='%{NOTSPACE:bucketowner} %{NOTSPACE:bucket_name} \\[%{INSIDE_BRACKETS:requestdatetime}\\] %{NOTSPACE:remoteip} %{NOTSPACE:requester} %{NOTSPACE:requestid} %{NOTSPACE:operation} %{NOTSPACE:key} "%{INSIDE_QS:request_uri}" %{NOTSPACE:httpstatus} %{NOTSPACE:errorcode} %{NOTSPACE:bytes_sent} %{NOTSPACE:objectsize} %{NOTSPACE:totaltime} %{NOTSPACE:turnaround_time} "?%{INSIDE_QS:referrer}"? "%{INSIDE_QS:useragent}" %{NOTSPACE:version_id} %{NOTSPACE:hostid} %{NOTSPACE:sigv} %{NOTSPACE:ciphersuite} %{NOTSPACE:authtype} %{NOTSPACE:endpoint} %{NOTSPACE:tlsversion}( %{NOTSPACE:accesspoint_arn} %{NOTSPACE:aclrequired})?',
 'input.grokCustomPatterns'='INSIDE_QS ([^"]*)\nINSIDE_BRACKETS ([^\\]]*)')
STORED AS INPUTFORMAT
 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
 's3://awsexamplebucket1-logs/prefix/';

Note: Replace s3_access_logs_db with your database name and s3://awsexamplebucket1-logs/prefix/ with the path that stores your Amazon S3 access logs. Also, replace s3_access_logs with your table name and s3_access_logs_partitioned with your partitioned table name.

Create and run the ETL job

Complete the following steps:

  1. Open the AWS Glue console.
  2. Choose ETL jobs, and then choose Script editor.
  3. Choose Create.
  4. On the Script tab, enter the following script:
    import sys
    from awsglue.transforms import *
    from awsglue.utils import getResolvedOptions
    from pyspark.context import SparkContext
    from awsglue.context import GlueContext
    from awsglue.job import Job
    from pyspark.sql.functions import split, col, size
    from awsglue.dynamicframe import DynamicFrame
    
    ## @params: [JOB_NAME]
    args = getResolvedOptions(sys.argv, ['JOB_NAME'])
    
    sc = SparkContext()
    glueContext = GlueContext(sc)
    spark = glueContext.spark_session
    job = Job(glueContext)
    job.init(args['JOB_NAME'], args)
    
    dyf = glueContext.create_dynamic_frame.from_catalog(database='s3_access_logs_db', table_name='s3_access_logs', transformation_ctx = 'dyf',additional_options = {"attachFilename": "s3path"})
    
    df = dyf.toDF()
    df2=df.withColumn('filename',split(col("s3path"),"/"))\
        .withColumn('year',split(col("filename").getItem(size(col("filename"))-1),"-").getItem(0))\
        .withColumn('month',split(col("filename").getItem(size(col("filename"))-1),"-").getItem(1))\
        .withColumn('day',split(col("filename").getItem(size(col("filename"))-1),"-").getItem(2))\
        .drop('s3path','filename')
    
    output_dyf = DynamicFrame.fromDF(df2, glue_ctx=glueContext, name = 'output_dyf')
    
    partitionKeys = ['year', 'month', 'day']
    sink = glueContext.getSink(connection_type="s3", path='s3://awsexamplebucket2-logs/prefix/',
                  enableUpdateCatalog=True, updateBehavior="UPDATE_IN_DATABASE",
                  partitionKeys=partitionKeys)
    sink.setFormat("glueparquet")
    sink.setCatalogInfo(catalogDatabase='s3_access_logs_db', catalogTableName='s3_access_logs_partitioned')
    sink.writeFrame(output_dyf)
    
    job.commit()
    Note: Replace s3_access_logs_db with your database name and s3://awsexamplebucket2-logs/prefix/ with your output S3 path. Also, replace s3_access_logs with your table name and s3_access_logs_partitioned with your partitioned table name.
  5. On the Job details tab, enter the name of your job, and then choose IAM role.
  6. Choose Save, and then choose Run.
    Note: Amazon S3 delivers access logs on a regular basis. To set time-based schedules for the AWS Glue ETL job, add a trigger. Also, turn on job bookmarks.

The ETL script converts access logs to Parquet format. Parquet provides significant query performance gains over plain text logs because Athena doesn't need to read the full file. However, processing the log files takes time and incurs costs, which might not be worth it if the logs aren't accessed frequently.

Resolve out-of-memory errors in the AWS Glue ETL job

Amazon S3 delivers access logs as many small files. When the AWS Glue ETL job runs, the driver stores each file path in memory. As the number of files grows, the driver runs out of memory.

To resolve out-of-memory (OOM) errors, take the following actions:

  • Scale up to a larger worker type such as G.2X, G.4X, or a memory-optimized R type. This provides more memory for the driver. For more information, see AWS Glue worker types.

  • Add "useS3ListImplementation":True to the additional_options parameter to reduce memory usage when listing files.

    additional_options = {"attachFilename": "s3path", "useS3ListImplementation":True}
  • Group multiple small files into a single Spark task instead of using one task per file. Add the following options to the additional_options parameter:

    additional_options = {"attachFilename": "s3path", "groupFiles": "inPartition", "groupSize": "1048576"}
  • Limit the number of input files per job run. The job processes the full dataset across multiple runs. To use this option, turn on job bookmarks and add the boundedFiles option to the additional_options parameter.

    additional_options = {"attachFilename": "s3path", "boundedFiles": "500"}

For more information, see Workload partitioning with bounded execution.

The following example shows all options combined in a single additional_options parameter:

additional_options = {"attachFilename": "s3path", "useS3ListImplementation":True, "groupFiles": "inPartition", "groupSize": "1048576", "boundedFiles": "500"}

Query the partitioned table

After the AWS Glue ETL job completes, run the following command in Athena to query the partitioned table.

SELECT * FROM "s3_access_logs_db"."s3_access_logs_partitioned" WHERE year = '2023' AND month = '03' AND day = '04'

Note: Replace 2023, 03, and 04 with your partition values.

Related information

Logging requests with server access logging

Enabling Amazon S3 server access logging

Debugging OOM exceptions and job abnormalities

Optimize memory management in AWS Glue

AWS OFFICIALUpdated 5 months ago
4 Comments

The input.format only works for 'newer' access logs. Log files before 2019 had different format / less attributes. Suggest changing the input.format and make some attributes optional:

'input.format'='%{NOTSPACE:bucketowner} %{NOTSPACE:bucket_name} \[%{INSIDE_BRACKETS:requestdatetime}\] %{NOTSPACE:remoteip} %{NOTSPACE:requester} %{NOTSPACE:requestid} %{NOTSPACE:operation} %{NOTSPACE:key} "%{INSIDE_QS:request_uri}" %{NOTSPACE:httpstatus} %{NOTSPACE:errorcode} %{NOTSPACE:bytes_sent} %{NOTSPACE:objectsize} %{NOTSPACE:totaltime} %{NOTSPACE:turnaround_time} "?%{INSIDE_QS:referrer}"? "%{INSIDE_QS:useragent}" %{NOTSPACE:version_id}( %{NOTSPACE:hostid})?( %{NOTSPACE:sigv})?( %{NOTSPACE:ciphersuite})?( %{NOTSPACE:authtype})?( %{NOTSPACE:endpoint})?( %{NOTSPACE:tlsversion})?( %{NOTSPACE:accesspoint_arn})?( %{NOTSPACE:aclrequired})?'

AWS

replied 2 years ago

Thank you for your comment. We'll review and update the Knowledge Center article as needed.

AWS
MODERATOR

replied 2 years ago

I have a huge amount of unpartition access logs in s3 that I'm trying to partition using Glue as suggested above. However, ETL job gets OOM as Driver job eventually reaches 90% memory usage. Any quick solutions?

replied a year ago

This article was reviewed and updated on 2026-06-15.

AWS
MODERATOR

replied 5 months ago