数仓分层模型|简练实用（推荐收藏）

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

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）星型模型：事实表+维度表（区域、类目、性别&＃8230;)等多表通过预先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数据来源主要几种：（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

以下是本人的经历：
《一个大数据小白到架构的经历》