在Where条件中使用变量时，Hive查询运行速度非常缓慢 [英] Hive query runs very slowly when using variables in Where condition

问题描述

这是我的配置单元查询：

This is my hive query :

set s=date_add(from_unixtime(UNIX_TIMESTAMP()),-200);
select * from a where dateString>=${hiveconf:s}

慢。出于某种原因，这种运行速度要快得多：

which runs very slowly. For some reason this runs much faster:

select * from a where dateString>='2015-01-01'

所以对于相同的日期值，第一个值要慢得多。为什么是这样？任何想法？

So with same date values the first one is much slower. Why is this? Any ideas?

推荐答案

指定分区时，不会发生动态分区修剪（除非使用Hive> = 0.14）。在where子句中指定精确值时，hive知道要扫描哪个分区。当从配置变量或另一个查询的结果中指定时，配置单元不知道要扫描的分区。
我建议使用Hive> = 0.14或通过用shell脚本替代hive conf来指定分区的确切值。

When specifying partitioning like that, dynamic partition pruning doesn't occur (unless using Hive >= 0.14). When specifying exact values in where clause, hive knows which partitions to scan. When specifying from config variables or from the result of another query, hive doesn't know which partitions to scan. I would suggest using Hive >= 0.14 or specifying the exact values of partitions by substituting variables by shell scripts instead of hive conf.

这篇关于在Where条件中使用变量时，Hive查询运行速度非常缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！