3

I am executing the following

hive -e 'select product_id from table;' > out.txt

This is giving me a text file with each product id on each line. I want them to be on one line with commas in between each id. I even tried to use shell scripting to get the desired format.

paste -s -d ',' < out.txt

Using this command I am getting the result on the terminal in the right format. But the file is not getting modified. Ideally, shouldn't this be possible by setting some hive configuration.

I looked at this SO question. But it did not work for me.

Community
  • 1
  • 1
user3309069
  • 101
  • 2
  • 8

2 Answers2

3

Try 

sed -i 'N;s/\n/,/' filename

Your question heading was misleading, as per the question heading,

For Hive versions 0.11.0 and above,

  INSERT OVERWRITE [LOCAL] DIRECTORY 'dirname' 
    ROW FORMAT DELIMITED 
    FIELDS TERMINATED BY ',' 
    <your query>;

Reference : https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Writingdataintothefilesystemfromqueries

is yet another solution to create csv from hive query results.

Arun A K
  • 2,205
  • 2
  • 27
  • 45
  • The file did get modified. But it is now looking like this 1,2\n 3,4\n 4,5 – user3309069 Jan 10 '15 at 21:39
  • If that is the case, are there any extra chars in the initial file? paste -s -d ',' < out.txt > out1.txt should work. There is no workaround in hive config changes to accomplish the same. – Arun A K Jan 10 '15 at 21:51
1

The answer is out there somewhere, but I am giving you my answer which I use on a regular basis in hive output to csv. This works like charm and you can also put a complex query into a file and use the below code:

hive -e 'select * from table' | sed 's/[\t]/~/g' > output.csv
hive -f /tmp/myhql.hql | sed 's/[\t]/~/g' > output.csv
peterh
  • 11,875
  • 18
  • 85
  • 108
BalaramRaju
  • 439
  • 2
  • 8
  • See you? Formatting things is very easy here, and goodlooking content attracts upvotes. Upvotes are our collective money, sex and drug here, soon you will be also addict. :-) (Btw, thanks the good content!) – peterh Oct 28 '15 at 15:55