Hive select fails with RuntimeException CSVSerde does not exist

Products

• Services Suite

Issue/Introduction

Symptoms:

Create a Hive table with the command hive -f mytable.sql using the following script:

add jar /tmp/csv-serde-0.9.1.jar;
drop table test_table;
 set hive.cli.print.header=true;
CREATE  TABLE test_table()
	row format serde 'com.bizo.hive.serde.csv.CSVSerde'
	with serdeproperties(
	"separatorChar" = "~",
	"quoteChar" = '"')
stored as textfile;

Select the table from a hive session returns in the following error message: 

hive> select * from  test_table limit 1;
FAILED: RuntimeException MetaException(message:org.apache.hadoop.hive.serde2.SerDeException SerDe com.bizo.hive.serde.csv.CSVSerde does not exist)

Environment

Cause

When creating the Hive table, csv-serde gets loaded before the create statement is executed. As a result, once you log back into the Hive CLI shell, the csv-serde jar file is no longer sourced.

Resolution

Make sure to source csv-serde before executing the query. There are several ways this can be accomplished.

1. Add the csv-serde to Hive aux jars at runtime.

$> hive --hiveconf hive.aux.jars.path=/home/gpadmin/3845/csv-serde-0.9.1.jar

2. Place csv-serde-0.9.1.jar in a common directory on the Hive client node and set hive.aux.jars.path in /etc/gphd/hive/conf/hive-site.xml.

3. Add the jar file during runtime.

hive> add jar /tmp/csv-serde-0.9.1.jar;