How to insert a key-value pair into a hive map?

Question

Based on the following tutorial , Hive has a map type. However, there seems to be no documentary way to insert a new key-value pair into the hive map using SELECT with some UDF or built-in function. Is it possible?

As an explanation, suppose I have a table named foo with one column typed by map , named column_containing_map .

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

The request may look like this:

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

Then the bar table will contain the same cards as the foo table, except that each card in bar will have an additional key-value pair.

Answer 1

Consider that you have a student table that has student tags for various topics.

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

You can insert values ​​directly into the table as shown below.

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

Here the empinfo table can be any table in your database. And the results:

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

Answer 2

Sorry, I didn’t quite understand this. What do you mean by with some UDF or inline function ? If you want to insert into the table with the Map , it is similar to any other data type. For instance:

I have a table called complex1 created as follows:

 CREATE TABLE complex1(c1 array<string>, c2 map<int,string> ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '\n'; 

I also have a file called com.txt that contains the following: Mohammad-Tariq, 007: Bond

Now I will load this data into the table created above:

load inpath data '/inputs/com.txt' into table complex1;

So this table contains:

select * from complex1;

Ok

["Mohammad", "Tariq"] {7: "Bond"}

Time: 0.062 seconds

I have another table called complex2:

 CREATE TABLE complex2(c1 map<int,string>); 

Now, to select the data from complex1 and paste into complex2, I will do the following:

Insert into table complex2 select c2 from complex1;

Scan the table for cross-validation:

select * from complex2;

Ok

{7: Bond}

Time: 0.062 seconds

NTN

Answer 3

I think the join function from brickhouse will do what you need. By slightly modifying the query in the original question, it will look something like this.

 SELECT combine(column_containing_map, str_to_map("NewKey:NewValue")) FROM foo; 

The limitation in this example is that str_to_map creates a MAP <STRING, STRING>. If your hive map contains other primitive types for keys or values, this will not work.

Answer 4

It seems we cannot insert a new pair to add an existing map. We can go through all the keys and then add these key values ​​to a new map with your new key-value pairs. Thus, we can get a new map with new data.