How to change or reset consumer offset in Kafka?
May 12, 2021How to deal with corrupt files in HDFS?
May 17, 2021Both explode and posexplode are User Defined Table generating Functions. UDTFs operate on single rows and produce multiple rows as output.
explode()
There are 2 flavors of explode, one flavor takes an Array and another takes a Map.
In the below example explode function will take in an Array and explode the array into multiple rows. So if we have 3 elements in the array we will end up with 3 rows.
0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> select array('Tom','Jerry','Emily'); +--------------------------+--+ | _c0 | +--------------------------+--+ | ["Tom","Jerry","Emily"] | +--------------------------+--+ 1 row selected (0.631 seconds)
0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> select explode(array('Tom','Jerry','Emily')); +--------+--+ | col | +--------+--+ | Tom | | Jerry | | Emily | +--------+--+ 3 rows selected (0.774 seconds)
Let’s also see the explode function which takes in a Map instead of an Array.
Maps are key value pairs. Here we have a Map with 3 elements with key as name and value as age.
0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> select map('Tom',10,'Jerry',20,'Emily',30); +-----------------------------------+--+ | _c0 | +-----------------------------------+--+ | {"Tom":10,"Jerry":20,"Emily":30} | +-----------------------------------+--+ 1 row selected (0.18 seconds)
Let’s apply explode() on the map. The map has 3 key value pairs so the explode function resulted in 3 rows.
0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> select explode(map('Tom',10,'Jerry',20,'Emily',30)) as (name, age); +--------+------+--+ | name | age | +--------+------+--+ | Tom | 10 | | Jerry | 20 | | Emily | 30 | +--------+------+--+ 3 rows selected (0.226 seconds)
posexplode()
Just like explode on array, posexplode also operates on arrays. Posexplode will take in an Array and explode the array into multiple rows and along with the elements in the array it will also give us the position of the element in the array.
Let’s see this with an example. As you can see, in addition to exploding the elements in the array the output also has the position of the element in the array.
0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> select posexplode(array('Tom','Jerry','Emily')) as (pos, name); +------+--------+--+ | pos | name | +------+--------+--+ | 0 | Tom | | 1 | Jerry | | 2 | Emily | +------+--------+--+ 3 rows selected (0.438 seconds)