Search code examples
hadoophivehiveqludf

Set Hive variable with the output of a UDF

I'm trying to set a Hive variable with the output of a UDF function, so I can use that value in a INSERT INTO myTable later in my .hql script.

This is the DDL of myTable:

CREATE TABLE myTable(
CreationTimestamp TIMESTAMP,
Tablename CHAR(50),
LastExtractedTimestamp TIMESTAMP,
OozieJobID CHAR(40) 
);

The following is not working:

set hiveconf:ct=select current_timestamp;   
INSERT INTO mytable VALUES ('${hiveconf:ct}','test','2015-12-11 11:25:03.341','testID');

and also this one is not working (without quotes):

set hiveconf:ct=select current_timestamp;   
INSERT INTO myTable VALUES (${hiveconf:ct}, 'test','2015-12-11 11:25:03.341','testID');

The result is that I get inserted a row in my table with a null value in place of my variable's value:

null test 2015-12-11 11:25:03.341 testID

So right now I'm using the following workaround:

INSERT INTO myTable select * from (select current_timestamp, 'test','2015-12-11 11:25:03.341','testID') as dummy;

Do you have any suggestion or better way to achieve this?

Thanks ;-)

Solution

  • That is not possible. Why ? Hive variables are inserted in the query when you submit the query and the query it is parsed, so before the UDF has even a chance to run at all. Consider using something like oozie so you can actually build a modular workflow.