kettle项目经验总结

Kettle项目总结

Sakila项目业务简介和各数据表及各表关系

业务简介

​ Sakila是在线DVD租赁商店系统的数据库，Sakila的主要目的是支撑DVD租赁商店的业务流程，下面列举了一些业务流程活动中的关键点来理解数据库是如何支撑的。

​ 1、每个商店维护自己的租赁影片清单，当客户取走或归还DVD光盘时会有一个专门的店员对这个清单进行维护。

​ 2、影片描述的内容同样在维护信息范围之内 ，如分类（动作、冒险、喜剧等）、演员、等级、特殊分类（例如被删除的情节和预告片）。这些信息可能被打印在DVD包装的标签上。

​ 3、必须在商店注册成为会员才可以租赁光盘。

​ 4、客户可以在任何一家商店租赁一张或多张光盘，同时，商店希望客户在每张光盘对应的租赁期内归还之前租赁的光盘。

​ 5、顾客可以在任意时间对任何租赁的光盘付费。

Sakila数据库的表

序号	英文表名	中文表名
1	actor	演员表
2	address	地址表
3	category	类别表
4	city	城市表
5	country	国家表
6	customer	客户表
7	film	电影表
8	film_actor	电影_演员表
9	film_category	电影_类别表
10	film_text	电影_文本表
11	inventory	库存表
12	language	语言表
13	payment	付款表
14	rental	租赁表
15	staff	工作人员表
16	store	商店表

其中各数据表设计规范如下：

​ 1、每张表都有自增主键列，列名采用“表名_id”的规则命名，如表film的自增列为film_id。

​ 2、外键约束引用主键，且名字与主键列的相同。例如，表store的address_id列引用addess的address_id列。

​ 3、每张表有一个列叫做last_update，这是一个TIMESTAMP类型的字段，用来记录增加或更新数据时的时间。

Sakila数据库中各表关系图

维度建模

维度建模基本概念

​ 维度建模(dimensional modeling)是专门用于分析型数据库、数据仓库、数据集市建模的方法。数据集市可以理解为是一种"小型数据仓库"。

维度表

​ 维度表示你要对数据进行分析时所用的一个量，比如你要分析产品销售情况，你可以选择按类别来进行分析，或按区域来分析。这样的按…分析就构成一个维度。再比如"昨天下午我在星巴克花费 200 元喝了一杯卡布奇诺"。那么以消费为主题进行分析，可从这段信息中提取三个维度：时间维度(昨天下午)，地点维度(星巴克)，商品维度(卡布奇诺)。通常来说维度表信息比较固定，且数据量小。

事实表

​ 表示对分析主题的度量。事实表包含了与各维度表相关联的外键，并通过JOIN 方式与维度表关联。事实表的度量通常是数值类型，且记录数会不断增加，表规模迅速增长。比如上面的消费例子，它的消费事实表结构示例如下：

​ 消费事实表：Prod_id(引用商品维度表)，TimeKey(引用时间维度表)，Place_id(引用地点维度表)，Unit(销售量)。

​ 总的说来，在数据仓库中不需要严格遵守规范化设计原则。因为数据仓库的主导功能就是面向分析，以查询为主，不涉及数据更新操作。事实表的设计是以能够正确记录历史信息为准则，维度表的设计是以能够以合适的角度来聚合主题内容为准则。

维度建模三种模式

星型模式

​ 星形模式(Star Schema)是最常用的维度建模方式。星型模式是以事实表为中心，所有的维度表直接连接在事实表上，像星星一样。

​ 注意：星形模式的维度建模由一个事实表和一组维表成，且具有以下特点：

1、维表只和事实表关联，维表之间没有关联；

2、每个维表主键为单列，且该主键放置在事实表中，作为两边连接的外键；

3、以事实表为核心，维表围绕核心呈星形分布；

雪花模式

​ 雪花模式(Snowflake Schema)是对星形模式的扩展。雪花模式的维度表可以拥有其他维度表的，虽然这种模型相比星型更规范一些，但是由于这种模型不太容易理解，维护成本比较高，而且性能方面需要关联多层维表，性能也比星型模型要低。所以一般不是很常用。

星座模式

​ 星座模式是星型模式延伸而来，星型模式是基于一张事实表的，而星座模式是基于多张事实表的，而且共享维度信息。

​ 注意：前面介绍的两种维度建模方法都是多维表对应单事实表，但在很多时候维度空间内的事实表不止一个，而一个维表也可能被多个事实表用到。在业务发展后期，绝大部分维度建模都采用的是星座模式。

模型命名规范

​ 维度表的命名全部遵循了dim_的规则，其中用来描述维度的内容。

​ 维度一般都是以人员(who)、时间(when)、地点(where)、事件(what)来划分。

​ 事实表的命名全部遵循了fact_的规则，其中< fact -name>用来描述事实的内容。

​ 事实表一般都是以多少(how match？)来划分。

维度表的处理

管理各种键

1、业务键

​ 源系统中业务主键，用来标识唯一的一个业务实体。

2、维度表代理键

​ 用来标识维表表里面的一行。数据仓库最佳实践表明，原则上，维度表应该使用自动生成的无意义的整型数值作为代理键。代理键的值和维度表里面的属性没有关系，代理键一般是在ETL过程中生成。

加载维度表

分类

​ 维度表分为静态维度表和动态维度表。静态维度是不会变化的维度数据，如时间、日期等（一旦生成就不会再进行修改）。动态维度是业务系统产生的，一段时间会产生变化。

缓慢变化维（SCD）类型

​ 类型一：对源系统的更新，也会直接更新目标的维度表。维度表总是保存当前最新的状态，如果发生变化就直接覆盖。通过Kettle的”插入/更新”步骤来实现。

​ 类型二：对源系统的更新，会往目标维度表里插入一行数据，通过不同的时间戳来维护同一条维度数据的多个版本（一个数据改变后会对应生成一个版本，这个版本的后缀是时间）。在任何一个给定的时间点，都可以找到一行对应的维度数据。可以按照时间追踪到维度的变化。通过Kettle的”维度查询 /更新”步骤来实现。

​ 类型三：对源系统的更新，会在目标维度里增加列，在目标维度表同一行新增的列里保存新的数据。Kettle没有一个专用的步骤来支持，但是可以写一个作业并使用”表里面的列是否存在（如果不存在，则更改表结构）”步骤来判断是否要更改表结构，然后使用SQL 脚本步骤，执行相应的DDL语句增加一个新列。

​ 注意：实际义务场景中，类型一和类型二运用最多，类型三很少使用，一般更多使用类型二。

Sakila数据仓库模型

​ 如下图所示：Sakila数据仓库模型为雪花模式。其中维度表都是dim开头，而事实表都是fact开头，其中dim_fime维度表含有子维度表。

维度表

​ 人员（who）：这组包含dim_customer表和dim_staff表，分别代表租赁业务中的客户和员工。这是属于类型2的缓慢变化维度：维度表里使用%_version_number、%_valid_from和%_valid_through列来跟踪同一客户或员工的历史记录。

​ 时间(when): 包含dim_date表和dim_time表，dim_date表代表租赁日期和归还日期，dim_time维度表用来记录当天租赁的时间。

​ 地点(where)：维度表dim_store用来记录DVD光盘是从哪个商店租赁的。

