Name: querying-aws-s3
Author: aws

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

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. 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: Look for DataExport.StorageLensTableDestination.IsEnabled: true . 2. Enable (if not configured) Enable S3 Metadata on a bucket: To also enable annotations (requires a service role): Enable Storage Lens S3 Tables export: Register S3 Tables federated catalog in Glue (required for Athena access): 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 S3 Metadata tables — namespace is b <bucket name : Table What it captures journal Event log — every CREATE, DELETE, UPDATE METADATA, and annotation events. Near real time. inventory Current state — one row per object (latest version). Updates within 1 hour. annotation Annotation payloads — text value column holds the full content. Near real time. Storage Lens tables — namespace is lens <config id exp : Table What it captures default storage metrics Per bucket/prefix: object count, size, storage class breakdown. Daily. default activity metrics Per bucket/prefix: GET/PUT/DELETE request counts. Daily. bucket property metrics Bucket config: versioning, encryption, lifecycle settings. Daily. 5. Query Query syntax: 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: Journal — audit who changed what: Journal — track annotation events: Inventory — find objects by storage class: Inventory — find objects by tag: Annotation — search across payloads: Annotation — extract JSON fields: Storage Lens — storage distribution: Routing: Athena vs Direct API Scenario Use Single known object + annotation name Direct API: get object annotation Aggregate/count across many objects Athena on annotation or inventory table Full text search across annotation payloads Athena with LIKE or json extract scalar Write/update an annotation Direct API: put object annotation (table is read only) Feature not configured on bucket Direct API loop ( list objects v2 + head object ); suggest enabling S3 Metadata Troubleshooting Error Cause Fix CATALOG NOT FOUND S3 Tables not registered in Glue Enable integration: S3 console Table buckets Enable integration Empty results from journal Feature just enabled; no events recorded yet Upload/delete an object and wait ~1 minute Empty results from inventory Table still BACKFILLING Check status; wait for ACTIVE (minutes to hours depending on object count) AccessDenied querying table Missing s3tables:GetTable or GetTableMetadataLocation See Security Considerations below Wrong namespace Bucket name has periods Periods are converted to underscores in namespace: my.bucket → b my bucket No Storage Lens data First delivery takes up to 48 hours Wait; no historical backfill Security Considerations Least Privilege IAM Policy Scope permissions to specific table bucket ARNs rather than using wildcards: Data Sensitivity Journal query results may contain sensitive fields: requester — AWS account ID or service principal that made the request source ip address — IP address of the requester Query results containing these fields should be stored in encrypted, access controlled locations. Avoid logging or sharing raw query output that contains IP addresses or principal identifiers. Encryption for Query Results Configure the Athena workgroup with EncryptionConfiguration to encrypt query results at rest: Audit Trail Enable CloudTrail logging for Athena ( StartQueryExecution , GetQueryResults ) and S3 Tables ( s3tables:GetTableData ) API calls to maintain an audit trail of who queried what metadata. Ensure CloudTrail logs are encrypted with SSE KMS and stored in a bucket with access logging enabled. Additional Resources [S3 Metadata overview](https://docs.aws.amazon.com/AmazonS3/latest/userguide/metadata tables overview.html) [Journal table schema](https://docs.aws.amazon.com/AmazonS3/latest/userguide/metadata tables schema.html) [Inventory table schema](https://docs.aws.amazon.com/AmazonS3/latest/userguide/metadata tables inventory schema.html) [Example metadata queries](https://docs.aws.amazon.com/AmazonS3/latest/userguide/metadata tables example queries.html) [S3 Annotations overview](https://docs.aws.amazon.com/AmazonS3/latest/userguide/annotations overview.html) [Storage Lens S3 Tables export](https://docs.aws.amazon.com/AmazonS3/latest/userguide/storage lens s3 tables naming.html) [Setting up permissions](https://docs.aws.amazon.com/AmazonS3/latest/userguide/metadata tables permissions.html) [Integrating S3 Tables with AWS analytics services](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3 tables integrating aws.html)