[レポート]Amazon S3の各機能を使ったポイントインタイムリカバリの実現 (STG309-R) #AWSreInvent

WITH total_stats AS (
  SELECT COUNT(*) as total_objects FROM inventory
)
SELECT 
  storage_class,
  COUNT(*) as object_count,
  SUM(size) as total_bytes,
  ROUND(AVG(CAST(size AS DOUBLE)), 2) as avg_size,
  MIN(size) as min_size,
  MAX(size) as max_size,
  ROUND(SUM(size) / 1024.0 / 1024.0, 2) as total_mb,
  ROUND(AVG(CAST(size AS DOUBLE)) / 1024.0, 2) as avg_kb,
  ROUND(100.0 * COUNT(*) / (SELECT total_objects FROM total_stats), 2) as percentage_of_objects
FROM inventory
WHERE size >= 0
GROUP BY storage_class
ORDER BY total_bytes DESC;

$ cat > ~/.kiro/settings/mcp.json <<EOF
{
"mcpServers": {
   "awslabs.s3-tables-mcp-server": {
      "command": "uvx",
      "args": ["awslabs.s3-tables-mcp-server@latest"]
      }
   }
}
EOF

/context add /workshop/amazonq/s3-tables.md
/context add /workshop/amazonq/s3-metadata.md

Analyze inventory table by storage class showing object count, total bytes, average/min/max sizes, total capacity in MB, average size in KB, and percentage of total objects, ordered by total bytes descending.

| Storage Class | Object Count | Total Bytes | Avg Size (bytes) | Min Size (bytes) | Max Size (bytes) | Total MB | Avg Size KB | % of Total |
|---------------|--------------|-------------|------------------|------------------|------------------|----------|-------------|------------|
| INTELLIGENT_TIERING | 18,889 | 3,009,765,711 | 159,339.60 | 10,249 | 307,183 | 2,870.34 | 155.61 | 45.26% |
| STANDARD | 22,841 | 2,977,604,281 | 130,362.26 | 0 | 307,197 | 2,839.66 | 127.31 | 54.74% |

WITH inventory_time_cte AS (
    SELECT COALESCE(inventory_time_from_property, inventory_time_default) AS inventory_time
    FROM (
        SELECT *
        FROM (VALUES (TIMESTAMP '2024-12-01 00:00')) AS T(inventory_time_default)
        LEFT JOIN (
            SELECT from_unixtime(CAST(value AS BIGINT) / 1000.0) AS inventory_time_from_property
            FROM "journal$properties"
            WHERE key = 'aws.s3metadata.oldest-uncoalesced-record-timestamp'
            LIMIT 1
        ) ON TRUE
    )
),
working_set AS (
    -- Extract only the list of objects created before a specific point in time from the Live inventory table
    SELECT
        bucket,
        key,
        sequence_number,
        version_id,
        is_delete_marker,
        last_modified_date,
        FALSE AS _is_perm_delete
    FROM inventory i
    WHERE i.last_modified_date <= TIMESTAMP 'CHANGE'

    UNION ALL

    -- Extract change history information from the Journal table
    SELECT
        bucket,
        key,
        sequence_number,
        version_id,
        is_delete_marker,
        COALESCE(last_modified_date, record_timestamp) AS last_modified_date,
        (record_type = 'DELETE' AND NOT COALESCE(is_delete_marker, FALSE)) AS _is_perm_delete
    FROM journal j
    CROSS JOIN inventory_time_cte t
    WHERE j.last_modified_date > (t.inventory_time - interval '15' minute)
        AND j.last_modified_date <= TIMESTAMP 'CHANGE'
),

-- Sort by last_modified_date / sequence_number for each object Key, then extract only the most recent record
-- If the latest record is a Delete operation (Permanent delete or delete marker), consider the object as non-existent at that point in time and exclude it from the list.

current_objects AS (
    SELECT *
    FROM (
        SELECT *,
            ROW_NUMBER() OVER (
                PARTITION BY key
                ORDER BY last_modified_date DESC, sequence_number DESC
            ) AS latest_rn
        FROM working_set
    )
    WHERE latest_rn = 1
        AND is_delete_marker = false
)    
    SELECT
        bucket,
        key,
        version_id
    FROM current_objects 
    ORDER BY key

Check if metadata is configured for general purpose buckets that start with 's3-timemachine-backup', and if so, tell me which tables there are.

What schema information is available for journal table?

List the creation history of the 'yellow3.png' object

Find the oldest version of 'yellow3.png' and copy it to the bucket starting with 's3-timemachine-restore-03'