如何绕过ODPS不支持的笛卡尔积的限制Cartesian product is not allowed without map join

最新推荐文章于 2024-06-25 08:57:14 发布

原创最新推荐文章于 2024-06-25 08:57:14 发布 · 9.7k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#算法 #hive #big data

大数据相关资料专栏收录该内容

16 篇文章

订阅专栏

本文介绍如何通过在SQL查询中添加临时字段，巧妙地解决ODPS中不允许无on的join导致的笛卡尔积问题，以满足将战区信息与tmp2表结合的需求。

如何绕过ODPS不支持的笛卡尔积的限制

需求情景再现

with tmp1 as (
select '保定五洲长城大街店' as a,'华北战区' as c
union all
select '新疆五洲乌鲁木齐店' as a,'西北战区' as c
)
,
tmp2 as (
select '保定五洲' as b
union
select '廊坊五洲' as b
)

--现在需求想把战区给tmp2表拼上
select tmp2.b ,tmp1.c from tmp1 join tmp2 on tmp1.a like concat('%',tmp2.b,'%')

odps不支持无 on 的 join 会报

*ODPS-0130252 Cartesian product is not allowed without map join *的错误

可以往select里增加字段来绕过这个限制

with tmp1 as (
select '保定五洲长城大街店' as a,'华北战区' as c
union all
select '新疆五洲乌鲁木齐店' as a,'西北战区' as c
)
,
tmp2 as (
select '保定五洲' as b
union
select '廊坊五洲' as b
)
select tmp22.b ,tmp11.c from
 (select *,1 as raoguo from tmp1) tmp11 
join (select * ,1 as raoguo from tmp2) tmp22
 on tmp11.raoguo =tmp22.raoguo
 and  tmp11.a like concat('%',tmp22.b,'%')