1

I want to write hive query output into HDFS file in CSV Format( comma separated).

For LOCAL DIRECTORY:

INSERT OVERWRITE LOCAL DIRECTORY '/home/test/result/' 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' SELECT * from table;

Above Works fine and writes output of query as CSV Format in local directory.

For HDFS:

INSERT OVERWRITE DIRECTORY '/home/test/result/' SELECT * from table;

Output written to the HDFS is serialized as text with columns separated by ^A and rows separated by newlines.

NOTE : 

ROW FORMAT DELIMITED FIELDS  ---- Not supported for above query

Is there any way to write hive query output in HDFS directory as CSV file(comma separated)

Dharmesh Porwal
  • 1,406
  • 2
  • 12
  • 21
Vengatesh Babu
  • 33
  • 2
  • 8

3 Answers3

0

There is only one way.. 1. create a table as expected your result eg:create external table result(col1 type,col2 type,......)row format delimited fields terminated by ',' stored as textfile location '/tmp/result'; 2. import data to this table eg:insert overwrite table result select * from tablename; 3.drop the external table eg:drop table result;

On dropping external table only schema will be deleted data will remain in text file in specified directory which will be in comma separated format.

hemendra kumar
  • 127
  • 7
0

Try this:

INSERT OVERWRITE DIRECTORY '/home/test/result/' SELECT concat(col1,',',col2,',', ...colN) from table;

Mukesh S
  • 2,856
  • 17
  • 23
0

For writing data to hdfs in a custom format, use printf.

insert overwrite directory "youroutputdir"  select printf("%s,%s,%d", col1, col2, col3) from mytable;
axiom
  • 8,765
  • 3
  • 36
  • 38