[hive]数仓分层|用户纬度拉链表|维度建模

胖胖学编程

已于 2023-07-07 16:33:11 修改

阅读量1.4k

点赞数 1

分类专栏：数仓文章标签： hive 大数据 hadoop Powered by 金山文档

于 2023-01-19 16:29:01 首次发布

本文链接：https://blog.csdn.net/qq_35896718/article/details/128735218

版权

数仓专栏收录该内容

5 篇文章 0 订阅

订阅专栏

https://www.modb.pro/404?redirect=%2Fdb%2F241289

一、数仓分层

1、ODS层：原始数据层

ODS(O=original D=data S=store)

1)设计要点

存储来自多个业务系统、前端埋点、爬虫获取的一系列数据源的数据。

我们要做三件事：

【1】保持数据原貌不做任何修改，保留历史数据，起到数据备份的作用。

【2】使用lzo压缩。100G的数据压缩之后大概为20G。

【3】创建分区表，防止后续的全表扫描，一般按天存储。

2）ODS层数据组成

【1】前端埋点日志：由kafka或者flume采集到HDFS上

flume采集的语句[flume]参数设置_胖胖学编程的博客-CSDN博客

【2】由前端业务数据库用sqoop采集到HDFS上

3）前端埋点日志的处理

前端埋点日志以JSON格式形式存在

建表语句

createexternaltable ods_log
(
    line string
)
partitioned by (dt string)
Stored as  
inputformat 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
;

将flume落盘的数据建立lzo索引，否则无法分片

hadoop jar /opt/module/hadoop-3.1.4/share/hadoop/common/hadoop-lzo-0.4.20.jar \
com.hadoop.compression.lzo.DistributedLzoIndexer \
-Dmapreduce.job.queuename=hive \
/user/hive/warehouse/ods.db/ods_log/dt=2022-11-11

添加分区

hive -e "use ods;alter table ods_log add if not exists partition (dt='$daystr');"

4）MySQL数据库的处理

mysql数据库的表通过sqoop采集到HDFS，使用\t作为分隔符，那ods层的表也要使用\t作为分割符；

5）同步策略

（1）每日增量同步：

1）流量表(ods_log)

也是埋点表，小时级增量表，所有的埋点数据都汇总在这一张表中。

包括前后端埋点和后端埋点

导入方式

如果是用的apache hadoop。用Nginx负载均衡，把数据均匀的发送到日志服务器中。就直接用flume拉取数据,taildir source memory channel hdfs sink。

要是用阿里的dataworks：包括调度+数据集成(从MySQL啥的导数据)+MaxCompute(类似hive，可以写sql) 注:一般用阿里的服务的话还用quickBI做报表展示。

就是后端和前端将数据写入到kafka中，然后从kafka中每小时进行数据同步。

2）卡片表：课程页面曝光与点击表，由组内其他成员使用spark开发（来自kafka）

前端埋点与后端埋点：

前后端埋点都是埋点，都是写到日志服务器中的。

前端埋点：不需要网络的，在自己手机app/浏览器/小程序里就能产生的用户行为数据，这些数据会定期上报到服务器。

后端埋点：需要和MySQL交互的数据，例如用户注册的时候，要把用户名密码之类的存道MySQL。

数据时间漂移：用户手机中存的前端的数据会累积到一定条数再发送到服务器，例如80条，假如用户1-1号存了40条就关掉了app，1-2号打开了app，这40条的数据就会变成1-2的了。

举例：用户关注了另一个用户是后端埋点，因为用户关注列表+1，用户上传了一个课程视频是后端埋点,因为要把视频上传到服务器。

既然后端的数据都写入到服务器了，那为什么还需要后端埋点呢？

比如一个用户关注了另一个用户，又取消关注了，这样的话后端的表里就没有这条记录了，但是可以从埋点里面查看到：关注了，并且取消关注了这个行为。这个行为在埋点表设计的时候，还得带用户id,关注了谁的id,这两个字段。

（2）每日全量同步：订单表、看课表、课程表、章节表、教师表、互动表、卡片表（全部来自MySQL）

注:可以设置生命周期,在一定时间用不到的，会自自动删掉。

1）课程维度表：记录课程属性信息的维度表。例如课程id 课程名称课程url 教师id等等。

2）订单表：由学员购买课程，下单产生记录。

3）看课表：由学生看课产生的记录，是存于Mysql中的两张表：直播看课记录表、录播看课记录表。

4）章节表：课程的每一章节信息，例如课程id 课程名称章节id 章节名称等。

5）教师表：存储教师信息

6）互动表：用户的评论、关注、分享行为

如果是埋点表应设计字段为

