Hive4

1.四大by

order by(全局排序)
只有一个reduce。这里无论设置多少个reduce数，都是一个
优点：全局排序
缺点：当数据量大的时候，耗时长，效率低，适用于较小数据量的场景（十万、百万）
sort by(分区内排序)
对每一个reduce内部的数据进行排序，全局结果来说不是排序的
也就是说只能保证每一个reduce输出的文件中的数据是按照规定字段排序的
适用于数据量大，但是对排序要求不严格的场景，可以大幅提高执行效率(抽样)
需要预先设置reduce个数，reduce结果文件内部有序，全局是无序的
set mapreduce.job.reduces = 10;
select * from emp sort by sal desc;
distribute by(分区排序)
控制特定的key到指定的reducer，方便后续的聚合操作，类似于MR中自定义分区，一般结合sort by使用
需要预先设置reduce个数，否则不会启动相应的reducer进行任务的执行，导致最后不能完全分区。
set mapreduce.job.reduces = 10;
select * from emp distribute by deptno sort by sal desc;
注意

distribute by要在sort by之前
distribute by的分区规则是根据分区字段的hash码与reduce的个数进行取模后，余数相同的分到一个分区
cluster by
当distribute by和sort by字段相同的时候，可以写成cluster by
但是这个排序，只能升序
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;
面试题

Q:订单表，取gmv（Gross Merchandise Volume，即商品交易总额，是成交总额（一定时间段内）的意思）top100的用户(比如有一亿的用户)

–全局排序显然不行，耗时长，效率低
select user_id from dwd_order_df order by gmv desc limit 100;
–多个reduce，分区内排序，最后再全局排序
set mapreduce.job.reduces = 100;
select * from (
select user_id,gmv
from dwd_order_df
distribute by city sort by gmv desc limit 100
) a
order by gmv limit 100;

2.三大join

common/shuffle/reduce join
最普通的join，产生shuffle，且这个会在reduce段join
mapjoin
通常用在大小表关联。
小表的阈值默认阈值是25M
set hive.mapjoin.smalltable.filesize = 25123456;–25M;
select /+ MAPJOIN(b)/ count(1)
from a --大表
join b --小表
on a.id = b.id
原理：将b表放在内存，在map端做join
join就发生在map操作的时候，每当扫描一个大表的数据，就要去内存查看小表，有没有与之相等的，继而进行关联
map端的join优势，没有shuffle

–设置参数，自动开启小表回收内存（与上等价）
set hive.auto.convert.join = true;
select count(1)
from a --大表
join b --小表
on a.id = b.id
SMB join（sort merge bucket）
大表和大表关联
限制：
a表和b表都是分桶的
a表和b表分桶的key必须是join key
设置一些参数
–默认是false
set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;
select count(1)
from a --大表5亿
join b --大表10亿
on a.id = b.id;

3.参数优化

mapjoin
set hive.mapjoin.smalltable.filesize = 25123456;–默认
set hive.auto.convert.join = true;
combiner 在map端进行聚合
set hive.map.agr = true;
在map端进行聚合的条数
set hive.groupby.mapaggr.checkinterval = 100000;–默认是10w
负载均衡
数据倾斜的时候
set hive.groupby.skewindata = true;
当选定为true，生成的查询计划会产生2个MR job
先加随机前缀，聚合一次
再去掉随机前缀，再聚合一次
4.思考
总结hive的SQL优化，比如count(distinct xxx)

1）行列过滤

列处理：在 SELECT 中，只拿需要的列，如果有，尽量使用分区过滤，少用 SELECT *。

行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 Where 后面，那么

就会先全表关联，之后再过滤。

2）采用分桶技术 (不提，数据量没那么大)

分到不同的文件。提高 join 查询的效率（用分桶字段做连接字段） ，提高采样的效率。

3）采用分区技术

分到不同的文件夹。好处是可以避免全表扫描 分区上线？？？？

4）小文件进行合并

1、为了减少 mapTask 数，当有大量小文件时，启动合并

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

set mapreduce.input.fileinputformat.split.maxsize=1073741824;

set mapreduce.input.fileinputformat.split.minsize=1;

set mapreduce.input.fileinputformat.split.minsize.per.node=536870912;

set mapreduce.input.fileinputformat.split.minsize.per.rack=536870912;

2、MR 作业结束后，判断生成文件的平均大小，如果小于阀值，就再启动一个 job 来合并文

