Querying Aws Redshift

Query AWS Redshift 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.

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

Supported Data Sources

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.

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:

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)

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

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.