用户	行为	被关注用户	时间
张三	关注	李四	2023年7月5日
张三	取消关注	李四	2023年7月5日

如果是MySQL表应设计字段为

用户	被关注用户	时间	是否有效
张三	李四	2023年7月5日	否

注：

1、一般，能用Mysql存就不用埋点数据，因为MySQL的数据是完全和看到的保持一致的。因为app上报是批量的，比如攒够100条或者等待10s上报一次数据到kafka，这样，如果用户取消关注之后，还没等上报，用户直接就关掉了app，这会导致，本次数据没有上报到kafka中。

2、可以看到这些表都是每日全量导入的，但是我会想，这些历史数据有什么用？像订单表这种内部也会保留历史数据。是不是只需要保留最新的全量分区就行了？

例子：有一个订单表。和一个课程表，含有一个属性字段记录该课程是会员课还是非会员的。

要求：统计每日会员课程的销售额

比如下单当天3月21日，这个课程是会员课程，需要计费，到了3月25日，这个课程变成了非会员课程，就是免费的了。

那么在3月25日向计算3月21的会员课程销售额，就得用3月21日的历史数据了。

另外写调度任务的时候, 用的都是历史分区，昨天的结果, 用昨天的分区求, 前天的结果, 用前天的分区求。

（3）特殊：一次性拉取，不建分区表（DIM层的父数据）

例如地区表，单位表

2、DIM

dim=dimension。存储为Parquet格式。

1）同步策略

全量同步：课程维度表。首日和每日都是全量先导入到ODS再导入到DIM层。

拉链表：用户维度表

特殊：单位表、地区（自己处理）

2）拉链表

（1）什么是拉链表

用于存储变化，但变化的频率较慢的数据。如果用全量存储，会存储大量重复的数据，因此用拉链表。

（2）每条数据的意义

该条数据的有效时间。

里面包含了用户的基本信息，例如用户的性别，年龄，生日，星座，职业等等。

（3）制造拉链表

【1】建表语句

create table dim_user_info(
id string,
user_name string --用户名称,
name string --真实姓名,
phone_num string,
gerder string --性别,
email string,
create_time string --创建时间,
operate_time string --操作时间,
start_date string --开始日期(拉链表特有),
end_date string --结束日期(拉链表特有)
)
partitioned by(dt string)
stored as parquet
table properties("parquet.compression"="lzo")

【2】分区规划

【3】首日装载

要进行初始化，ods层该表第一天从MySQL拉取的所有数据放到9999-99-99分区

insert overwrite table dim.dim_user_info partition(dt='9999-99-99')
select
id,
user_name,
name,
phone_num,
gerder,
email,
create_time,
operate_time,
'2022-10-01' start_date,
'9999-99-99' end_date
from 
ods.ods_user_info
where dt='2022-10-01'

【4】每日装载

注:图中的2020-6-14应为本例子中的2022-10-01,2020-6-15应为本例子中的2022-10-02

a)将最新的数据装载到9999-99-99分区

如果new为null（没有变化），则取old

如果new不为null（今天发生了新增及变化），则取new

select
if(new.id is not null,new.id,old.id) id,
if(new.user_name is not null,new.user_name,old.user_name) user_name,
if(new.name is not null,new.name,old.name) name,
if(new.phone_num is not null,new.phone_num,old.phone_num) num,
if(new.gerder is not null,new.gerder,old.gerder) gerder,
if(new.email is not null,new.email,old.email) nemail,
if(new.create_time is not null,new.create_time,old.create_time) create_time ,
if(new.operate_time is not null,new.operate_time,old.operate_time) operate_time,
if(new.start_date is not null,new.start_date,old.start_date) start_date,
if(new.end_date is not null,new.end_date,old.end_date) end_date
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    start_date,
    end_date
    from 
    dim.dim_user_info
    where dt='9999-99-99'
)old
full join
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-10-02' start_date,
    '9999-99-99' end_date --新增及变化的数据都是最新数据
    from 
    ods.ods_user_info --ods_user_info表是每日增量导入的
    where dt='2022-10-02' --新增及变化的数据
)new
on old.id=new.id

b)将过期数据装载到前一天的分区（注意日期之间没有重合）

new和old都有的数据取old

select
old.id id,
old.user_name user_name,
old.name name,
old.phone_num num,
old.gerder gerder,
old.email nemail,
old.create_time create_time ,
old.operate_time operate_time,
old.start_date start_date,
"2022-10-01" end_date
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    start_date,
    end_date
    from 
    dim.dim_user_info
    where dt='9999-99-99'
)old
full join
(
    select
    id,
    user_name,
    name,
    phone_num,
    gerder,
    email,
    create_time,
    operate_time,
    '2022-10-01' start_date,
    '9999-99-99' end_date --新增及变化的数据都是最新数据
    from 
    ods.ods_user_info --ods_user_info表是每日增量导入的
    where dt='2022-10-02' --新增及变化的数据
)new
on old.id=new.id
where new.id is not null and old.id is not null
;

