title: Collect to a Map in Hive
body: <p>I have a Hive table such as</p>
  
  <pre><code>id  |  value
  -------------
  A      1
  A      2
  B      3
  A      4
  B      5
  </code></pre>
  
  <p>Essentially, I want to mimic Python's <code>defaultdict(list)</code> and create a map with <code>id</code> as the keys and <code>value</code> as the values.</p>
  
  <p><strong>Query:</strong></p>
  
  <pre><code>select COLLECT_TO_A_MAP(id, value)
  from table
  </code></pre>
  
  <p><strong>Output:</strong></p>
  
  <pre><code>{A:[1,2,4], B:[3,5]}
  </code></pre>
  
  <p>I tried using <a href="https://github.com/klout/brickhouse/tree/master/src/main/java/brickhouse/udf/collect" rel="noreferrer">klout's</a> <code>CollectUDAF()</code> but it appears this will not append the values to an array, it will just update them.  Any ideas?</p>
  
  <p><strong>EDIT:</strong>
  Here is a more detailed description so I can avoid answers referencing that I try functions in the Hive documentation.  Suppose I have a table</p>
  
  <pre><code>num    |id    |value
  ____________________
  1       A      1
  1       A      2
  1       B      3
  2       A      4
  2       B      5
  2       B      6
  </code></pre>
  
  <p>What I am looking for is for a UDAF that provides this output</p>
  
  <pre><code>num     |new_map
  ________________________
  1       {A:[1,2], B:[3]}
  2       {A:[4], B:[5,6]}
  </code></pre>
  
  <p>To this query</p>
  
  <pre><code>select num
        ,COLLECT_TO_A_MAP(id, value) as new_map
  from table
  group by num
  </code></pre>
  
  <p>There is a workaround to achieve this.  It can be mimicked by using <em>Klout's</em> (see above referenced UDAF) <code>CollectUDAF()</code> in a query such as</p>
  
  <pre><code>add jar '~/brickhouse/target/brickhouse-0.6.0.jar'
  create temporary function collect as 'brickhouse.udf.collect.CollectUDAF';
  
  select num
         ,collect(id_array, value_array) as new_map
  from (
        select collect_list(id) as id_array
              ,collect_list(value) as value_array
              ,num
        from table
        group by num
       ) A
  group by num
  </code></pre>
  
  <p>However, I would rather not write a nested query.</p>
  
  <p><strong>EDIT #2</strong></p>
  
  <p>(As referenced in my original question) I have already tried using <em>Klout's</em> <code>CollectUDAF()</code>, even in the instance where you pass it two parameter and it creates a map.  The output from that is (if applied to the dataset in my 1st edit)</p>
  
  <pre><code>1    {A:2, B:3}
  2    {A:4, B:6}
  </code></pre>
  
  <p>As stated in my original question, it doesn't collect the values to an array it just collects the last one (or updates the array).</p>