1. Why Do You Need to Set an S3 Query Result Location in Athena?

Athena is a serverless query engine, meaning it does not store query results locally.
Instead, all query results are automatically saved to Amazon S3.
If you do not specify a query result location, Athena will not execute queries and will display the following error:

“Before you run your first query, you need to set up a query result location in Amazon S3.”

---

2. Best S3 Location for Query Results

A recommended structure for storing Athena query results is:

s3://your-athena-query-results/AthenaOutput/

Recommended S3 Folder Structure

s3://your-athena-query-results/   　 ✅ Dedicated Athena query results bucket (recommended)
  ├── AthenaOutput/               ✅ Stores query results
  ├── Logs/                       ✅ (Optional) Stores Athena logs
  ├── Temp/                       ✅ (Optional) Stores temporary files

Benefits:

• Easier management → Keep Athena query results separate from other S3 data.
• Better security control → Apply IAM policies and CloudTrail logs on a dedicated bucket.
• Structured data organization → Query results are stored in a designated folder.

---

3. How to Set Up Query Result Location in Athena

Option 1: Configure via AWS Console

1. Go to AWS Athena console
2. Click “Settings” (left-side menu)
3. Set “Query result location”
   • Enter: s3://your-athena-query-results/AthenaOutput/
4. Click “Save”
5. Run your query in Athena

---

Option 2: Configure via AWS CLI

If you use Athena Workgroups, you can update the query result location using AWS CLI:

aws athena update-work-group \
  --work-group "primary" \
  --configuration-updates ResultConfiguration="{OutputLocation='s3://your-athena-query-results/AthenaOutput/'}"

Notes:

• primary is the default workgroup.
• The OutputLocation defines where Athena will store query results.

---

Option 3: Temporarily Set Query Result Location in SQL

If you want to set the query result location for a single session, you can do so using SQL:

SET LOCATION 's3://your-athena-query-results/AthenaOutput/';

This will apply only for the current query session.

---

4. Best Practices for Managing Query Results

Athena stores query results indefinitely, so it’s best to manage and clean up old results to avoid unnecessary S3 storage costs.

(1) Organize Query Results by Date

A more structured way to store Athena query results is to separate them by date:

s3://your-athena-query-results/AthenaOutput/YYYY/MM/DD/

You can set this up dynamically using Athena Workgroup settings:

s3://your-athena-query-results/AthenaOutput/${year}/${month}/${day}/

This keeps query results well-organized and easy to manage.

---

(2) Set an S3 Lifecycle Policy to Delete Old Results

Since Athena query results are often temporary, you can configure S3 to automatically delete older query results after a certain period (e.g., 30 days).

Steps to Set a Lifecycle Rule:

1. Go to the S3 bucket in AWS Console.
2. Click the “Management” tab.
3. Create a new Lifecycle Rule.
4. Apply the rule to s3://your-athena-query-results/AthenaOutput/.
5. Set “Expiration” to delete files after 30 days.

Benefits:

• Reduces S3 storage costs by automatically removing old query results.
• Keeps the bucket clean without manual cleanup.

---

5. Summary

Setting	Purpose	Configuration
S3 Query Result Location	Stores Athena query results	s3://your-athena-query-results/AthenaOutput/
AWS Console	Permanent setting for query results	Athena > Settings > Query result location
AWS CLI	Workgroup-wide setting	aws athena update-work-group
SQL (Temporary)	One-time setting for session	SET LOCATION 's3://your-athena-query-results/AthenaOutput/';
S3 Lifecycle Rule	Automatically delete old query results	Set expiration after 30 days

---

Best Practice Recommendation

1. Use s3://your-athena-query-results/AthenaOutput/ as your query result location.
2. Organize query results by date (YYYY/MM/DD/).
3. Set an S3 Lifecycle Policy to delete results after 30 days.
4. (Optional) Use Workgroups to separate query results per project.

By following this setup, your Athena queries will run smoothly, and S3 storage costs will remain optimized!