Hive简介和Hive SQL优化

Hive 基本概念

Hive SQL 的优化方式

 

---

Hive 基本概念

hive是什么	为什么需要hive	hive的作用
hive是由Facebook开源用于解决海量结构化日志的数据统计； hive是一个基于hadoop的数据库工具,可以将结构化数据映射成一张数据表,并提供类SQL的查询功能 hive的本质是将SQL语句转化成MapReduce程序，然后在hdfs上运行MapREduce，并返回结果	没有hive，我们只能通过写mapReduce任务来访问hdfs中的数据，难度大成本高 有了hive，就可以通过SQL的方式来访问HDFS文件，同时提供了HDFS中结构化数据的元数据管理（比如列名，列的类型等）	把SQL语句转化成mapreduce代码 可以对数据进行存储 存储使用 HDFS 可以对数据进行计算 计算使用 MapReduce

 

---

Hive SQL 的优化方式

优化方向	优化操作	具体操作
尽早尽量过滤数据，减少每个阶段的数据量	列裁剪	避免select (*） 确定要select 的a,b,c,d等列
	分区裁剪	记得卡dt分区   想要知道一段sql依赖的是表的哪些分区 可以使用explain dependency select * from xxx
减少job数目	多张表关联的时候，如果join的key相同，那么不管有多少个表，都会合并成一个MapReduce任务	----------------- 1 job a和c都是通过b.key1来完成连接 select * from a.val, b.val, c.val from a join b on (a.key = b.key1) join c on (c.key = b.key1) ----------------- 2 job a和b通过key1, c和b通过key2 select * from a.val, b.val, c.val from a join b on (a.key = b.key1) join c on (c.key = b.key1)
join 操作以及优化	数据过滤	在join前过滤掉不需要的数据 select a.key  from (select key.val from A where dt = '20016-06-16') a left outer join (select key.val from B where dt = '20016-06-16') b on a.key = b.key
	小表 join 大表	join的左表会加载进内存，提高查询效率 A：1w行 B：100w行 select a.key, b.value from A a join B b on a.key = b.key
	小表用MAPJOIN     避免笛卡尔积（join 语句中要有on)	在hive可以在两个阶段进行join 1. mapjoin. hive在进行join的过程中需要shuffle，将小表加入到每个mapper的内存中，就可以省去shuffle的过程。 局限条件：只有使用hive引擎，且两张join的表的数据量相差非常大时，才会有优化效果 select /*+ mapjoin(b)*/ a.key, a.value from a join b on a.key = b.key 在join链接查询中没有on链接，而通过where条件语句会产生笛卡尔集 Select   a.key, b.value  from  A  a  join  B  b  where  a.key=b.key  （错误） Select   a.key, b.value  from  A  a  join  B  b  on  a.key=b.key（正确）

join 操作及优化

left semi join

left join 和 join 区别： B 表有重复值的情况下，left semi join 产生一条，join产生多条 exemple: select a.key, a.value from a left semi join b on (a.key = b.key)

 

数据去重	group by 效率比 distinct 更高效	尽量避免用distinct  用group by 代替 distinct col  select key from table group by key 用group by 代替 count(distinct key) select sum(1) from ( select key from a group by key) t
排序	order by  sort by  cluster by 不常用	order by 实现全局排序，一个reduce实现，由于不能并发执行，所以效率偏低 sort by 实现部分有序，单个reduce输出的结果是有序的，效率高，通常和distribute by 关键字一起使用。如 DISTRIBUTE BY col SORT BY col
解决数据倾斜问题	数据倾斜定义：把一个SQL语句分成100个任务，让10个机器一起完成。正常情况下，每个机器处理10个任务，但是数据倾斜的情况下，1个机器负责90个任务，其他9个机器处理10个任务	Join 关联，小表与大表关联，易出现数据倾斜   解决方法：Mapjoin，在map阶段完成join，不需要reduce.  Select /*+ MAPJOIN(b) */             a.key, a.value from a join b on a.key = b.key
		Join 关联，大表与大表关联，关联字段空值过多，易出现数据倾斜   解决方法：把空置变成一个字符串加上随机数，把倾斜的数据分布到不同的reduce上 select * from log a left outer join users b on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;   也可以在关联前，将关联的两张表的空值进行剔除
		Join 关联，不同数据类型关联产生数据倾斜，易出现数据倾斜   解决方法：字符类型转换 select * from users a left outer join logs b on a.usr_id = cast(b.user_id as string)