Sakila构建DVD租赁商店数据仓库（etl）

一、案例介绍

      Sakila样本数据库是MySQL官方提供的一个模拟DVD租赁商店管理的数据库。基于ETL工具，对数据库Sakila中的数据进行清洗操作，从而构建一个DVD租赁商店数据仓库系统，即实现定期从源数据仓库Sakila中抽取增量数据，转换成符合DVD租赁业务的数据，最后加载到目标仓库中。

二、Sakila数据库介绍

1. 资源下载

百度网盘 请输入提取码
提取码：uol3

或者官网下载

https://dev.mysql.com/doc/index-other.html

2.sakila数据库介绍

Sakila示例数据库中的基本表和视图简要介绍如下：

1. actor - 这张表存储了所有演员的信息，包括演员的ID、姓名和最后更新时间。

2. actor_info - 这个视图结合了 actor、film_actor 和 film 表，提供了更详细的演员信息，包括演员所出演的电影名称和描述。

3. address - 这张表存储了客户的地址信息，包括地址ID、地址、区域、城市、邮编、国家和最后更新日期。

4. category - 这张表存储了所有电影的分类信息，包括分类ID 和分类名称。

5. city - 这张表存储了城市的信息，包括城市ID、城市名称、区域以及最后更新时间。

6. country - 这张表存储了国家的信息，包括国家ID、国家名称以及最后更新时间。

7. customer - 这张表存储了客户信息，包括客户ID、姓名、电子邮件、地址、区域、城市、邮编以及最后更新时间。

8. customer_list - 这个视图结合了 customer、address 和 city 表，提供了更详细的客户信息，包括客户ID、姓名、电子邮件、地址、区域、城市、邮编、国家和最后更新日期。

9. film - 这张表存储了所有电影的信息，包括电影ID、电影名称、描述、发布年份、语言ID、原始语言ID、时长、租赁持续时间、租赁费用以及最后更新时间。

10. film_actor - 这张表存储了电影演员的关联信息，包括电影ID、演员ID以及最后更新时间。

11. film_category - 这张表存储了电影类型的关联信息，包括电影ID、分类ID以及最后更新时间。

12. film_list - 这个视图结合了 film 和 category 表，提供了更详细的电影信息，包括电影ID、电影名称、描述、发布年份、语言、分类、时长、租赁持续时间以及租赁费用。

13. film_text - 这张表存储了电影的描述信息，包括电影ID、标题、描述以及最后更新时间。 14. inventory - 这张表存储了所有电影的库存信息，包括库存ID、电影ID、租赁商店ID以及最后更新时间。

15. language - 这张表存储了语言的信息，包括语言ID、语言名称以及最后更新时间。

16. nicer_but_slower_film_list - 这个视图结合了 film_list、film_actor、actor 和 category 表，提供更详细的电影信息，包括电影ID、电影名称、描述、发布年份、语言、租赁持续时间、租赁费用、演员列表、分类列表以及平均评分。

17. payment - 这张表存储了所有顾客的付款信息，包括付款ID、顾客ID、租赁ID、付款金额、付款时间以及最后更新时间。

18. rental - 这张表存储了所有出租信息，包括租赁ID、客户ID、库存ID、租赁时间、返还时间、出租费用以及最后更新时间。

19. sales_by_film_category - 这个视图统计了每种电影类型的销售额，包括分类名称、销售额以及最后更新时间。

20. sales_by_store - 这个视图统计了每个店铺的销售额，包括店铺ID、城市、国家、销售额以及最后更新时间。

21. staff - 这张表存储了员工的信息，包括员工ID、姓名、电子邮件、店铺ID、用户名、密码以及最后更新时间。

22. staff_list - 这个视图结合了 staff 和 address 表，提供了更详细的员工信息，包括员工ID、姓名、电子邮件、地址、区域、城市、邮编、国家以及最后更新时间。

23. store - 这张表存储了店铺的信息，包括店铺ID、地址ID、经理ID以及最后更新时间。

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

      基于数据库Sakila构建一个星型模型的DVD租赁商店数据仓库，命名为sakila_dwh。数据仓库sakila_dwh中的事实表fact_rental是根据数据库Sakila中的数据表rental创建的；维度表是根据数据表sakila中的数据表的分类创建的，即从人员、时间、地点和事件四个角度进行创建数据库仓库sakila_dwh的维度表。
