Joiner is an active transformation which is used to join heterogeneous and homogeneous tables. In joiner we can join one relational and one flat file or two flat files or two relational tables. Both tables should have a common columns. For ‘n’ tables we need ‘n-1’ joins. Always make the less number of records table as master drag it to the joiner second time.
Configuring Joiner Transformation
Cache Directory: It is used to cahe the Master or Detail rows. Here we can specify the location. The default location is ‘$PMCacheDir’, we can override this location.
Join Type: It indicates the type of join we can perform. There are 4 types of joins are there.
Normal Join: Returns matching rows from both master and detail tables.
Master Outer Join: All rows from detail table and matching rows from master table.
Detail Outer Join: All rows from master and matching from detail table.
Full Outer Join: All (Matching & Non matching) from both tables.
Joiner Data Cache Size: Indicates the size of the data cache. The default value is ‘Auto’.
Joiner Index Cache Size: Indicates the size of the index cache. The default value is ‘Auto’.
Sorted Input: Input data in sorted order. It improves the performance. If we joining two pipelines in a mapping bwe should use ‘Sorted Input’ property.
Master Sort Order: Master source data is sorted. The default value is ‘Auto’. If we select ‘Ascending’ the master source data is sorted in ascending order.
Restrictions
You should not use joiner with sequence generator.
Cartesian join by using joiner
Create a dummy port in both sources o returns same value(1) and use that in a joiner condition.
Implementing Joiner Transformation with Example
- Import source EMP, DEPT tables, drag it to the mapping designer
- Create target by adding DNAME, LOC to source EMP table, and drag it to the mapping designer.
- Go to Transformation->Create->Select Joiner->OK
- Connect all ports from EMP, DEPT table to Joiner transformation.
- Edit Transformation->Condition->Add condition->DEPTNO=DEPTNO1->OK
- Properties->Join Type->Normal join (We can specify Normal Join/Master Outer Join/Detail Outer Join/Full Outer Join)->OK
- Connect all ports from Joiner to Target
