How to export hive table data to a file on local UNIX?


Login or Register for Dates, Times and to Reply

 
Thread Tools Search this Thread
# 1  
How to export hive table data to a file on local UNIX?

Hi All ,
I am stuck on the below situation.I have a table called "test" which are created on hive.I need to export the data from hive to a file(test.txt) on local unix system.I have tried the below command ,but its giving the exception .
Code:
hive -e "select * from test " > /home/user/test.txt ;

Error is like below :
Code:
NoViableAltException(26@[])
        at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:19
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:434)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:352)
        at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:995)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1038)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:931)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:921)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:2
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:422)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:790
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:623)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:212)
FAILED: ParseException line 1:0 cannot recognize input near 'hive' '-' 'e'

If anyone can help me in this regard ,it will be really beneficial for me.Thanks !
# 2  
I expect you have typed the listed command from the hive Command line interface, your session log could have looked something like this (what you may have typed in blue):

Code:
user@your_host:~$ cd /usr/local/hive

user@your_host:/usr/local/hive$ hive

Logging initialized using configuration in jar:file:/usr/local/hive/lib/hive-common-0.11.0.jar!/hive-log4j.properties
Hive history file=/tmp/user/hive_job_log_user_1072@your_host_201505291127_11072.txt
hive> hive -e "select * from test" > /home/user/test.txt ;

NoViableAltException(26@[])
        at org.apache.hadoop.hive.ql.parse.HiveParser.statement(HiveParser.java:
        at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:19
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:434)
        at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:352)
        at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:995)
        at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1038)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:931)
        at org.apache.hadoop.hive.ql.Driver.run(Driver.java:921)
        at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:2
        at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:220)
        at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:422)
        at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:790
        at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:684)
        at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:623)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.hadoop.util.RunJar.main(RunJar.java:212)
FAILED: ParseException line 1:0 cannot recognize input near 'hive' '-' 'e'


Instead you want to run hive from the shell command line, perhaps something like this (again your typed commands in blue):

Code:
user@your_host:~$ cd /usr/local/hive

user@your_host:/usr/local/hive$ hive -S -e "USE my_database; select * from test" > /home/user/test.txt

Login or Register for Dates, Times and to Reply

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extract hive table structure

Hi, I need to extract only the create table structure with columns alone. for eg hive_table show create table hive_table: create table hive_table(id number,age number) OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat' LOCATION 'hdfs:/path/' I need only below ... (5 Replies)
Discussion started by: rohit_shinez
5 Replies

2. Linux

Parsing - export html table data as .csv file?

Hi all, Is there any out there have a brilliant idea on how to export html table data as .csv or write to txt file with separated comma and also get the filename of link from every table and put one line per rows each table. Please see the attached html and PNG of what it looks like. ... (7 Replies)
Discussion started by: lxdorney
7 Replies

3. Shell Programming and Scripting

Data export UNIX shell script

Hi, I want to write a shell script which connect to my database with the following credentials : User name : user PWD : rap_user_1 Hostname : app.Unix.Gsm1900.Org Port : 7862 SID : PTNC1 Once connected to DB i want to fetch data with the help of a SQL statement and expoet... (4 Replies)
Discussion started by: neeraj617
4 Replies

4. Shell Programming and Scripting

Shell script to export data from Oracle table .

Hi, I want to write a shell script which will export data from oracle table . I don't want to save that data . I want the queries . Right now i am right clicking on the table and clicking on export as to my desktop . Please let me know if any one have any idea . (2 Replies)
Discussion started by: honey26
2 Replies

5. Shell Programming and Scripting

How to export table data to xml file?

Hi , I would like to get some suggestion from the experts. My requirement is to export oracle table data as an xml file. Any unix/linux tools, scripts available? Regards, (2 Replies)
Discussion started by: LinuxLearner
2 Replies

6. Shell Programming and Scripting

Export data from DB2 table to .txt file(space delimited)

Hi I need help on this. Its very urgent for me.. please try to help me out.. I have data in tables in DB2 database. I would like to export the data from DB2 tables into a text file, which has to be space delimited. so that I can carry out awk, grep operations on that file. I tried to export... (2 Replies)
Discussion started by: ss3944
2 Replies

7. UNIX for Dummies Questions & Answers

UNIX - File/Table/Data manipulation

Hi, I have a table (e.g.): a 1 e 4 5 6 b 2 r 4 4 2 c 5 r 3 7 1 d 9 t 4 4 9 . . What I need to do is to set the values of some values in column 2 to negative values. For example, the values 2 and 9 should become -2 and -9 in the modified file. How should I go about... (2 Replies)
Discussion started by: pc2001
2 Replies

8. Shell Programming and Scripting

help for writing shell script to export table data

Hi All, I need to write a shell script(ksh) to take the tables backup in oracle(exporting tables data). The tables list is not static, and those are selecting through dynamic sql query. Can any body help how to write this shell script. Thanks, (3 Replies)
Discussion started by: sankarg
3 Replies

9. Shell Programming and Scripting

unix script to export data from csv file to oracle database

Hello people, Need favour. The problem I have is that, I need to develop a unix shell script that performs recurring exports of data from a csv file to an oracle database. Basically, the csv file contains just the first name and last name will be dumped to an Unix server. The data from these... (3 Replies)
Discussion started by: vinayagan
3 Replies

10. UNIX for Dummies Questions & Answers

How to export data file from Unix

Hi, I am searching a way to export data file on Unix to SQL server on a daily bases. Any one has any ideas? Thanks in advance! (20 Replies)
Discussion started by: whatisthis
20 Replies

Featured Tech Videos