How to export a Hive table into a CSV file?

csv hive
Dunith Dhanushka picture Dunith Dhanushka · Jun 13, 2013 · Viewed 203.4k times · Source

I used this Hive query to export a table into a CSV file.

INSERT OVERWRITE DIRECTORY '/user/data/output/test' select column1, column2 from table1;

The file generated '000000_0' does not have comma separator

Is this the right way to generate CSV file? If no, please let me know how can I generate the CSV file?

Answer

user1922900 picture user1922900 · May 30, 2014

or use this

hive -e 'select * from your_Table' | sed 's/[\t]/,/g'  > /home/yourfile.csv

You can also specify property set hive.cli.print.header=true before the SELECT to ensure that header along with data is created and copied to file. For example:

hive -e 'set hive.cli.print.header=true; select * from your_Table' | sed 's/[\t]/,/g'  > /home/yourfile.csv

If you don't want to write to local file system, pipe the output of sed command back into HDFS using the hadoop fs -put command.

It may also be convenient to SFTP to your files using something like Cyberduck, or you can use scp to connect via terminal / command prompt.

Related questions

Hadoop/Hive : Loading data from .csv on a local machine

As this is coming from a newbie... I had Hadoop and Hive set up for me, so I can run Hive queries on my computer accessing data on AWS cluster. Can I run Hive queries with .csv data stored on …

Read More
Export as csv in beeline hive

I am trying to export my hive table as a csv in beeline hive. When I run the command !sql select * from database1 > /user/bob/output.csv it gives me syntax error. I have successfully connected to the database …

Read More
How to convert sas7bdat file to csv?

I want to convert a .sas7bdat file to a .csv/txt format so that I can upload it into a hive table. I'm receiving the .sas7bdat file from an outside server and do not have SAS on my …

Read More