​ 事件(what)：这组包括dim_actor和dim_film两个维度表，它们是租赁业务的主题。只有dim_film表和fact_rental表直接关联，因为电影才是租赁和归还的实际对象。但是，一部电影由众多演员构成，这些演员在某种意义上也是组排的对象。这就是所谓的桥接表dim_film_actor_bridge的由来，该表联系了演员和电影。另外，该表保存了一个权重因子，用来评估一个演员对影片的贡献值。

事实表

​ 多少(how match？)事实表fact_rental用来记录详细的租赁数据。

load_dim_date（加载日期维度表）

01静态维度

​ 维度表dim_date属于静态维度类型：通过数据集进行初始化并且不需要定期从sakila数据库进行加载。

​ 静态维度表操作步骤：第一步：生成数据；第二步：转换数据；第三步：加载数据。其中生成数据：生成记录（记录数自行确定）；转换数据：增加序列、javascript脚本等；加载数据：表输出。

02dim_date表结构

字段名（英文）	类型	字段名（中文名）	示例值
date_key	int	代理键（唯一标识）	20220101
date_value	date	年-月-日	2022-01-01
date_short	char	月/日/年(短)	1/1/00
date_medium	char	月(缩写) 日，年	Jan 1，2022
date_long	char	月，日，年	January 1，2022
date_full	char	星期，月，日，年	Saturday，January 1，2022
day_in_year	smallint	当年的第几天	1
day_in_month	tinyint	当月的第几天	1
is_first_day_in_month	char	是否月的第一天	yes
is_last_day_in_month	char	是否月的最后一天	no
day_abbreviation	char	星期的缩写	Sat
day_name	char	星期	Saturday
week_in_year	tinyint	年的第几个星期	1
week_in_month	tinyint	月的第几个星期	1
is_first_day_in_week	char	是否周的第一天	no
is_last_day_in_week	char	是否周的最后一天	yes
month_number	tinyint	月份	1
month_abbreviation	char	月份缩写	Jan
month_name	char	月份名称	January
year2	char	年份后两位	00
year4	smallint	年份	2022
quarter_name	char	季度名称	Q1
quarter_number	tinyint	季度	1
year_quarter	char	年-季度	2022-Q1
year_month_number	char	年-月份	2022-01
year_month_abbreviation	char	年-月缩写	2022-Jan

03生成dim_date表数据

​ 生成10年记录，从2011-01-01开始，这个功能的输入为：生成记录（但是生成记录生成的数据都是一模一样的）；转换为：增加序列（为了使生成记录生成的数据变成不一样）；脚本为：JS脚本（提取年，月，日等等数据）；输出为：表输出。

生成记录（生成记录控件）

​ 生成10年记录，从2011-01-01开始，因为是十年记录，所以限制处填写3650，然后依次对字段名称，类型，值等进行设置（注意：如果是日期时间类型，还需要在格式处定义时间的格式），最后确定即可。（注意生成记录生成数据内容都是一模一样的，所以需要增加序列等等一序列操作使数据不一样，生成记录常用于生成静态的时间维度表）

增加序列（增加序列控件）

​ 功能：为了使生成记录生成的数据变成不一样。

生成日期属性（JS脚本）

​ JavaScrpit脚本调用java的Locale、GregorianCalender（公历）、Date、DateFormat、SimpleDateFormat类的API进行访问。JS脚本代码如下：

//生成locale地区语言代码和国家代码(括号内容为直接点击JS函数进行调用)
var locale = new java.util.Locale(language_code.getString(),country_code.getString());
//生成 calendar日历
var calendar = new java.util.GregorianCalendar(locale);
//设置时间(括号内容为直接点击JS函数进行调用)
calendar.setTime(initial_date.getDate());
//设置日历为当前日期(其中-1是因为序列从1开始，日期开始为1号)
calendar.add(calendar.DAY_OF_MONTH,序列号.getInteger()-1);
//获取date
var date = new java.util.Date(calendar.getTimeInMillis());
//生成短日期,其中format(date)是把上一步的date变量传递进来
var date_short =java.text.DateFormat.getDateInstance(java.text.DateFormat.SHORT,locale).format(date);
//生成中日期
var date_medium = java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM,locale).format(date);
//生成长日期
var date_long = java.text.DateFormat.getDateInstance(java.text.DateFormat.LONG,locale).format(date);
//生成全日期
var date_full = java.text.DateFormat.getDateInstance(java.text.DateFormat.FULL,locale).format(date);
//简单格式化
var simpleDateFormat = java.text.SimpleDateFormat("D",locale);
//天在年的第几天
var day_in_year = simpleDateFormat.format(date);
//建立格式器
simpleDateFormat.applyPattern("d");
//天在月的第几天（调用上一步建立的格式器）
var day_in_month = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("EEEE");
//星期的名称
var day_name = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("E");
//星期的缩写
var day_abbreviation = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("ww");
//一年的第几周
var week_in_year = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("W");
//一月的第几周
var week_in_month = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("MM");
//月份
var month_number = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("MMMM");
//月的名称
var month_name = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("MMM");
//月的缩写
var month_abbreviation = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("yy");
//两位的年
var year2 = simpleDateFormat.format(date);

simpleDateFormat.applyPattern("yyyy");
//四位的年
var year4 = simpleDateFormat.format(date);

//季度名称
var quarter_name = "Q";
//季度，其中 parseInt(month_number)是将月份转换为整数
var quarter_number;
switch(parseInt(month_number)){
	case 1:case 2:case 3:quarter_number = "1";break;
	case 4:case 5:case 6:quarter_number = "2";break;
	case 7:case 8:case 9:quarter_number = "3";break;
	case 10:case 11:case 12:quarter_number = "4";break;
}
quarter_name += quarter_number;
//定义常量
var yes = "yes";
var no = "no";
//获取周的第一天
var first_day_of_week = calendar.getFirstDayOfWeek();
var day_of_week = java.util.Calendar.DAY_OF_WEEK;

//判断是否为周的第一天
var is_first_day_in_week;
if(first_day_of_week == calendar.get(day_of_week)){
	is_first_day_in_week = yes;
}else{
	is_first_day_in_week = no;
}

//日历的下一天
calendar.add(calendar.DAY_OF_MONTH,1);
//获取下一天
var next_day = new java.util.Date(calendar.getTimeInMillis());
//判断是否周的最后一天
var is_last_day_in_week;
if(first_day_of_week == calendar.get(day_of_week)){
	is_last_day_in_week = yes;
}else{
	is_last_day_in_week = no;
}
//判断是否为月的第一天
var is_first_day_in_month;
if(day_in_month == 1){
	is_first_day_in_month = yes;
}else{
	is_first_day_in_month = no;
}

//判断是否为月的最后一天
var is_last_day_in_month;
if(java.text.SimpleDateFormat("d",locale).format(next_day)==1){
	is_last_day_in_month = yes;
}else{
	is_last_day_in_month = no;
}

//年_季度
var year_quarter = year4 + "-" + quarter_name;

//年_月份
var year_month_number = year4 + "-" + month_number;

//年_月缩写
var year_month_abbreviation = year4 + "-" + month_abbreviation;

//日期代理键（唯一键）
var date_key = year4 + month_number + (day_in_month<10?"0":"") + day_in_month;

​ 在JS代码编写区编写JS脚本代码，然后在输出字段区点击获取变量来获取输出字段，最后确定即可。

加载日期维度（表输出控件）

