/redshift-api

name: redshift-api
description: Run SQL against Amazon Redshift — submit statements, poll status, page through results, and browse databases/schemas/tables. Use this whenever the user wants to query Redshift (provisioned cluster or Serverless), ask "what tables are in this schema", check a query's status, or mentions `redshift-data`, a Redshift cluster identifier / workgroup name, or a `redshift-data.{region}.amazonaws.com` endpoint. Always start from this skill when interacting with this service — its bundled scripts and recipes are the fastest path.

In the Amazon Redshift Data API, every call is a `POST` to `https://redshift-data.<region>.amazonaws.com/` with `Content-Type: application/x-amz-json-1.1` and `X-Amz-Target: RedshiftData.<Action>` — there are no REST-style paths. The API is fully asynchronous: submit a statement, get back an `Id`, poll `DescribeStatement` until done, then page results with `GetStatementResult` — `scripts/rs_query.sh` (operation 1) drives that loop for you. The same calls work against provisioned clusters and Serverless; only the connection-target field differs.

Request setup

Authentication is handled by the runtime — requests to this API are signed with credentials configured for the workspace, so there is nothing to set up. Do not try to obtain AWS keys or sign requests yourself. A persistent `AccessDeniedException` means the credential isn't configured for this workspace — report that instead of debugging auth.

Two pieces of configuration are real and required:

**1. Region** — it's part of the endpoint hostname:

export AWS_DEFAULT_REGION="us-east-1"        # the region your cluster / workgroup is in

**2. Connection target** — which cluster/workgroup and database the Data API connects to. Pick one and put the matching JSON fields in `RS_TARGET`; the recipes below merge it into each request body:

• **Serverless** — pass `WorkgroupName`. The configured identity is mapped to a database user.

Simplest.

• **Provisioned, temporary credentials** — pass `ClusterIdentifier` and `DbUser`. The Data API

resolves database credentials under the hood.

• **Provisioned or Serverless, Secrets Manager** — pass `SecretArn` pointing to a secret that holds

the database login.

Set whichever applies once:

export RS_DATABASE="dev"
# Serverless:
export RS_TARGET='{"WorkgroupName": "my-workgroup"}'
# — or provisioned + temp creds:
# export RS_TARGET='{"ClusterIdentifier": "my-cluster", "DbUser": "my_user"}'
# — or either + Secrets Manager:
# export RS_TARGET='{"ClusterIdentifier": "my-cluster", "SecretArn": "arn:aws:secretsmanager:us-east-1:123456789012:secret:rs-creds-AbCdEf"}'

**The helper used below.** One function wraps the endpoint, the headers, and the action name. The `Authorization` header is a placeholder — the runtime replaces it with a real signature.

rsapi() {
  local action="$1"
  curl -sS "https://redshift-data.${AWS_DEFAULT_REGION}.amazonaws.com/" \
    -H "Content-Type: application/x-amz-json-1.1" \
    -H "X-Amz-Target: RedshiftData.${action}" \
    -H "Authorization: placeholder" \
    -d "${2:?rsapi needs a JSON body as the second argument}"
}

**Sanity check** — list databases. A `200` with a `Databases` array confirms the workspace is wired up and the connection target is right. An error names which layer failed.

rsapi ListDatabases "$(jq -n --argjson t "$RS_TARGET" --arg db "$RS_DATABASE" \
  '$t + {Database: $db}')" | jq .

Core operations

Every action that connects to the database takes the same merged target body — `$t + {Database: $db, ...}`. Errors come back as `{"__type": "<Exception>", "message": "..."}` instead of the expected shape; on any call, an absent top-level field (`Id`, `Status`, `Records`, …) means you got the error envelope — print it and stop.

1. Run a query (`scripts/rs_query.sh`)

Run SQL through the bundled script (path is relative to this skill's directory): it submits with `ExecuteStatement`, polls `DescribeStatement` to a terminal state, pages `GetStatementResult` on `NextToken`, and decodes the typed one-key cell objects.

scripts/rs_query.sh \
  'SELECT event_name, COUNT(*) AS n FROM public.events
   WHERE event_date >= :start GROUP BY 1 ORDER BY 2 DESC LIMIT 20' \
  --param start=2024-01-01 --name top-events

• SQL is one argument (or stdin). Instance specifics come from `AWS_DEFAULT_REGION` / `RS_TARGET` /

`RS_DATABASE` above; `--database` overrides the database.

• `--param NAME=VALUE` (repeatable) binds `:NAME` placeholders — values are always strings; the

database casts them. `--name` sets `StatementName` so the query shows up in `ListStatements`.

• `--max-rows N` caps fetched rows (default 10000, `0` = everything); `--json` emits one JSON object

per row instead of TSV with a header. Statement id, status, duration, and row counts go to stderr.

• Exit codes: `0` success, `1` submit failed or SQL `FAILED`/`ABORTED` (the API's own message on

stderr), `2` gave up waiting after `--max-wait` seconds (default 600) — the statement id is on stderr; resume it with operation 2.

If the script errors, read it — it's plain `curl` + `jq` — and debug against `references/api.md`. For `BatchExecuteStatement` and sub-statement results, session reuse (`SessionKeepAliveSeconds` / `SessionId`), `ResultFormat: CSV`, cancelling, or catalog browsing, use the actions below.

2. Resume a statement by id (`DescribeStatement` → `GetStatementResult`)

When the script exits 2 (timeout) or you have a statement id from elsewhere, poll and fetch directly:

rsapi DescribeStatement "$(jq -n --arg id "$ID" '{Id: $id}')" \
  | jq '{Status, Duration, ResultRows, HasResultSet, Error}'
rsapi GetStatementResult "$(jq -n --arg id "$ID" '{Id: $id}')" \
  | jq -r '.Records[]? | [.[] | if .isNull then null else to_entries[0].value end] | @tsv'

• `Status` ∈ `SUBMITTED`, `PICKED`, `STARTED`, `FINISHED`, `FAILED`, `ABORTED`. Only `FINISHED`

with `HasR