hive sql, serde how to not quote my fields?

3
votes

Since by default serde quotes fields by ", How can I not quote my fields using serde?

I tried:

row format serde "org.apache.hadoop.hive.serde2.OpenCSVSerde"
with serdeproperties(
"separatorChar" = ",",
"quoteChar" = "")

But i'm getting

FAILED: SemanticException java.lang.StringIndexOutOfBoundsException: String index out of range: 0
2
csvhivedouble-quoteshive-serde

2 Answers

2
votes

You could achieve this by specifying \u0000 as the quote character. Since quoteChar expects a string, you should use this unicode version of NULL.

ROW FORMAT SERDE
    "org.apache.hadoop.hive.serde2.OpenCSVSerde"
WITH SERDEPROPERTIES (
    "separatorChar" = ",",
    "quoteChar" = "\u0000")

This unicode NULL \u0000 is what used by the CSV writer class as value for NO_QUOTE_CHARACTER: http://www.java2s.com/Code/Java/Development-Class/AverysimpleCSVwriterreleasedunderacommercialfriendlylicense.htm

0
votes

For some reason "quoteChar" = "\u0000" didn't work for me as suggested in Nirmal's answer above.

When saving to file without quotes around the fields, I use:

-- saving to file
INSERT OVERWRITE LOCAL DIRECTORY 'file:/home/sidazhou/temp' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
SELECT *
FROM temp_table
;

PS. I know this isn't what's being asked, which concerns ROW FORMAT SERDE instead of ROW FORMAT DELIMITED FIELDS.