​ 表输出时数据库连接的共享：在主对象树下找到DB连接，找到对应的数据库连接，右键后勾选共享，今后就不需要重复连接数据库了。表输出设置：1、先设置数据库连接，如果之前有且共享了，则直接选取；2、点击浏览找到目标表，如果没有则自己给目标表命名，有则直接选取相应数据表；3、没有目标数据表的时候点SQL进行创建，有则不需要点击SQL；4、勾选指定数据库字段，然后切换到数据库字段栏目下进行字段设置；5、如果目标数据表的字段与流里面的字段一样，则直接点击获取字段按钮；6、插入的字段框中会显示目标表字段信息与流字段即源字段信息并一一匹配（注意：如此处表字段名称为：date_value，而流字段名称为：date，直接点击获取字段时，获取的表字段和流字段名称都是叫date，这时需要手动点击表字段栏目下的date字段，将其修改为跟数据表的名称一模一样即可，另外表字段的数据类型和流字段的数据类型要保存一致）；7、最后点击确定完成表输出设置。

load_dim_time（加载时间维度表）

01dim_time表结构

字段名（英文）	类型	字段名（中文名）	示例值
time_key	int	代理键（唯一标识）	0
time_value	time	时：分：秒	00:00:01
hours24	tinyint	24小时格式	23
hours12	tinyint	12小时格式	11
minutes	tinyint	分	59
seconds	tinyint	秒	23
am_pm	char	上下午	PM

02生成dim_time表数据

​ 生成一天24小时的每一分钟，每一秒钟总共 24 x 60 x 60 = 86400 条记录，从00:00:00开始，这个功能的输入为：生成记录（共三个生成记录，分别生成24小时，60分钟，60秒）；转换为：增加序列（共三个增加序列）；脚本为：JS脚本（其中一个JS脚本是通过小时序列生成上下午十二小时及判断上下午数据）；连接为：记录关联（笛卡尔积输出，功能：关联上一步生成的时分秒，生成86400条数据）；脚本为：JS脚本（将记录关联的结果生成正确的时间time字段和time_key字段）；输出为：表输出。

生成记录（生成记录控件）

​ 三个生成记录输入，分别生成24小时，60分钟，60秒。

增加序列（增加序列控件）

​ 功能：三个增加序列操作，分别使生成记录生成的时，分，秒数据变成不一样。

生成12小时数据（JS脚本）

​ JS脚本是通过上述增加小时序列生成对应的上下午十二小时及判断上下午数据，JS脚本代码如下：

//生成12小时格式
var hours12 = hours24.getInteger()%12;

//生成am和pm的数据
var am_pm = hours24.getInteger()>12?"PM":"AM";

​ 在JS代码编写区编写JS脚本代码，然后在输出字段区点击获取变量来获取输出字段，最后确定即可。结果：生成判断上下午的am_pm字段和将一天24小时拆分为上下午各12小时的hours12字段。

记录关联（记录关联（笛卡尔输出）控件）

​ 功能：关联上一步生成的时分秒，生成86400条数据（不需要任何配置，直接确定即可）。

生成时间（JS脚本）

​ JS脚本是通过对上述记录关联生成的时，分，秒进行拼接生成正确的时间time字段和time_key字段，JS脚本代码如下：

//time
var time = hours24.getInteger() + ":" + minutes.getInteger() + ":" + seconds.getInteger();

//time_key
var time_key = (hours24.getInteger()<10?"0":"")+hours24.getInteger()+
				(minutes.getInteger()<10?"0":"")+minutes.getInteger()+
				(seconds.getInteger()<10?"0":"")+seconds.getInteger();

​ 在JS代码编写区编写JS脚本代码，然后在输出字段区点击获取变量来获取输出字段，最后确定即可。结果：通过对上述记录关联生成的时，分，秒进行拼接生成正确的时间time字段和time_key字段。

加载时间维度（表输出控件）

​ 直接点击获取字段时，在插入的字段框中，特别要注意表字段的名称要和数据库表中的名称一模一样，而且要和流字段名称一一匹配对应，另外注意表字段的数据类型和流字段的数据类型要保存一致。

load_dim_staff(加载店员表)

01dim_staff表结构

​ 注意：店员表是动态维度表，店员表里面的数据是随着业务系统的发展慢慢产生的，所以店员表的数据需要从原业务系统进行导出。我们店员表的数据也会缓慢进行更新，是个缓慢变化维类型，这里采用缓慢变化维的第二种形式，一个数据改变后会对应生成对应的一个版本，这个版本的后缀是通常是时间。

字段名（英文）	类型	字段名（中文名）	示例值
staff_key	int	代理键（唯一标识）	0
staff_last_update	datetime	最后更新日期	2011-01-01 00:00:00
staff_first_name	varchar	员工姓	mike
staff_last_name	varchar	员工名	hilyer
staff_id	int	员工id	1
staff_store_id	int	员工商店id	1
staff_version_number	smallint	版本号	1
staff_valid_from	date	开始日期	2011-01-01
staff_valid_through	date	截止日期	2022-12-31
staff_active	char	是否启用	Yes

02加载dim_staff表数据

​ 从原业务系统加载数据，第一步：获取上次更新的时间（这里是数据库里面的表，所以输入为表输入，而且查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_staff表的时间，而不是源业务数据库表的时间）；第二步：从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的staff表的最新的数据）；第三步：对数据进行转换（先进行字段选择操作，将表里面的字段类型和流里面的字段类型变成一样，然后进行值映射）；第四步：数据的加载（数据仓库下的维度查询/更新操作，因为是第二种缓慢变化维类型）。

获取上次更新的时间（表输入控件）

​ 查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_staff表的时间，而不是源业务数据库表的时间，SQL代码如下：其中coalesce(a,b,c,d…)函数如同oracle数据库的NVL()函数，只是mysql没有NVL()函数，和oracle数据库的coalesce(a,b,c,d…)函数功能一样，返回参数中第一个不为空的表达式。

SELECT
COALESCE(MAX(staff_last_update),"1970-01-01 00:00:00")
max_dim_staff_last_update
FROM dim_staff

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，点击确定即可。

从源中获取更新的数据（表输入控件）

​ 从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的staff表的最新的数据）

SELECT
  staff_id
, first_name
, last_name
, address_id
, picture
, email
, store_id
, active
, username
, password
, last_update
FROM staff where last_update > ?

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，注意：这里的？号是参数，需要在“从步骤插入数据”中获取第一步的上次更新时间，最后点击确定即可。

字段选择

​ 因为源业务数据库sakila库中的staff表中的active字段为：int类型；而ETL数据仓库sakila_dwh里的dim_staff表active字段为：String类型，所以需要借助字段选择将int类型的数据类型转换为String，1变为Y。

​ 字段选择操作：在元数据菜单栏下的需要改变元数据的字段框中选取前后数据类型不同的active字段，在数据类型栏下改为跟ETL数仓sakila_dwh里的dim_staff表一样的数据类型，再确定即可。

值映射

​ 将流里面active字段内容Y变成Yes，N变成No。

​ 值映射设置：1、选择需要进行值映射的原字段名称；2、设置不匹配时的默认值；4、值映射规则设置：把active里面的Y和N分别转换为Yes和No，然后确定即可。

加载更新的维度数据（维度查询/更新控件）

​ 因为是第二种缓慢变化维类型，所以选择数据仓库下的维度查询/更新操作。具体操作步骤为：先建立好数据库连接，再选择目标表，然后在关键字菜单栏下选择维字段名称和流里的字段名称，注意两者要一一对应，然后切换到字段菜单栏目下。切换到字段菜单栏目下后，依次选择出“查询/更新字段”框下的维字段和比较的流字段名称，因是第二种缓慢变化维形式，所以在“更新的维度的类型”下选择插入类型，然后选择代理关键字段并勾选使用自增字段，再然后依次在Version字段，Stream日期字段，开始日期字段，截止日期字段栏目下选择对应的字段，最后确定即可完成ETL数仓的数据加载。

