title: Azure Data Factory - Data Wrangling with Data Flow - Array bug
body: <p><strong>Azure Data Factory - Data Wrangling with Data Flow - Array bug.</strong></p>
  <p>I have a tricky firewall log file to wrangle using Azure data factory.    The file consists of  4 tab-separated columns.  Date and Time, Source, IP and Data.</p>
  <p>The Data column consists of key-value pairs separated with equal signs and text delimited by double-quotes.  The challenge is that the data column is inconsistent and contains any number of key-value pair combinations.</p>
  <p>Three lines from the source file.</p>
  <pre><code>2022-02-13 00:59:59 Local7.Notice   192.168.40.1    date=2022-02-13 time=00:59:59 devname=&quot;NoHouse&quot; devid=&quot;FG100ETK18006624&quot; eventtime=1644706798637882880 tz=&quot;+0200&quot; logid=&quot;0000000013&quot; type=&quot;traffic&quot; subtype=&quot;forward&quot; level=&quot;notice&quot; vd=&quot;root&quot; srcip=192.168.41.200 srcport=58492 srcintf=&quot;port1&quot; srcintfrole=&quot;undefined&quot; dstip=216.239.36.55 dstport=443 dstintf=&quot;wan1&quot; dstintfrole=&quot;undefined&quot; srccountry=&quot;Reserved&quot; dstcountry=&quot;United States&quot; sessionid=137088638 proto=6 action=&quot;client-rst&quot; policyid=5 policytype=&quot;policy&quot; poluuid=&quot;c2a960c4-ac1b-51e6-8011-6f00cb1fddf2&quot; policyname=&quot;All LAN over WAN1&quot; service=&quot;HTTPS&quot; trandisp=&quot;snat&quot; transip=196.213.203.122 transport=58492 appcat=&quot;unknown&quot; applist=&quot;block-p2p&quot; duration=6 sentbyte=3222 rcvdbyte=1635 sentpkt=14 rcvdpkt=8 srchwvendor=&quot;Microsoft&quot; devtype=&quot;Computer&quot; osname=&quot;Debian&quot; mastersrcmac=&quot;00:15:5d:29:b4:06&quot; srcmac=&quot;00:15:5d:29:b4:06&quot; srcserver=0
  2022-02-13 00:59:59 Local7.Notice   192.168.40.1    date=2022-02-13 time=00:59:59 devname=&quot;NoHouse&quot; devid=&quot;FG100ETK18006624&quot; eventtime=1644706798657887422 tz=&quot;+0200&quot; logid=&quot;0000000013&quot; type=&quot;traffic&quot; subtype=&quot;forward&quot; level=&quot;notice&quot; vd=&quot;root&quot; srcip=192.168.41.200 srcport=58496 srcintf=&quot;port1&quot; srcintfrole=&quot;undefined&quot; dstip=216.239.36.55 dstport=443 dstintf=&quot;wan1&quot; dstintfrole=&quot;undefined&quot; srccountry=&quot;Reserved&quot; dstcountry=&quot;United States&quot; sessionid=137088640 proto=6 action=&quot;client-rst&quot; policyid=5 policytype=&quot;policy&quot; poluuid=&quot;c2a960c4-ac1b-51e6-8011-6f00cb1fddf2&quot; policyname=&quot;All LAN over WAN1&quot; service=&quot;HTTPS&quot; trandisp=&quot;snat&quot; transip=196.213.203.122 transport=58496 appcat=&quot;unknown&quot; applist=&quot;block-p2p&quot; duration=6 sentbyte=3410 rcvdbyte=1791 sentpkt=19 rcvdpkt=11 srchwvendor=&quot;Microsoft&quot; devtype=&quot;Computer&quot; osname=&quot;Debian&quot; mastersrcmac=&quot;00:15:5d:29:b4:06&quot; srcmac=&quot;00:15:5d:29:b4:06&quot; srcserver=0
  2022-02-13 00:59:59 Local7.Notice   192.168.40.1    date=2022-02-13 time=00:59:59 devname=&quot;NoHouse&quot; devid=&quot;FG100ETK18006624&quot; eventtime=1644706798670487613 tz=&quot;+0200&quot; logid=&quot;0001000014&quot; type=&quot;traffic&quot; subtype=&quot;local&quot; level=&quot;notice&quot; vd=&quot;root&quot; srcip=192.168.41.180 srcname=&quot;GKHYPERV01&quot; srcport=138 srcintf=&quot;port1&quot; srcintfrole=&quot;undefined&quot; dstip=192.168.41.255 dstport=138 dstintf=&quot;root&quot; dstintfrole=&quot;undefined&quot; srccountry=&quot;Reserved&quot; dstcountry=&quot;Reserved&quot; sessionid=137088708 proto=17 action=&quot;deny&quot; policyid=0 policytype=&quot;local-in-policy&quot; service=&quot;udp/138&quot; trandisp=&quot;noop&quot; app=&quot;netbios forward&quot; duration=0 sentbyte=0 rcvdbyte=0 sentpkt=0 rcvdpkt=0 appcat=&quot;unscanned&quot; srchwvendor=&quot;Intel&quot; osname=&quot;Windows&quot; srcswversion=&quot;10 / 2016&quot; mastersrcmac=&quot;a0:36:9f:9b:de:b6&quot; srcmac=&quot;a0:36:9f:9b:de:b6&quot; srcserver=0
  </code></pre>
  <p>My strategy for wrangling this data set is as follows.</p>
  <ol>
  <li><p>Source the data file from azure data Lake Using a tab-delimited CSV
  data set. This successfully delivers the source data in four columns
  to my data flow.</p>
  </li>
  <li><p>Add a surrogate key transformation to add an incrementing key value to each row of data.</p>
  </li>
  <li><p>Add a derived column,
  with the following function.<br />
  regexSplit(Column_4,'\s(?=(?:[^&quot;]<em>([&quot;])[^&quot;]</em>\1)<em>[^&quot;]</em>$)')
  This splits the data by spaces ignoring the spaces between semicolons.</p>
  </li>
  <li><p>Then the unfold creates a new record for each item in the array while preserving the
  other column values.<br />
  unfold(SplitBySpace)</p>
  </li>
  <li><p>Then split the key-value pairs into their represented value and key by the Delimiter equals.</p>
  </li>
  <li><p>The final step would then be to unpivot the data back into columns with the respected values grouped by the surrogate key added in step 2.</p>
  </li>
  </ol>
  <p>This all sounds good but unfortunately step 5 fails with the following error.  “Indexing is only allowed on the array and map types”.</p>
  <p>The output after step 4.</p>
  <p><img src="https://i.stack.imgur.com/IKM6O.jpg" alt="enter image description here" /></p>
  <p>The unfold function returns an array according to the inspect tab, see below.  I would expect a string here!!</p>
  <p>Now in step 5, I split by “=”  with the expression split(unfoldSplitBySpace, '=') but this errors in the expression builder with the message “Split expect string type of argument”</p>
  <p>Changing the expression to split(unfoldSplitBySpace<a href="https://i.stack.imgur.com/IKM6O.jpg" rel="nofollow noreferrer">1</a>, '=')  remove the error from the expression Builder.</p>
  <p>BUT THEN the spark execution engines errors with “Indexing is only allowed on the array and map types”</p>
  <p><img src="https://i.stack.imgur.com/Q9tcp.jpg" alt="enter image description here" /></p>
  <p>The problem.<br />
  According to the Azure Data Factory UI, the output of the Unfold() function is an array type but when accessing the array elements or any other function the spark engine does not recognise the object as in array type.</p>
  <p>Is this a bug in the execution or do I have a problem in my understanding of how the data factory and a spark engine understand arrays?</p>