Hive连接查询优化

Question

Hive连接查询优化

optimizationhivebigdataquery-optimizationhiveql

3

Table A
---------
col1, col2,Adate,qty

Table B
-------
col2,cost,Bdate

表格大小如下：

A: 100万 B: 70万

考虑此查询：

SELECT 
  A.col1,
  A.col2,
  B.Bdate bdate,
  SUM(qty)*COLLECT_LIST(cost)[0] price 
FROM A 
JOIN B 
ON (A.col2 = B.col2 AND A.Adate <= B.Bdate) 
GROUP BY 
  A.col1,
  A.col2,
  B.bdate;

上述Hive查询在一个由4个从节点(8GB内存，100GB硬盘)和1个主节点(16GB内存，100GB硬盘)组成的集群上需要超过3个小时的时间。

这个查询能否被优化？如果可以，哪里可以进行优化？

- user1808266

请添加执行计划。 - leftjoin

2个回答

1

使用Tez和mapjoin。

set hive.auto.convert.join=true; --this enables map-join
set hive.mapjoin.smalltable.filesize=25000000; --adjust for your smaller table to fit in memory
set hive.execution.engine=tez;

此计算不是内存高效的：

SUM(qty)*COLLECT_LIST(cost)[0] price

COLLECT_LIST会将组中的所有成本值收集到非唯一(包含来自组中所有行的值)和无序(是的，无序，因为在collect_list之前没有任何分发+排序)数组中。这个数组可能足够大(元素数量=组中的行数)，取[0]元素，意味着你从组中随便挑选一个成本。收集数组以获取随机元素有意义吗？使用min()或max()代替。如果不关心应该取哪个cost，那么min(cost)或max(cost)或其他一些标量函数将消耗更少的内存。您可以使用first_value分析函数(可能需要子查询，但也将具有内存效率)。

- leftjoin

Hive执行引擎是TEZ。此外，我从选择子句中删除了SUM(qty)*COLLECT_LIST(cost)[0]价格。 - user1808266

@user1808266 如果您的数据中包含col2、date重复的情况，请在连接之前进行聚合。 - leftjoin

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Chema · Accepted Answer

我将尝试给您一些关于如何提高在Hive中查询性能的建议。

检查您正在使用的执行引擎

set hive.execution.engine;

如果您的执行引擎是 mr，而不是 MapReduce，则可以使用 Apache Spark 或 Apache Tez，它们都比 MapReduce 更快。

set hive.execution.engine=tez;

联接查询计算成本高，特别是当您联接三个或更多表时，或者当您使用非常大的数据时。

可以用一种策略来解决这个问题，即预先联接数据并将预联接结果存储在单独的表中，然后您可以进行查询。这是一种将规范化数据库去规范化的方法，使其更容易运行分析查询。预联接表的这种方法有一些成本，但可以使分析查询更容易编写和更快速运行。

还有一些其他技术可以提高Hive查询性能

联接表排序（最大的表放在最后）

与任何类型的调优一样，了解系统的内部工作原理非常重要。当Hive执行联接时，它需要选择哪个表进行流式传输，哪个表进行缓存。 Hive会将联接语句中的最后一个表用于流式传输，因此我们需要确保该流式传输表在两个表中最大。

A：100万 B：70万

因此，在这两个表联接时，重要的是较大的表出现在查询的最后。

Bucketing将数据存储在单独的文件中，而不是像分区一样存储在单独的子目录中。

它以有效随机的方式分割数据，而不是像分区那样可预测的方式。当记录插入到分桶表中时，Hive会计算指定分桶列中值的哈希码，并使用这些哈希码将记录分成桶。因此，有时将分桶称为哈希分区。分桶的目标是将记录均匀分布在预定义数量的桶中。如果所有连接的表都在连接键列上进行了分桶，则分桶可以提高连接的性能。

有关更多关于分桶的信息，请参见描述带有桶的表的Hive语言手册页面，

BucketedTables

bucketing-in-hive

分区

分区是一种根据特定列（如日期、城市和部门）的值将表分成相关部分的方法。

Hive 中的每个表都可以有一个或多个分区键来标识特定的分区。使用分区，可以轻松地查询数据的片段。

Apache Hive 分区