Map type variable in hive

hive, hiveql

Solution

Let's assume you have the following table:

describe test;
name      string    
ph        string    
category  map<string,int>

select * from test;
name    ph  category
Name1   ph1 {"type":1000,"color":200,"shape":610}
Name2   ph2 {"type":2000,"color":200,"shape":150}
Name3   ph3 {"type":3000,"color":700,"shape":167}

Accessing the map column :

select ph, category["type"], category["color"] from test;
ph1    1000    200
ph2    2000    200
ph3    3000    700

An equivalent using a Hive variable:

set hivevar:nameToID=
   map("t", category["type"], "c", category["color"], "s", category["shape"]);

select ph, ${nameToID}["t"], ${nameToID}["c"] from test;
ph1    1000    200
ph2    2000    200
ph3    3000    700

This works on Hive 0.9.0

Problem

I am having trouble trying to define map type in hive. According to Hive Manual there definitely is a map type, unfortunately there aren't any examples on how to use it. :-( Suppose, I have a table (users) with following columns: ``` Name Ph CategoryName ``` This "CategoryName" column has specific set of values. Now I want to create a hashtable that maps CategoryName to CategoryID. I tried doing: ``` set hivevar:nameToID=map('A',1,'B',2); ``` I have 2 questions: When I do `set hivevar:${nameToID['A']}` I thought that it would print out value as 1. But I get "${hivevar:nameToID['A']} is undefined" I am not sure how can I say something like, `select name, ph, ${nameToID[CategoryName]} from users`

Original source