Sakila 租赁业务数据集成案例

案例介绍

本文所介绍的 ETL 解决方案基于一个虚拟的 DVD 出租连锁店 Sakila，通过一个非常简单的星型模型对其进行分析，这个星型模型来自 MySQL 免费的 sakila 数据库。在这个示例中，系统定期从源数据库 sakila 抽取增量数据。然后转换成符合星型模型的数据，最后把数据加载到目标数据库的租赁业务星型模型中。剩下的内容简单地讨论了源数据库和目标数据库的模型，这样可以帮助读者理解 ETL 方案的工作方式和原理。

sakila数据库介绍

为了对 sakila 数据库模型有一个全面的了解，读者可以参考相关的官方文档。实际上，这个模型本身非常简单且易于理解，参考相关的业务流程可以非常容易地理解其本质。

DVD光盘业务租赁流程

sakila 数据库的主要目的在于支撑 DVD 租赁商店的业务流程，下面列举了一些业务流程活动中的关键点来帮助理解sakila 数据库是如何支撑的：

• 每个商店维护自己的租赁影片清单，当客户取走或归还 DVD 光盘时会有一个专门的店员对这个清单进行维护。
• 影片描写的内容同样在维护信息范围之列，如分类（动作、冒险、喜剧等）、演员、等级、特殊分类（例如被删除的情节和预告片）。这些信息可能被打印在 DVD 包装的标签上。
• 必须在商店注册成为会员才可以租赁光盘。
• 客户可以在任何一家商店租赁一张或多张光盘，同时，商店希望客户在每张光盘对应的的租赁期内归还之前租赁的光盘。
• 顾客可以在任意时间对任何租赁的光盘付费。

sakila数据库主题分类

下图为 sakila 数据库的关系图，读者可以按照自己的分类方式对这些表进行分类。

为了更快地使读者了解数据库模型的主从表关系，我们建议将其分为四个主题，具体分类如下：

• 电影类：包含 film 表和包含影片附加信息的表，如 category、actor 和 language。
• 商店类：包括 store 表和相关联的 staff 表、inventory 表。
• 客户类：以 customer 表为主线，包含与顾客有关联的 rental 表和 payment 表。
• 区域表：包括 country、city 和 address 表，这些表为顾客、商店和员工提供标准化的字典信息。

总体设计规范

了解总体设计规范可以加深对 sakila 数据库设计的理解，具体内容如下：

• sakila 数据库表采用单一对象名称命名。
• 每张表都有自增主键列，列名采用“表名_id”的规则命名，如表 file 的自增主键列为【film_id】。
• 外键约束引用主键，且名字与主键列的相同。例如，表 store 的【address_id】列引用表 address 的【address_id】列。
• 每张表都有一列叫做【last_update】，这是一个 TIMESTAMP 类型的字段，用来记录增加或更新数据时的时间。

安装sakila示例数据库

在安装 sakila 示例数据库之前。用户需要下载并安装 MySQL 关系型数据库的最新版本，最低版本不能低于 5.0。安装完 MySQL 数据库后，下载本文提供的 sakila 数据库建库脚本，按照如下步骤进行安装：

1、sakila 建库脚本下载链接： 数据库结构文件：sakila-schema.sql

数据文件：sakila-data.sql 2、使用 mysql 的 source 命令运行上述两个脚本，假设脚本位置在 D:\mysql\ 中，则命令如下：

Mysql \> Source D:\mysql\sakila-schema.sql
Mysql \> Source D:\mysql\sakila-data.sql

租赁业务星型模型

租赁业务的星型模型来源于 sakila 示例数据库，它是租赁业务里可能的几个维度模型之一。租赁星型模型的结构如下图所示：

上图展示了一个典型的维度模型，它包含一个叫做 fact_rental 的事实表，事实表与多个维度表关联。这种维度建模方式非常适用于联机事务处理（OLAP）。它同样也是一个经典的星型模式，因为几乎所有的维度都是单一的，维度表之间没有关联，维度表只和事实表有关。

租赁事实表

事实表的名称是 fact_rental，包含了一些数值类型的能体现出业绩的业务度量值（【count_returns】、【count_rentals】、【rental_duration】）。此外，事实表还包含一些列，用来作为指向维度表的键。当用户访问某个度量值时，维度表中的数据将提供该度量值对应的业务维度。

在 sakila 模型中，fact_rental 表与 sakila 模式下的原始 rental 表直接对应：rental表中的一行生成【fact_rental】表中的一行。

