kettle构建基于sakila数据库的DVD租赁商店数据仓库

一、案例介绍

1.1 案例背景

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

1.2 数据仓库的架构模型

数据仓库sakila_dwh的架构模型是一个星型模型，包括dim_date和dim_date两个静态维度表和dim_staff、dim_store、dim_customer、dim_actor、dim_film、dim_film_actor_bridge、rental_fact七个动态维度表。

二、数据准备

2.1 sakila源数据库介绍

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

2.2sakila源数据库加载

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

2.3 sakila_dwh数据仓库搭建

使用MySQL图形化管理软件MySqlWorkbench运行sakila_dw_schema.sql脚本文件创建sakila_dw数据仓库即可。

三、案例实施

Kettle是一款国外开源的ETL工具，Spoon是Kettle中的一个图形化组件，本etl项目用Spoon软件操作完成。

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

创建转换

在这里插入图片描述

配置控件

生成记录控件
在这里插入图片描述

增加序列控件

在这里插入图片描述

JavaScript控件

在这里插入图片描述

连接sakila_dwh数据库

在这里插入图片描述

进入表输出控件，连接好数据库，选择目标表为dim_date

在这里插入图片描述

点击数据库字段，添加字段映射

在这里插入图片描述

保存执行

在这里插入图片描述

此时已将3650条记录导入sakila_dwh数据库中的dim_date表，查看表中部分数据如下

在这里插入图片描述

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

创建转换

在这里插入图片描述

配置控件

生成记录2控件：

用来生成24小时数据

在这里插入图片描述

增加序列2控件：

在这里插入图片描述

JavaScript代码控件：

在这里插入图片描述

生成记录3控件：

用来生成60条分钟数据（从0分到59分共60条数据）

在这里插入图片描述

增加序列3控件：

在这里插入图片描述

生成记录4控件：

用来生成60条秒数据

在这里插入图片描述

增加序列4控件：

在这里插入图片描述

记录关联（笛卡尔输出）控件：（此控件在此无需设置）
在这里插入图片描述

JavaScript代码3控件：
在这里插入图片描述

表输出2控件：
在这里插入图片描述

至此，所有控件配置完毕，保存执行
在这里插入图片描述

数据库中的部分数据如下：

在这里插入图片描述

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

创建转换：

在这里插入图片描述

配置控件：

表输入控件：

在这里插入图片描述

表输入2控件：

在这里插入图片描述

字段选择控件：

由于dim_staff表中的staff_active的数据类型为char类型，所以将staff表中的active改为string类型

在这里插入图片描述

值映射控件：

在这里插入图片描述

维度查询/更新控件：

在这里插入图片描述

至此所有控件配置完成，保存执行：

在这里插入图片描述

数据库中数据如下；

在这里插入图片描述

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

创建转换1

在这里插入图片描述

创建转换2

在这里插入图片描述

转换2控件配置

映射输入规范控件

在这里插入图片描述

数据库查询控件

在这里插入图片描述

数据库查询2控件

在这里插入图片描述

数据库查询3控件

在这里插入图片描述

过滤记录控件

在这里插入图片描述

JavaScript代码控件

在这里插入图片描述

字段选择控件

在这里插入图片描述

至此，转换2控件配置完成，保存关闭，为转换1映射配置做准备。

转换1控件配置

表输入控件

在这里插入图片描述

表输入2控件

在这里插入图片描述

映射（子转换）控件

转换处浏览选择添加转换2文件，用于获取用户的地址信息

在这里插入图片描述

字段选择控件

在这里插入图片描述

值映射控件

数据表active中的active字段的值为0和1，对应的是Y和N，将Y替换为Yes，将N替换为No.

在这里插入图片描述

维度查询/更新控件

在这里插入图片描述

至此，所有控件配置完成，保存执行

在这里插入图片描述

查看数据库dim_customer表部分数据如下

在这里插入图片描述

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

创建转换

在这里插入图片描述

配置控件

表输入控件

在这里插入图片描述

表输入2控件

编写sql语句用于获取sakila数据库中store表的最新数据

在这里插入图片描述

映射（子转换）控件

浏览添加4.4步骤中的转换2，用于获取用户的地址信息

在这里插入图片描述

数据库查询控件

在这里插入图片描述

维度查询/更新控件

在这里插入图片描述

至此，所有控件配置完成

保存执行

在这里插入图片描述

查看数据库dim_store表中的部分数据如下

在这里插入图片描述

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

创建转换

在这里插入图片描述

配置控件

表输入控件

在这里插入图片描述

表输入2控件

编写sql语句用于获取数据库sakila数据表actor中的最新数据

在这里插入图片描述

插入/更新控件

在这里插入图片描述

至此，所有控件配置完成

保存并执行

