0

I am a beginner in Hadoop/Hive. I did some research to find out a way to export results of HiveQL query to CSV. I am running below command line in Putty - 

Hive -e ‘use smartsourcing_analytics_prod; select *  from solution_archive_data limit 10;’ > /home/temp.csv;

However below is the error I am gettingenter image description here

ParseException line 1:0 cannot recognize input near 'Hive' '-' 'e'

I would appreciate inputs regarding this.

Karver01
  • 79
  • 2
  • 10

2 Answers2

1

One way I do such things is to create an external table with the schema you want. Then do INSERT INTO TABLE target_table ... Look at the example below:

CREATE EXTERNAL TABLE isvaliddomainoutput (email_domain STRING, `count` BIGINT) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ","
STORED AS TEXTFILE
LOCATION "/user/cloudera/am/member_email/isvaliddomain";

INSERT INTO TABLE isvaliddomainoutput

SELECT * FROM member_email WHERE isvalid = 1;

Now go to "/user/cloudera/am/member_email/isvaliddomain" and find your data.

Hope this helps.

kashmoney
  • 97
  • 7
1
  • Run your command from outside the hive shell - just from the linux shell.
  • Run with 'hive' instead of 'Hive'
  • Just redirecting your output into csv file won't work. You can do:
    hive -e 'YOUR QUERY HERE' | sed 's/[\t]/,/g' > sample.csv
    like was offered here: How to export a Hive table into a CSV file?
  • AkashNegi answer will also work for you... a bit longer though
Community
  • 1
  • 1
belostoky
  • 934
  • 2
  • 11
  • 22
  • The directory where the file - sample.csv is saved? I checked my user account folder but it is not there. – Karver01 Nov 29 '16 at 19:53
  • It will be saved in the location from which u run the command. Or you can redirect it to your desired location, for example /path/to/sample.csv. to find out where u r at run pwd command. – belostoky Nov 30 '16 at 08:13