String to Map Conversion Hive

Question

I have a table having four columns.

C1    C2    C3    C4
--------------------
x1    y1    z1    d1
x2    y2    z2    d2

Now I want convert it into map data type having key and value pairs and load into separate table.

create table test
(
   level map<string,string>
)
row format delimited
COLLECTION ITEMS TERMINATED BY '&'
map keys terminated by '=';

Now I am using below sql to load data.

insert overwrite table test
select str_to_map(concat('level1=',c1,'&','level2=',c2,'&','level3=',c3,'&','level4=',c4) from input;

Select query on the table.

select * from test;
{"level1":"x1","level2":"y1","level3":"z1","level4":"d1=\\"}
{"level1":"x2","level2":"y2","level3":"z2","level4":"d2=\\"}

I didn't get why I am getting extra "=\ \" in last value.

I double check data but the issue persist.

Can you please help?

Solution

str_to_map(text, delimiter1, delimiter2) - Creates a map by parsing text Split text into key-value pairs using two delimiters. The first delimiter seperates pairs, and the second delimiter sperates key and value. If only one parameter is given, default delimiters are used: ',' as delimiter1 and '=' as delimiter2.

You can get this info by running this command:

describe function extended str_to_map

In your syntax there are two errors:

insert overwrite table test
select str_to_map(concat('level1=',c1,'&','level2=',c2,'&','level3=',c3,'&','level4=',c4) from input;

First is, one bracket ) is missing.

Second is, its not an error basically, you have not given the delimiters so the function is taking default values for delimiters, That's why your are getting ',' in your result.

To get the output in current format you should try this query:

insert overwrite table test
select str_to_map(concat('level1=',c1,'&','level2=',c2,'&','level3=',c3,'&','level4=',c4),'&','=') from input;

Comments

[MichaelChirico]

Note well that delimiter1 and delimiter2 are passed to split which means they'll be interpreted as regular expressions!