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

Question

we would like to put the results of a Hive query to a CSV file. I thought the command should look like this:

insert overwrite directory '/home/output.csv' select books from table;

When I run it, it says it completeld successfully but I can never find the file. How do I find this file or should I be extracting the data in a different way?

score 147 · Accepted Answer · edited Dec 24 '22 at 20:52

Although it is possible to use INSERT OVERWRITE to get data out of Hive, it might not be the best method for your particular case. First let me explain what INSERT OVERWRITE does, then I'll describe the method I use to get tsv files from Hive tables.

According to the manual, your query will store the data in a directory in HDFS. The format will not be csv.

Data written to the filesystem is serialized as text with columns separated by ^A and rows separated by newlines. If any of the columns are not of primitive type, then those columns are serialized to JSON format.

A slight modification (adding the LOCAL keyword) will store the data in a local directory.

INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp' select books from table;

When I run a similar query, here's what the output looks like.

[lvermeer@hadoop temp]$ ll
total 4
-rwxr-xr-x 1 lvermeer users 811 Aug  9 09:21 000000_0
[lvermeer@hadoop temp]$ head 000000_0 
"row1""col1"1234"col3"1234FALSE
"row2""col1"5678"col3"5678TRUE

Personally, I usually run my query directly through Hive on the command line for this kind of thing, and pipe it into the local file like so:

hive -e 'select books from table' > /home/lvermeer/temp.tsv

That gives me a tab-separated file that I can use. Hope that is useful for you as well.

Based on this patch-3682, I suspect a better solution is available when using Hive 0.11, but I am unable to test this myself. The new syntax should allow the following.

INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp' 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
select books from table;

do you know any performance difference between insert overwrite local and piping, at which approximated volume it can become an issue, also, piping guarantees you'll get one file, as the other approach gives us a directory which potentially we need to merge afterwards — fd8s0, Nov 05 '14 at 14:56
Is it possible to export the data in HDFS as Sequence file format? — Nageswaran, Jul 27 '15 at 06:59
I tried the solution (patch-3682) and it worked well for me - except that for some reason the output file did not include the headers. Note that I have set hive.cli.print.header=true; in my .hiverc. For what it's worth the headers got printed to the terminal instead (which is obviously not what I wanted). — Peter Cogan, Dec 03 '15 at 19:22
@lukas-vermeer, when you create the table using the "INSERT OVERWRITE" method , the header information is lost . Is there a way to get the header information ? — ML_Passion, Feb 23 '17 at 18:14
Hi Lukas, how did you make your shell works in hadoop file system? — notilas, Dec 10 '18 at 21:32

score 25 · Answer 2 · edited Jan 23 '14 at 19:43

25

If you want a CSV file then you can modify Lukas' solutions as follows (assuming you are on a linux box):

hive -e 'select books from table' | sed 's/[[:space:]]\+/,/g' > /home/lvermeer/temp.csv

edited Jan 23 '14 at 19:43

dee-see

23,668
5
58
91

answered Jan 23 '14 at 19:22

David Kjerrumgaard

1,056
7
10

4

Thanks for this. I am using a variation, but it works very well. Please note that this will output comma-delimited, not necessarily what some folks think of as CSV. CSV typically has some formatting to handle data with commas (e.g. wrap data with double-quotes, and double-double-quote for data with double-quotes). Worth mentioning that adding the "--hiveconf hive.cli.print.header=True" parameter will get your headers in the output as well. – jatal Oct 27 '14 at 18:04
This is the cleanest solution – Dutta Sep 21 '15 at 17:08
1

This failed for me for e.g., a date time string that had a space between date and time. – williaster Dec 15 '16 at 21:43
@williaster sed 's/\t\+/,/g' this should help for this issue. – Sudhakar Chavan Jun 01 '17 at 11:44
This wouldn't work if the tsv has text that contains commas. (because unquoted innocent string commas will be treated as separators) – yahiaelgamal Jul 11 '17 at 20:32

score 7 · Answer 3 · edited Jun 14 '19 at 18:14

7

This is most csv friendly way I found to output the results of HiveQL.
You don't need any grep or sed commands to format the data, instead hive supports it, just need to add extra tag of outputformat.

hive --outputformat=csv2 -e 'select * from <table_name> limit 20' > /path/toStore/data/results.csv

edited Jun 14 '19 at 18:14

vimuth

5,064
33
79
116

answered Jun 14 '19 at 17:55

Rishabh Sachdeva

91
1
3

2