维度表

之前我们提到过，租赁业务星型模型的每一个维度都是一个单独的维度表。星型模型维度表的命名全部遵守了dim_<dimension-name> 的规则，其中 <dimension-name> 用来描述维度的内容。

根据之前 sakila 数据库模型的分类，星型模型图中建议将维度表按照相关概念分为四组（加上一组事实表“how much?”，一共五组）。

• 人员（who）：这组包括 dim_customer 表和 dim_staff 表，分别代表租赁业务中的客户和员工。这是属于类型2的缓慢变化维度：维度表里用%_version_number、%_valid_from和%_valid_through列来跟踪同一客户或员工的历史记录。
• 时间（when）：这组中的维度表主要用来记录所有光盘租赁或归还的时间点，其中，维度表 dim_date 实际是日历，它是所谓的角色扮演维度，用来同时标记租赁日期和归还日期。而 dim_time 维度表则用来记录当天租赁的时间。
• 地点（where）：维度表 dim_store 用来记录 DVD 光盘是从哪个商店租赁的，和 dim_staff、dim_customer 一样，dim_store 也是缓慢增长维，也有一组列用来记录同一个商店的不同历史版本。
• 事件（what）：这组包括 dim_actor 和 dim_film 两个维度表，它们是租赁业务的主题。只有 dim_film 表和fact_rental 表直接关联，因为电影才是租赁和归还的实际对象。但是，一部电影由众多演员构成，这些演员在某种意义上也是租赁的对象。这就是所谓的桥接表 dim_film_actor_bridge 的由来，该表联系了演员和电影。另外，该表保存了一个权重因子，用来评估一个演员对影片的贡献值。通过原始指标值乘以权重因子就可以从演员的角度分析租赁收入，而把原来的指标值看成附加值。例如，我们就可以回答这样的问题：上个月 Robert De Niro 或 Al Pacino 的电影获得了多少租金收益？

在租赁业务的星型模型中，从源数据库 sakila 模型中派生出来的每个维度（除了表 dim_date 和 dim_time ）都对应着 sakila 数据模型中的某个表。例如，维度表 dim_store 对应着业务系统中的 store 表，维度表 dim_actor 对应着actor 表。

键和变更数据捕获

除了表 dim_date 和 dim_time，每个维度表都使用自增列作为代理主键，表 dim_date 和 dim_time 的主键将在稍后介绍，它们的主键叫做智能键。这两个表的智能键分别来源于部分时间和日期，它可以在 ETL 过程中直接发挥作用，也用来对事实表做分区。

维度表的键值被用来关联表 fact_rental 和维度表。所有维度表的主键列都以 <维度名称>_key 来命名，<维度名称>就是维度表的表名除了 dim_前缀 之外的剩余部分。

在讨论 sakila 数据库模式的设计规范时，曾提到源模式中的每个表里都有【last_update】字段，该字段保存了一个时间戳 TIMESTAMP，用来存储每一行的最后修改（或添加）时间。在接下来的内容中可以看到这个字段对于变更数据捕获非常有用，变更数据捕获对数据持续增长的场景非常有用。虽然变更数据捕获的方法有很多种，我们这里使用的是一种最直接的方法：每个维度表都有【last_update】字段，这个字段保存了原始 sakila 模式中对应表的【last_update】字段的值。这样可以在维度表上执行一个查询，获得最后加载日期/时间，并用这个日期/时间来识别和抽取所有源数据库里对应表的最新变更的数据行。

除了代理主键，每个维度表也包含一列用来存储来自 sakila 数据模型的主键值，例如，星型模型中的表 dim_film 有一列【film_id】用来保存表 film 中的【film_id】，当你读完后面的内容，就会知道这些列的重要性，这些列用来判断变更的数据是增加的还是更新的数据。

安装租赁业务的星型模型

租赁星型模型的安装步骤同源 sakila示例数据库的相同，将租赁星型模型的脚本文件在 MySQL 命令行中使用 SOURCE 命令执行。安装步骤如下：

1、下载租赁星型模型的脚本文件

数据文件： sakila_dwh_data.sql

表结构文件： sakila_dwh_schema.sql

2、使用 mysql 的 source 命令运行上述两个脚本，假设脚本位置在 D:\mysql\ 中，则命令如下：

Mysql > Source D:\mysql\sakila_dwh_schema.sql
Mysql > Source D:\mysql\sakila_dwh_data.sql

