3.2 总结数据仓库，分层设计，和数据库区别，Hive对比和调优

最新推荐文章于 2024-08-09 16:39:42 发布

jialun0116

最新推荐文章于 2024-08-09 16:39:42 发布

阅读量773

点赞数

分类专栏：面经文章标签：数据仓库数据库

本文链接：https://blog.csdn.net/qq_30031221/article/details/114294859

版权

面经专栏收录该内容

21 篇文章 3 订阅

订阅专栏

3.2 总结

如何理解数仓

数据仓库就是整合多个数据源的历史数据进行细粒度的、多维度的分析，帮助高层管理者或者业务分析员做出决策。
数据仓库是一个面向主题的、集成的、随时间变化的、但信息本身相对稳定的数据集合，用于对管理决策过程的支持。

为什么要设计数据分层

需要一套行之有效的数据组织和管理方法来让我们的数据体系更有序
复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层解决特定的问题，
清晰数据结构：每一个数据分层都有它的作用域和职责，当数据出现问题之后，只需要从有问题的步骤开始修复，在使用表的时候能够更方便地定位和理解，随着业务变化，只需要相应的数据
减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少极大的重复计算
统一数据口径：通过数据分层，提供统一的数据出口，统一对外输出的数据口径

通用的数据分层设计

数据运营层 ODS、数据仓库层 DW(DWD、DWM、DWS)、数据应用层 APP、维表层
数据运营层 ODS （Operational Data Store）
- 接入的是原始数据，一般不做处理
- 参考Sqoop导入的数据类型进行建模！
数据仓库层 DW （ Data Warehouse）
- 存放我们要重点设计的数据仓库中间层数据
- **数据明细层 DWD **（Data Warehouse Detail）
  - 对ODS层数据做一定的清洗和主题汇总
  - 提高易用性，采用维度退化手法，将商品一二三级分类表，sku商品表，spu商品表，商品品牌表合并汇总为一张维度表！
  - 对事实表，参考星型模型的建模策略，按照选择业务过程→声明粒度→确认维度→确认事实思路进行建模
  选择业务过程：选择感兴趣的事实表
  声明粒度：选择最细的粒度！可以由最细的粒度通过聚合的方式得到粗粒度！
  确认维度：根据3w原则确认维度，挑选自己感兴趣的维度
  确认事实：挑选感兴趣的度量字段，一般是从事实表中选取！
