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

horton — Tue, 18 Jul 2017 07:00:56 GMT

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

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

mqureshi — Tue, 18 Jul 2017 08:33:21 GMT

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/

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

sahilbhange — Tue, 20 Nov 2018 06:21:39 GMT

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:

Load the recent file data to STG table
Select all the expired records from HIST table
select * from HIST_TAB where exp_dt != '2099-12-31'
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
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
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
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!

question Re: Best and Easy way to implement and create SCD2 in Hive and in Pig? in Archives of Support Questions (Read Only)

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

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

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