Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

how to split date and time combination of field using Hive ?

Highlighted

how to split date and time combination of field using Hive ?

Contributor

Hi Friendz,

 

I have query in splitting the date and time field in hive table.
My data is there in flat file in the below format :

ID Price Start_DTTM DEL_DT_TM
101 200 01MAR2016:11:31:52 01MAR2016:11:59:59
102 300 04MAR2016:06:13:08 04MAR2016:09:19:07
104 500 03MAR2016:11:54:56 03MAR2016:15:56:34
105 800 03MAR2016:09:10:37 03MAR2016:09:52:03

I have created a table as below :
CREATE TABLE abc (ID int, Price double, Start_DTTM string, DEL_DT_TM string)
row format delimited fields terminated by ',' stored as textfile;

And loaded data into the table.

Now i want to spit the Date and time separately in different fields.


Please guide me

Thanks,
Syam.

1 REPLY 1

Re: how to split date and time combination of field using Hive ?

Guru
Hi,

Since the datetime string is in not standard format, I am afraid that you might have to use regexp_extract function to extra info and then re-built them into Date and Time separately.