Hive實現oracle的Minus函數

標籤：

在Oracle中minus運算的主要功能是： 在進行兩個表格或者兩個查詢結果的時候，返回在第一個表格/查詢結果中與第二個表格/查詢結果不同樣的記錄。

結果不同樣的記錄包括兩種情況：A，B 表中某一行的內容不同和A表中的資料在B表中不存在。總之返回的是A表的資料。

Hive中沒有實現minus功能的函數，僅僅好分步實現。

一、找出在Ａ表中的資料在Ｂ表中不存在的資料

insert overwrite table tmp_Ａ partition(name=‘Ａ_innot_B‘)

select 

                  a.*

from A a left outer join  B b on (a.id = b.id)  where b.id is NULL; 

二、找出在A表和B表都存在但內容不同的資料

ＵＤＦ函數例如以下：

public class Minus extends UDF{

String Ａ="";

String Ｂ="";

public Text evaluate(String... strs){

for(int i=0;i<strs.length/2;i++){

Ａ=Ａ+strs[i];

}

for(int i=strs.length/2;i<strs.length;i++){

Ｂ=Ｂ+strs[i];

}

if(Ａ.replace(" ", "").equals(Ｂ.replace(" ", ""))){

return new Text("NULL");

}else{

return new Text(strs[0].replace(" ", ""));

}

}

相應的查詢例如以下：

insert overwrite table tmp_Ａ_diff

select iminus(

                       a.*,b.*

     ) from A a join B b on (a.id=b.id);

上面的sql會執行Minus的java程式，改程式語句中有迴圈。假設資料量非常大非常耗時間。job進度卡著不動。也能夠使用hive內建的函數實現

insert overwrite table tmp_Ａ_diff
select if(
        regexp_replace(
                  concat(                        
                        a.*
                        ),
                          " ","")
       =
        regexp_replace(
                 concat(
                        b.*
                        ),
                          " ","")
       ,NULL,b.id)
    from A a
             join 
         B b 
             on (a.id=b.id);

這樣效率好些。

tmp_A_diff儲存的是A表和B表都存在但內容不同的資料的id和一些“NULL”。

依據id獲得每行資料

insert overwrite table tmp_A partition(name="A_in_B")

select            a.*

from tmp_A_diff b join A a on (a.id=b.id);

如今tmp_A中分區A_innot_B和分區A_in_B的資料就是oracle中（select * from A）minus (select * from B)的資料。

Hive實現oracle的Minus函數