sql - Hive - 在 JOIN 条件下爆炸

Question

我想在连接条件下对列进行爆炸。

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)

但是，这个查询失败了。我怀疑子查询没有按预期工作。在子查询中，我选择了两列，一列是b列，另一列是c列，这是一个爆炸。

这样子查询的写法对吗？如果错了，我该如何实现。

错误:

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

Answer 1

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