Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to remove double quote from csv file at time of loading csv file into Hive orc tabel using data frame temp table.,How to remove double.quote from column variable present in csv file..,I am loading csv file into Hive orc table using data frame.

Solved Go to solution
Highlighted

How to remove double quote from csv file at time of loading csv file into Hive orc tabel using data frame temp table.,How to remove double.quote from column variable present in csv file..,I am loading csv file into Hive orc table using data frame.

New Contributor

I m loading csv file into Hive orc table using data frame temporary table. After loading into Hive table data is present with double quote.

Input file

"Arpit","Jain",123

"Qwee","ffhh",5778

How to remove this double quote at time of inserting into Hive table which induce by csv format .

,

I m loading csv file to orc Hive table using data frame temporary table.

But in Hive table it's loaded with double quote.

How can I remove double quotes .

Input csv file in hdfs

"Arpit","Jain",1234,"India"

"ABC","abcd",7657,"India"

,
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: How to remove double quote from csv file at time of loading csv file into Hive orc tabel using data frame temp table.,How to remove double.quote from column variable present in csv file..,I am loading csv file into Hive orc table using data frame.

Guru

You will need to use OpenCSVSerde: https://cwiki.apache.org/confluence/display/Hive/CSV+Serde

Just add this to your create table ddl (and use the appropriate delim for separator character)

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

A limitation is that it stores all fields as string. See link above and this one: https://community.hortonworks.com/questions/56611/hive-ignoring-data-type-declarations-in-create-tab...

There are workarounds like loading using OpenCSVSerde into a temp table and then load that (Create table as select...) into an ORC table.

Alternatively, you could use pig to clean double quotes first and then load that data.

If this is what you were looking for, let me know by accepting the answer; else, let me know of any gaps.

View solution in original post

4 REPLIES 4
Highlighted

Re: How to remove double quote from csv file at time of loading csv file into Hive orc tabel using data frame temp table.,How to remove double.quote from column variable present in csv file..,I am loading csv file into Hive orc table using data frame.

Guru

You will need to use OpenCSVSerde: https://cwiki.apache.org/confluence/display/Hive/CSV+Serde

Just add this to your create table ddl (and use the appropriate delim for separator character)

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

A limitation is that it stores all fields as string. See link above and this one: https://community.hortonworks.com/questions/56611/hive-ignoring-data-type-declarations-in-create-tab...

There are workarounds like loading using OpenCSVSerde into a temp table and then load that (Create table as select...) into an ORC table.

Alternatively, you could use pig to clean double quotes first and then load that data.

If this is what you were looking for, let me know by accepting the answer; else, let me know of any gaps.

View solution in original post

Highlighted

Re: How to remove double quote from csv file at time of loading csv file into Hive orc tabel using data frame temp table.,How to remove double.quote from column variable present in csv file..,I am loading csv file into Hive orc table using data frame.

@Arpit Jain

When you create table as select ... into ORC table don't forget the cast the proper data type to match your target table. Some of the fields may get converted implicitly, others not.

Highlighted

Re: How to remove double quote from csv file at time of loading csv file into Hive orc tabel using data frame temp table.,How to remove double.quote from column variable present in csv file..,I am loading csv file into Hive orc table using data frame.

Explorer

doesn't work here, full script is as below:

<code>CREATE TABLE sr.sr2013 ( 
creation_date STRING,   
status STRING,   
first_3_chars_of_postal_code STRING,   
intersection_street_1 STRING,   
intersection_street_2 STRING,   
ward STRING,   
service_request_type STRING,   
division STRING,   
section STRING ) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' 
WITH SERDEPROPERTIES (
'colelction.delim'='\u0002', 
'mapkey.delim'='\u0003', 
'serialization.format'=',', 
'field.delim'=',', 
'skip.header.line.count'='1',
'quoteChar'= "\"") ;
<code>

<p><img src="/storage/attachments/93770-screenshot-12-2-2018-10-07-42-am.png"></p>

Highlighted

Re: How to remove double quote from csv file at time of loading csv file into Hive orc tabel using data frame temp table.,How to remove double.quote from column variable present in csv file..,I am loading csv file into Hive orc table using data frame.

Explorer

Impala rejected the change of:

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'

Don't have an account?
Coming from Hortonworks? Activate your account here