Joining Two Tables in Hive Using Hiveql(Hadoop)

Joining two Tables in Hive using HiveQL(Hadoop)

EDIT - PART 1
Okay - For some reason I am going to explain myself - so to start with I stumbled upon this question because of the SQL tag, and saw Hive, and started to not look and just skip it. BUT then I noticed it had been over a day and you had gotten no answers. I looked - I saw a SQL logic correction in the original query posted that I knew would be needed and would help, so I posted ONLY because no one had answered. I will try to address this last question - but after that I am keeping my advice to myself, as I may be giving bad advice. Good luck! I tried! And you seem to be getting answers now, so...

In TSQL, I could solve this entire problem with the below single query:

SELECT * 
FROM SO_Table1HIVE A
FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)

It would return everything, including your match buyer_id/user_id only. It won't match a buyer_id/user_id row with no matches in either time or product in the other table, but it will return it as a separate row with NULLS in the other table's fields. I would not match these any way - there is no accurate information provided to do it with as explained below.

END EDIT PART 1

If you can't do FULL OUTER JOIN with OR in Hive, the simplest way to meet the original criteria is to UNION ALL 2 INNER JOINs. On one of the queries, in addition to joining the matching user_ids, join on the PRODUCT_ID AND in your WHERE look for TIMESTAMPS that don't match CREATED_TIME. On the second query, in addition to joining the matching user_ids, join on the times AND in your WHERE look for products that don't match.

EDIT PART 2 - UPDATE FOR COMMENT QUESTION ADDITIONAL CRITERIA

If I understand the last criteria it is any record in either table that has a matching user_id = buyer_id, but nothing else matches. The FULL OUTER JOIN with OR condition will return them, but there isn't enough provided info for a way to relate the records to each other. We can easily identify them, but have no way to tie them back to each other. If you do so and you have more than one record without a match in either OR both tables, there are going to be multiple entries for each.

Any query I wrote to try to tie them without more info (and probably with) would be a guess and inaccurate.

For example, in the first table if there were these 2 (sample fake) records with nothing matching in the second except user_id:

1015826235  420003038067    2011-11-03 19:40:21.000
1015826235  720003038067    2004-11-03 19:40:21.000

AND in table2 - these non matching:

1015826235  {"product_id":520003038067,"timestamps":"10...
1015826235  {"product_id":620003038067,"timestamps":"10...

You can identify them, but if you match them without more criteria you get 4 instead of 2:

1015826235  420003038067    2011-11-03 19:40:21.000 1015826235 520003038067
1015826235  420003038067    2011-11-03 19:40:21.000 1015826235 620003038067
1015826235  720003038067    2004-11-03 19:40:21.000 1015826235 520003038067
1015826235  720003038067    2004-11-03 19:40:21.000 1015826235 620003038067

My suggestion would be simply to identify them and show them, as below.

BUYER_ID        ITEM_ID      CREATED_TIME           USER_ID PRODUCTID   timestamps  
----------------------------------------------------------------------
NULL            NULL         NULL                   1015826235  520003038067    2009-11-11 22:21:11.000
NULL            NULL         NULL                   1015826235  620003038067    2008-11-11 22:21:11.000
1015826235      420003038067 2011-11-03 19:40:21.000    NULL    NULL    NULL    
1015826235      720003038067 2004-11-03 19:40:21.000    NULL    NULL    NULL    

END EDIT PART 2 - UPDATE FOR COMMENT QUESTION ADDITIONAL CRITERIA - PART 1

I am working with TSQL, so I can't test for you an exact query with your syntax, but the concepts of the joins are the same, and this will return what you want. I did take your query and attempt your syntax, modify as needed. I tested in TSQL. You may be able to take this and improve upon it with functionality in HiveQL. There are other ways to do this - but this is the most straightforward and this will translate to HiveQL.

REMOVED, YOU GOT THIS PART AND IT IS INCLUDED LATER

(Again modify syntax as needed)**

SELECT *
FROM (
    SELECT BUYER_ID,ITEM_ID,CREATED_TIME,PRODUCT_ID,TIMESTAMPS 
    FROM testingtable2 LATERAL VIEW
        explode(purchased_item) exploded_table as prod_and_ts)
        prod_and_ts 
    INNER JOIN table2 A  ON A.BUYER_ID = prod_and_ts.[USER_ID] AND prod_and_ts.timestamps = UNIX_TIMESTAMP (table2.created_time) 
    WHERE prod_and_ts.product_id <> A.ITEM_ID
    UNION ALL
    SELECT BUYER_ID,ITEM_ID,CREATED_TIME,PRODUCT_ID,TIMESTAMPS 
    FROM testingtable2 LATERAL VIEW
            explode(purchased_item) exploded_table as prod_and_ts)
            prod_and_ts 
    INNER JOIN table2 A  ON A.BUYER_ID = prod_and_ts.[USER_ID] AND prod_and_ts.product_id = A.ITEM_ID
    WHERE  prod_and_ts.timestamps <> UNIX_TIMESTAMP (table2.created_time) 
) X

