redshift-api

Files (3)

• SKILL.md
• api.md · references/
• rs_query.sh · scripts/

SKILL.md (first lines)

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:

```bash
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:

```bash
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.

```bash
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.

```bash
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.

```bash
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:

```bash
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 `HasResultSet: true` has rows; `FAILED` carries the SQL error in `.Error`. `Duration` is
  **nanoseconds**.
- Result cells are typed one-key objects (see the gotcha under Error handling); column names/types
  are in `.ColumnMetadata` on the first page. Further pages follow `NextToken` — no page-size knob;
  `DescribeStatement.ResultRows` is the total up front.

From thimslugga/agent-plugins, MIT.

More from thimslugga/agent-plugins