How to locate/export Hive query?

0
votes

I am new at Hive and am attempting to export a hive query to a local file on my computer that way I can import results to excel.

When I do from inside hive;

hive -e select * from TABLE limit 10'>output.txt;

I get "FAILED: ParseException line 1:0 cannot recognize input near 'hive' '-' 'e'"

when I do

hive -S -e "USE DATABASE; select * from TABLE limit 10" > /tmp/test/test.csv;

from shell OR

insert overwrite local directory '/tmp/hello' select * from TABLE limit 10;

It goes to the hdfs system in Hive -- how do I get this to my local machine?

2
hivehiveql
since your file is in the HDFS, you will have to ssh it to your local box. Try scp and similar commands to move it out of HDFS to your system. – Neels
@Neels Not sure what you mean, can you please explain a bit more? Sorry, I am new to this.. – Sam
maybe you forgot the apostrophe before select clause – HISI
@hisi No I added it :( -- I did the one above hive -S -e "USE DATABASE; select * from TABLE limit 10" > /tmp/test/test.csv; in Shell but it went to the HDFS. Not sure how to make it go to local drive :( – Sam

2 Answers

0
votes

You can export query to CSV file like:

hive -e 'select * from your_Table' > /home/yourfile.csv

to get this file to your local machine, you should use HDFS:

HDFS DFS -get /tmp/hello /PATHinLocalMachine

Check out this Question

0
votes

You are seeing the error as you are running the hive -e commands in the hive repl as show below

hive (venkat)> hive -e 'select * from a';
NoViableAltException(26@[])
        at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:1084)
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:202)
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:437)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:320)
        at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1219)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1260)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1156)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1146)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:216)
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:168)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:379)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:739)
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:624)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at Sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.hadoop.util.RunJar.run(RunJar.java:233)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:148)
FAILED: ParseException line 1:0 cannot recognize input near 'hive' '-' 'e'

you have to do it in the OS shell as shown below

[venkata_udamala@gw02 ~]$ hive -e 'use database_name;select * from table_name;' > temp.txt