Query the size of files per year of an S3 using Athena

I think you have differences from your code output and actual most likely because your code is not catering for versioning of objects. That is, it's calculating storage based on the most recent version. You can try the following code to calculate total storage including versioning.

def get_bucket_size_per_year_including_versions(bucket_name):
    s3 = session.client('s3')
    paginator = s3.get_paginator('list_object_versions')

    size_per_year = defaultdict(int)

    for page in paginator.paginate(Bucket=bucket_name):
        versions = page.get('Versions', [])
        for obj in versions:
            year = obj['LastModified'].year
            size_per_year[year] += obj['Size']

    return size_per_year

Also, I'm wondering why you calculating storage in this way instead of either checking metrics in cloudwatch or taking advantage of S3 inventory. With S3 inventory, you can get a csv file listing all the objects including versioning, filename, date, and bytes. You can then use that csv and code it to get the required data.

Note, with your current approach, you are most likely incurring HEAD request charges. With 460TB of data and therefore there are many files, the HEAD request charges could be high, where inventory charges would be lower.

To query the size of files per year in an S3 bucket using Athena, you can use S3 Inventory reports combined with Athena queries. Here's a step-by-step approach to achieve this:

1. First, set up S3 Inventory for your bucket. This will generate daily or weekly reports of your objects and their metadata.

2. Once you have S3 Inventory configured, you can use Athena to query these inventory reports.

3. Create an external table in Athena that points to your S3 Inventory data. The exact schema will depend on the fields you've included in your inventory report, but it should include at least the object size and last modified date.

4. After creating the table, you can run a query like this to get the total size of files per year:

SELECT 
    YEAR(from_iso8601_timestamp(last_modified)) AS year,
    SUM(size) AS total_size_bytes
FROM 
    your_inventory_table
GROUP BY 
    YEAR(from_iso8601_timestamp(last_modified))
ORDER BY 
    year

This query assumes your inventory table has columns named 'last_modified' and 'size'. Adjust these column names if they're different in your inventory setup.

To convert the result to terabytes, you can modify the query like this:

SELECT 
    YEAR(from_iso8601_timestamp(last_modified)) AS year,
    SUM(size) / (1024.0 * 1024 * 1024 * 1024) AS total_size_tb
FROM 
    your_inventory_table
GROUP BY 
    YEAR(from_iso8601_timestamp(last_modified))
ORDER BY 
    year

This approach using S3 Inventory and Athena should give you accurate results and work well even for large buckets. It avoids the issues you encountered with the boto3 script and the problems with file paths in your previous Athena query.

Regarding the double slash issue you encountered, this is likely due to how the S3 paths are being interpreted. Using S3 Inventory as suggested above should avoid this problem entirely, as the inventory reports will contain the correct object information without you needing to construct the paths manually.
Sources
Calculate Amazon S3 bucket size and number of objects | AWS re:Post
Unlocking efficient data lifecycle management for Amazon S3 with last access times | AWS re:Post