CODEWITHSUNDEEP
csvhadoopapache-sparkhivebigdata
underwood
underwood

Reputation: 933

HIVE escaped by not working '\\'

I have a data-set in S3

123, "some random, text", "", "", 236

I build a external table on this dataset :

CREATE EXTERNAL TABLE db1.myData(
    field1 bigint, 
    field2 string, 
    field3 string, 
    field4 string, 
    field5 bigint, 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
ESCAPED BY '\\' 
LOCATION 's3n://thisMyData/';

Problem/ Issue :
when I do select * from db1.myData

field2 is shown as 

some random

I need the field to be

some random, text

Gotcha's :
1. I cannot change the delimiter as there are over ~300 .csv files at this location
2. ESCAPED BY is not escaping the '\\'
3. I'm using HIVE 0.13 so there I cannot use CSV SerDe and neither i'm allowed to import new jars to cluster (its a complicated process to add a new jar as I have to go through Director level approvals)

Question:

All suggestions are welcome !!

N.B : THis is not a repeat question. If you think its a repeat, please guide me to right page and I will take this off of this portal :)

Upvotes: 4

Views: 9937

Answers (2)

Robert Penridge
Robert Penridge

Reputation: 8513

I had to use: ESCAPED BY '\134' which translates to: ESCAPED BY '\'.

Additionally, because I was calling the Athena create table statement by passing in the statement from a JSON file I had to add an extra \ to mask the original \ in JSON. So my final statement within the JSON file looked like this: ESCAPED BY '\\134'.

Upvotes: 2

Nishu Tayal
Nishu Tayal

Reputation: 20820

If you are using Hive 0.14, you can use CSV Serde like this:

CREATE TABLE my_table(a string, b string, ...)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = "\t",
   "quoteChar"     = "'",
   "escapeChar"    = "\\"
)  
STORED AS TEXTFILE;

Refer below link for details:

https://cwiki.apache.org/confluence/display/Hive/CSV+Serde

Upvotes: 0

Related Questions