title: Flatten JSON with array using AWS Glue crawler / classifier / ETL job
body: <p>I'm crawling following JSON file (it's a valid JSON) from s3 data lake.
  Inside there are 2 fields (device, timestamp) and an array of objects called &quot;data&quot;. Each object in the data array differs from one another.</p>
  <pre><code>{
    &quot;device&quot;: &quot;0013374838793C8&quot;,
    &quot;timestamp&quot;: &quot;2019-03-04T14:44:39Z&quot;,
    &quot;data&quot;: [
      { &quot;eparke_status&quot;: &quot;09&quot; },
      { &quot;eparke_x&quot;: &quot;FFF588&quot; },
      { &quot;eparke_y&quot;: &quot;000352&quot; },
      { &quot;eparke_z&quot;: &quot;000ACC&quot; },
      { &quot;eparke_temp&quot;: &quot;14.00&quot; },
      { &quot;eparke_voltage&quot;: &quot;4.17&quot; }
    ]
  }
  </code></pre>
  <p>Unfortunately, when I'm crawling with AWS Glue crawler schema cannot be inferred properly and what I got in Athena is not what I expect.</p>
  <p>Following listing shows a row of data from AWS Athena.</p>
  <pre><code>1   0013374838793C8 2019-03-05T13:11:41Z    [{eparke_status=0B, eparke_x=null, eparke_y=null, eparke_z=null, eparke_temp=null, eparke_voltage=null}, {eparke_status=null, eparke_x=FFF6D4, eparke_y=null, eparke_z=null, eparke_temp=null, eparke_voltage=null}, {eparke_status=null, eparke_x=null, eparke_y=000133, eparke_z=null, eparke_temp=null, eparke_voltage=null}, {eparke_status=null, eparke_x=null, eparke_y=null, eparke_z=000DA3, eparke_temp=null, eparke_voltage=null}, {eparke_status=null, eparke_x=null, eparke_y=null, eparke_z=null, eparke_temp=14.00, eparke_voltage=null}, {eparke_status=null, eparke_x=null, eparke_y=null, eparke_z=null, eparke_temp=null, eparke_voltage=4.17}]
  </code></pre>
  <p>As you can see for each object inside array schema is discovered &quot;wrongly&quot;. Each column in DB contains ALL of the array objects fields, the majority of which are just set as nulls, which is understandable because they are not found. The discovered schema is not what I'm looking for.<br />
  <strong>Expectations</strong></p>
  <p>Following listing shows <strong>expected</strong> form of a table row after crawling with AWS Glue.</p>
  <pre><code>1   0013374838793C8 2019-03-05T13:11:41Z    eparke_status=0B eparke_x=FFF6D4 eparke_y=000133 eparke_z=000DA3 eparke_temp=14.00 eparke_voltage=4.17
  </code></pre>
  <p><strong>What I have tried so far?</strong></p>
  <p>AWS Glue Classifiers
  To force schema I tried to use classifiers.</p>
  <pre><code>$.device $.timestamp $.eparke_status $.eparke_x $.eparke_y $.eparke_z $.eparke_temp $.eparke_voltage
  </code></pre>
  <p>and</p>
  <pre><code>$.device $.timestamp $.data[0].eparke_status $.data[1].eparke_x $.data[2].eparke_y $.data[3].eparke_z $.data[4].eparke_temp $.data[5].eparke_voltage
  </code></pre>
  <p>Still, final schema looks the same - all objects are packed inside each column.</p>
  <p>Any ideas how to address this issue?
  I'm also trying to configure ETL job with a custom script but failed so far.</p>