Does this need a specific version? Doesn't work for me at all `hive --outputformat=csv2 -e 'select * from bla' > test.csv` gives -> *Unrecognized option: --outputformat=csv2* on Hive 2.1.1-cdh6.3.3 – PandaWood May 11 '22 at 01:48
@PandaWood - yes it needs 3.1+. alternatively, one can try *beeline* with same parameter – d-_-b Mar 02 '23 at 08:25

score 4 · Answer 4 · answered Aug 08 '13 at 17:23

4

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 '\001' - delimited to CSV. You could use a custom CSV SerDe or postprocess the extracted file.

answered Aug 08 '13 at 17:23

Olaf

6,249
1
19
37

This approach is best if one wants to use output in a subsequent oozie pipeline step. – cerd Apr 13 '14 at 21:30

score 4 · Answer 5 · edited Jun 27 '16 at 07:42

You can use INSERT … DIRECTORY …, as in this example:

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name, salary, address
FROM employees
WHERE se.state = 'CA';

OVERWRITE and LOCAL have the same interpretations as before and paths are interpreted following the usual rules. One or more files will be written to /tmp/ca_employees, depending on the number of reducers invoked.

score 3 · Answer 6 · answered Jul 29 '14 at 18:00

3

If you are using HUE this is fairly simple as well. Simply go to the Hive editor in HUE, execute your hive query, then save the result file locally as XLS or CSV, or you can save the result file to HDFS.

answered Jul 29 '14 at 18:00

Ray

150
2
10

score 3 · Answer 7 · answered Aug 27 '15 at 00:49

3

I was looking for a similar solution, but the ones mentioned here would not work. My data had all variations of whitespace (space, newline, tab) chars and commas.

To make the column data tsv safe, I replaced all \t chars in the column data with a space, and executed python code on the commandline to generate a csv file, as shown below:

hive -e 'tab_replaced_hql_query' |  python -c 'exec("import sys;import csv;reader = csv.reader(sys.stdin, dialect=csv.excel_tab);writer = csv.writer(sys.stdout, dialect=csv.excel)\nfor row in reader: writer.writerow(row)")'

This created a perfectly valid csv. Hope this helps those who come looking for this solution.

answered Aug 27 '15 at 00:49

sisanared

4,175
2
27
42

1

It's 2016 and we still have to jump through hoops to do this? I found shravster's solution to be the best, most elegant solution so far. – Josh Jun 02 '16 at 14:44
How did you replace all \t chars in the column data ? did you address it in the query or created a separate view for it? – Naresh S Apr 23 '18 at 08:30
@NareshS, sorry for the late response. Yes, the columns were handled in hive to replace tabs with spaces or if they are essential, you could replace with a substitute like <:tab>, or something along those lines – sisanared May 13 '18 at 18:10
@sisanared, Thanks for the response. I see we need to use regex replace for all string columns and this would be cumbersome if we have a table with large number of colums > 100. Is there a quick solution for such case – Naresh S May 14 '18 at 08:42
@NareshS, unfortunately the only other solution is to clean up data before putting it in your partitions. Otherwise you will have to do it while performing the select for all the string columns that could contain tab chars – sisanared May 18 '18 at 04:39
@sisanared, Thanks for the response. – Naresh S May 18 '18 at 09:52

score 2 · Answer 8 · edited Jun 30 '17 at 12:48

I had a similar issue and this is how I was able to address it.

Step 1 - Loaded the data from Hive table into another table as follows

DROP TABLE IF EXISTS TestHiveTableCSV;
CREATE TABLE TestHiveTableCSV 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n' AS
SELECT Column List FROM TestHiveTable;

Step 2 - Copied the blob from Hive warehouse to the new location with appropriate extension

Start-AzureStorageBlobCopy
-DestContext $destContext 
-SrcContainer "Source Container"
-SrcBlob "hive/warehouse/TestHiveTableCSV/000000_0"
-DestContainer "Destination Container"
-DestBlob "CSV/TestHiveTable.csv"

Ram Ghadiyaram · Answer 9 · 2016-06-27T07:26:20.167

2

You can use hive string function CONCAT_WS( string delimiter, string str1, string str2...strn )

for ex:

hive -e 'select CONCAT_WS(',',cola,colb,colc...,coln) from Mytable' > /home/user/Mycsv.csv

edited Jun 27 '16 at 07:26

answered Apr 08 '15 at 21:03

Ram Ghadiyaram

28,239
13
95
121

score 2 · Answer 10 · answered Apr 12 '19 at 15:14

2

hive  --outputformat=csv2 -e "select * from yourtable" > my_file.csv

or

hive  --outputformat=csv2 -e "select * from yourtable" > [your_path]/file_name.csv

For tsv, just change csv to tsv in the above queries and run your queries

