Geek Answers Handbook

How to compare two tables and return rows with difference with HIVE

So, let's say I have a table with about 180 columns and 100 records. This table is backed up to a temporary table, and the original table is deleted. After that, the migration (change) is performed along the pipeline, which creates the same table. I want to compare the backup table with the new single-line rows (records) with any difference that needs to be transferred to the 3rd table (result table), so I:

INSERT OVERWRITE TABLE
  zakj_customers.customers_detail_result
SELECT
  acct_id, IF (a.title != b.title, 1, 0) title, IF (a.fname != b.fname, 1, 0) fname, IF (a.dob != b.dob, 1, 0) dob, IF (a.cr_date != b.cr_date, 1, 0) cr_date
FROM
  zakj_customers.customers_detail a
LEFT OUTER JOIN
  zakj_customers.customers_detail_backup b
ON
  (a.acct_id = b.acct_id)
ORDER BY 
  title DESC,fname DESC,dob DESC,cr_date DESC
HAVING
  title > 0 AND fname > 0 AND dob > 0 AND cr_date > 0
;

So this question is incorrect, I am not very versed in SQL, and I get syntax errors, so I cannot assemble it correctly, and it was provided in the ticket in this format, which is obviously incorrect.

Can anyone see how this can be done?

Greetings

+5
1

"case when" if:

Case When a.title <> b.title then 1 Else 0 End title

where:

INSERT Into
  zakj_customers.customers_detail_result
SELECT
  acct_id, a.title, a.fname, dob, a.cr_date
FROM
  zakj_customers.customers_detail a
LEFT OUTER JOIN
  zakj_customers.customers_detail_backup b
ON
  (a.acct_id = b.acct_id)
Where b.acct_id is null or a.title <> b.title or a.fname <> b.fname or a.cr_date <> b.cr_date;

"b.acct_id is null" , <> .

( .)

+3

More articles:


All Articles