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.

Accepted Answer

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"}
```

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

Solution

Problem