Zipper Table 1 in hive

Zipper list in hive

In some cases, in order to maintain some of the historical states, you need to use a zipper list to save space while all states can be retained.

The zipper table is suitable for the following situations

The amount of data is a little large, and there are changes in some fields in the table, but the frequency of changes is not very high. Business requirements, uh, need to count this change state. It is a bit unrealistic to have a full copy every day.

Not only a waste of storage space, sometimes business statistics may also be a bit troublesome, at this time, the role of the zipper table is withdrawn, which not only saves space, but also meets the demand.

It is generally expressed by adding begin_date,en_date in the warehouse, as in the following example, the last two columns are start_date and end_date.

123456789101 2016-08-20 2016-08-20 create 2016-08-20 2016-08-201 2016-08-20 2016-08-21 payment 2016-08-21 2016-08-21 2016-08-20 2016-08-22 2016-08-22 9999-12-312 2016-08-20 2016-08-20 2016-08-20 2016-0820 2016-0820 2016-0821 9999-12-313 2016-0820 2016-08-20 20 create 2016-08-20 2016-08-20 2016-08-20 2016-08-22 pay 2016-08-22 9999-12-314 2016-08-21 2016 08-21 create 2016-08-21 2016-08-21 2016-08-21 2016-08-22 pay 2016-08-22 9999-12-315 2016-08-22 2016-08-22

Begin_date represents the start time of the record's life cycle, and end_date represents the end time of the record's life cycle.

End_date = '9999-12-31' indicates that the record is currently in a valid state

If you query all currently valid records, then select * from order_his where dw_end_date = '9999-12-31'

If you query a historical snapshot of 2016-08-21, then select * from order_his where begin_date