load_dim_customer（加载顾客表）

01dim_customer表结构

​ 注意：顾客表也是动态维度表，顾客表里面的数据是随着业务系统的发展慢慢产生的，所以顾客表的数据需要从原业务系统进行导出。我们顾客表的数据也会缓慢进行更新，是个缓慢变化维类型，这里采用缓慢变化维的第二种形式，一个数据改变后会对应生成对应的一个版本，这个版本的后缀是通常是时间。

字段名（英文）	类型	字段名（中文名）	示例值
customer_key	int	代理键（唯一标识）	601
customer_last_update	datetime	更新日期	2011-02-15 06:06:06
customer_id	int	用户id	1
customer_first_name	varchar	用户姓	MARY
customer_last_name	varchar	用户名	SMITH
customer_email	varchar	邮箱	MARY.SMITH@sakilacustomer.org
customer_active	char	是否激活	Yes
customer_created	date	创建时间	2011-02-14
customer_district	varchar	区域	1913 Hanoi Way
customer_address	varchar	地址	Nagasaki
customer_postal_code	varchar	邮政编码	35200
customer_phone_number	varchar	电话号码	28303384290
customer_city	varchar	城市	Sasebo
customer_country	varchar	国家	USA
customer_version_number	smallint	版本号	1
customer_valid_from	date	开始日期	1900-01-01
customer_valid_through	date	截止日期	2199-12-31

02加载dim_customer表数据

​ 从原业务系统加载数据，第一步：获取上次更新的时间（这里是数据库里面的表，所以输入为表输入，而且查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_customer表的时间，而不是源业务数据库表的时间）；第二步：从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的customer表的最新的数据）；第三步：映射来匹配地址信息（查询到address，district，postal_code，phone，city，country等字段）；第四步：对数据进行转换（先进行字段选择操作，将表里面的字段类型和流里面的字段类型变成一样，然后进行值映射）；第五步：数据的加载（数据仓库下的维度查询/更新操作，因为是第二种缓慢变化维类型）。

获取上次更新的时间（表输入控件）

​ 查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_customer表的时间，而不是源业务数据库表的时间，SQL代码如下：其中coalesce(a,b,c,d…)函数如同oracle数据库的NVL()函数，只是mysql没有NVL()函数，和oracle数据库的coalesce(a,b,c,d…)函数功能一样，返回参数中第一个不为空的表达式。

SELECT
COALESCE(MAX(customer_last_update),"1970-01-01 00:00:00")
AS max_dim_customer_last_update
FROM dim_customer

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，点击确定即可。

从源中获取最新的数据（表输入控件）

​ 从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的customer表的最新的数据）

SELECT
  customer_id
, store_id
, first_name
, last_name
, email
, address_id
, active
, create_date
, last_update
FROM customer where last_update > ?

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，注意：这里的？号是参数，需要在“从步骤插入数据”中获取第一步的上次更新时间，最后点击确定即可。

映射（子转换）

​ 因为不论是源业务数据库sakila库中的customer表还是ETL数据仓库sakila_dwh里的dim_customer表都是借助address_id来查询地址的，两者有相通的地方，所以借助映射（子转换）进行操作，需要新建一个 “fetch_address取地址.ktr” 的子转换，最后是 “加载dim_customer表数据.ktr” 主转换程序去调用子转换程序。

输入address_id（映射输入规范）

​ 因为两者都是借助address_id来查询地址，所以映射输入规范输入的字段名称就是sakila库中的customer表和sakila_dwh中的dim_customer表相通的address_id字段名称，数据类型选择Integer整型。

查询Address（数据库查询）

​ 1、建立好数据库连接；2、找到目标表名，勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入address_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字address_id和无用的last_update时间字段）；6、点击确定即可。

查询City（数据库查询）

​ 1、建立好数据库连接；2、找到目标表名，勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步查询Address步骤的查询表返回的值字段：city_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字city_id和无用的last_update时间字段）；6、点击确定即可。

查询Country（数据库查询）

​ 1、建立好数据库连接；2、找到目标表名，勾选使用缓存；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步查询City步骤的查询表返回的值字段：country_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字country_id和无用的last_update时间字段）；6、点击确定即可。

是否有第二地址（过滤记录）

​ 因为查到的地址可能是一个，也可能拥有两个地址（因为之前的查询表返回的值字段含有address2，需要判断它是否为空）。

​ 过滤记录操作步骤：在条件中选择需要过滤记录的字段（如这里的address2），再选择判断方法（如这里的is not null），最后确定即可。

拼接地址（JS脚本）

​ 如果上一步的adress2不为空，则需要将address和address2拼接起来。JS脚本如下。具体操作步骤：在JS代码区输入代码后直接确定即可。

var address = address.getString() + " " + address2.getString();

字段选择

​ 因为之前的步骤产生了很多多余的字段，那些多余的字段在ETL数据仓库sakila_dwh里的dim_customer表中是没有（如address_id，address2，city_id，country_id等字段），所以需要借助字段选择控件来移除多余的字段。具体操作步骤：切换到移除菜单栏下，依次选择需要移除的字段，最后确定即可。

输出（映射输出规范）

​ 映射输出规范，直接确定即可。

字段选择

​ 先调用 “fetch_address取地址.ktr” 的子转换（就是图中的映射（子转换）来匹配地址步骤）。后因为源业务数据库sakila库中的customer表中的active字段为：int类型；而ETL数据仓库sakila_dwh里的dim_customer表active字段为：String类型，所以需要借助字段选择将int类型的数据类型转换为String，1变为Y。

​ 字段选择操作：在元数据菜单栏下的需要改变元数据的字段框中选取前后数据类型不同的active字段，在数据类型栏下改为跟ETL数仓sakila_dwh里的dim_customer表一样的数据类型，再确定即可。

值映射

​ 将流里面active字段内容Y变成Yes，N变成No。

​ 值映射设置：1、选择需要进行值映射的原字段名称；2、设置不匹配时的默认值；3、值映射规则设置：把active里面的Y和N分别转换为Yes和No，然后确定即可。

更新维度数据（维度查询/更新控件）

​ 因为是第二种缓慢变化维类型，所以选择数据仓库下的维度查询/更新操作。具体操作步骤为：先建立好数据库连接，再选择目标表，然后在关键字菜单栏下选择维字段名称和流里的字段名称，注意两者要一一对应，然后切换到字段菜单栏目下。切换到字段菜单栏目下后，依次选择出“查询/更新字段”框下的维字段和比较的流字段名称，因是第二种缓慢变化维形式，所以在“更新的维度的类型”下选择插入类型，然后选择代理关键字段并勾选使用自增字段，再然后依次在Version字段，Stream日期字段，开始日期字段，截止日期字段栏目下选择对应的字段，最后确定即可完成ETL数仓的数据加载。

load_dim_store(加载商店表)

01dim_store表结构

字段名（英文）	类型	字段名（中文名）	示例值
store_key	int	代理键（唯一标识）	4
store_last__update	datetime	更新日期	1970-01-01 00:00:01
store_id	int	商店id	23
store_address	varchar	商店地址	47 MySakila Drive
store_district	varchar	商店区域	Alberta
store_postal_code	varchar	邮政编码	MARY.SMITH@sakilacustomer.org
store_phone_number	varchar	电话号码	111122233
store_city	varchar	城市	Lethbridge
store_country	varchar	国家	Canada
store_manager_staff_id	int	管理人员id	1
store_manager_first_name	varchar	管理人员姓	Mike
store_manager_last_name	varchar	管理人员名	Hillyer
store_version_number	smallint	版本号	1
store_valid_from	date	开始日期	1900-01-01
store_valid_through	date	截止日期	2199-12-31

