Hey there! Let's break down your two questions and walk through practical alternatives since Athena doesn't support DELETE FROM statements directly:

1. Can you delete rows from an Athena table? #

Short answer: No, not directly. Athena is a serverless query engine built on top of S3 storage, not a transactional database. It doesn't support row-level deletion operations like traditional SQL databases do. This is because your table data lives as files in S3—Athena just reads and queries those files, it doesn't modify them in-place.

2. Can you delete S3 data via an Athena query? #

Also no. Athena is read-only when it comes to modifying underlying storage. It can't issue commands to delete files in S3 directly through a query.

Practical Alternatives for Removing Rows (Better Than Manual S3 File Hunting) #

If you need to remove specific rows from tables across different S3 buckets, here are some streamlined approaches:

• Create a filtered copy of your table (CTAS)
  Use the CREATE TABLE AS SELECT (CTAS) statement to build a new table that only includes the rows you want to keep. Then swap this new table in place of the original. Example:

  CREATE TABLE filtered_table
  WITH (
    format = 'Parquet', -- or your table's existing format
    external_location = 's3://your-target-bucket/filtered-table-path/'
  ) AS
  SELECT * FROM original_table
  WHERE NOT (row_id = '123' OR created_date < '2023-01-01'); -- condition to exclude rows you want to delete
  

  Once the new table is ready, you can drop the original table and rename the filtered one to match the original name. This keeps your query interface the same while removing unwanted rows.

• Drop entire partitions (if your table is partitioned)
  If your table uses partitioning (e.g., by date, region, or category), and all the rows you want to delete fall within specific partitions, you can drop those partitions directly. This automatically deletes the corresponding S3 files too:

  ALTER TABLE your_partitioned_table DROP PARTITION (date='2022-12-31');
  

  This is way faster than manually locating and deleting partition folders in S3.

• Use AWS Glue ETL for targeted deletions
  For more complex deletion logic, set up a Glue ETL job:

  1. Use Athena to query and filter the rows you want to retain
  2. Write the filtered dataset back to S3 (either overwriting the original location or a new one)
  3. Refresh Athena's table metadata to reflect the updated data

Bonus: For Frequent Deletion Needs #

If you regularly need to perform row-level updates or deletions, consider using Athena with ACID-compliant storage formats like Apache Iceberg or Apache Hudi. These formats support transactional operations (including DELETE and UPDATE) while still leveraging S3 storage.

内容的提问来源于stack exchange，提问作者Guillermo Mirandes