# Kettle Etl Expert

> 生成准确的 Kettle/PDI ETL 脚本（.kjb 作业与 .ktr 转换），涵盖 MySQL/MSSQL 连接、参数化增量迁移、 数据核对完整交付包（check_xxx.ktr + check_xxx_detail.ktr + check_xxx_amount.ktr / check_xxx_value.ktr + check_xxx.kjb）、 多个核对 KJB 并联合并为统一结果核对作业、数据迁移清理脚本（MySQL/UFTMDB）自动同步、 根据 Kettle 脚本解析目标表并查询本地 MySQL 生成两层目标表文档（index + tables），与批量处理最佳实践。 **凡新建/修改 .ktr（含插入 Mapping、改 hop、增删步骤）必须按本 skill 做 Spoon GUI 布局检查**（见 ktr-detail-layout §7.5 + check_ktr_gui_layout.py）。 在用户提及 Kettle、PDI、.kjb、.ktr、ETL 脚本、数据迁移、数据同步、数据核对、增量迁移、 更新数据迁移清理脚本、数据迁移清理脚本、并联合并核对 KJB、字典映射、根据 Kettle 脚本输出文档、 目标表文档、表结构文档、更新到文档、更新文档表结构、同步库结构、**迁移说明**、**取数逻辑**、**过滤条件**、 **迁移后记录不一样**、**核对数量差异**、**源目标记录数不一致**、**数据核对差异分析**、 **ktr 布局**、**Spoon GUI**、**市场 映射**时使用。

