CODEWITHSUNDEEP
hivehiveql
NJ_315
NJ_315

Reputation: 1883

how can i copy data from a Hive table into local system?

i have created a table in Hive "sample" and loaded a csv file "sample.txt" into it.

now i need that data from "sample" into my local /opt/zxy/sample.txt.

How can i do that?

Upvotes: 4

Views: 11979

Answers (4)

Alex B
Alex B

Reputation: 2395

Readers who are accessing Hive from Windows OS can check out this script on Github.

It's a Python+paramiko script that extracts Hive data to local Windows OS file-system.

Upvotes: 0

Lukas Vermeer
Lukas Vermeer

Reputation: 5940

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 * from sample' > /opt/zxy/sample.txt

Hope that helps.

Upvotes: 1

Tariq
Tariq

Reputation: 34184

Since your intention is just to copy the entire file from HDFS to your local FS, I would not suggest you to do it through a Hive query, because of the following reasons :

  • It'll start a Mapreduce job which will take more time than a normal copy.
  • It'll create file(s) with different names(000000_0, 000001_0 and so on), which will require you to rename the file manually afterwards.
  • You might face problem in opening these files as they are without any extension. Your OS would be unable to choose an application to open these files on its own. In such a case you either have to rename the file or manually select an application to open it.

To avoid these problems you could use HDFS get command :

bin/hadoop fs -get /user/hive/warehouse/sample/sample.txt /opt/zxy/sample.txt

Simple n easy. But if you need to copy some selected data, then you have to use a Hive query.

HTH

Upvotes: 1

Doctor Dan
Doctor Dan

Reputation: 771

Hortonworks' Sandbox lets you do it through its HCatalog menu. Otherwise, the syntax is 

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/c' SELECT a.* FROM b

as per Hive language manual

Upvotes: 2

Related Questions