我有一个配置单元表，该配置表按年，月，日和小时划分。我需要对其执行查询以获取最近7天的数据。这是在Hive 0.14.0.2.2.4.2-2。我的查询当前看起来像这样：
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
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));
但这不能解决问题。
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（）
以秒为单位获取当前的Unix时间戳。此函数不是确定性的，其值在查询执行范围内也不是固定的，因此会阻止对查询的适当优化-自2.0版开始不推荐使用此函数，而推荐使用CURRENT_TIMESTAMP常量。
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
（我只是稍微更改了文档 :-) ）
由于unix_timestamp（）的值在执行期间可能会更改，因此应针对每一行对表达式进行求值，因此可以避免删除分区。
SET
set只是一种文本替换机制。 期间没有任何计算set。 唯一发生的事情是为变量分配了 文本 。 在执行查询之前，变量占位符（${hiveconf:...}）被分配的 文本 替换。 只有这样，查询才会被解析和执行。
${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“}]}
