优化配置单元查询.java.lang.OutOfMemoryError:超出了Java堆空间/GC开销限制 [英] Optimize Hive Query. java.lang.OutOfMemoryError: Java heap space/GC overhead limit exceeded

问题描述

由于不断遇到此OOM错误，如何优化这种形式的查询?还是想出一个更好的执行计划?如果我删除了substring子句，查询将正常工作，表明这会占用大量内存.

How can I optimize a query of this form since I keep running into this OOM error? Or come up with a better execution plan? If I removed the substring clause, the query would work fine, suggesting that this takes a lot of memory.

当作业失败时，直线输出将显示OOM Java堆空间.在线阅读建议我增加 export HADOOP_HEAPSIZE ，但这仍然会导致错误.我尝试的另一件事是增加 hive.tez.container.size 和 hive.tez.java.opts (tez堆)，但是仍然出现此错误.在YARN日志中，将超出GC开销限制，这表明内存不足和/或查询计划的效率极低，因为它无法收集回足够的内存.

When the job fails, the beeline output shows the OOM Java heap space. Readings online suggested that I increase export HADOOP_HEAPSIZE but this still results in the error. Another thing I tried was increasing the hive.tez.container.size and hive.tez.java.opts (tez heap), but still has this error. In the YARN logs, there would be GC overhead limit exceeded, suggesting a combination of not enough memory and/or the query plan is extremely inefficient since it can't collect back enough memory.

我正在使用Azure HDInsight Interactive Query 4.0.20个工作节点，D13v2 8内核和56GB RAM.

I am using Azure HDInsight Interactive Query 4.0. 20 worker node, D13v2 8 core, and 56GB RAM.

create external table database.sourcetable(
  a,
  b,
  c,
  ...
  (183 total columns)
  ...
)
PARTITIONED BY ( 
  W string, 
  X int, 
  Y string, 
  Z int
)

目标表

create external table database.NEWTABLE(
  a,
  b,
  c,
  ...
  (187 total columns)
  ...
  W,
  X,
  Y,
  Z
)
PARTITIONED BY (
  aAAA,
  bBBB
)

查询

insert overwrite table database.NEWTABLE partition(aAAA, bBBB, cCCC)
select
a,
b,
c,
...
(187 total columns)
...
W,
X,
Y,
Z,
cast(a as string) as aAAA, 
from_unixtime(unix_timestamp(b,'yyMMdd'),'yyyyMMdd') as bBBB,
substring(upper(c),1,2) as cCCC
from database.sourcetable

推荐答案

如果其他方法都没问题，请尝试在查询末尾添加按分区密钥进行分发:

If everything else is okay, try to add distribute by partiton key at the end of your query:

  from database.sourcetable 
  distribute by aAAA, bBBB, cCCC

结果，每个reducer只会创建一个分区文件，消耗更少的内存

As a result each reducer will create only one partition file, consuming less memory

这篇关于优化配置单元查询.java.lang.OutOfMemoryError:超出了Java堆空间/GC开销限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！