在这里插入图片描述

查看数据库sikala_dwh中的dim_actor表中部分数据如下

在这里插入图片描述

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

创建转换

在这里插入图片描述

配置控件

表输入控件

编写sql语句获取上次更新的时间

在这里插入图片描述

表输入2控件

从源数据库中的film表中获取最新数据

在这里插入图片描述

数据库查询控件

由于源数据库sakila库中的film表中有个language_id字段，却没有language_name字段，所以需要借助language_id查询sakila库中的language表找到language_name字段信息

在这里插入图片描述

数据库查询2控件

由于源数据库sakila库中的film表中有original_language_id字段，却没有original_language字段，所以需要借助original_language_id查询sakila库中的language表找到original_language字段信息

在这里插入图片描述

值映射控件

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

在这里插入图片描述

列拆分为多行控件

feature是电影的特点字段，源数据库sakila中是将多个特点放在一起采用“,”连接，这里要将每部电影的特点以“,”为分隔进行一一拆开，将原来的一行内容变为多行

在这里插入图片描述

增加常量控件

因为ETL数据仓库sakila_dwh里的dim_film表有film_has_trailers（是否有预告片）和film_has_commentaries（是否有评价）字段，其内容不是Yes就是No，所以要在本身的流数据里面增加两列常量数据。

在这里插入图片描述

列转行转换控件

因为之前将feature列拆分为多行，生成了special_feature字段，每部电影的此字段都有多行，因此需要借助列转行进行转置。注意：获取字段后需在构成分组的字段中删除掉special_feature字段，不然后面会出错。

在这里插入图片描述

结果生成4个新字段，去除了原来的special_feature和Yes字段

在这里插入图片描述

计算器控件

添加新字段用于存储将列转行控件流中的special_feature字段的NULL值替换成No的数据

在这里插入图片描述

数据库连接查询控件

因为之前流字段没有电影分类信息，category_id在film_category中有，所以借助数据库连接查询来关联film_category表，查询出category_id字段信息。

在这里插入图片描述

查询出category_id信息如下

在这里插入图片描述

数据库查询控件

上一步获取了category_id，还要再次借助category_id查询sakila库中的表category找到category_name字段信息

在这里插入图片描述

增加常量2控件

在这里插入图片描述

列转行控件

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

在这里插入图片描述

计算器控件2

将上一步新生成的16个字段中的null值替换成No

在这里插入图片描述

联合查询/更新控件

连接sakila_dwh数据库，选择dim_film表，进行数据加载输出

在这里插入图片描述

查看数据库sakila_dwh中dim_film表中的部分数据

在这里插入图片描述

3.8 加载电影_演员关联表dim_film_actor_bridge表数据

在3.7的基础上继续进行联合查询/更新后面的控件配置

数据库连接2控件：

编写sql语句根据电影id获取演员id，从而获取演员基本信息

在这里插入图片描述

数据库查询4控件

借助actor_id字段在sakila_dwh库的dim_actor表中查询actor_key字段信息

在这里插入图片描述

分组控件

按film_key字段分组，统计每部电影中演员的总数count_actors，为后续计算演员权重因子actor_weighting_factor做准备

在这里插入图片描述

计算器3控件

计算演员权重因子actor_weighting_factor，设置一个辅助变量one进行辅助计算，做移除处理

在这里插入图片描述

流查询控件

根据film_key字段来查询演员权重因子actor_weighting_factor字段

在这里插入图片描述

插入/更新控件

将准备好的字段信息插入到sakila_dwh库中的dim_film_actor_bridge表中

在这里插入图片描述

至此，执行整个转换。注意：如果4.7时已经执行过转换，则这次执行转换前应该先在数据库中将dim_film表中的信息清空，因为一整个转换执行一次会加载两个表，而di_film表此前已被加载过信息，再次加载相同数据会报错。

查看库中dim_film_actor_bridge部分数据如下：

在这里插入图片描述

3.9加载租赁事实表fact_rental

创建转换

在这里插入图片描述

配置控件

表输入控件

编写sql语句用于获取字段rental_last_update中的最大值，将该值替换为“1970-01-01 00:00:00”并赋值给临时字段max_fact_rental_last_update。

在这里插入图片描述

表输入2控件

编写sql语句用于获取sakila数据库中rental表中的最新数据

在这里插入图片描述

字段选择控件

构建数据仓库中维度表fact_rental需要的字段数据。

在这里插入图片描述

过滤记录控件

检查是否有归还日期renturn_datetime，若有，数据流向计算器步骤，将count_returns度量值设置为1，再计算rental_duration的值，即用归还日期减去租借日期；若没有，数据流向增加常量步骤，该步骤将度量值count_returns和rental_duration设置为空。

在这里插入图片描述