hive如何實現不等值連接

由於 hive 與傳統關係型數據庫面對的業務場景及底層技術架構都有著很大差異，因此，傳統數據庫領域的一些技能放到 Hive 中可能已不再適用。因為 hive 受限於 MapReduce 算法模型，只支持 equi-joins（等值 join），低版本的hive不支持非等值連接，那麼如何實現非等值連接，今天我們就來討論下！

一對一或一對多連接

1.準備數據

create table test1 
(
 id int
 ,age int
 ,name string
 ,sex string
 )
 ;
insert into test1 values
 (1,12,'zs','m')
 ,(2,15,'ls','m')
 ,(3,20,'ww','m')
 ,(4,20,'ll','f')
 ,(5,23,'xh','f')
;
create table test2 
 (
 id int
 ,age int
 ,name string
 ,class int
 )
 ;
 insert into test2 values
 (1,11,'zsang',1)
 ,(2,15,'lsa',1)
 ,(3,22,'ww',2)
 ,(4,40,'lling',1)
 ,(4,45,'llan',1)
 ,(5,25,'cxh',2)
;

2.想要實現以下需求：

1) 

select 
 t1.id as id
 , t1.name as name1
 , t2.name as name2
 from test1 t1
left join test2 t2
 on t2.name like concat(t1.name,'%')
 and t1.name <> t2.name
;
2）
select 
 t1.id as id
 , t1.age as age1
 , t2.age as age2
 from test1 t1
left join test2 t2
 on t1.id=t2.id 
 and t1.age < t2.age
;

因為hive 不支持不等值連接，所以需要想辦法來實現不等值連接,決定先過濾後關聯

1)

select 
 t1.id 
 ,t1.name as name1 
 , t2.name as name2 
 from test1 t1
left join
 (
 select 
 t1.id
 ,t2.name
 from test1 t1
 inner join test2 t2
 where t2.name like concat(t1.name,'%')
 and t1.name <> t2.name
 ) t2
 on t1.id =t2.id
;

結果：

2)

select 
 t1.id
 ,t1.age as age1
 , t2.age as age2
 from test1 t1
left join
 (
 select 
 t1.id
 ,t2.age
 from test1 t1
 inner join test2 t2
 on t1.id =t2.id
 where t1.age < t2.age
 ) t2
 on t1.id =t2.id
;

結果：

以上方法只支持一對一或一對多的連接，如果多對多連接就不支持了

多對多連接

1.準備數據

在以上test1表中增加一條數據

insert into test1 values (4,30,'li','f')

此時test1中數據為：

select * from test1;

id age name sex

1 12 zs m

2 15 ls m

3 20 ww m

4 20 ll f

4 30 li f

5 23 xh f

;

select * from test2;

id age name class

1 11 zsang 1

2 15 lsa 1

3 22 ww 2

4 40 lling 1

4 45 llan 1

5 25 cxh 2

若還用上面方法來實現該需求的話

select 
 t1.id
 ,t1.age as age1
 , t2.age as age2
 from test1 t1
left join
 (
 select 
 t1.id
 ,t2.age
 from test1 t1
 inner join test2 t2
 on t1.id =t2.id
 where t1.age < t2.age
 ) t2
 on t1.id =t2.id
;

結果為：

id age1 age2

1 12 null

2 15 null

3 20 22

4 20 40

4 20 40

4 20 45

4 20 45

5 23 25

4 30 40

4 30 40

4 30 45

4 30 45

;

以上結果明顯不對，那麼該如何實現呢？

select 
 t1.id as id
 ,t1.age as age1
 ,case when t1.age < t2.age then t2.age else null end as age2
 from test1 t1
left join test2 t2
 on t1.id=t2.id 

結果：

這是我暫時想到的辦法，如果大家有什麼好的辦法，歡迎留言評論，我也會將好的辦法更新！！！