-1

i trying to run this code for so long time can somebody tell me what is problem in it code :- 

CREATE EXTERNAL TABLE samp_log 
(
ip String ,col1 String ,col2 String , date String , time_hour int ,time_min int 
,time_sec int ,zone int , request String , request_con String , resp_code int 
,resp_byte BIGINT , reference String , ext_reference String , col13 String 
,col14 String ,col15 String , col16 String ,col17 String  
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' 
WITH SERDEPROPERTIES ("field.delim"=" ,[,]") 
STORED AS TEXTFILE

error - Driver returned: 1. Errors: OK FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Cannot validate serde: org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe

i also added jar file of hive-contrib..

dtolnay
  • 9,621
  • 5
  • 41
  • 62
Avinash Jadhav
  • 53
  • 1
  • 1
  • 6

1 Answers1

0

use RegexSerDe

https://cwiki.apache.org/confluence/display/Hive/GettingStarted#GettingStarted-ApacheWeblogData

Here is a POC:

create external table mytable  
( 
    ip      string
   ,dt      string
   ,tm      string
   ,tz      string
)
    row format serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe'
    with serdeproperties  
    (
        'input.regex' = '^(.*?) - - \\[(.*?):(.*?) (.*?)\\].*$'
    )
    location '/tmp/mytable'
;

select * from mytable
;

+-----------------+-------------+------------+------------+
|   mytable.ip    | mytable.dt  | mytable.tm | mytable.tz |
+-----------------+-------------+------------+------------+
| 123.123.123.123 | 26/Apr/2000 | 00:23:48   |      -0400 |
+-----------------+-------------+------------+------------+
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
  • Hey After running this above commnad it gives output as you shown but it giving error for other command like Select ip,dt from mytable etc.. – Avinash Jadhav Mar 09 '17 at 08:07
  • FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask MapReduce Jobs Launched: – Avinash Jadhav Mar 09 '17 at 17:58
  • You'll have to check the logs for more informative error message – David דודו Markovitz Mar 09 '17 at 18:01
  • in logs i can't able to understand much it also giving eroor like ( ERROR ql.Driver: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask ) i was thinking may be it will happen by using regex code . It don't give these error if i use basic delimiter command – Avinash Jadhav Mar 09 '17 at 18:22
  • P.s. This doesn't seem related to the original post. Do other queries on different tables works? – David דודו Markovitz Mar 09 '17 at 18:41
  • Yes it works on different table . Please can you try it to run on your pc because i can't able to find what's wrong in it? USE somemore data 123.123.123.123 - - [26/Apr/2000:00:23:48 -0400] "GET /pics/5star2000.gif HTTP/1.0" 200 4005 "http://www.jafsoft.com/asctortf/" "Mozilla/4.05 (Macintosh; I; PPC)" 123.123.123.123 - - [26/Apr/2000:00:23:50 -0400] "GET /pics/5star.gif HTTP/1.0" 200 1031 "http://www.acadgild.com/" "Mozilla/4.05 (Macintosh; I; PPC)" 123.123.123.123 - - [26/Apr/2000:00:23:51 -0400] "GET /pics/a2hlogo.jpg HTTP/1.0" 200 4282 "http://www.acadgild.com/" – Avinash Jadhav Mar 09 '17 at 18:50
  • Tested, no issues on my side. – David דודו Markovitz Mar 09 '17 at 19:35
  • I wonder why you have to add it in the first place and in any case, this does not seem to be the right place. Please read https://issues.apache.org/jira/browse/HIVE-1719 – David דודו Markovitz Mar 11 '17 at 07:27