Hive SQL 优化篇

一只鱼_yin
206 阅读9分钟

Hive SQL的本质是将SQL语言映射到Hadoop分布式存储和计算框架上，将SQL语句转换成MapReduce程序进行执行，通常应用在海量数据的查询处理中，因此在使用中常常需要进行调优，以提高数据查询或处理效率。在平时工作中，影响数据处理效率的主要体现在数据倾斜、数据冗余、复杂sql等问题。在实际使用中需综合考虑多个影响因素并进行优化。本文主要记录下常见的优化点，后续若发现有其他相关内容，会继续补充。

1.排除某些列

可以使用正则表达式来排除某些列，前提需要将 hive.support.quoted.identifiers值修改为none。使用语法SELECT (event_ts)?+.+ FROM <table>;。

# 查看下参数当前值
==>set hive.support.quoted.identifiers;
+-----------------------------------------+
|                   set                   |
+-----------------------------------------+
| hive.support.quoted.identifiers=column  |
+-----------------------------------------+
==>set hive.support.quoted.identifiers=none;
+---------------------------------------+
|                  set                  |
+---------------------------------------+
| hive.support.quoted.identifiers=none  |
+---------------------------------------+
# 修改完成

# 测试查询除 age以外的字段
==> desc test_table;
+-----------+------------+----------+
| col_name  | data_type  | comment  |
+-----------+------------+----------+
| id        | string     |          |
| name      | string     |          |
| age       | string     |          |
+-----------+------------+----------+
# 执行查询语句
==> SELECT `(age)?+.+` FROM test_table t;
+-------+---------+
| t.id  | t.name  |
+-------+---------+
+-------+---------+

参考：cloud.tencent.com/developer/a…

2.列裁剪和分区裁剪

其实就是在查询的时候，过滤掉不需要使用的列和分区,尤其是表的列数和分区数很大的时候，可以很好的减少读取开销，中间表存储开销和数据整合开销。

select id,name 
from test_table where pt_d='2022-11-01'

裁剪所对应的参数项为：hive.optimize.cp=true（默认值为真） 分区参数为：hive.optimize.pruner=true（默认值为真）

解析阶段对应的则是ColumnPruner逻辑优化器

3.谓词下推

就是将SQL语句中的where谓词逻辑都尽可能提前执行，减少下游处理的数据量。对应逻辑优化器是PredicatePushDown,该优化器就是将OperatorTree中的FilterOperator向上提。

SELECT
     t1.id
     ,t2.name
     ,t2.address
FROM test_a  t1
LEFT OUTER JOIN
(
    SELECT  id
            ,name
            ,address
    FROM test_b
    WHERE pt_d='2022-11-01'
) t2
ON t1.id=t2.id;

4.排序

hive中的排序语法有4个，Sort By，Order By，Cluster By，Distrbute By

  • Order By ：会对输入做全局排序，因此只有一个reduce，性能较低
  • Sort By：不是全局排序，其在数据进入reducer前完成排序， 只会保证每个reducer的输出有序
  • Distrbute By：是控制在map端如何拆分数据给reduce端的，按照指定的字段对数据进行划分输出到不同的reduce中
  • Cluster By：除了具有 distribute by 的功能外还兼具 sort by 的功能。排序只能是倒叙排序，不能指定排序规则为ASC或者DESC

实际应用中，更多使用Distrbute By与Sort By结合

SELECT
id
,create_time
FROM test_a
distrbute by id  sort by  create_time desc

5.去重

group by代替distinct，distinct通常在海量数据查询时效率比较低，这是由于按group by字段分组，按distinct字段排序，当group by字段是数量较少的枚举值时，会导致分配到较少的reduce个数，导致reduce负担较大，耗时增加。

SELECT COUNT(1)
FROM
(
    SELECT
    name  
    FROM test_a
    group by name
)T

使用过程中需要结合实际情况使用，当存在类似分组字段时尽量改写distinct，否则不用。这里涉及到改写后会多启动一个mr，因此要确保数据量计算耗时要大于job启动耗时。

