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 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));
但这并没有解决问题.
解决方法
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()
Gets current Unix timestamp in seconds. This function is not
deterministic and its value is not fixed for the scope of a query
execution,therefore prevents proper optimization of queries – this
has been deprecated since 2.0 in favour of CURRENT_TIMESTAMP constant.07000
(我刚刚更改了文档:-))
由于unix_timestamp()值可能在执行期间发生更改,因此应为每行计算表达式,从而防止分区消除.
为什么使用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)) ;
{“input_partitions”:[{“partitionName”:”default@table_name@year=2017/month=2/day=14″},{“partitionName”:”default@table_name@year=2017/month=2/day=21″}],”tabletype”:”MANAGED_TABLE”}]}
