hivelateral-join

Hive lateral view explode with 2 table joins


Checking to see if this is possible in Hive:

Select a.col1,b.col1
from tableA a join tableB b on a.col1 = b.col1
lateral view explode(numcred) tableA  as creds
where creds.id = 9;

I can not find the answer in the docs. In short:

I want to JOIN on two tables AND LATERAL VIEW EXPLODE TABLEA

Seems simple enough but throws syntax issue.


Solution

  • select  a.col1
           ,b.col1
    
    from   (Select  a.col1
    
            from    tableA a 
                    lateral view explode(numcred) e as creds 
    
            where   e.creds.id = 9
            ) a
    
            join    tableB b 
    
            on      a.col1 = b.col1