6.GROUP BY配置

  • Map端聚合：并不是所有的聚合操作都集中在reduce进行，很多聚合操作是可以在Map端进行预聚合，然后再在reduce端再聚合输出
    • hive.map.aggr=true，默认 true，对应的优化器为GroupByOptimizer
    • hive.groupby.mapaggr.checkinterval=100000，设置map端预聚合的行数阈值，超过该值就会分拆job，默认值100000
  • 数据倾斜时进行负载均衡
    • hive.groupby.skewindata=false，默认false，使用时根据需要改为true，在group by时启动两个MR,第一个job会将map端数据随机输入reducer，每个reducer做部分聚合，相同的key就会分布在不同的reducer中。第二个job再将前面预处理过的数据按key聚合并输出结果，这样就起到了均衡的效果。

7.Join优化

7.1 小表前置

Hive在解析SQL时，会优先将join左边的表优先读进内存中，将小表放在join的左边，可以有效的减少内存溢出(OOM)的风险。

SELECT t1.id
FROM test_a  t1
LEFT OUTER JOIN test_b  t2
ON t1.id=t2.id

7.2 多表join时key相同

当出现多表join时，key相同时会合并成一个MR来处理

SELECT t1.id
FROM test_a  t1
LEFT OUTER JOIN test_a  t2
ON t1.id=t2.id
LEFT OUTER JOIN
LEFT OUTER JOIN test_c  t3
ON t1.id=t3.id

若例子中的两个join中的条件不通，比如：t1.type=t3.type,就会多生成一个job

7.3 Map Join

就是将join操作在Map阶段完成，需要的数据在 Map 的过程中可以访问到则不再需要Reduce。 原理： 原始Join的MR任务之前创建一个新的MR本地任务，是将小表数据从 HDFS 上读取到内存中的哈希表中，读完后，将内存中的哈希表序列化为哈希表文件。在下一阶段，当MR任务启动时，会将这个哈希表文件上传到 Hadoop 分布式缓存中，该缓存会将这些文件发送到每个Mapper的本地磁盘上。所有 Mapper 都可以将此持久化的哈希表文件加载回内存，小表只需要读取一次。

SELECT
 /*+ MAPJOIN(t1) */
t1.id
,count(t2.id) as num
FROM test_a  t1
LEFT OUTER JOIN test_a  t2
ON t1.id=t2.id
group by t1.id
;

也可通过参数设置，在执行时通过参数值判断，将Join转成Map Join。 参数：

  • set hive.auto.convert.join = true; # 默认为false，参数为true时，Hive自动对左边的表进行统计，如果是小表就加入内存，即对小表使用Map join
  • set hive.mapjoin.smalltable.filesize=25000000; 大表小表的阀值，默认值是25mb
  • set hive.mapjoin.followby.gby.localtask.max.memory.usage=0.55，map join做group by 操作时，可以使用多大的内存来存储数据，如果数据太大，则不会保存在内存里
  • set hive.mapjoin.localtask.max.memory.usage=0.9，本地任务可以使用内存的百分比

参考:blog.csdn.net/kwu_ganymed… cloud.tencent.com/developer/a…

8. 合并小文件

小文件：小于hdfs数据块的文件（hadoop1.：64M，hadoop2.：128M） 小文件的危害：容易在文件存储端造成瓶颈，给 HDFS 带来压力，影响处理效率 可以通过一些参数设置进行预防，在MR处理时对文件进行合并

  • set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; 执行Map前进行小文件合并

  • set mapred.max.split.size=256000000; 每个Map最大输入大小(这个值决定了合并后文件的数量)

  • set mapred.min.split.size.per.node=100000000; 一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)

  • set mapred.min.split.size.per.rack=100000000; 一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)

  • set hive.merge.mapfiles = true, 设置map端输出进行合并，默认为true

  • set hive.merge.mapredfiles = true,设置reduce端输出进行合并，默认为false

  • set hive.merge.size.per.task = 25610001000, 设置合并文件的大小

  • set hive.merge.smallfiles.avgsize=1024000000, 当输出文件的平均大小小于1GB时，启动一个独立的map-reduce任务进行文件merge