And here is my tested TSQL version with my table names for reference:

SELECT * 
FROM(
    SELECT *
    FROM SO_Table1HIVE A
    INNER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND B.t1time = A.Created_TIME 
    WHERE B.PRODUCTID <> A.ITEM_ID
    UNION ALL
    SELECT * 
    FROM SO_Table1HIVE A
    INNER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND B.PRODUCTID = A.ITEM_ID  
    WHERE B.t1time <> A.Created_TIME  
 ) X

*EDIT PART 3 - UPDATE FOR COMMENT QUESTION ADDITIONAL CRITERIA -PART 2

In TSQL the entire query (no unions) can be run using a FULL OUTER JOIN with an OR condition on the join

SELECT * 
FROM SO_Table1HIVE A
FULL OUTER JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR B.PRODUCTID = A.ITEM_ID)

If you can't simply do the above, For the SQL logic for the new criteria - to grab those that don't match from both tables and display them as NULL in the other table use RIGHT JOIN and LEFT JOIN.
RIGHT JOIN will grab anything in the first table the matches the second and everything in the second, and LEFT does the opposite. Add the new queries to your UNION.

TSQL EXAMPLE - MODIFY FOR HIVE

SELECT * 
FROM SO_Table1HIVE A
RIGHT JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME    OR B.PRODUCTID = A.ITEM_ID)
WHERE A.BUYER_ID IS NULL 
UNION ALL
SELECT * 
FROM SO_Table1HIVE A
LEFT JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] AND (B.t1time = A.Created_TIME OR    B.PRODUCTID = A.ITEM_ID)
WHERE B.[USER_ID] IS NULL

Or, If you wanted to grab them and match them as duplicates add to UNION:

TSQL

SELECT * 
FROM SO_Table1HIVE A
JOIN SO_Table2HIVE B ON A.BUYER_ID = B.[USER_ID] 
WHERE B.t1time NOT IN(SELECT Created_TIME FROM SO_Table1HIVE)
AND A.Created_TIME  NOT IN(SELECT t1time FROM SO_Table2HIVE) 
AND B.PRODUCTID NOT IN(SELECT ITEM_ID FROM SO_Table1HIVE)
AND A.ITEM_ID NOT IN(SELECT PRODUCTID FROM SO_Table2HIVE) 

Again, Good luck!

Join multiple tables in Hive

1. Based on your desired result, you need to join all your tables on
   col1.
2. To ensure that you always have a value in col1, you need to
   coalesce them together (coalesce gives you the first non-null
   value).
3. To stitch your other columns together (and replace nulls with an empty space, combine coalesce with concat:

Putting that all together:

select
coalesce(t1.col1,t2.col1,t3.col1) as col1,
concat(coalesce(t1.col2,' '),',',coalesce(t2.col2,' '),',',coalesce(t3.col2,' '))
from
table1 t1
full join table2 t2
  on t1.col1 = t2.col1
full join table3 t3
  on t1.col1 = t3.col1

Join two tables in HIVE with sub query

It can be accomplished with CTE and Lag widow function

     With result as(select PRODUCT_ID, DESCRIPTION, ITEM_COST , DATE_LOADED ,
     LEAD(DATE_LOADED, 1,'2999-01-01')
     OVER (ORDER BY DATE_LOADED) AS fromdate from PRODUCT ) 
     SELECT s.product_id, s.items_sold, p.description, s.items_sold * p.item_cost 
     as total_cost  FROM sales s join result p on s.product_id = p.product_id 
     where s.DATE_LOADED >= p.DATE_LOADED  and s.DATE_LOADED < p.fromdate  ;  

Join two tables using HiveQL

If I understand your requirements correctly, I think you are almost there. It seems you only need to add a condition checking if there's no match between the two tables:

SELECT COUNT(BUYER_ID), BUYER_ID 
FROM Table1 dw 
LEFT OUTER JOIN Table2 dps ON (dw.BUYER_ID = dps.USER_ID) 
WHERE dps.USER_ID IS NULL
GROUP BY BUYER_ID;

The above will filter out BUYER_IDs that do have matches in Table2, and will show the remaining BUYER_IDs and their corresponding count values. (Well, that's what I understand you want.)

Join two tables and insert value in a new table in hive

Yes it is possible use the CTAS (create table new_table as select ...) syntax.

create table new_table as select * from customer_table, issues_table where customer_table.CustomedId = issues_table.CustomedId;

---

Related Topics