migrating-to-amazon-redshift

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Migrating to Amazon Redshift

迁移至Amazon Redshift

What this skill is

本技能说明

This skill is AI guidance, not an execution framework. It is entirely Markdown knowledge (rules, mappings, patterns, best practices) — no executable code. All execution — conversion, the discovery/migration/validation runners, dependencies, and infrastructure — you (the AI) generate at runtime from this knowledge, tailored to the customer's environment.

Principle: knowledge over shipped code → less drift, nothing for the customer to run or depend on, reliable first-time results. Do not look for a pyproject, a tools package, an orchestrator engine, or shipped scripts — there are none by design; you generate execution.

Runtime: this skill works with or without the AWS MCP server — step guidance uses AWS CLI syntax. Running it with the AWS MCP server is recommended for sandboxed execution and audit logging; without it, the AI runs the generated scripts on the host shell (assumes Bash, Python 3, and AWS CLI + credentials). Do not assume MCP-only tools are available.

本技能是AI指导方案，而非执行框架。它完全由Markdown知识构成（规则、映射、模式、最佳实践）——无任何可执行代码。所有执行逻辑（转换、发现/迁移/验证运行器、依赖项及基础设施）均由您（AI）在运行时根据这些知识生成，并针对客户环境进行定制。

核心原则：知识优先，而非预置代码 → 减少偏差，无需客户运行或依赖任何预置内容，确保首次执行即可获得可靠结果。请勿寻找pyproject、工具包、编排引擎或预置脚本——本技能设计上不包含这些内容；所有执行逻辑均由您生成。

运行环境： 本技能可在有/无AWS MCP服务器的情况下运行——步骤指导采用AWS CLI语法。推荐搭配AWS MCP服务器运行，以实现沙箱化执行和审计日志记录；若无MCP服务器，AI将在主机Shell中运行生成的脚本（假设环境已安装Bash、Python 3及AWS CLI并配置凭证）。请勿假设仅MCP可用的工具已存在。

Source routing

源系统路由

This skill migrates a supported source data warehouse to Amazon Redshift. First identify the source system, then load that source's knowledge under

references/<source>/

Teradata (Vantage) →
```
references/teradata/
```
— supported (all references below).
Other sources (e.g. Snowflake, Oracle) — unsupported; each is added as its own
references/<source>/
set when ready.

The workflow is source-agnostic (discovery → convert → migrate → validate → performance → report); only the conversion knowledge is source-specific. Everything below is the Teradata set.

本技能支持将受支持的源数据仓库迁移至Amazon Redshift。首先确定源系统，然后加载

references/<source>/

下对应源系统的知识：

Teradata (Vantage) →
```
references/teradata/
```
—— 已支持（以下所有参考内容均针对该系统）。
其他源系统（如Snowflake、Oracle）——暂不支持；待就绪后将以独立的
references/<source>/
集合形式添加。

工作流与源系统无关（发现→转换→迁移→验证→性能评估→报告）；仅转换知识是源系统特定的。以下所有内容均为Teradata相关集合。

When to use

适用场景

Migrating a Teradata system (Vantage) to Amazon Redshift.
Converting Teradata DDL, SQL, stored procedures, macros, or BTEQ to Redshift/RSQL.
Assessing Teradata→Redshift migration complexity/effort.

将Teradata系统（Vantage）迁移至Amazon Redshift。
将Teradata DDL、SQL、存储过程、宏或BTEQ转换为Redshift/RSQL。
评估Teradata→Redshift迁移的复杂度/工作量。

Operating principles

操作原则

Discovery is strictly read-only (SELECT-only) on the source. Never change production state: no DDL/DML, and never enable logging (
```
BEGIN/REPLACE QUERY LOGGING
```
). If DBQL is empty, mark it
```
unavailable
```
and fall back to always-on
```
DBC.AMPUsageV
```
— see
```
references/teradata/discovery-queries.md
```
.
Skill provides knowledge; you generate execution. Read the
```
references/
```
to reason and convert — apply the rules in
```
references/teradata/conversion-rules.md
```
directly for conversion, and generate the discovery/migration/validation runners (and the read-only discovery collector from
```
references/teradata/discovery-queries.md
```
) tailored to the environment.
Generate, don't assume a framework. Assume the environment has Bash, Python 3, and AWS CLI + credentials. Any Python lib a generated script needs (
```
teradatasql
```
,
```
boto3
```
, …) is
```
pip install
```
-ed on demand by that script / its run-instructions — pin exact versions. Teradata TTU (BTEQ/TPT) is Linux/Windows-only — not macOS; prefer WRITE_NOS + teradatasql
(cross-platform, no client) for discovery/extract unless a TTU/Linux host exists.
Credentials: use a read-only Teradata user; prefer IAM roles over IAM users. For production, reference credentials from AWS Secrets Manager or Systems Manager Parameter Store. For local development only, a git-ignored
```
.env
```
file or profile may be used — never commit it. Never hard-code or echo secrets. In a portable bundle, reference a co-located credentials file and ship a
```
credentials.env.example
```
template — the real file is git-ignored.
Persist state in files. All generated output goes under a git-ignored
```
output/
```
in the user's working dir; keep
```
output/state.md
```
current so work is resumable.

