Can we query map collection using hive

Question

I have a table having map column type(text,int) in hive.I want to apply some calculation on some specific column in that map.

eg : support in table x i have map collection and i have inserted int data id userid map (age:24,login:12) id userid map(age:54,login:13) . . Now i want count of user whose age>15 or sum of login id userwise.

Is this possible to get result in hive query and how? Thanks in advance.

Answer 1

You can do it with this queries. Lets name your map "user"

Count users with age > 15

hive> select count(*) from x where user['age'] > 15;

And lets sum logins otherwise

hive> select sum(user['login']) from x where user['age'] < 16;

If you need to cast values to int, use:

cast(user['age'] as int)