无标签

发布日期: 2020-03-14

文章字数: 2k

1. 用户维度表-拉链表

1.1 什么是维度表?什么是事实表?

先举一个文档上的例子

一

2020年5月21日，宋宋老师在京东的海狗自营旗舰店花了250块钱买了一瓶海狗人参丸

Mr. Song costing 250$ bought one bottle of fur seal ginseng capsules at fur seal flagship store on 2020-06-15.

主谓宾已经标黑

二

维度信息：dimension tables 抽象度量坐标

时间 on 2020-06-15 状语 ==string==
用户 Mr. Song 主语 ==string==
商品 seal ginseng capsules 宾语 ==string==
商家 at fur seal flagship store 状语 ==string==

事实信息：fact tables

250块钱 costing 250 $ 状语 ==int==
一瓶 one bottle 定语 ==int==

三

订单事实表: id:1001 price:250 number:1

时间维度表: id:1001 date:2020-06-15

用户维度表: id:1001 user: Mr.Song

商品维度表: id:1001 sku_id:seal ginseng type:capsules

分析四个表:

具有相同的粒度
事实表为Int类型,维度表为String类型

四

订单事实表: id:1001 price:250 number:1

时间维度表: order_id:1001 date:2020-06-15

用户维度表: order_id:1001 user: Mr.Song

商品维度表: order_id:1001 sku_id:seal ginseng type:capsules

graph LR
A[时间维度表
order_id:1001
date:2020-06-15]
B[用户维度表
order_id:1001
user: Mr.Song]
C[商品维度表
order_id:1001
sku_id:seal ginseng 
type:capsules ]
A-->D[订单事实表
id:1001
price:250
number:1]
B-->D
C-->D

理论上,文本方式表示度量事实是可行的,然后很少采用这种方式,设计者应该尽最大可能将文本数据放入维度种,将它们有效地关联其他文本维度属性上,以减少空间开销.不要在事实表种存储冗杂的文本信息.

五

事实表的粒度可以划分为三类:事务(支付事实表),周期型快照(加购事实),累计快照(订单事实)

主要存储数值的表 (事实表) Int

定位事实表的表 (维度表) String

事实表通过外键与维度表连接 String

1.2 用户维度表存什么

1.2.1 建表

create external table dwd_dim_user_info_his(
    `id` string COMMENT '用户id',
    `name` string COMMENT '姓名', 
    `birthday` string COMMENT '生日',
    `gender` string COMMENT '性别',
    `email` string COMMENT '邮箱',
    `user_level` string COMMENT '用户等级',
    `create_time` string COMMENT '创建时间',
    `operate_time` string COMMENT '操作时间',
    `start_date`  string COMMENT '有效开始日期',
    `end_date`  string COMMENT '有效结束日期'
) COMMENT '用户拉链表'
stored as parquet
location '/warehouse/gmall/dwd/dwd_dim_user_info_his/'
tblproperties ("parquet.compression"="lzo");

1.2.2 分析

存储的信息从图中可以一目了然,就是用户的基本信息,大多数一些符合维度的基本信息

缓慢变化维度:用户表中的数据每日既有可能新增，也有可能修改，但修改频率并不高，属于缓慢变化维度

首先是建表,建表中要注意添加拉链表的两个核心字段 start_date/end_date

ods_user_info(增量及更新)

字段分析

图中应该是增量及更新,新增变化

用户维度表

2. SQL的分析与书写

上文中,我们对用户维度表在整个数据仓库架构中的位置做了说明

同时对字段进行了分析

接下来开始针对拉链表进行sql书写

这个表的难点在于拉链表到底应该怎么实现

2.1 dwd_dim_user_info_his拉链表的初始化

(2020-06-14为第一天)

进行初始化是因为第一天不需要进行对比修改

insert overwrite table dwd_dim_user_info_his_tmp
select id,
       name,
       birthday,
       gender,
       email,
       user_level,
       create_time,
       operate_time,
       '2020-06-14' start_time,--第一天开始的时间
       '9999-99-99' end_time--结束时间
from ods_user_info
where dt = '2020-06-14';

2.2 full join下的新表及老表分析

要想分析新表及其老表,要明白其中存有什么

老表(dwd_dim_user_info_his_tmp)(截止到2020-06-16号)

用户1 “java0” start_time : 2020-06-14 end_time : 2020-06-15 –完成
用户1 “java1” start_time : 2020-06-15 end_time : 9999-99-99 –最新
用户2 “scala0” start_time : 2020-06-16 end_time : 9999-99-99 –最新

新表(ods_user_info)(今日数据2020-06-17号)

用户1 “java2” start_time : 2020-06-17 end_time : 9999-99-99

–注意新来数据统一start_time为当日时间,end_time为default
用户2 “scala1” start_time : 2020-06-17 end_time : 9999-99-99
用户3 “date0” start_time : 2020-06-17 end_time : 9999-99-99

通过新表老表一对比,基本可以明白,要想对老表中的信息进行修改,须做以下操作

用户维度表(拉链表) 新表是可以直接插入,但是==老表==需要做一下操作

将新表中同时存在的用户对应的老表的end_time修改为新表的(start_time-1)

思路:找出老表修改用户,直接left join新表,这个时候用新表元素(id)不为空来进行判断修改

--1.拉取老表中所有end_time = '9999-99-99'的颗粒
select 
	* 
from 
	dwd_dim_user_info_his ==>old
--2.拉取新表中的数据并添加start_time=today end_time=default
select 
	*,
	'2020-06-17' start_time,
    '9999-99-99' end_time
