Hive Variable Substitution with function

Question

I am trying to to something like the following in Hive

set TODAY="2013-11-04"; //this works

SET TODAY = to_date(from_unixtime(unix_timestamp())); //this does not..

set TODAY; TODAY=to_date(from_unixtime(unix_timestamp()))

Any suggestions?

Answer 1

SET TODAY = to_date(from_unixtime(unix_timestamp()));

This is not available in Hive. What you can do is

select  concat ('set TODAY=',to_date(from_unixtime(unix_timestamp())),'\;') from testTableName limit 1 >>/path/to/HQL/file/fileName.sql

Now in the file fileName.sql you will see

set TODAY=2013-11-05;

The file fileName.sql need to be loaded before running the other dependent queries. That you can do by this:

hive -f hiveQueries.sql

You file hiveQueries.sql should contain something like this:

    use testDB;
    source /path/to/HQL/file/fileName.sql;
    select * from testTable where someColumn=${hiveconf:TODAY};

Hope this helps..:)