Hive SQL优化方式及使用技巧

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 '01'
    lines terminated by '
'
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 '01'
    lines terminated by '
'
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.unin 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 * from(
select * from a group by key,value
union all
select key,value from b group by key,value
)
---- 一个job，其中select *不会产生mapreduce任务，用于小表

不同表太多的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实际扫描多少分区

转载请注明出处：https://www.cnblogs.com/fnlingnzb-learner/p/13087976.html