数据仓库模型与缓慢变化维度技术深度剖析-DW商业环境实战

原文链接:https://juejin.im/post/5bdf0b84e51d450eca7c6c8b

本套系列博客从真实商业环境抽取案例进行总结和分享,并给出大数据商业实战指导,请持续关注本套博客。版权声明:本套大数据商业实战系列归作者(秦凯新)所有,禁止转载,欢迎学习。

1 数据仓库模型概述

2 数据仓库分层模型

  • STAGE 层

STAGE 层作为数据缓冲层,主要负责采集不同类型的业务系统数据并保存一定期限内的相关业务数据,完成不同类型数据源的统一临时存储,同时避免 ETL 操作对业务系统性能造成影响,STAGE 层数据在数据结构、数据之间的逻辑关系上都与业务系统基本保持一致。

  • ODS 数据层

ODS(Operational Data Store)层数据来源于 STAGE 层,它的数据经过了对 STAGE 层数据的清洗,包括编码表去重、去空、垃圾数据过滤、数据类型规则化等。ODS存储了运营系统(如OLTP(联机事务处理)系统)近实时的详细数据。

另外 ODS 作为 DW 和 STAGE 层的桥梁,也可以实现指标一致性的管理,将不同系统不同部门相同指标的定义及指标数据按照业务规则取其一,保证不同源数据的数据一致性,也可以满足用户对明细数据的查询要求,直接从ODS层获取明细数据进行分析。

  • DWD 数据层

DWD(Data Warehouse Detail)层数据是将 ODS 层数据根据数据清洗规则,经过质量检查、数据清洗、转换、标准化后,形成符合质量要求的公共数据中心,有的也称为 ODS层,是业务层与数据仓库的隔离层

把 ODS 数据表结构改变成项目主题数据仓库的表结构,对 DWD 层的所有表添加了代理键,标准化了业务系统编码类型不统一的问题,建立了数据仓库维度表和事实表的关联体系,也为缓慢变化维的实现奠定了基础。

  • DWC 数据层

DWC(Data Warehouse Center)层主要管理固化报表的数据存储,数据主要来源于 DWD 层,根据前台所需数据建立物理模型,使用 ETL 抽取 DWD 层数据推送给 DWC 层,这样显著减少前台应用直接关联 DWD 层查询明细数据的成本,提高平台数据获取的速度。

  • DWB数据层:

DWB数据层(data warehouse base)表示 基础数据层,存储的是客观数据,一般用作中间层,可以认为是大量指标的数据层。

  • DWS数据层:

DWS数据层 (data warehouse service)表示 服务数据层,基于DWB上的基础数据,整合汇总成分析某一个主题域的服务数据,一般是宽表。

  • DM 数据层

DM(Data Mart)层即数据集市,将指标与维度建立物理模型组成数据集市,这是 OLAP 的数据基础。该层实现了合并不同系统的数据源来满足面向主题的业务需求,它的建模是终端用户驱动的,也是由业务需求驱动的。按主题,维度及 KPI 指标对 DM 层进行模型设计、建模,DM 层数据是将 DWD 层数据进行进一步整合、转换、汇总、计算等 ETL 操作处理获取的。

DW数据分层,由下到上关系依次为 STAGE->ODS->DWD->DWB->DWS

3 企业级数据仓库总体设计

4 OLTP和OLAP的区别

4.1 联机事务处理OLTP(on-line transaction processing)

主要是执行基本日常的事务处理,比如数据库记录的增删查改。比如在银行的一笔交易记录,就是一个典型的事务。

4.2 OLTP的特点有:

    1. 实时性要求高。比如银行异地汇款,秒级到账的节奏。
    1. 数据量不是很大,生产库上的数据量一般不会太大,而且会及时做相应的数据处理与转移。
    1. 交易一般是确定的,比如银行存取款的金额肯定是确定的,所以OLTP是对确定性的数据进行存取
    1. 高并发,并且要求满足ACID原则。比如两人同时操作一个银行卡账户,比如大型的购物网站秒杀活动时上万的QPS请求。

4.3 联机分析处理OLAP

联机分析处理OLAP(On-Line Analytical Processing) 是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。

4.4 OLAP的特点有:

    1. 实时性要求不是很高,比如最常见的应用就是天级更新数据,然后出对应的数据报表。
    1. 数据量大,因为OLAP支持的是动态查询,所以用户也许要通过将很多数据的统计后才能得到想要知道的信息,例如时间序列分析等等,所以处理的数据量很大;
    1. OLAP系统的重点是通过数据提供决策支持,所以查询一般都是动态,自定义的。所以在OLAP中,维度的概念特别重要。一般会将用户所有关心的维度数据,存入对应数据平台。

