Hive_Hive 优化指南详解

Hive 的SQL 运行优化 ，我们可以从以下几个方面着手

 

优化方向简介

 

场景一 .去重场景问题

场景二.通过一定方式去减少JOB数量

场景三.合理控制并行数量

场景四.控制任务中 节点 / 文件 数量

场景五.排序问题

场景六.通过让Map端多承担压力, 减少Reduce计算成本和数据传输成本

场景七.数据倾斜问题

场景八.数据裁剪问题

场景九.减少IO次数

场景十.JVM重用

 

下面我们细化的去看这个问题

 

优化方向详细列表

 

TIPS: 有的优化细节，案例篇幅过长，会单独另起一篇文章。

 

场景一 .去重场景问题

1) UNION -- UNION ALL 之间的区别，如何取舍

2）DISTINCT 替代方式 GROUP BY

文章链接 ：https://blog.csdn.net/u010003835/article/details/105493563

 

场景二.减少JOB的数量

1) 巧妙的使用 UNION ALL 减少 JOB 数量

2) 利用多表相同的JOIN 条件，去减少 JOB 的数量

文章链接 ：https://blog.csdn.net/u010003835/article/details/105493938

 

场景三.合理进行并行控制

合理的使用并行化参数控制

针对于以下语句

1) UNION ALL

2) JOIN

文章链接 ：https://blog.csdn.net/u010003835/article/details/105494048

 

场景四.控制任务中 节点 / 文件 数量

1) Mapper 数量控制

2) Reducer 数量控制

3) 控制 Mapper，Reducer 输出的文件数量

文章链接：https://blog.csdn.net/u010003835/article/details/105494261

 

场景五.排序问题

1) 合理使用 ORDER BY 与 SORT BY , 在两者之间做取舍

2) 通过使用 LIMIT 限制排序的输出

文章链接：https://blog.csdn.net/u010003835/article/details/105494790

 

场景六.通过让MAP 端, 多去承担任务， 去减少 Reducer 的计算成本 和 数据传输成本。

1）MAP JOIN 的方式

2)  MAP AGGR , 在 Map 端进行预聚合

文章链接：https://blog.csdn.net/u010003835/article/details/105495067

 

场景七.数据倾斜问题

  1) 由于空值导致的数据倾斜问题

  2) 由于数据类型不一致，导致的转换问题，导致的数据倾斜

  3) 业务数据本身分布不均，导致的数据倾斜

文章链接：https://blog.csdn.net/u010003835/article/details/105495135

 

场景八.数据裁剪问题

1) 记录数裁剪

   i.通过分区，分桶 表的优势 去构建 

  ii.通过筛选条件，去除无效的记录，使得无效数据在 map 阶段 剔除

2) 列裁剪

 i.剔除无效, 非计算范围内的列数据

 ii.使用列式存储

文章链接：https://blog.csdn.net/u010003835/article/details/105495151

 

场景九.减少IO数量

1) 通过多表插入  FROM A INSERT B SELECT a, ... INSERT C SELECT a, ...

2) 一次输入,多次使用 WITH TABLE AS (...)

文章链接 : https://blog.csdn.net/u010003835/article/details/105495184

 

场景十.JVM重用

 见本文下面

 

下面我们分别去看下这些个优化方向

 

测试表以及测试数据

+----------------------------------------------------+
|                   createtab_stmt                   |
+----------------------------------------------------+
| CREATE TABLE `datacube_salary_org`(                |
|   `company_name` string COMMENT '????',            |
|   `dep_name` string COMMENT '????',                |
|   `user_id` bigint COMMENT '??id',                 |
|   `user_name` string COMMENT '????',               |
|   `salary` decimal(10,2) COMMENT '??',             |
|   `create_time` date COMMENT '????',               |
|   `update_time` date COMMENT '????')               |
| PARTITIONED BY (                                   |
|   `pt` string COMMENT '????')                      |
| ROW FORMAT SERDE                                   |
|   'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'  |
| WITH SERDEPROPERTIES (                             |
|   'field.delim'=',',                               |
|   'serialization.format'=',')                      |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' |
| LOCATION                                           |
|   'hdfs://cdh-manager:8020/user/hive/warehouse/data_warehouse_test.db/datacube_salary_org' |
| TBLPROPERTIES (                                    |
|   'transient_lastDdlTime'='1586310488')            |
+----------------------------------------------------+

