数仓开发和Hive优化

什么是数据仓库

数据仓库是面向主题的,集成的,相对稳定的,反应历史变化的数据集合,用于支持企业或组织决策分析处理

OLAP是多维数据库,主要用于多维分析

数据仓库和数据库的区别

数据库是面向事务的,数据由日常产生,存储当前交易数据,一般设计要符合三范式

数据仓库是面向主题的, 数据来源于数据库或文件等,一般存储历史数据, 经过一定的规则转换得到,用来分析的,其设计一般是星型的，有利于查询

数据仓库的基本架构是什么/说一说你整个项目从数据采集到指标、报表的这整个过程是怎么做的

数据源->ETL->数据存储与管理->OLAP->BI工具

数据源:企业的各类信息,包括存放RDBMS关系型数据库中的各种业务处理数据和各类文档数据,市场信息,竞争对手的信息等等

数据存储与管理:这是整个数据仓库的核心,针对各业务系统的数据,进行抽取,清理,并有效集成,按照主题进行组织, 按覆盖范围可分为企业级数仓和部门级数仓

OLAP服务器:对需要的数据进行有效集成,按多维模型组织,以便多角度,多层次分析,并发现趋势,具体可分为:

ROLAP:关系型在线分析处理

MOLAP:多维在线分析处理

MOLAP:混合型线上分析处理

模型设计的思路:一是自上而下,一是自下而上 推崇自下而上, 建设数据仓库应该按照实际的应用需求，加载需要的数据，不需要的数据不要加载到数据仓库中

数仓建模的四步:

1.选择业务过程

比如电商项目要完成: 启动app,搜索,登陆,点击,下单,加购物车,支付,退货,评价,点赞,收藏,发货 以后生成的事实表 业务模块

2.声明粒度

按照时间的最小粒度

3.确定维度

日期,地域,商品,用户,仓库,优惠卷,物流商家,设备,渠道,版本,操作系统 以后生成的维度表

4.确定事实

ods层从用户行为表和MySQL业务数据库里直接拿过来的 保持数据原貌

dwd层 确定维度表和事实表 并作维度退化, 围绕事实表,组建星型模型

dws层

设备总表: id 这一天干了什么 访客 来了不一定注册

用户总表: 首次登陆的时间,累计登陆的次数,最近30日登陆的次数

首次下单时间,累计下单次数,最近30日下单次数,末次下单时间

首次支付金额,累计支付金额,最近30日支付金额,末次支付金额

商品总表/宽表

数据分析OPLAP:

数据立方体:时间轴,地域轴,商品轴,等,就是维表,事实表要分析的字段,看起来就像多维立方体一样

数据立方体的操作:切片,切块,旋转,上卷,下钻

在数据立方体上选择某一维的操作叫切片,对二维或多维选择叫切块,主要是对where语句做工作,模拟sql如下:

切片: select Locates.地区,Products.分类,SUM(数量) from Sales,Dates,Products,Locates where Dates.季度 = 2 and Sales.Date_key = Date.Date_key and Sales.Locate_key = locates.locates_key and Sales.Product_key = Products.Product_key group by Locates.地区,Products.分类

切块:select Locates.地区,Products.分类,SUM(数量) from Sales,Dates,Products,Locates where  (Dates.季度 = 2 OR Dates.季度 = 3) AND` (Locates.地区 ='江苏OR`Locates.地区='上海') and Sales.Date_key = Date.Date_key and Sales.Locate_key = locates.locates_key and Sales.Product_key = Products.Product_key group by Dates.季度, Locates.地区,Products.分类

旋转 就是指改变报表或页面的展示方向,从sql上来说就是改变select后面字段的顺序而已

上卷 就是不看某些维度, 下钻就是将某些维度进行细分,主要是对group by语句的工作

留存分析,漏斗分析,事件分析,用户分析,粘性分析,全行为路径分析

数据质量监控:

1.单表数据量监控

一张表的记录数在一个已知的范围内,或者上下浮动不会超过某个阀值

sql结果:var 数据量 = count(昨天)

数值检测:如果数据量不在[数值上限,数值下限],则触发报警

同步增加:如果(今天的数据量 - 上周的数据量)/上周的数据量*100)不在[比例下线, 比例上限]则触发报警