发现阶段对源系统严格只读（仅执行SELECT）。绝不能更改生产环境状态：禁止执行DDL/DML，绝不能启用日志记录（
```
BEGIN/REPLACE QUERY LOGGING
```
）。若DBQL为空，标记为
```
unavailable
```
并切换至持续可用的
```
DBC.AMPUsageV
```
——详见
```
references/teradata/discovery-queries.md
```
。
技能提供知识，您生成执行逻辑。阅读
```
references/
```
下的内容进行推理和转换——直接应用
```
references/teradata/conversion-rules.md
```
中的规则进行转换，并根据环境生成发现/迁移/验证运行器（以及来自
```
references/teradata/discovery-queries.md
```
的只读发现收集器）。
生成执行逻辑，而非假设存在框架。假设环境已安装Bash、Python 3及AWS CLI并配置凭证。生成的脚本所需的任何Python库（如
```
teradatasql
```
、
```
boto3
```
等）需由该脚本/其运行指令按需通过
pip install
安装——并固定精确版本。Teradata TTU（BTEQ/TPT）仅支持Linux/Windows系统——不支持macOS；除非存在TTU/Linux主机，否则优先使用WRITE_NOS + teradatasql
（跨平台，无需客户端）进行发现/提取。
凭证管理： 使用只读Teradata用户；优先使用IAM角色而非IAM用户。针对生产环境，从AWS Secrets Manager或Systems Manager Parameter Store引用凭证。仅针对本地开发，可使用Git忽略的
```
.env
```
文件或配置文件——绝不能提交该文件。绝不能硬编码或回显密钥。在可移植包中，引用同目录下的凭证文件并附带
```
credentials.env.example
```
模板——真实凭证文件需被Git忽略。
将状态持久化至文件。所有生成的输出均存储在用户工作目录下Git忽略的
```
output/
```
文件夹中；保持
```
output/state.md
```
内容最新，以便恢复工作。

Workflow (phases)

工作流（阶段）

Run in order; each phase's

result/