02加载dim_store表数据

​ 从原业务系统加载数据，第一步：获取上次更新的时间（这里是数据库里面的表，所以输入为表输入，而且查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_store表的时间，而不是源业务数据库表的时间）；第二步：从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的store表的最新的数据）；第三步：映射来匹配地址信息（查询到address，district，postal_code，phone，city，country等字段）；第四步：查询（先进行数据库查询操作，查询出商店管理者的信息）；第五步：数据的加载（数据仓库下的维度查询/更新操作，因为是第二种缓慢变化维类型）。

获取上次更新的时间（表输入控件）

​ 查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_store表的时间，而不是源业务数据库表的时间，SQL代码如下：其中coalesce(a,b,c,d…)函数如同oracle数据库的NVL()函数，只是mysql没有NVL()函数，和oracle数据库的coalesce(a,b,c,d…)函数功能一样，返回参数中第一个不为空的表达式。

SELECT
COALESCE(MAX(store_last_update),"1970-01-01 00:00:00")
AS max_dim_store_last_update
FROM dim_store

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，点击确定即可。

从源中获取最新的数据（表输入控件）

​ 从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的store表的最新的数据）

SELECT
  store_id
, manager_staff_id
, address_id
, last_update
FROM store where last_update > ?

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，注意：这里的？号是参数，需要在“从步骤插入数据”中获取第一步的上次更新时间，最后点击确定即可。

匹配地址(映射（子转换）)

​ 因为不论是源业务数据库sakila库中的store表还是ETL数据仓库sakila_dwh里的dim_store表都是借助address_id来查询地址的，两者有相通的地方，所以借助映射（子转换）进行操作，需要新建一个 “fetch_address取地址.ktr” 的子转换，最后是 “加载dim_store表数据.ktr” 主转换程序去调用子转换程序。

​ 具体操作：找到子转换程序的物理地址，直接确定即可。

商店管理查询（数据库查询）

​ 因为源业务数据库sakila库中的store表中有个manager_staff_id字段，所以我们还需要查询到商店管理员姓名（即first_name和last_name）。

​ 具体操作：1、建立好数据库连接；2、找到目标表名，勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入manager_staff_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字staff_id和无用的last_update时间字段）；6、点击确定即可。

加载维度数据（维度查询/更新控件）

​ 因为是第二种缓慢变化维类型，所以选择数据仓库下的维度查询/更新操作。具体操作步骤为：先建立好数据库连接，再选择目标表，然后在关键字菜单栏下选择维字段名称和流里的字段名称，注意两者要一一对应，然后切换到字段菜单栏目下。切换到字段菜单栏目下后，依次选择出“查询/更新字段”框下的维字段和比较的流字段名称，因是第二种缓慢变化维形式，所以在“更新的维度的类型”下选择插入类型，然后选择代理关键字段并勾选使用自增字段，再然后依次在Version字段，Stream日期字段，开始日期字段，截止日期字段栏目下选择对应的字段，最后确定即可完成ETL数仓的数据加载。

load_dim_actor(加载演员表)

01dim_actor表结构

字段名（英文）	类型	字段名（中文名）	示例值
actor_key	int	代理键（唯一标识）	201
actor_last_update	datetime	最后更新日期	2006-02-15 04:34:33
actor_first_name	varchar	演员姓	GUINESS
actor_last_name	varchar	演员名	PENELOPE
actor_id	int	演员id	1

02加载dim_actor表数据

​ 从原业务系统加载数据，第一步：获取上次更新的时间（这里是数据库里面的表，所以输入为表输入，而且查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_store表的时间，而不是源业务数据库表的时间）；第二步：从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的store表的最新的数据）；第三步：数据的加载（Kettle的”插入/更新”控件来实现，因为是第一种缓慢变化维类型（即对源系统演员表的更新，也会直接更新目标的维度表。维度表总是保存当前最新的状态，如果发生变化就直接覆盖））。

获取上次更新的时间（表输入控件）

​ 查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_actor表的时间，而不是源业务数据库表的时间，SQL代码如下：其中coalesce(a,b,c,d…)函数如同oracle数据库的NVL()函数，只是mysql没有NVL()函数，和oracle数据库的coalesce(a,b,c,d…)函数功能一样，返回参数中第一个不为空的表达式。

SELECT
COALESCE(MAX(actor_last_update),"1970-01-01 00:00:00")
AS max_dim_actor_last_update
FROM dim_actor

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，点击确定即可。截图同上略。

从源中获取最新的数据（表输入控件）

​ 从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的store表的最新的数据）

SELECT
  actor_id
, first_name
, last_name
, last_update
FROM actor where last_update > ?

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，注意：这里的？号是参数，需要在“从步骤插入数据”中获取第一步的上次更新时间，最后点击确定即可。截图同上略。

插入更新（数据加载插入更新控件）

​ 插入更新设置：1、先设置数据库连接；2、点击浏览找到目标表，如果没有则自己给目标表命名，有则直接选取相应数据表；3、指定用于查询对比的字段（一般是主键）；4、点击获取和更新字段来指定需要更新的字段，即：数据库中表的字段内容会根据数据流的字段内容而进行插入更新变化的字段；最后确定即可。

load_dim_film(加载电影表)

01dim_film表结构

字段名（英文）	类型	字段名（中文名）	示例值
film_key	int	代理键（唯一标识）	1001
film_last_update	datetime	最后更新日期	2006-02-15 05:03:42
film_title	varchar	标题	ACADEMY DINOSAUR
film_description	text	描述	A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies
film_release_year	smallint	发行年份	2006
film_language	varchar	语言	English
film_original_language	varchar	原版语言	Not Applicable
film_rental_duration	tinyint	租赁期限	6
film_rental_rate	decimal	出租率	Null
film_duration	int	时长	1
film_replacement_cost	decimal	更换成本	20.99
film_rating_code	char	评级码	PG
film_rating_text	varchar	评级文本	Parental Guidance Suggested
film_has_trailers	date	是否有预告片	No
film_has_commentaries	date	是否有评价	No
film_has_deleted_scenes	char	是否有删除场景	Yes
film_has_behind_the_scenes	char	是否有幕后	Yes
film_has_category_action	char	动作片	No
film_has_category_animation	char	动画片	No
film_has_category_children	char	儿童片	No
film_has_category_classics	char	经典片	No
film_has_category_comedy	char	喜剧片	No
film_has_category_ducumentary	char	纪录片	Yes
film_has_category_drama	char	戏剧	No
film_has_category_family	char	家庭片	No
film_has_category_foreign	char	外国电影	No
film_has_category_games	char	游戏	No
film_has_category_horror	char	恐怖片	No
film_has_category_music	char	音乐	No
film_has_category_new	char	新片	No
film_has_category_scifi	char	科幻	No
film_has_category_sports	char	运动	No
film_has_category_travel	char	旅行	No
film_id	int	电影id	1

02加载dim_film表数据

​ 从原业务系统加载数据，第一步：获取上次更新的时间（这里是数据库里面的表，所以输入为表输入，而且查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_film表的时间，而不是源业务数据库表的时间）；第二步：从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的film表的最新的数据）。后续具体步骤比较多，参考下面共15个步骤

