skills.rest
aws
awsOFFICIAL
Explore all skills by this author →
WebsiteGitHub

querying-aws-redshift

Publish Redshift SYS_* system tables to S3 Tables and query them via Athena or Redshift.

2.5k|282|Updated Apr 23, 2026
Search Tags:#Iceberg#Athena#redshift#system tables#Monitoring#S3 Tables#log publishing
One-click install
npx skills add https://github.com/aws/agent-toolkit-for-aws --skill querying-aws-redshift
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: querying-aws-redshift
Source: https://github.com/aws/agent-toolkit-for-aws/tree/main/skills/specialized-skills/system-table-skills/querying-aws-redshift
Command: npx skills add https://github.com/aws/agent-toolkit-for-aws --skill querying-aws-redshift

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve?

Redshift's in-cluster SYS_* monitoring views have limited retention and querying them adds load to the running cluster, making historical or high-volume system-table analysis impractical. This Skill enables publishing those system tables to S3 Tables as Apache Iceberg tables and querying them off-cluster at scale.

Core Features & Use Cases

  • Enable and verify log publishing: Turn on S3 Tables log publishing for Provisioned clusters or Serverless namespaces, confirm publishing status, and resolve the exact S3 Tables namespace from the API.
  • Query published system tables: Run SQL against sys_query_history, sys_query_text, sys_connection_log, and other SYS_* tables from Athena, Redshift auto-mounted catalogs, or external schemas.
  • Security and permissions setup: Apply least-privilege IAM policies, dual-principal KMS key policies, Lake Formation registration, and audit alarms for sensitive query_text data.
  • Use Case: Build a weekly dashboard of the longest-running queries and failed authentication attempts across all Redshift clusters in an account, without touching the live clusters.

Quick Start

Ask the agent to enable S3 Tables log publishing for your Redshift cluster and show the ten longest-running queries from the past week using Athena.

Frequently Asked Questions about querying-aws-redshift

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I publish Redshift system tables to S3 Tables?▼

For Provisioned clusters run aws redshift enable-logging with --log-destination-type s3table and --log-exports listing the SYS_* tables. For Serverless namespaces use aws redshift-serverless update-namespace with --s3-table-names and --s3-table-action Enable. Pass --s3-table-kms-key-id for customer-managed encryption.

How do I query Redshift system tables in Athena?▼

Query using the three-part name "s3tablescatalog/aws-redshift"."<NAMESPACE>"."<SYS_TABLE>" after registering the S3 Tables catalog in Glue. Always read the namespace from describe-logging-status or get-namespace rather than constructing it manually, and confirm your workgroup has an encrypted output location.

What is the difference between Provisioned and Serverless flags for S3 Tables logging?▼

Provisioned uses enable-logging/disable-logging with --log-exports and cluster or account granularity. Serverless uses update-namespace with --s3-table-names, --s3-table-action Enable|Disable, and namespace or account granularity. Using Provisioned flag names on Serverless causes an Unknown options error.

Why is my Athena query against published Redshift tables returning empty results?▼

Empty results usually mean ingestion is still in flight or the table was not included when enabling publishing. Check LastIngestionTimes in the status response, verify the table appears in the published list, and confirm you are using the namespace returned by the API.

When should I query the live SYS_ views instead of S3 Tables?▼

Query the live in-cluster SYS_ views when you need real-time current cluster state, since published tables only contain events recorded after publishing was enabled. Use S3 Tables for historical or high-volume analysis because it adds no load to the cluster and retains data beyond the in-cluster retention window.

Why does the Redshift auto-mounted catalog not appear in pg_database?▼

Auto-mount is a poll that can take up to 300 seconds, so wait five minutes or reboot the cluster to force discovery. If it still does not appear, verify the Lake Formation resource registration and that both Redshift service-linked roles are listed as ReadOnlyAdmins.

Related Skills

Explore More Data & Analytics Skills→
darellchua2darellchua2
4

opentofu-aws-explorer

Provision and manage AWS infrastructure as code using OpenTofu/Terraform.

Community
Advanced
CENKSSSCENKSSS

test-metrics-dashboard

Analyzes Jest test execution data to produce quality trend dashboards and top failing tests.

Community
Advanced
tomztomz

aws-monitoring

Centralizes AWS monitoring guidance for CloudWatch, X-Ray, and alerting.

Community
Advanced
AndreKuraitAndreKurait

querying-aws-cloudwatch

Run SQL queries on CloudWatch Logs exported as Iceberg tables.

Community
Advanced