[Hive] 행을 그룹화하여 배열로 만들기 ( rows to array )

Sample Data

meta_col1	meta_col2	key	value
a	b	1	11
a	b	2	22
a	b	3	33

with aa as (
    select  'a' as meta_col1 , 'b' as meta_col2 , 1 as key, 11 as value
    union all
    select 'a' as meta_col1 , 'b' as meta_col2 , 2 as key, 22 as value
    union all
    select 'a' as meta_col1 , 'b' as meta_col2 , 3 as key, 33 as value
)
select *
from aa 


-- meta_col1	meta_col2	key	value
-- a		b		1	11
-- a		b		2	22
-- a		b		3	33

 

Result Data

meta_col1	meta_col2	array_key	array_val
a	b	[1,2,3]	[11,22,33]

 

Query

with aa as (
    select  'a' as meta_col1 , 'b' as meta_col2 , 1 as key, 11 as value
    union all
    select 'a' as meta_col1 , 'b' as meta_col2 , 2 as key, 22 as value
    union all
    select 'a' as meta_col1 , 'b' as meta_col2 , 3 as key, 33 as value
)
select meta_col1,
      meta_col2,
      COLLECT_SET(key) as array_key,
      COLLECT_SET(value) as array_value
      
from aa
group by meta_col1,
      meta_col2
      
      
-- meta_col1	meta_col2	array_key	array_value
-- a			b	[1,2,3]		[11,22,33]