mysql - 使用 Pentaho Kettle，如何在保持引用完整性的同时从单个表加载多个表？

Question

需要将具有 100,000+ 条记录的单个文件中的数据加载到 MySQL 上的多个表中，维护文件/表中定义的关系；表示关系已经匹配。解决方案需要在最新版本的 MySQL 上运行，并且需要使用 InnoDB 引擎； MyISAM 不支持外键。

我是使用 Pentaho 数据集成(又名 Kettle)的新手，任何指点都将不胜感激。

我可以补充一点，外键约束不被禁用是一个要求。由于我的理解是，如果数据库的引用完整性出现问题，当重新打开外键约束时，MySQL 将不会检查引用完整性。 来源: 5.1.4。服务器系统变量 -- foreign_key_checks

所有方法都应包括一些验证和回滚策略，以防插入失败或无法保持引用完整性。

再次重申，对此完全陌生，如果您有任何问题或要求澄清，请尽我所能提供尽可能多的信息 - 请告诉我。

如果您能够从 kjb 和 ktr 文件(作业/转换)中发布 XML，那就太棒了。甚至可能追查你在任何地方所做的每条评论/答案并投票给他们... :-) ...真的，找到答案对我来说真的很重要。

谢谢!

---

示例数据: 为了更好地举例说明，假设我正在尝试加载一个文件，其中包含员工姓名、他们过去曾占用的办公室以及他们的职位历史记录，由选项卡分隔.

文件:

EmployeeName<tab>OfficeHistory<tab>JobLevelHistory
John Smith<tab>501<tab>Engineer
John Smith<tab>601<tab>Senior Engineer
John Smith<tab>701<tab>Manager
Alex Button<tab>601<tab>Senior Assistant
Alex Button<tab>454<tab>Manager

注意: 单表数据库是完全规范化的(与单表一样多)——例如，在“John Smith”的情况下，只有一个 John Smith；这意味着没有会导致引用完整性冲突的重复项。

MyOffice数据库架构有以下表格:

Employee (nId, name)
Office (nId, number)
JobTitle (nId, titleName)
Employee2Office (nEmpID, nOfficeId)
Employee2JobTitle (nEmpId, nJobTitleID)

所以在这种情况下。表格应如下所示:

Employee
1 John Smith
2 Alex Button

Office
1 501
2 601
3 701
4 454

JobTitle
1 Engineer
2 Senior Engineer
3 Manager
4 Senior Assistant

Employee2Office
1 1
1 2
1 3
2 2
2 4

Employee2JobTitle
1 1
1 2
1 3
2 4
2 3

这是用于创建数据库和表的 MySQL DDL:

create database MyOffice2;

use MyOffice2;

CREATE TABLE Employee (
      id MEDIUMINT NOT NULL AUTO_INCREMENT,
      name CHAR(50) NOT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB;

CREATE TABLE Office (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  office_number INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE JobTitle (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  title CHAR(30) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE Employee2JobTitle (
  employee_id MEDIUMINT NOT NULL,
  job_title_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (job_title_id) REFERENCES JobTitle(id),
  PRIMARY KEY (employee_id, job_title_id)
) ENGINE=InnoDB;

CREATE TABLE Employee2Office (
  employee_id MEDIUMINT NOT NULL,
  office_id MEDIUMINT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES Employee(id),
  FOREIGN KEY (office_id) REFERENCES Office(id),
  PRIMARY KEY (employee_id, office_id)
) ENGINE=InnoDB;

---

---

---

我对选定答案的回应:

准备:

1. (a) 使用示例数据，通过更改 <TAB> 创建 CSV以逗号分隔。
2. (b) 安装 MySQL 并使用 MySQL DDL 示例创建示例数据库
3. (c) 安装 Kettle(它基于 Java，可在任何运行 Java 的设备上运行)
4. (d) 下载 KTR 文件

数据流分步:(我的笔记)

1. 在 Kettle 中打开 KTR 文件，然后双击“CSV 文件输入”并浏览到您创建的 CSV 文件。分隔符应该已经设置为逗号。然后点击确定。
2. 双击“插入员工”并选择数据库连接器，然后按照 Creating a New Database Connection 上的说明进行操作

Answer 1

我整理了一个样本 transformation(right click and choose save link)根据您提供的内容。我觉得有点不确定的唯一步骤是最后一个表格输入。我基本上是将连接数据写入表中，如果特定关系已存在则让它失败。

注意事项:

这个解决方案并没有真正满足“所有方法都应该包括一些验证和回滚策略，以防插入失败或无法保持参照完整性。”标准，尽管它可能不会失败。如果您真的想设置一些复杂的东西，我们可以，但这绝对可以让您进行这些转换。

分步数据流

1. 我们从阅读您的文件开始。在我的例子中，我将它转换为 CSV，但标签也很好。

2. 现在我们将使用组合查找/更新 将员工姓名插入到 Employee 表中。插入后，我们将 employee_id 作为 id 附加到我们的数据流中，并从数据流中删除 EmployeeName。

3. 这里我们只是使用“选择值”步骤将 id 字段重命名为 employee_id

4. 像插入员工一样插入职务，并将职务 ID 附加到我们的数据流中，同时从数据流中删除 JobLevelHistory。

5. 将标题 id 简单重命名为 title_id(参见第 3 步)

6. 插入办公室，获取 ID，从流中删除 OfficeHistory。

7. 将 office id 简单重命名为 office_id(见步骤 3)

8. 将上一步的数据复制到两个流中，值分别为 employee_id,office_id 和 employee_id,title_id。

9. 使用表插入来插入连接数据。我已将其选择为忽略插入错误，因为可能存在重复项并且 PK 约束会使某些行失败。

输出表