Hive SQL Full Outer Join with Where Clause

user_2415897 0
Viewed 668

I am creating a full outer join with a where clause. However, it can only generate inner join result. I suspect that it is due to the where clause, but I do need this where condition being added. So how can I create a query with both needs meet (both the where condition and full outer join)? Here is my query.

select
  t1.key1 as key1_1
, t1.key2 as key2_1
, t1.key3 as key3_1
, t1.date as date_1
, t1.v1
, t2.key1 as key1_2
, t2.key2 as key2_2
, t2.key3 as key3_2
, t2.date as date_2
, t2.v2
from t1 
full outer join t2
on t1.key1 = t2.key1 and t1.key2 = t2.key2 and t1.key3 = t2.key3 
where datediff(t1.date, t2.date) between -5 and 5
;

Sample data

t1
key1 key2 key3 date        v1
A1   B1   C1   2015-01-01  10
A1   B2   C2   2015-01-01  11

t2
key1 key2 key3 date        v2
A1   B1   C1   2015-01-01  20
A1   B1   C1   2015-01-03  30
A1   B1   C1   2015-02-01  40
A1   B1   C1               50
A1   B1   C2   2015-01-02  60

Desired result

key1_1 key2_1 key3_1 date_1     v1 key1_2 key2_2 key3_2 date_2     v2
A1     B1     C1     2015-01-01 10 A1     B1     C1     2015-01-01 20
A1     B1     C1     2015-01-01 10 A1     B1     C1     2015-01-03 30
                                   A1     B1     C1     2015-02-01 40
                                   A1     B1     C1                50
                                   A1     B1     C2     2015-01-02 60
A1     B2     C2     2015-01-01 11

These are all the scenarios that I can think of as now. I can add in if I find any missing scenarios. My point here is the fact that the following results should be included:

  1. if the two tables meet all those conditions set up with the keys and date, then it is included as shown in line 1 and 2 in the desired result.
  2. if any of those conditions is not met, then we will keep one table's information in the result as shown in line 3, 4, 5, and 6 in the desired result.

EDIT: Based on @Gordon Linoff 's suggestion, I used a union all to resolve the issue. Please see my solution in my answer post below.

hiveql full-outer-join where-clause sql
3 Answers

You may just want to move the logic to the on clause:

from t1 full outer join
     t2
     on t1.key1 = t2.key1 and
        t1.key2 = t2.key2 and
        t1.key3 = t2.key3 and
        datediff(t1.date, t2.date) between -5 and 5

EDIT:

If the above doesn't work, then perhaps you can rewrite the query as a union all:

select . . . 
from t1 join
     t2
     on t1.key1 = t2.key1 and
        t1.key2 = t2.key2 and
        t1.key3 = t2.key3
where datediff(t1.date, t2.date) between -5 and 5
union all
select . . .
from t1
where not exists (select 1
                  from t2
                  where t1.key1 = t2.key1 and
                        t1.key2 = t2.key2 and
                        t1.key3 = t2.key3 and
                        datediff(t1.date, t2.date) between -5 and 5
                 )
union all
select . . .
from t2
where not exists (select 1
                  from t1
                  where t1.key1 = t2.key1 and
                        t1.key2 = t2.key2 and
                        t1.key3 = t2.key3 and
                        datediff(t1.date, t2.date) between -5 and 5
                 );

I'm not 100% sure that Hive will accept these correlation clauses either.

user_11440350

The problem, as you have already realized is that the where forces t1.date and t2.date to exist. You just need to avoid this assumption, like:

(t1.date is null) or (t2.date is null) or (datediff(t1.date, t2.date) between -5 and 5)
user_4365600

Here is my solution to my own question, based on @Gordon Linoff 's suggestion shown in the discussion session.

create table t3 as
select *, row_number () over () as id from t1;

create table t4 as
select *, row_number () over () as id from t2;

create table t5 as
select 
  t1.id as id_1
, t1.key1 as key1_1
, t1.key2 as key2_1
, t1.key3 as key3_1
, t1.date as date_1
, t1.v1
, t2.id as id_2
, t2.key1 as key1_2
, t2.key2 as key2_2
, t2.key3 as key3_2
, t2.date as date_2
, t2.v2
from t3 as t1 
full outer join t4 as t2
on t1.key1 = t2.key1 and t1.key2 = t2.key2 and t1.key3 = t2.key3 
where datediff(t1.date, t2.date) between -5 and 5
;

set hive.mapred.mode=nonstrict;
create table t6 as
select
  t1.id as id_1
, t1.key1 as key1_1
, t1.key2 as key2_1
, t1.key3 as key3_1
, t1.date as date_1
, t1.v1
, null as id_2
, null as key1_2
, null as key2_2
, null as key3_2
, null as date_2
, null as v2
from t3 as t1 
where t1.id not in (select t2.id_1 from t5 as t2 where t2.id_1 is not null)
;

create table t7 as
select
  null as id_1
, null as key1_1
, null as key2_1
, null as key3_1
, null as date_1
, null as v1
, t1.id as id_2
, t1.key1 key1_2
, t1.key2 key2_2
, t1.key3 key3_2
, t1.date date_2
, t1.v2
from t4 as t1 
where t1.id not in (select t2.id_2 from t5 as t2 where t2.id_2 is not null)
;

create table t8 as
select * from t5 union all
select * from t6 union all
select * from t7
;
user_24158970
Related