ETL示例解决方案 —— Sakila示例之load_dim_film转换（笔记八）

load_dim_film转换

load_dim_film转换用来加载dim_film维度表和dim_film_actor_bridge连接表。
　　load_dim_film转换是目前最为复杂的转换，它需要同时加载两张表。整个转换可以分为两个部分，前半部分 “max_dim_film_update”步骤到 “ load_dim_film ”步骤。这部分主要加载维度表dim_film。
　　后半部分从 “ Get film_actor ” 步骤开始，到 “ dim_file_actor_bridge ” 步骤结束，用来加载dim_film_actor_bridge表。
　　另一个导致转换比较复杂的原因是需要解决dim_film表的几个多值维度问题。最后，表dim_film本身还要处理非正规化问题，例如，原表引用了语言表，要通过数据库查询做反正规化处理。
　　
load_dim_film转换中和前面转换相同的步骤

• 使用两个 “ 表输入 ” 步骤max_film_last_update和Film来捕获变化的数据。
• 两个数据库查询步骤：Lookup Language 和Lookup Original Language ，用来查找电影的语言。
• 使用值映射步骤：Rating Text转换源数据库film表中的rating字段的值。

前面已经详细描述了这些步骤，这里就不再赘述。下面是未介绍过的的内容。

• 源数据库film表里的special_features字段是一个非原子的值列表（MySQL的SET类型），在dim_film表中，它被转化成一组字段，字段值是Y/N，字段名是film_has_%。
• 源sakila库中，存储在film_categroy和category表中的电影多个分类也需要扁平化、非正规化成dim_film表中的一组字段（film_is_%）的Y/N值。

上面介绍了加载dim_film表的转换，对于表dim_film_actor_birdge，要说明两件相关但不同的事情：

• 对于添加到dim_film维度表中的每一行，必须要获取到这个电影里所有演员的列表。对于每个演员，还要从之前加载的dim_actor表中查找对应的actor_key，以便插入到dim_film_actor_bridge表。
• 对于添加到dim_film_actor_bridge表中的数据，为了把dim_actor维度分配给fact_rental事实表的度量列，必须给每个电影里的演员计算和分配一个权重因子。

下面详细讨论上面这些转换技术。

分割special_features列表
　　源数据库film表中的电影特征列里的值是以逗号分隔的字符串，为了把这个字符串转换成维度表里的多个特征列，首先要把逗号分隔的字符串正规化成一组行。这样后面才能把每个电影里的多个特征值放到电影维度的多个特征列里。
　　“Normalize Special Features ”步骤用来解析和分割特征值字符串。这是一个 “ 列拆分为多行 ” 步骤。这个步骤可以吧一个列里以分隔符分割的字符串，按分隔符拆分为多个数据行。

　　例如，输入流里有一个special_features列表，值是 “ Deleted Scenes，Trailers ”，那么该步骤就会在输出流中输出两行：一个是 “ Deleted Scenes ” ，另一个是 “ Trailers ”。除了special_feature字段，这俩行中的其余字段完全相同。

将每个特征值扁平化为Y/N标志
　　尽管把一个分隔符分割的特征字符串列表正规化为多行的形式便于处理每个特征值，但这也容易引入数据重复问题。有时候我们也可以重新扁平化每个电影的多行数据，但这里我们把每个特征值都存储在自己的列中，而不是存储在一个列的列表中。“ Denormalize Special Features to ‘Yes’ Columns ” 步骤完成列这个功能。

　　“ Denormalize Special Features to ‘Yes’ Columns ” 步骤是一个 “ 行转换（反正规化）”类型的步骤。之所以叫这个名字是因为它可以把多行数据转换为多列数据。该步骤有三个重要的配置项如下：

• 关键字段：此字段将被反正规化，根据这个字段生成多列。
• 构成分组的字段：要确定一组数据所需要的字段，一般来说，这些是来自输入流的你想在输出流保留的字段。
• 目标字段：这里定义列要添加到输出流里的字段，用来指定一组数据里关键字段里的唯一值应该写到哪个输出字段里。例如，设置的第一行表示，当关键字段的值是trailers时，程序向 “ tmp_has_trailers ” 字段里写入 “Yes”。

