Hive-Sql常用参数设置和开发技巧

常用开发技巧的整理：

1、Hive脚本常用参数的设置
##压缩设置
set mapred.compress.map.output = true;
set mapred.output.compress = true;
set hive.exec.compress.output = true;
##输出设置
set hive.merge.mapfiles = true;
set hive.merge.mapredfiles = true;
set hive.merge.size.per.task = 256000000;
set hive.merge.smallfiles.avgsize = 128000000;
##输入设置
set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
set mapred.max.split.size = 512000000;
set mapred.min.split.size.per.node = 256000000;
set mapred.min.split.size.per.rack = 256000000;
set hive.exec.reducers.bytes.per.reducer = 256000000;
##mapjoin设置
set hive.auto.convert.join = true;
set hive.mapjoin.smalltable.filesize = 10240000;
##并行设置
set hive.exec.parallel = true;
set hive.exec.parallel.thread.number = 8;
##性能提升设置
set hive.map.aggr = true;
##动态分区设置
set hive.exec.dynamic.partition.mode = nonstrict;
set hive.exec.dynamic.partition = true;
##内存相关设置
set mapreduce.map.java.opts=-Xmx20480m;
set mapreduce.reduce.java.opts=-Xmx20480m;
set mapreduce.map.memory.mb=30720;
set mapreduce.reduce.memory.mb=30720;

2、临时表的构建采用with这种方式，不用建立真实的临时表。
with
tmp_xxx1 as( select yyy1 from table_a where ...),
tmp_xxx2 as( select yyy2 from table_b where ...)

insert overwrite table target_table partition (dayno= '')
select
...
from tmp_xxx1
join tmp_xxx2 on ...
优势：临时表构建以后数据直接放在内存中，省去临时表的读写耗时。
对于构建多个临时表的脚本，可以通过和并行参数结合，同一个Sql内多个临时表并行构建，大大加快执行效率。
缺点：消耗的内存较多。

3、Mapjoin
可以通过设置参数，当表小于设定大小时，将小表自动放入内存，再map阶段做关联（小表放前面）。
当表的大小超过参数指定大小的时候，如果任需要做Mapjoin，则需要在Sql中显示指定需要放入内存的表。
select /* +mapjoin(t1) */
from table_a t1
join table_b t2 on ...

4、分桶、分桶表的用处主要有两个方面
大表之间做Mapjoin关联，如果两个大表直接做mapjoin，会报OOM错误，分桶之后再做mapjoin就可以很好的解决这个问题。
数据的抽样，分桶表是根据分桶字段的hash值随机分布到各桶中，每个桶中都是1个数量相当的随机样本。
set hive.enforce.bucketing = true; ##强制分桶参数设置
##建表
create external table xxx(
imei string,
...
)
partitioned by (dayno string)
clustered BY (imei) INTO 100 BUCKETS;
用于做关联的分桶表，分桶字段设置为关联字段，且关联的两个表的桶的个数需要能整除。

5、grouping sets 用于同一个表中的数据按照各种不同的维度进行汇总，在进行指标计算的时候非常实用。

select
coalesce(A,'all'), --维度A
coalesce(B,'all'), --维度B
coalesce(C,'all') --维度C
COUNT(*) --指标1
SUM(...) --指标2
from table_xxxx
group by
A,
B,
C
grouping sets
(
(),
(A),
(B),
(A,C),
(B,C),
(A,B,C)
)

 

6、对map内容进行行转列

hive写法：

select tf.key,count(1) as cnt,count(distinct imei) as user
from (
select *
from dw.f_evt_xx_sdk_log
where dayno=30000101
and category_code='10000'
and act_code='200'
) t lateral view explode(t.event_info) tf as key
group by tf.key

 

presto写法：

select tf.key,count(1) as cnt
,count(distinct imei) as user
from (select *from dw.f_evt_xx_sdk_log where dayno=30000101and category_code='10000' and act_code='200') t
CROSS JOIN UNNEST(t.event_info) as tf (key)
group by tf.key