在接下来的 ETL 解决方案中，将把 sakila 源数据库中的数据加载到星型模型中，因此我们在本小节中只导入星型模型的表结构即可，不用加载数据。如果使用加载数据的 SQL 脚本加载了数据，后面的 ETL 流程也能执行：只不过不能再加载变化的数据。

ETL示例解决方案

前面我们已经对数据库模型进行了讨论。接下来将介绍如何使用ETL解决方案把数据从sakila示例数据库加载到租赁星型模型。

生成静态维度

维度表 dim_date 和 dim_time 属于静态维度类型：通过数据集进行初始化并且不需要定期从sakila示例数据库进行加载（虽然可能在未来需要表 dim_date 生成更多的日期数据）。

加载维度表 dim_date

加载维度表 dim_date 的转换工作流如下图所示，工作流主要分为 3 部分：生成、转换、加载，下面对转换中的步骤进行简要描述。

（1）转换首先使用“生成记录”控件生成 10 年的数据行（10*366=3660），步骤名为“生成 10 年数据”，生成数据同时生成了一些常量，其中的一个常量是初始日期，设置为 2000-01-01，其他的常量有语言和地区代码，“生成 10 年数据”步骤的参数设置如下图所示。

（2）“生成 10 年数据”步骤的数据流入步骤“增加序列【day】”，“增加序列【day】”的参数设置如下图所示。这个步骤使用的是“增加序列”控件，目的是为每一个输入行生成一个自增的序列数字，在后面的步骤中，这个序列号将会和【initial_date】相加生成一系列连续的日期数据。

（3）“计算维度属性”步骤是这个转换中最重要的部分，这个步骤使用的是“JavaScript代码”控件.在步骤中，“增加序列【day】”步骤作为输入流，其字段被设置为JavaScript变量，用来计算不同的日期，因此我们重命名“JavaScript代码”控件为“计算维度属性”。步骤“计算维度属性”首先将序列号和【initial_date】相加，生成日历对象，然后，通过 JavaScript 表达式将其转换为不同的日期和日期的各个部分。同时，还是用 JavaScrip t表达式生成智能主键，用于区别表 dim_date 中的数据。

JavaScript 代码内容如下：

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

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

//set the calendar to the current date by adding DaySequence days
calendar.add(calendar.DAY_OF_MONTH,DaySequence - 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("yy");
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;
var no = local_no;

//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_in_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_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;
}

//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;

该 JavaScript 表达式是用来语言和国家地区代码（在步骤“生成10年数据”中设置）来完成对日期做本地化设置。例如，国家代码是 gb 且语言代码为 en，日期 2009-03-09 被格式化为 Monday, March 9, 2009，如果国家地域代码为 ca 且国家代码是 fr，那么统一的日期将会被格式化为 lundi 9 mars 2009。

“计算维度属性”步骤的图标左上角，有一个“x4”的标签，这个图标并不是这个步骤的一部分，而是表示此步骤生成的拷贝数量，可以右键单击此步骤，然后在菜单中选择“改变开始复制的数量”，最后设置数量为 4 即可。

（4）转换的最后一个步骤是“加载 dim_date”，使用的是“表输出”控件，它接收“计算维度属性”步骤输出的数据，然后生成相应的 SQL 命令，最终见该数据插入到维度表 dim_date 中。“加载 dim_date”步骤的参数设置如下图所示。

其中设置数据库连接时，先点击“新建”按钮，弹出如下图所示的“数据库连接”页面，按所给提示设置数据库连接参数。

（5）运行转换，若转换中各步骤的参数设置正确，且 sakila_dwh 数据库已正常运行，就可以非常容易地运行这个转换，几秒之内将数据加载到表 dim_date 中。

加载维度表 dim_time

加载维度表 dim_time 的转换工作流如下图所示，工作流主要分为3部分：生成、转换、加载。与前一节相比，该转换多了“记录关联（笛卡尔输出）”控件用于计算笛卡尔积，下面对转换中的各步骤进行简要描述。

（1）和“加载维度表 dim_date”转换类似，“加载维度表 dim_time”转换也通过“生成记录”控件初始化数据，在该转换中有3个生成记录步骤并行执行：

• “生成【时】”步骤生成24行数据，用来表示一天中的24小时。

• “生成【分】”步骤生成60行数据，用来表示每小时的60分钟。

