HQL使用技巧

任务优化

常见的方法包括防止数据倾斜、减少MR Job数和增加任务并行数等等，在优化前我们可以用Explain查看sql的执行计划。

• 了解数据特点
• 降低数据量
• 数据倾斜
  • 调节参数
  • count distinct数据倾斜
  • join数据倾斜
  • 大key值造成数据倾斜
  • 数据类型不同数据倾斜
• 排序优化
• Union all优化
• Hive框架优化
• 异常排查

了解数据特点

• 查看表结构、字段类型、含义、分区等
• 查看重复数据、数据分布、数据总量

select colname, count(1) cnt
from table 
group by colname
having count(1)>num

降低数据量

• 合理使用条件过滤
  where条件后筛选合适的分区和必要的过滤条件
• 查询时去掉不必要的字段
  select后不加用不到的字段

数据倾斜

当我们发现SQL语句的执行日志中，reduce任务长时间卡在99%，这时我们可能就是遇到了数据倾斜的问题，一般可以通过参数设置和SQL调优解决

参数设置

set hive.groupby.skewindata = true #group by 操作是否支持倾斜数据。

• 含义：控制生成两个MR Job,第一个MR Job Map的输出结果随机分配到reduce中减少某些key值条数过多某些key条数过小造成的数据倾斜问题。
• 解释：
  在第一个 MapReduce 中，map 的输出结果集合会随机分布到 reduce 中， 每个reduce 做部分聚合操作，并输出结果。这样处理的结果是，相同的 Group By Key 有可能分发到不同的reduce中，从而达到负载均衡的目的；
  第二个 MapReduce 任务再根据预处理的数据结果按照 Group By Key 分布到 reduce 中（这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中），最后完成最终的聚合操作。

SQL优化

Count distinct数据倾斜

举例来说，假设我们要统计近一年发文作者数

select count(distinct author_id)
from table1
where dt between '2021-07-11' and '2022-07-10'
and p_num>=1

此时，由于使用了distinct，因此在Map阶段不能用combine对输出结果驱虫，必须将author_id作为Key输出，在Reduce阶段再对来自于不同Map Task、相同Key的结果去重
这个任务在执行时Reduce num为1，导致最终Map的全部输出由单个Reduce Task处理，Reduce Task需要Shuffle大量的数据，还需要进行排序聚合

**解决方法：**用group by代替count distinct

select count(author_id)
from
	(select author_id
	from table1
	where dt between '2021-07-11' and '2022-07-10'
	and p_num>=1
	group by author_id) t1

大Key值造成数据倾斜

关联0或者空值这种异常值造成的数据倾斜
解决方式：

1. 异常值不参与关联
2. 将0值和空值用rand()随机散，比如

case when t1.author_id is null then rand() else t1.author_id end ```
##### 数据类型不同造成数据倾斜
例如t1和t2表中，author_id的数据类型分别是字符型和数值型，这样当按照author_id进行两表join时，默认的Hash操作会按数值型的author_id来分配，这会导致所有字符型的author_id的记录都被分配到一个Reducer中。
**解决方式：**把数值型转化成字符型
```sql
select * from t1 left join t2 on (t1.author_id = cast(t2.author_id as string))

任务优化-Join优化原则

• 将数据量少的表/子查询或者join key分布均匀的放在join的左边
• 在join时，提前过滤掉不需要的数据
• 相同join条件的查询只消耗一个job，尽量放在一起

任务优化-排序优化

• order by实现的是全局排序，一个reduce，，输入规模较大时建议使用limit
• sort by实现部分有序，多个reduce，只保证每个reduce输出的结果是有序的，然后对所有产出结果做一次归并排序即可，效率高
• distribute by将所选字段划分reduce
• cluster by是sort by和distribute by的结合，等价于distribute by col sort by col，需要注意的是cluster by指定的列只能是降序，不能指定asc和desc

任务优化-排序优化(取Top N)

