hive sql获取周几 hive sql sum

Hive SQL 优化实践

文章目录

• Hive SQL 优化实践

• 前言
• 背景
• 1. Join无关的优化

• 1.1 group by 引起的优化
• 1.2 count distinct优化
• 1.3 map端数据倾斜

• 2.Join相关优化

• 2.1大表Join小表
• 2.2大表Join大表（两个表都超过了1GB）

• 2.2.1.间接转换为Map Side Join

• 适用场景
• 步骤

• 2.2.2.Join时用case when 语句

• 适用场景
• 步骤

• 2.2.3倍数B表，再取摸Join

• （1）通用方案
• （2）专用方案

• 2.2.4动态一分为二

前言

我们在编写Hive SQL时，通常因为编写不合理或者数据本身的问题，导致查询时间过长的问题（慢查询）。在使用MySQL时，我们通常是围绕索引来进行优化；而在Hive中通常解决数据倾斜的问题。下面就结合实例来讲解一下Hive优化的一些惯用思路。

背景

汇总表：卖家买家近n天的交易汇总信息

dw_trade_aggregate_da :buyer_id,seller_id,cnt （买家，卖家，近90天销售额）

卖家信息表

dw_hr_seller_info_da : seller_id,s_level (卖家，销售等级)

需求

获得每个买家在各个级别卖家的成交比例信息

初始解法

select m.buyer_id 
        ,SUM(cnt) AS cnt 
        ,SUM(case WHEN m.s_level=0 THEN cnt then 0 end) AS cnt_s0 
        ,SUM(case WHEN m.s_level=1 THEN cnt then 0 end) AS cnt_s1 
        ,SUM(case WHEN m.s_level=2 THEN cnt then 0 end) AS cnt_s2 
        ,SUM(case WHEN m.s_level=3 THEN cnt then 0 end) AS cnt_s3 
        ,SUM(case WHEN m.s_level=4 THEN cnt then 0 end) AS cnt_s4 
        ,SUM(case WHEN m.s_level=5 THEN cnt then 0 end) AS cnt_s5 
 from (  
 select  a.buyer_id
        ,a.seller_id
        ,b.s_level
        ,a.cnt
from (
    SELECT  buyer_id 
            ,seller_id 
            ,cnt
    FROM dw_trade_aggregate_da ) a
     join 
    (SELECT  seller_id 
            ,s_level
    FROM dw_hr_seller_info_da ) b
    ON a.seller_id=b.seller_id --按照seller_id进行关联，获取到卖家的销售等级
       ) m
    GROUP BY  m.buyer_id

1. Join无关的优化

1.1 group by 引起的优化

如果GROUP BY m.buyer_id因为buyer_id数据倾斜导致查询过慢。我们可以设置一下参数。

set hive.map.aggr =true --map聚合
set hive.groupby.skewindata=true --group by 倾斜的优化操作

原本的一个MR Job转换成两个MR job

1. 第一个MR job：map 输出回随机分布到Reduce中，做部分聚合操作并输出结果，进行负载均衡。
2. 第二个MR job：按key分发，保证相同key到同一个reducer，完成最终的聚合操作。

1.2 count distinct优化

直接使用count distinct会将Map端的输出直接分散的一个Reduce Task，会导致性能问题

一般先group by去重后,再count，伪代码如下：

SELECT  COUNT(id)
FROM 
(
	SELECT  *
	FROM student
	GROUP BY  id
)

1.3 map端数据倾斜

分片策略设置不正确，导致单个Map task 的数据量过大，处理速度缓慢。

set hive.tez.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; --合并小文件
set mapreduce.input.fileinputformat.split.minsize = 32000000; --分片的大小设置为32mb，最大不要超过1gb
set mapreduce.input.fileinputformat.split.maxsize = 32000000;
set mapreduce.input.fileinputformat.split.minsize.per.node= 32000000; --以节点为单位
set mapreduce.input.fileinputformat.split.minsize.per.rack= 32000000; --以机架为单位

2.Join相关优化

2.1大表Join小表

set hive.auto.convert.join= true;
set hive.auto.convert.join.nonconditionaltask.size =25000000

这样就开启了Map Side Join。流程如下

• 将小表读入内存当中。
• 分发到每个节点。
• 直接在内存中与大表进行Join，省略了shuffle和Reduce的过程，直接避免了数据倾斜的问题。
• 但是hive.auto.convert.join.nonconditionaltask.size不能超过1GB，因为HDFS显示的文件大小是压缩后的大小，与实际有可能膨胀十倍左右。

2.2大表Join大表（两个表都超过了1GB）

2.2.1.间接转换为Map Side Join

适用场景

若经过行列限制之后，满足Map Side Join的条件，即可解决

步骤

• 限制行
  按照某个条件，将当前业务需求内需要的数据过滤出来
• 限制列
  选取需要的字段

2.2.2.Join时用case when 语句

适用场景

倾斜的Key少却明确，可以单独处理。如null值引起的倾斜

步骤

• 参数设置

set hive.optimize.skewinfo=dw_hr_seller_info_da:(seller_id)[("0")("1")]; --dw_hr_seller_info_da的seller_id的0、1值引起倾斜
set hive.optimize.skewjoin=ture;

• 把空值转换为随机数

SELECT   a.buyer_id
        ,a.seller_id
        ,b.s_level
        ,a.cnt
FROM dw_trade_aggregate_da a
LEFT JOIN dw_hr_seller_info_da b
ON (CASE 
    		WHEN a.seller_id is null 
    				THEN concat('hive',rand()) -- null值此时替换为随机数，分散到各个reducer，解决倾斜
    		ELSE a.seller_id
   	END
   ) = b.seller_id

