Issue with Insert overwrite directory

Question

1)

insert overwrite directory `'/user/sample/newfolder'` 

row format delimited

fields terminated by ', '

select * from emp;

Is giving me data without a header. Even after using set hive.cli.print.header=true;

I tried doing hive -e 'set hive.cli.print.header=true;select * from emp;' > /user/sample/newfolder/sample.xls -- it didnt work saying : No such file or directory

2) The data per record is going to the other line. How can I limit it to a single row?

ex: 1, ppp, ddd,44,

45,www

but i want it as 1,ppp,ddd,44,45,www

Answer 1

Adding header when doing insert overwrite directory is not supported yet, see this Jira

You can concatenate your output file with header file:

hadoop fs -cat /user/dir/header.csv /user/dir/output_file.csv | hadoop fs -put - /user/dir/output_w_header.csv

Or rewrite your select query like this (ORDER BY will trigger single final reducer and may work slow):

select * from 
(
select --header
      0           as order_col
      'col1_name' as col1,
      'col2_name' as col2,
       ...
      'colN_name' as colN
UNION ALL 
select --data
       1                    order_col,
       cast(col1 as string) col1, --cast to strings
       col2, ... coln 
  from emp
)s 
order by order_col;