How do I output the results of a HiveQL query to CSV?

Question

I want to put my Results of a hiveql query into a CSV file.

How can I do it?

I tried doing this, but it didn’t work.

insert overwrite directory '/download/output.csv' select goods from the table;

Answer 1

You are using the right command insert overwrite ,it is the best function to use to save data in a file, but it is saving data in HDFS directory.So, you will not find the data in your local csv file.To save the data in your Local machine you have to use the word “local”

insert overwrite local directory '/download/output.csv' select goods from table;

This will save the Data in your csv file.

Hope this helps!

Answer 2

You should use CREATE TABLE AS SELECT (CTAS) statement to create a directory in HDFS with the files containing the results of the query. After that, you will have to export those files from HDFS to your regular disk and merge them into a single file.

You also might have to do some trickery to convert the files from '01' - delimited to CSV. You could use a custom CSV SerDe or post-process the extracted file.

You can also refer the following video for more information regarding the same: