Complex Data Types – Part-3

In the previous article, we looked at how to work with the complex datatype “Array.” Now, we’ll look at the “MAP,” which is a another complex datatype.

Before we proceed for the exercise, let us know what MAP data type is.

Map is a complex data type in Apache Hive that can store Key-Value pairs. Values from a map can be accessed using the keys. It is an unordered collection of key-value pairs. Keys must be of primitive types. Values can be of any type.

We are going to consider the below data set to learn “Complex Data Types” in Apache Hive. The female-male ratio in top-ranked universities in the United States, the United Kingdom, and Australia is seen in this figure.

Male_Female_Ratio_Pic

In the above “Female:Male Ratio” column, if you see the values are in pairs. Male and Female are the keys separated by commas (,) and are assigned with some values separated by colons (:). Let’s implement it in Apache Hive.

CREATE TABLE tbRatio(
Rnk Int, University String, Ratio Map<String, Int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
COLLECTION ITEMS TERMINATED BY ‘,’
MAP KEYS TERMINATED BY ‘:’;

LOAD DATA LOCAL INPATH ‘Desktop/MAP_dataset.csv’ INTO TABLE tbRatio;

SELECT * FROM tbRatio;

MAP_Script_Implementation

Now, lets query the table using MAP keys.

SELECT ratio[‘Male’] FROM tbRatio WHERE Rnk=1
SELECT ratio[‘Male’], ratio[‘Female’] FROM tbRatio WHERE Rnk=1
SELECT ratio[‘Male’], ratio[‘Female’] FROM tbRatio WHERE Rnk BETWEEN 1 AND 5;

MAP_Queries

Hope you liked this post.

Please do subscribe for more interesting updates.

3 comments

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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