Hive query runs very slowly when using variables i

Question

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?

Answer 1

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.