Hive implements Oracle's minus function

The main function of the minus operation in Oracle is to return records that are not the same as the second table/query results in the first table/query results when two tables or two query results are performed.

results not the same records include two cases: The contents of a row in a /b table are different and the data in table A does not exist in table B. In summary, the data for table A is returned.

there is no function to implement minus function in Hive , which is only a step-up implementation.

First, find out the data in table A that does not exist in table B

Insert Overwrite table Tmp_a partition (name= 'a_innot_b ')

Select

A.*

From a a left outer joins b b on (a.id = b.id) where b.id is NULL;

Ii. find data that exists in both a and B tables, but with different contents

The UDF functions are as follows:

Public class Minus extends udf{

String a="";

String B="";

Public Text Evaluate (String ... strs) {

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

a=a+strs[i];

}

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

b=b+strs[i];

}

if (a. Replace ("", ""). Equals (b. Replace ("", ""))) {

return New Text ("NULL");

}Else{

return New Text (Strs[0].replace ("", ""));

}

}

The corresponding query such as the following:

Insert Overwrite table Tmp_a_diff

Select Iminus (

a.*,b.*

) from a a joins B b on (a.id=b.id);

The SQL above executes the minus Java program, which has loops in the program statement. Assume that the amount of data is very large and time consuming. The job progress card is not moving. can also be implemented using Hive's own function

Insert Overwrite table tmp_a_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);

This is a better efficiency.

Tmp_a_diff Stores the ID and some "NULL"of data that are present in both a and B tables, but with different contents .

get each row of data based on ID

Insert Overwrite table tmp_a partition (name= "A_in_b")

Select A.*

From Tmp_a_diff B joins a A on (a.id=b.id);

the data in the tmp_a partition a_innot_b and partition A_in_b is now in Oracle ( SELECT * from A)minus data (SELECT * from B) .

Hive implements Oracle's minus function