题目引入:
请向用户推荐他的朋友收藏的商品,但是不要包括用户已经收藏过的商品。
--建表语句
DROP TABLE IF EXISTS friendship;
CREATE TABLE friendship (
`user1_id` varchar(32) ,
`user2_id` varchar(32)
) COMMENT '用户关系表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/sdc/rds/friendship';
DROP TABLE IF EXISTS user_favorites;
CREATE TABLE user_favorites (
`user_id` varchar(32) ,
`id` varchar(32)
) COMMENT '用户收藏表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
NULL DEFINED AS ''
LOCATION '/warehouse/sdc/rds/user_favorites';
--插入数据
INSERT overwrite table friendship VALUES ('1','2'),('1','3'),('1','4'),('1','6'),('2','1'),('2','3'),('2','4'),('2','5'),('6','1');
INSERT overwrite table user_favorites VALUES ('1','88'),('2','23'),('3','24'),('4','56'),('5','11'),('6','23'),('2','77'),('3','77'),('6','88');
SQL代码:
select need.user1_id,collect_set(need.new_id) rep_id
from
(select distinct fs.user1_id,
uf.id
from friendship fs left join user_favorites uf
on fs.user1_id=uf.user_id ) lose
right join
( select t1.user1_id,
new_id
from(
select fs.user1_id,
all_id
from friendship fs left join (
select user_id,
collect_set(id) all_id
from user_favorites
group by user_id ) uf on fs.user2_id=uf.user_id
)t1
lateral view explode(t1.all_id) tmp as new_id) need
on need.user1_id=lose.user1_id and need.new_id=lose.id
where lose.id is null and lose.user1_id is null
group by need.user1_id;
spark引擎执行结果:
注意:该结果有误,分析两表join后的结果,发现左表没有数据显示!
最终结果没有起到过滤筛选的效果:
更换为MR引擎执行:
结果如下图所示,该数据符合预期!
结论:猜想spark引擎在执行计算时与hive解析出来的执行代码存在兼容性问题。