Inserting system timestamp into a timestamp field in hive table

Question

I m using Hive 0.8.0 version. I wanted to insert the system timestamp into a timestamp field while loading data into a hive table. In Detail: I have a file with 2 fields like below:

id name
1  John
2  Merry
3  Sam

Now i wanted to load this file on hive table along with the extra column "created_date". So i have created hive table with the extra filed like below:

CREATE table mytable(id int,name string, created_date timestamp) row format delimited fields terminated by ',' stored as textfile;

If i load the data file i used the below query:

LOAD DATA INPATH '/user/user/data/' INTO TABLE mytable;

If i run the above query the "created_date" field will be NULL. But i wanted that field should be inserted with the system timestamp instead of null while loading the data into hive table. Is it possible in hive. How can i do it?

Answer 1

You can do this in two steps. First load data from the file into a temporary table without the timestamp. Then insert from the temp table into the actual table, and generate the timestamp with the unix_timestamp() UDF:

create table temptable(id int, name string)
row format delimited fields terminated by ','
stored as textfile;

create table mytable(id int, name string, created_date timestamp)
row format delimited fields terminated by ','
stored as textfile;

load data inpath '/user/user/data/' into table temptable;

insert into table mytable
select id, name, unix_timestamp()
from temptable;