It is a pretty common use case to export the contents of a Hive table into a CSV file. It\u2019s pretty simple if you are using a recent version of Hive. In this post, we will see who to achieve this with both newer and older versions of Hive.<\/p>\n

Hive version 11 or higher<\/span><\/h2>\n

If you are using Hive version 11 or higher below command will do the job. <\/span>ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘,’ dictates that the columns should be delimited by a comma.<\/span><\/p>\n

INSERT OVERWRITE LOCAL DIRECTORY '\/home\/hirw\/sales \nROW FORMAT DELIMITED FIELDS TERMINATED BY ',' \nselect * from sales_table;<\/pre>\n
 <\/p>\n
Hive versions older than 11<\/span><\/h2>\n
By default selecting the hive table and writing to a file like below will result in a tab separated file and of course this is not what you want as you want a comma separated file.<\/span><\/p>\n
hive -e 'select * from sales_table' > \/home\/hirw\/sales.tsv<\/pre>\n
With below, you are selecting the table and piping the results to sed command and passing a regex expression.<\/span><\/p>\n
hive -e 'select * from sales_table' | sed 's\/[\\t]\/,\/g' > \/home\/hirw\/sales.csv<\/pre>\n
The regex expression does a global match of all tab character [t] and replace with a ,<\/span><\/p>\n
This approach is not preferred as you might get inconsistent results when dealing with huge datasets. Also you are combing Hive and sed (Linux) tools to get the job done which in our opinion is not a clean approach.<\/span><\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
It is a pretty common use case to export the contents of a Hive table into a CSV file. It\u2019s pretty simple if you are using [\u2026]<\/span><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[],"class_list":["post-1707","post","type-post","status-publish","format-standard","hentry","category-apache-hive"],"yoast_head":"\n