件

set hive.merge.mapredfiles=true;

set hive.merge.mapfiles=true;

set hive.merge.smallfiles.avgsize=268435456;

合理设置 Map 数

①通常情况下，作业会通过 input 的目录产生一个或者多个 map 任务。

主要的决定因素有：input 的文件总个数，input 的文件大小，集群设置的文件块大小。

②是不是 map 数越多越好？

答案是否定的。如果一个任务有很多小文件（远远小于块大小 128m），则每个小文件也会

被当做一个块，用一个 map 任务来完成，而一个 map 任务启动和初始化的时间远远大于逻

辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的 map 数是受限的。

③是不是保证每个 map 处理接近 128m 的文件块，就高枕无忧了？

答案也是不一定。比如有一个 127m 的文件，正常会用一个 map 去完成，但这个文件只有

一个或者两个小字段，却有几千万的记录，如果 map 处理的逻辑比较复杂，用一个 map 任

务去做，肯定也比较耗时。

针对上面的问题 2 和 3，我们需要采取两种方式来解决：即减少 map 数和增加 map 数；

6）合理设置 Reduce 数

Reduce 个数并不是越多越好

①过多的启动和初始化 Reduce 也会消耗时间和资源；

55海量数据的传输、存储、计算

②另外，有多少个 Reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果

这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

在设置 Reduce 个数的时候也需要考虑这两个原则：处理大数据量利用合适的 Reduce 数；使

单个 Reduce 任务处理数据量大小要合适；

关于 map task 个数和 reduce task 个数问题

map task 个数要根据情况来设置

reduce task 个数要经过多次测试，求出最优方案，通常符合正态分布。

7）sql 小注意点

union 和 union all

union 去重，union all 不去重

Union 因为会将各查询子集的记录做比较，故比起 union all ，通常速度都会慢上许多。一

般来说，如果使用 union all 能满足要求的话，务必使用 union all。

去重时 distinct 和 group by

在能使用 group by 代替 distinc 就不要使用 distinct

使用 distinct 会将所有的数据都 shuffle 到一个 reduce 里面，group by 直接按条件分组，由多

个 reduce 处理，将数据分布到多台机器上执行，处理速度就快了

了解分桶

分桶是对数据进行哈希取值，然后放到不同文件中存储。

数据加载到桶表时，会对字段取 hash 值，然后与桶的数量取模。把数据放到对应的文件中。

物理上，每个桶就是表(或分区）目录里的一个文件，一个作业产生的桶(输出文件)和 reduce

任务个数相同。

桶表专门用于抽样查询，是很专业性的，不是日常用来存储数据的表，需要抽样查询时，才

创建和使用桶表。

元数据所有表的用途

HIVE版本，VERSION
数据库
DBS：数据库的基本信息
DATABASE_PARAMS：创建数据库时指定的参数
表
TBLS：表/视图的基本信息
TABLE_PARAMS：表/视图的属性信息
TBL_PRIVS：表/视图的授权信息
列
CDS：一个字段CD_ID，与SDS表关联
COLUMNS_V2：表的字段信息
SORT_COLS：排序字段信息
BUCKETING_COLS：分桶字段信息
分区
PARTITIONS：分区的基本信息
PARTITION_KEYS：分区的字段信息
PARTITION_KEY_VALS：分区的字段值
PARTITION_PARAMS：分区的属性信息
索引
IDXS：索引表
INDEX_PARAMS：索引相关的属性信息
函数
FUNCS：用户注册的函数信息
FUNC_RU：用户注册函数的资源信息
存储
SDS：文件存储的基本信息
SD_PARAMS：存储SDS表的属性信息
SERDES：存储序列化/反序列化调用的类信息
SERDE_PARAMS：存储序列化的一些属性、格式信息,比如：行、列分隔符
数据倾斜相关信息
SKEWED_COL_NAMES
SKEWED_COL_VALUE_LOC_MAP
SKEWED_STRING_LISTSKEWED_STRING_LIST_VALUES
SKEWED_VALUES
权限
ROLES
GLOBAL_PRIVS
PART_COL_PRIVS：分区字段的权限信息
PART_PRIVS：分区的权限信息
TBL_COL_PRIVS
统计信息
TAB_COL_STATS：表字段的统计信息
PART_COL_STATS：分区字段的统计信息