- 数据中间层 DWM (Data WareHouse Middle）
  - 对明细数据按照常用维度做初步的汇总
  - 对数据做轻度的聚合操作，生成中间表，提升公共指标的复用性，减少重复加工
- 数据服务层 DWS （Data WareHouse Servce）
  - 又称数据集市或宽表
  - 按照业务划分，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。
- 在实际计算中，如果直接从DWD或者ODS计算出宽表的统计指标，会存在计算量太大并且维度太少的问题，因此一般的做法是，在DWM层先计算出多个小的中间表，然后再拼接成一张DWS的宽表。
数据应用层 APP （Application）
- 面向业务定制的应用数据
- 主要是提供给数据产品和数据分析使用的数据
- 报表数据存放在这
维表层 （Dimension）
- 高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。
- 低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。
如下图，可以认为是一个电商网站的数据体系设计。我们暂且只关注用户访问日志这一部分数据。
- 在ODS层中，由于各端的开发团队不同PC端、H访问、小程序等或者各种其它问题，用户的访问日志被分成了好几张表上报到了我们的ODS层。
- 为了方便大家的使用，我们在DWD层做了一张用户访问行为日志表，在这里，我们将PC网页、H5、小程序和原生APP访问日志汇聚到一张表里面，统一字段名，对部分枚举类型的值进行翻译，剔除异常数据，保证质量。
- 在DWM层，选取业务关注的核心维度来做聚合操作，比如只保留人、商品、设备和页面区域维度，计算出常用的统计指标：次数、时长。用户访问天表，用户登陆行为天表，订单行为天表
- 然后在DWS层，我们将一个人在整个网站中的行为数据放到一张表中，构建商品、商铺、地址主题宽表，更多用户相关的指标：登陆次数、访问次数、添加购物车次数、购买商品数、购买不同商品数、购买金额，时间维度数据：天、月级的数据。有了这张表，就可以快速满足大部分的通用型业务需求了。
- 最后，在APP应用层，根据需求从DWS层的一张或者多张表取出数据拼接成一张应用表即可。

分层的原则是什么？为什么要这样分层？每层之间的界限又是什么？

从对应用的支持来讲，我们希望越靠上层次，越对应用友好。比如APP层，基本是完全为应用来设计的，很易懂，DWS层的话，相对来讲就会有一点点理解成本，然后DWM和DWD层就比较难理解了，因为它的维度可能会比较多，而且一个需求可能要多张表经过很复杂的计算才能完成。
**从能力范围来讲，我们希望80%需求由20%的表来支持。直接点讲，就是大部分（80%以上）的需求，都用DWS的表来支持就行，**DWS支持不了的，就用DWM和DWD的表来支持，这些都支持不了的极少一部分数据需要从原始日志中捞取。结合第一点来讲的话就是：80%的需求，我们都希望以对应用很友好的方式来支持，而不是直接暴露给应用方原始日志。
从数据聚合程度来讲，我们希望，越上层数据的聚合程度越高，看上面的例子即可，ODS和DWD的数据基本是原始日志的粒度，不做任何聚合操作，DWM做了轻度的聚合操作只保留了通用的维度，DWS做了更高的聚合操作，可能只保留一到两个能表征当前描述主体的维度。从这个角度来看，我们又可以理解为我们是按照数据的聚合程度来划分数据层次的。

数据集市和数据仓库的区别

数据仓库是企业级的，能为整个企业各个部门的运行提供决策支持手段；
数据集市则是一种微型的数据仓库,它通常有更少的数据,更少的主题区域,以及更少的历史数据,因此是部门级的，

数据库和数据仓库有什么区别？

面向主题特性，操作型数据库是为了支撑各种业务而建立，需要严格满足完整性等约束和范式设计要求，而分析型数据库则是为了对从各种繁杂业务中抽象出来的分析主题(如用户、成本、商品等)进行分析而建立，可以不满足第一范式
集成性，数据仓库会将不同源数据库中的数据汇总到一起，比数据库更加庞大；
范围性，数据仓库内的数据是面向公司全局的，数据库可能面向部门的
历史性，数据仓库的时间跨度比较长，数据库可能保存几个月，数据仓库可能几年

维度建模

未看

HDFS文件读写流程

namenode和datanode通讯，未看

Hive和HBase的对比区别

Hive 数据仓库，Hive的本质其实就相当于将HDFS中已经存储的文件在Mysql中做了一个双射关系，以方便使用HQL去管理查询。
Hbase 数据库，面向列存储的非关系型数据库
Hive 适用于离线的数据分析和清洗，延迟较高
Hbase 适用于单表非关系型数据的存储，不适合做关联查询，延迟低适合在线业务
Hive 存储的数据依旧在DataNode上，编写的HQL语句会转换成MapReduce代码执行
HBase 数据持久存储放在DataNode上，以region的形式管理

Hive内部表和外部表的区别？

创建表时：创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。
删除表时：在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。

Hive分区表和分桶表的区别？

分区在HDFS上的表现形式是一个目录，分桶是一个单独的文件
分区: 细化数据管理，直接读对应目录，缩小mapreduce程序要扫描的数据量
分桶：提高join查询的效率（用分桶字段做连接字段），提高采样的效率

Hive调优及优化

1. 请慎重使用COUNT(DISTINCT col)

原因：distinct会将列中所有的数据保存到内存中，极有可能发生内存溢出
**解决方案：**可以考虑使用Group By 或者 ROW_NUMBER() OVER(PARTITION BY col)方式代替COUNT(DISTINCT col)

2. 小文件会造成资源的过度占用以及影响查询效率

原因：
- 小文件在HDFS中存储本身就会占用过多的内存空间，那么对于MR查询过程中过多的小文件又会造成启动过多的Mapper Task, 每个Mapper都是一个后台线程，会占用JVM的空间
- 在Hive中，动态分区会造成在插入数据过程中，生成过多零碎的小文件
- 不合理的Reducer Task数量的设置也会造成小文件的生成，因为最终Reducer是将数据落地到HDFS中的
- Hive中分桶表的设置
解决方案：
- 常见于在流计算的时候采用Sequencefile格式进行存储
- 减少reduce的数量(可以使用参数进行控制)
- 慎重使用动态分区，最好在分区中指定分区字段的val值
- 合并多个文件数据到一个文件中，重新构建表

3. 请慎重使用SELECT

**原因：**在大数据量多字段的数据表中，会造成很多无效数据的处理，会占用程序资源，造成资源的浪费
**解决方案：**在查询数据表时，指定所需的待查字段名，而非使用 * 号

4. 不要在表关联后面加WHERE条件

原因：

SELECT * FROM stu as t 
LEFT JOIN course as t1
ON t.id=t2.stu_id
WHERE t.age=18;

上面语句是否具有优化的空间？如何优化？

SELECT * FROM (SELECT * FROM stu WHERE age=18) as t 
LEFT JOIN course AS t1 on t.id=t1.stu_id

解决方案：
- 采用谓词下推的技术，提早进行过滤有可能减少必须在数据库分区之间传递的数据量
- 所谓谓词下推就是通过嵌套的方式，将底层查询语句尽量推到数据底层去过滤，这样在上层应用中就可以使用更少的数据量来查询，这种SQL技巧被称为谓词下推(Predicate pushdown)

5. 处理掉字段中带有空值的数据

原因：一个表内有许多空值时会导致MapReduce过程中,空成为一个key值,对应的会有大量的value值, 而一个key的value会一起到达reduce造成内存不足
解决方式：

1.在查询的时候，过滤掉所有为NULL的数据，比如：
create table res_tbl as  
select n.* from 
(select * from res where id is not null ) n 
left join org_tbl o on n.id = o.id;

2.查询出空值并给其赋上随机数,避免了key值为空（数据倾斜中常用的一种技巧）
create table res_tbl as
select n.* from res n 
full join org_tbl o 
on case when n.id is null then concat('hive', rand()) else n.id end = o.id;

6.设置并行执行任务数

通过设置参数 hive.exec.parallel 值为 true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果 job 中并行阶段增多，那么集群利用率就会增加。

-- 打开任务并行执行
set hive.exec.parallel=true;

-- 同一个 sql 允许最大并行度，默认为 8
set hive.exec.parallel.thread.number=16;

7. 设置合理的Reducer个数

原因：
- 过多的启动和初始化 reduce 也会消耗时间和资源
- 有多少个Reduer就会有多少个文件产生，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题
解决方案：
- Reducer设置的原则：每个Reduce处理的数据默认是256MB,每个任务最大的reduce数，默认为1009
- N=min(每个任务最大的reduce数，总输入数据量/reduce处理数据量大小)
- 设置Reducer的数量 set mapreduce.job.reduces=n

8. 为什么任务执行的时候只有一个reduce？

原因：
- 使用了Order by （Order By是会进行全局排序）
- 直接COUNT(1),没有加GROUP BY，比如：有笛卡尔积操作 SELECT COUNT(1) FROM tbl WHERE pt=’201909’
解决方案：
- 避免使用全局排序，可以使用sort by进行局部排序
- 使用GROUP BY进行统计，不会进行全局排序，比如：SELECT pt,COUNT(1) FROM tbl WHERE pt=’201909’group by pt;

9. 选择使用Tez引擎

Tez: 是基于Hadoop Yarn之上的DAG（有向无环图，Directed Acyclic Graph）计算框架。它把Ｍap/Reduce过程拆分成若干个子过程，同时可以把多个Ｍap/Reduce任务组合成一个较大的DAG任务，减少了Ｍap/Reduce之间的文件存储。同时合理组合其子过程，也可以减少任务的运行时间
设置 hive.execution.engine = tez;通过上述设置，执行的每个HIVE查询都将利用Tez, 当然，也可以选择使用spark作为计算引擎