title: converting parquet files in S3 to CSV and store back in S3
body: <p><strong>Information:</strong></p>
  <p>I have parquet files stored in S3 which I need to convert into CSV and store back into S3.
  the way I have the parquet files structured in S3 is as so:</p>
  <pre><code>2019
  2020
   |- 01
   ...
   |- 12
     |- 01
     ...
     |- 29
       |- part-0000.snappy.parquet
       |- part-0001.snappy.parquet
       ...
       |- part-1000.snappy.parquet
       ...
  </code></pre>
  <p><strong>The solution required:</strong></p>
  <ul>
  <li>Any AWS tooling (needs to use lambda, no EC2, ECS) (open to suggestions though)</li>
  <li>That the CSV files keep their headers during conversion (if they are split up)</li>
  <li>That the CSV retain are original information and have no added columns/information</li>
  <li>That the converted CSV file remain around 50-100MB</li>
  </ul>
  <p><strong>The solution I have already tried:</strong></p>
  <p><em><strong>&quot;entire folder method&quot;</strong></em></p>
  <p>Using Athena <code>CREATE EXTERNAL TABLE</code> -&gt; <code>CREATE TABLE AS</code> on the entire data folder (e.g: s3://2020/06/01/)</p>
  <p>fig: #1</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE EXTERNAL TABLE IF NOT EXISTS database.table_name (
           value_0 bigint,
           value_1 string,
           value_2 string,
  ) 
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  WITH SERDEPROPERTIES ( 'serialization.format' = '1' )
  LOCATION 's3://2020/06/01' TBLPROPERTIES ('has_encrypted_data'='false')
  </code></pre>
  <p>fig: #2</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE database.different_table_name
  WITH ( format='TEXTFILE', field_delimiter=',', external_location='s3://2020/06/01-output') AS
  SELECT * FROM database.table_name
  </code></pre>
  <p>doing this &quot;entire folder method&quot; works at converting parquet to CSV but leaves the CSV files at around 1GB+ size which is way too large. I tried creating a solution to split up the CSV files (thanks to help from this <a href="https://docs.aws.amazon.com/pinpoint/latest/developerguide/tutorials-importing-data-lambda-function-input-split.html" rel="nofollow noreferrer">guide</a>) but it failed since lambda has a 15-minute limit &amp; memory constraints which made it difficult to split about all these 1GB+ CSV files into about 50-100MB files.</p>
  <p><em><strong>&quot;single file method&quot;</strong></em></p>
  <p>using the same <code>CREATE EXTERNAL TABLE</code> (see fig: #1) and</p>
  <p>fig: #3</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE database.different_table_name
  WITH ( format = 'TEXTFILE', field_delimiter=',', external_location = 's3://2020/06/01-output') AS
  SELECT *, &quot;$path&quot; FROM database.table_name
  WHERE &quot;$path&quot; LIKE 's3://2020/06/01/part-0000.snappy.parquet';
  </code></pre>
  <p>doing this &quot;single file method&quot; required me to integrate AWS SQS to listen to events from S3 for objects created in the bucket which looked for <code>.snappy.parquet</code>. this solution converted the parquet to CSV and created CSVs which fit the size requirements. the only issue is that the CSVs were missing headers, and had additional fields which never existed in the parquet in the first place such as the entire bucket location.</p>