When to Use Hive CSVSerde

I've seen some postings (including This one) where people are using CSVSerde for processing input data. CSVSerde is a magical piece of code, but it isn't meant to be used for all input CSVs.

tl;dr - Use CSVSerde only when you have quoted text or really strange delimiters (such as blanks) in your input data - otherwise you will take a rather substantial performance hit...

When to use CSVSerde:

For example: If we have a text file with the following data:

col1 col2 col3
----------------------
121 Hello World 4567
232 Text 5678
343 More Text 6789

Pipe delimited it would look like:

121|Hello World|4567|
232|Text|5678|
343|More Text|6789|

but Blank delimited with quoted text would look like (don't laugh - Progress database dumps are blank delimited and text quoted in this exact format):

121 'Hello World' 4567
232 Text 5678
343 'More Text' 6789

Notice that the text may or may not have quote marks around it - text only needs to be quoted if it contains a blank. This is a particularly nasty set of data. You need custom coding - unless you use CSVSerde.

CSVSerde can handle this data with ease. Blank delimited/Quoted text files are parsed perfectly without any coding when you use the following table declaration: .

CREATE TABLE my_table(col1 string, col2, string, col3 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ( "separatorChar" = " ",
"quoteChar" = "'")

Performance Hit when Using CSVSerde on conventional CSV data

tl;dr Using CSVSerde for conventional CSV files is about 3X slower...

The following code shows timings encountered when processing a simple pipe-delimited csv file. One Hive table definition uses conventional delimiter processing, and one uses CSVSerde.

The input timings were on a small cluster (28 data nodes). The file used for testing had 62,825,000 rows. Again, rather small.

Table DDL using conventional delimiter definition:

CREATE external TABLE test_csv_serde (
`belnr` string,
`bukrs` string,
`budat` string,
`bstat` string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
location '/user/<uname>/elt/test_csvserde/';

-- Load the data one-time
insert overwrite table test_csv_serde
select * from <large table>;

Table DDL using CSVSerde (same file/source data as the other table):

CREATE external TABLE test_csv_serde_using_CSV_Serde_reader (
`belnr` string,
`bukrs` string,
`budat` string,
`bstat` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = "|")
location '/user/<uname>/elt/test_csvserde/';

Results:

hive> select count(*) from test_csv_serde;
Time taken: 8.683 seconds, Fetched: 1 row(s)
hive> select count(*) from test_csv_serde_using_CSV_Serde_reader;
Time taken: 27.442 seconds, Fetched: 1 row(s)

hive> select count(*) from test_csv_serde;
Time taken: 8.707 seconds, Fetched: 1 row(s)
hive> select count(*) from test_csv_serde_using_CSV_Serde_reader;
Time taken: 27.41 seconds, Fetched: 1 row(s)

hive> select min(belnr) from test_csv_serde;
Time taken: 10.267 seconds, Fetched: 1 row(s)
hive> select min(belnr) from test_csv_serde_using_CSV_Serde_reader;
Time taken: 29.271 seconds, Fetched: 1 row(s)