answered Apr 12 '19 at 15:14

Terminator17

782
1
6
13

score 1 · Answer 11 · edited Jun 27 '16 at 07:59

1

The default separator is "^A". In python language, it is "\x01".

When I want to change the delimiter, I use SQL like:

SELECT col1, delimiter, col2, delimiter, col3, ..., FROM table

Then, regard delimiter+"^A" as a new delimiter.

edited Jun 27 '16 at 07:59

Ram Ghadiyaram

28,239
13
95
121

answered Nov 06 '13 at 22:27

moshaholo

166
9

score 1 · Answer 12 · answered Dec 11 '18 at 21:34

1

I tried various options, but this would be one of the simplest solution for Python Pandas:

hive -e 'select books from table' | grep "|" ' > temp.csv

df=pd.read_csv("temp.csv",sep='|')

You can also use tr "|" "," to convert "|" to ","

answered Dec 11 '18 at 21:34

notilas

2,323
4
23
36

score 0 · Answer 13 · answered Jan 03 '18 at 13:35

0

Similar to Ray's answer above, Hive View 2.0 in Hortonworks Data Platform also allows you to run a Hive query and then save the output as csv.

answered Jan 03 '18 at 13:35

schoon

2,858
3
46
78

score 0 · Answer 14 · answered Nov 21 '18 at 14:57

In case you are doing it from Windows you can use Python script hivehoney to extract table data to local CSV file.

It will:

Login to bastion host.
pbrun.
kinit.
beeline (with your query).
Save echo from beeline to a file on Windows.

Execute it like this:

set PROXY_HOST=your_bastion_host

set SERVICE_USER=you_func_user

set LINUX_USER=your_SOID

set LINUX_PWD=your_pwd

python hh.py --query_file=query.sql

score 0 · Answer 15 · answered Mar 25 '19 at 20:22

Just to cover more following steps after kicking off the query: INSERT OVERWRITE LOCAL DIRECTORY '/home/lvermeer/temp' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select books from table;

In my case, the generated data under temp folder is in deflate format, and it looks like this:

$ ls
000000_0.deflate  
000001_0.deflate  
000002_0.deflate  
000003_0.deflate  
000004_0.deflate  
000005_0.deflate  
000006_0.deflate  
000007_0.deflate

Here's the command to unzip the deflate files and put everything into one csv file:

hadoop fs -text "file:///home/lvermeer/temp/*" > /home/lvermeer/result.csv

Anil Kumar · Answer 16 · 2019-04-18T06:11:41.883

0

I may be late to this one, but would help with the answer:

edited Apr 18 '19 at 06:11

answered Apr 18 '19 at 05:35

Anil Kumar

525
6
27

score 0 · Answer 17 · edited May 10 '19 at 11:33

0

This shell command prints the output format in csv to output.txt without the column headers.

$ hive --outputformat=csv2 -f 'hivedatascript.hql' --hiveconf hive.cli.print.header=false > output.txt

edited May 10 '19 at 11:33

Constantin Chirila

1,979
2
19
33

answered May 10 '19 at 10:21

Varanasi Sai Bhargav

1
3

score 0 · Answer 18 · edited Jul 15 '20 at 12:10

Use the command:

hive -e "use [database_name]; select * from [table_name] LIMIT 10;" > /path/to/file/my_file_name.csv

I had a huge dataset whose details I was trying to organize and determine the types of attacks and the numbers of each type. An example that I used on my practice that worked (and had a little more details) goes something like this:

hive -e "use DataAnalysis;
select attack_cat, 
case when attack_cat == 'Backdoor' then 'Backdoors' 
when length(attack_cat) == 0 then 'Normal' 
when attack_cat == 'Backdoors' then 'Backdoors' 
when attack_cat == 'Fuzzers' then 'Fuzzers' 
when attack_cat == 'Generic' then 'Generic' 
when attack_cat == 'Reconnaissance' then 'Reconnaissance' 
when attack_cat == 'Shellcode' then 'Shellcode' 
when attack_cat == 'Worms' then 'Worms' 
when attack_cat == 'Analysis' then 'Analysis' 
when attack_cat == 'DoS' then 'DoS' 
when attack_cat == 'Exploits' then 'Exploits' 
when trim(attack_cat) == 'Fuzzers' then 'Fuzzers' 
when trim(attack_cat) == 'Shellcode' then 'Shellcode' 
when trim(attack_cat) == 'Reconnaissance' then 'Reconnaissance' end,
count(*) from actualattacks group by attack_cat;">/root/data/output/results2.csv

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

18 Answers18

Linked

Related