我面临一个错误
" FAILED: Error in semantic analysis: Line 1:101 OR not supported in JOIN currently dob
"
同时运行下面提到的查询..
Insert Overwrite Local Directory './Insurance_Risk/Merged_Data' Select f.name,s.age,f.gender,f.loc,f.marital_status,f.habits1,f.habits2,s.employement_status,s.occupation_class,s.occupation_subclass,s.occupation from sample_member_detail s Join fb_member_detail f
On s.email=f.email or
s.dob=f.dob
or (f.name=s.name and f.loc = s.loc and f.occupation=s.occupation)
where s.email is not null and f.email is not null;
谁能告诉我,在蜂巢"OR
"操作符是否可以使用?如果不是,那么应该是什么查询,它将给出与上述查询相同的结果。我有两个表,我想用或运算符在三个条件中的任意一个条件下连接这两个表。请帮. .
对不起,Hive只支持等量连接。你可以尝试从这些表的全笛卡尔积中选择(你必须在非严格模式下):
Select f.name,s.age,f.gender,f.loc,f.marital_status,f.habits1,f.habits2,s.employement_status,s.occupation_class,s.occupation_subclass,s.occupation
from sample_member_detail s join fb_member_detail f
where (s.email=f.email
or s.dob=f.dob
or (f.name=s.name and f.loc = s.loc and f.occupation=s.occupation))
and s.email is not null and f.email is not null;
您也可以使用UNION来获得相同的结果:
INSERT OVERWRITE LOCAL DIRECTORY './Insurance_Risk/Merged_Data'
-- You can only UNION on subqueries
SELECT * FROM (
SELECT f.name,
s.age,
f.gender,
f.loc,
f.marital_status,
f.habits1,
f.habits2,
s.employement_status,
s.occupation_class,
s.occupation_subclass,
s.occupation
FROM sample_member_detail s
JOIN fb_member_detail f
ON s.email=f.email
WHERE s.email IS NOT NULL AND f.email IS NOT NULL;
UNION
SELECT f.name,
s.age,
f.gender,
f.loc,
f.marital_status,
f.habits1,
f.habits2,
s.employement_status,
s.occupation_class,
s.occupation_subclass,
s.occupation
FROM sample_member_detail s
JOIN fb_member_detail f
ON s.dob=f.dob
WHERE s.email IS NOT NULL AND f.email IS NOT NULL;
UNION
SELECT f.name,
s.age,
f.gender,
f.loc,
f.marital_status,
f.habits1,
f.habits2,
s.employement_status,
s.occupation_class,
s.occupation_subclass,
s.occupation
FROM sample_member_detail s
JOIN fb_member_detail f
ON f.name=s.name AND f.loc = s.loc AND f.occupation=s.occupation
WHERE s.email IS NOT NULL AND f.email IS NOT NULL;
) subquery;