4.5 总结:

OLAP是数据仓库的核心部心,所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库,用于处理商业智能、决策支持等重要的决策信息;数据仓库是在数据库应用到一定程序之后而对历史数据的加工与分析,读取较多,更新较少。

5 缓慢变化维度技术案例精讲

5.1原始数据准备

  • 新增了第6条数据
  • 删除了第2条数据
  • 修改了第1条数据的name列、cty列和st列(name列按SCD2处理,cty列和st列按SCD1处理)
  • 修改了第4条数据的cty列和st列(按SCD1处理)
  • 修改了第5条数据的name列(按SCD2处理)

5.2 建立过渡表

	DROP TABLE IF EXISTS tbl_stg;
	CREATE TABLE tbl_stg (
		id INT,
		name STRING,
		cty STRING,
		st STRING
	)
	ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
复制代码

5.3 建立维度表渡表

	DROP TABLE IF EXISTS tbl_dim;
	CREATE TABLE tbl_dim (
		sk INT,
		id INT,
		name STRING,
		cty STRING,
		st STRING,
		version INT,
		effective_date DATE,
		expiry_date DATE)
	CLUSTERED BY (id) INTO 8 BUCKETS
	STORED AS ORC TBLPROPERTIES ('transactional'='true');
复制代码

5.4 向维表中添加数据

    	INSERT INTO tbl_dim
    	SELECT
    		ROW_NUMBER() OVER (ORDER BY tbl_stg.id) + t2.sk_max,
    		tbl_stg.*,
    		1,
    		CAST('1900-01-01' AS DATE),
    		CAST('2200-01-01' AS DATE)
    	from tbl_stg CROSS JOIN (SELECT COALESCE(MAX(sk),0) sk_max FROM tbl_dim) t2;
复制代码

5.5 更新过渡表中的数据

   drop table tbl_stg;
   LOAD DATA LOCAL INPATH '/root/opendir/test-data/aa.txt' INTO TABLE tbl_stg;
复制代码

5.6 设置日期变量

   SET hivevar:pre_date = DATE_ADD(CURRENT_DATE(),-1);
   SET hivevar:max_date = CAST('2200-01-01' AS DATE);
复制代码

5.7 name列的缓慢变化维scd2-->向维度表装载更新后的数据设置已删除记录和SCD2的过期(测试时间为20180904)

1,张三,US,CA -->scd2 scd1
2,李四,US,CB -->删除
3,王五,CA,BB -->没变
4,赵六,CA,BC -->scd1
5,老刘,AA,AA -->scd2

1,张,U,C
3,王五,CA,BB
4,赵六,AC,CB
5,刘,AA,AA
6,老杨,DD,DD

UPDATE tbl_dim
SET expiry_date = ${hivevar:pre_date}
WHERE sk IN
(SELECT a.sk FROM (
SELECT sk,id,name FROM tbl_dim WHERE expiry_date = ${hivevar:max_date}) a LEFT JOIN tbl_stg b ON a.id=b.id
WHERE b.id IS NULL OR a.name<>b.name);


  tbl_dim.sk	tbl_dim.id	tbl_dim.name	tbl_dim.cty	tbl_dim.st	tbl_dim.version	tbl_dim.effective_date	tbl_dim.expiry_date
	1	1	张三	US	CA	1	1900-01-01	2018-09-03  -->过期
	2	2	李四	US	CB	1	1900-01-01	2018-09-03  -->过期(记录删除)	
	3	3	王五	CA	BB	1	1900-01-01	2200-01-01
	4	4	赵六	CA	BC	1	1900-01-01	2200-01-01	
	5	5	老刘	AA	AA	1	1900-01-01	2018-09-03  -->过期
复制代码

5.8 处理SCD2新增行(name列的缓慢变化维scd2)

