How can I insert a key-value pair into a hive map?

Question

Based on the following tutorial, Hive has a map type. However, there does not seem to be a documented way to insert a new key-value pair into a Hive map, via a SELECT with some UDF or built-in function. Is this possible?

As a clarification, suppose I have a table called foo with a single column, typed map, named column_containing_map.

Now I want to create a new table that also has one column, typed map, but I want each map (which is contained within a single column) to have an additional key-value pair.

A query might look like this:

CREATE TABLE IF NOT EXISTS bar AS
SELECT ADD_TO_MAP(column_containing_map, "NewKey", "NewValue") 
FROM foo;

Then the table bar would contain the same maps as table foo except each map in bar would have an additional key-value pair.

Answer 1

Consider you have a student table which contains student marks in various subjects.

hive> desc student;
id                      string
name                    string
class                    string
marks                   map<string,string>

You can insert values directly to table as below.

INSERT INTO TABLE student
SELECT STACK(1,
'100','Sekar','Mathematics',map("Mathematics","78")
)
FROM empinfo 
LIMIT 1;

Here 'empinfo' table can be any table in your database. And Results are:

100     Sekar   Mathematics     {"Mathematics":"78"}

Answer 2

for key-value pairs, you can insert like following sql:

INSERT INTO TABLE student values( "id","name",'class',

map("key1","value1","key2","value2","key3","value3","key4","value4") )

please pay attention to sequence of the values in map.