数仓分层模型|简练实用(推荐收藏)

通过阅读本文,可以让你快速了解数仓如何分层,合理,实用。
笔者坚持原创,根据实践总结,希望对新手有所帮助。

分层案例

1.电信通讯

stage层 ->bdl层 ->analysis层

2.传统金融/保险

ods层 ->pdm层 ->dm层

3.互联网金融/电商

odl层 ->bdl层 ->idl层 ->adl层

尽管行业不同,套路却差不多。本次借鉴互联网分层模型,使用HIVE作为数据仓库,搭建数据平台。

专业术语

ODL层 (Operational Data Layer):操作数据层

外部数据什么样,该层数据就是什么样(关系型数据库、JSON格式等)
部分关系型数据可以直接转IDL层

BDL层 (Base Data Layer):基础数据层

ODL层经过简单格式化解析后存储到BDL层,常见于JSON日志格式的解析。

IDL层 (Interface Data Layer):接口层,也称主题表,宽表

由BDL层经过去重、去噪、字典翻译、空值转化,日期格式化、关联JOIN、维度分析等清洗后的数据
如:用户、产品、绑卡、订单、用户行为等明细数据。

ADL层(Application Data Layer):应用层 ,也称数据集市

通常与需求对接,由IDL层基于某些维度的深度加工统计汇总等操作转化而来,涉及到多个主题以及tmp数据之间的关联JOIN后的结果。

DIC层(Dictionary Data Layer):字典层

存储一些诸如省、市、县区域表、渠道列表、商品类目等等表数据,可以从数据源直接sqoop生成dic_xxx表,也可以通过odl层转化层dic_表。

TMP层(Temporary Data Layer):临时层

存储一些中间计算结果

分层模型

简要说明

  1. 层次间的转换没必要循规蹈矩,按部就班,适当做到灵活,避免重复清洗浪费资源
  2. ODL层干净的关系型数据可以直接转换为IDL层数据,减少计算量
  3. ODL层侧重与外部对接,BDL层/TMP层/IDL层侧重清洗,IDL层和ADL层侧重对外提供应用服务
  4. 层数太少不够灵活,太多则在数据推翻重洗耗时,时间成本(一个坑)
  5. 数据源提供的数据越详细越好,避免后期大量重复的清洗工作。

此外,大家可能经常听到“星型模型”和“雪花模型”,简单解释下

(1)星型模型:事实表+维度表(区域、类目、性别...)等多表通过预先JOIN冗余到一张宽表里去,常见IDL层。

(2)雪花模型:在计算的时候,才将事实表跟维度表做join。

现在一般都是采用(1)的模式,为什么呢? 预先计算,挺高性能,避免后续重复计算。CPU和内存的资源永远比磁盘空间宝贵的多。至于(2)的方式,有点就是灵活,不需要太多的重复清洗,但是性能不如(1).

建设思路

从需求出发,逆推应用层ADL结构,进而推导出它涉及的主题表IDL表结构,再推导可能涉及的基础表BDL表结构,最后分析所需的数据源取自何处。
需求包含“明确”需求和“潜在”需求。

开发步骤

  1. 创建ODL、BDL、IDL、ADL层表结构(HQL)
  2. 确定数据抽取方案(增量或全量)
  3. 编写sqoop脚本将data同步到ODL层
  4. 编写ODL->BDL->IDL->ADL层ETL清洗脚本(HQL),注意:清洗的顺序,时间
  5. 确保上一层的数据稳定,减少对下一层的影响
  6. 编写Hue workflow Ooize脚本
  7. 打通Kylin、FineBI、Hive关系,实现数据可视化、可导出目标
  8. 将稳定后所有脚本WIKI上保存一份

HIVE开发规范

Hive数据来源主要几种:(1)关系型数据仓库导入 (2)HDFS存储的Log数据
(3)Flume sink过来的

数据的生产者:Ngnix log日志、业务系统埋点、监控日志、kafka 等

业务系统埋点的JSON格式 参考 《埋点-JSON格式通用》

表命名规范

ODL层:表名前缀 odl_
BDL层:表名前缀 bdl_
IDL层:表名前缀 idl_
ADL层:表名前缀 adl_

