/querying-aws-redshift

name: querying-aws-redshift
description: >-
  Enables Redshift system-table (SYS_*) log publishing to S3 Tables in Apache
  Iceberg format for both Provisioned clusters and Serverless namespaces,
  verifies publishing status, and queries the published logs via any
  Iceberg-compatible engine including Redshift and Athena. Covers system tables
  such as sys_query_history, sys_query_text, sys_connection_log,
  sys_query_detail, and sys_session_history. Applies when turning on S3 Tables
  log publishing for a cluster or namespace, confirming publishing status and
  locating the S3 Tables namespace, querying non-realtime data from Redshift
  system tables off-cluster at scale, or building dashboards for Redshift
  monitoring and auditing, especially for historical or high-volume system-table data
  beyond the in-cluster SYS_ view retention window. Trigger phrases: publish
  redshift system table log to s3 tables, enable-logging s3 tables, describe
  redshift logging status, query redshift system tables in athena or redshift,
  redshift log exports to iceberg.
version: 1
argument-hint: "['enable CLUSTER'|'status CLUSTER'|'query SQL'|'configure']"

Query AWS Redshift System Tables

Overview

**Works best with** the [AWS MCP server](https://docs.aws.amazon.com/aws-mcp/) 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.

Redshift can publish **system tables** — the `SYS_*` monitoring data such as `sys_query_history`, `sys_query_detail`, and `sys_connection_log` — to **S3 Tables** as continuously-updated Apache Iceberg tables.

Terminology used throughout: **system table** refers to a `SYS_*` dataset generally, and each one maps 1:1 to a published Iceberg table. Where this skill says **`SYS_` view**, it means specifically the live in-cluster object you query on the cluster itself — that is a view, and it is a different thing from the published S3 Tables copy. This applies to both **Provisioned clusters** and **Serverless namespaces**. It is an opt-in extension of the existing logging APIs. Published tables are read-only, stored in the AWS-managed `aws-redshift` table bucket, and queryable via any Iceberg-compatible engine including Amazon Athena and Amazon Redshift itself.

Querying the S3 Tables copy is preferred over the live in-cluster `SYS_` views when analyzing historical or high-volume system-table data because:

• The in-cluster `SYS_` views have a limited retention window; S3 Tables retains history well beyond it.
• Querying S3 Tables adds **no load** to the running Redshift cluster.
• The logs are Iceberg tables, so they can be queried at scale from any Iceberg-compatible engine and joined with other lake data.

Decision Tree

| User intent | Use this skill? | Alternative | |---|---|---| | Turn on S3 Tables log publishing for a cluster or namespace | **Yes** | — | | Confirm a cluster/namespace is publishing / find its S3 Tables namespace | **Yes** | — | | Querying non-realtime data from Redshift system tables | **Yes** | — | | Build daily/weekly/monthly dashboard for Redshift monitoring and auditing | **Yes** | — | | Selectively stop S3 Tables publishing | **Yes** | — | | Query published system tables from Redshift (cross-database) | **Yes** | — | | Query published system tables from Athena | **Yes** | — | | Inspect the *current, real-time* `SYS_` state on a live cluster | **No** | Query the `SYS_` view on the cluster directly | | Query data *inside* customer tables | **No** | Direct Redshift SQL on the cluster |

Supported Data Sources

| Compute type | Enable / disable API | Status API | Granularity options | |---|---|---|---| | Redshift Provisioned cluster | `redshift enable-logging` / `redshift disable-logging` | `redshift describe-logging-status` | `cluster` (default), `account` | | Redshift Serverless namespace | `redshift-serverless update-namespace` with `--s3-table-action Enable`/`Disable` | `redshift-serverless get-namespace` | `namespace` (default), `account` |

Both compute types publish into the same AWS-managed `aws-redshift` table bucket and are queried identically once published. They differ only in the enable/disable API surface and in the casing of the status response — see the flag and field tables in [Common Tasks](#common-tasks).

Not covered by this skill: Redshift audit logs delivered to S3 or CloudWatch (`useractivitylog`, `userlog`, `connectionlog`), which use the separate `--log-exports` mechanism on Serverless and are not `SYS_*` system tables.

Common Tasks

1. Check If Configured

Before querying, confirm the cluster or namespace is publishing to S3 Tables.

# Provisioned
aws redshift describe-logging-status --region <REGION> --cluster-identifier <CLUSTER_ID>
# Serverless
aws redshift-serverless get-namespace --region <REGION> --namespace-name <NAMESPACE_NAME>

**Interpret the response.** The two compute types return the *same* information under **different field names and casing** — Provisioned uses PascalCase under `S3Tables`, Serverless uses camelCase under `namespace.s3TablePublishStatus`:

| Meaning | Provisioned (`describe-logging-status`) | Serverless (`get-namespace`) | |---|---|---| | Not enabled | `LoggingEnabled: false` or no `S3Tables` block | no `s3TablePublishStatus` block | | Destination includes S3 Tables | `LogDestinationType` contains `s3table` | `logDestinationType` contains `s3table` | | List of published `SYS_*` tables | `S3Tables.S3Tables` | `namespace.s3TablePublishStatus.s3Tables` | | **The exact S3 Tables namespace** (required for querying) | `S3Tables.S3TableNamespace` | `namespace.s3TablePublishStatus.s3TableNamespace` | | Granularity | `S3Tables.S3TableGranularity` (`cluster`/`account`) | `namespace.s3TablePublishStatus.s3TableGranularity` (`namespace`/`account`) | | Per-table last ingest time | `S3Tables.LastIngestionTimes` | `namespace.