Multiple Delimiter file is not loading properly into hive

#1

Hi,
I have data in this below format in HDFS
2016-09-03 19:13:44 : [pb47209] : bdgtr004a : 1970 : OPEN
2016-09-03 19:15:52 : [pb47209] : bdgtr004b : 1970 : CLOSE : pb47209 0.0 1.0 1970 00:00:01 02:09
2016-09-03 19:17:09 : [pb47209] : bdgtr004c : 3757 : OPEN
2016-09-03 19:18:16 : [pb47209] : bdgtr004d : 3757 : CLOSE : pb47209 0.0 1.7 3757 00:00:01 01:08

I want to load this file into hive(external table) I tried with MultiDelimtSerde but i am unable to load it properly.
I tried below command
CREATE EXTERNAL TABLE L1_REVO_RAW_DATA (
LOG_TIME TIMESTAMP,LOGIN_USER_ID STRING,HOST_NAME STRING,LOGIN_SESSION_ID INT,SESSION_STATUS STRING,EXIT_USER_ID STRING,MAX_MEM_USED DOUBLE,CPU_UTILIZATION DOUBLE,EXIT_SESSION_ID INT,CPU_TIME STRING,ELAPSED_SESSION_TIME STRING)
ROW FORMAT SERDE ‘org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe’ WITH SERDEPROPERTIES (“field.delim”=": ") STORED AS TEXTFILE LOCATION ‘/input/sample/’;

Please help me to fix this issue.

0 Likes