String Conversion To Map Key Values

In a key-value pair, there are two related data elements: the key is the name of a constant that determines the data set, and the content is the value that belongs to the set.

Below image depicts the key-value pair.

Such map keys can be handled by Hive by defining the column with MAP data type while creating the table. However, if the data is in a string, it still can be converted into map keys using STR_TO_MAP function.

Splits text into key-value pairs using two delimiters. Delimiter1 separates text into K-V pairs, and Delimiter2 splits each K-V pair. Default delimiters are ‘,’ for delimiter1 and ‘:’ for delimiter2.

Syntax: str_to_map(text[, delimiter1, delimiter2])

Below is an example:
SELECT STR_TO_MAP(‘2006:1234,2007:3456,2008:4567′,’,’,’:’);
Result: {“2008″:”4567″,”2007″:”3456″,”2006″:”1234”}

If you want to test if it is converted into desired output, use the below command.
SELECT col1[2006] FROM (
                   SELECT STR_TO_MAP(‘2006:1234,2007:3456,2008:4567′,’,’,’:’) col1
                                             ) x;
Result: 1234


Hope you liked this post.

Please do click on follow button to receive updates on latest posts.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s