HIVE testing for data from table 1, which is not in table 2

Question

I am looking to create an HIVE SQL query to find all the values ​​from table 1 that are NOT presented in table 2. I understand that I need to use a connection, but I cannot figure out how to implement it for this situation ...

Thanks James

eg:

Table1 url number xe.com 5 google.com 2 ebay.co.uk 6 Table2 url visits facebook.com 8 google.com 4 ebay.co.uk 15 

So, for example, the query should return all the values ​​from table 1 that are present in table 2, i.e.

 url number visits google.com 2 4 ebay.co.uk 6 15 

Answer 1

LEFT JOIN will return all rows from Table1 whether or not a match exists. If there is no match, the columns from Table2 will be NULL - these are the rows you need:

 SELECT Table1.url, Table1.number FROM Table1 LEFT OUTER JOIN Table2 ON Table1.url = Table2.url WHERE Table2.url IS NULL