综合案例—构建DVD租赁商店数据仓库

一、案例概述

（一）案例背景

        sakila样本数据库是MySQL官方提供的一个模拟DVD租赁商店管理的数据库。本次就是要对数据库sakila中的数据进行清洗操作，从而构建一个DVD租赁商店数据仓库系统，即实现定期从源数据库sakila中抽取增量数据，转换成符合DVD租赁业务的数据，最后加载到目标数据仓库中，便于在线DVD租赁商店的决策者对数据进行分析得出商业决策。

（二）数据仓库的架构模型

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

二、数据准备

        我们可以从MySQL的官网下载数据库sakila的建库脚本，若是在Windows环境下安装数据库sakila，则下载名称为sakila-db.zip的压缩包文件；若是在Linux环境下安装数据库sakila，需要下载名称为sakila-db.tar.gz的压缩包文件。

        本次下载的是名称为sakila-db.zip的压缩包文件，该压缩包文件中包含三个文件，分别是文件sakila.mwb、文件sakila-data.sql和文件sakila-schema.sql。其中，文件sakila.mwb是一个MySQL Workbench数据模型，用于查看数据库结构；文件sakila-data.sql是用于创建数据库sakila的数据；文件sakila-schema.sql是用于创建数据库sakila的数据结构。使用MySQL图形化管理软件SQLyog先运行脚本文件sakila-schema.sql创建数据库sakila和数据表，再运行脚本文件sakila-data.sql向数据库sakila中的数据表加载数据

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

案例实现：通过Kettle工具加载日期数据至dim_date日期维度表。

使用Kettle工具，创建一个转换，并添加生成记录控件、增加序列控件、JavaScript代码控件、表输出控件以及Hop跳连接线，具体如图所示。

配置生成记录控件 

配置增加序列控件

配置JavaScript控件

配置表输出控件

 运行转换

 查看数据表dim_date是否已成功插入3650条日期数据，查看结果如图所示：

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

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

使用Kettle工具，创建一个转换，并添加生成记录控件、增加序列控件、JavaScript代码控件、记录关联（笛卡尔输出）控件、表输出控件以及Hop跳连接线，具体如图所示。

配置生成记录控件

 配置增加序列控件

配置JavaScript代码控件

 配置生成记录2控件

配置增加序列2控件

 配置生成记录3控件

 配置增加序列3控件

配置JavaScript代码2控件

配置表输出控件

运行转换

查看数据表dim_time中的数据

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

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

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

 配置表输入控件

配置表输入2控件

 配置字段选择控件

配置值映射控件

配置维度查询/更新控件 

 运行转换

查看dim_staff表中的数据

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

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

 配置表输入控件

配置表输入2控件

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

配置映射输入规范控件

 配置数据库查询控件

配置数据库查询2控件

配置数据库查询3控件

配置过滤记录控件

配置JavaScript代码控件

 配置字段选择控件

双击“字段选择”控件，进入“选择/改名值”界面，在“元数据”选项卡的“移除”处添加要移除的字段，如图所示 

配置映射控件

双击“映射”控件，进入“映射”界面，单击“转换”选项卡处的【Browser】按钮，选择添加转换fetch_address，用于获取用户的地址信息，如图所示。

配置字段选择控件

 配置值映射控件 

配置维度查询/更新控件

 运行转换

 查看dim_customer表中的数据

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

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

配置表输入控件：双击进入表输入控件，在SQL框中编写SQL语句，用于获取字段store_last_update中的最大值，将该值替换为”1970-01-01 00:00:00”并赋值给临时字max_dim_store_last_update；单击“预览”按钮，查看临时字段max_dim_store_last_update是否将默认值设置为“1970-01-01 00:00:00” ，如图所示。

表输入2控件：双击进入表输入2控件，在SQL框中编写SQL语句，用于获取sakila数据 库中store数据表中的最新数据，如图所示。

映射（子转换）控件：双击“映射”控件，进入“映射”界面，单击“映射转换”选项卡处的【浏览】按钮，选择添加转换2（4.5步骤中的转换2），用于获取用户的地址信息，如图所示。 

数据库查询控件：双击进入控件配置界面，单击表名右侧的【浏览】按钮，添加staff数据表，用于查询商店员工的信息；在“查询所需的关键字”框中，添加查询所需的关键字staff_id，用于指定表字段和流字段的比较条件；在“查询表返回的值”框中，添加查询表返回的值，即员工姓名，如图所示。 

维度查询/更新控件：单击【浏览】按钮，选择输出的目标表，即维度表dim_store；在“关键字”选项卡处添加关键字字段store_id，用于指定维度表字段和流字段的比较条件；在“字段”选项卡处添加查询/更新字段，用于指定维度表字段store_id和流字段store_id数据一致需要更新的字段，；在“代理关键字段”处的下拉框中选择store_key为代理关键字段，并指定“创建代理键”是使用自增字段；在“Version字段”处的下拉框中选择store_version_number；在“Stream日期字段”处的下拉框中选择last_update；在“开始日期字段”处的下拉框中选择store_valid_from；在“截止日期字段”处的下拉框中选择store_valid_through，如图所示。

运行转换

查看dim_store中的数据

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

使用Kettle工具，创建一个转换，并添加表输入控件、插入/更新控件以及Hop跳连接线，具体如图所示。 

配置表输入控件：双击进入控件，在SQL框中编写SQL语句，用于获取字段actor_last_update中的最大值，将该值替换为”1970-01-01 00:00:00”并赋值给临时字段max_dim_actor_last_update；单击“预览”按钮，查看临时字段max_dim_actor_last_update是否将默认值设置为“1970-01-01 00:00:00”，如图所示。

表输入2控件：双击进入表输入2控件，在SQL框中编写SQL语句，用于获取数据库sakila中数据表actor中的最新数据，如图所示

插入/更新控件：单击目标表右侧的【浏览】按钮，弹出“数据库浏览器”窗口，选择目标表，即维度表dim_actor，单击【获取字段】按钮，用来指定查询数据所需要的关键字，这里选择的dim_actor数据表中的actor_id字段和输入流里面的actor_id字段；单击【获取和更新字段】按钮，用来指定需要更新的字段，如图所示。 

运行转换

 查看dim_actor 数据