3

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.

Emre
  • 5,976
  • 7
  • 29
  • 42
Don
  • 98
  • 1
  • 4
  • 10

2 Answers2

9
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
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
3

Not at my computer now, so no way to test this, but my guess is you'll have to write an inner query. Something like this:

SELECT
  a.col1,
  b.col1
FROM (
  SELECT
    dummy.col1
  FROM table_a dummy
  LATERAL VIEW EXPLODE(numcred) tableA as creds
  WHERE 
    creds.id = 9
) a
JOIN tableB b 
ON 
  a.col1 = b.col1
user1352683
  • 397
  • 1
  • 3
  • 14