环比增加:如果(今天的数据量 - 昨天的数据量)/昨天的数据量*100)不在[比例下线,比例上限],则触发报警

阀值配置的六个数值当中,至少配置一个阀值,对于没有配置的阀值,不做监控

2.单表空值监控

某个字段为空的记录数在一个范围内,或者占总数量的百分比在某个阀值范围内

目标字段:选择要监控的字段,不能选"无"

sql结果:var 异常数据量 = count(目标字段为空的记录)

单次检测:如果(异常数据量)不在[数值上限,数值下限],则触发报警

3.单表重复值检测:

一个或多个字段是否满足某些规则

目标字段:选择要监控的字段,groupby这里的字段列表,没有重复

单词检测:如果(异常数据量)不在[数值上限,数值下限],则触发报警

4.单表值域检测

一个或多个字段没有重复记录

目标字段:选择要监控的字段,支持多选

检测规则:填写"目标字段"要满足的条件.其中$1表示第一个目标字段,$2第二个目标字段,以此类推

阀值配置与空值检测相同

5.跨表数据量对比

主要针对同步流程,监控两张表的数据量是否一致

sql结果:count(本表)-count(关联表)

阀值配置与空值检测相同

6.跨表主外键对比

监控字段和关联字段:需要符合外键约束的两个字段

阀值配置与"空值检测"相同

拉链表是怎么实现的/缓慢变化维你们是怎么处理的？

拉链表主要用于数据会发生变化,但是大多数的数据是缓慢变化的

拉链表的使用:生效开始日期<=某个日期 且 生效结束日期 >= 某个日期,能够得到某个时间点的数据全量切片

例: select * from order_info where stat_date<=‘2020-01-01’ and end_date>=‘2020-01-01’

1.建立拉链表, 字段与MySQL中一致,表末加上start_date有效开始日期和end_date有效结束日期

2.通过insert overwrite table 表名 select 字段 , ‘2020-01-01’ ,‘9999-99-99’ from 表名 where 分区 初始化拉链表

3.创建临时拉链表, 订单变化表 union all(初始拉链表 left join 订单变化表) left join条件是,如果历史拉链表中没有订单变化表例的数据,就是新增数据,否则为变化数据…订单变化表中id在初始拉链表中不存在，为null，即为新增数据，则这条数据的结束日期就是该条数据的结束日期，不需要改变。 订单变化表中id在初始拉链表中存在，不为null，即为需要变化数据，则这条数据的添加到拉链表中时，要将id相同的上一条数据的日期的9999-99-99改成date_add(oi.dt,-1)，即新的这条数据的前一天的日期

4.把临时拉链表覆盖给拉链表

UDF, UDAF, UDTF怎么写，分哪几个步骤，怎么转换成mr的？

udf 输入一行数据输出一行数据 Java代码中继承UDF, 实现evalute方法

udaf 输入多行数据输出一行数据,一般在group by 中使用 Java代码中继承UDAF, 实现init(初始化),iterate(函数处理读入行数据),terminatePartialfal(返回iterate处理的中间结果), merge(合并上述处理结果),terminate(返回最终结果)

udtf 实现一行输入多行输出 Java代码中继承GenericUDTF, 实现如下方法: initialize(初始化校验参数是否正确), process(处理返回结果), forward(将结果返回)

例UDF函数

自定义UDF函数
1．创建一个Maven工程Hive
2．导入依赖
<dependencies>
    <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>2.1.1</version>
        </dependency>
</dependencies>
3．创建一个类
package com.atguigu.hive;
import org.apache.hadoop.hive.ql.exec.UDF;

public class Lower extends UDF {

	public String evaluate (final String s) {
		
		if (s == null) {
			return null;
		}
		
		return s.toLowerCase();
	}
}
4．打成jar包上传到服务器
5．将jar包添加到hive的classpath
hive (default)> add jar /opt/module/datas/udf.jar;
6．创建临时函数与开发好的java class关联
hive (default)> create temporary function mylower as "com.alibaba.hive.Lower";
7．即可在hql中使用自定义的函数strip 
hive (default)> select ename, mylower(ename) lowername from emp;

Hive的执行原理

