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