Hive SQL multiple left outer join query is missing records in its results

Question

I am trying to join 7 tables and insert the joined data into one big joined table, to do this I am using the query below

INSERT OVERWRITE TABLE databaseName.joinTab PARTITION (tran_date)
SELECT <180 cols across all 7 tables>
FROM databaseName.table1 tab1
LEFT OUTER JOIN databaseName.table2 tab2 ON (tab1.id = tab2.id and 
tab1.tran_date='20171030' and tab2.tran_date='20171030')
LEFT OUTER JOIN databaseName.table3 tab3 ON (tab1.id = tab3.id and 
tab1.tran_date='20171030' and tab3.tran_date='20171030')
LEFT OUTER JOIN databaseName.table4 tab4 ON (tab1.id = tab4.id and 
tab1.tran_date='20171030' and tab4.tran_date='20171030')
LEFT OUTER JOIN databaseName.table5 tab5 ON (tab1.id = tab5.id and 
tab1.tran_date='20171030' and tab5.tran_date='20171030')
LEFT OUTER JOIN databaseName.table6 tab6 ON (tab1.id = tab6.id and 
tab1.tran_date='20171030' and tab6.tran_date='20171030')
LEFT OUTER JOIN databaseName.table7 tab7 ON (tab1.id = tab7.id and 
tab1.tran_date='20171030' and tab7.tran_date='20171030')
WHERE (tab1.tran_date='20171030');

tran_date is the partition column for all of these tables, the reason that i have a where clause as well as the condition being in the ON statement is that i was finding that the tez job started would do a full table scan for table1 if i didnt.

So my issue here is if i do a count(*) from table1 on tran_date=20171030 then i get 11845917 as the result

If i do a count(*) from the new joined table(joinTab) for that same partition tran_date=20171030 I only get the result 97609 which is a very large difference, as i'm using left outer joins i had thought that it should move all the data from table1 into the join table and populate nulls for anything not in the other tables. I should mention tran_date in joinTab is derived from when table1 data is loaded

Is there anything here that doesn't look right?

Thanks for your help

Dan

Giacomo Giacomo · Accepted Answer · 2017-11-10T10:42:35

I couldn't test if this solution works because you haven't provided a reproducible example, but you can try something like this:

WITH tab1_temp AS (SELECT <tab1 cols> WHERE tab1.tran_date='20171030'
)
INSERT OVERWRITE TABLE databaseName.joinTab PARTITION (tran_date)
SELECT <180 cols across all 7 tables>
FROM tab1_temp
LEFT OUTER JOIN databaseName.table2 tab2 ON (tab1.id = tab2.id and 
tab1.tran_date='20171030' and tab2.tran_date='20171030')
LEFT OUTER JOIN databaseName.table3 tab3 ON (tab1.id = tab3.id and 
tab1.tran_date='20171030' and tab3.tran_date='20171030')
LEFT OUTER JOIN databaseName.table4 tab4 ON (tab1.id = tab4.id and 
tab1.tran_date='20171030' and tab4.tran_date='20171030')
LEFT OUTER JOIN databaseName.table5 tab5 ON (tab1.id = tab5.id and 
tab1.tran_date='20171030' and tab5.tran_date='20171030')
LEFT OUTER JOIN databaseName.table6 tab6 ON (tab1.id = tab6.id and 
tab1.tran_date='20171030' and tab6.tran_date='20171030')
LEFT OUTER JOIN databaseName.table7 tab7 ON (tab1.id = tab7.id and 
tab1.tran_date='20171030' and tab7.tran_date='20171030')
;

Hive SQL multiple left outer join query is missing records in its results

1 Answers