CODEWITHSUNDEEP
sqlhadoophivehiveql
Jack Daniel
Jack Daniel

Reputation: 2611

Hive - Explode in JOIN Condition

I want to do explode on a column in a join condition. 

select
t1.a,t2.b, t2.c
from table1 AS t1
join (select b,LATERAL VIEW explode(ABCD) AS c from person) AS t2 ON (t1.c=t2.c)

But, this query is failing. I suspect the subquery is not working as expected. In the subquery, I have selected two columns, one is column b and another is column c, which is an explode.

Is this way of writing subquery is correct? If wrong, how can I achieve this.

Error:

Error while compiling statement: FAILED: SemanticException Cannot do equality join on different types: string and map<bigint,array<string>>

Upvotes: 0

Views: 5928

Answers (1)

dalin qin
dalin qin

Reputation: 126

CREATE TABLE people(b int,abcd array<String> ); 

INSERT INTO people SELECT 1, ARRAY("bob", "alice", "tom") from dummy limit 1;
INSERT INTO people SELECT 2, ARRAY("john", "mike", "jack") from dummy limit 1;

create table table1(name string, salary int);
insert into table1 values("bob",1000);
insert into table1 values("alice",500);

hive> select b,c  from people LATERAL VIEW explode(ABCD) ep AS c;
OK
1       bob
1       alice
1       tom
2       john
2       mike
2       jack


hive> select
t1.salary,t2.b, t2.c
from table1 AS t1
join (select b,c  from people LATERAL VIEW explode(ABCD) ep AS c) AS t2 ON (t1.name=t2.c)

OK
1000    1       bob
500     1       alice

Upvotes: 3

Related Questions