Hive 知识重点梳理

Hive 知识重点梳理

1. Hive数据倾斜

原因
key分布不均匀
业务数据本身的特性
SQL语句造成数据倾斜（示例如下）

解决方法

1. hive设置如下
hive.map.aggr=true
在map中会做部分聚集操作，效率更高但需要更多的内存。
hive.groupby.skewindata=true
有数据倾斜的时候进行负载均衡，当选项设定为true,生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job在根据预处理的数据结果按照 Group By Key 分布到Reduce中(这个过程可以保证相同的 Group By Key 被分布到同一个Reduce中)，最后完成最终的聚合操作。

2. SQL语句调整:
- 选用join key分布最均匀的表作为驱动表。做好列裁剪和filter操作，以达到两表join的时候，数据量相对变小的效果。
- 大小表Join： 使用map join让小的维度表（1000条以下的记录条数）先进内存。在Map端完成Reduce。
- 大表Join大表：把空值的Key变成一个字符串加上一个随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终的结果。
- count distinct大量相同特殊值：count distinct时，将值为空的情况单独处理，如果是计算count
distinct，可以不用处理，直接过滤，在做后结果中加1。如果还有其他计算，需要进行group
by，可以先将值为空的记录单独处理，再和其他计算结果进行union.

2. 表数据拆分聚合

业务背景
hive表test_user_browse记录了用户浏览过的商品，该表的数据如下：
username product
John product_a,product_b,product_c,product_d
Tom product_e,product_f
现在，想要将product字段拆分为多行。
实现方式
现在借助lateral view 和explode()来完成。
select
username,
browse_product
from pms.test_user_browse
lateral view explode(split(product, ‘,’)) myTable as browse_product;

执行结果如下：
username browse_product
John product_a
John product_b
John product_c
John product_d
Tom product_e
Tom product_f

lateral view为侧视图,意义是为了配合UDTF来使用,把某一行数据拆分成多行数据.不加lateral view的UDTF只能提取单个字段拆分,并不能塞会原来数据表中.加上lateral view就可以将拆分的单个字段数据与原始表数据关联上.
在使用lateral view的时候需要指定视图别名和生成的新列别名
例如
select id,num from table lateral view explode(array1) subview as num;
subview为视图别名,num为指定新列别名

3. 排序四个by关键字用法

order by ：会对输入做全局排序，因此只有一个reducer(多个reducer无法保证全局有序).只有一个reducer,会导致当输入规模较大时，需要较长的计算时间。
sort by ：不是全局排序，其在数据进入reducer前完成排序
distribute by ：按照指定的字段对数据进行划分输出到不同的reduce中
cluster by ： 当distribute by 和sort by的字段相同时，等同于cluster by.可以看做特殊的distribute + sort

4. 三种join的原理

Hive中的join可分为三种，分别是Map-join、Reduce-join和SMB Join，本文简单介绍这三种join的原理和机制。

1.Map-join
MapJoin的主要意思就是，当链接的两个表是一个比较小的表和一个特别大的表的时候，我们把比较小的table直接放到内存中去，然后再对比较大的表格进行map操作。join就发生在map操作的时候，每当扫描一个大的table中的数据，就要去去查看小表的数据，哪条与之相符，继而进行连接。这里的join并不会涉及reduce操作。map端join的优势就是在于没有shuffle，能加快查询速度。

2.Reduce-join
Reduce-Join又叫做Shuffle-Join和Common-Join。如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join,即：在Reduce阶段完成join.
整个过程包含Map、Shuffle、Reduce阶段。

Map阶段
读取源表的数据，Map输出时候以Join on条件中的列为key，如果Join有多个关联键，则以这些关联键的组合作为key; Map输出的value为join之后所关心的(select或者where中需要用到的)列；同时在value中还会包含表的Tag信息，用于标明此value对应哪个表；按照key进行排序

Shuffle阶段
根据key的值进行hash,并将key/value按照hash值推送至不同的reduce中，这样确保两个表中相同的key位于同一个reduce中

Reduce阶段
根据key的值完成join操作，期间通过Tag来识别不同表中的数据。

3.SMB Join（sort merge bucket）
SMB 存在的目的主要是为了解决大表与大表间的 Join 问题，分桶其实就是把大表化成了“小表”，然后 Map-Side Join 解决之，这是典型的分而治之的思想。

set hive.enforce.bucketing=true;

set hive.enforce.sorting=true;

表优化数据目标：相同数据尽量聚集在一起

5. 数据压缩与存储格式选择

压缩方式

存储格式
读操作耗时对比：ORC<Parquet<RC<Text
结论：ORCFile存储文件读操作效率最高

存储效率分析：ORC<Parquet<RC<Text
结论：ORCFile存储文件占用空间少，压缩效率高

综上，使用 ORC + Gzip 的方式或者 ORC+Snappy 的方式，既能保证保证数据文件可以 split，又能保证不错的压缩/解压速度和压缩比。Gzip与Snappy不同的是，后者需要安装。
我们的数据 ods 层外部表的数据基本都是普通文本文件，为了压缩之后支持 split, 我们采用了 bzip2 压缩方式，bzip2 可以调整压缩比，缺点就是解压比较慢。DW 层内部表采用的是 ORC+Snappy 的模式。

6.分区和分桶

分区

1.是指按照数据表的某列或某些列分为多个区，区从形式上可以理解为文件夹，比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。
2.那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找

分桶
1.分桶是相对分区进行更细粒度的划分。
2.分桶将整个数据内容安装某列属性值得hash值进行区分，如要按照name属性分为3个桶，就是对name属性值的hash值对3取摸，按照取模结果对数据分桶。如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件