1. 从人员角度创建维度表dim_customer和维度表dim_staff，分别表示租赁业务中的客户和员工。
2. 从时间角度创建维度表dim_date和维度表dim_time，用于记录所有DVD租赁时间和归还时间。
3. 从地点角度创建维度表dim_store，用于记录DVD光盘是从哪个商店租赁的。
4. 从事件角度创建维度表dim_actor和dim_film，分别用于记录演员的基本信息和电影的基本信息。由于电影是租赁和归还的实际对象，因此维度表dim_film应与事实白哦fact_rental关联。一部电影由多位演员出演，所以有链接桥梁dim_film_actor_bridge，该表将电影与演员联系起来。

四、案例具体实现

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

（1）新建转换，命名为load_dim_date，设置组件：

（2）配置【生成记录】组件

（3）配置【增加序列】组件

（3）配置【JavaScript】组件

代码如下：

//Script here
//生成locale
var locale = new java.util.Locale(language.getString(),country_code.getString());
//生成Calendar
var calendar = new java.util.GregorianCalendar(locale);
//设置时间
calendar.setTime(initial_date.getDate());
//设置日历为当前日期
calendar.add(calendar.DAY_OF_MONTH,DaySequence.getInteger()-1);
//获取日期
var date = new java.util.Date(calendar.getTimeInMillis());
//生成短日期
var date_short = java.text.DateFormat.getDateInstance(java.text.DateFormat.SHORT,locale).format(date);
//生成中日期
var date_medium = java.text.DateFormat.getDateInstance(java.text.DateFormat.MEDIUM,locale).format(date);
//生成长日期
var date_long = java.text.DateFormat.getDateInstance(java.text.DateFormat.LONG,locale).format(date);
//生成全日期
var date_full = java.text.DateFormat.getDateInstance(java.text.DateFormat.FULL,locale).format(date);
//简单格式化
var simpleDateFormat = java.text.SimpleDateFormat("D",locale);
//天在年的第几天
var day_in_year = simpleDateFormat.format(date);
//建立格式器
simpleDateFormat.applyPattern("d");
//天在月的第几天
var day_in_month = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("EEEE");
//星期的名称
var day_name = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("E");
//星期的缩写
var day_abbreviation = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("ww");
//一年的第几周
var week_in_year = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("W");
//一月的第几周
var week_in_month = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("MM");
//月份
var month_number = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("MMMM");
//月的名称
var month_name = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("MMM");
//月的缩写
var month_abbreviation = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("yy");
//两位的年
var year2 = simpleDateFormat.format(date);
simpleDateFormat.applyPattern("yyyy");
//四位的年
var year4 = simpleDateFormat.format(date);
//季度名称
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;
//定义常量
var yes = "yes";
var no = "no";
//获取周的第一天
var first_day_of_week = calendar.getFirstDayOfWeek();
var day_of_week = java.util.Calendar.DAY_OF_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;
}
//日历的下一天
calendar.add(calendar.DAY_OF_MONTH,1);
//获取下一天
var next_day = new java.util.Date(calendar.getTimeInMillis());
//判断是否周的最后一天
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;
}
//判断是否为月的第一天
var is_first_day_in_month;
if(day_in_month == 1){
    is_first_day_in_month = yes;
}else{
    is_first_day_in_month = no;
}
//判断是否为月的最后一天
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;
}
//年_季度
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;

获取变量：

（4）建立sakila_dwh数据库连接

并且鼠标右键单击sakila_dwh数据库，将该数据库的共享打开，后续会用到该数据库

（5）表输出

数据库字段，输入字段映射

 

（5）运行转换，查看结果数据

运行成功。

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

（1）新建转换工程load_dim_time

（2）生成记录1，时

（3）增加序列

（4）JavaScript代码

var hours12=hours24.getInteger()%12;
var am_pm=hours24.getInteger()>12 ? "PM":"AM";

（5）生成记录2，分

（6）增加序列2

（7）生成记录3，秒

（8）增加序列3

（9）关联记录

（10）JavaScript代码2

var time=hours24.getInteger()+":"+minutes.getInteger()+":"+seconds.getInteger();
var time_key=(hours24.getInteger()<10 ? "0":"")
+hours24.getInteger()+(minutes.getInteger()<10 ? "0":"")
+minutes.getInteger()+(seconds.getInteger()<10 ? "0":"")
+seconds.getInteger();

（11）表输出

（12）执行转换，预览结果数据

这里要多次执行“获取更多行”，直到将所有数据输出到数据库中；或者在执行时将“要获得的行数”设置大一点，这样就能一次性输出。

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

（1）新建转换工程load_dim_staff

（2）表输入
 
 
预览数据

