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)
... View more