注意：在 “ 行转列 ” 的配置对话框中，对于 “ 目标字段 ” 的解释稍微有些不正确。存储在新字段的值不是一个 “ Yes ” 字符串，实际它是一个字段为 “ Yes ” 的字段的值。在本步骤前面有一个 “ Add Yes/No ” 步骤，这个步骤定义列两个常量字段，字段名称分别为 “ Yes ” 和 “ No ” ，相应的值也为“ Yes ” 和 “ No ” 。
创建电影分类字段
　　在sakila数据库中，电影属于多个分类。film_categroy表存储电影分类信息，这个表里有指向film表和category表的外键。在租凭星型模型中，分类信息存储在dim_film维度表中，每个分类都是一个值为 Yes/No 字段。这种处理方法和上面电影特征的处理方法类似。
　　“ Get film_catrgories ” 步骤用来获取每部电影的分类列表。该步骤是一个 “ 数据库连接 ” 步骤。与 “ 数据库查询 ” 步骤相同，“ 数据库连接 ” 步骤也要指向SQL查询，此SQL查询使用输入流中的数据作为参数。不同的是，在“ 数据库查询 ” 步骤中，为了从数据库中查找每一行，参数通常映射到一列主键或唯一约束上；在“ 数据库连接 ” 步骤，为了能检索到一组数据，参数通常映射到外键上。
　　与“ 数据库查询 ” 类似，“ 数据库连接 ” 步骤类型向输出流中增加了数据库中的很多指定字段。不同的是，“ 数据库查询 ” 步骤对于每一行输入流最多只能向输出流返回一行，而“ 数据库连接 ” 步骤是从数据库中返回的每一行都输出到输出流中。换句话说，“ 数据库连接 ” 步骤包含了 “ 数据库查询 ” 的结果，同时还可能返回了更多的数据行。
　　“ Get film_categories ” 步骤把每行电影转换成了分类不同的多行数据。该步骤后面也有一个和前面类似的行转列步骤 “ Denormalize Special Features to ‘Yes’ Columns ” ，这个步骤把电影的多个分类（多行数据）都转换成电影维度表里的多个列，列的值是 “ Yes/No ”。

加载dim_film表
　　加载dim_film维度表是通过load_dim_film步骤完成的。这个步骤是一个 “ 联合查询/更新 ” 步骤，这个步骤和 “ 维度查询/更新 ” 步骤类似。在这里把这个步骤当作load_dim_actor转换中的 “ 插入/更新 ” 步骤就可以。与 “ 插入/更新 ” 步骤类型不同的是， “ 联合查询/更新 ” 步骤还会返回该维度行的键。这是为什么这里要选择这个特殊步骤的原因：我们需要dim_film的键，在后面的转换中需要把它们加载到dim_film_actor_bridge表里。
加载dim_film_actor_birdge表
　　加载dim_film_actor_birdge表的第一步是从sakila数据库的film_actor表中获取电影的演员名称。通过 “ 数据库连接 ” 步骤 “ Get film_actor ” 可以得到演员名称。实际上，装载桥接表的时候，还需要获取另外两个数据。这就是为什么 “ Get film_actor ” 步骤有两个输出（注意下图中的 “ Get film_actor ” 步骤有两个输出流）。
　　首先，需要从dim_actor表查找actor_key的值。对于 “ 数据库查询 ” 步骤来说，这是一个简单的任务。在load_dim_film转换中，通过 “ Lookup dim_actor ” 步骤来实现。
　　其次，需要计算一个权重因子，并将它连同每个film_key / actor_key组合存储在dim_film_actor_bridge表中。因为sakila数据库中没有可以确定每一个演员对电影实际贡献的数据，所以可以简单地加上每个演员都有相同的权重。可以通过以下公式来计算每个电影中任意演员的权重：
　　权重 = 1 / < 电影中的演员数>
使用两个步骤计算这个公式：

• “ Calculate actor count ” 步骤是 “ 分组 ”类型的步骤，主要做两件事：
  　　1.它与SQL语言中的GROUP BY子句相似，基于指定的字段（或字段集），将输入流中的数据进行分组；
  　　2.为整个分组生成一个或多个聚集值，发送到输出流。
• “ Calculate Actor weighting factor ” 步骤是计算器类型步骤，运行除法 1/ count_actors，然后将结果保存到actor_weighting_factor字段。
  　　我们得到了 actor_key 和 actor_weighting_factor，但是这些数据仍然还在它们自己的流中。因此，使用 “ Lookup actor weighting factor ”步骤将它们重新连接起来。这个步骤是 “ 流查询 ” 类型的步骤，它使用从 “Lookup dim_actor ” 步骤获得的film_key，从“ Calculate Actor weighting factor ” 步骤中寻找匹配的行，从而将actor_weighting_factor字段从 “ Calculate Actor weighting factor ” 步骤传递到 “ Lookup actor weighting factor ” 步骤的输出流中。
  　　执行完“ Lookup actor weighting factor ” 步骤之后，有一个包含film_key、actor_key和actor_weighting_factor字段的数据流。使用一个简单的 “ 插入/更新 ”步骤，这个数据流就可以加载到dim_film_actor_bridge表中。