Querying S3 Batch Operations completion reports with Amazon Athena

You can use S3 Batch Operations to perform large-scale batch operations on Amazon S3 objects. S3 Batch Operations can perform a single operation on lists of Amazon S3 objects that you specify. A single job can perform a specified operation on billions of objects containing exabytes of data. Amazon S3 tracks progress, sends notifications, and stores a detailed completion report of all actions, providing a fully managed, auditable, and serverless experience.

When running an S3 Batch Operations job on a large number of objects, you may find "Job completed with failures", like this:

When creating a job, you can specify a report location, and whether all tasks are logged or only failures. S3 Batch operations completion reports are formatted as described here.

You may need the successes reported for logging and auditing purposes, and in this case you will need to just find the failures are so you can address them.

The job above, with over 99 million tasks, created 174 CSV files totalling over 11 GB. There are two methods to find the failures amongst these:

1. Start by opening the manifest.json file from your job's Completion report destination. This lists all the CSVs and their checksums. It also has a TaskExecutionStatus entry for each - only one should be "failed". Search for "failed", then open the S3 object key from that section, e.g:

{
      "TaskExecutionStatus": "failed",
      "Bucket": "<mybucket>",
      "MD5Checksum": "c57210e56d40541a6ba0ec2b6356a8d3",
      "Key": "<prefix>/job-<job_id>/results/7bb04c8806ab0695fa711788962e15a1431bbfda.csv"
    },

2. You could also filter through all the CSVs using Amazon Athena. This is particularly useful if you want to search for all operations on a particular key or prefix. In Query editor, under your default (or preferred) catalogue and database, run the following query to tell Athena how to process the S3 location with the CSVs. Use the Completion report destination from your S3 Batch Operations job as LOCATION.

CREATE EXTERNAL TABLE IF NOT EXISTS job_results (
    bucket_name STRING,
    object_key STRING,
    VersionId STRING,
    TaskStatus STRING,
    ErrorCode INT,
    HTTPStatusCode INT,
    ResultMessage  STRING
    )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
    'serialization.format' = ',',
    'field.delim' = ','
    )
LOCATION 's3://<myreportsbucket>/<prefix>/job-<job_id>/results/'

Now run this query to find all failed tasks:

SELECT * FROM job_results
WHERE taskstatus LIKE 'failed'

In the case of the example above, Athena found the failed row in around 5 seconds at a cost of under $0.06.

If you need to troubleshoot S3 Batch Operations issues, please refer to this article.