ETL示例解决方案——Sakila

一、案例介绍

          Sakila样本数据库是MySQL官方提供的一个模拟DVD租赁商店管理的数据库。我们将综合运用前面所学过的ETL知识，对数据库Sakila中的数据进行清洗操作，从而构建一个DVD租赁商店数据仓库系统，即实现定期从源数据库库Sakila中抽取增量数据，转换成符合DVD租赁业务的数据，最后加载到目标仓库中。

1.1案例背景介绍

       在日益激烈的商业竞争中，在线DVD租赁商店的决策者都迫切地需要更加准确的战略决策信息。每个在线DVD租赁商店的数据都存储在数据库中，因此该数据库中拥有海量的数据且并不缺乏足够的信息，但是这些数据并不是战略决策需要的信息，虽然这些海量数据对于在线DVD租赁商店的运作是非常有用的，但是对于商业的战略决策和目标制定的作用是微乎其微的。

        对于在线DVD租赁商店的决策者来说，他们需要从多个不同的商业角度观察数据，例如时间、电影、演员、用户等角度观察数据，并进行相关的分析得出决策，但是数据库中的数据不适合从多个角度进行分析，无法得出战略决策。然而，数据仓库支持复杂度分析操作，侧重于决策支持，并且还提供直观易懂的查询结果，因此我们需要基于数据库Sakila创建一个DVD租赁商店数据仓库，并将Sakila数据库中的数据加载到数据仓库中，便于在线DVD租赁商店的决策者对数据进行分析得出准确的商业决策。

1.2数据仓库的架构模型

       数据仓库Sakila_dwh的架构模型是一个星型模型，其中dim_film表、dim_customer表、dim_actor表、dim_staff表、dim_date表以及dim_time表均为维度表；fact_rental表为事实表。

二、数据库Sakila介绍

       数据库Sakila中一共含有16张数据表，分别是actor（演员）表、address（地址）表、category（类别）表、city（城市）表、country（国家）表、customer（顾客）表、film（电影）表、film_actor（演员所属电影）表、film_category（电影所属类别）表、film_text（电影描述）表、inventory（库存）表、language（语言）表、payment（付款）表、rental（租赁）表、staff（工作人员）表以及store（商店）表。

三、构建DVD租赁商店数据仓库

       我们基于数据库Sakila构建一个星型模型的DVD租赁商店数据仓库，并命名为sakila_dwh。数据仓库sakila_dwh中的事实表fact_rental是根据数据库sakila中的数据表rental创建的；维度表是根据数据表sakila中的数据表的分类创建的，即从人员、时间、地点以及事件四个角度进行创建数据仓库sakila_dwh的维度表，具体如下：

       从人员角度创建维度表dim_customer和维度表dim_staff，分别表示租赁业务中的客户和员工；从时间角度创建维度表dim_date和维度表dim_time，用于记录所有DVD的租赁时间和归还时间；从地点角度创建维度表dim_store，用于记录DVD光盘是从哪个商店租赁的；从事件角度创建维度表dim_actor和维度表dim_film，其中维度表dim_actor用于记录演员的基本信息，维度表dim_film用于记录电影的基本信息。由于电影是租赁和归还的实际对象，因此维度表dim_film应与事实表fact_rental关联。一部电影是由多位演员出演，所以会有桥接表dim_film_actor_bridge，该表将电影与演员相关联。

        数据仓库sakila_dwh中的每个维度表（dim_date和dim_time除外）都对应着数据库sakila中某个数据表，例如维度表dim_store对应着数据表store，维度表dim_actor对应着数据表actor。

3.1 加载日期数据至日期维度表

通过kettle工具加载日期数据至dim_date日期维度表。

（1）打开kettle按快捷键ctrl+N创建转换。

（2）配置控件

生成记录控件：双击进入“生成记录”控件，在“限制”处添加生成的日期，默认为10，本实验改为3660，即生成10年的日期；在“字段”框中添加字段language_code（语言）、country_code（国家码）、initial_date（初始化的日期），对生成的日期进行初始化，具体如图所示。

增加序列控件：双击进入“增加序列”控件，进入“增加序列”配置界面，并在“值的名称”处将valuename改为DaySequence，即增加一列日期字段，后续用于改变“生成记录”控件生成的日期，具体如图所示。

JavaScript代码：双击“JavaScipt”控件，进入“JavaScript”配置界面，勾选“兼容模式？”处的复选框，使得JavaScript代码控件的兼容性更强；在JavaScript代码框中编写代码（代码见下方），并点击获取变量获取相应字段，具体如图所示。

//Create a Locale according to the specified language code
var locale = new java.util.Locale(
    language_code.getString()
,   country_code.getString()
);

//Create a calendar, use the specified initial date
var calendar = new java.util.GregorianCalendar(locale);
calendar.setTime(initial_date.getDate());

//set the calendar to the current date by adding DaySequence days
calendar.add(calendar.DAY_OF_MONTH,DaySequence.getInteger() - 1);