（3）表输入2，这里的SQL语句可以直接通过“获取SQL查询语句”自动生成

（4）字段选择

（5）值映射

（6）维度查询/更新

字段

（7）预览结果数据

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

（1）新建转换工程load_dim_customer

（2）表输入

SELECT coalesce (max(customer_last_update),"1970-01-01 00:00:00")
as max_dim_customer_last_update FROM dim_customer;

预览数据

（3）表输入2

SELECT
  customer_id
, store_id
, first_name
, last_name
, email
, address_id
, active
, create_date
, last_update
FROM sakila.customer

（4）新建一个转换为dim_customer的子转换
①设计转换
 
②映射输入规范

③数据库查询

④数据库查询2

⑤数据库查询3

⑥过滤记录

⑦JavaScript代码

var address=address.getString()+" "+address2.getString();

⑧字段选择

移除

（5）配置【映射（子转换）】

将刚刚新建的dim_customer的子转换进行保存，再在【映射（子转换）】组件中将“fetch_address.ktr”文件导入

（6）字段选择

（7）值映射

（8）维度查询/更新

字段

（9）运行，预览结果数据

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

（1）新建转换 load_dim_store

（2）表输入

SELECT coalesce (max(store_last_update),"1970-01-01 00:00:00")
as max_dim_store_last_update FROM dim_store;

（3）表输入2

select
store_id,
manager_staff_id,
address_id,
last_update
from store where last_update > ?

（4）映射（子转换）

同样把建立的子转换fetch_adress.ktr，导入

（5）数据库查询

（6）维度查询/更新

字段

（7）运行load_dim_store转换

查看数据表dim_store中的数据

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

（1）新建转换load_dim_actor

（2）表输入

SELECT coalesce (max(actor_last_update),"1970-01-01 00:00:00")
as max_dim_actor_last_update FROM dim_actor;

（3）表输入2

select
actor_id,
first_name,
last_name,
last_update
from actor where last_update > ?

（4）插入/更新

（5）运行load_dim_actor转换

查看dim_actor数据表

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

首先准备好数据表film数据表和空表dim_film_actor_bridge、空表dim_film
（1）新建转换load_dim_film
 （2）表输入

select
coalesce(max(film_last_update),"1970-01-01 00:00:00")
as max_dim_film_last_update
from dim_film;

（3）表输入2

select
film_id,
title,
description,
CAST(release_year AS UNSIGNED)AS release_year,
language_id,
original_language_id
rental_duration,
rental_rate,
length,
replacement_cost,
rating,
special_features,
last_update
from film where last_update > ?

（4）数据库查询

（5）数据库查询2

（6）值映射

（7）列拆分为多行

（8）增加常量

（9）列转行

（10）计算器

（11）数据库连接

（12）数据库查询3

（13）增加常量2

（14）列转行2

（15）计算器2

（16）联合查询/更新

（17）数据库连接2

select actor_id
from film_actor
where film_id = ?

（18）数据库查询4

（19）分组

（20）计算器3

（21）流查询
这里将默认值设置为0，不然后面运行过程中会出错

（22）插入/更新

（23）运行转换load_dim_film

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

准备表rental、表inventory、表dim_film和空表dim_customer、空表fact_rental
（1）新建转换load_fact_rental（2）表输入

SELECT coalesce (max(rental_last_update),"1970-01-01 00:00:00")
as max_fact_rental_last_update FROM fact_rental;

（3）表输入2

SELECT
  rental_id
, rental_date
, inventory_id
, customer_id
, return_date
, staff_id
, last_update
FROM rental where last_update > ?

（4）字段选择

（5）过滤记录

（6）计算器

（7）增加常量

（8）数据库查询

（9）数据库查询2

（10）维度查询/更新

（11）维度查询/更新2

（12）维度查询/更新3

（13）增加常量2

（14）插入/更新

（15）运行load_fact_rental

4.9 加载数据库sakila中的数据至数据仓库sakila_dwh

（1）新建作业

（2）配置转换load_dim_staff

（3）load_dim_customer

（4）load_dim_store

（5）load_dim_actor

（6）load_dim_film

（7）load-fact_rental

（8）发送邮件

地址填自己的邮箱

这里的用户密码是自己邮箱开通SMTP服务时的密码

（9）发送邮件2

配置跟上面的是一样的，上面那个邮件是用来发送作业成功，本发送邮件是用来发送作用执行失败

（10）运行作业

查看邮件

再数据库sakila_dwh中查询结果

 
至此本实验任务完成