INSERT INTO tbl_dim
	SELECT
		ROW_NUMBER() OVER (ORDER BY t1.id) + t2.sk_max,
		t1.id,
		t1.name,
		t1.cty,
		t1.st,
		t1.version,
		t1.effective_date,
		t1.expiry_date
	FROM
	(
		SELECT
			t2.id id,
			t2.name name,
			t2.cty cty,
			t2.st st,
			t1.version + 1 version,
			${hivevar:pre_date} effective_date,
			${hivevar:max_date} expiry_date
		FROM tbl_dim t1 INNER JOIN tbl_stg t2
		ON t1.id=t2.id AND t1.name<>t2.name AND t1.expiry_date = ${hivevar:pre_date}
		LEFT JOIN tbl_dim t3 ON t1.id = t3.id AND t3.expiry_date = ${hivevar:max_date}
		WHERE t3.sk IS NULL) t1
	CROSS JOIN
	   (SELECT COALESCE(MAX(sk),0) sk_max FROM tbl_dim) t2;
	   
	tbl_dim.sk	tbl_dim.id	tbl_dim.name	tbl_dim.cty	tbl_dim.st	tbl_dim.version	tbl_dim.effective_date	tbl_dim.expiry_date
	1	1	张三	US	CA	1	1900-01-01	2018-09-03 
	6	1	张	    U	C	2	2018-09-03	2200-01-01 --->新增行
	2	2	李四	US	CB	1	1900-01-01	2018-09-03 
	3	3	王五	CA	BB	1	1900-01-01	2200-01-01
	4	4	赵六	CA	BC	1	1900-01-01	2200-01-01
	5	5	老刘	AA	AA	1	1900-01-01	2018-09-03 
	7	5	刘	    AA	AA	2	2018-09-03	2200-01-01 --->新增行
复制代码

5.9 处理SCD1-->因为hive的update还不支持子查询,所以这里使用了一个临时表存储需要更新的记录。

用先delete再insert代替update,因为SCD1本身就不保存历史数据,所以这里更新维度表里的所有cty或st改变的记录,而不是仅仅更新当前版本的记录

DROP TABLE IF EXISTS tmp;
CREATE TABLE tmp AS
SELECT a.sk,a.id,a.name,b.cty,b.st,a.version,a.effective_date,a.expiry_date FROM tbl_dim a, tbl_stg b
WHERE a.id=b.id AND (a.cty <> b.cty OR a.st <> b.st);
DELETE FROM tbl_dim WHERE sk IN (SELECT sk FROM tmp);
INSERT INTO tbl_dim SELECT * FROM tmp;

原始数据
1,张三,US,CA -->scd2 scd1
2,李四,US,CB -->删除
3,王五,CA,BB -->没变
4,赵六,CA,BC -->scd1
5,老刘,AA,AA -->scd2

过度业务数据
1,张,U,C
3,王五,CA,BB
4,赵六,AC,CB
5,刘,AA,AA
6,老杨,DD,DD
复制代码

修改了第4条数据的cty列和st列(按SCD1处理)

6	1	张	    U	C	2	2018-09-03	2200-01-01  -->scd2 scd1
1	1	张三	U	C	1	1900-01-01	2018-09-03  -->scd2 scd1
2	2	李四	US	CB	1	1900-01-01	2018-09-03  -->删除
3	3	王五	CA	BB	1	1900-01-01	2200-01-01  -->没变
4	4	赵六	AC	CB	1	1900-01-01	2200-01-01  -->scd1
5	5	老刘	AA	AA	1	1900-01-01	2018-09-03  -->scd2
7	5	刘	    AA	AA	2	2018-09-03	2200-01-01  -->scd2
复制代码

5.10 除SCD1和SCD2外,处理新增记录

INSERT INTO tbl_dim
	SELECT
		ROW_NUMBER() OVER (ORDER BY t1.id) + t2.sk_max,
		t1.id,
		t1.name,
		t1.cty,
		t1.st,
		1,
		${hivevar:pre_date},
		${hivevar:max_date}
	FROM
	(
		SELECT t1.* FROM tbl_stg t1 LEFT JOIN tbl_dim t2 ON t1.id = t2.id
		WHERE t2.sk IS NULL) t1
	CROSS JOIN
	(SELECT COALESCE(MAX(sk),0) sk_max FROM tbl_dim) t2;
	
	
1,张三,US,CA -->scd2 scd1
2,李四,US,CB -->删除
3,王五,CA,BB -->没变
4,赵六,CA,BC -->scd1
5,老刘,AA,AA -->scd2

1,张,U,C
3,王五,CA,BB
4,赵六,AC,CB
5,刘,AA,AA
6,老杨,DD,DD

6	1	张	    U	C	2	2018-09-03	2200-01-01  -->scd2 scd1
1	1	张三	U	C	1	1900-01-01	2018-09-03  -->scd2 scd1
2	2	李四	US	CB	1	1900-01-01	2018-09-03  -->删除
3	3	王五	CA	BB	1	1900-01-01	2200-01-01  -->没变
4	4	赵六	AC	CB	1	1900-01-01	2200-01-01  -->scd1
5	5	老刘	AA	AA	1	1900-01-01	2018-09-03  -->scd2
7	5	刘	    AA	AA	2	2018-09-03	2200-01-01  -->scd2
8	6	老杨	DD	DD	1	2018-09-03	2200-01-01  -->新增
复制代码

