redshift-guide

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Amazon Redshift Guide

Amazon Redshift 指南

Redshift is NOT PostgreSQL (read first)

Redshift 并非 PostgreSQL（请先阅读）

Redshift speaks PostgreSQL's wire protocol and shares much of its surface syntax, so LLMs assume PostgreSQL behavior carries over — it frequently does not. Divergences span system tables (

pg_catalog

is incomplete), DDL (no indexes, no sequences), functions (

string_agg

SUBSTR

on tables, leader-node-only functions), types (a

text

column becomes VARCHAR(256)), and comparison semantics (trailing blanks, unenforced constraints). Assume divergence and verify against the reference below — do not answer from PostgreSQL habit. Common PostgreSQL→Redshift divergences are in

references/redshift-sql-syntax.md

Works best with the AWS MCP server — it runs the AWS CLI and Redshift Data API calls below in a sandboxed, audit-logged environment. All guidance here is plain AWS CLI and SQL and works without it.

Redshift 支持PostgreSQL的有线协议，并且在表面语法上有很多相似之处，因此LLMs会默认PostgreSQL的行为可以直接迁移到Redshift——但实际情况往往并非如此。两者的差异涵盖系统表（

pg_catalog

并不完整）、DDL（无索引、无序列）、函数（

string_agg

、针对表的

SUBSTR

、仅领导者节点可用的函数）、数据类型（

text

列会变为VARCHAR(256)）以及比较语义（尾随空格、未强制执行的约束）。请默认两者存在差异，并参考下方的文档进行验证——不要基于PostgreSQL的习惯作答。 常见的PostgreSQL→Redshift差异记录在

references/redshift-sql-syntax.md

中。

配合 AWS MCP server 使用效果最佳——它会在沙箱环境中运行下文提到的AWS CLI和Redshift Data API调用，并提供审计日志。本文中的所有指导均基于标准AWS CLI和SQL，无需依赖该服务也可使用。

STEP 0: Serverless or Provisioned?

步骤0：是Serverless还是预配置集群？

Establish this before answering — APIs, system tables, and capabilities differ. Take it from the question when it says which one; ask when it does not.

SELECT version()

does not identify it.

Serverless — identified by a workgroup (and namespace). Data API calls take
```
--workgroup-name
```
; the user says "workgroup"/"Serverless".
Provisioned — identified by a cluster. Data API calls take
```
--cluster-identifier
```
; the user says "cluster".

Target	System Views	Credentials API
Provisioned	`SYS_` , all `SVV_` + `STL_` , `STV_` , `SVL_` , `SVCS_` (single-AZ only — disabled on Multi-AZ)	`redshift:GetClusterCredentials`
Serverless	`SYS_` + a subset of `SVV_` ONLY (no `STL` / `STV` / `SVL` / `SVCS` )	`redshift-serverless:GetCredentials`

在作答前请先确认这一点——API、系统表和功能均有所不同。如果问题中已说明，则直接使用该信息；若未说明，请询问用户。

SELECT version()

无法区分两者。

Serverless — 通过工作组（和命名空间）识别。Data API调用需传入
```
--workgroup-name
```
；用户会提及“工作组”/“Serverless”。
预配置集群 — 通过集群识别。Data API调用需传入
```
--cluster-identifier
```
；用户会提及“集群”。

目标类型	系统视图	凭证API
预配置集群	`SYS_` 、所有 `SVV_` + `STL_` 、 `STV_` 、 `SVL_` 、 `SVCS_` （仅单AZ可用——多AZ环境下禁用）	`redshift:GetClusterCredentials`
Serverless	`SYS_` + 部分 `SVV_` （无 `STL` / `STV` / `SVL` / `SVCS` ）	`redshift-serverless:GetCredentials`

Critical Facts

关键要点

