Hive table with MultiDelimitSerDe format fails


#1

Hi Team,

While querying the HIVE tables formatted by “MultiDelimitSerDe” a run time exception is received.

After browsing the other forums came to know the following link
https://www.ericlin.me/2016/01/how-to-query-a-multiple-delimited-table-in-hive/

Is there a way to fix this?

Attached the log:

hive> select M.title,U.userid from movies M
> join ratings R on (M.movieid = R.movieid)
> join users U on (U.userid=R.userid);
Query ID = santhoshkumarthirumalai3040_20191002181330_75a2ebe3-e039-4623-9843-bc5031600587
Total jobs = 1
Execution log at: /tmp/santhoshkumarthirumalai3040/santhoshkumarthirumalai3040_20191002181330_75a2ebe3-e039-4623-9843-bc5031600587.log
2019-10-02 18:13:36 Starting to launch local task to process map join; maximum memory = 523239424
2019-10-02 18:13:38 Dump the side-table for tag: 1 with group count: 6040 into file: file:/tmp/santhoshkumarthirumalai3040/a7d4eaaa-8900-48b0-b2fa-fc57af194d8d
/hive_2019-10-02_18-13-30_979_3918950595009008769-1/-local-10005/HashTable-Stage-5/MapJoin-mapfile01–.hashtable
2019-10-02 18:13:38 Uploaded 1 File to: file:/tmp/santhoshkumarthirumalai3040/a7d4eaaa-8900-48b0-b2fa-fc57af194d8d/hive_2019-10-02_18-13-30_979_391895059500900
8769-1/-local-10005/HashTable-Stage-5/MapJoin-mapfile01–.hashtable (121266 bytes)
2019-10-02 18:13:38 Dump the side-table for tag: 0 with group count: 3883 into file: file:/tmp/santhoshkumarthirumalai3040/a7d4eaaa-8900-48b0-b2fa-fc57af194d8d
/hive_2019-10-02_18-13-30_979_3918950595009008769-1/-local-10005/HashTable-Stage-5/MapJoin-mapfile10–.hashtable
2019-10-02 18:13:38 Uploaded 1 File to: file:/tmp/santhoshkumarthirumalai3040/a7d4eaaa-8900-48b0-b2fa-fc57af194d8d/hive_2019-10-02_18-13-30_979_391895059500900
8769-1/-local-10005/HashTable-Stage-5/MapJoin-mapfile10–.hashtable (180283 bytes)
2019-10-02 18:13:38 End of local task; Time Taken: 1.651 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there’s no reduce operator
Starting Job = job_1568355188440_3208, Tracking URL = http://cxln2.c.thelab-240901.internal:8088/proxy/application_1568355188440_3208/
Kill Command = /usr/hdp/2.6.2.0-205/hadoop/bin/hadoop job -kill job_1568355188440_3208
Hadoop job information for Stage-5: number of mappers: 1; number of reducers: 0
2019-10-02 18:13:50,903 Stage-5 map = 0%, reduce = 0%
2019-10-02 18:14:20,199 Stage-5 map = 100%, reduce = 0%
Ended Job = job_1568355188440_3208 with errors
Error during job, obtaining debugging information…
Examining task ID: task_1568355188440_3208_m_000000 (and more) from job job_1568355188440_3208
Task with the most failures(4):

Task ID:
task_1568355188440_3208_m_000000
at org.apache.hadoop.mapred.MapRunner.configure(MapRunner.java:38)

URL:
http://cxln2.c.thelab-240901.internal:8088/taskdetails.jsp?jobid=job_1568355188440_3208&tipid=task_1568355188440_3208_m_000000

Diagnostic Messages for this Task:
Error: java.lang.RuntimeException: Error in configuring object
at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:112)
at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:78)
at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136)
at org.apache.hadoop.mapred.MapTask.runOldMapper(MapTask.java:449)
at org.apache.hadoop.mapred.MapTask.run(MapTask.java:343)
at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:170)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1866)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:164)
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:109)
… 9 more
Caused by: java.lang.RuntimeException: Error in configuring object
at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:112)
at org.apache.hadoop.util.ReflectionUtils.setConf(ReflectionUtils.java:78)
at org.apache.hadoop.util.ReflectionUtils.newInstance(ReflectionUtils.java:136)
at org.apache.hadoop.mapred.MapRunner.configure(MapRunner.java:38)
… 14 more
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.ReflectionUtils.setJobConf(ReflectionUtils.java:109)
… 17 more
Caused by: java.lang.RuntimeException: Map operator initialization failed
at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.configure(ExecMapper.java:140)
… 22 more
Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.ClassNotFoundException: Class org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe not found
at org.apache.hadoop.hive.ql.exec.MapOperator.getConvertedOI(MapOperator.java:350)
at org.apache.hadoop.hive.ql.exec.MapOperator.setChildren(MapOperator.java:385)
at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.configure(ExecMapper.java:109)
… 22 more
Caused by: java.lang.ClassNotFoundException: Class org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe not found**
at org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:2147)
at org.apache.hadoop.hive.ql.plan.PartitionDesc.getDeserializer(PartitionDesc.java:143)
at org.apache.hadoop.hive.ql.exec.MapOperator.getConvertedOI(MapOperator.java:316)
… 24 more

FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
MapReduce Jobs Launched:
Stage-Stage-5: Map: 1 HDFS Read: 0 HDFS Write: 0 FAIL
Total MapReduce CPU Time Spent: 0 msec



#2

Hi, Santhosh
Can you try with this format to with “MultiDelimitSerDe” it is just having multiple delimiter and the package must install from “org.apache.hadoop.hive.serde2.MultiDelimitSerDe”.

CREATE TABLE test (
id string,
hivearray array,
hivemap map<string,int>)
ROW FORMAT SERDE ‘org.apache.hadoop.hive.serde2.MultiDelimitSerDe’
WITH SERDEPROPERTIES (“field.delim”="[,]",“collection.delim”=":",“mapkey.delim”="@");