title: Hive output to s3 with comma separated values and a .csv or .txt file format .An alternative like using sqoop to export from hive to s3 will also work
body: <p>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.</p>
  
  <p>What I have tried doing is </p>
  
  <pre><code>set hive.io.output.fileformat=CSVTextFile; 
  INSERT OVERWRITE DIRECTORY "s3n://akshayhazari/results" select * from books;
  </code></pre>
  
  <p>This is the working: </p>
  
  <pre><code>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 = http://localhost:8088/proxy/application_1403776308919_0011/
  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
  </code></pre>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <pre><code>hive&gt; SET hive.exec.compress.output=true; 
  hive&gt; SET io.seqfile.compression.type=BLOCK;
  hive&gt; SET mapred.output.compression.codec = org.apache.hadoop.io.compress.GzipCodec;
  hive&gt; Insert overwrite directory "Books" select * from books;
  </code></pre>
  
  <p>This is the Output in hdfs:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Then I would use this to add stuff to s3:</p>
  
  <pre><code>hadoop fs -cp Books/000000_0.gz s3n://akshayhazari/results
  </code></pre>
  
  <p>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. </p>
  
  <p>Any help is appreciated </p>