Important built-in function in Hive

Posted on by Sumit Kumar

(I)explode() and posexplode():-

explode() takes in an array (or a map) as an input and outputs the elements of the array (map) as separate rows.

Below example will help to understand explode() better.

1)Create example data set that having only one column  as Array<int>.

sumit2955@hadoop:~$ cat array_exm1
100,200,300,500
400,200,201
300,45
101
2)create table and load array_exm1 into that table.
hive> create table array1(num array<int>)
    > row format delimited 
    > collection items terminated by ',';
OK
Time taken: 1.157 seconds
hive> load data local inpath '/home/sumit2955/array_exm1' into table array1;
Loading data to table default.array1
OK
Time taken: 1.461 seconds
hive> select * from array1;
OK
[100,200,300,500]
[400,200,201]
[300,45]
[101]

hive> select num[0] from array1;
OK
100
400
300
101
hive> select explode(num) from array1;
OK
100
200
300
500
400
200
201
300
45
101
(3)posexplode() is similar to explode but instead of just returning the elements of the array it returns 
the element as well as its position in the original array.
hive> select posexplode(num) from array1;
OK
0       100
1       200
2       300
3       500
0       400
1       200
2       201
0       300
1       45
0       101

About ATI Base Author: Sumit Kumar

Author of Hadoop tutorial View all posts by Sumit Kumar →

Got Something To Say:

Your email address will not be published. Required fields are marked *

*

*


Planet Polaris

Website Support
website support at Planet Polaris