feeds the next (see

references/teradata/orchestration.md

Discovery — inventory the source. →
```
references/teradata/discovery-queries.md
```
(read-only collection SQL + BTEQ driver template the AI generates) →
```
output/discovery/result/inventory.json
```

Conversion — schema + code. Apply the conversion rules directly, flag the manual-rewrite long tail, and fix Redshift errors from the references. →

references/teradata/conversion-rules.md

references/teradata/data-type-mapping.md

references/teradata/architecture-mapping.md

references/teradata/stored-procedure-migration.md

references/teradata/bteq-to-rsql.md

references/teradata/common-errors.md

Data migration — extract → S3 → COPY, restartable. →
```
references/teradata/data-migration-patterns.md
```
Validation — counts/aggregates/sampling. →
```
references/teradata/validation-patterns.md
```

Performance — baseline vs Redshift; size the target. →

references/teradata/performance.md

references/teradata/sizing.md

Reporting — aggregate all phases. →
```
references/teradata/reporting.md
```

按顺序执行；每个阶段的

result/

目录内容将作为下一阶段的输入（详见

references/teradata/orchestration.md

）。

发现 —— 盘点源系统。→
```
references/teradata/discovery-queries.md
```
（AI生成的只读收集SQL + BTEQ驱动模板）→
```
output/discovery/result/inventory.json
```

转换 —— Schema + 代码。直接应用转换规则，标记需要手动重写的长尾内容，并根据参考内容修复Redshift错误。→

references/teradata/conversion-rules.md

、

references/teradata/data-type-mapping.md

、

references/teradata/architecture-mapping.md

、

references/teradata/stored-procedure-migration.md

、

references/teradata/bteq-to-rsql.md

、

references/teradata/common-errors.md

数据迁移 —— 提取→S3→COPY，支持重启。→
```
references/teradata/data-migration-patterns.md
```

验证 —— 计数/聚合/抽样。→

references/teradata/validation-patterns.md

性能评估 —— 基准对比Redshift；调整目标集群规模。→
```
references/teradata/performance.md
```
、
```
references/teradata/sizing.md
```
报告生成 —— 汇总所有阶段内容。→
```
references/teradata/reporting.md
```

Conversion (how the AI applies it)

转换（AI应用方式）

There is no converter to run — convert by applying the rules in
references/teradata/conversion-rules.md
directly (with the type / architecture / stored-procedure / BTEQ references): apply the deterministic rules to the well-understood bulk, flag the manual-rewrite constructs with their suggested rewrites, assign a confidence per object, and fix any Redshift errors using

references/teradata/common-errors.md

. The reference docs are the single source of truth;

conversion-rules.md

includes golden input→output examples to match.

本技能无现成转换器可运行——需通过直接应用
references/teradata/conversion-rules.md
中的规则（结合类型/架构/存储过程/BTEQ相关参考内容）进行转换：将确定性规则应用于易于理解的大部分内容，标记需要手动重写的结构并给出建议重写方案，为每个对象分配置信度，并使用

references/teradata/common-errors.md

修复Redshift错误。参考文档为唯一可信来源；

conversion-rules.md

包含需匹配的标准输入→输出示例。

Execution modes (connectivity)

执行模式（连接方式）

Connected — your host can reach Teradata/Redshift → run the generated scripts in place.
Disconnected — it can't → generate a self-contained bundle under
```
output/<phase>/
```
(script + co-located credentials template + relative
```
result/
```
+
```
run-instructions.md
```
); the operator runs it on a reachable host and copies
```
result/
```
back. The copied-back
```
result/
```
is the durable state — read it (+
```
state.md
```
) and continue.

连接模式 —— 您的主机可访问Teradata/Redshift → 直接运行生成的脚本。
离线模式 —— 无法访问 → 在
```
output/<phase>/
```
下生成独立的自包含包（脚本 + 同目录凭证模板 + 相对路径
```
result/
```
+
```
run-instructions.md
```
）；操作人员在可访问的主机上运行该包并将
```
result/
```
目录内容复制回来。复制后的
```
result/
```
为持久化状态——读取该目录内容（+
```
state.md
```
）并继续后续工作。

Project-workspace layout (per migration run)

项目工作区布局（每次迁移运行）

<project-workspace>/
  migration-config.yaml          # operator-authored: endpoints, scope, strategy
  .gitignore                     # ignores output/
  output/                        # everything generated (git-ignored)
    state.md                     # progress cursor
    discovery/   …  result/inventory.json
    conversion/  …  result/{ddl,sql,procedures,rsql}/  manual_review.json
    data_migration/ … result/{extract,load,templates}/  migration_manifest.json
    validation/  …  result/validation_report.json
    performance/ …  result/{perf_baseline,perf_compare}.json
    reporting/      result/migration_report.md

<project-workspace>/
  migration-config.yaml          # 操作人员编写：端点、范围、策略
  .gitignore                     # 忽略output/目录
  output/                        # 所有生成内容（Git忽略）
    state.md                     # 进度标记
    discovery/   …  result/inventory.json
    conversion/  …  result/{ddl,sql,procedures,rsql}/  manual_review.json
    data_migration/ … result/{extract,load,templates}/  migration_manifest.json
    validation/  …  result/validation_report.json
    performance/ …  result/{perf_baseline,perf_compare}.json
    reporting/      result/migration_report.md

Security considerations

安全注意事项

No shipped code or dependencies. This skill is text-only — the customer runs nothing from it. Any runner the AI generates MUST pin exact dependency versions, validate/sanitize inputs (file paths, SQL, shell args), and never print or log credentials, secrets, or PII.
Least privilege + ephemeral credentials. Use a read-only Teradata user for discovery. On AWS prefer IAM roles over IAM users and IAM auth over username/password. Keep secrets in AWS Secrets Manager / Parameter Store — never hard-code, echo, or commit them (credentials files are git-ignored; ship only
```
*.example
```
templates).
Data in transit / at rest. Use TLS to both engines; stage extracts in an encrypted S3 bucket (SSE) with a least-privilege bucket policy; load via
```
COPY … IAM_ROLE
```
(not access keys). Enable encryption on the target Redshift cluster.
Blast radius. Discovery is read-only by design. Migration writes to the target — validate against a throwaway / non-production Redshift first, and never point a generated write-path at production without explicit operator confirmation.
No secret leakage in artifacts. Generated
```
output/…
```
(manifests, reports,
```
state.md
```
) MUST NOT embed credentials or endpoints beyond what the operator supplies in
```
migration-config.yaml
```
.
COPY
IAM_ROLE
hardening. Scope the role's policy to the specific staging prefix (not bucket-wide
```
s3:*
```
), and include condition keys in its trust policy (
```
aws:SourceAccount
```
/
```
aws:SourceArn
```
, or
```
sts:ExternalId
```
for cross-account) to prevent confused-deputy assumption — per Redshift IAM-role authorization best practices.
Logging & monitoring. Enable CloudTrail (S3 data events on the staging bucket + Redshift management events), Redshift audit logging (connection/user-activity logs to S3 or CloudWatch), and CloudWatch alarms on COPY failures or unusual staging-bucket access during the migration.

