一、相关说明
-
映射组件说明:
映射是用来定义子转换,便于封装和重用。主要组件有映射(子转换)、映射输入规则、映射输出规则等。 -
映射(子转换)组件说明:
映射(子转换)是用来配置子转换,对子转换进行调用的一个步骤。 -
映射输入规则组件说明:
映射输入规范是输入字段
,由调用的转换
输入。 -
映射输出规则组件说明:
映射输出规范是向调用的转换输出所有列,不做任何处理。 -
需求说明:
从数据库中的edu_course表读取数据,根据teacher_id查询edu_teacher表,获取用户信息,把结果保存到Excel。 -
测试数据源如下:需要读者在MySQL中执行如下SQL语句初始化数据
DROP TABLE IF EXISTS `edu_course`; CREATE TABLE `edu_course` ( `id` char(19) NOT NULL COMMENT '课程ID', `teacher_id` char(19) NOT NULL COMMENT '课程讲师ID', `subject_id` char(19) NOT NULL COMMENT '课程专业ID', `subject_parent_id` char(19) NOT NULL COMMENT '课程专业父级ID', `title` varchar(50) NOT NULL COMMENT '课程标题', `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '课程销售价格,设置为0则可免费观看', `lesson_num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '总课时', `cover` varchar(255) NOT NULL COMMENT '课程封面图片路径', `buy_count` bigint(10) unsigned NOT NULL DEFAULT '0' COMMENT '销售数量', `view_count` bigint(10) unsigned NOT NULL DEFAULT '0' COMMENT '浏览数量', `version` bigint(20) unsigned NOT NULL DEFAULT '1' COMMENT '乐观锁', `status` varchar(10) NOT NULL DEFAULT 'Draft' COMMENT '课程状态 Draft未发布 Normal已发布', `is_deleted` tinyint(3) DEFAULT NULL COMMENT '逻辑删除 1(true)已删除, 0(false)未删除', `gmt_create` datetime NOT NULL COMMENT '创建时间', `gmt_modified` datetime NOT NULL COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_title` (`title`), KEY `idx_subject_id` (`subject_id`), KEY `idx_teacher_id` (`teacher_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT COMMENT='课程'; /*Data for the table `edu_course` */ insert into `edu_course`(`id`,`teacher_id`,`subject_id`,`subject_parent_id`,`title`,`price`,`lesson_num`,`cover`,`buy_count`,`view_count`,`version`,`status`,`is_deleted`,`gmt_create`,`gmt_modified`) values ('1330124425118449666','1323301279040057345','1178214681504444418','1178214681483472898','Hadoop从入门到精通','120.00',80,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/21/fe9e2b556bca4fc4a07f2d46a5a0e914Hadoop.jpg',0,4,1,'Normal',NULL,'2020-11-21 20:22:30','2020-12-07 03:35:36'),('1330128144329441282','1323301279040057345','1178214681529610242','1178214681483472898','Spark入门到精通','110.00',80,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/21/08260d45cce6422cabbbe22d8ffcd424spark.jpg',0,24,1,'Normal',NULL,'2020-11-21 20:37:17','2020-12-07 18:03:44'),('1335870900815859714','1335868673288114178','1178214681139539969','1178214681118568449','计算机专业英语基本','900.00',70,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/8e3292d0b0c34f87bf5c4a5d3f65b6fcC语言.jpg',0,8,1,'Draft',NULL,'2020-12-07 16:56:57','2020-12-07 21:37:27'),('1335886692483665921','1323301279040057345','1178214681428946945','1178214681399586817','高等数学三','100.00',80,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/0ab8c396d71e427d8a04d39a940dcb00下载.jpg',0,2,1,'Normal',NULL,'2020-12-07 17:59:42','2020-12-07 21:31:30'),('1335915817915191297','1323301279040057345','1335914886087643138','1335914886049894402','《大学英语B》','100.00',60,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/331363e28a154c148a8ec8490d28e094大学英语B.jpg',0,0,1,'Normal',NULL,'2020-12-07 19:55:26','2020-12-07 19:59:39'),('1335917543426691074','1323301279040057345','1335914886087643138','1335914886049894402','《计算机应用基础》','70.00',80,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/54daea0ca6d0427692fd234c2783fb4d计算机应用基础.jpg',0,3,1,'Normal',NULL,'2020-12-07 20:02:17','2020-12-07 22:08:32'),('1335918198325317633','1323301279040057345','1335914886087643138','1335914886049894402','语文','120.00',80,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/e50d849fca644d4aafacc38ce75f5008语文.jpg',0,0,1,'Normal',NULL,'2020-12-07 20:04:53','2020-12-07 20:08:41'),('1335965309869260801','1189390295668469762','1178214681210843137','1178214681181483010','线性代数','10.00',40,'/static/1.jpg',0,0,1,'Draft',NULL,'2020-12-07 23:12:06','2020-12-07 23:12:06'); /*Table structure for table `edu_teacher` */ DROP TABLE IF EXISTS `edu_teacher`; CREATE TABLE `edu_teacher` ( `id` char(19) NOT NULL COMMENT '讲师ID', `name` varchar(20) NOT NULL COMMENT '讲师姓名', `intro` varchar(500) NOT NULL DEFAULT '' COMMENT '讲师简介', `career` varchar(500) DEFAULT NULL COMMENT '讲师资历,一句话说明讲师', `level` int(10) unsigned NOT NULL COMMENT '头衔 1高级讲师 2首席讲师', `avatar` varchar(255) DEFAULT NULL COMMENT '讲师头像', `sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序', `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '逻辑删除 1(true)已删除, 0(false)未删除', `gmt_create` datetime NOT NULL COMMENT '创建时间', `gmt_modified` datetime NOT NULL COMMENT '更新时间', PRIMARY KEY (`id`), UNIQUE KEY `uk_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='讲师'; /*Data for the table `edu_teacher` */ insert into `edu_teacher`(`id`,`name`,`intro`,`career`,`level`,`avatar`,`sort`,`is_deleted`,`gmt_create`,`gmt_modified`) values ('1','张三','近年主持国家自然科学基金(6项)、江苏省重大科技成果转化项目(5项)、江苏省产学研前瞻性联合研究项目(3项)、省工业科技支撑、省高技术、省自然科学基金等省部级及其企业的主要科研项目40多个,多个项目在企业成功转化,产生了较好的经济、社会和环境效益。积极开展产学研科技合作,并与省内16家企业建立了江苏省研究生工作站,其中6家为江苏省优秀研究生工作站','高级',1,'https://guli-file-190513.oss-cn-beijing.aliyuncs.com/avatar/default.jpg',0,0,'2019-10-30 14:18:46','2019-11-12 13:36:36'),('1189389726308478977','晴天','高级讲师简介','高级讲师资历',2,'https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/10/30/de47ee9b-7fec-43c5-8173-13c5f7f689b2.png',1,0,'2019-10-30 11:53:03','2019-10-30 11:53:03'),('1189390295668469762','李刚','高级讲师简介','高级讲师',2,'https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/10/30/b8aa36a2-db50-4eca-a6e3-cc6e608355e0.png',2,0,'2019-10-30 11:55:19','2019-11-12 13:37:52'),('1189426437876985857','王二','高级讲师简介','高级讲师',1,'https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/11/08/e44a2e92-2421-4ea3-bb49-46f2ec96ef88.png',0,0,'2019-10-30 14:18:56','2019-11-12 13:37:35'),('1189426464967995393','王五','高级讲师简介','高级讲师',1,'https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/10/30/65423f14-49a9-4092-baf5-6d0ef9686a85.png',0,1,'2019-10-30 14:19:02','2019-11-12 13:37:18'),('1192249914833055746','李四','高级讲师简介','高级讲师',1,'https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/11/07/91871e25-fd83-4af6-845f-ea8d471d825d.png',0,0,'2019-11-07 09:18:25','2019-11-12 13:37:01'),('1192327476087115778','1222-12-12','1111','11',1,'https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/11/08/5805c6cd-c8ad-4a77-aafd-d2e083bfd8a4.png',0,1,'2019-11-07 14:26:37','2019-11-11 16:26:26'),('1195337453429129218','test','sdfsdf','sdfdf',1,'https://guli-file-190513.oss-cn-beijing.aliyuncs.com/avatar/default.jpg',0,1,'2019-11-15 21:47:12','2019-11-15 21:47:27'),('1323301279040057345','苏江明','大数据高级讲师','大数据高级讲师',2,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/03/2326f645cccb45a28c62aa26f1f687d5file.png',1,0,'2020-11-03 00:29:45','2020-11-03 00:29:45'),('1323302269290065922','梁江怀','大数据首席讲师','大数据首席讲师',2,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/03/101477ccd8ed42b8b359eba4cc7c2169file.png',1,0,'2020-11-03 00:33:41','2020-11-03 00:33:41'),('1330094056755269633','赵匡胤','高级讲师','高级讲师',1,'https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/21/10a83aba03c54e659a044b1c9d6b3e8efile.png',1,0,'2020-11-21 18:21:50','2020-11-21 18:21:50'),('1335567949326569474','苏江明2','热爱教育,热爱技术,热爱科技,热爱生活','首席大数据讲师',2,NULL,1,0,'2020-12-06 20:53:07','2020-12-06 20:53:07');
二、设计转换
本次实验需要使用两个转换来配合完成,一个作为子转换,一个作为子转换的调用者(父转换),具体如下:
子转换设计
- 映射:映射输入规范
- 查询:数据库查询
- 映射:映射输出规范
调用者(父转换)设计
- 输入:表数据
- 映射:映射(子转换)
- 输出:Excel输出
三、转换配置
-
子转换配置步骤如下
-
Step1:映射输入规范组件配置
-
双击组件,写上步骤名称等
-
相关配置如下所示:
-
Step2:数据库查询组件配置
-
双击打开,设置步骤名称
-
配置信息,如下
-
Step3:输出映射规则组件配置
-
双击打开,设置步骤名称
-
配置信息,如下,
-
Step4:保存子转换,如取名为sub-tran
上述配置好后,点击确定即可!
-
-
调用者(父转换)配置步骤如下
-
Step1:表输入组件配置
-
双击组件,写上步骤名称等
-
相关配置如下所示:
-
Step2:映射 (子转换)组件配置
-
双击组件,写上步骤名称,选择子转换所在位置
-
相关配置如下所示:
-
Step3:Microsoft Excel组件配置
-
双击组件,写上步骤名称等
-
相关配置如下所示:
-
Step4:保存子转换,如取名为mapping-trans
上述配置好后,点击确定即可!
-
四、运行转换
这里运行的时候,只需要运行调用者(父转换)即可
- 点击运行按钮,成功运行如下:
五、结果查看
-
在文件中查看结果
-
Preview data 中查看结果
-
结果分析
该转换完成的功能等价于如下SQL语句,即左外连接SELECT * FROM edu_course c LEFT JOIN edu_teacher t ON c.`teacher_id`=t.`id`;