Partition legacy data in Amazon S3

To partition legacy data in Amazon S3 for cost-effective querying with Athena, you'll need to reorganize your data and create a partitioned table structure. Here's how you can approach this:

1. Determine partition keys: Choose columns that are frequently used in your queries and have low cardinality. Common choices include date-based columns (year, month, day) or categorical data.

2. Reorganize your data: Use an AWS Glue ETL job to read your existing data, extract the partition key information, and write the data back to S3 in a partitioned structure. For example, if you're partitioning by date, your new S3 structure might look like: s3://your-bucket/year=2023/month=05/day=15/data_files.

3. Create a partitioned table in Athena: Use a CREATE TABLE statement with the PARTITIONED BY clause to define your table structure. For example:

CREATE EXTERNAL TABLE your_table ( column1 string, column2 int ) PARTITIONED BY (year string, month string, day string) STORED AS parquet LOCATION 's3://your-bucket/'

4. Add partitions: After creating the table, you need to add the partitions. For Hive-style partitions (as created by the Glue job), you can use:

MSCK REPAIR TABLE your_table

For non-Hive style partitions, you'll need to use ALTER TABLE ADD PARTITION statements.

5. Query your data: Now you can query your partitioned data, and Athena will only scan the relevant partitions based on your WHERE clause, significantly reducing the amount of data scanned and lowering costs.

To save costs:

• Always include partition columns in your WHERE clauses when querying.
• Use compression (e.g., Parquet format) for your data files.
• Regularly clean up or archive old data to reduce storage costs.
• Consider using Athena workgroups to set query result location and enforce cost controls.

By implementing these steps, you'll transform your legacy data into a partitioned structure that Athena can query more efficiently, reducing the amount of data scanned and lowering your costs.
Sources
Partition your data - Amazon Athena
What is partitioning? - Amazon Athena
Prevent Athena query timeout with S3 access logs partition | AWS re:Post
Use partitioning and bucketing - Amazon Athena
ALTER TABLE ADD PARTITION - Amazon Athena

To partition data for Athena and avoid scanning the full bucket, you can create virtual partitions based on specific columns (like date, region, etc.). Use CREATE TABLE statements with the PARTITIONED BY clause to define these columns. When you load data, you can add partitions using ALTER TABLE ADD PARTITION. This way, Athena only scans the relevant partitions during queries, saving costs. Even without prefixes or folders, partition columns in your data will help minimize the amount of data scanned.

For partitioning and table definitions, Athena only considers slash-separated key prefixes that form folder-like hierarchies. Every table or partition is specified as a key prefix in S3, and Athena will scan all objects under the prefix or the partition or multiple that match the query. Furthermore, the prefix must end in a forward slash, and if it isn't specified, Athena will function as it had been specified.

That makes it impossible to use a part of a filename that isn't slash-separated, such as a timestamp prefix (.../mylogfile-20241017.log.gz), to tell Athena to search only objects matching a filename prefix, such as .../mylogfile-202410 to specify only objects for October 2024. Specifying that as the partition or table location would cause Athena to look for objects under the prefix .../mylogfile-202410/, adding the trailing slash that wasn't specified, and preventing .../mylogfile-20241017.log.gz from matching. For clarity: this is an example of what will not work.

In short, it isn't possible to reduce the amount of data Athena scans based on the contents of each object without arranging them into an appropriate prefix/"folder" structure, which you mentioned not having. This limitation is stated explicitly in the red notice box on this documentation page: https://docs.aws.amazon.com/athena/latest/ug/tables-location-format.html

Athena reads all data stored in the Amazon S3 folder that you specify. If you have data that you do not want Athena to read, do not store that data in the same Amazon S3 folder as the data that you do want Athena to read.

Partitioning being prefix-based is also mentioned in the very first sentence of the documentation page on partitioning: https://docs.aws.amazon.com/athena/latest/ug/ctas-partitioning-and-bucketing-what-is-partitioning.html