获取上次更新的时间（表输入控件）

​ 查询的上次更新时间是ETL数据仓库sakila_dwh里的dim_film表的时间，而不是源业务数据库表的时间，SQL代码如下：其中coalesce(a,b,c,d…)函数如同oracle数据库的NVL()函数，只是mysql没有NVL()函数，和oracle数据库的coalesce(a,b,c,d…)函数功能一样，返回参数中第一个不为空的表达式。

SELECT
COALESCE(MAX(film_last_update),"1970-01-01 00:00:00")
AS max_dim_film_last_update
FROM dim_film

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，点击确定即可。截图同上略。

从源中获取最新的数据（表输入控件）

​ 从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的film表的最新的数据），其中的 cast() 函数的功能是改变数据类型，因为在sakila库中的film表的release_year的字段类型为：year，而ETL数据仓库sakila_dwh里的dim_film表的release_year的字段类型为：smallint （注意：必须要使最终数据仓库中表里面的字段类型和流里面的字段类型保持一致，即必须含有 “CAST(release_year AS UNSIGNED) AS release_year” 语句，将其借助cast()函数改为无符号形式，否则会报错）。

SELECT
  film_id
, title
, description
, CAST(release_year AS UNSIGNED) AS release_year
, language_id
, original_language_id
, rental_duration
, rental_rate
, length
, replacement_cost
, rating
, special_features
, last_update
FROM film where last_update > ?

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，注意：这里的？号是参数，需要在“从步骤插入数据”中获取第一步的上次更新时间，最后点击确定即可。截图同上略。

查询language（数据库查询）

​ 因为源业务数据库sakila库中的film表中有个language_id字段，却没有language_name字段，所以我们还需要借助language_id查询sakila库中的language表找到language_name字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（language表），勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入language_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字language_id和无用的last_update时间字段），因为ETL数仓中语句名称的字段名为：film_language，为了便于后续查看，所以这里把新的名称改为了language而不是采用之前的name；6、点击确定即可。

查询原版语言（数据库查询）

​ 因为源业务数据库sakila库中的film表中有个original_language_id字段，却没有original_language字段，所以我们还需要借助original_language_id查询sakila库中的language表找到original_language字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（language表），勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入original_language_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字language_id和无用的last_update时间字段），因为ETL数仓中语句名称的字段名为：film_original_language，为了便于后续查看，所以这里把新的名称改为了original_language而不是采用之前的name，如果没有原版语言，默认显示为：Not Applicable，所以在默认下设置为：Not Applicable；6、点击确定即可。

Rating字段文本映射（值映射）

​ 将流里面rating字段内容规范化（即评论规范化）。

​ 值映射设置：1、选择需要进行值映射的原字段名称；2、设置匹配完成后目标字段名称（可以不设置，不设置时，名称跟之前一样，覆盖映射匹配前字段信息）；3、设置不匹配时的默认值；4、值映射规则设置，然后确定即可。

将feature列拆分为多行（列拆分为多行控件）

​ feature是电影的特点字段，往往一部电影有很多种特点，但是在源业务数据库sakila库中的film表的feature字段内容里是多个特点放在一起，采用“，”连接，所以需要将每部电影的特点与“，”为分割，进行一一拆开，将原来的一行内容变成多行。

​ 具体操作步骤：先选择要拆分的字段名称，再设置分隔符，手动填写新字段的名称，最后确定即可。

增加常量Yes,No（增加常量转换控件）

​ 因为ETL数据仓库sakila_dwh里的dim_film表有film_has_trailers（是否有预告片），film_has_commentaries（是否有评价）等字段内容不是Yes就是No的字段内容信息，所以我们使用一个增加常量Yes,No的输入控件。增加常量的功能：就是在本身的数据流里面添加一列数据，该列的数据都是相同的值。

​ 具体操作步骤：设置所添加常量列的列名（字段名称），字段类型及常量值，确定即可。

special feature列转行（列转行转换控件）

​ 因为之前的将feature列拆分为多行，生成了special feature字段，一部电影的special feature字段有多行（因为每部电影有多个特点），所以需要借助列转行进行转置。

​ 具体操作步骤：首先选择关键字段（需要把该列所对应的值转换为新的字段的字段名称），再点击获取字段来获得构成分组的字段（切记需要把多余的字段排除），然后在目标字段框下手动填写目标字段名称，数据字段名称，关键字值名称及数据类型，最后确定即可。结果生成4个新字段，去除了原来的special feature字段和Yes字段。

将feature的NULL转换为No（替换NULL值应用控件）

​ 因为上一步的列转行，生成的四个新字段含有多个null值，而null值往往不好进行分析，所以需要替换null值。

​ 具体操作步骤：先勾选选择字段按钮（也不可以不勾选，默认为不勾选，不勾选的情况下为替换所有字段的NULL值，不过通常项目情况下是替换单一字段的NULL值），然后在字段框中的字段栏目下选择具有NULL的字段，手动在值替换为栏目下输入固定值，再确定即可。

获取电影分类id（数据库连接查询控件）

​ 因为在之前的流字段没有电影分类信息，而电影分类category_id在film_category中有，所以借助数据库连接查询来关联film_category表，查询出电影分类id字段（即category_id字段），SQL脚本代码如下：

select category_id
from film_category
where film_id = ?

​ 数据库连接查询设置：先指定数据库连接，然后在SQL框中输入相应的SQL查询（查询第二个数据库的表数据），其中的问号（?）代表参数，再然后在The parameter to use框中依次写入参数的字段名称及字段类型（自身库中表的字段），最后确定即可。

查询分类名称(数据库查询)

​ 根据上一步查询，我们获取了电影分类id字段，却没有分类名称字段，所以我们还需要借助category_id查询sakila库中的category表找到category_name字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（category表），勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入category_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字lcategory_id和无用的last_update时间字段），因为ETL数仓中语句名称的字段名为：film_category，为了便于后续查看，所以这里把新的名称改为了category而不是采用之前的name；6、点击确定即可。操作截图同上，生成了一个category分类名称字段，略。

增加常量Yes（增加常量转换控件）

​ 因为ETL数据仓库sakila_dwh里的dim_film表有film_has_category_action（判断动作片分类），film_has_category_animation（判断动画片分类）等字段内容不是Yes就是No的字段内容信息，所以我们使用一个增加常量Yes的输入控件（因为之前的special feature列转行步骤去除了Yes字段，但还有No字段）。增加常量的功能：就是在本身的数据流里面添加一列数据，该列的数据都是相同的值。

​ 具体操作步骤：设置所添加常量列的列名（字段名称），字段类型及常量值，确定即可。操作截图同上，生成了一个Yes字段，略。

category列转行（列转行转换控件）

​ 因为category(分类字段)字段，一部电影的category字段有多行（因为每部电影有多个分类），所以需要借助列转行进行转置。

​ 具体操作步骤：首先选择关键字段（需要把该列所对应的值转换为新的字段的字段名称），再点击获取字段来获得构成分组的字段（切记需要把多余的字段排除），然后在目标字段框下手动填写目标字段名称，数据字段名称，关键字值名称及数据类型，最后确定即可。结果生成16个新字段，去除了原来的category字段和Yes字段。

将category的NULL转换为No（替换NULL值应用控件）

​ 因为上一步的列转行，生成的16个新字段含有多个null值，而null值往往不好进行分析，所以需要替换null值。