• “生成【秒】”步骤生成60行数据，用来表示每分钟的60秒。

（2）每个生成行类型的步骤在随后的序列类型步骤中获得一系列连续整数 023 时、059 分、0~59 秒，三个增加序列的步骤参数设置如下图所示。

（3）其中在小时的分支中，在序列步骤之后的步骤为“计算【hours12】”，这是一个 JavaScript 脚本步骤，用来获得 12 小时制的表示方法，并通过计算生成 12 小时制的符号 AM/PM 。

JavaScript 脚本代码：

var hours12 = str2num(hours24) % 12;
var am_pm = (hours24 >= 12 ? 'PM' : 'AM');

（4）接着是“笛卡尔积”步骤，它是一个“记录关联（笛卡尔输出）”控件，用来连接两个输入流，生成笛卡尔乘积输出流，也就是两个输入流数据行的各种组合方式（n*n），每个输出行包含所有输入行的字段。

尽快通过限制“笛卡尔积”步骤，可以只生成特定组合行，但是在这里我们需要生成所有的组合。一共生成 246060 = 84600行，每一行代表 24 小时制中的一秒（实际上并不完全正确，因为没有考虑闰秒。我们忽略这个小问题继续往下看）。

（3）“笛卡尔积”步骤之后是一个“ JavaScript 脚本”控件和一个“表输出”控件，这些控件对应步骤的功能和“加载维度表 dim_date ”中的最后两步基本相同：输入流中的值通过 JavaScript 表达式进行修改后生成需要的时间格式，另外生成 dim_time 维度表的智能主键，最后将数据插入到维度表。

JavaScript代码：

//Script here
var time = "" 
         + hours24
         + ":"
         + minutes
         + ":"
         + seconds
;
var time_key = (hours24<10?"0":"")
             + hours24
             + (minutes<10?"0":"")
             + minutes
             + (seconds<10?"0":"")
             + seconds
;

（4）假设数据库处于运行状态，且已经安装 sakila_dwh 表结构和数据，此时执行转换可以在很短时间内将数据加载到 dim_time 。

循环加载

在“生成静态维度”中，我们讨论了如何构造和加载 dim_time 和 dim_date 维度表。由于加载这些表只需要一次操作，因此不能算是真正的 ETL 过程。接下来，我们介绍如何抽取从最后一次加载后的变化数据，并对数据进行转换，然后加载到租赁星型模式的维度表和事实表。

作业 Load_rentals

租赁星型模型的转换都在 load_rentals 作业中，作业流程如下图所示，接下来对作业中的关键内容进行介绍。

（1）Start 上图中作业 load_rentals 的第一个作业项是“START”节点。每个有效的作业都只能有一个“START”节点，用来表明作业开始运行的位置。

（2）不同的转换作业项 “Start”作业项连接到后面的转换作业项。位于“Start”作业项和第一个转换作业项之间的连接线上有一个圆心标记，右击由“Start”发出的连接线，选择“无条件通道”即可选择。这个符号表明这是一个无条件的连接，即使之前的作业项没有执行成功也会执行下一个作业项。

“Start” 后面的各个转换作业项用来运行不同的转换，完成特定的功能。例如：“加载 dim_staff”作业项执行“加载维度表 dim_staff”转换。为了进一步了解转换是如何同转换作业项关联的，双击转换作业项。会弹出转换设置对话框，在里面配置转换作业项。例如，双击“加载 dim_staff”作业项，将会看到如下图所示的配置对话框：

“步骤名称”设置为“加载 dim_staff”，在“转换文件名”下拉框中选择所对应的转换文件名，前半部分为系统文件库中文件夹名，表示转换所在的路径，后半部分“/加载维度表 dim_staff”是所存在的转换文件名。对话框中“步骤名称”和“转换文件名”没有关系，可以使用任何名称来命名步骤。但本节中步骤名和转换名相对应，这种一样的命名方式可以使作业项清晰地关联到转换文件。

（3）不同类型的作业项连接和流程异常 在 load_rentals 作业中，每个转换作业项后面都有一个执行成功的作业项连接，执行成功的作业项连接是绿色的并且指向下一个作业项；每个转换作业项还有一个执行失败的作业项连接，执行失败的作业项连接是黄色的（带一个停止图标）并且指向一个发送邮件作业项。

