kettle 8.2 ETL 项目实战教程
01 项目简介
01 Sakila 简介
业务简介
Sakila 是在线 DVD 租赁商店系统的数据库,Sakila 示例数据库最初由 MySQL AB 文档团队的
前成员 Mike Hillyer 开发,现在由 MySQL 文档团队维护和销售。Sakila 的主要目的是支撑 DVD
租赁商店的业务流程,下面列举了一些业务流程活动中的关键点来理解数据库是如何支撑
的。
每个商店维护自己的租赁影片清单,当客户取走或归还 DVD 光盘时会有一个专门的店
员对这个清单进行维护。
影片描述的内容同样在维护信息范围之内 ,如分类(动作、冒险、喜剧等)、演员、等
级、特殊分类(例如被删除的情节和预告片)。这些信息可能被打印在 DVD 包装的标签
上。
必须在商店注册成为会员才可以租赁光盘。
客户可以在任何一家商店租赁一张或多张光盘,同时,商店希望客户在每张光盘对应的
租赁期内归还之前租赁的光盘。
顾客可以在任意时间对任何租赁的光盘付费。
Sakila 数据库的表
序号
表名(英文)
表名(中文)
1
2
3
4
5
6
7
8
9
10
11
12
actor
address
category
city
country
customer
film
film_actor
film_category
film_text
inventory
language
演员表
地址表
类别表
城市表
国家表
客户表
电影表
电影_演员表
电影_类别表
电影_文本表
库存表
语言表
13
14
15
16
payment
rental
staff
store
总体设计规范
付款表
租赁表
工作人员表
商店表
每张表都有自增主键列,列名采用“表名_id”的规则命名,如表 film 的自增列为 film_id。
外键约束引用主键,且名字与主键列的相同。例如,表 store 的 address_id 列引用 addess
的 address_id 列。
每张表有一个列叫做 last_update,这是一个 TIMESTAMP 类型的字段,用来记录增加或
更新数据时的时间。
sakila 模型关系图
02 安装 sakila 数据库
软件:
MySQL 5.5
Navicat for MySQL
在 sakila-db 的文件夹下有两个 SQL 脚本文件。
sakila-schema.sql 创建数据库和表
sakila-data.sql 插入数据
03 维度建模
维度建模基本概念
维度建模(dimensional modeling)是专门用于分析型数据库、数据仓库、数据集市建模的
方法。数据集市可以理解为是一种"小型数据仓库"。
维度表 (dimension)
维度表示你要对数据进行分析时所用的一个量,比如你要分析产品销售情况,你可以选择
按类别来进行分析,或按区域来分析。这样的按..分析就构成一个维度。再比如"昨天下午我
在星巴克花费 200 元喝了一杯卡布奇诺"。那么以消费为主题进行分析,可从这段信息中提
取三个维度:时间维度(昨天下午),地点维度(星巴克), 商品维度(卡布奇诺)。通常来说维度
表信息比较固定,且数据量小。
事实表 (fact table)
表示对分析主题的度量。事实表包含了与各维度表相关联的外键,并通过 JOIN 方式与
维度表关联。事实表的度量通常是数值类型,且记录数会不断增加,表规模迅速增长。比如
上面的消费例子,它的消费事实表结构示例如下:
消费事实表:Prod_id(引用商品维度表), TimeKey(引用时间维度表),Place_id(引用地点维
度表), Unit(销售量)。
总的说来,在数据仓库中不需要严格遵守规范化设计原则。因为数据仓库的主导功能就是面
向分析,以查询为主,不涉及数据更新操作。事实表的设计是以能够正确记录历史信息为准
则,维度表的设计是以能够以合适的角度来聚合主题内容为准则。
维度建模三种模式
星型模式
星形模式(Star Schema)是最常用的维度建模方式。星型模式是以事实表为中心,所有的
维度表直接连接在事实表上,像星星一样。
星形模式的维度建模由一个事实表和一组维表成,且具有以下特点:
a. 维表只和事实表关联,维表之间没有关联;
b. 每个维表主键为单列,且该主键放置在事实表中,作为两边连接的外键;
c. 以事实表为核心,维表围绕核心呈星形分布;
雪花模式
雪花模式(Snowflake Schema)是对星形模式的扩展。雪花模式的维度表可以拥有其他维
度表的,虽然这种模型相比星型更规范一些,但是由于这种模型不太容易理解,维护成本比
较高,而且性能方面需要关联多层维表,性能也比星型模型要低。所以一般不是很常用。
星座模式
星座模式是星型模式延伸而来,星型模式是基于一张事实表的,而星座模式是基于多张
事实表的,而且共享维度信息。
前面介绍的两种维度建模方法都是多维表对应单事实表,但在很多时候维度空间内的事实表
不止一个,而一个维表也可能被多个事实表用到。在业务发展后期,绝大部分维度建模都采
用的是星座模式。
模型命名规范
维度表的命名全部遵循了 dim_的规则,其中用来描
述维度的内容。
维度一般都是以人员(who)、时间(when)、地点(where)、事件(what)来划分。
事实表的命名全部遵循了 fact_的规则,其中< fact -name>用来描述事实的内
容。
事实表一般都是以多少(how match?)来划分。
04 处理维度表
管理各种键
业务键
源系统中业务主键,用来标识唯一的一个业务实体。
维度表代理键
用来标识维表表里面的一行。
数据仓库最佳实践表明,原则上,维度表应该使用自动生成的无意义的整型数值作为代理键。
代理键的值和维度表里面的属性没有关系,代理键一般是在 ETL 过程中生成。
加载维度表
分类
维度表分为静态维度表和动态维度表。
静态维度是不会变化的维度数据,如时间、日期等。
动态维度是业务系统产生的,一段时间会产生变化。
缓慢变化维(SCD)类型
类型一:对源系统的更新,也会直接更新目标的维度表。维度表总是保存当前最新的状态,
如果发生变化就直接覆盖。通过 Kettle 的”插入/更新”步骤来实现。
类型二:对源系统的更新,会往目标维度表里插入一行数据,通过不同的时间戳来维护同一
条维度数据的多个版本。在任何一个给定的时间点,都可以找到一行对应的维度数据。可以
按照时间追踪到维度的变化。通过 Kettle 的”维度查询 /更新”步骤来实现。
类型三:对源系统的更新,会在目标维度里增加列,在目标维度表同一行新增的列里保存新
的数据。Kettle 没有一个专用的步骤来支持,但是可以写一个作业并使用”表里面的列是否存
在”步骤来判断是否要更改表结构,然后使用 SQL 脚本步骤,执行相应的 DDL 语句增加一
个新列。
sakila 数据仓库模型
维度表
人员(who):这组包含 dim_customer 表和 dim_staff 表,分别代表租赁业务中的客户和员工。
这 是 属 于 类 型 2 的 缓 慢 变 化 维 度 : 维 度 表 里 使 用 %_version_number 、 %_valid_from
和%_valid_through 列来跟踪同一客户或员工的历史记录。
时间(when): 包含 dim_date 表和 dim_time 表,dim_date 表代表租赁日期和归还日期,
dim_time 维度表用来记录当天租赁的时间。
地点(where):维度表 dim_store 用来记录 DVD 光盘是从哪个商店租赁的。
事件(what):这组包括 dim_actor 和 dim_film 两个维度表,它们是租赁业务的主题。
只有 dim_film 表和 fact_rental 表直接关联,因为电影才是租赁和归还的实际对象。但是,一
部电影由众多演员构成,这些演员在某种意义上也是组排的对象。这就是所谓的桥接表
dim_film_actor_bridge 的由来,该表联系了演员和电影。另外,该表保存了一个权重因子,
用来评估一个演员对影片的贡献值。
事实表
多少(how match?)事实表 fact_rental 用来记录详细的租赁数据。
创建
在 sakila-dwh 目录在存在两个 SQL 脚本
sakila_dwh_schema.sql 创建库和表