Title: Compare two tables in HIVE
Tags: sql;hadoop;hive;nosql
Question: I have 3 tables in hive:
Control_table, with known data
New_table, with data to check
Result_table, table where records with different values in new_table then control_table are inserted to
All three tables have same column names (which I won't actually present for security reasons) and number of columns and those are:
c1, c2, c3, c4, c5, c6, c7
Where c1 is acting as primary key.
Now I need to put together SQL query for HIVE to compare two tables (control_table with new_table) and rows which have different values to be moved into result table. Now result table have same number of columns as new_table or control_table. If possible I would like to populate only columns which are actually different.
So example of this would be:
Control_table:  c1  | c2 | c3 | c4 | c5 | c6 | c7 |
With a values:  11  | 22 | 33 | 44 | 55 | 66 | 77 |
New_table:   c1 | c2 | c3 | c4 | c5 | c6 | c7 |
With values: 11 | 21 | 33 | 44 | 54 | 66 | 87 |
Compare them and result insert into result_table, so result table would look like this:
Result_table:  c1 | c2 | c3 | c4 | c5 | c6 | c7 |
With values:   11 | 21 | -- | -- | 54 | -- | 87 |
So only columns with the actual difference in value would be populated.
Now I am rubbish in the SQL, I tried several approaches and tried to ask question here, but got never good answer, here is link to my previous answer which has also example of the sql:
How to compare two tables and return rows with difference with HIVE
P.S.: It might be that this is not possible, I'm not sure, as I have no previous experiences with NoSQL.

Here is the accepted answer: Modify bellow for your purpose:
SELECT nt.c1,
CASE WHEN ct.c2 <&gt; nt.c2 THEN '--' ELSE  nt.c2 END,
CASE WHEN ct.c3 <&gt; nt.c2 THEN '--' ELSE  nt.c3 END
New_table nt
JOIN Control_table ct ON nt.c1 = ct.c1
ct.c2 <&gt; nt.c2 OR ct.c3 <&gt; nt.c3
Comment for this answer: @vikrantrana I have added it just for the clarity, if you would like to have in JOIN please do so.
Comment for this answer: @www- why you have added where clause in join statement? WHERE
ct.c2 <> nt.c2 OR ct.c3 <> nt.c3... can we get the desired result without having this where stament
Comment for this answer: Although you have mentioned to modify your code, still then and else values needs to be swapped to match the answer to the original question.

Here is another answer: select * from Control_table
full outer join New_table
on Control_table.c1=New_table.c1
where Control_table.c1 is null  or  New_table.c1 is null;
This would fetch records if any of the record mismatches from both tables. And you can do you table data validation. If you want to store it into other table.
create a managed table with:
CREATE TABLE RESULT_TABLE AS SELECT * FROM Control_table;
and load the result into this result table as:
INSERT INTO TABLE RESULT_TABLE
select * from Control_table full outer join New_table on
Control_table.c1=New_table.c1
where Control_table.c1 is null
or  New_table.c1 is null;

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2014:00:10/16/14', 'FavoriteCount': 2.0, 'PostTypeId': 1, 'Tags': '<sql><hadoop><hive><nosql>', 'Title': 'Compare two tables in HIVE', 'ViewCount': 16133, 'id': 26399990, 'user_AboutMe': '', 'user_AccountId': 248682.0, 'user_DisplayName': 'Jakub Zak', 'user_Reputation': 1452.0, 'user_Views': 205.0, 'user_id': 524226.0}}