if you have a row like this,

100.00,23.02,10-feb-13 10:02:00, DAMNO

then you should use below code,

insert overwrite table tablename

select

regexp_extract(colvalue, ‘^(?:([^,]*)\,?){1}’, 1) column1

regexp_extract(colvalue, ‘^(?:([^,]*)\,?){2}’, 1) column2,

regexp_extract(colvalue, ‘^(?:([^,]*)\,?){3}’, 1) column3,

from_unixtime(unix_timestamp(regexp_extract(colvalue, ‘^(?:([^,]*)\,?){4}’, 1),’dd-MMM-yy HH:mm:ss’)) column4

from tablename2

Advertisements