hiveSQL优化

本文主要介绍关于hive,hadoop,sql的知识点，对【HIVE SQL 代码优化总结(容易踩坑的地方)】和【hive SQL优化】有兴趣的朋友可以看下由【lingan_Hong】投稿的技术文章，希望该技术和经验能帮到你解决你所遇的Hive相关技术问题。

hive SQL优化

一、Hadoop简介

Hadoop有两大核心：HDFS和MapReduce。
hdfs即为 Hadoop distributed file system，负责分布式数据存储；
mr即为MapReduce，负责分布式数据运算。

hive提供基于sql的查询语言用以查询数据的方式，运行hive时，脚本被编译为MapReduce进行执行。

Hive的优化主要分为：配置优化、SQL语句优化、任务优化等方案。
其中在开发过程中主要涉及到的可能是SQL优化这块。
优化的核心思想是以下四个：
减少数据量（分区裁剪、列剪裁）
避免数据倾斜（例如加参数、Key打散）
避免全表扫描（例如on添加加上分区等）
减少job数（例如相同的on条件的join放在一起作为一个任务）

二、HIVE SQL执行顺序

第一步：确定数据源，进行表的查询和加载
from
(left/right/inner/outner) join
on
第二步：过滤数据，进行条件筛选
where
group by
having
第三步：查询数据
select
第四步：显示数据
distinct
order by
limit
union/union all

二、优化技巧和代码编写注意事项 1. 列裁剪和分区裁剪

a. 列裁剪(说人话就是选自己要的字段)
这里就是尽量不使用select * from table；
而要使用 select 字段A，字段B，… from table;

b. 分区裁剪(说人话就是对于分区表要进行分区字段进行过滤)
如果不做分区操作，hive每次都会进行全表扫描，在查询的时候通过where选择指定的分区，查询效率高很多。
尽量不要直接对超大的分区表进行select 操作。如 某张表存量数据量超千亿条，TB级别。
对于分区表要进行分区字段进行过滤，然后同时注意在where后面把分区字段的筛选放到具体id的查询前面；
如何区分哪个是分区表，可以在hive使用的使用时候，在元数据搜索出来以后，可以对表查看数据字典，一般分区字段会显示PartitionField
举例：

select 字段A，字段B from table where 分区字段 = 20220101 and id = ’aaa‘;

2. 表连接（left/right/inner/outter）join优化

a. 注意使用相同的连接键
观察好要连接的两张表的主键，不要使用错误的字段进行连接。

b. 小表在前，大表在后
将条数少的表、或者子查询后的表放在Join操作符的左边。但新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。不过在做join的过程中通过小表在前可以适当地减少数据量，提高效率。

c. 太多个join操作，连接过于复杂时候，可以建立中间表

d. 尽早过滤数据，在子查询或者中间表中过滤数据， 同时 join后面不要跟where条件
因为这样会导致匹配的时候全表join，如果提前过滤数据，会提高效率。

举例：

select ... from table1 A
inner join table2 B
on A.key = B.key
where A.id>1 and id<10
and B.date > '2022-01-01' and B.date < '2022-07-01'

以上写法不好，
应该为：

select A.col1,A.col2, B.col3, B.col4
from (select col1,col2      
      from database.table1  
      where id > 1 and id < 10) A
inner join 
     (select col3,col4
     from database.table2 
     where date > '2022-01-01' and date < '2022-07-01'
     ) B
on A.key = B.key;

3. 避免数据倾斜

数据倾斜：就是某一个或几个key占据了整个数据的90%，或者null太多，没有处理就进行join操作，这样整个任务的效率都会被这个key的处理拖慢，同时也可能会因为相同的key会聚合到一起造成内存溢出。
可能造成数据倾斜操作的算子有：distinct、join、groupby、count distinct等。出现数据倾斜时， 可能就是你的代码中使用了这些算子中的某一个所导致的。表现出来就是hive上面的进程一直在运行没有结果。

举例，解决办法：
a. 通过参数调优：
set hive.map.aggr=true;
set hive.groupby.skewindata = ture;
但是这个处理方案对于我们来说是个黑盒，无法把控。

b. 提前了解要进行join表的字段的值的分布，如果与大量null或者无意义的值，要对于的null的值进行剔除，然后再join操作或者聚合操作。

select 字段A from TABLE where id not in ('',' ','\n','null','\null','na','\N','无')

c. 一般情况下，两张表进行join，on连接的字段都具有唯一性或者是表的主键，不会有大量的70%的重复值，这样写的时候，是否也观察一下是不是自己用来连接的字段是错的。

4. 避免笛卡尔积

笛卡尔积的定义：如A表1000行，B表100万行，笛卡尔积为1000*100w=1亿行
所以hive里面不允许使用笛卡尔积操作，会导致数据量暴增，计算集群会爆掉。这里要注意空值、无意义值、长尾数据。
join操作必须带有on，否则会产生笛卡尔积。

5. order by

使用order by必须带着limit ，因为oder by是全局排序，速度非常慢。
或者使用sort by 代替order by

6.distinct 使用group by 替代

比如要对某列去除计数，不要使用count(distinct)， 要使用 group by

select count(distinct id)
from tableA
where date='2020-08-10' and id is not null

转换为

select count(a.uid)
from
(select id from tableA where id is not null and date = '2020-08-10' group by id) a

7、使用with as

在SQL中需要重复使用某张表、某部分数据（子查询）的情况，通常会选择通过with as语法，但是这种优化与子查询的数据结果的大小有关，超过某个特定的大小则不建议使用此方法，可以直接新建一个临时表。

8、注意编写规范和注释

a. 缩进与对齐
SQL如果编写的代码过长，如300、500行，在编写上如果 缩进对齐 或 范式 过于随意，嵌套太多，则可能过几天自己也看不懂自己的脚本。如下列代码：

select A.col1,A.col2, B.col3, B.col4
from (select col1,col2      
      from database.table1  
      where id > 1 and id < 10
      ) A
inner join 
     (select col3,col4
      from database.table2 
      where date > '2022-01-01' and date < '2022-07-01'
     ) B
on A.key = B.key;

b. 写好注释
其次对字段、库名、表名写好注释，脚本开头写好声明和脚本撰写作者，否则也会造成脚本不易读懂。

----------------------------------------------
---表名：xtable
---表名说明：知乎专栏日志
---开发者：lingan on 03-25 2021
---修改者：lingan on 03-25 2022
---修改内容：xxxxxxx
----------------------------------------------
drop table if not exists tableA;
create table if not exists tableA 
as
select A.col1,  ---字段col1
       A.col2,  ---字段col2
       B.col3,  ---字段col3
       B.col4   ---字段col4
from (select col1,col2      
      from database.table1  
      where id > 1 and id < 10
      ) A   ---表A
inner join 
     (select col3,col4
      from database.table2 
      where date > '2022-01-01' and date < '2022-07-01'
     ) B     ---表B
on A.key = B.key;

本文《HIVE SQL 代码优化总结(容易踩坑的地方)》版权归lingan_Hong所有，引用HIVE SQL 代码优化总结(容易踩坑的地方)需遵循CC 4.0 BY-SA版权协议。