2.2.3倍数B表，再取摸Join

（1）通用方案

• B表Join上一个numbers表(这个表只有一列为1-10的整数，笛卡尔积)，这样就扩大了10倍。
• 在on的条件上，除了原来的key再加上mod(其他任意数值型字段,10)+1=number,去掉重复数据。
• 但是比较简单粗暴，所有数据都直接放大了10倍。

SELECT   m.buyer_id 
        ,SUM(cnt) AS cnt 
        ,SUM(case WHEN m.s_level=0 THEN cnt then 0 end) AS cnt_s0 
        ,SUM(case WHEN m.s_level=1 THEN cnt then 0 end) AS cnt_s1 
        ,SUM(case WHEN m.s_level=2 THEN cnt then 0 end) AS cnt_s2 
        ,SUM(case WHEN m.s_level=3 THEN cnt then 0 end) AS cnt_s3 
        ,SUM(case WHEN m.s_level=4 THEN cnt then 0 end) AS cnt_s4 
        ,SUM(case WHEN m.s_level=5 THEN cnt then 0 end) AS cnt_s5 
FROM 
(
	SELECT  buyer_id 
	       ,a.seller_id 
	       ,cnt 
	       ,b.s_level
	FROM dw_trade_aggregate_da a
	JOIN 
	(
		SELECT  seller_id 
		       ,s_level 
		       ,number
		FROM dw_hr_seller_info_da b
		JOIN numbers  --不指定join的key，则进行笛卡尔积，扩充10倍,相较于union all ，比较巧妙。
	) t
	ON a.seller_id = t.seller_id AND mod(cast(rand()*100 as int ,10)+1 = t.number  --按照两个字段进行分发,甚至可以是随机数更加均匀
) m
GROUP BY  m.seller_id

（2）专用方案

• 创建临时表，提前过滤出每日最新大卖家（dim_big_seller），并扩大原先设定的倍数（如100倍）

create external table if not exists dim.dim_big_seller_da(
	seller_id bigint
  ,seller_join_key bigint
)
stored as orc
location '/user/bigdata/dim/dim_big_seller_da'

insert overwrite table dim.dim_big_seller_da
select t.seller_id
			,concat(t.seller_id,'rnd',n.num ) as seller_join_key 
from (select seller_id
			from dw_trade_aggregate_da 
			group by seller_id
			having count(buyer_id) >=10000 -- 过滤出大卖家
) t
join numbers n -- 只有一列 0-100整数，用于扩大

• 在A表和B表分别新建一个join列，其逻辑为

• 大买家，则concat一个随机正整数
• 不是大买家，保持不变

select m.buyer_id 
        ,SUM(cnt) AS cnt 
        ,SUM(case WHEN m.s_level=0 THEN cnt else 0 end) AS cnt_s0 
        ,SUM(case WHEN m.s_level=1 THEN cnt else 0 end) AS cnt_s1 
        ,SUM(case WHEN m.s_level=2 THEN cnt else 0 end) AS cnt_s2 
        ,SUM(case WHEN m.s_level=3 THEN cnt else 0 end) AS cnt_s3 
        ,SUM(case WHEN m.s_level=4 THEN cnt else 0 end) AS cnt_s4 
        ,SUM(case WHEN m.s_level=5 THEN cnt else 0 end) AS cnt_s5
FROM 
(
	SELECT  a.buyer_id 
	       ,a.seller_id 
	       ,b.s_level 
	       ,a.cnt
	FROM 
	(
		SELECT  buyer_id 
		       ,seller_id 
		       ,cnt 
		       ,if (big.seller_id is not null 
                    ,concat(dw_trade_aggregate_da.seller_id,'rnd',cast(rand()*100 AS bigint)) -- 大卖家 
                    ,dw_trade_aggregate_da.seller_id) -- 非大卖家
                                                                       AS seller_id_joinkey 
		FROM dw_trade_aggregate_da
		LEFT JOIN 
		(
			SELECT  seller_id
			FROM dim_big_seller --提前处理出来的大卖家表
			GROUP BY  seller_id --去重 
		) big
		ON dw_trade_aggregate_da.seller_id=big.seller_id 
	) a
	JOIN 
	(
		SELECT  seller_id 
		       ,s_level 
		       ,coalesce(seller_id_joinkey,dw_hr_seller_info_da.seller_id) AS seller_id_joinkey -- 选其中不为空的
		FROM dw_hr_seller_info_da
		LEFT JOIN 
		(
			SELECT  seller_id 
			       ,seller_id_joinkey --生成逻辑和上面的生成逻辑一致
			FROM dim_big_seller 
		) big
		ON dw_hr_seller_info_da.seller_id=big.seller_id 
	) b
	ON a.seller_id_joinkey =b.seller_id_joinkey 
) m
GROUP BY  m.seller_id

2.2.4动态一分为二

1. 不倾斜的正常join
2. 倾斜的把他们找出来创建临时表，做map join
3. union all 1、2两部分

drop table if exists tmp.tmp_dw_hr_seller_info_da
--创建临时表
create external table if not exists tmp.tmp_dw_hr_seller_info_da  (
 seller_id bigint
 ,s_level bigint
)
PARTITIONED by (pt STRING)
row format delimited
fields terminated by '\u0001'
collection items terminated by '\u0002'
map keys terminated by '\u0003'
stored as orc
location '/user/bigdata/tmp/tmp_dw_hr_seller_info_da'
--将大卖家数据插入临时表
insert overwrite table tmp.tmp_dw_hr_seller_info_da
select seller_id,count(buyer_id) as byr_cnt
from dw_trade_aggregate_da
group by seller_id
having byr_cnt >=10000