当运行这个作业时，作业项将按顺序执行：首先，启动“加载 dim_staff”作业项并执行相关的转换，如果执行成功，作业将会在连接线上显示成功标志，然后继续执行作业项“加载 dim_customer”，然后依次执行其他转换作业项，以此类推，直到最后一个转换被成功执行，最后执行“发送邮件成功”作业项，则作业执行成功。

当然，也有一种可能是有某个作业项执行失败，此时，作业将会显示是哪个作业项执行失败，然后继续执行“发送邮件失败”作业项，最后整个作业停止执行。

（4）顺序执行 作业里的作业项是顺序执行的，这和转换不同：在转换中， 所有的步骤同时启动运行。数据流从输入步骤顺序流入，然后从输出步骤流出。

作业项的顺序执行对于同步的工作来说很有必要，例如，在某个作业中，最先执行的“Start”作业项后面有一系列用来加载维度表的作业项（从“加载 dim_staff”到“加载 dim_file”），最后才是加载事实表 fact_rental 的转换作业项“加载 fact_rental”。这样可以确保在事实表加载之前维度表中新的维度行已经全部加载完毕，这样事实表才可以和维度关联。

（5）邮件作业项 在讨论不同类型的作业项连接时曾提到发送邮件作业项。它可以及时给系统管理员报告作业的运行状态：可能是所有的作业项都运行成功，然后发送一个成功的信息，也可能是某个作业项运行失败，作业提前结束并发送一个失败的通知（仅当作业失败才发送通知不是一个很好的办法，没有收到邮件并不代表作业成功，也可能是发送作业运行失败的邮件服务器发生故障）。

在使用邮件作业项时，需要配置邮件服务器，双击作业项，然后在“服务器”标签页的位置填写合适的邮件服务器地址。如果你不能确定如何填写，可以暂时不用设置发送邮件作业项，这个加载租赁星型模型的作业也可以不用配置发送邮件作业项。只是不能收到关于作业执行状态的 E-mail 通知。

（6）运行作业 运行作业的方式同运行转换完全相同。运行作业项可能需要一点时间，但是应该会在一分钟内结束。不用担心再重新运行一遍作业或某个单独的转换，如果租赁星型模型已经是最新的，作业就会发现没有要处理的数据。

加载维度表 dim_staff

作业 load_rentals 中第一个要执行的转换就是“加载 dim_staff”，该转换的工作流如下图所示。这个转换的目的是加载 dim_staff 维度表，下面将介绍该转换的步骤。

（1）变更数据库捕获与抽取 “加载 dim_staff”转换的前两个步骤都是“表输入”控件，这一步骤会执行SQL语句并且将返回的数据行转换为转换的输入流。“获取 dim_staff 最后一次更新时间”步骤使用 sakila_dwh 数据库连接执行下面的 SQL 查询语句：

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

** 此查询是为了获得维度表 dim_staff 的最后一次更新时间，dim_staff 表中【staff_last_update】字段的值来源于 staff 表中的【last_update】，无论插入还是更新源数据库的 staff 表，当前的操作时间都会自动保存在【last_update】 列中。该步骤的参数设置如下图所示。

紧接着是“Staff”步骤，它是用来抽取 staff 表里的数据，在 sakila 数据库中执行下面的查询：

SELECT *
FROM staff
WHERE last_update > ?

SQL 语句用来查询 staff 表中的【last_update】列大于参数的数据行，其中的 ？是指参数，参数的值由上一个步骤提供，由于表 staff 中参数日期之前的数据已经被加载到 dim_staff 维度表里。因此，这种设计保证了“staff”步骤的输出只包括最新的数据。组件配置如下图所示：

（2）转换和映射 Active 标记 转换中接下来的两个步骤是“字段选择”和“值映射”步骤，这两个步骤用来转换一个布尔类型的值，把这个值转化为 Yes/No 值，这个值表示员工当前是否在 sakila 租赁商店工作。这种表示方式更加适合报表和分析。

“字段选择”步骤是对输入流的一种常用操作，例如删除列、重命名列、转换列的数据类型，最后生成特定的输出格式等。双击组件，在“元数据”标签页中增加 String 类型的【active】字段，配置如下图所示：

“值映射”步骤是把输入流中指定的值，通过映射关系，转换成另一个值。双击组件，映射字段选择【active】，将字段中源值 N 及 Y 映射为 No 和 Yes，具体配置如下图所示：

（3）加载类型 2 缓慢变化维度表 dim_staff 转换中最后一个步骤是“加载 dim_staff SCD2”。这个表是一个类型2的缓慢变化维度：源数据库中数据行的任何变化都会使目标数据库中的维度表增加一行记录，新增加的一行是原记录的一个版本。

