Export Hive Query Results

Question

I'm new to hive and could use some tips.

I'm trying to export query results from hive as a csv. When I try to pipe them out of CLI like:

hive -e 'select * from table'>OutPut.txt

I get a text file that has all the records but doesn't have the column headers. Does anyone have a tip for how to export the query results with the column headers, to a csv file?

If I run the query in hue, and then download the results as a csv I get a csv with the column headers but no records. If anyone has a tip on how to download query results from hue with records and column headers, I would greatly appreciate it too.

Solution

To export the column headers, you need to set the following in the hiverc file:

set hive.cli.print.header=true;

To get just the headers into a file, you could try the following:

hive -e 'set hive.cli.print.header=true; SELECT * FROM TABLE_NAME LIMIT 0;' > /file_path/file_name.txt

Comments

[user3476463]

Hi Visakh, thanks for getting back to me on this. Your suggestion worked great for getting the column headers included along with the data, as a text file. Can you suggest how I could add 'c' as a delimiter for the columns, to the exported data?

[visakh]

I would suggest processing the file_name.txt using any Unix utility such as sed or awk to add the delimiters for the column names.

[Chaitra Bannihatti]

Hi i did the same way, but in the result i am getting column names along with table names for each column name

[Chaitra Bannihatti]

I want oly the column names to come