title: How to insert data into array of Struct in Hive
body: <p>I need to create an array of struct in Hive so that for one ID, I can put multiple structs in an array of struct. </p>
  
  <p>I have created below table</p>
  
  <pre><code>CREATE TABLE if not exists tbl1
  (
  sess_id STRING
  , source_start_ts STRING
  , source_end_ts STRING
  ,Node_String STRUCT &lt; Node: STRING, Time_Spent:STRING, 
  Txn_Type: STRING, Txn_Status: STRING, Call_Status: STRING &gt;
  )
   STORED AS ORC
  </code></pre>
  
  <p>and then created second table which has array of above struct (I could have array of struct on the first table, but I tried that too and failed)</p>
  
  <pre><code>CREATE TABLE if not exists tbl2
  (
   sess_id STRING
  ,Col2 Array&lt;STRUCT &lt; Node: STRING, 
  Time_Spent:STRING, Txn_Type: STRING, Txn_Status: STRING, 
  Call_Status: STRING &gt;&gt;
  ) STORED AS ORC
  </code></pre>
  
  <p>However, when using below collect_set to populate it, I get an error</p>
  
  <pre><code>insert into table tbl2
  select sess_id
  , collect_set(Node_String) as Col2
  from tbl1
  where sess_id = 'abc'
  group by sess_id
  </code></pre>
  
  <p>here is the error</p>
  
  <blockquote>
    <p>SQL Error [40000] [42000]: Error while compiling statement: FAILED:
    UDFArgumentTypeException Only primitive type arguments are accepted
    but
    struct
    was passed as parameter 1.</p>
  </blockquote>
  
  <p>I guess collect_set does not accept struct type. Is there any function that does this?</p>
  
  <p>Here is an example</p>
  
  <pre><code>id, source_start_dt, source_end_dt, Node_string
  1,'2019-01-01','2019-01-02' , {"node1","10s","activation", "123", "failed"}
  1,'2019-01-01','2019-01-02', {"node2","120s","activation", "123", "Logged"}
  1,'2019-01-01','2019-01-02', {"node3","450s","activation", "123", "completed"}
  </code></pre>
  
  <p>As you can see above, there are multiple Node_String with different struct fields for each ID. ID '1' has 3 rows and in order to roll those three row up in one, I used collect_set</p>
  
  <p>Thanks</p>