7.从海量数据中选出前100大的数字

典型堆排序的应用
1.若要从小到大排序，建大顶堆；若从大到小排，建小顶堆
2.比如求TOP100大，我们首先取前100个元素调整成小顶堆，如果发现，然后扫描后面的数据，并与堆顶元素比较，如果比堆顶元素大，那么用该元素替换堆顶，然后再调整为小顶堆。
最后堆中的元素就是TOP100大

8.Hive中追加导入数据的4种方式是什么？请写出简要语法

从本地导入： load data local inpath ‘/home/1.txt’ (overwrite)into table student;
从Hdfs导入： load data inpath ‘/user/hive/warehouse/1.txt’ (overwrite)into table student;
查询导入： create table student1 as select * from student;(也可以具体查询某项数据)
查询结果导入：insert （overwrite）into table staff select *from track_log;

9. Hive优化

通用设置
hive.optimize.cp=true：列裁剪
hive.optimize.prunner：分区裁剪
hive.limit.optimize.enable=true：优化LIMIT n语句
hive.limit.row.max.size=1000000：
hive.limit.optimize.limit.file=10：最大文件数

本地模式(小任务)

1. job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
2. job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)
3. job的reduce数必须为0或者1
   hive.exec.mode.local.auto.inputbytes.max=134217728
   hive.exec.mode.local.auto.tasks.max=4
   hive.exec.mode.local.auto=true
   hive.mapred.local.mem：本地模式启动的JVM内存大小

并发执行
hive.exec.parallel=true ，默认为false
hive.exec.parallel.thread.number=8

Strict Mode：
hive.mapred.mode=true
严格模式不允许执行以下查询：

• 分区表上没有指定了分区
• 没有limit限制的order by语句
• 笛卡尔积：JOIN时没有ON语句

动态分区
hive.exec.dynamic.partition.mode=strict：该模式下必须指定一个静态分区
hive.exec.max.dynamic.partitions=1000
hive.exec.max.dynamic.partitions.pernode=100：在每一个mapper/reducer节点允许创建的最大分区数
DATANODE：dfs.datanode.max.xceivers=8192：允许DATANODE打开多少个文件

推测执行
mapred.map.tasks.speculative.execution=true
mapred.reduce.tasks.speculative.execution=true
hive.mapred.reduce.tasks.speculative.execution=true;

多个group by合并
hive.multigroupby.singlemar=true：当多个GROUP BY语句有相同的分组列，则会优化为一个MR任务

虚拟列
hive.exec.rowoffset：是否提供虚拟列

分组
1.两个聚集函数不能有不同的DISTINCT列，以下表达式是错误的：

INSERT OVERWRITE TABLE pv_gender_agg SELECT pv_users.gender, count(DISTINCT pv_users.userid), count(DISTINCT pv_users.ip) FROM pv_users GROUP BY pv_users.gender;

2.SELECT语句中只能有GROUP BY的列或者聚集函数。
Combiner聚合
hive.map.aggr=true;在map中会做部分聚集操作，效率更高但需要更多的内存。
hive.groupby.mapaggr.checkinterval：在Map端进行聚合操作的条目数目

数据倾斜

1. hive.groupby.skewindata=true：数据倾斜时负载均衡，当选项设定为true，生成的查询计划会有两个MRJob。
2. 第一个MRJob 中，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupByKey 有可能被分发到不同的Reduce中，从而达到负载均衡的目的；
3. 第二个MRJob再根据预处理的数据结果按照GroupByKey分布到Reduce中（这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。

排序
ORDER BY colName ASC/DESC
hive.mapred.mode=strict时需要跟limit子句
hive.mapred.mode=nonstrict时使用单个reduce完成排序
SORT BY colName ASC/DESC ：每个reduce内排序
DISTRIBUTE BY(子查询情况下使用 )：控制特定行应该到哪个reducer，并不保证reduce内数据的顺序
CLUSTER BY ：当SORT BY 、DISTRIBUTE BY使用相同的列时。

合并小文件
hive.merg.mapfiles=true：合并map输出
hive.merge.mapredfiles=false：合并reduce输出
hive.merge.size.per.task=25610001000：合并文件的大小
hive.mergejob.maponly=true：如果支持CombineHiveInputFormat则生成只有Map的任务执行merge
hive.merge.smallfiles.avgsize=16000000：文件的平均大小小于该值时，会启动一个MR任务执行merge。

自定义map/reduce数目
1.减少map数目：
　　set mapred.max.split.size
　　set mapred.min.split.size
　　set mapred.min.split.size.per.node
　　set mapred.min.split.size.per.rack
　　set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
2.增加map数目：

1. 当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
2. 假设有这样一个任务： select data_desc, count(1), count(distinct id),sum(case
   when …),sum(case when …),sum(…) from a group by data_desc
3. 如果表a只有一个文件，大小为120M，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，我们要考虑将这一个文件合理的拆分成多个，这样就可以用多个map任务去完成。
   . 　set mapred.reduce.tasks=10; 　　
   　create table a_1 as select * from a distribute by rand(123);
4. 这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多。

3.reduce数目设置：

1. 参数1：hive.exec.reducers.bytes.per.reducer=1G：每个reduce任务处理的数据量
2. 参数2：hive.exec.reducers.max=999(0.95*TaskTracker数)：每个任务最大的reduce数目
3. reducer数=min(参数2,总输入数据量/参数1)
4. set mapred.reduce.tasks：每个任务默认的reduce数目。典型为0.99*reduce槽数，hive将其设置为-1，自动确定reduce数目。

使用索引：
hive.optimize.index.filter：自动使用索引
hive.optimize.index.groupby：使用聚合索引优化GROUP BY操作