​ 具体操作步骤：先勾选选择字段按钮（也不可以不勾选，默认为不勾选，不勾选的情况下为替换所有字段的NULL值，不过通常项目情况下是替换单一字段的NULL值），然后在字段框中的字段栏目下选择具有NULL的字段，手动在值替换为栏目下输入固定值，再确定即可。操作截图同上，略。

加载维度数据（联合查询/更新控件）

​ 因为是第二种缓慢变化维类型，所以选择数据仓库下的联合查询/更新操作。具体操作步骤为：先建立好数据库连接，再选择目标表，然后点击获取字段，则会在“关键字（在表中查询记录）”菜单栏下列出所有字段名称，然后删除在这个菜单栏下的多余字段（也就是dim_fim目标表上不需要的字段），选择维字段名称和流里的字段名称，注意两者要一一对应。再然后选择代理关键字段并勾选使用自增字段，最后确定即可完成ETL数仓的数据加载。

03dim_film_actor_bridge表结构

字段名（英文）	类型	字段名（中文名）	示例值
film_key	int	代理键（唯一标识）	1001
actor_key	int	代理键	201
actor_weighting_factor	decimal	演员权重因子	0.1

04加载dim_film_actor_bridge表数据

​ 电影与演员的桥接表：主要功能是评判各演员的贡献力。因为是第一种缓慢变化维类型（即对源系统电影表的更新，也会直接更新目标的维度表。维度表总是保存当前最新的状态，如果发生变化就直接覆盖））。

获取演员id（数据库连接查询控件）

​ 因为在ETL数据仓库sakila_dwh里的dim_film表没有演员信息，而源业务数据库sakila库中的film_actor表中有actor_id，所以借助数据库连接查询来关联film_actor表，查询出电影演员id字段（即actor__id字段），SQL脚本代码如下：

select actor_id
from film_actor
where film_id = ?

​ 数据库连接查询设置：先指定数据库连接，然后在SQL框中输入相应的SQL查询（查询第二个数据库的表数据），其中的问号（?）代表参数，再然后在The parameter to use框中依次写入参数的字段名称及字段类型（自身库中表的字段），最后确定即可。

查询dim_actor（数据库查询）

​ 根据上一步查询，我们获取了演员id字段，却没有演员信息字段，所以我们还需要借助actor_id查询数据仓库sakila_dwh库中的dim_actor表找到actor_key字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（dim_actor表），勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入actor_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字actor_id和无用的last_update时间字段），保留actor_key；6、点击确定即可。操作截图同上，生成了一个actor_key名称字段，略。

按film_key分组（排序记录和分组控件）

​ 分组设置：1、做分组之前必须对数据源进行排序，而且根据分组字段来确定排序字段（排序字段和分组字段必须一致，如这里都是film_key字段），在分组时：先在构成分组的字段框中的分组字段栏目下选取分组字段，然后在聚合框中的名称栏目下输入聚合后所得新字段的名称，在subject栏目下选择聚合字段名称，在类型栏目下选择聚合类型（聚合类型有多种，这里使用的是count记个数方式），最后确定即可；2、启动后结果展示：根据聚合字段及聚合类型及所创建的聚合后的字段名称创建了一个新表，对原表数据进行分组聚合操作。

计算演员的权重因子（计算器转换控件）

​ 计算器转换就是利用一个函数集合来创建新的字段，还可以设置字段是否移除（但一般指的是一些临时字段）。

​ 计算器转换设置：1、手动设置新字段的名称，然后点击后面的计算栏目就会弹出一大推公式，选择所使用的计算公式，再然后选择需要用于计算的原字段名称（注意最多三个A,B,C），再然后在移除那里设置为“否”（注意：actor_weight_factor我们设置移除为否，因为这个字段是我们最后需要的演员权重因子，而one字段是为了计算权重因子设置的中间字段，是个常量，最后结果中不需要保留，所以移除那里选择“是”说明这个字段可以移除），这样新生成的字段才会显示输出到结果中，最后点击确定按钮即可；2、启动后结果展示：采用计算器新生成了one（常量）,actor_weight_factor（演员的权重因子）列两个新字段。

查询演员的权重因子（流查询控件）

​ 流查询是在查询前把数据都加载到内存中，并且流查询只能进行等值查询。

​ 流查询设置：先指定查询步骤（因为根据film_key字段来查询actor_weight_factor(演员权重因子)字段，所以此处“Lookup step”为计算演员的权重因子），在查询值所需的关键字框中依次选取字段和查询字段的名称，然后在指定用来接受的字段框中依次选取Field栏目下的字段名称及其对应数据类型，最后确定即可。

插入更新（dim_film_actor_bridge表数据加载插入更新控件）

​ 因为是第一种缓慢变化维类型（即对源系统电影表的更新，也会直接更新目标的维度表。维度表总是保存当前最新的状态，如果发生变化就直接覆盖））。

​ 插入更新设置：1、先设置数据库连接；2、点击浏览找到目标表，如果没有则自己给目标表命名，有则直接选取相应数据表；3、指定用于查询对比的字段（一般是主键）；4、点击获取和更新字段来指定需要更新的字段，即：数据库中表的字段内容会根据数据流的字段内容而进行插入更新变化的字段；最后确定即可。

load_fact_rental（加载租凭事实表）

01fact_rental表结构

字段名（英文）	类型	字段名（中文名）	示例值
customer_key	int	代理键（唯一标识）	730
staff_key	int	代理键	4
film_key	int	代理键	1080
store_key	int	代理键	4
rental_date_key	int	代理键	20050524
return_date_key	int	代理键	20050524
rental_time_key	int	代理键	225330
count_returns	int	返还数量	1
count_rentals	int	租赁数量	1
rental_duration	int	租赁时长	169860
rental_last_update	datetime	上次更新时间	2006-02-15 21:30:53
rental_id	int	租赁id	1

02加载fact_rental表数据

​ 从原业务系统加载数据，第一步：获取上次更新的时间（这里是数据库里面的表，所以输入为表输入，而且查询的上次更新时间是ETL数据仓库sakila_dwh里的fact_rental表的时间，而不是源业务数据库表的时间）；第二步：从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的rental表的最新的数据）。后续具体步骤比较多，参考下面共15个步骤

获取上次更新的时间（表输入控件）

​ 查询的上次更新时间是ETL数据仓库sakila_dwh里的fact_rental表的时间，而不是源业务数据库表的时间，SQL代码如下：其中coalesce(a,b,c,d…)函数如同oracle数据库的NVL()函数，只是mysql没有NVL()函数，和oracle数据库的coalesce(a,b,c,d…)函数功能一样，返回参数中第一个不为空的表达式。

SELECT
COALESCE(MAX(rental_last_update),"1970-01-01 00:00:00")
AS max_fact_rental_last_update
FROM fact_rental

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，点击确定即可。截图同上略。

从源中获取最新的数据（表输入控件）

​ 从源业务数据中获取最新的数据（这里是数据库里面的表，所以输入为表输入，而且需要借助第一步查询到的上次更新时间当where条件判断查询源业务数据库sakila库中的rental表的最新的数据）。

SELECT
  rental_id
, rental_date
, inventory_id
, customer_id
, return_date
, staff_id
, last_update
FROM rental where last_update > ?

​ 表输入操作：建立好数据库连接后，点击获取SQL查询语句，在SQL框中输入相应SQL语句，注意：这里的？号是参数，需要在“从步骤插入数据”中获取第一步的上次更新时间，最后点击确定即可。截图同上略。

增加列，更改数据类型（字段选择控件）

