querying-aws-redshift

Files (4)

• SKILL.md
• example-queries.md · references/
• permissions-setup.md · references/
• security.md · references/

SKILL.md (first lines)

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

```bash
# 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.s3TablePublishStatus.lastIngestionTimes` |
| All available system tables published | `S3Tables.EnabledAll` | `namespace.s3TablePublishStatus.enabledAll` |

Notes:

- `LogDestinationType` is a **comma-joined list** when more than one destination is active — e.g. `"cloudwatch,s3table"`. Test with a substring/contains check, not equality against `s3table`.
- An empty `LastIngestionTimes` / `lastIngestionTimes` map, or a table listed as published but absent from the map, means data for that table may still be in flight. Compare successive values to confirm new data is landing.
- On Serverless, do **not** read the top-level `logExports` field for this feature — that field carries the CloudWatch/S3 audit logs (`useractivitylog`, `userlog`, `connectionlog`) and is unrelated to `SYS_*` S3 Tables publishing.

### 2. Enable (if not configured)

```bash
# Provisioned
aws redshift enable-logging --region <REGION> --cluster-identifier <CLUSTER_ID> --log-destination-type s3table --log-exports <SYS_TABLE>... --s3-table-granularity <cluster|account> --s3-table-kms-key-id <KMS_KEY_ARN>
# Serverless
aws redshift-serverless update-namespace --region <REGION> --namespace-name <NAMESPACE_NAME> --log-destination-type s3table --s3-table-names <SYS_TABLE>... --s3-table-action Enable --s3-table-granularity <namespace|account> --s3-table-kms-key-id <KMS_KEY_ARN>
```

`--s3-table-kms-key-id` is part of both commands deliberately, not an optional add-on. Omitting it does not fail — the tables fall back to an AWS-owned key you cannot audit, restrict by policy, or revoke. Because `SYS_*` tables carry `query_text`, `user_name`, and `remote_host`, treat the customer-managed key as the default and drop the flag only for throwaway environments.

Enable from AWS console Amazon Redshift Console > Clusters > select your cluster > Tabs > Integrations / System table integration

**The two compute types take different flags for the same feature.** Do not carry Provisioned flag names over to Serverless:

| Purpose | Provisioned (`enable-logging`) | Serverless (`update-namespace`) |
|---|---|---|
| Which system tables to publish | `--log-exports` | `--s3-table-names` |
| Enable vs disable | separate `enable-logging` / `disable-logging` operations | `--s3-table-action Enable` \| `Disable` |
| Granularity | `--s3-table-granularity` `cluster` \| `account` | `--s3-table-granularity` `namespace` \| `account` |
| Customer-managed KMS key | `--s3-table-kms-key-id` | `--s3-table-kms-key-id` |
| Validate without applying | `--dry-run` | `--dry-run` |

Notes:

- Granularity: Provisioned supports `cluster` (default) or `account`; Serverless supports `namespace` (default) or `account`.
- `cluster`/`namespace` granularity → one S3 table per cluster/namespace; `account` → one shared table for all clusters/namespaces per account per region.
- Use `all` to publish all available `SYS_*` tables — `--log-exports all` on Provisioned, `--s3-table-names all` on Serverless.
- **Encryption at rest is strongly recommended for production.** Without `--s3-table-kms-key-id` the published tables are encrypted with an AWS-owned key, which you cannot audit, restrict by policy, or revoke. `SYS_*` tables carry `query_text`, `user_name`, and `remote_host` (see [Security Considerations](#security-considerations)), so pass a customer-managed key. Grant key access using the complete key policy in `${SKILL_DIR}/references/security.md` rather than an abbreviated action list — it needs **two** service principals (`systemtables.redshift.amazonaws.com` for publishing and `maintenance.s3tables.amazonaws.com` for table maintenance/compaction). Provisioning only the publishing principal lets writes succeed while compaction silently fails.
- Both operations accept `--dry-run` to validate the request without changing anything. Provisioned returns a `DryRunOperation` error on success ("Request would have succeeded, but DryRun flag is set"); Serverless returns an empty body and exit code 0. Note that the Serverless dry-run validates request *shape* only, not parameter values, so a successful dry-run there does not guarantee the values are accepted.

**Disable selectively:**

```bash
# Provisioned
aws redshift disable-logging --region <REGION> --cluster-identifier <CLUSTER_ID> --log-destination-type s3table --log-exports <SYS_TABLE>...
# Serverless
aws redshift-serverless update-namespace --region <REGION> --namespace-name <NAMESPACE_NAME> --log-destination-type s3table --s3-table-names <SYS_TABLE>... --s3-table-action Disable
```

### 3. Verify Permissions

Full setup commands for both paths: **`${SKILL_DIR}/references/permissions-setup.md`**. Load it before creating roles or registering resources.

**Athena path** — needs the `s3tablescatalog/aws-redshift` catalog registered in Glue, a workgroup with an output location, and S3 Tables read permissions. Confirm the catalog is queryable:

```bash

From aws/agent-toolkit-for-aws, Apache-2.0.

More from aws/agent-toolkit-for-aws