How to query a multiple delimited table in Hive

This article explains how to query a multi delimited Hive table in CDH5.3.x. Use case as follow: Having the following table definitions:

CREATE TABLE test_multi 
(a string, b string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe' 
WITH SERDEPROPERTIES (
    "field.delim"="#|",
    "collection.delim"=":",
    "mapkey.delim"="@"
);

Query all columns is OK:

select * from test_multi;
+---------------+---------------+
| test_multi.a  | test_multi.b  |
+---------------+---------------+
| eric          | test more     |
+---------------+---------------+
1 row selected (1.58 seconds)

However, query a single column will get the following error in HS2:

select a from test_multi;
Error: Error while processing statement: FAILED: Execution Error, return code 2 from 
org.apache.hadoop.hive.ql.exec.mr.MapRedTask (state=08S01,code=2)

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:334)
        at org.apache.hadoop.hive.ql.exec.MapOperator.setChildren(MapOperator.java:352)
        at org.apache.hadoop.hive.ql.exec.mr.ExecMapper.configure(ExecMapper.java:126)
        ... 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:1953)
        at org.apache.hadoop.hive.ql.exec.MapOperator.getConvertedOI(MapOperator.java:304)
        ... 24 more

This happens in CDh5.3.3, which ships Hive 0.13, and I am not sure whether it also applies to CDh5.4.x and CDh5.5.x. This is caused by class org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe was not loaded to Hive’s UDF list when HiveServer2 starts up. We need to copy the JAR file that contains this class to Hive’s AUX directory. Steps as follows: 1) Locate the AUX directory for HiveServer2, if you don’t have one create one and update Hive’s configuration through Cloudera Manager. If you don’t use Cloudera Manager, simply create a directory on HiveServer2 host, in my case is /hive/jars. 2) Create a symlink to file /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar (or if not using Cloudera Manager, /usr/lib/hive/lib/hive-contrib.jar) from within /hive/jars

ln -s /opt/cloudera/parcels/CDH/lib/hive/lib/hive-contrib.jar /hive/jars/hive-contrib.jar

3) If you don’t use Cloudera Manager, add the following:

hive.aux.jars.path
/hive/jars/hive-contrib.jar

to hive-site.xml for HiveServer2. If you use Cloudera Manager, simply go to step 4. 4) Restart HiveServer2. This should be able to remove the error we saw earlier in the post and get Hive query working. Hope this helps.