from
	ods_user_info
where dt = '2020-06-17' ==>new
--3.这里首先要明白的就是,新表是可以直接插进去的,不用做修改,但是如果老表中存在对应的用户信息,需要将老表中的end_date修改成新表中的start_date,表示老表中的数据修改完成
nvl(start_date,end_date)--如果存在start_date就更新,不存在就不变
select
	old.id,
	old.name,
	old.birthday,
	old.gender,
	old.email,
	old.user_level,
	old.create_time,
	old.operate_time,
	old.start_time,
	if(new.start is not null and old.end_time = '9999-99-99',date_add(new.dt, -1), old.end_date) end_time
from ()old
left join
()new
on old.id = new.id ==>t3
--4 这个时候直接将t3与t2 进行union all就可以了
selet
	*
from ()t3
union all
()new
--5 插入到临时表中先
insert overwrite table dwd_dim_user_info_his_tmp

然后去data_grip整合一下

SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_dim_user_info_his_tmp
--1.拉取老表中所有end_time = '9999-99-99'的颗粒
select id,
       name,
       birthday,
       gender,
       email,
       user_level,
       create_time,
       operate_time,
       start_date,
       end_date
from dwd_dim_user_info_his_tmp di;
--2.拉取新表中的数据并添加start_time=today end_time=default
select
       id,
       name,
       birthday,
       gender,
       email,
       user_level,
       create_time,
       operate_time,
       '2020-06-15' start_date,
       '9999-99-99' end_date
from ods_user_info
where dt = '2020-06-15';
--3.这里首先要明白的就是,新表是可以直接插进去的,不用做修改,但是如果老表中存在对应的用户信息,需要将老表中的end_date修改成新表中的start_date,表示老表中的数据修改完成
--nvl(start_date,end_date)--如果存在start_date就更新,不存在就不变
select old.id,
       old.name,
       old.birthday,
       old.gender,
       old.email,
       old.user_level,
       old.create_time,
       old.operate_time,
       old.start_date,
       `if`(new.start_date is not null and old.end_date = '9999-99-99',date_add(new.start_date,-1),old.end_date) end_date
from (select id,
       name,
       birthday,
       gender,
       email,
       user_level,
       create_time,
       operate_time,
       start_date,
       end_date
from dwd_dim_user_info_his_tmp di
where start_date < '2020-06-15') old --这样可以保证重复插入不会出错,否则会发生两遍修改
    left join
    (select
            id,
            name,
            birthday,
            gender,
            email,
            user_level,
            create_time,
            operate_time,
            '2020-06-15' start_date,
            '9999-99-99' end_date
from ods_user_info
where dt = '2020-06-15') new
     on old.id = new.id;
--4 这个时候直接将t3与t2 进行union all就可以了-----------
SET hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
insert overwrite table dwd_dim_user_info_his_tmp
select *
from (
         select-->>>>>新来的表1<<<<
               id,
               name,
               birthday,
               gender,
               email,
               user_level,
               create_time,
               operate_time,
               '2020-06-15' start_date,
               '9999-99-99' end_date
         from ods_user_info
         where dt = '2020-06-15'--------------------------------
         union all
         select--------抽取修改过的旧表
               old.id,
               old.name,
               old.birthday,
               old.gender,
               old.email,
               old.user_level,
               old.create_time,
               old.operate_time,
               old.start_date,
               `if`(new.start_date is not null and old.end_date = '9999-99-99', date_add(new.start_date, -1),
                    old.end_date) end_date
               --修改旧表
         from (select---------------拿出旧表
                     id,
                     name,
                     birthday,
                     gender,
                     email,
                     user_level,
                     create_time,
                     operate_time,
                     start_date,
                     end_date
               from dwd_dim_user_info_his_tmp----------------------------
               where start_date < '2020-06-15') old --这样可以保证重复插入不会出错,否则会发生两遍修改
                  left join------------与新来的表对比
             (select--------------新表
                    id,
                    name,
                    birthday,
                    gender,
                    email,
                    user_level,
                    create_time,
                    operate_time,
                    '2020-06-15' start_date,
                    '9999-99-99' end_date
              from ods_user_info
              where dt = '2020-06-15') new---------------
                           on old.id = new.id
     ) his
order by cast(his.id as bigint);--根据id排序格式转换,锦上添花

这个时候插入到真正的老表就可以啦

insert overwrite table dwd_dim_user_info_his
select * from dwd_dim_user_info_his_tmp

至此一个可以更新的用户维度表就完成了

回顾一点核心点就是两点:

先将老表与新表left join 修改掉原来的end_date
然后将修改后的老表与新表进行union all连接起来

3. 问题:用户表如何分区?

Jinxin Li

http://fourgold.github.io/2020/03/14/11%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%9303_DWD_%E6%8B%89%E9%93%BE%E8%A1%A8_%E7%94%A8%E6%88%B7%E7%BB%B4%E5%BA%A6%E8%A1%A8_%E6%80%BB%E7%BB%93/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Jinxin Li !

无标签

数据仓库累积型快照事实表

2020-03-16 Jinxin Li

数据仓库ADS的普通需求

2020-03-13 Jinxin Li

数据仓库用户维度表

1. 用户维度表-拉链表

1.1 什么是维度表?什么是事实表?

1.2 用户维度表存什么

1.2.1 建表

1.2.2 分析

2. SQL的分析与书写

2.1 dwd_dim_user_info_his拉链表的初始化

2.2 full join下的新表及老表分析

3. 问题:用户表如何分区?

你的赏识是我前进的动力