Hive Query-使用OR运算符在三个连接条件下连接两个表 [英] Hive Query- Joining two tables on three joining conditions with OR operator

问题描述

FAILED：语义分析错误：第1行：101或者在JOIN中不支持dob

code>

同时运行下面提到的查询..

 插入覆盖本地目录'./Insurance_Risk/Merged_Data'选择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加入fb_member_detail f 
在s.email = f.email或
 s.dob = f.dob 
或（f.name = s。名称和f.loc = s.loc和f.occupation = s.occupation）
其中s.email不为null且f.email不为null; 
  

任何人都可以告诉我，在配置单元 OR 操作符是否可以使用？
如果不是，那么应该是什么样的查询将给出与上述查询给出的结果相同的结果。
我有两个表格，我想用or操作符将三个条件中的任何一个连接到两个表格。
请帮忙..

解决方案

抱歉的Hive只支持equi连接。您可以尝试从这些表的完整笛卡尔积中进行选择（您必须处于非严格模式）：

 选择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 
其中（s.email = f.email 
或s.dob = f.dob 
或（f.name = s.name和f.loc = s.loc和f .occupation = s.occupation））
和s.email不为空且f.email不为空; 
  

I am facing an error

"FAILED: Error in semantic analysis: Line 1:101 OR not supported in JOIN currently dob"

while running the below mentioned query..

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;

Can anyone tell me that, in hive "OR" operator can be used or not? if not, then what should be the query which will give the same result as given by the above mentioned query. I have 2 tables and I want to join the two tables on any one of the three conditions with or operator. Please help..

解决方案

Sorry Hive supports only equi-joins. You can always try select from full Cartesian product of those tables(you have to be in non-strict mode):

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;

这篇关于Hive Query-使用OR运算符在三个连接条件下连接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！