9.数据倾斜

数据倾斜通常表现为：

任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大

造成这个情况的原因一般主要涉及到两方面

  • 一是任务中需要处理大量相同的key的数据
  • 二是任务读取不可分割的大文件

具体的解决方案：

  • 空值或无意义值
    实际业务种有些大量的null或者无意义的数据参与其中，在表join时，触发shuffle被分配到一个reduce中了
    方案：（1）过滤null和无意义的数据，不参与join操作；（2）给null的数据随机赋值，让其分配到不同的reduce中

  • 不同数据类型
    参与join的其中一个表的某个字段中存在不同数据类型string和int，导致在join时，hash会按int类型进行分配，这样所有string的都会被分配到一个reduce中
    方案：将类型统一，转为string

  • 不可拆分的大文件
    存在适用GZIP等不支持分割的压缩格式文件，这类文件只能被一个任务读取，导致数据读取时某个map成为瓶颈；
    方案：转为支持分割的压缩文件格式：bzip2/zip，或者增加set mapreduce.map.memory.mb=4096;,set mapreduce.map.java.opts=-Xmx3600m;

10.JVM重用

在MR job中，默认是每执行一个task就启动一个JVM。如果task非常小而碎，那么JVM启动和关闭的耗时就会很长。可以通过调节参数mapred.job.reuse.jvm.num.tasks来重用。例如将这个参数设成5，那么就代表同一个MR job中顺序执行的5个task可以重复使用一个JVM，减少启动和关闭的开销。但它对不同MR job中的task无效。

11.各种模式

11.1 并行执行

Hive会将一个查询转化成一个或者多个阶段。可以是MR阶段、抽样阶段、合并阶段、limit阶段、或者Hive执行过程中可能需要的其他阶段。默认情况下一次只会执行一个阶段，但不乏有相互不怎么依赖，可以并行执行的阶段，这个时候可以通过参数调整。在数据量大，子查询较多的时候使用,否则没有必要。

  • set hive.exec.parallel=false; 默认false，需要可调整为true
  • set hive.exec.parallel.thread.number=8;同一个sql允许最大并行度，默认为8

11.2 严格模式

主要是防止一些危险操作

  • set hive.strict.checks.no.partition.filter=false; ，限制分区表所有分区扫描,修改后必须在where中加上分区限制，否则不执行
  • set hive.strict.checks.orderby.no.limit=false; order by后面必须要加limit限制，否则不允许执行
  • set hive.strict.checks.cartesian.product=false ; 限制笛卡尔积的查询

11.3 本地执行

Hive也可以不将任务提交到集群进行运算，而是直接在一台节点上处理。因为消除了提交到集群的overhead，所以比较适合数据量很小，且逻辑不复杂的任务。但需要做参数调整。

  • hive.exec.mode.local.auto=false; 默认false，关闭状态。需要可调整为true
  • hive.exec.mode.local.auto.inputbytes.max=134217728; 默认128M，任务的输入数据总量必须小于该值，走本地模式
  • hive.exec.mode.local.auto.tasks.max; 默认没有设置，最大输入文件个数，当输入文件个数小于这个值时采用本地模式

11.4Fetch抓取

Hive中对某些情况的查询可以不必使用MapReduce计算,例如：SELECT * FROM emp;在这种情况下，Hive可以简单地读取emp对应的存储目录下的文件，然后输出查询结果到控制台。 参数hive.fetch.task.conversion=more ,表示：执行（无论是否有分区表）全局查找（select * ）、字段查找、limit都不走mapreduce。 此外，该参数其他值：

  • none：执行任何查询都走mapreduce；
  • minimal：执行（单个分区表）全局查找（select * ）、字段查找、limit不走mapreduce，不加分区字段执行会走MR

参考：blog.csdn.net/qq_32727095…

本文主要参考：

avatar
文章
阅读
粉丝