//get the calendar date
var date = new java.util.Date(calendar.getTimeInMillis());
//en-us example: 9/3/07
var date_short  = java.text.DateFormat.getDateInstance(
                      java.text.DateFormat.SHORT
                  ,   locale
                  ).format(date);
//en-us example: Sep 3, 2007 
var date_medium = java.text.DateFormat.getDateInstance(
                      java.text.DateFormat.MEDIUM
                  ,   locale
                  ).format(date);
//en-us example: September 3, 2007
var date_long   = java.text.DateFormat.getDateInstance(
                      java.text.DateFormat.LONG
                  ,   locale
                  ).format(date);
//en-us example: Monday, September 3, 2007
var date_full   = java.text.DateFormat.getDateInstance(
                      java.text.DateFormat.FULL
                  ,   locale
                  ).format(date);

//day in year: 1..366
var simpleDateFormat   = java.text.SimpleDateFormat("D",locale);
var day_in_year        = simpleDateFormat.format(date);
//day in month: 1..31
simpleDateFormat.applyPattern("d");
var day_in_month       = simpleDateFormat.format(date);
//en-us example: "Monday"
simpleDateFormat.applyPattern("EEEE");
var day_name           = simpleDateFormat.format(date);
//en-us example: "Mon"
simpleDateFormat.applyPattern("E");
var day_abbreviation   = simpleDateFormat.format(date);
//week in year, 1..53
simpleDateFormat.applyPattern("ww");
var week_in_year       = simpleDateFormat.format(date);
//week in month, 1..5
simpleDateFormat.applyPattern("W");
var week_in_month      = simpleDateFormat.format(date);
//month number in year, 1..12
simpleDateFormat.applyPattern("MM");
var month_number       = simpleDateFormat.format(date);
//en-us example: "September"
simpleDateFormat.applyPattern("MMMM");
var month_name         = simpleDateFormat.format(date);
//en-us example: "Sep"
simpleDateFormat.applyPattern("MMM");
var month_abbreviation = simpleDateFormat.format(date);
//2 digit representation of the year, example: "07" for 2007
simpleDateFormat.applyPattern("y");
var year2              = simpleDateFormat.format(date);
//4 digit representation of the year, example:  2007
simpleDateFormat.applyPattern("yyyy");
var year4              = "" + simpleDateFormat.format(date);
//handling Quarters is a DIY
var quarter_name = "Q";
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;

//get the local yes/no values
var yes = local_yes.getString();
var no = local_no.getString();

//initialize for week calculations
var first_day_of_week = calendar.getFirstDayOfWeek();
var day_of_week = java.util.Calendar.DAY_OF_WEEK;

//find out if this is the first day of the 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;
}

//calculate the next day
calendar.add(calendar.DAY_OF_MONTH,1);
//get the next calendar date
var next_day = new java.util.Date(calendar.getTimeInMillis());

//find out if this is the first day of the week
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;
}

//find out if this is the first day of the month
var is_first_day_of_month;
if(day_in_month == 1){
    is_first_day_in_month = yes;
} else {
    is_first_day_in_month = no;
}

//find out if this is the last day in the month
var is_last_day_of_month;
if(java.text.SimpleDateFormat("d",locale).format(next_day)==1){
    is_last_day_in_month = yes;
} else {
    is_last_day_in_month = no;
}

//date = year4 + "-" + month_number + "-" + day_in_month
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;

注意：在获取字段后，建议将date字段数据类型改为Date，其余的都改为string，此建议是在我运行时，数据库写不进去数据所得出的修改方案，仅供参考，具体看各自的实验过程。

表输出控件：双击进入“表输出”控件，连接好相应的数据库，选择目标表dim_date，具体如下

目标表选好后，点击下方数据库字段，单击“输入字段映射”按钮，弹出“映射匹配”对话框，依次选中“源字段”选项框的字段和“目标字段”选项框的字段，再单击“Add”按钮，将一对映射字段添加至“映射”选项框中，具体如下。

（3）保存运行

数据库部分数据如下所示：

3.2 加载时间数据至时间维度表

（1）操作介绍

通过kettle工具加载时间数据至日期维度表dim_time。

（2）打开kettle创建转换

（3）配置控件

生成记录控件：双击“生成记录”控件，进入“生成记录”配置界面，在步骤名称处改名为“Generate Hours”，在“限制”处添加生成的时间，这里添加的是生成24小时数据，起始时间是0；在“字段”框添加生成小时字段的相关信息，如图所示。

增加序列控件：双击“增加序列”控件，进入“增加序列”配置界面，将步骤名称改为“Hours Sequence”，即增加一列小时字段，将值的名称改为“HoursSequence”，由于时间是由时分秒构成，因此需要生成时分秒的数据，这里生成24小时数据，如图所示。

JavaScript代码控件：双击“JavaScript代码”控件，进入“JavaScript代码”界面，把步骤名称改为“Calculate hours 12”，勾选“兼容模式？”处的复选框，使得JavaScript代码控件的兼容性更强；在JavaScript代码复选框中编写代码；单击【获取变量】按钮，将代码中定义的变量添加至字段框。配置如图所示。

