Hive 4

1.hive的四大by

1.1order by（全局排序）

只有一个reduce，这里无论你设置多少个reduce，都是一个

优点：全局排序

缺点：当数据量大的时候，耗时长，效率低，适用于较小数据量的场景

1.2sort by（分区内排序）

对每一个reduce内部的数据进行排序，全局结果来说不是排序的

只能保证每一个reduce输出的文件中的数据是按照规定字段排序的

适用于数据量大，但是对排序要求不严格的场景，可以大幅提高执行效率

1.3distribute by（分区排序）

控制特定的key到指定的reducer,方便后续的聚合操作，类似于MR中自定义分区，一般结合sort by使用

需要预先设置reduce个数，否则不会启动相应的reducer进行任务的执行，导致最后不能完全分区。

注意：

（1）distribute by 要在 sort by 之前

（2）distribute by 的分区规则是根据分区字段的hash码与reduce的个数进行取模后，余数相同的分到一个分区

1.4cluster by

当distribute by 和 sort by 字段相同的时候，可以写成cluster by

但是这个排序，只能升序

2.hive的三大join

2.1 common/shuffle/reduce join

最普通的join，产生shuffle，且这个会在reduce端做join

2.2 mapjoin

通常用在大小表关联

小表的阈值

set hive.mapjoin.smalltable.filesize = 25123456; --25M

select /*+ MAPJOIN(b) */ count(1)

from a --大表

from 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 --大表

from b --小表

on a.id = b.id;

2.3  SMB join

sort merge bucket

大表和大表关联

a 5亿  ，  b 10亿

限制：

1.a  b  都是分桶的

2.a  b  分桶的key必须是join key

3.设置一些参数

set hive.auto.convert.sortmerge.join=true;

set hive.optimize.bucketmapjoin=true;

set hive.optimize.buckermapjoin.sortedmerge=true;

select count(1)

from a --大表

from b --大表

on a.id = b.id;

3.hive的参数优化

3.1 mapjoin

set hive.mapjoin.smalltable.filesize = 25123456;

set hive.auto.convert.join = true;

3.2 combiner 在map端进行聚合

set hive.map.aggr = true;

3.3 负载均衡

数据倾斜的时候

set hive.groupby.skewindata = true;

当选定为true，生成的查询计划会产生两个MR job

先加随机前缀，聚合一次

再去掉随机前缀，再聚合一次

4.UDF

4.1 pom.xml 增加hive-exec

4.2 编写java

        继承UDF

        实现evaluate

4.3 打包上传服务器lib，赋权

4.4 导入jar包

        add jar

        创建函数

面试题：

订单表，取gmv  top100的用户

比如说有1亿的用户有gmv

select user_id from dwd_order_df order by gmv desc limit 100;

全局排序显然不行，耗时长，效率低

可不可以多个reduce，分区内排序，最后在全局排序

select *

from (select user_id

                ,gmv

                from dwd_order_df

        distribute by city sort by gmv desc limit 100

) x

order by gmv desc limit 100;

作业：

1. hive的SQL优化，比如count(distinct xxx)

（1）列裁剪和分区裁剪

最基本的操作。所谓列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。

（2）sort by代替order by

HiveQL中的order by与其他SQL方言中的功能一样，就是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完。如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。

（3）group by代替distinct

当要统计某一列的去重数时，如果数据量很大，count(distinct)就会非常慢，原因与order by类似，count(distinct)逻辑只会有很少的reducer来处理。这时可以用group by来改写

（4）空值或无意义值

当事实表是日志类数据时，往往会有一些项没有记录到，我们视情况会将它置为null，或者空字符串、-1等。如果缺失的项很多，在做join时这些空值就会非常集中，拖累进度。因此，若不需要空值数据，就提前写where语句过滤掉。

（5）合并小文件

2.了解分桶

分桶：并非所有的数据集都可形成合理的分区，特别之前所提到过的要确定合适的划分大小的疑虑。对于每一个表或者分区，可以进一步细分成桶，桶是对数据进行更细粒度的划分。Hive默认采用对某一列的每个数据进行hash（哈希），使用hashcode对 桶的个数求余，确定该条记录放入哪个桶中。

3.整理元数据所有表的用途

表名	作用
version	存储Hive版本的元数据表
DBS	该表存储Hive中所有数据库的基本信息
DATABASE_PARAMS	该表存储数据库的相关参数
TBLS	该表中存储Hive表、视图、索引表的基本信息
TABLE_PARAMS	该表存储表/视图的属性信息
TBL_PRIVS	该表存储表/视图的授权信息
SDS	该表保存文件存储的基本信息
SD_PARAMS	该表存储Hive存储的属性信息，在创建表时候使用
SERDES	该表存储序列化使用的类信息
SERDE_PARAMS	该表存储序列化的一些属性、格式信息
COLUMNS_V2	该表存储表对应的字段信息
PARTITIONS	该表存储表分区的基本信息
PARTITION_KEYS	该表存储分区的字段信息
PARTITION_KEY_VALS	该表存储分区字段值
PARTITION_PARAMS	该表存储分区的属性信息