powercenter与kettle的排序取前n条记录的比较 - kingstudy

公告

ETL这个词对于从事BI职位的coder来说，相信并不陌生，今天的故事就从ETL的两个工具powercenter与kettle说起。最近的1年多，由于各种原因，leader要求ETL这块需要换一个工具。原来的powercenter被抛弃了，我们迎来了更加开源的kettle。原本主要从事于BI前端开发cognos工作的我也由于人手不足而被安排了一些ETL的改造任务，不管怎么来说，是繁琐的工作也好或者是各种不同的问题，或者说是一种真心的疲惫。对于Leader我还是心存感激的，虽然平时也会有不少的抱怨。抱怨他的方式和大家的报酬一直得不到提升。因为最起码从一个出发点来说，我学到了更多的东西，对于我这个少不更事的年轻人（二十四五岁，还算年轻吧）来说。好了言归正传，故事开始了。

需求：每天取不同店铺交易额topN的商品交易额清单。

part1：原ETL的模型powercenter中的Mapping，如图所示，在对所有指标分组聚合统计之后运用了Rank组件。

1.1：需要设置 rank的ports 属性。里面就是上一步输入的字段名称。Ｉ－Ｏ—Ｗ－Ｒ　设置输入输出。此处最重要的就是设置Ｒ字段就是根据什么字段ｒａｎｋ，这里勾上相应字段。接着如下图：

1.2：设置ｒａｎｋ后取的ｔｏｐ记录数　ｎｕｍｂｅｒ　ｏｆ　ｒａｎｋｓ　为３　则代表取每个ｇｒｏｕｐ　ｂｙ　的ｔｏｐ３，至于是升序或者是降序就是这里的顶部或者是底部了，设置top/bottom处为top即可，取Rank后的最大值向下。

总结：Powercenter中一个rank组件就解决了每天取top n条记录的问题。

part2：kettle中如何实现这个问题呢？kettle中没有rank组件。最终还是找到了解决的方法。

2.1:测试数据准备工作

-- 创建表
CREATE TABLE `tmallorder` (
`datekey` INT(11) NOT NULL,-- 日期
`storename` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, -- 店铺名称
`goodsname` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, -- 商品名称
`goodsmoney` DECIMAL(20,4) DEFAULT NULL -- 销售额
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 插入测试数据
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121111,'GXG旗舰店','GXG黑色呢绒大衣20121111G1',985231455.80)
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121111,'GXG旗舰店','GXG黑色呢绒大衣20121111G2',882373145.48)
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121111,'太平鸟旗舰店','太平鸟黑色呢绒大衣20121111T1',100025863.80)
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121111,'太平鸟旗舰店','太平鸟黑色呢绒大衣20121111T3',1126988817.48)
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121112,'GXG旗舰店','GXG黑色呢绒大衣20121111G1',2585231455.80)
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121112,'GXG旗舰店','GXG黑色呢绒大衣20121111G2',1585231455.80)
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121112,'太平鸟旗舰店','太平鸟黑色呢绒大衣20121111T1',300025863.80)
INSERT INTO tmallorder(datekey,storename,goodsname,goodsmoney)
VALUES(20121112,'太平鸟旗舰店','太平鸟黑色呢绒大衣20121111T2',170025863.80)

--最终的统计结果

日期店铺商品名称销售额

20121111 GXG旗舰店 GXG黑色呢绒大衣20121111G1 985231455.8000
20121111 GXG旗舰店 GXG黑色呢绒大衣20121111G2 882373145.4800
20121111 太平鸟旗舰店太平鸟黑色呢绒大衣20121111T1 100025863.8000
20121111 太平鸟旗舰店太平鸟黑色呢绒大衣20121111T2 126988817.4800
20121111 太平鸟旗舰店太平鸟黑色呢绒大衣20121111T3 1126988817.4800
20121112 GXG旗舰店 GXG黑色呢绒大衣20121111G1 2585231455.8000
20121112 GXG旗舰店 GXG黑色呢绒大衣20121111G2 1585231455.8000
20121112 太平鸟旗舰店太平鸟黑色呢绒大衣20121111T1 300025863.8000
20121112 太平鸟旗舰店太平鸟黑色呢绒大衣20121111T2 170025863.8000

2.2: 针对所有记录排序取top n,不分组取top n

组件：sortrows+js

2.2.1:

tmallorder就是测试表数据的一个输出，sort rows 就是先针对记录做一个排序，针对业务需求选择是降序或者是升序，本例是取交易额大的所以排序指标是降序

排序字段为1个交易额 ASC 为Ｎ即为降序排序。

2.2.2:

trans_Status = CONTINUE_TRANSFORMATION;
if(getProcessCount("r")>3) {
trans_Status = SKIP_TRANSFORMATION;
}

SKIP_TRANSFORMATION , ERROR_TRANSFORMATION, CONTINUE_TRANSFORMATION是TRANSFORMATION已经预先定义好的静态常量,不可更改。作用是过滤记录行，控制转换流程

例如：

trans_Status = CONTINUE_TRANSFORMATION

if (field.getString()==’123’) trans_Status = SKIP_TRANSFORMATION

getProcessCount("")方法在kettle是一个特殊的函数，含义和参数解释spoon官方解释：

// Returns a number with the current processed Rows.
// The type is changable.
//
// Usage:
// getProcessCount(var);
// 1: String - The Pentaho/Kettle Type:
// u - Lines Update
// i - Lines Insert
// w - Lines Write
// r - Lines Read
// o - Lines Output

JS后面的操作就是输出处理后的数据了，我们可以看一下结果，如下图。