在维度表中，一个职员记录有不同的版本，所有这些版本的【staff_id】都是相同的，【staff_id】是原表中职员的id。将“维度查询/更新”组件拖至画布，双击组件，步骤名称改为“加载 dim_staff SCD2”，并在“基本配置”标签页中连接“sakila_dwh”数据库，将数据流中的关键字字段与表中字段对应，具体配置如下图所示：

再选择“字段”标签页，插入需要更新的表字段和所对应的数据流字段；

接着选择“关键字段”标签页，选择代理关键字段为【staff_key】，并勾选“使用自增字段”来创建代理键，用来在数据仓库内部中的维度表和事实表建立关联。具体如下图所示：

最后选择“版本控制”标签页，选择对应的“Version 字段”、“Stream 日期字段”、“开始日期字段”和“截止日期字段”，具体配置如下图所示：

在维度表中，一个职员记录有不同的版本，所有这些版本的【staff_id】都是相同的，【staff_id】是原表中职员的 id。另外，维度表还有一对列【staff_valid_from】和【staff_valid_through】，用来说明每个职员版本适用的时间。此外还有列【staff_version_number】是用来维护这些数据的版本号。

加载维度表 dim_customer

“加载 dim_customer”是 load_rentals 作业里的第二个作业项。目的是加载维度 dim_customer，转换工作流如下图所示。它与“加载 dim_staff”转换的结构类似，下面我们将分别介绍该转换的步骤。

（1）变更数据捕获与抽取 转换开始的地方是两个“表输入”控件，与“加载 dim_staff”一样，用来捕获并抽取变更的数据。

“获取 dim_customer 最后一次更新时间”步骤的配置如下图所示：

步骤中的查询语句为：

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

“Customer”步骤的配置如下图所示：

查询语句为：

SELECT *
FROM customer
WHERE last_update > ?

（2）使用子转换查找 Customer 地址 “查找 Customer 地址”步骤是使用“映射（子转换）”控件，它允许重复调用一个存在的转换。双击步骤，打开配置窗口，“查找 Customer 地址”步骤的配置如下图所示。“查找 Customer 地址”是用了“fetch_address 子转换”，该转换在后面小节中介绍。在转换的文件或目录中，点击“浏览”，选中“fetch_ address 子转换”加载到该步骤中。在将“查找 Customer 地址”和“Customer”的结果集根据“address_id”字段进行排序，通过“记录集连接”连接到一起

“查找 Customer 地址”步骤的目的是为了查找客户的地址，这种做法对于反正规化的维度表 dim_customer 是非常有必要的，维度表里的数据来自 address 表以及和它相关的 city、country 表。dim_store 维度表同样也是反正规化设计的，也需要引用地址数据，所以我们要把获取地址的转换作为子转换，供不同转换使用。

（3）转换和映射 Active 标记 “加载 dim_customer”转换中的“字段选择”和“值映射”步骤的作用和“加载 dim_staff”转换是一样的，用来把一个 active 值转化为 Yes/No 值。

“字段选择”步骤的配置如下图所示：

“值转换”步骤的配置如下图所示：

（4）加载类型 2 缓慢变化维度表 Customer 转换的最后一个步骤为把数据缓慢加载到维度表 dim_customer 中，“更新 Customer SCD”步骤的配置如下图所示：

加载维度表 dim_store

“加载 dim_store”转换的结构和“加载 dim_customer”非常相似，就是为了加载缓慢变化维度表 dim_store。该转换的工作流如下图所示。

（1）变更数据捕获与抽取 在该转换中，变更数据捕获的原理与“加载 dim_staff”转换和“加载 dim_customer”转换的原理基本相同，它同样使用“维度更新查找”组件来加载并维护维度历史信息。区别在于数据原表和目标表变成了 store 和 dim_store。

“获取 dim_store 最后一次更新时间”步骤从 sakila_dwh 数据库的 dim_store 表中获取最后一次更新时间，SQL 语句如下：

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

“Store”步骤从 sakila 数据库的 store 表中获取数据，SQL 语句如下：

SELECT	*
FROM	store
WHERE 	last_update > ?

（2）使用子转换查找 Store 地址