SHOW commands are the primary metadata interface — SHOW DATABASES, SHOW SCHEMAS, SHOW TABLES, SHOW COLUMNS, SHOW TABLE, SHOW VIEW. Do NOT default to pg_catalog or information_schema. → Load
references/redshift-sql-metadata.md
for metadata/discovery questions and any "relation does not exist" report — it has the diagnostic flow.
SYS_
views are the preferred system views — they work everywhere.
```
STL_
```
,
```
STV_
```
,
```
SVL_
```
, and
```
SVCS_
```
are provisioned single-AZ only, and some
```
SVV_
```
views are unsupported on Serverless. → Load
references/redshift-sql-metadata.md
for any system-view or monitoring question.
sys_load_error_detail
for COPY debugging (not
```
stl_load_errors
```
, which is provisioned single-AZ only).

DATEADD/DATEDIFF — unit-first argument order:

DATEADD(day, -30, GETDATE())

DATEDIFF(day, start, end)

APPROXIMATE COUNT(DISTINCT col) — Redshift-specific, ~2% error, much faster than exact COUNT(DISTINCT) on large datasets.
MERGE ... REMOVE DUPLICATES — simplified dedup when source and target have identical schemas.
COPY should use IAM_ROLE (the namespace role, not the caller role) + supports MANIFEST for explicit file lists + MAXERROR for error tolerance.
SUBSTR()
is leader-node-only — works on literals but errors on table columns (
```
SUBSTR() function is not supported (Hint: use SUBSTRING instead)
```
). Use
```
SUBSTRING()
```
on columns.
UNIQUE / PRIMARY KEY / FOREIGN KEY are informational only — NOT enforced (duplicate rows are accepted with no error). Optimizer hints; enforce integrity in the application or via MERGE.
```
NOT NULL
```
IS enforced.
SHOW VIEW <schema.name>
returns the definition of a regular view, materialized view, or late-binding view. MV freshness:
```
SVV_MV_INFO
```
(
```
is_stale
```
).
TOP N
and
LIMIT N
both work (
```
TOP N PERCENT
```
does not). A
```
text
```
column becomes
```
VARCHAR(256)
```
— use
```
VARCHAR(max)
```
or explicit length.

