数据粒度的转换

 业务系统一般存储非常明细的数据，而数据仓库中数据是用来分析的，不需要非常明细的数据。一般情况下，会将业务系统数据按照数据仓库粒度进行聚合，这个过程就被称为数据粒度的转换。例如，将城市转换成省份或者直辖市。

一.案例介绍
        通过Kettle工具将对数据表company进行数据粒度的转换操作，即将数据表company中字段为salesArea的数据都统一成省级份，并存储到新数据表company_new中。

二.数据准备
        A公司的日手机销售情况中的销售区域是市级，而B公司的日手机销售情况中的销售区域是省级，A公司和B公司的日手机销售情况合并后存储在数据表company中，通过仔细观察数据表company中字段为salesArea的一列，发现该列既包含市级也包含省级。数据表company中的部分数据内容，如图所示

 

 建表

company-new表

create table `company_new` (
	`id` int (30),
	`salesArea` varchar (150),
	`brand` varchar (150),
	`model` varchar (150),
	`unitPrice` varchar (150),
	`number` int (30)
); 

temporary表

CREATE TABLE `temporary` (
  `id` int DEFAULT NULL,
  `salesArea` varchar(150) DEFAULT NULL
);

provincial表

CREATE TABLE `provincial` (
  `pid` int(11) NOT NULL,
  `Provincial` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `provincial` */
 
insert  into `provincial`(`pid`,`Provincial`) values (1,'北京市'),(2,'天津市'),(3,'上海市'),(4,'重庆市'),(5,'河北省'),(6,'山西省'),(7,'台湾省'),(8,'辽宁省'),(9,'吉林省'),(10,'黑龙江省'),(11,'江苏省'),(12,'浙江省'),(13,'安徽省'),(14,'福建省'),(15,'江西省'),(16,'山东省'),(17,'河南省'),(18,'湖北省'),(19,'湖南省'),(20,'广东省'),(21,'甘肃省'),(22,'四川省'),(23,'贵州省'),(24,'海南省'),(25,'云南省'),(26,'青海省'),(27,'陕西省'),(28,'广西壮族自治区'),(29,'西藏自治区'),(30,'宁夏回族自治区'),(31,'新疆维吾尔自治区'),(32,'内蒙古自治区'),(33,'澳门特别行政区'),(34,'香港特别行政区');

city表

CREATE TABLE `city` (
  `cid` int(11) NOT NULL,
  `city` varchar(50) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`city`),
  KEY `pid` (`pid`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provincial` (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
/*Data for the table `city` */
 
insert  into `city`(`cid`,`city`,`pid`) values (10,'七台河市',10),(7,'万宁市',24),(2,'三亚市',24),(4,'三明市',14),(12,'三门峡市',17),(1,'上海市',3),(11,'上饶市',15),(8,'东方市',24),(17,'东莞市',20),(5,'东营市',16),(5,'中卫市',30),(18,'中山市',20),(13,'临夏回族自治州',21),(10,'临汾市',6),(13,'临沂市',16),(8,'临沧市',25),(12,'临高县',24),(6,'丹东市',8),(11,'丽水市',12),(6,'丽江市',25),(9,'乌兰察布市',32),(3,'乌海市',32),(21,'乌苏市',31),(1,'乌鲁木齐市',31),(15,'乐东黎族自治县',24),(10,'乐山市',22),(4,'九江市',15),(16,'云林县',7),(21,'云浮市',20),(6,'五家渠市',31),(3,'五指山市',24),(15,'亳州市',13),(13,'仙桃市',18),(7,'伊 春 市',10),(18,'伊宁市',31),(6,'佛山市',20),(9,'佳木斯市',10),(17,'保亭黎族苗族自治县',24),(6,'保定市',5),(4,'保山市',25),(15,'信阳市',17),(5,'儋州市',24),(2,'克拉玛依市',31),(14,'六安市',13),(2,'六盘水市',23),(1,'兰州市',21),(11,'兴安盟',32),(9,'内江市',22),(21,'凉山彝族自治州',22),(2,'包头市',32),(1,'北京市',1),(5,'北海市',28),(3,'十堰市',18),(1,'南京市',11),(11,'南充市',22),(1,'南宁市',28),(7,'南平市',14),(15,'南投县',7),(1,'南昌市',15),(6,'南通市',11),(13,'南阳市',17),(17,'博乐市',31),(2,'厦门市',14),(4,'双鸭山市',10),(22,'台东县',7),(13,'台中县',7),(4,'台中市',7),(8,'台北县',7),(1,'台北市',7),(18,'台南县',7),(5,'台南市',7),(10,'台州市',12),(1,'合肥市',13),(8,'吉安市',15),(2,'吉林市',9),(7,'吐鲁番市',31),(11,'吕梁市',6),(3,'吴忠市',30),(16,'周口市',17),(7,'呼伦贝尔市',32),(1,'呼和浩特市',32),(11,'和田市',31),(11,'咸宁市',18),(4,'咸阳市',27),(10,'哈密市',31),(1,'哈尔滨市',10),(2,'唐山市',5),(14,'商丘市',17),(10,'商洛市',27),(9,'喀什市',31),(17,'嘉义县',7),(7,'嘉义市',7),(4,'嘉兴市',12),(5,'嘉峪关市',21),(3,'四平市',9),(4,'固原市',30),(5,'图木舒克市',31),(3,'基隆市',7),(20,'塔城市',31),(6,'大 庆 市',10),(13,'大兴安岭地区',10),(2,'大同市',6),(13,'大理白族自治州',25),(2,'大连市',8),(4,'天水市',21),(1,'天津市',2),(14,'天门市',18),(1,'太原市',6),(19,'奎屯市',31),(10,'威海市',16),(13,'娄底市',19),(9,'孝感市',18),(9,'宁德市',14),(2,'宁波市',12),(8,'安庆市',13),(9,'安康市',27),(5,'安阳市',17),(4,'安顺市',23),(10,'定安县',24),(11,'定西市',21),(9,'宜兰县',7),(13,'宜宾市',22),(5,'宜昌市',18),(9,'宜春市',15),(3,'宝鸡市',27),(17,'宣城市',13),(12,'宿州市',13),(13,'宿迁市',11),(20,'屏东县',7),(11,'屯昌县',24),(4,'山南地区',29),(6,'岳阳市',19),(14,'崇左市',28),(13,'巢湖市',13),(17,'巴中市',22),(8,'巴彦淖尔市',32),(4,'常州市',11),(7,'常德市',19),(8,'平凉市',21),(4,'平顶山市',17),(7,'广元市',22),(14,'广安市',22),(1,'广州市',20),(10,'庆阳市',21),(13,'库尔勒市',31),(10,'廊坊市',5),(6,'延安市',27),(9,'延边朝鲜族自治州',9),(2,'开封市',17),(7,'张家口市',5),(8,'张家界市',19),(7,'张掖市',21),(14,'彰化县',7),(3,'徐州市',11),(14,'德宏傣族景颇族自治州',25),(14,'德州市',16),(5,'德阳市',22),(9,'忻州市',6),(12,'怀化市',19),(15,'怒江傈傈族自治州',25),(7,'思茅市',25),(17,'恩施土家族苗族自治州',18),(11,'惠州市',20),(1,'成都市',22),(10,'扬州市',11),(8,'承德市',5),(10,'抚州市',15),(4,'抚顺市',8),(1,'拉萨市',29),(20,'揭阳市',20),(3,'攀枝花市',22),(9,'文山壮族苗族自治州',25),(6,'文昌市',24),(7,'新乡市',17),(5,'新余市',15),(11,'新竹县',7),(6,'新竹市',7),(2,'无锡市',11),(5,'日喀则地区',29),(11,'日照市',16),(1,'昆明市',25),(14,'昌吉市　',31),(14,'昌江黎族自治县',24),(3,'昌都地区',29),(5,'昭通市',25),(7,'晋中市',6),(5,'晋城市',6),(2,'景德镇市',15),(2,'曲靖市',25),(6,'朔州市',6),(13,'朝阳市',8),(5,'本溪市',8),(13,'来宾市',28),(1,'杭州市',12),(7,'松原市',9),(7,'林芝地区',29),(6,'果洛藏族自治州',26),(4,'枣庄市',16),(2,'柳州市',28),(2,'株洲市',19),(3,'桂林市',28),(10,'桃园县',7),(12,'梅州市',20),(4,'梧州市',28),(12,'楚雄彝族自治州',25),(8,'榆林市',27),(6,'武威市',21),(1,'武汉市',18),(6,'毕节地区',23),(11,'永州市',19),(7,'汉中市',27),(4,'汕头市',20),(13,'汕尾市',20),(7,'江门市',20),(16,'池州市',13),(1,'沈阳市',8),(9,'沧州市',5),(12,'河池市',28),(14,'河源市',20),(5,'泉州市',14),(9,'泰安市',16),(12,'泰州市',11),(4,'泸州市',22),(3,'洛阳市',17),(1,'济南市',16),(8,'济宁市',16),(18,'济源市',17),(2,'海东地区',26),(3,'海北藏族自治州',26),(5,'海南藏族自治州',26),(1,'海口市',24),(8,'海西蒙古族藏族自治州',26),(3,'淄博市',16),(6,'淮北市',13),(4,'淮南市',13),(8,'淮安市',11),(2,'深圳市',20),(16,'清远市',20),(3,'温州市',12),(5,'渭南市',27),(5,'湖州市',12),(3,'湘潭市',19),(14,'湘西土家族苗族自治州',19),(8,'湛江市',20),(10,'滁州市',13),(16,'滨州市',16),(11,'漯河市',17),(6,'漳州市',14),(7,'潍坊市',16),(15,'潜江市',18),(19,'潮州市',20),(9,'澄迈县',24),(21,'澎湖县',7),(1,'澳门特别行政区',33),(9,'濮阳市',17),(6,'烟台市',16),(8,'焦作市',17),(8,'牡丹江市',10),(9,'玉林市',28),(7,'玉树藏族自治州',26),(3,'玉溪市',25),(3,'珠海市',20),(18,'琼中黎族苗族自治县',24),(4,'琼海市',24),(14,'甘南藏族自治州',21),(20,'甘孜藏族自治州',22),(8,'白城市',9),(6,'白山市',9),(13,'白沙黎族自治县',24),(3,'白银市',21),(10,'百色市',28),(9,'益阳市',19),(9,'盐城市',11),(11,'盘锦市',8),(12,'眉山市',22),(2,'石嘴山市',30),(1,'石家庄市',5),(3,'石河子市　',31),(16,'神农架林区',18),(1,'福州市',14),(3,'秦皇岛市',5),(16,'米泉市',31),(10,'红河哈尼族彝族自治州',25),(6,'绍兴市',12),(12,'绥 化 市',10),(6,'绵阳市',22),(15,'聊城市',16),(10,'肇庆市',20),(2,'自贡市',22),(9,'舟山市',12),(2,'芜湖市',13),(23,'花莲县',7),(5,'苏州市',11),(12,'苗栗县',7),(9,'茂名市',20),(4,'荆州市',18),(8,'荆门市',18),(3,'莆田市',14),(12,'莱芜市',16),(17,'菏泽市',16),(3,'萍乡市',15),(8,'营口市',8),(14,'葫芦岛市',8),(3,'蚌埠市',13),(11,'衡水市',5),(4,'衡阳市',19),(8,'衢州市',12),(6,'襄樊市',18),(11,'西双版纳傣族自治州',25),(1,'西宁市',26),(1,'西安市',27),(10,'许昌市',17),(8,'贵港市',28),(1,'贵阳市',23),(11,'贺州市',28),(18,'资阳市',22),(7,'赣州市',15),(4,'赤峰市',32),(4,'辽源市',9),(10,'辽阳市',8),(15,'达州市',22),(8,'运城市',6),(7,'连云港市',11),(16,'迪庆藏族自治州',25),(5,'通化市',9),(5,'通辽市',32),(8,'遂宁市',22),(3,'遵义市',23),(5,'邢台市',5),(2,'那曲地区',29),(4,'邯郸市',5),(5,'邵阳市',19),(1,'郑州市',17),(10,'郴州市',19),(6,'鄂尔多斯市',32),(7,'鄂州市',18),(9,'酒泉市',21),(1,'重庆市',4),(7,'金华市',12),(2,'金昌市',21),(7,'钦州市',28),(12,'铁岭市',8),(5,'铜仁地区',23),(2,'铜川市',27),(7,'铜陵市',13),(1,'银川市',30),(10,'锡林郭勒盟',32),(7,'锦州市',8),(11,'镇江市',11),(1,'长春市',9),(1,'长沙市',19),(4,'长治市',6),(15,'阜康市',31),(9,'阜新市',8),(11,'阜阳市',13),(6,'防城港市',28),(15,'阳江市',20),(3,'阳泉市',6),(8,'阿克苏市',31),(22,'阿勒泰市',31),(12,'阿图什市',31),(19,'阿坝藏族羌族自治州',22),(12,'阿拉善盟',32),(4,'阿拉尔市',31),(6,'阿里地区',29),(12,'陇南市',21),(16,'陵水黎族自治县',24),(12,'随州市',18),(16,'雅安市',22),(2,'青岛市',16),(3,'鞍山市',8),(5,'韶关市',20),(1,'香港特别行政区',34),(5,'马鞍山市',13),(17,'驻马店市',17),(19,'高雄县',7),(2,'高雄市',7),(5,'鸡 西 市',10),(3,'鹤 岗 市',10),(6,'鹤壁市',17),(6,'鹰潭市',15),(10,'黄冈市',18),(4,'黄南藏族自治州',26),(9,'黄山市',13),(2,'黄石市',18),(11,'黑 河 市',10),(8,'黔东南苗族侗族自治州',23),(9,'黔南布依族苗族自治州',23),(7,'黔西南布依族苗族自治州',23),(2,'齐齐哈尔市',10),(8,'龙岩市',14);

三，建立转换1并添加控件

四，配置转换一控件

表输入控件

 点击控件进入配置界面，在SQL框中编写获取数据表company中所有数据的SQL语句，然后单击【预览】按钮，查看数据表company中数据是否成功从MySQL数据库中抽取到表输入流中，具体如图所示。

 预览结果

2.字段选择控件

   双击“字段选择”控件，进入“选择/改名值”界面，在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id和salesArea，具体如图所示

 3.排序记录控件

双击“排序记录”控件，进入“排序记录”配置界面，在“字段”框中添加字段salesArea，通过以该字段为基础对整体数据进行升序排序，具体如图所示

 4.表输入2控件

点击进入配置界面，在SQL框中编写SQL语句，用于查询city数据表中字段city和pid的数据，用于与数据表company中字段salesArea进行合并连接；单击【预览】按钮，查看city数据表中的字段city和pid的数据是否成功从MySQL数据库中抽取到表输入流中，具体如图所示

预览部分

 

 5.排序记录2控件

双击“排序记录2”控件，进入“排序记录”配置界面，并在“字段”框中添加字段city，以该字段为基础对整体数据进行升序排序，具体如图所示

 6. 双击“记录集连接”控件，进入“合并排序”配置界面，并在“第一个步骤”处的下拉框中选择“排序记录”控件，“第二个步骤”处的下拉框中选择“排序记录2”控件；在“第一个步骤的连接字段”和“第二个步骤的连接字段”处分别添加连接字段，这里添加的连接字段是salesArea和city，将“排序记录”控件流中的数据与“排序记录2”控件流中的数据进行合并连接；在“连接类型”处的下拉框选择连接类型，这里选择的LEFT OUTER，即左外连接，具体如图所示。

 7.字段选择2控件

双击“字段选择2”控件，进入“选择/改名值”界面，并在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id、pid和salesArea，具体如图所示

 8.排序记录3控件

双击“排序记录3”控件，进入“排序记录”配置界面，在“字段”框中添加字段pid，以该字段为基础对整体数据进行升序排序，具体如图所示。

 9.表输入3控件

双击进入控件，在SQL框中编写SQL，用于获取数据表provincial中字段为id和Provincial的数据，后续用于与“排序记录”控件流中的字段数据进行合并连接，然后单击【预览】按钮，查看数据表provincial中字段为id和Provincial的数据是否成功从MySQL数据库中抽取到表输入流中，具体如图所示

点击预览记录

 

10.排序记录4控件

 双击“排序记录4”控件，进入“排序记录”配置界面，并在“字段”框中添加字段pid，以该字段为基础对整体数据进行升序排序，具体如图所示

 11.记录集连接2控件

双击“记录集连接2”控件，进入“合并排序”配置界面，并在“第一个步骤”处的下拉框中选择“排序记录3”控件，“第二个步骤”处的下拉框中选择“排序记录4”控件；在“第一个步骤的连接字段”和“第二个步骤的连接字段”处分别添加连接字段，这里分别添加的连接字段是pid和pid；在“连接类型”处的下拉框选择连接类型，这里选择的LEFT OUTER，即左外连接，在“字段”框中添加字段pid，以该字段为基础对整体数据进行升序排序，具体如图所示

 12.过滤记录控件

双击“过滤记录”控件，进入“过滤记录”配置界面；在“条件”处设置过滤的条件，将字段为Provincial中值为“null”的数据过滤掉；在“发送true数据给步骤：”处的下拉框中选择“空操作”，将Provincial字段值为不为null的数据放在“空操作”控件中；在“发送false数据给步骤：”处的下拉框中选择“空操作（什么也不做）2”，将Provincial字段值为null的数据放在“空操作（什么也不做）2”控件中，具体如图所示

13.字段选择3控件

双击“字段选择3”控件，进入“选择/改名值”界面；在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id和Provincial，用于后续进行输出，具体如图所示。

 14.表输入控件

双击“表输出”控件，单击目标表右侧的【浏览】按钮，指定输出目标表，即数据表temporary；勾选“指定数据库字段”复选框，用于将数据表temporary的字段与字段选择3控件输出流中的字段进行匹配，然后点击“输入字段映射”进行映射匹配，具体如下：

 15.字段选择4控件

双击“字段选择4”控件，进入“选择/改名值”界面，并在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id和salesArea，用于后续进行输出，具体如图所示

16.表输出2控件

 单击目标表右侧的【浏览】按钮，指定输出目标表，即数据表temporary；勾选“指定数据库字段”复选框，用于将数据表temporary的字段与字段选择4控件输出流中的字段进行匹配；单击“数据库字段”选项卡，具体如图所示

 单击【输入字段映射】按钮，弹出“映射匹配”对话框，依次选中“源字段”选项框的字段和“目标字段”选项框对应的字段，再单击【Add】按钮，将一对映射字段添加至“映射”选项框中，若“源字段”选项框的字段和“目标字段”选项框的字段相同，则可以单击【猜一猜】按钮，让Kettle自动实现映射，具体如图所示

 总体配置

 五.运行转换1并查看结果

 

表输出2

 

 temporary

 六.建立转换2并添加控件

 七.配置转换2控件

1.表输入控件

双击进入表输入控件，在SQL框中编写获取数据表temporary中字段id和salesArea的数据的 SQL语句，然后单击【预览】按钮，查看数据表temporary中数据是否成功从MySQL数据库中抽取到表输入流中，如图所示

2.字段选择控件

 双击“字段选择”控件，进入“选择/改名值”界面，并在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id和salesArea，后续用于与数据表company中的数据进行合并，“选择和修改”选项卡的配置如图所示

 3.排序记录控件

双击“排序记录”控件，进入“排序记录”配置界面，并在“字段”框中添加字段id，以此字段为基础对所有数据进行升序排序，如图所示。

 

 4.表输入控件

双击进入表输入2控件，在SQL框中编写SQL语句，用于查询数据表company中的全部数据，然后单击【预览】按钮，查看数据表company中的数据是否成功从MySQL数据库中抽取到表输入流中，如图所示。

 

 5.字段选择2控件

双击“字段选择2”控件，进入“选择/改名值”界面，并在“选择和修改”选项卡的“字段”处手动添加所需字段，这里添加字段id、brand、model、unitPrice、number，后续通过字段id将数据表temporary中字段salesArea和数据表company中的数据进行合并，“选择和修改”选项卡的配置如图所示

 6.排序记录2控件

 双击“排序记录2”控件，进入“排序记录”配置界面，并在“字段”框中添加字段id，以此字段为基础对全部数据进行升序排序，具体如图所示。

 7.记录集连接控件

双击“记录集连接”控件，进入“合并排序”配置界面，并在“第一个步骤”处的下拉框中选择“排序记录”，“第二个步骤”处的下拉框中选择“排序记录2”；在“第一个步骤的连接字段”和“第二个步骤的连接字段”处添加连接字段，这里添加的连接字段是id和id，用于将“排序记录”控件流中的数据与“排序记录2”控件中的数据合并连接；在“连接类型”处的下拉框选择连接类型，这里选择的FULL OUTER，即完全外连接，具体如图所示

 8.字段选择3控件

双击“字段选择3”控件，进入“选择/改名”配置界面，并在“选择和修改”选项卡的“字段”处手动添加所需字段，由于源数据表company中包含字段id、salesArea、brand、model、unitPrice、number，因此需要获取“记录集连接”控件流中的字段id、salesArea、brand、model、unitPrice、number，用于与源数据表company中的字段相对应，具体如图所示

 9.表输出控件

双击进入控件，单击目标表右侧的【浏览】按钮，指定输出目标表，即数据表company_new；勾选“指定数据库字段”复选框，用于将数据表temporary的字段与字段选择3控件输出流中的字段进行匹配；单击“数据库字段”选项卡，具体如图所示。

 

 八.运行转换2查看结果