（4）对拉链表进行查询

【1】获取在某天有效的所有用户的数据

--获取2019-01-01有效的所有历史数据
select * from user_info where start_date<='2019-01-01' and end_date>='2019-01-01';

【2】获取目前所有用户的最近数据

select * from user_info where end_date>='9999-99-99';

3、DWD层

dwd=data warehouse detail

1）说明

dwd层是对事实表的处理，代表的是业务的最小粒度层，任何数据的记录都可以从这一层获取，为后续的dws和dwt层做准备。

dwd层是站在选择好事实表的基础上，对维度建模的视角。

2）对埋点数据进行处理

将流量表(ods_flow_ph)表唯一的字段line这个json串进行解析,解析成一个字段一个字段的。

3）对业务数据进行处理

【1】周期快照事实表

除了流量表以外的表都是从MySQL导入进来的。

103-尚硅谷-数仓搭建-DWD层优惠券领用事实表_哔哩哔哩_bilibili

一个知识点:累计快照事实表(没看完)
如：优惠券领用，这种发生周期变化的场景
createtable dwd_coupun_use(
coupun_id string, --优惠券id
user_id string, --用户id
coupun_status string, --优惠券状态
using_time string, --使用时间(下单)
used_time string, --使用时间(支付)
expire_time  string --过期时间
)
partitioned by(dt string)
;

4、dim和dwd

DIM和DWD采用维度建模，一般采用星型模型，呈现状态一般为星座模型。详情见维度建模的步骤。

5、dws、dwt、ads

dws= data warehouse service

dwt=data warehosue topic

ads=application data store

dws、dwt、ads都是以需求为驱动的，和纬度建模已经没有关系了。

dws、dwt：统称为宽表层，这两层的设计思想大致相同，通过以下案例进行说明：

1）问题引出

两个需求，统计每个省份订单的个数、统计内个省份订单的总金额

2）处理办法

都是将省份和订单表进行join,group by省份，然后计算，同样的数据被计算了两次，实际上类似的场景还有很多，那怎么设计才能避免重复计算呢？针对上述场景可以设计一张地区表，其主键为地区ID，字段为：下单次数，下单金额，支付次数，支付金额等，上述所有指标统一进行计算，并将结果保存在该宽表中，这样就能有效避免数据的重复计算。

3）需要那些宽表（主题表）：以维度为基准

4）DWS和DWT层的区别：

DWS层存放所有当天的汇总行为，例如每个地区当天下单次数，下单金额等，dwt层存放的是所有主题对象的累计行为，例如每个地区最近7天（15天、30天、60天）的下单次数，下单金额等。

5）ads层：存储各个报表需要的结果。

二、纬度建模

纬度建模步骤：选择业务过程声明粒度、确认纬度、确认事实

1、选择业务过程

整个业务流程中选取我们需要建模的业务，根据公司业务提供的需求及日后的易扩展性等进行选择业务。

这里我们选择了几个业务过程是：支付、订单、加购物车、优惠券领用、收藏、评论、退款等。

2、声明粒度

总体采用最小粒度规则，不做任何聚合操作。

声明力度意味着定义事实表中的一行数据表示什么，应尽可能选择最小粒度，以此来应对各种各样的需求。

3、确认纬度

确定维度对象，维度的主要作用是描述业务事实，主要表示的是：“谁、何处、何时”等信息。

确定维度的原则是：后续需求中是否要分析相关维度的指标。例如：需要统计哪个用户下的订单多，哪个地区下的订单多，什么地区下单多，需要确定的维度就包括：用户维度，地区维度，时间维度。

4、确认事实

事实表只有纬度id和度量值。

胖胖学编程

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
[hive]数仓分层|用户纬度拉链表|维度建模

针对上述场景可以设计一张地区表，其主键为地区ID，字段为：下单次数，下单金额，支付次数，支付金额等，上述所有指标统一进行计算，并将结果保存在该宽表中，这样就能有效避免数据的重复计算。数据时间漂移：用户手机中存的前端的数据会累积到一定条数再发送到服务器，例如80条，假如用户1-1存了40条就关掉了app，1-2打开了app，这40条的数据就会变成1-2的了。dwd层是对事实表的处理，代表的是业务的最小粒度层，任何数据的记录都可以从这一层获取，为后续的dws和dwt层做准备。用户维度表：维度表。
复制链接

扫一扫