大数据项目--知行教育（3）_知行教育离线数仓分析项目问题

数仓建模分析

数据要从业务数据库采集到数仓中（ODS）

ODS分析

表数量：2个表（业务2个表，ODS原始存储，ODS也是2个表）

表类型：外部表，分区表（采集数据的日期）

数据存储类型：

• TEXTFILE（行存储格式）
• SequenceFile（行存储格式）
• ORC（选择这个）（列存储格式）
• Parquet（列存储格式）

表压缩选择：

• Snappy
• Lzo
• GZIP
• BZIP
• LZ4
• Zlib（选择）（ODS冷数据）

Snappy、Lzo、Zlib区别

Zlib特点：压缩率贼高，性能不好

Snappy：压缩率适中，速度快

Lzo：压缩率还好，速度还好

ODS层特点是：刚开始的时候用一下，后续很少用ODS数据了。（冷数据）

既然是冷数据，用Zlib压缩成最小即可。

ODS总结

• 两个表（因为两个业务表）
• 数据格式：ORC
• 压缩：Zlib
• 分区表、外部表

DW分析

DW层主要做数据分析（迭代计算）用。

• DWD，数据清洗、转换
• DWM，数据中间层，预聚合、维度退化（宽表）
• DWS, 针对业务主体做开发

ODS -> DWD

所需字段

普通字段
session_id
sid
ip
msg_count
create_time
area
province
city
origin_channel
seo_source
from_url
hourinfo
分区字段
dayinfo
monthinfo
quarterinfo
yearinfo
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

表数据格式：ORC

压缩：Snappy

分区（多级分区 年-> 季度-> 月 -> 天）

表类型：内部表

表数量：1张表即可

ODS -> DWM层

DWM：预聚合 + 维度退化

当前这个看板：

1. 预聚合由于重复的Sid存在，所以无法聚合
2. 维度退化也不需要，因为维度都集成在DWD中事实表中了

所以，这个看板不需要DWM层级

DWD->DWS

指标：

• 访问量
• 咨询量

维度：

• 时间维度（年、季度、月、天、小时）
• 地区维度（省、市、区）
• 来源渠道
• 搜索来源
• 受访页面

两个指标，我们做两个表，因为一个指标对应它需要的维度

表需要2张，对应两个指标

表1，访问量相关的表

指标：访问量

维度：

• 时间维度（年、季度、月、天、小时）
• 地区维度（省、市、区）
• 来源渠道
• 搜索来源
• 受访页面
• 总访问量

通过分析，我们设计如下图的一张表，就可以提供所有在访问量这个指标下，关于时间、地区、来源、搜索、受访页面这些维度上的全部统计需求

-1 表示在某个维度上不存在
time_type:时间维度的类型
1.year
2.quarter
3.month
4. day
5.hour

attr_type:在地区、来源、搜索、受访页面这些维度上的类型
1. 省市区
2. origin
3. seo
4. from_url
1
2
3
4
5
6
7
8
9
10
11
12
13

表2，咨询量相关的表

指标：咨询量

维度：

• 时间维度（年、季度、月、天、小时）
• 地区维度（省、市、区）
• 总访问量

如图，得到上图的表，所有的需求关于咨询的都可以解决。

ADS层分析

ADS 有时候也被称之为APP层

作用上是存储业务或者报表等直接可用的结果数据

对于当前这个看板，DWS层的数据，聚合到那一步就足够可视化工具去使用了。

如果在进一步生成针对每个需求的结果表数据，可视化反而不好用。

所以，在当前这个看板，APP层可以省略了。

看板的建模分析总结

数据的流转是：

业务数据库（2张表） -> ODS 2张表 -> DWD 一张表 -> DWS 两个表 -> 导入MySQL -> 接入可视化工具

ODS: ORC存储、Zlib压缩、分区表（导入时间日期分区）

DWD：ORC存储、Snappy压缩（分区表：年->季度->月->天）

DWS：ORC存储，Snappy压缩（分区表：年->季度->月->天）

DWM：省略

ADS：省略

需要做的事情：

1. 采集业务数据到ODS
2. ODS数据聚合到DWD
3. DWD数据分析处理到DWS生成2张表

为什么要用压缩

压缩和解压本质上：CPU的计算（执行算法）

本质的原因是：CPU的速度远大于硬盘同时也远大于网络传输速度

核心点：将硬盘负载或者网络负载转移到CPU负载，是一种收益行为

行存储和列存储的区别

• 列存储，每个文件存储一个列，多个文件存储多个列，多个文件合成一张二维表

  • 优点：列过滤，列查找，针对列相关操作更快。扩展列，增删列很容易。列单独存储

    • 列相关查询、过滤性能更快
    • 扩展列、删除列更简单
    • 列单独存储，每个列均可以进行单独排序，性能更好
    • 列单独存储，可以针对每个列的数据类型设置针对性的压缩算法，使得压缩率更好
    • 数据加载可以选择指定的列加载到内存中。更加节省内存。

    数仓的特性很大一部分是针对列的过滤，列的搜索，列的匹配，所以很多数仓结构比较适合使用列存储

    列存储也比较适合做OLAP

  • 缺点：整行相关操作性能低。同时对事务的支持性不行（因为把列都拆开存储，每个列单独做事务，整体还要同步很麻烦）

• 行存储，数据行存储，一个文件可表达一个二维表。

  • 优点：

    • 概念简单容易理解，和很多现实中的数据模型概念相通，比如CSV文件，文本数据文件等

      概念简单容易理解是一个很大的优势，毕竟性能低点可以忍，难以理解就不可以忍了。

      人天生懒。

    • 针对行操作更快捷

    • 事务支持比较好

  • 缺点：

    • 针对列的操作性能比列存储低，因为无论操作哪个列都要取出来整行数据
    • 只能针对整行数据选定压缩算法，无法针对列选定，压缩率不高（对比列存储）
    • 排序只可基于某一个列排序，然后整体行和行之间排序（索引等额外的解决方案除外，这里只是指原始状态下）
    • 扩展列、删除列不方便