/querying-aws-s3

name: querying-aws-s3
description: >-
  Queries S3 object metadata, tracks bucket activity, audits object changes, searches
  annotations, and analyzes storage metrics using S3 Metadata system tables (journal,
  inventory, annotation) and S3 Storage Lens tables via Athena SQL. Applies when counting
  objects, finding recent uploads or deletions, identifying who wrote to a prefix, breaking
  down storage classes, finding objects by tag, searching annotation content, analyzing
  storage lens metrics, or enabling S3 Metadata tracking. Prefers system tables over raw S3
  APIs (list-objects-v2, head-object) at scale. Trigger phrases: bucket activity, object
  count, who uploaded, track deletions, storage class breakdown, find by tag, search
  annotations, storage lens metrics, audit bucket changes.
version: 1
argument-hint: "[bucket-name|query|'configure BUCKET'|'status BUCKET']"

Query AWS S3 System Tables

Overview

**Works best with** the [AWS MCP server](https://docs.aws.amazon.com/agent-toolkit/latest/userguide/getting-started-aws-mcp-server.html) for sandboxed execution and audit logging. All commands below use the AWS CLI and work in any environment with configured AWS credentials. Use IAM roles or temporary credentials; avoid long-lived access keys.

Amazon S3 Metadata provides continuously-updated Apache Iceberg tables that capture object-level metadata for general-purpose buckets. S3 Storage Lens exports aggregated storage and activity metrics as Iceberg tables. Both are read-only, stored in the AWS-managed `aws-s3` table bucket, and queryable via Amazon Athena.

System tables are preferred over raw S3 APIs (`list-objects-v2`, `head-object`) because:

• `list-objects-v2` paginates at 1000 objects/page — inefficient for large buckets (millions or billions of objects). The inventory table answers `SELECT COUNT(*)` in seconds at any scale.
• `list-objects-v2` cannot identify who uploaded an object, from which IP, or when something was deleted. Only the journal table has `requester`, `source_ip_address`, and delete event tracking.
• Filtering by tag requires `get-object-tagging` per object. The inventory table has `object_tags` as a queryable map column.

Decision Tree

| User intent | Use this skill? | Table | Alternative | |---|---|---|---| | How many objects in my bucket | **Yes** | inventory | — | | What was recently uploaded/deleted | **Yes** | journal | — | | Who wrote/deleted objects (audit) | **Yes** | journal (requester, source_ip) | — | | Storage class breakdown | **Yes** | inventory | — | | Find objects by tag or user metadata | **Yes** | inventory | — | | Search annotation content | **Yes** | annotation | Single object → direct API `get-object-annotation` | | Write/update an annotation | **No** | — | Direct API: `put-object-annotation` (tables are read-only) | | Query data *inside* objects | **No** | — | `querying-data-lake` | | Bucket-level storage metrics/trends | **Yes** | Storage Lens tables | — | | Enable metadata tracking | **Yes** | see Enable section | — |

Common Tasks

1. Check If Configured

Before querying, confirm S3 Metadata is enabled on the target bucket.

aws s3api get-bucket-metadata-configuration --bucket <BUCKET> --region <REGION>

**Interpret the response:**

• `MetadataConfigurationNotFound` error → not enabled. See Enable section below.
• `TableStatus: ACTIVE` → ready to query.
• `TableStatus: BACKFILLING` → queryable but inventory may be incomplete.
• `TableStatus: FAILED` → check error field (usually IAM).

**For Storage Lens:**

aws s3control get-storage-lens-configuration --account-id <ACCOUNT> --config-id <CONFIG_ID> --region <REGION>

Look for `DataExport.StorageLensTableDestination.IsEnabled: true`.

2. Enable (if not configured)

**Enable S3 Metadata on a bucket:**

aws s3api create-bucket-metadata-configuration \
  --bucket <BUCKET> \
  --region <REGION> \
  --metadata-configuration '{
    "JournalTableConfiguration": {"RecordExpiration": {"Expiration": "DISABLED"}},
    "InventoryTableConfiguration": {"ConfigurationState": "ENABLED"}
  }'

To also enable annotations (requires a service role):

aws s3api create-bucket-metadata-configuration \
  --bucket <BUCKET> \
  --region <REGION> \
  --metadata-configuration '{
    "JournalTableConfiguration": {"RecordExpiration": {"Expiration": "ENABLED", "Days": 90}},
    "InventoryTableConfiguration": {"ConfigurationState": "ENABLED"},
    "AnnotationTableConfiguration": {"ConfigurationState": "ENABLED", "Role": "<ROLE_ARN>"}
  }'

**Enable Storage Lens S3 Tables export:**

aws s3control put-storage-lens-configuration \
  --account-id <ACCOUNT> \
  --config-id <CONFIG_ID> \
  --region <REGION> \
  --storage-lens-configuration '{
    "Id": "<CONFIG_ID>",
    "IsEnabled": true,
    "AccountLevel": {"BucketLevel": {}},
    "DataExport": {
      "StorageLensTableDestination": {"IsEnabled": true}
    }
  }'

**Register S3 Tables federated catalog in Glue** (required for Athena access):

aws glue create-catalog --region <REGION> --cli-input-json '{
  "Name": "s3tablescatalog",
  "CatalogInput": {
    "FederatedCatalog": {
      "Identifier": "arn:aws:s3tables:<REGION>:<ACCOUNT>:bucket/*",
      "ConnectionName": "aws:s3tables"
    }
  }
}'

For setup permissions and IAM role requirements, see [Security Considerations](#security-considerations) below.

3. Verify Permissions

Querying requires:

• Athena execution permissions
• S3 Tables read permissions (see least-privilege policy in Security Considerations)
• The S3 Tables federated catalog registered in Glue (`s3tablescatalog`)
• Athena workgroup with SSE-KMS encryption configured on the output location

If `CATALOG_NOT_FOUND` errors occur, the Glue integration may not be enabled. See: [Integrating S3 Tables with AWS analytics services](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-aws.html)

4. Identify the Target Table

*