​ 字段选择就是从数据流中选择（筛选）字段，并且可以改变字段名称及修改数据类型。使用场景：通常情况下，我们从元数据抓取过来的数据是含有很多多余的字段的，这时，我们就需要借助字段选择进行字段的筛选，这个功能是整个ETL工作过程中最常用的一个功能。

​ 字段选择设置：1、点击获取选择的字段，在“选择和修改”栏目下将需要修改字段名称的字段进行设置；2、在移除栏目下添加要移除的字段名称；3、在元数据栏目下，对需要修改字段名称和字段数据类型的字段进行修改（如果没有字段需要修改数据类型，则可以省略这个步骤）；4、启动后结果展示：相对于原表，把rental_date列名改为rental_datetime，return_date列名改为return_datetime；另外新增加了四个字段（rental_date_key，rental_time_key，return_date_key，return_time_key），且这四个字段的类型都是字符串。

过滤记录（过滤记录控件）

​ 过滤记录就是让数据流从一路到两路（类似于python编程的if else条件判断）。因为归还日期可能是空值也可能不是空值，总之两种情况，后续的操作步骤就是归还日期为空时，我们需要做什么，归还日期不为空时，我又该干什么？

​ 过滤记录设置：先设置输出后设置过滤记录控件，先在条件框中选取条件判断字段的名称，然后选取条件判断语句，再然后在发送true数据给一个步骤（发送false数据给另一个步骤）的右侧分别选取各自相应的输出（如同if else条件判断一样，return_datetime字段不为空时，则进行“计算租凭的周期”操作，相应的return_datetime字段为空时，则进行“添加租凭时长三个字段”操作），最后确定即可。

计算租凭的周期（计算器控件）

​ 计算器转换就是利用一个函数集合来创建新的字段，还可以设置字段是否移除（但一般指的是一些临时字段）。

​ 计算器转换设置：1、手动设置新字段的名称，然后点击后面的计算栏目就会弹出一大推公式，选择所使用的计算公式，再然后选择需要用于计算的原字段名称（注意最多三个A,B,C），再然后在移除那里设置为“否”（注意：后面三个字段（rental_duration，count_returns，return_date_key1）我们设置移除为否，因为这三个字段是我们最后需要的租凭周期，租凭次数等，而前面两个字段（milisecs和rental_duration_milisecs）是为了计算租凭周期而设置的中间字段，其中milisecs字段为常量，最后结果中不需要保留，所以移除那里选择“是”说明这个字段可以移除），这样新生成的字段才会显示输出到结果中，最后点击确定按钮即可；2、启动后结果展示：采用计算器新生成了milisecs（常量），rental_duration_milisecs，rental_duration，count_returns，return_date_key1共五列五个新字段。

添加租凭时长三个字段（增加常量控件）

​ 因为过滤记录的其中一条数据流（计算租凭的周期）中生成了三个我们最终需要的不能移除的字段（rental_duration，count_returns，return_date_key1），所以我们在另一条数据流中也要有这三个字段，三个字段的内容可以使用常量代替，设置一个增加常量的输入控件。增加常量的功能：就是在本身的数据流里面添加一列数据，该列的数据都是相同的值。

​ 具体操作步骤：设置所添加常量列的列名（字段名称），字段类型及常量值，确定即可。

查询inventory获取film_id和store_id（数据库查询）

​ 根据上一步增加列，更改数据类型操作的结果截图，我们获取了库存inventory_id字段，却没有film_id和store_id字段，所以我们还需要借助inventory_id查询源业务数据库sakila库中的inventory表找到film_id和store_id字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（inventory表），勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入inventory_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字inventory_id和无用的last_update时间字段），保留film_id和store_id字段；6、点击确定即可。结果：生成了一个film_id和store_id名称字段。

从dim_film获取film_key（数据库查询）

​ 根据上一步查询inventory获取film_id和store_id操作，我们获取了film_id和store_id字段，却没有film_key字段，所以我们还需要借助film_id查询数据仓库sakila_dwh库中的dim_film表找到film_key字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（dim_film表），勾选使用缓；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入film_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字film_id和无用的last_update时间字段），保留film_key字段；6、点击确定即可。结果：生成了一个film_key名称字段。操作截图同上，略。

查询dim_customer获取customer_key（数据库查询）

​ 根据上一步增加列，更改数据类型操作的结果截图，我们获取了顾客customer_id字段，却没有customer_key字段，所以我们还需要借助customer_id查询数据仓库sakila_dwh库中的dim_customer表找到customer_key字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（dim_customer表），勾选使用缓存；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入customer_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字customer_id和无用的last_update时间字段），保留customer_key字段；6、点击确定即可。结果：生成了一个customer_key名称字段。操作截图同上，略。

查询dim_staff获取staff_key（数据库查询）

​ 根据上一步增加列，更改数据类型操作的结果截图，我们获取了店员staff_id字段，却没有staff_key字段，所以我们还需要借助staff_id查询数据仓库sakila_dwh库中的dim_staff表找到staff_key字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（dim_staff表），勾选使用缓存；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入staff_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字staff_id和无用的last_update时间字段），保留staff_key字段；6、点击确定即可。结果：生成了一个staff_key名称字段。操作截图同上，略。

查询dim_store获取store_key（数据库查询）

​ 根据上一步查询inventory获取film_id和store_id操作，我们获取了film_id和store_id字段，却没有store_key字段，所以我们还需要借助store_id查询数据仓库sakila_dwh库中的dim_store表找到store_key字段信息。

​ 具体操作：1、建立好数据库连接；2、找到目标表名（dim_store表），勾选使用缓存；3、在“查询所需的关键字”框中依次选择表字段名称，比较操作符，字段1名称（流里面的字段，就是上一步输入store_id）；4、点击获取返回字段；5、在“查询表返回的值”框中删除多余的字段（如查询所需的关键字store_id和无用的last_update时间字段），保留store_key字段；6、点击确定即可。结果：生成了一个store_key名称字段。

增加常量count_rentals（增加常量控件）

​ 因为目标表fact_rental表中含有一个count_rentals常量字段，所以需要设置一个增加常量的输入控件。增加常量的功能：就是在本身的数据流里面添加一列数据，该列的数据都是相同的值。

​ 具体操作步骤：设置所添加常量列的列名（字段名称），字段类型及常量值，确定即可。

插入更新（fact_rental表数据加载插入更新控件）

​ 因为是第一种缓慢变化维类型（即对源系统租凭表的更新，也会直接更新目标的维度表。维度表总是保存当前最新的状态，如果发生变化就直接覆盖））。

​ 插入更新设置：1、先设置数据库连接；2、点击浏览找到目标表，如果没有则自己给目标表命名，有则直接选取相应数据表；3、指定用于查询对比的字段（一般是主键）；4、点击获取和更新字段来指定需要更新的字段，即：数据库中表的字段内容会根据数据流的字段内容而进行插入更新变化的字段；最后确定即可。

加载作业

循环加载

​ 由于dim_date和dim_time维度表是静态维度表，只需要一次操作，因此不能算是真正的ETL过程。而dim_satff、dim_customer、dim_store、dim_actor、dim_film和fact_rental是需要循环加载的，因此我们需要使用作业把转换连接起来，做成定时任务。作业调用之前的七大转换时：关键是各转换子文件的地址要匹配正确（点击浏览找到对应转换子文件的位置，将位置放在Transformation下，确定即可）；其中循环加载错误的 “中止作业” 操作什么都不需要设置，直接调用即可。