0

I have partitioned table in hive and I want to assign value for date column dynamically( yesterday's date ). Below is my current query but it's not working.

ALTER TABLE db1.table1 ADD IF NOT EXISTS PARTITION (loaddate="date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1)") LOCATION "hdfs://location1/abc/rawdata/externalhivetables/downloading/data";

Instead of returning the date value it's returning me the complete expression.

select downloading.loaddate From downloading limit 3;

+------------------------------------------------------------+
 |              downloading.loaddate                       |
+------------------------------------------------------------+
| date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1) |
| date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1) |
| date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1) |
hampi2017
  • 701
  • 2
  • 13
  • 33

2 Answers2

0

In hive shell we cannot assign variable variables from the result of query yet, we need to have 2 steps:

Use Shell script to execute the query and store the result into a variable.

Then initialize the hive shell/script with the variable.

bash$ var=`hive -S -e "select date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd') , 1);"`
bash$ echo $var

Now initialize hive/beeline shell with the varvalue

bash$ hive -hiveconf dd=$var

hive> ALTER TABLE db1.table1 ADD IF NOT EXISTS PARTITION (loaddate='${hiveconf:dd}') LOCATION "hdfs://location1/abc/rawdata/externalhivetables/downloading/data";

Refer to this and this links for additional information.

notNull
  • 30,258
  • 4
  • 35
  • 50
0

Use shell to calculate date and substitute it using shell variable substitution:

bash$ dt=$(date -d '-1 day' +%Y-%m-%d)
bash$ hive -e "ALTER TABLE db1.table1 ADD IF NOT EXISTS PARTITION (loaddate='$dt') LOCATION 'hdfs://location1/abc/rawdata/externalhivetables/downloading/data'"
leftjoin
  • 36,950
  • 8
  • 57
  • 116