CODEWITHSUNDEEP
hadoophivehiveql
Nabil
Nabil

Reputation: 1811

Hive Query array as field

I have two Hive table :

Client Table :

id,name,salary 
1 ,John, 10000
2 ,Melissa, 5000

Account Table : 

id,account_number,client_id
1 ,00920202, 1
2 ,00920203, 1
3 ,00920204, 1
4 ,00920205, 2
5 ,00920206, 2

I need a hive query that return this results : 

id,name,salary,accounts
1 ,John, 10000, {00920202, 00920203, 00920204}
2 ,Melissa, 5000, {00920205, 00920206}

Thanks in advance

Upvotes: 0

Views: 617

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use collect_list if you are sure the account numbers are unique. Else use collect_set which eliminates duplicates.

select c.id,c.name,c.salary,collect_list(a.account_number) as all_accounts
from client c
join account a on a.client_id=c.id
group by c.id,c.name,c.salary

Upvotes: 2

Related Questions