无法在以后的set语句中引用Hive日期变量 [英] Not able to reference Hive date variable in later set statements

问题描述

我正在尝试将日期存储到蜂巢变量中，但以后无法使用.

I am trying to store a date into hive variable but not able to use it later.

hive> select to_date(date_sub(last_day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),1));
OK
2019-07-30

set my_date=select to_date(date_sub(last_day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),1));

现在我想从my_date中提取年份和月份，但是它不起作用.

now I want to extract year and month out of my_date but it's not working.

hive> select date_format('${hiveconf:my_date}','yyyy-MM');
FAILED: ParseException line 1:85 missing ) at 'yyyy' near 'yyyy'
line 1:89 missing EOF at '-' near 'yyyy'

下面的语句有效:

hive> select date_format('2019-07-30','yyyy-MM');
OK
2019-07

不确定发生了什么.如何引用my_date变量并存储Year&蜂巢中另一组变量中的月份.

Not sure what's happening. How can I refer to my_date variable and store year & month in another set of variable in hive.

set year_month_date = select date_format('${hiveconf:my_date}','yyyy-MM');

推荐答案

配置单元替换是简单的文本替换.该语句:

Hive variable substitution is simple text replacement. This statement:

set my_date = select to_date(date_sub(last_day(FROM_UNIXTIME(UNIX_TIMESTAMP()，'yyyy-MM-dd'))，1)); 将分配字符串'select to_date(date_sub(last_day(FROM_UNIXTIME(UNIX_TIMESTAMP()，'yyyy-MM-dd'))，1))'到变量 my_date .不幸的是，Hive不会计算变量.您的最终声明将被解析为

set my_date=select to_date(date_sub(last_day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),1)); will assign string 'select to_date(date_sub(last_day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),1))' to the variable my_date. Hive does not calculate variables unfortunately. And your final statement will be resolved as

select date_format('select to_date(date_sub(last_day(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')),1))','yyyy-MM');

这是不正确的选择语句.

And this is incorrect select statement.

您可以在单独的脚本中计算变量，然后使用shell将其传递给另一个脚本，例如此答案.另请参见 https://stackoverflow.com/a/56450129/2700344

You can calculate the variable in the separate script and pass it to another script using shell, like in this answer. See also https://stackoverflow.com/a/56450129/2700344

您可以使用shell echo命令在Hive脚本中打印变量:

You can print variable inside the Hive script using shell echo command:

! echo my_date contains '${hiveconf:my_date}';

也不要使用FROM_UNIXTIME(UNIX_TIMESTAMP()，'yyyy-MM-dd').使用current_date()代替，有关更多详细信息，请参见以下答案: https://stackoverflow.com/a/41140298/2700344 .

Also do not use FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'). Use current_date() instead, see this answer for more details: https://stackoverflow.com/a/41140298/2700344.

这篇关于无法在以后的set语句中引用Hive日期变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！