生成记录2控件：双击“生成记录”控件，进入“生成记录”界面，在步骤名称处改名为“Generate Minutes”，在“限制”处添加60表示生成60条数据；在“字段”框添加生成字段的名称，字段类型及默认值为0，如图所示。

生成控件3控件：双击“生成记录”控件，进入“生成记录”界面，在步骤名称处改名为“Generate Seconds”，在“限制”处添加60；在“字段”框添加生成字段的名称，字段类型及默认值为0，如图所示。

配置JavaScript代码2控件：双击“JavaScript代码2”控件，进入“JavaScript代码2”界面，勾选“兼容模式？”处的复选框，使得JavaScript代码2控件的兼容性更强；在Java Script代码框中编写代码；单击【获取变量】按钮，将代码中定义的变量添加至字段框。JavaScript代码2控件的配置，如图所示。

配置表输出控件：

运行转换：

查看数据表dim_time的部分数据：

通过navicat工具，查看数据表dim_time是否已成功插入86400条时间数据，查看结果如图所示（这里只截取了部分数据）。

3.3 加载员工数据至员工维度表

通过Kettle工具加载员工数据至员工维度表dim_staff

1.打开kettle工具，创建转换。

2.配置表输入控件

3.配置表输入控件2

4.字段选择控件配置

5.值映射控件配置

6.维度查询/更新

7.运行转换

8.查看dim_staff的数据

3.4 加载用户数据至用户维度表

通过Kettle工具加载用户数据至用户维度表dim_customer。

1.打开kettle工具，创建转换

使用Kettle工具，创建一个转换，并添加表输入控件、映射控件、字段选择控件、值映射控件、维度查询/更新控件以及Hop跳连接线，具体如图所示。

2.配置表输入控件

3.配置表输入2控件

4.打开kettle工具，新建转换

使用Kettle工具，创建一个转换（该转换为上个转换的子转换），并添加映射输入规范控件、数据库查询控件、过滤记录控件、JavaScript代码控件、字段选择控件以及Hop跳连接线，如图所示。

5.配置映射输入规范控件

双击“映射输入规范”控件，进入“Mapping input specification”界面，并添加映射的字段“address_id”，该字段为传递的参数（由于转换load_dim_customer中表输入2控件流获取的字段address_id，用于查询用户的地址信息，而后续数据仓库的维度表数据也需要用户的地址信息，因此这里将字段address_id作为传递的参数），添加完毕后单击【确定】按钮，完成“映射输入规范”控件的配置，如图所示。

6.配置数据库查询控件

7.配置数据库查询2控件

8.配置数据库查询3控件

9.配置过滤记录控件

10.配置JavaScript代码控件

11.配置字段选择控件

12.配置映射控件

13.配置字段选择控件

14.配置值映射控件

15.配置维度查询/更新控件

16.运行转换

17.查看dim_customer数据库中的部分数据

3.5 加载商店数据至商店维度表

1.配置表输入控件

2.配置表输入2控件

3.配置映射（子转换）控件

4.配置数据库查询控件

5.配置查询/更新控件

6.运行转换

7.查看dim_store数据库部分数据

3.6 加载演员数据至演员维度表

1.配置表输入控件

2.配置表输入2输入控件

3.配置插入/更新控件

4.运行转换

5.查看dim_actor数据库中的部分数据

3.7 加载电影数据至电影维度表

1.配置表输入控件

2.配置表输入2控件

3.配置数据库查询控件

4.配置数据库查询2控件

5.配置值映射控件

6.配置列拆分为多行控件

7.配置增加常量控件

8.配置列转行控件

9.配置计算器控件

10.配置数据库连接控件

11.配置数据库查询3控件

12.配置增加常量2控件

13.配置列转行2控件

14.配置计算器控件

15.配置联合查询/更新控件

16.配置数据库连接2控件

17.配置数据库查询4控件

18.配置分组控件

19.配置计算器3控件

20.配置流查询控件

21.配置插入/更新控件

22.运行转换

23.查看dim_film数据库部分数据

3.8 加载租赁数据至租赁事实表

1.配置表输入控件

2.配置表输入2控件

3.配置字段选择控件

4.配置过滤记录控件

5.配置计算器控件

6.配置增加常量控件

7.配置数据库查询控件

8.配置数据库查询2控件

9.配置维度查询/更新控件

10.配置维度查询/更新2控件

11.配置维度查询/更新3控件

12.配置增加常量2控件

13.配置插入/更新控件

14.运行转换

15.查看fact_rental数据库的部分数据

3.9 发送邮件

1.创建作业

2.各转换配置

3.发送邮件配置

注意：该密码为qq邮箱授权码，获取过程如下：

1. 进入【QQ邮箱】，点击上面【设置】，如下图所示。

2. 在【邮件设置】页面，点击【账户】，如下图所示。

3. 鼠标往下拉，找到【POP3/IMAP/SMTP/Exchange/CardDAV/CalDAV服务】，点击【生成授权码】，如下图所示。

4. 根据页面弹框提示，使用手机编辑短信，发送【配置邮件客户端】至1069070069，然后回到电脑页面，点击【我已发送】，如下图所示。

5. 最后生成授权码成功。