Import data in Hive with an arbitrary type of time

Question

I have a data file that I want to import into Hive that contains timestamps. Timestamps are in the format MM / dd / yyyy HH: mm: ss.

I would like to create a table containing the timestamp type to store this value, however, I cannot figure out how to directly import the data.

My workaround is to import the data into a temporary table with my date as a string, and then read the data from this temporary table into my permanent table, doing the conversion of the time format on the fly.

So, my whole two-step boot function looks something like this:

create table tempTable(
timeField string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

--load data local here!

create table finalTable(
timeField timestamp
) stored as RCFILE;

insert into table finalTable select 
from_unixtime( unix_timestamp(timeField,'MM/dd/yyyy HH:mm') )
from tempTable;

So finally my question is :-) Is this the “right” or “best” way to do this? Am I using an ineffective / stupid workaround?

thanks!

Answer 1

- yyyy/MM/dd HH: mm: ss

timestamp hive.

.

Answer 2

( ) ROW FORMAT DELIMITED FIELDS TERMINATED by ",";

04/05/2014 04:25:55 .