特别的

TMP表:表名前缀 tmp_ ,用于存储中间计算、临时的数据,配合前面4层计算
DIC表:表名前缀 dic_ ,用于存储变化不大的字典信息,如省份城市、区域、类目等数据。

外部表和内部表

外部表
当需要通过Hive的HQL语句读取HDFS数据时,需要建立外部表 create external “表名”,并指定数据在hdfs上的路径 location ,完成这样一个映射关系。
内部表
操HQL语句如同关系型数据一样,创建表时不需要 external关键字

每个表增加个dt时间分区是个好习惯

dt 可以表示哪一天清洗的,也可以表示取自哪一天的全量数据或增量数据。
dt 可以让数据可追溯,哪天数据有误可以根据dt抽取出来分析,可以重新计算。

insert into 和 insert overwrite

insert into 增量插入
insert overwrite 全量覆盖,已有的数据会消失,特别适合全量累计更新的需求。
建议用如下语句,

 insert overwrite TABLE idl_event PARTITION (dt='${dt}') 
select  * from  odl_event   o where  o.dt='${dt}' 

idl_event表dt的含义是根据odl_event表达dt决定的,若dt只是一天的增量数据 ,那么idl_event的dt也仅仅是一天的增量。若odl_event的dt表示全量数据,那么idl_event的dt,每一个dt都是表示全量数据。

示例场景:当发现T日的增量数据清洗有误,需重清洗,则可以使用上面的语句,避免T日以前的数据也被覆盖掉。

boolean类型

关系型数据库中场景的boolean类型值位1或0 以及一些非boolean类型有时也用0,1,2..等数值表示,不够直观,容易误解
建议boolean类型在数仓中用字符串 “Y” 或 “N” 表示 ,未知的用“U”表示(unknow)

金额单位

建议以元为单位(最终展示),避免从“元——>分” ,“分—->元” 频繁转化。
建议使用decimal ,而非double ,避免一些计算导致精度不准确

日期格式

建议是 YYYY-MM-DD HH:MM:SS 、YYYY-MM-DD或 YYYYMMDD格式
有些LOG日志可能是Long类型的时间戳,按日期排序去重挺好的,但在这个在IDL层以上要转化为上条建议的格式。
一些商业智能分析工具,如tableau、帆软等对YYYY-MM-DD格式的支持更好,可根据日期计算换算成-周、月报等。

字段“type”和status

一张表可能有多个status或type字段,命名规范 xxx_status、xxx_type

表字段名称

odl->bdl->idl->adl 每一层相同含义的字段名称尽量保持一致,避免理解上带来误解。
针对一些涉及KV键值对的字段,如性别 sex=1 男 ,sex=2 女 ,需要新增一个字段 例:sex_value=男 导出报表时,查询改字段。

合适需要创建分区partition

分区一般按照时间分区,如按天 、按小时 ;当hive需要查询hdfs时,要创建跟hdfs相同的分区类型才可以访问其数据。

数据倾斜与优化

有时候跑Hive时,会发现job跑到99%时会停止在那里,说明在Map阶段,执行快的job在等待少了执行慢的job,然后在继续reduce 。
往往是因为语句使用了distinct ,group by 或者太多的join操作导致的。
评估不同维度数据的差异是否很大,若是很大可以根据业务拆分多个语句跑

某些字段计算时,归属哪一层

比如,用户的“首次绑卡时间”,“最后一次登录时间” ,“首次购买时间”,“最近一次购买时间” 这里涉及到了【用户】-【绑卡信息】-【订单信息】-【登录信息】多个实体
,就要考虑到BDL/IDL/ADL中的哪一层计算的问题.

清洗使用HIVE,查询请借助Impala

Impala查询的速度,是Hive的几十倍,一般1~5秒内可以范围。
Impala不适合清洗,因为语法跟hive还是有很大一部分差异的
Impala比较耗内存
一般商业智能分析工具如tableau、帆软获取其它的都支持Impala

以下是本人的经历:
《一个大数据小白到架构的经历》
《Hive 25亿数据性能优化总结》

推荐阅读更多精彩内容