为什么此查询不会发生分区消除？

Question

我有一个配置单元表，该配置表按年，月，日和小时划分。我需要对其执行查询以获取最近7天的数据。这是在Hive 0.14.0.2.2.4.2-2。我的查询当前看起来像这样：

SELECT COUNT(column_name) from table_name 
where year >= year(date_sub(from_unixtime(unix_timestamp()), 7)) 
AND month >= month(date_sub(from_unixtime(unix_timestamp()), 7)) 
AND day >= day(date_sub(from_unixtime(unix_timestamp()), 7));

这需要很长时间。当我用上面的实际数字代替时，请说：

SELECT COUNT(column_name) from table_name 
where year >= 2017
AND month >= 2
AND day >= 13

它会在几分钟内完成。有什么办法可以更改上面的脚本，以便实际上只包含查询中的数字而不是函数？

我尝试使用set像：

set yearLimit = year(date_sub(from_unixtime(unix_timestamp()), 7));

SELECT COUNT(column_name) from table_name 
where year >= ${hiveconf:yearLimit}
AND month >= month(date_sub(from_unixtime(unix_timestamp()), 7)) 
AND day >= day(date_sub(from_unixtime(unix_timestamp()), 7));

但这不能解决问题。

Answer 1

解决方案

select      count (column_name)

from        table_name

where       year  >= year  (date_sub (current_date,7)) 
        and month >= month (date_sub (current_date,7)) 
        and day   >= day   (date_sub (current_date,7))
;

原始查询出了什么问题？

unix_timestamp（）

以秒为单位获取当前的Unix时间戳。此函数不是确定性的，其值在查询执行范围内也不是固定的，因此会阻止对查询的适当优化-自2.0版开始不推荐使用此函数，而推荐使用CURRENT_TIMESTAMP常量。

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

（我只是稍微更改了文档 :-) ）

由于unix_timestamp（）的值在执行期间可能会更改，因此应针对每一行对表达式进行求值，因此可以避免删除分区。

为什么使用SET没有用？

set只是一种文本替换机制。
期间没有任何计算set。
唯一发生的事情是为变量分配了 文本 。
在执行查询之前，变量占位符（${hiveconf:...}）被分配的 文本 替换。
只有这样，查询才会被解析和执行。

hive> set a=sele;
hive> set b=ct 1+;
hive> set c=1;
hive> ${hiveconf:a}${hiveconf:b}${hiveconf:c};
OK
2

演示版

create table table_name (column_name int) partitioned by (year int,month int,day int);

set hive.exec.dynamic.partition.mode=nonstrict;


insert into table_name partition (year,month,day)

select  pos
       ,year(dt)
       ,month(dt)
       ,day(dt)

from   (select  pe.pos
               ,date_sub (current_date,pe.pos) as dt

        from    (select 1) x 
                lateral view posexplode (split (space (99),' ')) pe
        ) t
;

explain dependency

select      count (column_name)

from        table_name

where       year  >= year  (date_sub (from_unixtime (unix_timestamp ()),7)) 
        and month >= month (date_sub (from_unixtime (unix_timestamp ()),7)) 
        and day   >= day   (date_sub (from_unixtime (unix_timestamp ()),7))
;

