create table test (
item MAP<STRING, STRING>
);
and sometimes we want to iterate all the items inside the MAP as key-value pairs.
Hive offered such function called explode():
explode() takes in an array as an input and outputs the elements of the array as separate rows. UDTF's can be used in the SELECT expression list and as a part of LATERAL VIEW.
An example use of explode() in the SELECT expression list is as follows:
Consider a table named myTable that has a single column (myCol) and two rows:
Array<int> myCol |
---|
[1,2,3] |
[4,5,6] |
SELECT explode(myCol) AS myNewCol FROM myTable;
Will produce:
(int) myNewCol |
---|
1 |
2 |
3 |
4 |
5 |
6 |
the above is extracted from the official guide:
and here I am going to take a tour through how to explode the MAP structure and also how to explode multiple MAP structure
taking `test` table as an example:
hive>select * from test;
{"123":"abc"}
{"234":"bcd"}
now if we do :
hive>select key, value from test
lateral view explode(item) dummy_table as key, value;
123 abc
234 bcd
as we can see, explode will expand the MAP into multiple rows. and of course we can use key, value in any clause like 'group by' or 'sort by' etc.
and how about if we have multiple items which are all MAP structure, like :
create table test2 (
item1 MAP<STRING, STRING>,
item2 MAP<STRING, STRING>
)
hive>select * from test2;
{"123":"abc","234":"bcd"} {"123":"aaa","234":"bbb"}
now if we do the same query again:
hive>select key1, value1, key2, value2 from test2
lateral view explode(item1) dummy1 as key1, value1
lateral view explode(item2) dummy2 as key2, value2;
123 abc 123 aaa
123 abc 234 bbb
234 bcd 123 aaa
234 bcd 234 bbb
we see that Hive won't show just two lines, instead, it will try all combinations.
so now we have a problem, how about I wanna do sum up on item1["123"] ?
if the value of key "123" is not the alphabet but number instead, I should be able to sum up the value based on the key, right ?
but now, as we showed above, Hive will do combination, so the value will be duplicated!
Here is my solution, and simple:
hive>select key1, value1, key2, value2 from test2
lateral view explode(item1) dummy1 as key1, value1
lateral view explode(item2) dummy2 as key2, value2
where key1 = key2;
123 abc 123 aaa
234 bcd 234 bbb
so now if we do sum up like :
hive>select key1,SUM(value1), SUM(value2) from test2
lateral view explode(item1) dummy1 as key1, value1
lateral view explode(item2) dummy2 as key2, value2
where key1 = key2;
we will get the correct sum up value of every key.
I have arrays where the keys may not be same in both rows. Is there a way to do outer join so that, we get keys (and values) from either row?
ReplyDeletee.g.
123 abc 123 aaa
234 bcd 234 bbb
444 xyz
555 ppp
I have arrays where the keys may not be same in both rows. Is there a way to do outer join so that, we get keys (and values) from either row?
ReplyDeletee.g.
123 abc 123 aaa
234 bcd 234 bbb
444 xyz
555 ppp
very informative blog and useful article thank you for sharing with us , keep posting Hadoop Admin Online Training Hyderabad
ReplyDeleteTül perde modelleri
ReplyDeletesms onay
vodafone mobil ödeme bozdurma
nft nasıl alınır
ankara evden eve nakliyat
trafik sigortası
dedektör
web sitesi kurma
aşk kitapları