Query Amazon Athena 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 Athena system tables are Apache Iceberg tables that Athena automatically creates and populates with account usage data. The query_executions system table records one row per completed query with the metadata from GetQueryExecution plus additional fields that GetQueryExecution does not return. The tables are read-only and stored as fully managed Amazon S3 Tables in the service-managed aws-athena S3 table bucket in your account and Region, and are queryable with Athena SQL (the focus of this skill) or any other Iceberg-compatible engine, such as Amazon Redshift.

The system table is preferred over the raw Athena APIs (ListQueryExecutions, GetQueryExecution) because:

• ListQueryExecutions returns only query IDs and paginates; GetQueryExecution must then be called once per ID. Reconstructing "how much did each user scan last month" means thousands of sequential API calls. The system table answers it with one GROUP BY.
• The APIs offer no SQL, no aggregation, and no joins, and cover only a rolling 45-day window. The system table retains data for as long as you configure through S3 Tables record expiration and supports arbitrary SQL, including window functions and cross-table joins.
• principal_arn (the IAM user or role that ran the query) is not included in the GetQueryExecution response — obtaining it otherwise requires a CloudTrail join. It is a first-class column here.
• tables_accessed (the tables and views referenced by each query) is likewise not included in the GetQueryExecution response, enabling lineage and "who queried what table" analysis directly.

Decision Tree