The AWS MCP server (recommended runtime) additionally provides sandboxed execution and audit logging for the generated scripts.

无预置代码或依赖项。本技能仅包含文本内容——客户无需运行任何来自本技能的内容。AI生成的任何运行器必须固定精确的依赖版本，验证/清理输入（文件路径、SQL、Shell参数），且绝不能打印或记录凭证、密钥或PII。
最小权限 + 临时凭证。发现阶段使用只读Teradata用户。在AWS环境中，优先使用IAM角色而非IAM用户，IAM认证而非用户名/密码。将密钥存储在AWS Secrets Manager/Parameter Store中——绝不能硬编码、回显或提交密钥（凭证文件需被Git忽略；仅发布
```
*.example
```
模板）。
传输中/静态数据安全。与两个引擎的连接均使用TLS；提取的数据暂存于加密S3桶（SSE），并配置最小权限桶策略；通过
```
COPY … IAM_ROLE
```
加载数据（而非访问密钥）。启用目标Redshift集群的加密功能。
影响范围控制。发现阶段设计为只读。迁移阶段会写入目标集群——先在临时/非生产Redshift集群中进行验证，未经操作人员明确确认，绝不能将生成的写入路径指向生产环境。
工件中无密钥泄露。生成的
```
output/…
```
内容（清单、报告、
```
state.md
```
）不得嵌入凭证或端点信息，除非操作人员在
```
migration-config.yaml
```
中提供了这些内容。
COPY
IAM_ROLE
强化。将角色的策略范围限定为特定暂存前缀（而非桶级别的
```
s3:*
```
），并在信任策略中添加条件键（
```
aws:SourceAccount
```
/
```
aws:SourceArn
```
，或跨账户场景下的
```
sts:ExternalId
```
），以防止混淆代理问题——遵循Redshift IAM角色授权最佳实践。
日志与监控。启用CloudTrail（暂存桶的S3数据事件 + Redshift管理事件）、Redshift审计日志（连接/用户活动日志存储至S3或CloudWatch），并针对迁移期间的COPY失败或异常暂存桶访问配置CloudWatch告警。

推荐的运行环境AWS MCP服务器还可为生成的脚本提供沙箱化执行和审计日志记录功能。

References (specialized knowledge)

参考内容（专业知识）

File	Topic
`references/teradata/orchestration.md`	phase workflow + state model
`references/teradata/conversion-rules.md`	the 72 conversion rules (source of truth)
`references/teradata/data-type-mapping.md`	TD→RS type mapping
`references/teradata/architecture-mapping.md`	PI→DISTKEY, PPI→SORTKEY, Join Index→MV
`references/teradata/stored-procedure-migration.md`	SP → PL/pgSQL
`references/teradata/bteq-to-rsql.md`	BTEQ → RSQL
`references/teradata/common-errors.md`	common Redshift errors + fixes
`references/teradata/discovery-queries.md`	DBC system-view inventory queries
`references/teradata/data-migration-patterns.md`	COPY/TPT/micro-batch/checkpoint
`references/teradata/validation-patterns.md`	row-count/aggregate/sample compare
`references/teradata/performance.md`	representative-query extraction + compare
`references/teradata/sizing.md`	RG node type + count from the source profile
`references/teradata/reporting.md`	migration status-report generation

文件	主题
`references/teradata/orchestration.md`	阶段工作流 + 状态模型
`references/teradata/conversion-rules.md`	72条转换规则（可信来源）
`references/teradata/data-type-mapping.md`	TD→RS类型映射
`references/teradata/architecture-mapping.md`	PI→DISTKEY、PPI→SORTKEY、连接索引→MV
`references/teradata/stored-procedure-migration.md`	存储过程→PL/pgSQL
`references/teradata/bteq-to-rsql.md`	BTEQ→RSQL
`references/teradata/common-errors.md`	常见Redshift错误及修复方案
`references/teradata/discovery-queries.md`	DBC系统视图盘点查询
`references/teradata/data-migration-patterns.md`	COPY/TPT/微批量/检查点
`references/teradata/validation-patterns.md`	行数/聚合/抽样对比
`references/teradata/performance.md`	代表性查询提取及对比
`references/teradata/sizing.md`	根据源系统配置确定RG节点类型及数量
`references/teradata/reporting.md`	迁移状态报告生成