Hive query output to file

Question

I run hive query by java code. Example:

"SELECT * FROM table WHERE id > 100"

How to export result to hdfs file.

score 70 · Answer 1 · answered Jan 12 '13 at 03:25

70

The following query will insert the results directly into HDFS:

INSERT OVERWRITE DIRECTORY '/path/to/output/dir' SELECT * FROM table WHERE id > 100;

answered Jan 12 '13 at 03:25

Charles Menguy

40,830
17
95
117

You could use the `printf` UDF (0.9) to format your input as you want, otherwise creating your own UDF might be an option. – Charles Menguy Jan 12 '13 at 03:43
@Charles - can you show or link to an example of how to use a printf UDF to format output? Not obvious what to do here. – quux00 May 22 '13 at 03:27
Thinks Charles is suggesting you use the printf UDF to format what you output, for example: insert overwrite directory '/path/to/output/dir' select printf("%s\t%s",arg1, arg2) from table where id > 100; – Dan Bennett Jan 06 '15 at 18:09

score 40 · Answer 2 · answered Apr 08 '14 at 23:46

40

This command will redirect the output to a text file of your choice:

$hive -e "select * from table where id > 10" > ~/sample_output.txt

answered Apr 08 '14 at 23:46

tnguyen80

417
4
3

how to overwrite the file if i want to run another select * command from a different table and write to same file ? – sarath Feb 24 '16 at 19:09
1

This will take ages for large tables, as it has to write it to stdout. – TayTay May 27 '16 at 12:20
wouldn't that also write the progress to the output file? or is the progress written to stderr? – Avision Jul 24 '16 at 08:28
3

You can add -S to make it silent, meaning less noise in the file. – jsarma Dec 13 '16 at 20:22
If your query is long, you can read the query from a file, like this: `$hive -f ~/query.hql > ~/out.txt` – ruhong Feb 15 '19 at 02:38

score 28 · Answer 3 · answered Apr 30 '15 at 02:54

28

This will put the results in tab delimited file(s) under a directory:

INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/YourTableDir'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
SELECT * FROM table WHERE id > 100;

answered Apr 30 '15 at 02:54

topkara

886
9
15

2

Is there a way to spit out a header row? – Chris Jul 06 '16 at 02:05
I'm getting an error with this code: cannot recognize input near 'ROW' 'FORMAT' 'DELIMITED' in select clause – Selrac Nov 13 '16 at 15:40
It seems like you have somehow a runoff line in your input; perhaps missing closing quotes. Try it in the console. – topkara Nov 14 '16 at 19:58
Please check this https://issues.apache.org/jira/browse/HIVE-5672 and the fix is available from hive version 0.13 – maxmithun Jun 23 '17 at 03:14

score 3 · Answer 4 · edited Jul 23 '15 at 11:06

3

I agree with tnguyen80's response. Please note that when there is a specific string value in query better to given entire query in double quotes.

For example:

$hive -e "select * from table where city = 'London' and id >=100" > /home/user/outputdirectory/city details.csv

edited Jul 23 '15 at 11:06

Cleb

25,102
20
116
151

answered Jul 23 '15 at 10:25

Magesh Nagarajan

31
1

is this going to write to HDFS or to the local filesystem - that looks like local to me, whereas the "INSERT OVERWRITE DIRECTORY" method writes to HDFS – TobyEvans Nov 22 '16 at 17:59

score 3 · Answer 5 · answered Apr 23 '17 at 18:06

3

The ideal way to do it will be using "INSERT OVERWRITE DIRECTORY '/pathtofile' select * from temp where id > 100" instead of "hive -e 'select * from...' > /filepath.txt"

answered Apr 23 '17 at 18:06

Sandeep Dabhade

71
1
4

Does it creates the file or file need to be created before? – Reihan_amn Jun 03 '17 at 21:34

Ankita · Answer 6 · 2019-02-06T20:31:56.150

@sarath how to overwrite the file if i want to run another select * command from a different table and write to same file ?

INSERT OVERWRITE LOCAL DIRECTORY '/home/training/mydata/outputs' SELECT expl , count(expl) as total
FROM ( SELECT explode(splits) as expl FROM ( SELECT split(words,' ') as splits FROM wordcount ) t2 ) t3 GROUP BY expl ;

This is an example to sarath's question

the above is a word count job stored in outputs file which is in local directory :)

score 3 · Answer 7 · edited Feb 15 '20 at 16:48

3

Two ways can store HQL query results:

Save into HDFS Location

INSERT OVERWRITE DIRECTORY "HDFS Path" ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
SELECT * FROM XXXX LIMIT 10;

Save to Local File

$hive  -e "select * from table_Name" > ~/sample_output.txt
$hive -e "select * from table where city = 'London' and id >=100" > /home/user/outputdirectory/city details.csv

edited Feb 15 '20 at 16:48

MartenCatcher

2,713
8
26
39

answered Feb 15 '20 at 15:46

seshu

31
1

score 2 · Answer 8 · edited Mar 19 '19 at 21:14

Create an external table
Insert data into the table
Optional drop the table later, which wont delete that file since it is an external table

Example:

Creating external table to store the query results at '/user/myName/projectA_additionaData/'

CREATE EXTERNAL TABLE additionaData
(
     ID INT,
     latitude STRING,
     longitude STRING
)
COMMENT 'Additional Data gathered by joining of the identified cities with latitude and longitude data' 
ROW FORMAT DELIMITED FIELDS
TERMINATED BY ',' STORED AS TEXTFILE location '/user/myName/projectA_additionaData/';

Feeding the query results into the temp table

 insert into additionaData 
     Select T.ID, C.latitude, C.longitude 
     from TWITER  
     join CITY C on (T.location_name = C.location);

Dropping the temp table

drop table additionaData

score 1 · Answer 9 · answered Sep 12 '17 at 09:08

To directly save the file in HDFS, use the below command:

hive> insert overwrite  directory '/user/cloudera/Sample' row format delimited fields terminated by '\t' stored as textfile select * from table where id >100;

This will put the contents in the folder /user/cloudera/Sample in HDFS.

score 0 · Answer 10 · edited Nov 09 '18 at 22:37

0

Enter this line into Hive command line interface:

insert overwrite directory '/data/test' row format delimited fields terminated by '\t' stored as textfile select * from testViewQuery;

testViewQuery - some specific view

edited Nov 09 '18 at 22:37

WattsInABox

4,548
2
33
43

answered Nov 22 '17 at 14:13

Don

21
1

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar or using Ctrl+K on your keyboard to nicely format and syntax highlight it! – WhatsThePoint Nov 22 '17 at 14:37

score 0 · Answer 11 · answered Apr 25 '19 at 20:49

To set output directory and output file format and more, try the following:

INSERT OVERWRITE [LOCAL] DIRECTORY directory1
[ROW FORMAT row_format] [STORED AS file_format] 
SELECT ... FROM ...

Example:

INSERT OVERWRITE DIRECTORY '/path/to/output/dir'
ROW FORMAT DELIMITED
STORED AS PARQUET
SELECT * FROM table WHERE id > 100;

Hive query output to file

11 Answers11

Linked