Best and Easy way to implement and create SCD2 in Hive and in Pig?

[#1]

How to write and learn SCD 2 type in Hive and Pig?

[#2 · Accepted solution]

@Upendra N

I think you probably realize what makes SCD type 2 difficult in Hadoop (hive/Pig) is that you cannot update records (With new Hive ACID you can but under the hood its doing the magic, that you can also do your self).

Rather than reprinting the process here, here is one link that describes implementing doing SCD Type 2 in Hadoop using Hive. Hope this helps.

https://www.softserveinc.com/en-us/tech/blogs/process-slowly-changing-dimensions-hive/

[#3]

Here's the detailed implementation of slowly changing dimension type 2 in Hive using exclusive join approach.

Assuming that the source is sending a complete data file i.e. old, updated and new records.

Steps:

1. Load the recent file data to STG table
2. Select all the expired records from HIST table

   select * from HIST_TAB where exp_dt != '2099-12-31'

3. Select all the records which are not changed from STG and HIST using inner join and filter on HIST.column = STG.column as below

   select hist.* from HIST_TAB hist inner join STG_TAB stg on hist.key = stg.key where hist.column = stg.column

4. Select all the new and updated records which are changed from STG_TAB using exclusive left join with HIST_TAB and set expiry and effective date as below

   select stg.*, eff_dt (yyyy-MM-dd), exp_dt (2099-12-31) from STG_TAB stg left join (select * from HIST_TAB where exp_dt = '2099-12-31') hist
on hist.key = stg.key where hist.key is null or hist.column != stg.column

5. Select all updated old records from the HIST table using exclusive left join with STG table and set their expiry date as shown below:

   select hist.*, exp_dt(yyyy-MM-dd) from (select * from HIST_TAB where exp_dt = '2099-12-31') hist left join STG_TAB stg
on hist.key= stg.key where hist.key is null or hist.column!= stg.column

6. unionall queries from 2-5 and insert overwrite result to HIST table

More detailed implementation of SCD type 2 can be found here-

https://github.com/sahilbhange/slowly-changing-dimension

Hope this helps!