- Skill: `yangchen91/kettle-etl-expert` (Agent Skill, multi-file: 44 files)
- Install (CLI): `npx skillmds@latest add yangchen91/kettle-etl-expert`
- Raw SKILL.md: https://api.skillmd.com/api/skills/yangchen91/kettle-etl-expert/raw
- Safety review: pending (external: skill-scanner PASS, skillspector CAUTION)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: YangChen91 (https://skillmd.com/u/yangchen91)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/yangchen91/kettle-etl-expert

---


# Kettle ETL Expert

基于生产环境验证的 Kettle 脚本模板，生成可直接在 Spoon 中打开的 `.kjb` / `.ktr` 文件。

## 核心能力

- `.kjb` 作业与 `.ktr` 转换生成
- MySQL / MSSQL 连接配置与参数化设计
- 增量迁移、数据核对、批量 ETL 模板
- 数据迁移清理脚本（从 ktr/kjb 自动提取目标表并更新 SQL）
- 目标表文档（从 ktr 解析写入表 + 本地 MySQL 字段，生成 index / tables 两层文档）
- 常见 XML 结构与步骤错误修复
- **Spoon GUI 布局**：新建/改 ktr 后强制按 §7.5 摆放并跑 `check_ktr_gui_layout.py`

## 触发场景

| 场景 | 说明 |
|------|------|
| 数据迁移 | 跨库/跨表迁移 |
| 数据同步 | 源表与目标表一致性 |
| 数据核对 | 数量、数值、明细三类核对 |
| **数据核对差异分析** | 迁移后源/目标记录数不一致的根因定位（见 [data-verification-analysis.md](references/data-verification-analysis.md)） |
| 批量处理 | 大批量 ETL |
| 清理脚本同步 | 从 ktr/kjb 更新 MySQL/UFTMDB 清理 SQL |
| 目标表文档 | 解析 Kettle 目标表 + 查库生成 index / tables 文档 |
| 同步表结构 | 本地库结构变更后，仅刷新二层文档「表字段」节 |
| **迁移说明** | 分析 KTR 取数过滤逻辑，填充 `cicc_migration/`「迁移说明」章节 |

**关键词**：Kettle、PDI、.kjb、.ktr、ETL、数据迁移、数据同步、数据核对、增量迁移、字典映射、更新数据迁移清理脚本、数据迁移清理脚本、目标表文档、表结构文档、根据 Kettle 脚本输出文档、**更新文档表结构**、**同步库结构**、**迁移说明**、**取数逻辑**、**过滤条件**、**迁移后记录不一样**、**核对数量差异**、**源目标记录数不一致**、**数据核对差异分析**

---

## 1. KJB（Job）规范

### 1.1 基础结构

- `<name>` — 作业名称
- `<parameters>` — 参数定义
- `<connection>` — 数据库连接
- `<entries>` — 条目（TRANS、SQL、子作业等）
- `<hops>` — 条目连接

### 1.2 常用参数

| 参数名 | 默认值 | 说明 |
|--------|--------|------|
| p_increment_flag | 'N' | 增量标志（'Y'/'N'） |
| p_increment_fundaccount | '' | 增量资金账号列表 |
| p_exclude_client | '' | 排除客户 ID |
| p_exclude_fundaccount | '' | 排除资金账号 |

### 1.3 模板引用

| 类型 | 参考 |
|------|------|
| MySQL 连接 | [conn-mysql.md](references/conn-mysql.md) |
| MSSQL 连接 | [conn-mssql.md](references/conn-mssql.md) |
| TRANS 条目 | [entry-trans.md](references/entry-trans.md) |
| SQL 条目 | [entry-sql.md](references/entry-sql.md) |
| Hops | [hops-template.md](references/hops-template.md) |

---

## 2. KTR（Transformation）规范

### 2.1 基础结构

- `<info>` — 名称、参数、日志配置
- `<connection>` — 数据库连接
- `<step>` — 转换步骤
- `<order>` — 步骤 hop 连接

### 2.2 常用步骤

| Step | 用途 | 参考 |
|------|------|------|
| TableInput | 表读取 | [step-tableinput.md](references/step-tableinput.md) |
| InsertUpdate | 插入/更新 | [step-insertupdate.md](references/step-insertupdate.md) |
| FilterRows | 过滤 | [step-filterrows.md](references/step-filterrows.md) |
| SortRows | 排序 | [step-sortrows.md](references/step-sortrows.md) |
| MergeJoin | 已排序集连接 | [step-mergejoin.md](references/step-mergejoin.md) |
| Append | 多路源合并 | [ktr-detail-layout.md](references/ktr-detail-layout.md) |
| ExcelOutput | Excel 输出 | [step-exceloutput.md](references/step-exceloutput.md) |
| MappingInput / ValueMapper / MappingOutput | 字典映射子转换 | [dict-mapping-sub-transform.md](references/dict-mapping-sub-transform.md) |

### 2.3 参数化 SQL

参考 [sql-param-template.md](references/sql-param-template.md)

---

## 3. 最佳实践

### 3.1 作业流程

```
Start → SQL-创建临时表 → Transform1 → Transform2 → ... → SQL-清理 → End
```

### 3.2 增量迁移

- `p_increment_flag` 控制全量/增量
- `p_increment_fundaccount` 指定增量账号
- `p_exclude_client` / `p_exclude_fundaccount` 排除数据

### 3.3 数据核对交付规则（强制）

**触发条件**（满足任一即适用本规则）：
- 用户要求为某个迁移 `.ktr` 实现/编写**数据核对脚本**
- 用户提到「给 `{name}.ktr` 做核对」「生成 check 脚本」「数据迁移结果核对」
- 用户以 `as_fundrequest.ktr` 为例要求核对（或其他迁移脚本）

**交付物：必须一次性生成 4 个文件，禁止只生成部分 KTR 或遗漏 KJB。**

```
check_{name}.ktr              # 数量核对
check_{name}_detail.ktr       # 明细核对
check_{name}_amount.ktr       # 金额核对（资金/持仓等有金额语义）
check_{name}_value.ktr        # 数值核对（主数据/配置等无金额语义，见命名规则）
check_{name}.kjb              # 串联 3 个 KTR 并做差异判断
```

**第三类核对命名规则（金额 vs 数值）：**

| 数据类型 | KTR 文件 | KJB TRANS 条目 | KJB SIMPLE_EVAL | SetVariable 变量 |
|---------|---------|---------------|-----------------|-----------------|
| 有金额字段（资金、持仓、流水） | `check_{name}_amount.ktr` | 核对{name}**金额** | {name}**金额**差异判断 | `p_{name}_amount_diff` |
| 无金额语义（操作员、字典、配置） | `check_{name}_value.ktr` | 核对{name}**数值** | {name}**数值**差异判断 | `p_{name}_value_diff` |

实现流程相同（SUM + JoinRows + ScriptValueMod），仅文件名与 KJB 展示名称不同。非金额表**禁止**使用「金额」字样。

**as_fundrequest 标准示例：**

| 角色 | 文件 |
|------|------|
| 原始迁移脚本 | `D:\DataMigration_WorkSpace\CICC_Kettle_Scripts\4.资金持仓信息\as_fundrequest.ktr` |
| 数量核对 | `check_as_fundrequest.ktr` |
| 明细核对 | `check_as_fundrequest_detail.ktr` |
| 金额核对 | `check_as_fundrequest_amount.ktr` |
| 核对作业 | `check_as_fundrequest.kjb` |

**执行顺序：**

1. 读取原始迁移 `{name}.ktr`（用户会提供路径；未提供则主动询问）
2. 生成 3 个核对 KTR（连接/字段/SQL 与迁移脚本一致）
3. 生成 `check_{name}.kjb`，结构参考 `前台历史数据迁移结果核对 - 副本.kjb`
4. 4 文件输出到同一目录，KJB 用 `${Internal.Entry.Current.Directory}` 引用 KTR

**SetVariable ↔ KJB SIMPLE_EVAL 变量映射：**

| KTR | SetVariable 变量 | KJB 差异判断步骤 |
|-----|-----------------|-----------------|
| check_{name}.ktr | p_{name}_diff | {name}差异判断 |
| check_{name}_detail.ktr | p_{name}_detail_diff | {name}明细差异判断 |
| check_{name}_amount.ktr | p_{name}_amount_diff | {name}金额差异判断 |
| check_{name}_value.ktr | p_{name}_value_diff | {name}数值差异判断 |

**KJB 流程：**

```
Start → 核对{name} → 差异判断(==0) → 核对{name}明细 → 明细差异判断(==0)
  → 核对{name}金额/数值 → 金额/数值差异判断(==0)
```

完整规范（KTR 步骤、KJB 条目配置、检查清单）见 [data-verification-package.md](references/data-verification-package.md)

**从模板派生核对脚本**时，务必阅读 [data-verification-package.md § 从模板生成核对脚本](references/data-verification-package.md#从模板生成核对脚本经验)（SQL XML 转义、连接全量替换、明细步骤字段清理）；标杆：`check_tsys_user*`。

### 3.3.1 多个核对 KJB 并联合并（强制）

**触发条件**（满足任一即适用）：
- 用户说「合并到一个 kjb」「并联合并」「把多个 check_xxx.kjb 合并」
- 用户要求生成类似 `前台历史数据迁移结果核对.kjb` 的统一核对作业

**前置步骤：**
1. 读取用户指定的所有 `check_{name}.kjb`（或工作空间下待合并的核对 KJB）
2. 读取参考模板：`D:\DataMigration_WorkSpace\CICC_Kettle_Scripts\5.前台历史数据迁移\前台历史数据迁移结果核对.kjb`
3. 提取每个单表 KJB 的 TRANS 条目、SIMPLE_EVAL 条目、变量名、KTR 文件名

**交付物：**
- 生成 **1 个**合并后的 `{业务域}迁移结果核对.kjb`（如 `资金持仓信息迁移结果核对.kjb`）
- **保留**原有各 `check_{name}.kjb`，不删除、不覆盖

**布局规则（每表一行，横向三类核对）：**

| 列 | 条目类型 | xloc |
|----|---------|------|
| Start | SPECIAL | 112 |
| 数量核对 | TRANS `check_{name}.ktr` | 256 |
| 数量差异判断 | SIMPLE_EVAL `p_{name}_diff` | 432 |
| 明细核对 | TRANS `check_{name}_detail.ktr` | 640 |
| 明细差异判断 | SIMPLE_EVAL `p_{name}_detail_diff` | 864 |
| 金额核对 | TRANS `check_{name}_amount.ktr` | 1120 |
| 金额差异判断 | SIMPLE_EVAL `p_{name}_amount_diff` | 1360 |

- 第 N 行 `yloc = 96 + (N-1) × 96`（第 1 行 96，第 2 行 192，以此类推）
- Start 的 `yloc` 取各行中间值（如 2 行时 yloc=144）

**Hop 规则：**
- `Start → 各行第一个 TRANS`：`unconditional=Y`（多行并行启动）
- 行内链路：`TRANS → SIMPLE_EVAL → TRANS → ...`，均为 `evaluation=Y`、`unconditional=N`
- 差异不为 0 时，该行后续步骤不执行；各行互不影响

**合并示例（已验证）：**

| 角色 | 文件 |
|------|------|
| 单表 KJB（保留） | `check_as_fundrequest.kjb`、`check_as_fundrevertjour.kjb` |
| 合并后统一 KJB | `资金持仓信息迁移结果核对.kjb` |

完整规范见 [data-verification-package.md § 核对 KJB 并联合并](references/data-verification-package.md#核对-kjb-并联合并)

### 3.3.2 数据核对差异分析（强制）

**触发条件**（满足任一即适用）：
- 用户问某个迁移 KTR **迁移后为什么记录不一样**、源/目标 COUNT 对不上、核对作业报错或差异非 0
- 用户要求分析 `check_{name}.ktr` 执行结果中源数据与目标数据的差异原因

**必须执行** [data-verification-analysis.md](references/data-verification-analysis.md) 中的五步流程，并按该文档 **「对用户输出模板」** 组织结论，不得只复述核对 SQL。

**核心原则**：
1. 先读**迁移** `{name}.ktr` 的真实落库路径（FilterRows、Mapping、Upsert 主键、是否 Truncate），再对比 **check** 脚本
2. 做**数量三角校验**：`raw_source` / `expected_target`（DISTINCT 主键 − 过滤）/ `actual_target`
3. 对每类根因**量化行数**并附**样例**（优先连库：MySQL 用 database-expert；MSSQL 源库 `gts_cicc_sit` 见分析文档）
4. 区分「核对口径问题」与「真实漏迁/多迁」

**标杆案例**：`as_stkcode` — 265 行前导零主键合并 + 1 行价格过滤 = 266 行 gap，目标 219,172 与迁移一致（见分析文档 § 标杆案例）。

### 3.4 数据核对（三类步骤）

**数量核对**：TableInput（源/目标 COUNT）→ JoinRows → ScriptValueMod（diff = source - target）→ SetVariable

**数值核对**（`check_{name}_value.ktr`，无金额语义表）：与金额核对流程相同，但 KJB 条目用「数值」。

- 将源/目标关键字段**转换为可 SUM 的数值表达式**后求和比对（标杆：`check_tsys_user_value.ktr`）
- 源侧示例：日期 `CONVERT(BIGINT, CONVERT(VARCHAR(8), open_date, 112))`、状态码映射为数字、加固定偏移量
- 目标侧示例：`COALESCE(create_date, 0) + COALESCE(user_status, 0) + COALESCE(user_order, 0)`
- JavaScript：`var diff = Number(v_source) - Number(v_target);`；输出字段 `<type>Number</type>`

**金额核对**（`check_{name}_amount.ktr`）：TableInput（源/目标 SUM）→ JoinRows → ScriptValueMod → SetVariable

**明细核对**：TableInput（关键字段）→ SortRows（两侧）→ MergeJoin（LEFT OUTER，源为左表）→ FilterRows（目标侧字段 IS NULL；列名相同时带 `_1` 后缀）→ GroupBy → SetVariable

**明细核对画布布局（强制）**：生成 `check_{name}_detail.ktr` 时必须按 [ktr-detail-layout.md](references/ktr-detail-layout.md) 设置 GUI 坐标。

**数量/金额/数值核对画布布局（强制）**：

- **单目标**（1 源 + 1 目标）：JoinRows 在 **y_mid**，源/目标上下分列 → [§4.1](references/ktr-detail-layout.md#41-单目标1-源--1-目标)
- **双目标 / 双库**（account + trade 嵌套 JoinRows）：主链与源同行，两目标同行，trade 目标与第一处 Join 同列 → [§4.2](references/ktr-detail-layout.md#42-双目标--双库1-源--account--trade嵌套-joinrows)（标杆：`check_as_exchangerate.ktr`，生成器 `compute_dual_join_gui()`）

- **优先 [§0 通用网格布局算法](references/ktr-detail-layout.md#0-通用网格布局算法强制)**（用户已验证）：
  - 基准点 `(100, 100)`；同行同 yloc、同列同 xloc
  - 5 字空档 = **前步名称末尾 → 后步名称开头**（55px）
  - 名称宽度 = **display_width**（汉字 11px、ASCII/半角 6px），禁止 `len×11`
  - **简单双路 MergeJoin**（§0.4）：统一 `S = max(s_i)`，MergeJoin 在 `y_mid`
  - **嵌套多 MergeJoin**（§0.5～§0.6）：按列边界 `s[j]`、从右往左列对齐、MergeJoin 与长支路 Sort 同行；见 [§0.6 经验总结](references/ktr-detail-layout.md#06-布局调整经验总结as_fundrevertjourktr)
- **单源**：两路 TableInput 同列 x[0]，SortRows 同列 x[1]，MergeJoin 及后续在 y_mid 同行
- **多源（含 Append）**：两路 TableInput 汇入 Append → Unique → SortRows src；目标链同列对齐
- **参考样例**：`as_fundrequest.ktr`（简单双路，S=189）、`as_fundrevertjour.ktr`（嵌套多 MergeJoin，§0.6）、`check_as_fundbo_detail.ktr`（多源）

### 3.5 关键一致性规则

**连接名 ≠ 数据库名**
- 连接名（如 `30ACCOUNT`）是 Kettle 标识，不能作表前缀
- ❌ `FROM 30ACCOUNT.as_fundrequest`
- ✅ `FROM as_fundrequest`（库名在 `<database>` 配置）

**核对脚本必须与迁移脚本一致**
1. 先读原始迁移 `.ktr`，再写核对脚本
2. 连接名、变量名（如 `${30_DB_DATABASENAME}`）、`<connection>` 属性完全一致
3. 字段映射按迁移脚本别名（如 `create_time AS curr_datetime` → 目标侧用 `curr_datetime`）
4. 源 SQL 条件、`UNION ALL` 结构、日期参数与迁移脚本完全匹配
5. 目标表用迁移标识过滤存量（如 `batch_no_str = 'Ayers'`）

**核对参数**

| 参数名 | 说明 |
|--------|------|
| p_begin_date / p_end_date | 日期范围 |
| p_increment_flag | 增量标识 |
| p_increment_fundaccount | 增量账号列表 |

### 3.6 迁移脚本更新后同步核对脚本

1. 识别迁移脚本变更（WHERE、字段映射、UNION ALL、参数）
2. 同步 `check_{name}.ktr`（数量）、`check_{name}_amount.ktr`（数值）、`check_{name}_detail.ktr`（明细）、`check_{name}.kjb`（参数默认值）
3. 用 `ElementTree.parse` 预检 XML，再在 Spoon 中验证可打开且执行结果一致；WHERE 含 `<`/`>` 时确认已转义为 `&lt;`/`&gt;`

### 3.7 性能

- 大批量迁移前删非唯一索引，迁移后重建
- TableInput 启用 `STREAM_RESULTS`
- InsertUpdate `commit` 建议 100–1000

### 3.8 字典映射 sub_transform（Excel → KTR）

**触发条件**：用户要求从 `CICC_数据字典映射.xlsx`（或其他 Excel 字典）生成 `sub_transform_*.ktr` 字典映射子转换。

**标准结构：** `映射输入规范` → `值映射` → `映射输出规范`（参考 `sub_transform_RDS_country_code.ktr`）

**生成方式（强制优先）：** 运行 [scripts/gen_sub_transform_mapper.py](scripts/gen_sub_transform_mapper.py)，**禁止**手工逐条编写 ValueMapper 映射条目。

```bash
python scripts/gen_sub_transform_mapper.py \
  --excel "CICC_数据字典映射.xlsx" \
  --sheet "GTS市场代码" \
  --output "sub_transform_GTS_exchange_code.ktr" \
  --trans-name sub_transform_GTS_exchange_code \
  --mapper-name "值映射(GTS->HS 3.0)"
```

**列与命名约定（已验证：`RDS-AC_Category`）：**

| 规则 | 说明 |
|------|------|
| 映射列对 | 用户说明**哪两列**；口述顺序 **先源后目标** → `--src-col` / `--tgt-col` |
| 一 Sheet 多 KTR | 同一 Sheet 可有多对目标列，**每对单独一个** `sub_transform_*.ktr` |
| 文件名 ≤3 列 | 可不带目标字段名，如 `sub_transform_GTS_exchange_code.ktr` |
| 文件名 **>3 列** | **必须**带目标字段名，如 `sub_transform_RDS_ac_catetory_client_type.ktr`、`…_organ_flag.ktr` |

**`target_field` 填充 + 父 Mapping Output（强制 · 已验证）：**

| 规则 | 说明 |
|------|------|
| 子转换 `target_field` | **必须**填 Excel **目标列**表头（如 B 列 `exchange_type` / `Country_Code`）；**禁止** `<target_field/>` 留空覆盖源字段 |
| 父 Mapping Output | `parent` = 子转换 `target_field`，**禁止**再走「源字段 → 业务名」改名（如勿写 `GTS_Exchange_Code → exchange_type`） |
| 连带同步 | 改/生成子转换后，业务目录（如 `CICC/`）下凡引用该 `sub_transform_*.ktr` 的父 Mapping Output **一并改** |

- 默认空格 ` ` 作无匹配默认值；RDS 国家码可用 `--non-match-default "N/A"`
- Excel 更新后按列对重新跑脚本同步各 KTR

完整规范见 [dict-mapping-sub-transform.md](references/dict-mapping-sub-transform.md) **§1.1～§1.2**、§2～§5


### 3.8.1 JS / SQL CASE 市场代码 → Mapping 替换（强制）

**触发条件**（满足任一）：
- 用户要求将迁移 KTR 中 JavaScript `exMap` 市场转换统一为 `sub_transform_GTS_exchange_code.ktr`
- 用户要求将 SQL 内 `CASE … HKEX→HK / SHA→C1 / SZA→C2` 改为子映射（模式 B → 模式 A）

**改造要点（已验证 `ac_stkcode.ktr`、`ac_accmarket_trans.ktr`、`hs_original/entrust/realtime/tradelog.ktr`）：**

1. 删除 `ScriptValueMod` 中的 `exMap` **或**删除 SQL 内市场 CASE，改为 Mapping 步骤 `市场 映射 (子转换)`
2. **TableInput SQL 直接别名**：`EXCHANGE_CODE AS GTS_Exchange_Code` 或 `SUBSTRING(product_id,…) AS GTS_Exchange_Code`（**禁止**用 SelectValues 把旧名改成 `GTS_Exchange_Code`）
3. Mapping input：`GTS_Exchange_Code` ↔ `GTS_Exchange_Code`（必须同名，否则报 `Unable to find mapped value`）
4. Mapping output：`exchange_type` → `exchange_type`（`parent` = 子转换 `target_field`；**禁止** `GTS_Exchange_Code → exchange_type`）
5. 同步 `row-meta`、ExcelOutput、ReplaceNull、FilterRows hop
6. **`字段选择` 分两种位置**（先看 hops 再改）：
   - **模式 A**（Mapping 在后）：`字段选择` 只保留 `exchange_type`，丢弃 `GTS_Exchange_Code`
   - **模式 B**（Mapping 在前，如 `ac_accmarket_trans`）：`字段选择` 保留 `GTS_Exchange_Code` 传给 Mapping；Meta 用上游小写字段名（`org_id`→`ORG_ID` 等），**不要**写 `exchange_type`
7. Kettle 字段名**不区分大小写**；`exchange_type` 与 InsertUpdate 的 `EXCHANGE_TYPE` 无需额外转换（若 Output child 已用 `EXCHANGE_TYPE`，保持 `exchange_type → EXCHANGE_TYPE` 即可）
8. **GUI 布局（强制）**：新 Mapping 夹在 `*_trans → 下游` 之间，按 [ktr-detail-layout.md §7.5](references/ktr-detail-layout.md#75-向已有主链插入步骤时的-gui-摆放强制--已验证) 计算坐标；**禁止**写死 `(240,160)` 等无关坐标；改完后运行 `check_ktr_gui_layout.py`

待改造清单与完整 XML 见 [dict-mapping-sub-transform.md §6](references/dict-mapping-sub-transform.md#6-父转换接入-mappingjs-exmap--sub_transform-替换)

### 3.8.2 修改 .ktr 后的 GUI 布局检查（强制）

**触发条件**：凡新建或修改任意 `.ktr`（增删步骤、改 `<order>` hop、插入 Mapping/JS/Filter 等），交付前**必须**执行本检查。

**必须执行：**

1. 按 [ktr-detail-layout.md §7](references/ktr-detail-layout.md#7-迁移-ktr-画布布局spoon-gui)（迁移）或 §0～§6（核对）摆放 `<GUI>`
2. 向已有主链**插入**步骤时，严格执行 [§7.5](references/ktr-detail-layout.md#75-向已有主链插入步骤时的-gui-摆放强制--已验证)：`N.yloc=A.yloc`，`N.xloc` 为 A/B 中点；间距不足则下游右移
3. 运行布局检查脚本（有 ERROR 不得交付）：

```bash
py -3 "%USERPROFILE%\.cursor\skills\kettle-etl-expert\scripts\check_ktr_gui_layout.py" "<改过的.ktr或目录>" -r
```

**禁止：** 只改 hop/SQL 不改 GUI；新步骤复制模板固定坐标；插入步出现在上游左侧或错行。

### 3.9 数据迁移清理脚本更新（强制）

**触发条件**（满足任一即适用）：
- 用户说「**更新数据迁移清理脚本**」「同步清理 SQL」「根据 ktr 更新清理脚本」
- 新增/删除迁移 `.ktr` 后需同步清理表清单

**交付物（固定 2 个文件，与 Kettle 脚本同目录）：**

| 文件 | 范围 |
|------|------|
| `数据迁移清理脚本(MySQL).sql` | `ufg_query` / `ufg_account` / `ufg_trade` / `hsuf` |
| `数据迁移清理脚本(UFTMDB).sql` | 仅 `ufg_trade`（无 schema 前缀） |

**执行流程：**

```
确定根目录 → 递归遍历全部 .ktr/.kjb → 提取写入目标表 → 分组写 SQL → 更新生成日期
```

1. **根目录**：用户指定路径；未指定时使用 Kettle 脚本所在目录（如 `D:\DataMigration_WorkSpace\CICC_Kettle_Scripts`）
2. **必须先运行** [scripts/extract_migration_tables.py](scripts/extract_migration_tables.py)（禁止凭记忆维护表清单）：

```bash
py -3 scripts/extract_migration_tables.py "<Kettle根目录>" --grouped
```

3. **提取范围**：步骤 `InsertUpdate` / `TableOutput` / `ExecSQL`；**排除** `check_*`、`sub_transform_*`、测试/分析脚本（见 [cleanup-scripts.md](references/cleanup-scripts.md)）
4. **跨 schema 写入**：`InsertUpdate` 的 `<lookup><schema>` 优先于 connection 的 database（如 `30ACCOUNT` 连接写 `ufg_trade.as_stockbo`）
5. **同步原则**：以 ktr/kjb 为准增删表；双库同表（如 `ac_clientbcan`）两侧都要清理；`temp_*` 不进清理脚本
6. **SQL 策略**：整表迁移用 `TRUNCATE`；带 Ayers 标识用 `DELETE WHERE …`（字段见 [cleanup-scripts.md §清理 SQL 写法](references/cleanup-scripts.md#清理-sql-写法)）
7. **保留 MySQL 脚本业务分区**（前台历史 / 客户信息 / 资金持仓 / 系统基础 / 费用套餐），更新文件头生成日期

完整规范、Ayers 标识表、检查清单见 [cleanup-scripts.md](references/cleanup-scripts.md)

### 3.10 目标表文档输出（强制）

**触发条件**（满足任一即适用）：
- 用户说「**根据 Kettle 脚本输出/更新到文档**」「生成目标表索引」「表结构文档」
- 需要从 `.ktr` 汇总写入目标表，并结合本地 MySQL 输出字段清单
- 用户说「**更新文档表结构**」「同步库结构」「刷新表字段」——本地 MySQL 表结构已变更，仅需同步二层文档字段清单

**交付物（两层文档，输出到 `{Kettle根目录}/docs/`）：**

| 文件 | 说明 |
|------|------|
| `index.md` | 第一层索引：按脚本**子目录**分类，每目录一张表（数据库、表名、字段详情、**CICC迁移映射**链接） |
| `tables/*.md` | 第二层详情：单库 `{schema}__{table}.md`；多库同名 `{table}.md` |
| `cicc_migration/*.md` | CICC 迁移映射二层文档（由 tables 派生，无关联系统信息，无类型列） |
| `_multi_schema_diff_report.md` | 同名多库差异汇总（辅助） |

**索引规则：**
- **不展示** KJB/KTR 引用关系
- 同表名跨多库 → **一行**，数据库列顿号连接，**一个**字段详情链接
- 多库同名表 **强制合并** 为 `{table}.md`

**二层文档章节（tables）：** 关联脚本 → **关联系统信息**（微服务、接口、菜单路径、页签，值留空供手工补充）→ 表字段

**CICC 迁移映射（cicc_migration）：** 由 tables 自动派生；无「关联系统信息」；章节顺序为：**关联脚本 → 迁移说明 → 表字段**；表字段列为：序号 | 字段名 | **迁移数据** | **备注**

**「迁移说明」章节（强制）：** 分析关联 KTR 的 TableInput `WHERE` 过滤逻辑，插在「表字段」之前。见 [cicc-migration-notes.md](references/cicc-migration-notes.md)。

**过滤条件说明格式（强制）：**

- SQL 代码块下方，用 **1、2、3… 编号列表** 逐条说明各过滤条件的影响
- **禁止**每条都加「**说明**：」前缀
- **禁止**写「取数来源」（源表名、TableInput 步骤名）
- 脚本注释中的业务原因写在编号列表之后的 **原因** 行

| 标杆 | 场景 |
|------|------|
| `ufg_account__ac_team.md` | 单条业务过滤 |
| `hsuf__tsys_user.md` | 多条业务过滤（编号 1、2、3） |

填充命令：

```bash
python scripts/fill_cicc_migration_notes.py --kettle-root "<Kettle根目录>" --docs-dir "<cicc_migration目录>"
```

**「迁移数据」列填写规范（强制）：** 见 [cicc-migration-data-column.md](references/cicc-migration-data-column.md)。标杆样例：

| 场景 | 标杆文档 |
|------|----------|
| 单源直映 / CASE / 常量 | `docs/cicc_migration/ufg_account__ac_broker.md` |
| UNION 双分支 + MergeJoin + CheckSum/JS | `docs/cicc_migration/ac_clientbcan.md`（规则见 [cicc-migration-ac-clientbcan.md](references/cicc-migration-ac-clientbcan.md)） |
| 嵌套视图 + MergeJoin + 大写 InsertUpdate | `ufg_account__as_stockbo.md`（规则见 [cicc-migration-as-stockbo.md](references/cicc-migration-as-stockbo.md)） |
| 多 KTR 顺序写入（trans + creat） | `ufg_account__as_fundbo.md` |
| 前台历史 UNION/日志/流水（§5） | `ufg_query__hs_entrust.md`（规则见 [cicc-migration-frontend-history.md](references/cicc-migration-frontend-history.md)） |

| 类型 | 写法 |
|------|------|
| 源表直映 | `AE.AE_CODE`（表名.列名，无库名、无「源表」前缀） |
| 脚本常量 | 默认 `'000000'`、默认 `0` |
| 条件映射 | `AE.status='A'` 取 `'0'`，否则 `'1'` |
| UNION 同值 | 各分支同字面量 → 仅写 `默认 '…'`（不展开 UNION/MergeJoin） |
| UNION 异值 / 拼接 | 按场景分述（中华通/港股）；`'前缀' 拼接 列名` |
| UNION 字面量 vs 列 | 逐分支列举，如 `中华通 为 ''；港股.client_acc_id` |
| UNION + MergeJoin | 主路多分支描述优先，不追加辅路同名字段合并 |
| JS/校验转换 | 回溯 ScriptValueMod、CheckSum 等中间步骤（如 CRC32 + JS 取模） |
| 脚本未映射 | 迁移数据=MySQL 表结构默认值；备注=**脚本未映射** |

填充命令：`python scripts/fill_cicc_migration_logic.py --root "<Kettle根目录>" [--cicc-dir "<cicc_migration>"] [--tables-dir "<tables>"]`

**二层表字段列（tables）：** 序号 | 字段名 | 类型 | 系统字段名称 | 注释（**系统字段名称**来自 `数据迁移字段说明.xlsx` 各 sheet 的 Field→字段含义，**纯文本**；**注释**列留空；**禁止**默认值、可空、键、单独「库间差异」列）

**多库差异内联标注**（以 ufg_account 为主）：字段缺失标在字段名 `（trade无）`；类型差异标在类型 `（trade: varchar(32)）`；键差异标在键 `（trade: 无）`

**执行流程（两种模式，按场景选用）：**

| 场景 | 命令 | 说明 |
|------|------|------|
| **首次生成 / Kettle 脚本变更** | `gen_target_table_schema_doc.py --root "<根目录>"` | 全量生成 index + tables；会重置关联系统信息、系统字段名称、注释 |
| **仅同步库结构**（常用） | 同上 + **`--refresh-fields-only`** | 只刷新 `tables/*.md` 的「表字段」节；**保留**关联系统信息、手工系统字段名称、手工注释；并同步 `cicc_migration/` |
| **仅同步 CICC 映射** | 同上 + **`--sync-cicc-only`** | 由 tables 派生 `cicc_migration/` 并修补 index 的 CICC迁移映射 列 |

```
确定 Kettle 根目录 → 判断全量 or 仅同步库结构 → 运行脚本 → 校验 tables
```

1. **根目录**：用户指定；未指定时用当前 Kettle 项目目录
2. **全量生成**（新文档、KTR 目标表有增减）：

```bash
python scripts/gen_target_table_schema_doc.py --root "<Kettle根目录>"
```

3. **仅同步库结构**（用户说「更新文档表结构」等，且 docs 已存在）：

```bash
python scripts/gen_target_table_schema_doc.py --root "<Kettle根目录>" --refresh-fields-only
```

   - 查询本地 MySQL `INFORMATION_SCHEMA.COLUMNS`，重写各文档 `## 表字段` 表格（字段名、类型、库间差异）
   - **保留**：`## 关联脚本`、`## 关联系统信息`、已填写的**系统字段名称**与**注释**
   - 新增字段：系统字段名称从 Excel 映射填充（无则留空），注释留空
   - 同步更新文档头部「生成时间」及多库差异说明

4. **字段来源**：查询本地 MySQL `INFORMATION_SCHEMA.COLUMNS`（配合 **database-expert** skill 的 `mysql-local` 连接）
5. **目标表提取**（仅全量模式）：KTR 中 `TableOutput` / `InsertUpdate` + 目标连接（`30ACCOUNT`→ufg_account、`30_TRADE`→ufg_trade 等）；排除 `ayers`/`gts`/`rds` 源库连接
6. **目录归类**（仅全量模式）：按 KTR 所在父目录（相对根目录）汇总

完整规范见 [target-table-docs.md](references/target-table-docs.md)

---

## 4. 常见问题

### 4.1 KTR 无法在 Spoon 打开

| 原因 | 修复 |
|------|------|
| `<info>` 不完整 | 补全 log、maxdate、size_rowset |
| connection 缺 attributes | 补 STREAM_RESULTS、PORT_NUMBER 等 |
| ExcelOutput 非标准元素 | 仅用标准结构，去掉 `excel_write_field_*` |
| FilterRows 引用不存在步骤 | 添加 Dummy 步骤或修正引用 |
| `<order>` hop 缺失 | 补全所有步骤连接 |
| SQL 中 `>`/`<` 未转义 | 用 `&gt;`/`&lt;`（见下） |

#### SQL 在 XML 中的转义（强制）

KTR/KJB 的 `<sql>...</sql>` 是 XML 文本节点，比较运算符必须转义，否则 Spoon 报 **not well-formed (invalid token)** 且无法打开。

| SQL 原文 | XML 中写法 |
|---------|-----------|
| `LEN(col) < 6` | `LEN(col) &lt; 6` |
| `amount > 0` | `amount &gt; 0` |
| `a < b AND c > d` | `a &lt; b AND c &gt; d` |

**典型症状**：`line N, column 52` 附近报错，对应 `LEN(user_code) < 6` 中的裸 `<`。

**与迁移脚本对齐**：生成核对脚本时，WHERE 条件应**逐字复制**迁移 KTR 中已转义的 SQL，不要从 Python/文档里粘贴未转义的 `<`。标杆对比：

- 迁移：`tsys_user.ktr` → `LEN(user_code) &lt; 6`
- 核对：`check_tsys_user*.ktr` → 同上

**Python 生成器写法**：

```python
USERS_WHERE = "and (LEN(user_code) &lt; 6 or ISNUMERIC(user_code)=0)"

def xml_escape_sql(sql: str) -> str:
    return sql.replace("&", "&amp;").replace("<", "&lt;").replace(">", "&gt;")
```

写入 `<sql>` 前对 SQL 做转义；读取 KTR 分析时用 `&lt;` → `<` 反向还原。

#### 从模板派生核对脚本的常见陷阱

以 `check_as_fundrequest*` 为模板生成 `check_{name}*` 时，除 SQL 转义外还需检查：

| 陷阱 | 表现 | 修复 |
|------|------|------|
| 连接只替换第一处 | detail/amount 仍引用 `30ACCOUNT` | `re.sub(..., count=0)` 或全局 `replace` 所有 `<connection>` |
| SortRows 残留模板字段 | 仍含 `init_date`/`txn_reference` | 按迁移主键重写排序字段，删除多余 `<field>` |
| MergeJoin 键未改 | `keys_2` 仍为 `txn_reference` | 源键=源明细字段，目标键=目标明细字段（如 `user_code`↔`user_id`） |
| FilterRows 左值未改 | 仍引用 `txn_reference_1` 或误写 `user_id_1` | 改为目标侧实际列名；列名相同时带 `_1`，不同时用原名（见下表） |
| row-meta 未清理 | TableInput 元数据仍是模板列 | 重写 `<row-meta>` 与 SELECT 列一致 |

#### 明细核对 FilterRows 字段名规则

MergeJoin 之后，FilterRows 用 `目标侧字段 IS NULL` 筛出「源有、目标无」的记录。字段名取决于 Join 后**实际输出列名**，不是 Join 键名：

| 左右 SELECT 列名 | MergeJoin 后目标列 | FilterRows leftvalue |
|-----------------|-------------------|---------------------|
| 相同（如都是 `txn_reference`） | `txn_reference_1` | `txn_reference_1` |
| 不同（如 `user_code` / `user_id`） | `user_id`（无后缀） | `user_id` |

**运行时报错**：`Fields 'xxx_1' used in the condition are not found in input from previous steps` → 说明 FilterRows 引用了不存在的列，通常是误加了 `_1`。对照两侧 TableInput 的 SELECT 列名修正。

已验证案例：`check_tsys_user.ktr` / `check_tsys_user_detail.ktr` / `check_tsys_user_value.ktr`（`1.系统基础信息/操作员/`）。

### 4.2 生成后检查清单

- [ ] XML 合法、标签闭合（可用 `xml.etree.ElementTree.parse` 预检）
- [ ] 所有引用步骤已定义
- [ ] `<order>` hop 完整
- [ ] connection attributes 完整
- [ ] ExcelOutput 为标准结构
- [ ] SQL 特殊字符已转义（重点检查 `<`、`>`、`&`）
- [ ] 核对脚本连接名/变量/字段与迁移脚本一致
- [ ] 从模板派生时 SortRows / MergeJoin / FilterRows 无残留模板字段
- [ ] 明细 FilterRows 的 `leftvalue` 与 MergeJoin 实际输出列名一致（列名不同时不加 `_1`）
- [ ] 明细核对 KTR GUI 符合 [ktr-detail-layout.md §0～§6](references/ktr-detail-layout.md)（display_width、名称间距 5 字、hop 无交叉）
- [ ] 迁移 KTR 布局符合 [§0、§0.6、§7 / §7.5](references/ktr-detail-layout.md#7-迁移-ktr-画布布局spoon-gui)（插入步夹在上下游之间；禁止写死无关坐标）
- [ ] 已对改动过的 `.ktr` 运行 [check_ktr_gui_layout.py](scripts/check_ktr_gui_layout.py)，无 ERROR
- [ ] 在 Spoon 中打开验证后可正常运行

---

## 5. 文件生成规则

1. **KJB**：根节点 `<job>`，含 `<name>`、`<parameters>`、`<entries>`、`<hops>`
2. **KTR**：根节点 `<transformation>`，含 `<info>`、`<connection>`、`<step>`、`<order>`
3. **变量**：`${variable_name}`
4. **相对路径**：`${Internal.Entry.Current.Directory}`
5. **编码**：UTF-8；缩进 2 空格
6. **输出位置**：
   - **交付物**（`.ktr` / `.kjb`）：写入迁移脚本所在目录（与 `{name}.ktr` 同目录）
   - **辅助生成器**（`gen_*.py` 等临时/复用脚本）：写入 `kettle-etl-expert/scripts/`，**禁止**散落在 Kettle 脚本目录根层级（遵循 Cursor general-rule）
7. **核对脚本前置**：生成核对脚本前必须先读取原始迁移脚本的连接定义与字段映射
8. **核对完整交付**：为迁移脚本实现数据核对时，必须同时生成 3 个 check KTR + 1 个 `check_{name}.kjb`（见 3.3）
9. **核对 KJB 合并**：用户要求合并多个 `check_{name}.kjb` 时，按 3.3.1 生成统一 `{业务域}迁移结果核对.kjb`，保留原单表 KJB（见 3.3.1）
10. **验证**：生成后先用 XML 解析器预检，再在 Spoon 中打开验证；核对脚本特别注意 SQL 中 `<`/`>` 已转义（见 [§4.1](SKILL.md#41-ktr-无法在-spoon-打开)）
11. **明细 KTR 布局**：含 MergeJoin / Append 的核对 KTR 必须遵循 [ktr-detail-layout.md §1～§6](references/ktr-detail-layout.md)
11a. **数量/金额/数值 KTR 布局**：单目标 JoinRows 见 [§4.1](references/ktr-detail-layout.md#41-单目标1-源--1-目标)；双库嵌套 JoinRows 见 [§4.2](references/ktr-detail-layout.md#42-双目标--双库1-源--account--trade嵌套-joinrows)（`compute_dual_join_gui()`）
12. **字典映射 KTR**：从 Excel 生成 sub_transform 时运行 [scripts/gen_sub_transform_mapper.py](scripts/gen_sub_transform_mapper.py)；`target_field` **必须**填目标列表头（禁止留空）；用户指定列对（先源后目标）；Sheet **>3 列**时文件名必须带**目标字段名**；改子转换后同步父 Mapping Output（`parent`=`target_field`，见 [dict-mapping-sub-transform.md](references/dict-mapping-sub-transform.md) §1.1～§1.2）
13. **JS / SQL CASE 市场映射替换**：按 [dict-mapping-sub-transform.md §6](references/dict-mapping-sub-transform.md#6-父转换接入-mappingjs-exmap--sub_transform-替换) 与 [§3.8.1](SKILL.md#381-js--sql-case-市场代码--mapping-替换强制) 接入 Mapping；SQL 直接别名 `GTS_Exchange_Code`；Output 用 `exchange_type → exchange_type`；**先判断** `字段选择` 在 Mapping 前还是后；**GUI 按 §7.5**
14. **迁移 KTR 布局（强制检查）**：增删步骤后同步 `<GUI>`；插入步按 [§7.5](references/ktr-detail-layout.md#75-向已有主链插入步骤时的-gui-摆放强制--已验证)；含 MergeJoin 时按 [§0](references/ktr-detail-layout.md#0-通用网格布局算法强制)。交付前运行 [check_ktr_gui_layout.py](scripts/check_ktr_gui_layout.py)，有 ERROR 不得交付（见 [§3.8.2](SKILL.md#382-修改-ktr-后的-gui-布局检查强制)）
15. **清理脚本同步**：用户要求更新数据迁移清理脚本时，按 [§3.9](SKILL.md#39-数据迁移清理脚本更新强制) 遍历目录下全部 `.ktr`/`.kjb` 并更新两个 `.sql` 文件
16. **目标表文档**：按 [§3.10](SKILL.md#310-目标表文档输出强制) 处理——**首次/KTR 变更**用全量生成；用户说「**更新文档表结构**」「同步库结构」时用 `--refresh-fields-only` 仅同步表字段，保留手工内容
17. **迁移说明**：按 [cicc-migration-notes.md](references/cicc-migration-notes.md) 分析 KTR 取数过滤，运行 [scripts/fill_cicc_migration_notes.py](scripts/fill_cicc_migration_notes.py) 填充 `cicc_migration/`「迁移说明」章节；过滤条件说明用 **1、2、3 编号列表**，禁止每条加「说明」前缀（标杆：`hsuf__tsys_user.md`）
18. **核对差异分析**：用户问迁移后记录数不一致时，按 [data-verification-analysis.md](references/data-verification-analysis.md) 五步流程连库量化根因，并按文档输出模板回复；标杆 `as_stkcode`（265 前导零合并 + 1 价格过滤）

## 6. Scripts

| 脚本 | 用途 |
|------|------|
| [scripts/extract_migration_tables.py](scripts/extract_migration_tables.py) | 递归扫描 ktr/kjb，提取迁移目标表（`--grouped` 按库分组） |
| [scripts/gen_sub_transform_mapper.py](scripts/gen_sub_transform_mapper.py) | 从 Excel 字典 Sheet（指定源列→目标列）生成 `sub_transform_*.ktr`；支持一 Sheet 多对映射 |
| [scripts/gen_target_table_schema_doc.py](scripts/gen_target_table_schema_doc.py) | 解析 KTR 目标表 + 查本地 MySQL，生成 `docs/index.md` 与 `docs/tables/`；`--refresh-fields-only` 仅同步库结构 |
| [scripts/fill_cicc_migration_logic.py](scripts/fill_cicc_migration_logic.py) | 分析 KTR 迁移逻辑，填充 `cicc_migration/` 文档「迁移数据」列（含 UNION 分支、场景别名、MergeJoin 优先规则、嵌套视图解析、大小写无关列匹配、多 KTR 合并） |
| [scripts/apply_frontend_history_migration.py](scripts/apply_frontend_history_migration.py) | 前台历史 §5 共 8 表专用：业务抽象填充「迁移数据」（禁止照搬 SQL）；跳过 hs_entrust 手工标杆 |
| [scripts/fill_cicc_migration_notes.py](scripts/fill_cicc_migration_notes.py) | 分析 KTR 取数过滤，填充 `cicc_migration/` 文档「迁移说明」章节 |
| [scripts/check_ktr_gui_layout.py](scripts/check_ktr_gui_layout.py) | **强制**：检查 .ktr Spoon GUI（重叠、同行 hop 回绕、插入步是否夹在上下游之间）；改 ktr 后必跑 |
| [scripts/layout_ktr_gui.py](scripts/layout_ktr_gui.py) | 按 §0 网格规则批量更新 KTR 的 `<GUI>` 坐标；含 2 个 MergeJoin 自动走 `fundrevertjour`（§0.5～§0.6）（若仓库中存在） |
| [scripts/gen_check_as_exchangerate.py](scripts/gen_check_as_exchangerate.py) | 从 `check_as_fundrequest*` 模板生成 `check_as_exchangerate*` 核对四件套（第三类用 `_value`/「数值」，见命名规则）；双库布局见 `compute_dual_join_gui()`（[§4.2](references/ktr-detail-layout.md#42-双目标--双库1-源--account--trade嵌套-joinrows)） |
| [scripts/gen_check_tsys_user.py](scripts/gen_check_tsys_user.py) | 从 `check_as_fundrequest*` 模板生成 `check_tsys_user*` 核对四件套（`--output-dir` + `--template-dir`） |
| [scripts/gen_check_custodian_broker.py](scripts/gen_check_custodian_broker.py) | 托管商券商经纪人信息 4 表核对 + 合并 KJB |
| [scripts/gen_check_security_info.py](scripts/gen_check_security_info.py) | 证券信息 5 表核对 + 合并 KJB（含 GTS 映射、参数 `${en_exchange_type}`） |
| [references/data-verification-analysis.md](references/data-verification-analysis.md) | **迁移后源/目标记录数不一致**时的根因分析流程与输出模板 |
| [scripts/templates/sub_transform_info.xml](scripts/templates/sub_transform_info.xml) | 子转换 `<info>` 块 XML 模板 |

依赖：`pip install openpyxl`