title: Redshift COPY failing to read tar.gz from S3. Error code 1216. Missing newline: Unexpected character 0x2f found at location 1
body: <h3>tar --list</h3>
  
  <p>When I list the contents of the tar.gz file I get the following:</p>
  
  <pre class="lang-bash prettyprint-override"><code>$ tar --list --verbose --file /tmp/tmp.tar.gz | head -5
  drwxrwxr-x user/user     0 2015-07-22 19:51 ./
  -rw-rw-r-- user/user 113376 2015-07-13 06:29 ./NASDAQ_20140324.txt
  -rw-rw-r-- user/user 116101 2015-07-13 06:29 ./NASDAQ_20140602.txt
  -rw-rw-r-- user/user 120710 2015-07-13 06:30 ./NASDAQ_20140822.txt
  -rw-rw-r-- user/user 123969 2015-07-13 06:31 ./NASDAQ_20141104.txt
  </code></pre>
  
  <hr>
  
  <h3>zcat</h3>
  
  <p>When I <code>zcat</code> the file I get:</p>
  
  <pre class="lang-bash prettyprint-override"><code>$ zcat /tmp/tmp.tar.gz | head -5
  ./0000775000175000017500000000000012553663674010514 5ustar  useruser./NASDAQ_20140324.txt0000664000175000017500000033534012550547030013173 0ustar  useruserAAIT,D,20140324,31.1,31.29,30.97,31.11,14600
  AAL,D,20140324,36.25,36.86,36.03,36.8,6514500
  AAME,D,20140324,3.71,3.75,3.71,3.73,5900
  AAOI,D,20140324,25.76,26.15,24.84,25.81,213300
  AAON,D,20140324,19.2267,19.2933,18.8667,19.1667,149700
  </code></pre>
  
  <hr>
  
  <h3>stl_load_errors</h3>
  
  <p>The issue from <code>stl_load_errors</code> (listing pertinent columns only) for the <code>&lt;files&gt;</code> found in the <code>s3://&lt;bucket&gt;/&lt;key&gt;</code> are:</p>
  
  <pre><code>filename        =&gt;  &lt;file&gt;.tar.gz
  line_number     =&gt;  1
  colname         =&gt;  (empty)
  type            =&gt;  (empty)
  col_length      =&gt;  (empty)
  position        =&gt;  0
  raw_line        =&gt;  ./
  raw_field_value =&gt;  (empty)
  err_code        =&gt;  1216
  err_reason      =&gt;  Missing newline: Unexpected character 0x2f found at location 1
  </code></pre>
  
  <p>Breaking this down further:</p>
  
  <ul>
  <li><code>error_code</code> of <code>1216</code> is <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_Load_Error_Reference.html" rel="nofollow">Invalid input line.</a></li>
  <li><code>error_reason</code> has <code>0x2f</code> which is the <a href="http://www.fileformat.info/info/unicode/char/2f/index.htm" rel="nofollow">forward slash UTF-8 character</a></li>
  </ul>
  
  <blockquote>
    <p><em>Note</em>: On <code>line_number</code> = <code>1</code>, at the <code>position</code> of <code>0</code>, the
    <code>raw_line</code> has <code>./</code>, which apart from the period (<code>.</code>) is the forward
    slash character mentioned in the <code>error_reason</code></p>
  </blockquote>
  
  <p><strong>This seems to be consistent with what the <code>zcat</code> output provides</strong>, which has a malformed first line. Whether this is a red herring or not, I do not know.</p>
  
  <hr>
  
  <p><strong>But wait, there's more...</strong></p>
  
  <blockquote>
    <p>The text files originally come zipped, so I convert the <code>zip</code> archive
    files into <code>tar.gz</code> archive files in this manner ...</p>
  </blockquote>
  
  <ol>
  <li>zip files are <code>unzip</code>ed into a temp dir</li>
  <li>text files in the temp dir are transformed<br>
  2.1. <code>sed</code> removes a header line from the file and pipes into ...<br>
  2.2. <code>awk</code> prepends a column to the output and saves to a temp text file<br>
  2.3. <code>mv</code> just renames the temp file to the original file name within the temp working directory</li>
  <li>tar.gz file is create from the transformed temp files</li>
  </ol>
  
  <h3>1.</h3>
  
  <pre class="lang-bash prettyprint-override"><code>unzip -q "${in_archive_file_path}" -d "${tmp_working_dir}"
  </code></pre>
  
  <h3>2.</h3>
  
  <pre class="lang-bash prettyprint-override"><code>for in_file_path in `find "${tmp_working_dir}" -type f -iname "*_????????.txt" | sort -n`;
  do  
      sed -e "1{/^${quote_header_mask}/d;}" "${in_file_path}" |
      awk -v in_var="${exchange}" '{print in_var,$0}' OFS=, &gt; "${tmp_working_dir}/tmp.txt"
      mv -f "${tmp_working_dir}/tmp.txt" "${in_file_path}"
  done
  </code></pre>
  
  <p>and <code>quote_header_mask="&lt;ticker&gt;,&lt;date&gt;,&lt;open&gt;,&lt;high&gt;,&lt;low&gt;,&lt;close&gt;,&lt;vol&gt;"</code></p>
  
  <h3>3.</h3>
  
  <pre class="lang-bash prettyprint-override"><code>tar c -C "${tmp_working_dir}/" . | pigz --best -p4 &gt; "${working_dir}/tmp.tar.gz"
  mv -f "${working_dir}/tmp.tar.gz" "${out_file_path}"
  </code></pre>
  
  <p><code>working_dir</code> is the parent to <code>tmp_working_dir</code></p>
  
  <hr>
  
  <h3>COPY</h3>
  
  <pre class="lang-sql prettyprint-override"><code>copy source.quote_daily
  (
      exchange_code
      ,ticker_code    
      ,date_key_local
      ,price_open     
      ,price_high     
      ,price_low      
      ,price_close    
      ,volume
  )
  from 's3://&lt;bucket&gt;/&lt;key&gt;' 
  credentials 'aws_access_key_id=&lt;key value&gt;;aws_secret_access_key=&lt;secret key value&gt;' 
  delimiter  ','
  gzip
  trimblanks
  compupdate off
  statupdate off
  ;   
  </code></pre>
  
  <hr>
  
  <h3>Question(s)</h3>
  
  <ol>
  <li>Is <code>zcat</code> pointing me in the right direction with respect to what <code>Redshift</code> will "see" when decompressing the archive ... ?</li>
  <li>... which might mean my <code>tar.gz</code> creation script is screwing things up?</li>
  </ol>