+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+
| datacube_salary_org.company_name  | datacube_salary_org.dep_name  | datacube_salary_org.user_id  | datacube_salary_org.user_name  | datacube_salary_org.salary  | datacube_salary_org.create_time  | datacube_salary_org.update_time  | datacube_salary_org.pt  |
+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+
| s.zh                              | engineer                      | 1                            | szh                            | 28000.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| s.zh                              | engineer                      | 2                            | zyq                            | 26000.00                    | 2020-04-03                       | 2020-04-03                       | 20200405                |
| s.zh                              | tester                        | 3                            | gkm                            | 20000.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | finance                       | 4                            | pip                            | 13400.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | finance                       | 5                            | kip                            | 24500.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | finance                       | 6                            | zxxc                           | 13000.00                    | 2020-04-07                       | 2020-04-07                       | 20200405                |
| x.qx                              | kiccp                         | 7                            | xsz                            | 8600.00                     | 2020-04-07                       | 2020-04-07                       | 20200405                |
| s.zh                              | engineer                      | 1                            | szh                            | 28000.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| s.zh                              | engineer                      | 2                            | zyq                            | 26000.00                    | 2020-04-03                       | 2020-04-03                       | 20200406                |
| s.zh                              | tester                        | 3                            | gkm                            | 20000.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | finance                       | 4                            | pip                            | 13400.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | finance                       | 5                            | kip                            | 24500.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | finance                       | 6                            | zxxc                           | 13000.00                    | 2020-04-07                       | 2020-04-07                       | 20200406                |
| x.qx                              | kiccp                         | 7                            | xsz                            | 8600.00                     | 2020-04-07                       | 2020-04-07                       | 20200406                |
| s.zh                              | enginer                       | 1                            | szh                            | 28000.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| s.zh                              | enginer                       | 2                            | zyq                            | 26000.00                    | 2020-04-03                       | 2020-04-03                       | 20200407                |
| s.zh                              | tester                        | 3                            | gkm                            | 20000.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | finance                       | 4                            | pip                            | 13400.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | finance                       | 5                            | kip                            | 24500.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | finance                       | 6                            | zxxc                           | 13000.00                    | 2020-04-07                       | 2020-04-07                       | 20200407                |
| x.qx                              | kiccp                         | 7                            | xsz                            | 8600.00                     | 2020-04-07                       | 2020-04-07                       | 20200407                |
+-----------------------------------+-------------------------------+------------------------------+--------------------------------+-----------------------------+----------------------------------+----------------------------------+-------------------------+

 

 

 

场景一 .去重场景问题

1) UNION -- UNION ALL 之间的区别，如何取舍

2) DISTINCT 替代方式 GROUP BY

 

 

场景二.减少JOB的数量

1) 巧妙的使用 UNION ALL 减少 JOB 数量

2) 利用多表相同的JOIN 条件，去减少 JOB 的数量

 

 

场景三.合理进行并行控制

合理的使用并行化参数控制

针对于以下语句

1) UNION ALL

2) JOIN

 

场景四.控制任务中 节点 / 文件 数量

1) Mapper 数量控制

2) Reducer 数量控制

3) 控制 Mapper，Reducer 输出的文件数量

 

 

场景五.排序问题

1) 合理使用 ORDER BY 与 SORT BY , 在两者之间做取舍

2) 通过使用 LIMIT 限制排序的输出

 

 

 

 

场景六.通过让MAP 端, 多去承担任务， 去减少 Reducer 的计算成本 和 数据传输成本

1）MAP JOIN 的方式

2)  MAP AGGR , 在 Map 端进行预聚合

 

 

 

 

 

场景七.数据倾斜问题

1) 由于空值导致的数据倾斜问题

2) 由于数据类型不一致，导致的转换问题，导致的数据倾斜

3) 业务数据本身分布不均，导致的数据倾斜

 

 

 

场景八.数据裁剪问题

1) 记录数裁剪

   i.通过分区，分桶 表的优势 去构建 

  ii.通过筛选条件，去除无效的记录，使得无效数据在 map 阶段 剔除

2) 列裁剪

 i.剔除无效, 非计算范围内的列数据

 ii.使用列式存储

 

 

 

 

场景九.减少IO次数

1)  通过多表插入  FROM A INSERT B SELECT a, ... INSERT C SELECT a, ...

2) 一次输入,多次使用 WITH TABLE AS (...)

 

 

 

场景十.JVM重用

适用场景：

　　　　1.小文件个数过多

　　　　2.task个数过多

　　通过执行：set mapred.job.reuse.jvm.num.tasks=n;   来设置task插槽个数

　　缺点：

　　　　设置开启后，task插槽会一直占用资源，不论是否有task，直到所有的task即整个job执行完成后才会释放所有的task插槽资源！