question 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. in Archives of Support Questions (Read Only)

How to remove double quote from csv file ?

arpit3006 — Tue, 21 Dec 2021 20:29:26 GMT

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"

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.

gkeys — Wed, 16 Nov 2016 02:49:36 GMT

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.html

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.

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.

cstanca — Thu, 17 Nov 2016 06:30:07 GMT

@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.

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.

axie — Tue, 21 Dec 2021 19:16:22 GMT

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

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'= "\"") ;

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.

axie — Sun, 02 Dec 2018 23:40:13 GMT

Impala rejected the change of:

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

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.

ebeb — Tue, 21 Dec 2021 16:26:17 GMT

Impala doesnt support the ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' even in newer version like v3.4.0. Any other option to remove double quotes in the output from Impala where the input csv file has quotes?