Making zipper tables with kettle

The Zipper table is a common table in the Data Warehouse, which is mainly used to store non-time-varying tables, such as the Customer basic information table.

The following is the first two instance tables, User_info and user_info_l, of which user_info_l is a zipper table.

User_info table and data:

user_info_l table and converted data:

Kettle's design is very simple, just a "table input" a "Dimension query/update

Here's a look at the configuration of the table input:

This is simple, but there must be a data date for the basic table.

The following are the configuration of dimension query/update:

Here's a look at the key places in the setup, which are:

1. If you do not check the change of data will not be inserted, as for the use of non-tick, we can study.

2. Here is the creation of each version of the keyword, which is basically the primary key of the source table.

3. Here is the field to update, in addition to the source table keywords, the others are selected.

4. The time to forget a few ...

5.kettle automatically gives the primary key generated by the list.

6. The Proxy keyword gets the method.

7. The version number of each record, followed by one version number for each change.

8. Depending on which field you are updating, you can also use the system time.

9. The earliest start date of the Zipper, which is also the first time the empty table is inserted.

10. The latest end date of the zipper, updated fields Use this value, updated to update the date, you can refer to the above figure in the user_info_l data.

I found a problem when using, do not know whether it is a bug, or kettle intentionally. When inserting a zipper table, a blank record with a proxy primary key of 0 is generated. It could be a bug, or it might be kettle to prevent problems with the data, forcing other fields to be empty.

To solve this problem is simple, just add a deleted trans:

The configuration of the build record is as follows:

Then put two trans into a job, job such as:

At this point, the Zipper table is created.

If in use, the classmate in doubt can contact me qq:415426434.

Making zipper tables with kettle