selecting from two tables and using lateral view hive

Question

I am trying to select from two tables in Hive, but it seems to be giving me an error

select b.item item1, street.id as street_id, street.name as street_name, 
c.color as color_id, 'cities' as city  
from mytable.first b, mytable.second c 
LATERAL VIEW EXPLODE(b.cities) citiestbl as street

This gives me a

Error while compiling statement: FAILED: SemanticException [Error 10085]: Line 1:120 JOIN with a LATERAL VIEW is not supported 'c'

If the syntax is not supported, then you cannot do what you want. — Gordon Linoff

Vamsi Prabhala Vamsi Prabhala · Accepted Answer · 2018-01-24T20:40:31

You are performing a cross join between first and second tables in the query and using a lateral view. As the error message suggests, join with a lateral view isn't supported.

Not sure if you need a cross join, but re-write the query as

select bc.item as item1, citiestbl.street, bc.color as color_id, bc.cities as city  
from (select b.item,c.color,b.cities 
      from mytable.first b 
      cross join mytable.second c) bc 
LATERAL VIEW EXPLODE(bc.cities) citiestbl as street 
--Note that citiestbl is a table alias and street is the column-alias for the exploded column
--Only the exploded column can be referred to in the select, which is street in this case

selecting from two tables and using lateral view hive

1 Answers