• row_number：排序不允许并列，，积食两条记录的值相等，也不会出现相等的排序值
• rank：排序时出现相等的值会并列，即值相等的两条数据会有相同的序列值1,1,2,3,4
• dense_rank：排序的值允许并列，但会跳跃地排序，例如1,1,3,4,5,5,7

任务优化-Union all

善用union all，多表union all会优化成一个MR job，但在使用union all聚合时，由于聚合函数、分组会使MR job数增加，消除子查询内的group by、巧用中间表（临时表、单独处理子查询内的Join等能起到的效果

HQL常用关键字

HQL常用关键字经典案例（抽样）

随机抽样

使用rand()函数和limit关键字来获得抽样数据。distribute和sort关键字在这里用来确保mappers和reducers可以高效的使用随机分布的数据。order by rand()语句也可达到相同的目的

select * from <Table_Name> distribute by rand() sort by rand() limit <n>

桶表抽样(Bucket table sampling)

桶表抽样是一种桶表进行优化的抽样。其中的col值指定抽样数据的列，在对整行抽样的时候可以同样使用rand()函数。如果抽样字段跟cluster by字段相同，tablesample语句会更加高效

select * from <Table_Name> tablesample(bucket <specified bucket number to sample> 
out of <total number of buckets> on [colname|rand()]
#举例
select author_id
from tabel1
tablesample(bucket 1 out of 2 on rand()) a
where p_num>=1

块抽样(Block sampling)

块抽样允许Hive随机抽取N%的数据量作为输入数据。抽样的粒度是HDFS Block的大小（默认256M）

select * from <Table_Name> 
tablesample(n percent|byteLengthLiteral|n rows) s;

#根据比例抽样
select author_id
from table1 tablesample (10 percent) a
where p_num>=1

#根据数据大小抽样
select author_id
from table1 tablesample (3M) a
where p_num>=1

#根据行抽样
select author_id
from table1 tablesample (4 rows) a
where p_num>=1

HQL常用关键字经典案例（分析窗口函数）

• CUME_DIST 统计小于等于当前值的行数占分组内总行数的比例
• PERCENT_RANK （分组内当前行RANK-1）/（分组总行-1）
• NTILE(n) 将分组数据按照顺序切分成n片，返回当前切片值
• LEAD(col, n, default) 对当前列(col)向下查找n条记录，n不指定时默认1，default不指定时默认为null
• LAG(col, n, default) 对当前列(col)向上查找n条记录，n不指定时默认1，default不指定时默认为null
• FIRST_VALUE(col)、LAST_VALUE(col) 分别在当前分组中查找该列的第一个、最后一个值
• Grouping sets 在一个group by查询中，根据不同的维度进行聚合，等价于将不同维度的结果集进行一个union all（）
• WITH CUBE 根据group by的所有维度的所有组合进行union

#WITH CUBE
select count(author_id) as a_cnt,r_date,p_cat 
from table1 
group by r_date,p_cat with cube
#等价union all
select count(author_id) as a_cnt,null,null from table1 
union all
select count(author_id) as a_cnt,r_date,null from table1 group by r_date
union all
select count(author_id) as a_cnt,null,p_cat from table1 group by p_cat
union all
select count(author_id) as a_cnt,r_date,p_cat from table1 group by r_date,p_cat
#等价Grouping  sets
select count(author_id) as a_cnt,r_date,p_cat 
from table1 
group by r_date,p_cat grouping sets((),(r_date),(p_cat),(r_date,p_cat))

• WITH ROLLUP 是CUBE的子集，以最左侧维度为主对维度进行层级聚合，用来实现上卷、下钻功能

#WITH CUBE
select count(author_id) as a_cnt,r_date,p_cat 
from table1 
group by r_date,p_cat with rollup
#等价union all
select count(author_id) as a_cnt,null,null from table1 
union all
select count(author_id) as a_cnt,r_date,null from table1 group by r_date
union all
select count(author_id) as a_cnt,r_date,p_cat from table1 group by r_date,p_cat
#等价Grouping  sets
select count(author_id) as a_cnt,r_date,p_cat 
from table1 
group by r_date,p_cat grouping sets((),(r_date),(r_date,p_cat))