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.
STR_TO_MAP:
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.
Big Data and SQL 
2 comments