6 用户内容推荐之行变列以及列变行数据仓库技术

        drop table if exists articles;
        create external table articles(
         article_id STRING,
         url STRING,
         kws array< STRING >
        )
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY ','
        COLLECTION ITEMS TERMINATED BY '|'
        LOCATION '/hivepeixun/articles';
复制代码

drop table if exists user_actions;

复合数组元素,拆分split   
create external table user_actions(
 user_id STRING,
 article_id STRING,
 ts STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/hivepeixun/users';

collect_set去重功能:
select user_id, collect_set(article_id) from user_actions group by user_id;
 
collect_list没有去重功能:用户分组后,进行文章汇总成集合:
select user_id, collect_list(article_id) from user_actions group by user_id;
:
select user_id,sort_array(collect_list(article_id)) from user_actions group by user_id;
 
拆开某条字段,一条数据变成多条记录,没有关联时为空的就过滤掉了:

select article_id,kw from articles2 lateral view explode(kws) t as kw;

select a.* , b.kw from user_actions as a left outer join (select article_id, kw from articles lateral view explode(kws) t as kw) b on (a.article_id =b.article_id)
复制代码

select a.user_id , b.kw , count(1) as weight from user_actions as a 
left outer join (select article_id, kw from articles lateral view explode(kws) t as kw)
b on (a.article_id =b.article_id) group by a.user_id ,b.kw order by a.user_id ,weight desc;
复制代码

6 用户内容推荐需求挖掘

未完待续

7 最后

本节内容主要探讨了数据仓库模型与缓慢变化维度技术深度剖析,本文参考了经典案例,并进行重新编排,内容有所变化,可能部分截图来自github公开源码,部分是我的测试案例,如有雷同某位大神私有内容,请直接留言于我,我来重新修正案例。

秦凯新 于深圳

展开阅读全文

原创--数据仓库缓慢变化维之完全解决方案

05-24

论坛格式化不太方便,写成文档上传了,欢迎下载rnhttp://download.csdn.net/source/464602rn[url=http://www.mydwbi.com/posts/list/144.page]http://www.mydwbi.com/posts/list/144.page[/url]rn本人原创文档,首发www.mydwbi.com欢迎转贴, 转贴请注明出处。rn[color=#FF0000][b]做数据库的兄弟们,数据仓库才是王道啊![/b][/color]rnrnrnrn不愿下载的我贴在后面:rnrn目录 rnrn缓慢变化维解决方案 1 rn参考文档:Wikipedia 2 rn一.新数据覆盖旧数据 3 rn二.保存多条记录,并添加字段加以区分 3 rn三.不同字段保存不同值 4 rn四.另外建表保存历史记录 4 rn五.混合模式 5 rn六.非常规混合模式 6 rnrnrnrnrn参考文档:Wikipedia rnrn缓慢变化维定义 rnrnWikipedia中的定义: rnDimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information. rnrnSlowly Changing Dimensions (SCD) are dimensions that have data that slowly changes. rnrn大意是说数据会发生缓慢变化的维度就叫”缓慢变化维”。 rn举个例子就清楚了: rn在一个零售业数据仓库中,事实表保存着各销售人员的销售记录,某天一个销售人员从北京分公司调到上海分公司了,那么如何来保存这个变化呢?也就是说销售人员维度要怎么恰当的处理这一变化。先来回答一个问题,为什么要处理,或保存这一变化?如果我们要统计北京地区或上海地区的总销售情况的时候,这个销售人员的销售记录应该算在北京还是算在上海?当然是调离前的算在北京,调离后的算在上海,但是如标记这个销售人员所属区域?这里就需要处理一下这个维度的数据,即我们缓慢变化维需要做的事情。 rnrn处理缓慢变化维一般按不同情况有以下几种解决方案: rnrn一. 新数据覆盖旧数据 rn此方法必须有前提条件,即你不关心这个数剧的变化。例如,某个销售人员的英文名改了,如果你不关心员工的英文名有什么变化则可直接覆盖(修改)数据仓库中的数据。 rnrn二. 保存多条记录,并添加字段加以区分 rn这种情况下直接新添一条记录,同时保留原有记录,并用单独的专用的字段保存区别。如: rn(以下表格中Supplier_State表示上面例子中所属区域,为描述清晰,不用代理键表示) rnrnSupplier_key Supplier_Code Supplier_Name Supplier_State Disable rn001 ABC Phlogistical Supply Company CA Y rn002 ABC Phlogistical Supply Company IL N rn或: rnrnSupplier_key Supplier_Code Supplier_Name Supplier_State Version rn001 ABC Phlogistical Supply Company CA 0 rn002 ABC Phlogistical Supply Company IL 1 rnrn以上两种是添加数据版本信息或是否可用来标识新旧数据。 rn下面一种则是添加记录的生效日期和失效日期来标识新旧数据: rnrnSupplier_key Supplier_Code Supplier_Name Supplier_State Start_Date End_Date rn001 ABC Phlogistical Supply Company CA 01-Jan-2000 21-Dec-2004 rn002 ABC Phlogistical Supply Company IL 22-Dec-2004 rnrn空的End_Date表示当前版本数据,或者你也可一用一个默认的大时间 (如: 12/31/9999)来代替空值, 这样数据还能被索引识别到. rnrn三. 不同字段保存不同值 rnrnSupplier_key Supplier_Name Original_Supplier_State Effective_Date Current_Supplier_State rn001 Phlogistical Supply Company CA 22-Dec-2004 IL rnrn这种方法用不同的字段保存变化痕迹.但是这种方法不能象第二种方法一样保存所有变化记录,它只能保存两次变化记录.适用于变化不超过两次的维度。 rnrn四. 另外建表保存历史记录 rnrn即另外建一个历史表来表存变化的历史记录,而维度只保存当前数据。 rnrnSupplier: rnSupplier_key Supplier_Name Supplier_State rn001 Phlogistical Supply Company IL rnrnSupplier_History: rnSupplier_key Supplier_Name Supplier_State Create_Date rn001 Phlogistical Supply Company CA 22-Dec-2004 rnrn这种方法仅仅记录一下变化历史痕迹,其实做起统计运算来还是不方便的。 rnrn五. 混合模式 rn这种模式是以上几种模式的混合体,相对而言此种方法更全面,更能应对错综复杂且易变化的用户需求,也是较为常用的。 rnrnRow_Key Supplier_key Supplier_Code Supplier_Name Supplier_State Start_Date End_Date Current Indicator rn1 001 ABC001 Phlogistical Supply Company CA 22-Dec-2004 15-Jan-2007 N rn2 001 ABC001 Phlogistical Supply Company IL 15-Jan-2007 1-Jan-2099 Y rnrnrn此中方法有以下几条优点: rn1. 能用简单的过滤条件选出维度当前的值。 rn2. 能较容易的关联出历史任意一时刻事实数据的值。 rn3. 如果事实表中有一些时间字段(如:Order Date, Shipping Date, Confirmation Date),那么我们很容易选择哪一条维度数据进行关联分析。 rnrn其中Row_Key和 Current Indicator字段是可有可无的,加上去更方便,毕竟维度表的数据都不大,多点冗余字段不占太大空间但能提高查询效率。 rn这种设计模式下事实表应以Supplier_key为外键,虽然这个字段不能唯一标识一条维度数据,从而形成了事实表与维表多对多的关系,因此在做事实和维度做关联时应加上时间戳字段(或Indicator字段)。 rnrn六. 非常规混合模式 rn上面说到第五种实现方式有点弊端,那就是事实表和维表不是多对一关系,而是多对多关系,这种关系不能在建模时解决只能在报表层面,在报表运行时解决,且在BI语意层建模时需要添加时间过滤条件,比较繁琐。 rn下面这种解决方案可以解决此多对多关系,但是得修改一下事实表: rnrnSupplier Dimension: rnVersion_Number Supplier_key Supplier_Code Supplier_Name Supplier_State Start_Date End_Date rn1 001 ABC001 Phlogistical Supply Company CA 22-Dec-2004 15-Jan-2007 rn0 001 ABC001 Phlogistical Supply Company IL 15-Jan-2007 1-Jan-2099 rnrnFact Delivery: (为描述清晰,同样不使用代理键标识维度) rnDelivery_Key Supplier_key Supplier_version_number Quantity Product Delivery_Date Order_Date rn1 001 0 132 Bags 22-Dec-2006 15-Oct-2006 rn2 001 0 324 Chairs 15-Jan-2007 1-Jan-2007 rnrn此方案中向维表中的当前数据版本号始终为0,即插入维度数据时先将老版本的数据的version_number改成1(递增),然后再插入当前数据,此时才能保持当前数据版本号始终为0。 rn事实表中插入数据时所有的维度数据版本号始终全部为0。 rn因此此方案完全可解决事实表和维表多对多关系问题,另外还有个优点是能保证事实表和维表的参照完整性,而且我们在用ERwin,PowerDesigner等建模工具建模时,Version_Number和Supplier_key可作为复合主键在两实体间建立链接。 rnrnrnrn 论坛

没有更多推荐了,返回首页