{“ input_partitions”：[{“ partitionName”：“ default @ table_name @ year = 2016
/ month = 11 / day = 14”}，{“ partitionName”：“ default @ table_name @ year =
2016 / month = 11 / day = 15“}，{” partitionName“：” default @ table_name @
year = 2016 / month = 11 / day = 16“}，{” partitionName“：” default @
table_name @ year = 2016 / month = 11 / day = 17“ }，{“ partitionName”：“
default @ table_name @ year = 2016 / month = 11 / day = 18”}}，{“
partitionName”：“ default @ table_name @ year = 2016 / month = 11 / day =
19”}， {“ partitionName”：“ default @ table_name @ year = 2016 / month = 11 /
day = 20”}，{“ partitionName”：“ default @ table_name @ year = 2016 / month =
11 / day = 21”}，{“ partitionName“：” default @ table_name @ year = 2016 /
month = 11 / day = 22“}，{” partitionName“：”default @ table_name @ year =
2016 / month = 11 / day = 23“}，{” partitionName“：” default @ table_name @
year = 2016 / month = 11 / day = 24“}，{” partitionName“：” default @
table_name @ year = 2016 / month = 11 / day = 25“}，{” partitionName“：”
default @ table_name @ year = 2016 / month = 11 / day = 26“}，{”
partitionName“：” default @ table_name @ year = 2016 / month = 11 / day =
27“}，{” partitionName“：” default @ table_name @ year = 2016 / month = 11 /
day = 28“}，{” partitionName“：” default @ table_name @ year = 2016 / month =
11 / day = 29“}，{” partitionName“：” default @ table_name @ year = 2016 /
month = 11 / day = 30“}，{” partitionName“：” default @ table_name @ year =
2016 / month = 12 / day = 1“}，{” partitionName“：” default @ table_name @
year = 2016 / month = 12 / day = 10“}，{” partitionName“：”default @
table_name @ year = 2016 / month = 12 / day = 11“}，{” partitionName“：”
default @ table_name @ year = 2016 / month = 12 / day = 12“}，{”
partitionName“：” default @ table_name @ year = 2016 / month = 12 / day =
13“}，{” partitionName“：” default @ table_name @ year = 2016 / month = 12 /
day = 14“}，{” partitionName“：” default @ table_name @ year = 2016 / month =
12 / day = 15“}，{” partitionName“：” default @ table_name @ year = 2016 /
month = 12 / day = 16“}，{” partitionName“：” default @ table_name @ year =
2016 / month = 12 / day = 17“}，{” partitionName“：” default @ table_name @
year = 2016 / month = 12 / day = 18“}，{” partitionName“：” default @
table_name @ year = 2016 / month = 12 / day = 19“}，{” partitionName“：”
default @ table_name @ year = 2016 / month = 12 / day = 2“}，{”
partitionName“：”default @ table_name @ year = 2016 / month = 12 / day =
20“}，{” partitionName“：” default @ table_name @ year = 2016 / month = 12 /
day = 21“}，{” partitionName“：” default @ table_name @ year = 2016 / month =
12 / day = 22“}，{” partitionName“：” default @ table_name @ year = 2016 /
month = 12 / day = 23“}，{” partitionName“：” default @ table_name @ year =
2016 / month = 12 / day = 24“}，{” partitionName“：” default @ table_name @
year = 2016 / month = 12 / day = 25“}，{” partitionName“：” default @
table_name @ year = 2016 / month = 12 / day = 26“}，{” partitionName“：”
default @ table_name @ year = 2016 / month = 12 / day = 27“}，{”
partitionName“：” default @ table_name @ year = 2016 / month = 12 / day =
28“}，{” partitionName“：” default @ table_name @ year = 2016 / month = 12 /
day = 29“}，{” partitionName“：”default @ table_name @ year = 2016 / month =
12 / day = 3“}，{” partitionName“：” default @ table_name @ year = 2016 /
month = 12 / day = 30“}，{” partitionName“：” default @ table_name @ year =
2016 / month = 12 / day = 31“}，{” partitionName“：” default @ table_name @
year = 2016 / month = 12 / day = 4“}，{” partitionName“：” default @
table_name @ year = 2016 / month = 12 / day = 5“}，{” partitionName“：”
default @ table_name @ year = 2016 / month = 12 / day = 6“}，{”
partitionName“：” default @ table_name @ year = 2016 / month = 12 / day =
7“}，{” partitionName“：” default @ table_name @ year = 2016 / month = 12 /
day = 8“}，{” partitionName“：” default @ table_name @ year = 2016 / month =
12 / day = 9“}，{” partitionName“：” default @ table_name @ year = 2017 /
month = 1 / day = 1“}，{” partitionName“：”default @ table_name @ year = 2017
/ month = 1 / day = 10“}，{” partitionName“：” default @ table_name @ year =
2017 / month = 1 / day = 11“}，{” partitionName“：” default @ table_name @
year = 2017 / month = 1 / day = 12“}，{” partitionName“：” default @
table_name @ year = 2017 / month = 1 / day = 13“}，{” partitionName“：”
default @ table_name @ year = 2017 / month = 1 / day = 14“}，{”
partitionName“：” default @ table_name @ year = 2017 / month = 1 / day =
15“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 1 /
day = 16“}，{” partitionName“：” default @ table_name @ year = 2017 / month =
1 / day = 17“}，{” partitionName“：” default @ table_name @ year = 2017 /
month = 1 / day = 18“}，{” partitionName“：” default @ table_name @ year =
2017 / month = 1 / day = 19“}，{” partitionName“：”default @ table_name @ year
= 2017 / month = 1 / day = 2“}，{” partitionName“：” default @ table_name @
year = 2017 / month = 1 / day = 20“}，{” partitionName“：” default @
table_name @ year = 2017 / month = 1 / day = 21“}，{” partitionName“：”
default @ table_name @ year = 2017 / month = 1 / day = 22“}，{”
partitionName“：” default @ table_name @ year = 2017 / month = 1 / day =
23“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 1 /
day = 24“}，{” partitionName“：” default @ table_name @ year = 2017 / month =
1 / day = 25“}，{” partitionName“：” default @ table_name @ year = 2017 /
month = 1 / day = 26“}，{” partitionName“：” default @ table_name @ year =
2017 / month = 1 / day = 27“}，{” partitionName“：” default @ table_name @
year = 2017 / month = 1 / day = 28“}，{” partitionName“：”default @ table_name
@ year = 2017 / month = 1 / day = 29“}，{” partitionName“：” default @
table_name @ year = 2017 / month = 1 / day = 3“}，{” partitionName“：” default
@ table_name @ year = 2017 / month = 1 / day = 30“}，{” partitionName“：”
default @ table_name @ year = 2017 / month = 1 / day = 31“}，{”
partitionName“：” default @ table_name @ year = 2017 / month = 1 / day =
4“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 1 / day
= 5“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 1 /
day = 6“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 1
/ day = 7“}，{” partitionName“：” default @ table_name @ year = 2017 / month =
1 / day = 8“}，{” partitionName“：” default @ table_name @ year = 2017 / month
= 1 / day = 9“}，{” partitionName“：”default @ table_name @ year = 2017 /
month = 2 / day = 1“}，{” partitionName“：” default @ table_name @ year = 2017
/ month = 2 / day = 10“}，{” partitionName“：” default @ table_name @ year =
2017 / month = 2 / day = 11“}，{” partitionName“：” default @ table_name @
year = 2017 / month = 2 / day = 12“}，{” partitionName“：” default @
table_name @ year = 2017 / month = 2 / day = 13“}，{” partitionName“：”
default @ table_name @ year = 2017 / month = 2 / day = 14“}，{”
partitionName“：” default @ table_name @ year = 2017 / month = 2 / day =
15“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 2 /
day = 16“}，{” partitionName“：” default @ table_name @ year = 2017 / month =
2 / day = 17“}，{” partitionName“：” default @ table_name @ year = 2017 /
month = 2 / day = 18“}，{” partitionName“：”default @ table_name @ year = 2017
/ month = 2 / day = 19“}，{” partitionName“：” default @ table_name @ year =
2017 / month = 2 / day = 2“}，{” partitionName“：” default @ table_name @ year
= 2017 / month = 2 / day = 20“}，{” partitionName“：” default @ table_name @
year = 2017 / month = 2 / day = 21“}，{” partitionName“：” default @
table_name @ year = 2017 / month = 2 / day = 3“}，{” partitionName“：” default
@ table_name @ year = 2017 / month = 2 / day = 4“}，{” partitionName“：”
default @ table_name @ year = 2017 / month = 2 / day = 5“}，{”
partitionName“：” default @ table_name @ year = 2017 / month = 2 / day =
6“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 2 / day
= 7“}，{” partitionName“：” default @ table_name @ year = 2017 / month = 2 /
day = 8“}，{” partitionName“：”default @ table_name @ year = 2017 / month = 2
/ day = 9“}]，” input_tables“：[{” tablename“：” default @ table_name“，”
tabletype“：” MANAGED_TABLE“}]}}

explain dependency

select      count (column_name)

from        table_name

where       year  >= year  (date_sub (current_date,7)) 
        and month >= month (date_sub (current_date,7)) 
        and day   >= day   (date_sub (current_date,7))
;

{“ input_partitions”：[{“ partitionName”：“ default @ table_name @ year = 2017
/ month = 2 / day = 14”}，{“ partitionName”：“ default @ table_name @ year =
2017 / month = 2 / day = 15“}，{” partitionName“：” default @ table_name @
year = 2017 / month = 2 / day = 16“}，{” partitionName“：” default @
table_name @ year = 2017 / month = 2 / day = 17“ }，{“ partitionName”：“
default @ table_name @ year = 2017 / month = 2 / day = 18”}，{“
partitionName”：“ default @ table_name @ year = 2017 / month = 2 / day =
19”}， {“ partitionName”：“ default @ table_name @ year = 2017 / month = 2 /
day = 20”}，{“ partitionName”：“ default @ table_name @ year = 2017 / month =
2 / day = 21”}]，“ input_tables“：[{” tablename“：” default @ table_name“，”
tabletype“：” MANAGED_TABLE“}]}