Splitting hive query output as comma separate value

Question

I am trying to load hive query output into a csv file, when i run below script

hive -e "select * from mytable" > output.csv

i want the output of row fields should be separated by commas.

`hive -e "select * from mytable" |awk -v OFS=, '{$1=$1}1' >output.csv` this is not complete answer — P...., Mar 14 '19 at 15:35
Look also at this: https://stackoverflow.com/a/53755790/2700344 — leftjoin, Mar 14 '19 at 19:07

score 1 · Answer 1 · answered Mar 14 '19 at 15:47

1

You can write a sql query for that, which would be less error prone.

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/mytable/data' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','  
LINES TERMINATED BY "\n"
SELECT * FROM mytable;

if you have a beeline, it has an option to export the data into csv.

beeline --outputformat=csv2 -e "select * from mytable" > mytable.csv

answered Mar 14 '19 at 15:47

Gaurang Shah

11,764
9
74
137

Thanks for the answers, it is working, but the results are getting into .gz files, Is there a way we can get the results into normal files – Venkat J Mar 16 '19 at 18:00
I need to load the hive query output into Teradata, .gz files creating issues with delimiter while loading to Teradata, but when i copy data into a flat file, i could load without issues. – Venkat J Mar 16 '19 at 18:27
which option are you talking about? – Gaurang Shah Mar 18 '19 at 13:49

Splitting hive query output as comma separate value

1 Answers1