Iceberg tables use
CREATE TABLE ... USING ICEBERG
(not

STORED AS ICEBERG

, not

TABLE_FORMAT=ICEBERG

Datashares support read and write operations — consumers can write once the producer grants write privileges. Treat "permission denied" on a datashare write as a missing grant, not an unsupported operation. → Load
references/redshift-sql-metadata.md
for requirements and limits.

SHOW命令是主要的元数据接口——SHOW DATABASES、SHOW SCHEMAS、SHOW TABLES、SHOW COLUMNS、SHOW TABLE、SHOW VIEW。不要默认使用pg_catalog或information_schema。→ 若遇到元数据/发现问题或任何“relation does not exist”报错，请查阅
references/redshift-sql-metadata.md
——该文档包含诊断流程。
SYS_
视图是推荐使用的系统视图——可在所有环境中使用。
```
STL_
```
、
```
STV_
```
、
```
SVL_
```
和
```
SVCS_
```
仅适用于预配置单AZ集群，部分
```
SVV_
```
视图在Serverless环境中不支持。→ 若遇到系统视图或监控相关问题，请查阅
references/redshift-sql-metadata.md
。
**
```
sys_load_error_detail
```
**用于调试COPY操作（而非
```
stl_load_errors
```
，后者仅适用于预配置单AZ集群）。
DATEADD/DATEDIFF——参数顺序为单位在前：
```
DATEADD(day, -30, GETDATE())
```
、
```
DATEDIFF(day, start, end)
```
。
APPROXIMATE COUNT(DISTINCT col)——Redshift专属功能，误差约2%，在大型数据集上比精确COUNT(DISTINCT)快得多。
MERGE ... REMOVE DUPLICATES——当源表和目标表 schema 相同时，可简化去重操作。
COPY操作应使用IAM_ROLE（命名空间角色，而非调用者角色）+ 支持MANIFEST指定明确的文件列表 + MAXERROR设置错误容忍度。
SUBSTR()
仅在领导者节点可用——可用于字面量，但对表列使用时会报错（
```
SUBSTR() function is not supported (Hint: use SUBSTRING instead)
```
）。对表列请使用
```
SUBSTRING()
```
。
UNIQUE / PRIMARY KEY / FOREIGN KEY仅为信息性约束——不强制执行（重复行可被插入且无报错）。仅作为优化器提示；请在应用层或通过MERGE操作保证数据完整性。
```
NOT NULL
```
是强制执行的。
**
```
SHOW VIEW <schema.name>
```
**返回普通视图、物化视图或延迟绑定视图的定义。物化视图新鲜度：
```
SVV_MV_INFO
```
（
```
is_stale
```
字段）。
TOP N
和
LIMIT N
均可用（
```
TOP N PERCENT
```
不可用）。
```
text
```
列会变为
```
VARCHAR(256)
```
——请使用
```
VARCHAR(max)
```
或指定明确长度。

Iceberg表使用
CREATE TABLE ... USING ICEBERG
（而非

STORED AS ICEBERG

或

TABLE_FORMAT=ICEBERG

）。

数据共享支持读写操作——生产者授予写入权限后，消费者即可写入。若在数据共享写入时遇到“permission denied”，请视为缺少权限授予，而非操作不支持。→ 请查阅
references/redshift-sql-metadata.md
了解要求和限制。

Safety Guardrails

安全防护规则

BLOCK: DROP DATABASE, DELETE without WHERE, publicly-accessible=true, GRANT ALL ON ALL WARN then confirm: RESIZE, RESTORE, VACUUM on large tables, ALTER PASSWORD, WLM config change Confirm: CREATE, GRANT specific, COPY, UNLOAD

禁止执行：DROP DATABASE、不带WHERE的DELETE、publicly-accessible=true、GRANT ALL ON ALL 警告后确认：RESIZE、RESTORE、对大表执行VACUUM、ALTER PASSWORD、WLM配置变更 需确认：CREATE、特定权限的GRANT、COPY、UNLOAD

Security Considerations

安全注意事项

Apply these defaults when generating anything that connects, loads, or exports. Details are in the reference files noted.

In transit: the Data API is HTTPS-only. For JDBC/ODBC set the
```
require_ssl
```
parameter and connect with
```
sslmode=verify-full
```
so the server certificate is checked.
At rest: keep cluster/namespace encryption enabled, and add
```
ENCRYPTED KMS_KEY_ID '<arn>'
```
to
```
UNLOAD
```
— it writes query results to S3, outside Redshift's own encryption. →
```
references/redshift-sql-ddl-copy.md
```
Credentials: prefer
```
SecretArn
```
(Secrets Manager) or IAM Identity Center;
```
DbUser
```
is acceptable because it issues temporary credentials. Never place database passwords in code, environment variables, or SQL text. →
```
references/redshift-sql-recipes-load-api.md
```
Least privilege: scope the namespace
```
IAM_ROLE
```
to the specific bucket and prefix (
```
s3:GetObject
```
on
```
arn:aws:s3:::<bucket>/<prefix>/*
```
), not
```
s3:*
```
or a managed full-access policy, and condition its trust policy on both
```
aws:SourceArn
```
(the cluster/namespace ARN) and
```
aws:SourceAccount
```
—
```
SourceArn
```
alone still allows another resource in the account to assume it. Grant per-object privileges rather than
```
GRANT ALL ON ALL
```
.
Audit: CloudTrail records
```
redshift-data:*
```
API calls but not the SQL executed; enable Redshift audit logging (
```
useractivitylog
```
,
```
connectionlog
```
,
```
userlog
```
) for that. Both capture query text and user activity, so encrypt every destination in use: the CloudWatch Logs group (
```
aws logs associate-kms-key
```
), the CloudTrail trail (SSE-KMS), and the audit-log S3 bucket (SSE-S3 — audit logging to S3 supports only S3-managed keys, not KMS). Serverless only supports sending audit logs to CloudWatch.
Network: keep
```
PubliclyAccessible=false
```
and connect over a VPC endpoint. Do not open port 5439 to
```
0.0.0.0/0
```
or
```
::/0
```
— scope inbound rules to specific CIDRs or to a referencing security group.
Sensitive data: Data API results persist for 24h and
```
sys_load_error_detail
```
can echo fragments of rejected rows, so treat statement IDs and load-error output as sensitive.
Further reading: Security in Amazon Redshift for the full guidance behind these defaults.

在生成任何连接、加载或导出相关内容时，请遵循以下默认规则。详细信息请参考标注的文档。

传输中：Data API仅支持HTTPS。对于JDBC/ODBC，请设置
```
require_ssl
```
参数，并使用
```
sslmode=verify-full
```
连接，以验证服务器证书。
静态存储：保持集群/命名空间加密启用，并在
```
UNLOAD
```
中添加
```
ENCRYPTED KMS_KEY_ID '<arn>'
```
——该操作会将查询结果写入Redshift自身加密范围外的S3。→
```
references/redshift-sql-ddl-copy.md
```
凭证：优先使用
```
SecretArn
```
（Secrets Manager）或IAM Identity Center；
```
DbUser
```
是可接受的，因为它会颁发临时凭证。切勿将数据库密码放置在代码、环境变量或SQL文本中。→
```
references/redshift-sql-recipes-load-api.md
```
最小权限原则：将命名空间
```
IAM_ROLE
```
的权限范围限定在特定存储桶和前缀（对
```
arn:aws:s3:::<bucket>/<prefix>/*
```
授予
```
s3:GetObject
```
），而非
```
s3:*
```
或全访问托管策略，并在信任策略中同时添加
```
aws:SourceArn
```
（集群/命名空间ARN）和
```
aws:SourceAccount
```
条件——仅
```
SourceArn
```
仍允许账户内的其他资源扮演该角色。授予对象级权限，而非
```
GRANT ALL ON ALL
```
。
审计：CloudTrail会记录
```
redshift-data:*
```
API调用，但不会记录执行的SQL；请启用Redshift审计日志（
```
useractivitylog
```
、
```
connectionlog
```
、
```
userlog
```
）来记录SQL。两者都会捕获查询文本和用户活动，因此请加密所有使用的目标：CloudWatch日志组（
```
aws logs associate-kms-key
```
）、CloudTrail跟踪（SSE-KMS）以及审计日志S3存储桶（SSE-S3——审计日志到S3仅支持S3托管密钥，不支持KMS）。Serverless仅支持将审计日志发送到CloudWatch。
网络：保持
```
PubliclyAccessible=false
```
，通过VPC端点连接。不要将5439端口开放给
```
0.0.0.0/0
```
或
```
::/0
```
——将入站规则限定在特定CIDR或引用的安全组。
敏感数据：Data API结果会保留24小时，
```
sys_load_error_detail
```
可能会回显被拒绝行的片段，因此请将语句ID和加载错误输出视为敏感信息。
扩展阅读： Security in Amazon Redshift 了解这些默认规则背后的完整指导。

Routing Table

路由表

MANDATORY: When a question matches a row below, you MUST load and read the referenced file BEFORE answering.

Ask whether the target is provisioned or Serverless before giving troubleshooting steps — unless the question already says which one, in which case use that and do not re-confirm.

User Intent	Route To
"CREATE TABLE", "DISTKEY/SORTKEY", "ENCODE", "IDENTITY", "COPY", "UNLOAD", "IAM_ROLE", "Iceberg table"	`references/redshift-sql-ddl-copy.md`
"LISTAGG", "DATEADD/DATEDIFF", "NVL/DECODE", "type mapping", "text type", "VARBYTE", "recursive CTE"	`references/redshift-sql-functions-types.md`
"QUALIFY", "PIVOT/UNPIVOT", "MERGE", "TOP N", "SUBSTR error", "UNIQUE/PK not enforced", "trailing blanks", "leader-node function", "JSON", "SUPER", "PartiQL", "nested/semi-structured data"	`references/redshift-sql-extensions-semantics.md`
"system view", "SVV_/SYS_", "SHOW commands", "STL vs SYS", "list tables", "distkey/sortkey lookup", "datashare discovery", "2-part vs 3-part", "permission denied", "GRANT", "privileges", "relation/table does not exist"	`references/redshift-sql-metadata.md`
"how do I write SQL", "PostgreSQL vs Redshift", "which SQL reference", general dialect question	`references/redshift-sql-syntax.md` (index of the 6 SQL references + PostgreSQL-vs-Redshift failure table)
"COPY failed", "load error", "Data API poll", "async query", "Data API throttle"	`references/redshift-sql-recipes-load-api.md`
"materialized view", "MV refresh", "AUTO REFRESH", "stale view"	`references/redshift-sql-materialized-views.md`
General Redshift question not matching above	Answer directly from general knowledge
Aurora, RDS, DynamoDB, Athena (non-Redshift)	REFUSE. State this skill is for Amazon Redshift only. Do not provide guidance for other database services.

强制要求：当问题与下方某一行匹配时，必须先加载并阅读参考文档，然后再作答。

在提供故障排除步骤前，请询问目标是预配置集群还是Serverless——除非问题中已说明，否则无需再次确认。

用户意图	参考文档
"CREATE TABLE"、"DISTKEY/SORTKEY"、"ENCODE"、"IDENTITY"、"COPY"、"UNLOAD"、"IAM_ROLE"、"Iceberg table"	`references/redshift-sql-ddl-copy.md`
"LISTAGG"、"DATEADD/DATEDIFF"、"NVL/DECODE"、"类型映射"、"text type"、"VARBYTE"、"递归CTE"	`references/redshift-sql-functions-types.md`
"QUALIFY"、"PIVOT/UNPIVOT"、"MERGE"、"TOP N"、"SUBSTR错误"、"UNIQUE/PK未强制执行"、"尾随空格"、"领导者节点函数"、"JSON"、"SUPER"、"PartiQL"、"嵌套/半结构化数据"	`references/redshift-sql-extensions-semantics.md`
"系统视图"、"SVV_/SYS_"、"SHOW命令"、"STL vs SYS"、"列出表"、"distkey/sortkey查询"、"数据共享发现"、"2部分 vs 3部分"、"permission denied"、"GRANT"、"权限"、"relation/table不存在"	`references/redshift-sql-metadata.md`
"如何编写SQL"、"PostgreSQL vs Redshift"、"SQL参考文档"、通用方言问题	`references/redshift-sql-syntax.md` （6个SQL参考文档的索引 + PostgreSQL与Redshift差异表）
"COPY失败"、"加载错误"、"Data API轮询"、"异步查询"、"Data API限流"	`references/redshift-sql-recipes-load-api.md`
"物化视图"、"MV刷新"、"AUTO REFRESH"、"过期视图"	`references/redshift-sql-materialized-views.md`
不匹配上述情况的通用Redshift问题	直接通过通用知识作答
Aurora、RDS、DynamoDB、Athena（非Redshift场景）	拒绝作答。说明本技能仅适用于Amazon Redshift，请勿为其他数据库服务提供指导。

Data API Quick Reference

Data API快速参考

→ Load
references/redshift-sql-recipes-load-api.md
before answering ANY Data API, COPY-error, or async-query question. It carries the bounded poll loop, the

HasResultSet

and

ResourceNotFoundException

handling, the per-target parameters, and the auth options.

Data API calls are async by default — use long polling (

--wait-time-seconds

, 1–30) rather than blind sleeps, and keep a bounded loop for work that can exceed 30s. Serverless takes

--workgroup-name

, provisioned takes

--cluster-identifier

→ 在回答任何Data API、COPY错误或异步查询相关问题前，请查阅
references/redshift-sql-recipes-load-api.md
。该文档包含有限轮询循环、

HasResultSet

和

ResourceNotFoundException

处理、针对不同目标的参数以及认证选项。

Data API调用默认是异步的——请使用长轮询（

--wait-time-seconds

，1–30秒）而非盲目等待，并为可能超过30秒的任务设置有限循环。Serverless需传入

--workgroup-name

，预配置集群需传入

--cluster-identifier

。