Merge N arrays into one?

Question

I have a table called connection which stores property_ids and their existing API connections. A property can have multiple apis on the same connection_id. A property can also have multiple connection_ids. The order of importance for the apis is ascending so API 1 has a higher importance than API 14.

With the above in mind, I 'm trying to select a single connection_id per property per day. Given the below data:

+------------+-------------+---------------+----------------+
| yyyy_mm_dd | property_id | connection_id |      apis      |
+------------+-------------+---------------+----------------+
| 2019-10-01 |         100 |         123   | ['8']          |
| 2019-10-01 |         100 |         200   | ['16']         |
| 2019-10-01 |         100 |           5   | ['1','2','14'] |
+------------+-------------+---------------+----------------+

I want the following returned (as connection_id 5 holds the lowest API connection):

+------------+-------------+---------------+
| yyyy_mm_dd | property_id | connection_id |
+------------+-------------+---------------+
| 2019-10-01 |         100 |           5   |
+------------+-------------+---------------+

I was thinking to achieve this, I could merge the arrays and then sort them ascending before selecting the item at index 0. However, I feel this might be overcomplicating it.

Under collection functions I don't see any merge function - https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-CollectionFunctions. Maybe this can be achieved without merging?

user_2700344 · Answer

If you need connection_id with lowest apis then you can sort array, then take record with lowest apis[0]

select yyyy_mm_dd, property_id, connection_id
  from
(
 select yyyy_mm_dd, property_id, connection_id, apis,
        row_number() over(partition by yyyy_mm_dd, property_id order by api0 ) rn
   from
      (
       select yyyy_mm_dd, property_id, connection_id, apis, sort_array(apis)[0] as api0
         from mytable
      )s
)s 
where rn=1;

And if array is string, not integer then it will not work with sort, you can explode array, cast it as int and take record with lowest API:

select yyyy_mm_dd, property_id, connection_id
  from
(
 select yyyy_mm_dd, property_id, connection_id, apis,
        row_number() over(partition by yyyy_mm_dd, property_id order by api ) rn
   from
      (
       select t.yyyy_mm_dd, t.property_id, t.connection_id, t.apis, cast(e.api as int) as api
         from mytable t
              lateral view explode(apis) e as api
      )s
)s 
where rn=1;

Merge N arrays into one?

1 Answers