output hive query result as csv enclosed in quotes

Question

I have to export data from a hive table in a csv file in which fields are enclosed in double quotes.

So far I am able to generate a csv without quotes using the following query

INSERT OVERWRITE DIRECTORY '/user/vikas/output'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
SELECT QUERY

The output generated looks like

1,Vikas Saxena,Banking,JL5

However, I need the output as

"1","Vikas Saxena","Banking","JL5"

I tried changing the query to

INSERT OVERWRITE DIRECTORY '/user/vikas/output'
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   "separatorChar" = ",",
   "quoteChar"     = "\"",
   "escapeChar"    = "\\"
)
SELECT QUERY

But it displays error

Error while compiling statement: FAILED: ParseException line 1:0 cannot recognize input near 'ROW' 'FORMAT' 'SERDE'

So why don't you just create an EXTERNAL TABLE mapped to that directory, INSERT in it, then DROP the table? — Samson Scharfrichter, Sep 21 '16 at 21:38

score 0 · Answer 1 · answered Aug 01 '17 at 11:49

0

Create an external table:

CREATE EXTERNAL TABLE new_table(field1 type1, ...)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "\""
)
STORED AS TEXTFILE
LOCATION '/user/vikas/output';

Then select into that table:

insert into new_table select * from original_table;

Your CSV is then on disk at /user/vikas/output

answered Aug 01 '17 at 11:49

la_kal

31
2

This writes the csv without a header. Is there a way to also get the header to write too? – Mike Pone Mar 23 '22 at 16:41
Need advise on the above, need data with headers. – Ma28 Jun 29 '23 at 13:48

output hive query result as csv enclosed in quotes

1 Answers1

Linked