How to Set Dynamic S3 Query Result Location in Athena Workgroups

Posted on by user

Athena allows you to dynamically organize query results in Amazon S3 using variables such as ${year}, ${month}, and ${day}. This helps structure query results by date, making them easier to manage and clean up.

Step 1: Open Athena Workgroup Settings

  1. Go to AWS Management Console → Open Athena.
  2. Click “Workgroups” (from the left-side menu).
  3. Select the Workgroup you want to configure (e.g., primary).
  4. Click “Edit workgroup” (top-right corner).

Step 2: Configure the Query Result Location

  1. Under “Query results location and encryption”, find “S3 location”.
  2. Enter the following dynamic S3 path:
    s3://your-athena-query-results/AthenaOutput/${year}/${month}/${day}/
  3. Click “Save changes”.

Step 3: Verify Configuration

  1. Go back to Athena and run a sample query:
    SELECT 'Test Query' AS result;
  2. Open your S3 bucket (your-athena-query-results) and check:
    s3://your-athena-query-results/AthenaOutput/2025/02/24/
    If the folder structure is created correctly, the setup is successful.

Step 4: Automate Cleanup with S3 Lifecycle Rules

Since Athena automatically stores query results, you may want to delete old results after a certain period.

  1. Go to the S3 bucket (your-athena-query-results).
  2. Click “Management” → “Create lifecycle rule”.
  3. Set a rule to delete objects under /AthenaOutput/ after 30 days.

This prevents unnecessary storage costs and keeps your S3 bucket clean.

Benefits of Using Dynamic Query Result Paths

FeatureBenefit
Automatic Query Result OrganizationEach query’s result is saved in a structured way by date.
Easy TrackingYou can quickly locate query results from a specific day.
Simplified CleanupApply S3 Lifecycle policies to delete results older than 30 days.
No Manual Sorting RequiredQueries automatically store results in the correct date folder.

Alternative: Configure Workgroup via AWS CLI

If you want to set up Athena Workgroup settings via AWS CLI, run:

aws athena update-work-group \
  --work-group "primary" \
  --configuration-updates ResultConfigurationUpdates="{OutputLocation='s3://your-athena-query-results/AthenaOutput/${year}/${month}/${day}/'}"

This achieves the same dynamic S3 query result storage without using the AWS console.

Conclusion

Setting S3 dynamic query result locations in Athena improves organization, makes tracking easier, and reduces storage costs when combined with lifecycle policies.
By following these steps, you’ll have a well-structured, automated query result management system!