（3）查询 Store 数据 “查找 Store 数据库”步骤是使用“数据库查询”组件，该步骤使用 SQL 语句从数据库中查询数据，前面步骤来的数据作为 SQL 语句的参数，这些参数一般是数据库中的主键或者唯一约束。该步骤的输出列包括了输入列的所有字段，加上在查询中新增的字段。

本转换里的“查询 Store 数据库”步骤用于反正规化源数据库里的 store 表，以便可以加载到 dim_store 维度表中。步骤的配置如下图所示。

（4）加载类型 2 缓慢变化维度表 Store

fetch_address 子转换

在“加载 dim_customer”转换和“加载 dim_store”转换中使用了“映射（子转换）”组件调用 fetch_address 转换。该转换的工作流如下图所示。

（1）地址数据级联查询 “fetch_address”子转换中包含了一系列的“数据库查询”组件，它对应的步骤包括“查询 Address”、“查询 City”和“查询 Country”。这些步骤形成了级联查询。

“查询 Address”步骤使用输入流的【address_id】字段在 address 表中查询到对应的数据化，然后作为查询结果字段之一的【city_id】增加到输出流。

接下来“查询 City”步骤使用【city_id】字段在表 city 中获得对应的数据行，并把【country_id】增加到输出流。

最后，“查询 Country”步骤使用【country_id】字段作为查询条件从表 country 中获得对应的数据行，至此，输出流中已经获得了与输出流中【address_id】对应的所有地址数据。

（2）更多的反正规化：拼接地址信息 在 sakila 数据库中，address 表用两列【address】和【address2】来存放多行地址，但是 dim_customer 维度表和 dim_store 维度表中只有一个地址字段，所以必须要处理多行地址。

“fetch_address”转换里有一个过滤步骤“是否包含多行地址？”，这个步骤将地址输入流分为两个输出流：一个数据流是多行地址的情况，另一个数据流是单行地址的情况。“是否包含多行地址？”步骤与“多行地址合并”步骤的连接线上有一个钩号标记，表示“true”输出。相反地，禁止标记表示“false”输出。

“多行地址合并”步骤使用的是“JavaScript 代码”组件，目的是把多行地址合成一个字段。

无论是多行地址还是单行地址，数据最终都流向“字段选择”步骤，在这个转换中，“字段选择”步骤只选择输出需要的字段，丢弃了所有类似于【city_id】和【country_id】这样的中间字段。虽然也可以输出“fetch_address”转换里产生的所有字段，但不建议这样做，因为这样所有调用这个子转换的转换不得不计算哪些字段是有用的哪些是没用的。另外，一个外部转换可能有意无意地使用了子转换输出的某些字段。所以在维护“fetch_address”子转换时，需要确认这些字段是否被使用。

（3）子转换接口 “fetch_address”转换开始和结束步骤分别是“映射输入规范”和“映射输出规范”组件，任何子转换都需要这两个步骤，它们是子转换的接口。

“映射输入规范”组件是一个特殊的输入组件，它把外部转换的数据注入到子转换里，同时也定义了要使用数据流中的哪几个字段。“输入 address_id”步骤指定了需要从输入流中获取一个【input_id】字段，配置如下图所示。

“映射输出规范”组件是一个特殊的输出组件，它定义了本地转换的数据流从哪里传给外部转换。

加载维度表 dim_actor

“加载 dim_actor”转换时用来加载维度表 dim_actor的，它是目前为止最简单的一个转换，工作流入下图所示。

（1）变更数据捕获与抽取 该转换的变更数据捕获方式与之前所有转换相同，只是源表和目标表变为了 actor 和 dim_actor。

“获取 dim_actor 最后一次更新时间”步骤的 SQL 语句为：

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

“Actor”步骤的 SQL 语句为：

SELECT *
FROM actor
WHERE last_update > ?

（2）加载维度表 actor “加载 dim_actor”转换使用“插入/更新”步骤把数据加载到表 dim_actor 中。该步骤的工作原理是使用数据库表的关键字比较输入流的关键字，如果数据流里的关键字在数据库中已经存在，程序将更新数据库里该关键字数据行里的某些指定字段。如果这行数据不存在，程序将把数据流里的这行数据写入到数据库中。

对于 dim_actor 维度表，“插入/更新”步骤已经足够了，因为 dim_actor 维度表并不是一个缓慢变化维度（至少不是类型 2 的缓慢变化维度），所以我们只做更新就可以。例如，演员名字写错了，直接修改就可以。

加载维度表 dim_film

