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 '\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实际扫描多少分区

 

 

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

posted @ 2020-06-10 19:20  Boblim  阅读(12228)  评论(0编辑  收藏  举报