Sorting within collect_list() in hive

Question

Let's say I have a hive table that looks like this:

ID    event    order_num
------------------------
A      red         2
A      blue        1
A      yellow      3
B      yellow      2
B      green       1
...

I'm trying to use collect_list to generate a list of events for each ID. So something like the following:

SELECT ID, 
collect_list(event) as events_list,
FROM table
GROUP BY ID;

However, within each of the IDs that I group by, I need to sort by order_num. So that my resulting table would look like this:

ID    events_list
------------------------
A      ["blue","red","yellow"]
B      ["green","red"]

I can't do a global sort by ID and order_num before the collect_list() query because the table is massive. Is there a way to sort by order_num within collect_list?

Thanks!

sorting by id and order_num and performing a `collect_list` might be the only way. — Vamsi Prabhala, Jun 08 '18 at 19:03
I hope that's not the case! I know in google bigquery, you can do things like this: STRING_AGG(event, "" ORDER BY order_num ASC) AS event_list — Slyron, Jun 08 '18 at 19:25

score 6 · Accepted Answer · answered Jun 09 '18 at 22:04

So, I found the answer here. The trick is to use a subquery with a DISTRIBUTE BY and SORT BY statement. See below:

WITH table1 AS (
    SELECT 'A' AS ID, 'red' AS event, 2 AS order_num UNION ALL
    SELECT 'A' AS ID, 'blue' AS event, 1 AS order_num UNION ALL
    SELECT 'A' AS ID, 'yellow' AS event, 3 AS order_num UNION ALL
    SELECT 'B' AS ID, 'yellow' AS event, 2 AS order_num UNION ALL
    SELECT 'B' AS ID, 'green' AS event, 1 AS order_num
)

-- Collect it
SELECT subquery.ID, 
collect_list(subquery.event) as events_list
FROM (
SELECT
        table1.ID,
        table1.event,
        table1.order_num
    FROM table1
    DISTRIBUTE BY
        table1.ID
    SORT BY
        table1.ID,
        table1.order_num
) subquery
GROUP BY subquery.ID;

This method may not work as you wished in some big data compute engine. — Armstrongya, May 21 '21 at 08:21

score 4 · Answer 2 · answered Jun 08 '18 at 22:09

4

The function sort_array() should sort the collect_list() items

select ID, sort_array(collect_list(event)) as events_list,
from table
group by ID;

answered Jun 08 '18 at 22:09

nobody

10,892
8
45
63

6

But can it sort the array by another column (e.g. the order_num column)? It doesn't seem like it. – Slyron Jun 09 '18 at 00:31

Vince Liu · Answer 3 · 2022-06-01T08:04:34.287

this my first answer question of stack overflow. but the answer is very very userful.

WITH table1 AS (
    SELECT 'A' AS ID, 'red' AS event, 2 AS order_num UNION ALL
    SELECT 'A' AS ID, 'blue' AS event, 1 AS order_num UNION ALL
    SELECT 'A' AS ID, 'yellow' AS event, 3 AS order_num UNION ALL
    SELECT 'B' AS ID, 'yellow' AS event, 2 AS order_num UNION ALL
    SELECT 'B' AS ID, 'green' AS event, 1 AS order_num
)

select ID 
       ,sort_array(collect_list(struct(order_num, item_score))).col2 as item_list
from (
        select ID
               ,event
               ,order_num
               ,concat(event, ':', order_num) as item_score
        from table1
     ) t0 
group by ID

score 1 · Answer 4 · edited Aug 07 '19 at 22:07

1

Try the following:

WITH tmp AS (
  SELECT * FROM data DISTRIBUTE BY ID SORT BY ID, order_num desc
)
SELECT ID, collect_list(event)
FROM tmp
GROUP BY ID

edited Aug 07 '19 at 22:07

Tomerikoo

18,379
16
47
61

answered Aug 07 '19 at 21:45

Rakesh Chintha

615
5
6

Sorting within collect_list() in hive

4 Answers4