1. 用户提交查询等任务给Driver。
2. 编译器获得该用户的任务Plan。
3. 编译器Compiler根据用户任务去MetaStore中获取需要的Hive的元数据信息。
4. 编译器Compiler得到元数据信息，对任务进行编译，先将HiveQL转换为抽象语法树，然后将抽象语法树转换成查询块，将查询块转化为逻辑的查询计划，重写逻辑查询计划，将逻辑计划转化为物理的计划（MapReduce）, 最后选择最佳的策略。
5. 将最终的计划提交给Driver。
6. Driver将计划Plan转交给ExecutionEngine去执行，获取元数据信息，提交给JobTracker或者SourceManager执行该任务，任务会直接读取HDFS中文件进行相应的操作。
7. 获取执行的结果。
8. 取得并返回执行结果。
   

Hive数据倾斜场景一:

解决办法:

Hive数据倾斜场景二:

不同数据类型关联产生数据倾斜

如果a.id是string类型, b.id是bigint类型 当它俩join,hive会把a.id转化为数字id,做hash来分配reduce, 那么就到一个reduce上去了. 解决办法就是类型转换成string

Select * from log01 a
Left outer join log2 b
On a.id = cast(b.id asstring);

Hive常见优化

1. 驱动表, 选用join key分布最均匀的表作为驱动表;
2. 行列过滤(只取需要的列, 先将行过滤, 再join);
3. 将小表进行缓存, 在map端完成reduce;
4. 大表join大表时, 将空值附上随机数, 分配到不同的reduce上 如果优化效果不大, 可以将数据倾斜的数据,单独拿出来进行处理, 最后union
5. 分区分桶

(分区表相当于hive的索引，加快查询速度) (两个表join的时候，如果两个表在相同列上有分桶操作，会减少join数据量【要求两个表的桶数量要相同或成倍数】)

7. map阶段数据压缩

set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

8. 小文件合并,减少map数

set hive.merge.mapfiles = true ##在 map only 的任务结束时合并小文件
set hive.merge.mapredfiles = false ## true 时在 MapReduce 的任务结束时合并小文件
set hive.merge.size.per.task = 25610001000 ##合并文件的大小
set mapred.max.split.size=256000000; ##每个 Map 最大分割大小
set mapred.min.split.size.per.node=1; ##一个节点上 split 的最少值
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ##执行Map前进行小文件合并

9. 合理设置reduce的个数(有多少个reduce就会有多少个文件输出, 如果生成的都是小文件, 那么作为下次输入时,就会面对很多小文件的问题)

第一种方法：根据数据量调整reduce个数 hive.exec.reducers.bytes.per.reducer 【设置每个reduce处理的数据量，默认256M】
第二种方法: 直接设置reduce的个数 set mapred.reduce.tasks = number

10.并行计算，stage不依赖的时候可以并行计算

// 开启任务并行执行
set hive.exec.parallel=true;
// 同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;

11.对最终生成的hive表数据压缩

1、通过参数设置
set hive.exec.compress.output=true
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
2、通过建表语句设置
create table tablename (
xxx,string
xxx, bigint
)
ROW FORMAT DELTMITED FIELDS TERMINATED BY ‘\t’
STORED AS orc tblproperties(“orc.compress” = “SNAPPY”)

常见SQL优化:

1.	count distinct优化
		不要使用count (distinct   cloumn) ,而要使用子查询实现count(distinct)
		select count(1) from (select id from tablename group by id) tmp;
2.	Distinct 与 group by的区别
Distinct 是对全局/全表进行去重, shuffle后只会在一个reduce中进行, 容易发生数据倾斜
Group by 是分组去重, 有多少个task就会生成多少个reduce, 性能更高
3.	如果需要根据一张表的字段约束另一个张表，用in代替join
		select id,name from tb1  a join tb2 b on(a.id = b.id);
		可优化为
		select id,name from tb1 where id in(select id from tb2); in 要比join 快
4.where语句优化
		select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';
		可优化为
		select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id);
5.union优化
		尽量不要使用union （union 去掉重复的记录）而是使用 union all 然后在用group by 去重
6.消灭子查询内的 group by 、 COUNT(DISTINCT)，MAX，MIN。 可以减少job的数量。