CODEWITHSUNDEEP
hiveunix-timestampcreate-table
shree11
shree11

Reputation: 535

Inserting system timestamp into a timestamp field in hive table

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?

Upvotes: 2

Views: 13341

Answers (1)

Joe K
Joe K

Reputation: 18424

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;

Upvotes: 3

Related Questions