A parent-child table is a table in which there is a self-referencing relationship. In other words, there is a hierarchical relationship among its rows. A typical example of this is a table with employees, in which one of the columns contains references to the employee that is above each employee in the hierarchy.
In this recipe you will load the parent-child table of employees of Steel Wheels. The hierarchy of roles in Steel Wheels is as follows:
A sales representative reports to a sales manager
A sales manager reports to a vice-president
A vice-presidents reports to the president
The president is the highest level in the hierarchy. There is a single employee with this role.
You will load all employees from a file. These are sample rows in that file:
EMPLOYEENUMBER|LASTNAME|FIRSTNAME|EXTENSION|EMAIL|OFFICECODE|JOBTITLE|REP_TO 1002|Murphy|Diane|x5800|[email protected] |1|President| 1056|Patterson|Mary|x4611|[email protected] |1|VP Sales|[email protected] 1076|Firrelli|Jeff|x9273|[email protected] |1|VP Marketing|[email protected] 1088|Patterson|William|x4871|[email protected] |6|Sales Manager (JAPAN, APAC)|[email protected] ...
As you can see, among the fields you have the e-mail of the employee who is above in the hierarchy. For example, Gerar Bondur is a Sales Manager, and reports to the employee with e-mail <[email protected]>, that is, Mary Patterson.
In order to run this recipe, either truncate the employees table in Steel Wheels, or create the table employees in a different database.
Create a transformation that inserts the record for the president who is the first in the hierarchy, and doesn't report to anyone. The transformation should read the file, filter the record with
JOBTITLE=President, and insert the data into theemployeestable.Create another transformation to load the rest of the employees. Define a named parameter named
LEVELthat will represent the role of the employees being loaded.Use a Text file input step to read the file of employees.
Use a Get Variables step to add the variable
LEVELas a new field namedlevel.Add a Filter rows step to filter the employees to load based on their role. In order to do that, enter the following condition:
JOBTITLE REGEXP level.Add a Database lookup to find out the employee number of the employee who is one above in the hierarchy: In the upper grid add a row with the condition
EMAIL = REP_TO. Use the lower grid to get the fieldEMPLOYEENUMBERand rename it toREPORTSTO.Add a Table Output step, and use it to insert the records in the table
employees. Your final transformation looks like this:
Finally create a job to put all together. Drag to the work area a START entry, and four Transformation job entries. Link all of them in a row.
Use the first Transformation entry to execute the transformation that loads the president.
Double-click the second Transformation entry and configure it to run the transformation that loads the other employees. Under the Parameters tab, add a parameter named
LEVELwith valueVP.*.Repeat step 10 for the third Transformation entry, but this time type
.*Manager.*as the value for theLEVELparameter.Repeat step 10 for the fourth Transformation entry, but this time type
Sales Rep.*as the value for theLEVELparameter.Save and run the job. The table should have all employees loaded, as you can see below:
SELECT EMPLOYEENUMBER N , LASTNAME , REPORTSTO , JOBTITLE FROM employees; +------+-----------+-----------+----------------------------+ | N | LASTNAME | REPORTSTO | JOBTITLE | +------+-----------+-----------+----------------------------+ | 1002 | Murphy | NULL | President | | 1056 | Patterson | 1002 | VP Sales | | 1076 | Firrelli | 1002 | VP Marketing | | 1088 | Patterson | 1056 | Sales Manager (JAPAN, APAC)| | 1102 | Bondur | 1056 | Sale Manager (EMEA) | | 1143 | Bow | 1056 | Sales Manager (NA) | | 1165 | Jennings | 1143 | Sales Rep | | 1166 | Thompson | 1143 | Sales Rep | | 1188 | Firrelli | 1143 | Sales Rep | | ... | ... | ... | ... | +------+-----------+-----------+----------------------------+ 23 rows in set (0.00 sec)
If you have to load a table with parent-child relationships, loading all at once is not always feasible. Look at the sampledata database. There is no physical foreign key from the REPORTSTO column to the EMPLOYEENUMBER column, but if the foreign key had existed, loading all records at once would fail because of the foreign key constraint. Not only that; in this case loading all at once would be impossible because in the file you missed the ID of the parent employee needed for the REPORTSTO column.
So, in this recipe there was one possible solution for loading the table. We loaded all employees, one role at a time, beginning by the president and followed by the roles below in the hierarchy. The transformation that loaded the other roles simply read the file, kept only the employees with the role being loaded, looked for the ID of the parent employee in the hierarchy, and inserted the records. For the roles you could have used fixed values but you used regular expressions instead. In doing so, you avoided calling the transformation once for each different role. For example, for loading the vice-presidents you called the transformation once with the regular expression VP.* which matched both VP Sales and VP Marketing.