Hive self join based on one column

Question

I have one table in Hive for which data has come from SAP system. This table has columns and data as given below:

+======================================================================+
|document_number | year | cost_centre | vendor_account_number | amount | 
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |                       |  123.5 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  25.96 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |                       |  586   |
+----------------------------------------------------------------------+

As shown above, value for vendor_account_number column is present in only 1 row and I want to bring it on all the rest of the rows.

Expected output is as follows:

+======================================================================+
|document_number | year | cost_centre | vendor_account_number | amount | 
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  123.5 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  25.96 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  586   |
+----------------------------------------------------------------------+

To achieve this, I wrote following CTE in Hive

with non_blank_account_no as(
  select document_number, vendor_account_number
  from my_table
  where vendor_account_number != ''
)

and then did self left outer join as follows:

select 
    a.document_number, a.year, 
    a.cost_centre, a.amount,
    b.vendor_account_number
from my_table a
left outer join non_blank_account_no b on a.document_number = b.document_number
where a.document_number = ' '

but I am getting duplicated output as shown below

+======================================================================+
|document_number | year | cost_centre | vendor_account_number | amount | 
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  123.5 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  25.96 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  586   |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  123.5 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  25.96 |
+----------------------------------------------------------------------+
|       1        | 2016 |     XZ10    |      1234567890       |  586   |
+----------------------------------------------------------------------+

Can anyone please help me understanding what is wrong with my Hive query?

@Andrew, Yes CTE is supposed to select only 1 row in which vendor_account_number is not null — Shekhar
I know it's only supposed to have 1 row, the question is how many does it actually have? It certainly looks like you're getting a cross join. — Andrew

David דודו Markovitz David דודו Markovitz · Accepted Answer · 2017-06-28T18:16:53

In many use-cases a self-join can be replaced by a windows function

select  document_number
       ,year
       ,cost_center

       ,max (case when vendor_account_number <> '' then vendor_account_number end) over 
        (
            partition by    document_number
        )                                       as vendor_account_number

       ,amount

from    my_table

Hive self join based on one column

1 Answers