数仓分层模型|简练实用以及Impala使用

快来打我* 2022-05-13 15:28 448阅读 0赞

# 分层案例 #

> ### 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）：临时层

> 存储一些中间计算结果

![Image 1][]

分层模型

### 简要说明 ###

> 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格式通用》][-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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODY0Njcy_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODY0Njcy_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODY0Njcy_size_16_color_FFFFFF_t_70 2][]

![2018111611250674.png][]

![70][]

![70 1][]

![70 2][]

![70 3][]

/etc/default/impala

![70 4][]

复制core-site.xml、hdfs-site.xml、hive-site.xml到conf下修改hdfs-site.xml：

![70 5][]

确定/var/run/hadoop-hdfs路径是否存在

将impala用户加到hive,hdfs,hadoop组里：

![70 6][]

![70 7][]

配置keyberos认证：

![70 8][]

![70 9][]

![70 10][]

生成principal:

![70 11][]

![70 12][]

同步过去，进行权限修改：

![70 13][]

![70 14][]

/etc/impala/conf/hdfs-site.xml：

![70 15][]

将配置文件复制到hadoop/conf目录下，然后重启hadoop服务。

![70 16][]

![70 17][]

![70 18][] full也是支持的

![70 19][]

![70 20][]

![70 21][]

![70 22][]

![70 23][]

![70 24][]

![70 25][]

![70 26][]

![70 27][]

![70 28][]

[Image 1]: 
[-JSON]: https://www.jianshu.com/p/e2fcce9273d6
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODY0Njcy_size_16_color_FFFFFF_t_70]: /images/20220504/733a56680f6248f29916833963b23e41.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODY0Njcy_size_16_color_FFFFFF_t_70 1]: /images/20220504/8aa552a45e804ef08f5cddb24b26b9d0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2ODY0Njcy_size_16_color_FFFFFF_t_70 2]: /images/20220504/34c3d4554ac44ef7b0ccf73a847b7eaf.png
[2018111611250674.png]: /images/20220504/26370e4e82d24a5fb4772962f034eb68.png
[70]: /images/20220504/91fa51d7659749a2ba922868c887be15.png
[70 1]: /images/20220504/016ea4203ae84964a404b3da6cb6e68f.png
[70 2]: /images/20220504/9ed299ad1802430aa87b206bff2584b5.png
[70 3]: /images/20220504/1a582bf77e7d4fceab29198a9ab4e634.png
[70 4]: /images/20220504/b98d6bd5b92a44daba9d9d4570ed34bd.png
[70 5]: /images/20220504/e43583b1bbc144bd871d07222e89be23.png
[70 6]: /images/20220504/0f1c08952e4445e7a5ea42ca55dd2ff6.png
[70 7]: /images/20220504/14af76fc061b4689b45b5efdd8dde36e.png
[70 8]: /images/20220504/beabd6d745dd4c24afe2012f6cbf1f9e.png
[70 9]: /images/20220504/91c021dff26a4ce097ae5768bf9a5b53.png
[70 10]: /images/20220504/acaca64d738a460fa2fc3030274bfd96.png
[70 11]: /images/20220504/ebd3d754b34d471bbf0cca9368580941.png
[70 12]: /images/20220504/f4a9cb9792ef47c3b08c9f95924d0de4.png
[70 13]: /images/20220504/c59880d6ff2f4f20b3cf36f5c6e3bc0b.png
[70 14]: /images/20220504/37223ff37487478d907f42b5498181cd.png
[70 15]: /images/20220504/4030a93a90314e71bfc8c0891a256a3e.png
[70 16]: /images/20220504/88053059bdd54eddba3defcd55ebaaa6.png
[70 17]: /images/20220504/197fa8a630474c45b6aebca52a9b3656.png
[70 18]: /images/20220504/5cdb6bce289a40d5972f4f10e1b6fecc.png
[70 19]: /images/20220504/20d8b7f05c5848e6813ed2ce8617e873.png
[70 20]: /images/20220504/71bd9a1d3bb44894803a2098f0c2b701.png
[70 21]: /images/20220504/8951af6ead654a93903442e8db2f2957.png
[70 22]: /images/20220504/c0a3539b49894ca28e80784d9a2a9676.png
[70 23]: /images/20220504/59baaf9540df47e0817e709e9d121f46.png
[70 24]: /images/20220504/9f184a757a6b4572a7c70751868dd36f.png
[70 25]: /images/20220504/a946a1dedb6142f18b9cab7b83e1c53f.png
[70 26]: /images/20220504/77ba8393f6e7487bb33ed0096d8da5ef.png
[70 27]: /images/20220504/f9b769bde7344fc8be6ef792cd119c80.png
[70 28]: /images/20220504/9fb5529d5c704a77afc3045886e5de9e.png