Kettle 创建 Transformation

2023-07-25 20:53:16

1.第一步，先准备数据和工具

安装好mysql以及客户端工具

数据：

USE `test`;

CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_name` varchar(50) CHARACTER SET latin1 NOT NULL,
`customer_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `customer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_name` varchar(50) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `trade` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_id` int(11) NOT NULL,
`status` int(11) NOT NULL COMMENT '对内= 1 ，对外= 2',
`amount` decimal(11,2) NOT NULL,
`account_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

CREATE TABLE `trade_detail` (
`customer_name` varchar(50) CHARACTER SET latin1 NOT NULL,
`customer_id` int(11) NOT NULL,
`account_name` varchar(50) CHARACTER SET latin1 NOT NULL,
`account_id` int(11) NOT NULL,
`amount` double DEFAULT NULL,
`description` tinytext
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

以上我们建了四个表，客户customer，账户account，交易记录trade，交易明细表trade_detail

现在插入数据

insert into test.`account` (account_name,customer_id) values("account1",1),("account2",1),("account3",1),("account4",1),("account5",1)；

insert into test.`customer` (customer_name) values("customer1"),("customer2"),("customer3"),("customer4"),("customer5");

insert into test.`trade` (customer_id,status,amount,account_id) values(1,1,100,1),(1,1,200,2),(1,1,240,3),(1,2,320,4),(1,2,500,5);

用下面的sql查询一下得到每个客户下每个账户的交易明细

SELECT * FROM trade t
LEFT JOIN account a ON t.`account_id` = a.`id`
LEFT JOIN customer c ON c.`id` = t.`customer_id`;

2.我们要用kettle完成将这三个表中的数据汇总到trade_detial表中

看下面一步一步的操作：

2.1我们新建一个Transformation

点击保存按钮，保存到e:/test/etl/transfor.ktr 这个后缀是kettle专用的

2.2从左侧选择【输入】->【表输入】，拖拽到右侧的空白区域，双击【表输入】