HiveSQL优化方式及使用技巧

HiveSQL优化⽅式及使⽤技巧
HIVE简介
Hive是基于Hadoop的⼀个数据仓库⼯具，可以将结构化的数据⽂件映射为⼀张数据库表，并提供简单的sql查询，可以将sql语句转换为MapReduce任务进⾏运⾏
同时，hive也允许熟悉map-reduce的开发者开发⾃定义的mapper和reducer来处理内建的mapper和reducer⽆法处理的复杂的分析⼯作。

⽤户发出sql命令----> hive处理并转换为MapReduce---->提交任务到hadoop并运⾏在hdfs
HIVE DDL
Hive建表(压缩表和⾮压缩表)
⼀个表可以拥有⼀个或者多个分区，每个分区以⽂件夹的形式单独存在于表⽂件夹的⽬录下
创建表：指定exterbal关键字的就是外部表，没有就是内部表。

内部表在drop的时候会从hdfs上删除数据，外部表不会删除
如果不指定数据库，hive会把表创建在默认数据库下。

创建内部表：
create table if not exists `my_inner_table`(
`id` bigint comment '逻辑id,记录的唯⼀id',
`user_id` string comment 'user_id'
) comment '内部表'
partitioned by (`dt` string comment 'date, yyyy-MM-dd')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY'\001'
lines terminated by'\n'
STORED AS TEXTFILE
创建外部表：
create external table if not exists `my_external_table`(
`id` bigint comment '逻辑id,记录的唯⼀id',
`user_id` string comment 'user_id'
) comment '外部表'
partitioned by (`dt` string comment 'date, yyyy-MM-dd')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY'\001'
lines terminated by'\n'
STORED AS TEXTFILE
location 'hdfs://user/user.sql/';
HIVE SQL优化
优化的根本思想：
尽早尽量过滤数据，减少每个阶段的数据量
减少job数
解决数据倾斜问题
尽早尽量过滤数据，减少每个阶段的数据量
1.列裁剪：
例如某表有a,b,c,d,e五个字段，但是我们只需要a和b，那么请⽤select a,b from table ⽽不是select * from table
2.分区裁剪：
在查询的过程中减少不必要的分区，即尽量指定分区
3.利⽤hive的优化机制减少job数：
不论是外关联outer join还是内关联inner join，如果join的key相同，不管有多少表，都会合并为⼀个MapReduce任务：
select a.val,b.val,c.val from a JOIN b ON (a.key= b.key1) JOIN c ON (c.key2 = b.key1) ----⼀个job
select a.val,b.val,c.val from a JOIN b ON (a.key= b.key1) JOIN c ON (c.key2 = b.key2) ----两个job
4.善⽤multi-insert:
#查询了两次a
insert overwrite table tmp1
select ... from a where条件1;
insert overwrite table tmp2
select ... from a where条件2;
#查询了⼀次a
from a
insert overwrite table tmp1
select ... where条件1
insert overwrite table tmp2
select ... where条件2
5.善⽤union all:
不同表的union all相当于multi inputs，同⼀表的union all相当于map⼀次输出多条
6.避免笛卡尔积：关联的时候⼀定要写关联条件
7.join前过滤掉不需要的数据
#hive0.12之前，会先把a全部数据和b的全部数据进⾏了关联，然后再筛选条件，0.12之后做了优化
1. select a.val,b.val from a LEFT OUTER JOIN b ON (a.key=b.key)where a.dt='2020-05-07'and b.dt='2020-05-07'
#优化后的⽅案
2.select x.val,y.val from
(select key, val from a where a.dt='2020-05-07') x
LEFT OUTER JOIN
(select key, val from b where b.dt='2020-05-07') y
ON x.key=y.key
8.⼩表放前⼤表放后
在编写带有join的代码语句时，应该将条⽬少的表/⼦查询放在join操作符的前⾯
因为在Reduce阶段，位于join操作符左边的表会先被加载到内存，载⼊条⽬较少的表可以有效的防⽌内存溢出(OOM)。

所以对于同⼀个key 来说，对应的value值⼩的放前⾯，⼤的放后⾯
9.在map阶段进⾏join
join阶段有两种，⼀种是在map阶段进⾏的，⼀种是在reduce阶段进⾏的。

当⼩表和⼤表进⾏join时，尽量采⽤mapjoin，即在map端完成，尽早结合数据，使reduce端接收数据量减少。

同时可以避免⼩表与⼤表join产⽣的数据倾斜。

如果⼀个表特别⼩，推荐⽤mapjoin；如果不是，我们⼀般⽤reduce join
MAPJOIN写法：
select/*+ MAPJOIN(b) */ a.value,b.value from a join b on a.key=b.key
10.hive0.13之前实现IN和EXISTS
LEFT SEMI JOIN是IN和EXISTS的⼀种⾼效实现，在hive0.13之前是不⽀持IN和EXISTS的
1.select a.key,a.value from a where a.key in (select b.key from b)（hive0.13前不⽀持）
2.select a.key,a.value from a left outer join b on a.key=b.key where b.key is not null
3.select a.key,a.value from a left semi join b on a.key=b.key
4.select a.key,a.value from a JOIN b ON (a.key=b.key)
LEFT SEMI JOIN产⽣的数据不会重复。

11.使⽤动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
create table my_table(
id bigint
) partitioned by(dt string)
STORED AS TEXTFILE;
insert overwrite table my_table partition(dt)
select id,dt from tmp_table
12.union all优化
hive0.13之前不⽀持union all直接放外层，必须外层套⼀个查询，例如：
select key,value from a
union all
select key,value from b
是不⽀持的。

hive对union all的优化只局限于⾮嵌套查询。

select*from(
select key,value from a group by key,value
union all
select key,value from b group by key,value
)
---- 三个job
select key,value from(
select key,value from a union all
select key,value from b
) as tmp
group by tmp.key,tmp.value
---- ⼀个job，⽤于⼩表
不同表太多的union all，不推荐使⽤。

可以写在中间表的不同分区⾥，然后再进⾏union all
13.尽量避免使⽤distinct
尽量避免使⽤distinct进⾏重排，特别是⼤表，容易产⽣数据倾斜(key⼀样在⼀个reduce处理)。

使⽤group by替代
select distinct key from a
select key from a group by key
14.排序优化
只有order by产⽣的结果是全局有序的，可以根据实际场景进⾏选择排序
order by实现全局排序，⼀个reduce实现，由于不能并发执⾏，所以效率低
sort by实现部分有序，单个reduce的输出结果是有序的，效率⾼，通常与distribute by⼀起使⽤(distribute by 关键词可以指定map到reduce的key分发)
cluster by col1等价于distribute by col1 sort by col1，但不能指定排序规则
15.使⽤explain dependency查看sql实际扫描多少分区
转载请注明出处：。