SemanticException [Error 10085]: JOIN with a LATERAL VIEW is not supported 'id'

Question

I received a code from my superiors which was working on the old Hive Cluster. I am working on a new cluster and trying to run it using beeline function on Putty 'bf mycode.sql 'throws the error:

Error: Error while compiling statement: FAILED: SemanticException [Error 10085]: Line 6:24 JOIN with a LATERAL VIEW is not supported 'id' (state=42000, code=10085)

create table myTable as select distinct AtypeId
,cast(from_unixtime(t.timestamp) as date) as date
,C
,t.id
,t.marketid
from File1 LATERAL VIEW explode(eventlist) exploded_table as t
join File2
on oldID=t.id
order by AtypeId,date;

Any idea what I should do?

Answer 1

Try after changing your query as below:

create table myTable as
select t.* from
(
  select distinct 
     AtypeId
     ,cast(from_unixtime(t.timestamp) as date) as date
     ,C
     ,t.id
     ,t.marketid
  from 
     File1 LATERAL VIEW explode(eventlist) exploded_table
) as t join File2 on oldID=t.id
order by AtypeId,date;

There are certain restriction on using joins and lateral view together for ex you can not do lateral view with map join.

One thing I have a doubt is, in older version it might be doing something different in the join so was not throwing any error. You need to look at explain plan for both versions to understand that better.

Answer 2

If anyone facing the issue, this worked for me.

create table myTable as select distinct AtypeId
,cast(from_unixtime(t.timestamp) as date) as date
,t.id
,t.marketid
from File1 LATERAL VIEW explode(eventlist) exploded_table as t
order by AtypeId,date;

create table myTable2 as select distinct AtypeId
,date
,C
,id
,marketid
from myTable
inner join File2
on oldID=id;