Querying Aws S3

Query AWS S3 System Tables

Overview

Works best with the AWS MCP server 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

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 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

4. Identify the Target Table

S3 Metadata tables — namespace is b_<bucket-name>:

Storage Lens tables — namespace is lens_<config-id>_exp:

5. Query

Query syntax:

"s3tablescatalog/aws-s3"."<namespace>"."<table>"

Constraints:

• You MUST confirm workgroup and output location before executing
• You MUST ensure the Athena workgroup enforces SSE-KMS encryption on query results
• You MUST warn user that tables are read-only — no INSERT/UPDATE/DELETE
• You SHOULD use the key columns documented in this skill to build queries. If you need the full schema (e.g., AWS has added new columns), run get-tables once on any single namespace — schemas are identical across all instances of the same table type:

  aws glue get-tables --catalog-id "<ACCOUNT>:s3tablescatalog/aws-s3" --database-name "<namespace>" --region <REGION>

Journal — audit who changed what:

SELECT key, record_type, record_timestamp, requester, source_ip_address
FROM "s3tablescatalog/aws-s3"."b_<bucket>"."journal"
WHERE record_type = 'DELETE'
  AND record_timestamp > current_timestamp - interval '24' hour
ORDER BY record_timestamp DESC;

Journal — track annotation events:

SELECT key, record_type, annotation.name, record_timestamp
FROM "s3tablescatalog/aws-s3"."b_<bucket>"."journal"
WHERE record_type IN ('CREATE_ANNOTATION', 'DELETE_ANNOTATION', 'UPDATE_ANNOTATION_METADATA')
ORDER BY record_timestamp DESC LIMIT 20;

Inventory — find objects by storage class:

SELECT key, size, storage_class, last_modified_date
FROM "s3tablescatalog/aws-s3"."b_<bucket>"."inventory"
WHERE storage_class = 'GLACIER'
ORDER BY size DESC LIMIT 50;

Inventory — find objects by tag:

SELECT key, size, object_tags
FROM "s3tablescatalog/aws-s3"."b_<bucket>"."inventory"
WHERE object_tags['environment'] = 'staging';

Annotation — search across payloads:

SELECT object_key, name, text_value
FROM "s3tablescatalog/aws-s3"."b_<bucket>"."annotation"
WHERE text_value LIKE '%error%';

Annotation — extract JSON fields:

SELECT object_key, json_extract_scalar(text_value, '$.status') as status