I ve been trying to work with hive to output to s3 . I have been successful at that but the resultant output is not comma separated but there is a delimiter such as ^A I suppose. I had also worked on using sqoop to import and export data from s3 to psql but I haven't been able to do that on hive, even if I get the solution to that it would work.
What I have tried doing is
set hive.io.output.fileformat=CSVTextFile; INSERT OVERWRITE DIRECTORY "s3n://akshayhazari/results" select * from books; This is the working:
Total jobs = 3 Launching Job 1 out of 3 Number of reduce tasks is set to 0 since there's no reduce operator Starting Job = job_1403776308919_0011, Tracking URL = Kill Command = /usr/local/hadoop/bin/hadoop job -kill job_1403776308919_0011 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2014-06-26 16:51:07,188 Stage-1 map = 0%, reduce = 0% 2014-06-26 16:51:29,868 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 2.95 sec MapReduce Total cumulative CPU time: 2 seconds 950 msec Ended Job = job_1403776308919_0011 Stage-3 is selected by condition resolver. Stage-2 is filtered out by condition resolver. Stage-4 is filtered out by condition resolver. Moving data to: s3n://akshayhazari/tmp/hive-hduser/hive_2014-06-26_16-50-41_646_3052840892739735120-1/-ext-10000 Moving data to: s3n://akshayhazari/results MapReduce Jobs Launched: Job 0: Map: 1 Cumulative CPU: 2.95 sec HDFS Read: 188 HDFS Write: 0 SUCCESS Total MapReduce CPU Time Spent: 2 seconds 950 msec OK Time taken: 55.726 seconds Where as I get a file such as 000000_0 which is unreadable but after downloading it and converting it to a txt gives me ^A delimiter file. I want to get an output as a csv or txt file directly and with comma or tab separated values . Even if you are able to use INSERT OVERWRITE DIRECTORY syntax to produce the above locally , it will be of great help as I would be able to work that on s3.
Adding detail to Original Question (This is just an added detail but the question still remains the same): I figured what I have to do is produce a gzipped output on s3. Also minimize on the s3 usage. Where as hive puts all its temp files on S3. So to optimize usage I did this.
hive> SET hive.exec.compress.output=true; hive> SET io.seqfile.compression.type=BLOCK; hive> SET mapred.output.compression.codec = org.apache.hadoop.io.compress.GzipCodec; hive> Insert overwrite directory "Books" select * from books; This is the Output in hdfs:
hduser@akshay:~$ hadoop fs -ls Books Found 1 items -rw-r--r-- 1 hduser supergroup 161 2014-06-27 11:45 Books/000000_0.gz Then I would use this to add stuff to s3:
hadoop fs -cp Books/000000_0.gz s3n://akshayhazari/results The output is not a text or csv and is ureadable. Even the delimiters are unreadable. Is there any work around for this in Hive or do I have to create a script to fix the file and delimiters.
Any help is appreciated
1 Answer
Depending on which version of Hive you're using, you may be able to do:
insert overwrite directory 's3n://akshayhazari/results' row format delimited fields terminated by ',' select * from books; I think that was added in Hive 0.11 or so.
Edit: turns out the above is only for local directories.
You can certainly also do:
create external table tmp_table(cols...) location 's3n://akshayhazari/results' row format delimited fields terminated by ','; insert into tmp_table select * from books; drop table tmp_table; To do pretty much the same thing but without specifying columns, you could do something like:
create table tmp_table(cols...) location 's3n://akshayhazari/results' row format delimited fields terminated by ',' as select * from books; alter table tmp_table set tblproperties('EXTERNAL'='TRUE'); drop table tmp_table; create-table-as-select has the restriction that you cannot create an external table, but I think you should be able to just mark it external after the fact, and then drop it.
4
