1

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));

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'

below statement is working:

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

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');
leftjoin
  • 36,950
  • 8
  • 57
  • 116
vikrant rana
  • 4,509
  • 6
  • 32
  • 72
  • 1
    Unfortunately, Hive variables substitution does not work in such way. It is simple substitution without calculation. – leftjoin Jul 27 '19 at 15:20

1 Answers1

3

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)); 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.

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

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

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

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.

leftjoin
  • 36,950
  • 8
  • 57
  • 116