“加载 dim_film”转换时用来加载 dim_film 维度表和 dim_film_actor_bridge 连接表的，工作流如下图所示。“加载 dim_film”转换是我们目前遇到的最复杂的转换，因为它需要同时加载两张表。从工作流图可以发现整个转换的前半部分是从“获取 dim_film 最后一次更新时间”步骤到“加载 di_film”步骤，这部分主要负责加载维度表 dim_ film。下面部分从“获取 film_actor”步骤到“加载 film_actor 桥接表”步骤结束，用来加载 dim_film_actor_ bridge 表。另一个导致转换比较复杂的原因是需要解决 dim_film 表的几个多值维度问题。最后，表 dim_film 本身还要处理非正规化问题，例如，原表引用了语言表，要通过数据库查询做反正规化处理。

（1）变更数据捕获与抽取 该转换的变更数据捕获方式与之前所有转换相同，只是源表和目标表变为了 film 和 dim_film。

“获取 dim_film 最后一次更新时间”步骤的 SQL 语句为：

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

“Film”步骤从 sakila 数据库的 film 表中获取数据，SQL 语句如下：

SELECT *
FROM film
WHERE last_update > ?

（2）查找电影语言 该转换使用了两个“数据库查询”控件来查找电影的语言，其中一个步骤是“查找 language”，参数配置如下图所示。

另一个步骤是“查找 origin_language”，它的参数配置如下图所示。

（3）映射 Rating 字段 然后使用“值映射”控件转换源数据库 film 表中的 rating 字段为 rating_text 字段。

（4）分割 special_features 列表 源数据库 film 表中的电影特征列里的值是以逗号分隔的字符串，为了把这个字符串转换成维度表里的多个特征列，首先要把逗号分隔的字符串正则化成一组行，这样后面才能把每个电影里的多个特征值放到电影维度表的多个特征列里。

“正规化 special_features”步骤用来解析和分割特征值字符串。这是一个“列拆分为多行”步骤，这个步骤可以把一个列里以分隔符分割的字符串，按分隔符拆分为多个数据行。

（5）将每个特征值扁平化为 Y/N 标志 尽管把一个分隔符分割的特征字符串列表正规化为多行的形式便于处理每个特征值，但这也容易引入数据重复的问题。有时候我们也可以重新扁平化每个电影的多行数据，但这里我们把每个特征值都存储在自己的列中，而不是存储在一个列表中。

在此之前先使用“增加常量”控件增加【Yes】和【No】字段，参数设置如下图所示。

“反正规化 special_feature 至 Yes 列”步骤是一个“列转行”控件，之所以叫这个名字是因为它可以把多行数据转换为多列数据，步骤配置如下图所示。

然后对空值填充为 No。

（5）创建电影分类字段

（6）加载 dim_film 表

（7）加载 dim_film_actor_bridge 表

加载事实表 fact_rental

“获取fact_rental最后一次更新时间”步骤的SQL语句为：

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

“获取更新的rentals”步骤是从rental表中获取最新数据，SQL语句如下：

SELECT 
	rental_id,
	rental_date,
	inventory_id,
	customer_id,
	return_date,
	staff_id,
	last_update
FROM sakila.rental
WHERE last_update > ?

“获取日期时间智能键”步骤是根据rental_fact表引入日期相关列，配置如下图所示：

“是否有归还日期”是判断租借商品是否归还，以便计算租期。使用【过滤】转换，具有归还日期的行输出到“计算租期”步骤，不具有归还日期的行输出到“设置租期为空”步骤。配置如下图所示：

“计算租期”步骤使用【计算器】转换计算租借时间等相关信息，具体配置如下图所示：

“设置租期为空”步骤使用【增加常量】转换为归还日期为空的行设置一些默认值，配置如下图所示：

“查询film_id和store_id”步骤使用【数据库查询】转换根据inventory_id从inventory表中查询film_id和store_id信息，配置如下图所示：

“查询film_key”步骤使用【数据库查询】转换根据film_id从dim_film表中查询film_key信息，配置如下图所示：

查询dim_customer_key步骤使用【维度查询/更新】转换根据customer_id从dim_customer表中查询dim_customer_key信息，配置如下图所示：

同样，查询dim_staff_key和“查询dim_store_key”的配置分别如图所示：

查询dim_staff_key：

查询dim_store_key：

最后将所有处理后的数据输出到fact_rental表中，使用到的转换为【插入/更新】转换。配置如下图所示：