How can I apply the Hive Explode / Lateral View multiple arrays?

Question

I have the data like below.

[{"aref":"4864190760901781","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760905386","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760906244","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760906269","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760906301","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760907010","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760907127","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760908786","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760909289","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760917217","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760917514","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760918728","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760928420","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760929006","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760931945","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760933107","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760936217","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760937835","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760941175","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null},{"aref":"4864190760942157","aprd":null},{"aref":null,"aprd":"UC"},{"aref":null,"aprd":null},{"aref":null,"aprd":null}]

How can I apply Hive Explode / Lateral View on my data?

I want to retrieve the two columns aref & aprd

Answer 1

Try this for lateral view explode.

 SELECT acol.aref    AS aref, 
           acol.aprd     AS aprd 
    FROM   your_table lateral view explode(yourcol_name) adtable AS acol;

Answer 2

For lateral view explode details refer this link:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

Depending upon your requirement 2 answers possible:

1. SELECT a.aref AS aref, a.aprd AS aprd FROM table_name LATERAL view EXPLODE(segemnt_colume_name) tablealis AS a;

In above query output you will only get rows which does not have any null value in struct of array column. Means values like {"aref":null,"aprd":null} will not generate any rows.

2. SELECT a.aref AS aref,a.aprd AS aprd from table_name LATERAL view OUTER EXPLODE(segemnt_colume_name) tablealis AS a;

Here we get all rows including rows containing null values in struct of array column.