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 the 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 the vice-president
A vice-president 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. The following are the 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 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.Use a Join rows step to merge the employee data with the level the transformation will be filtering on. Leave the condition field empty so that the level from the Get Variables step will be added to each record.
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 step 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 Dummy step to send employee records that do not have an employee record parent to. This step will act as an error handling step.
Add a Table Output step and use it to insert the records in the table
employees. Your final transformation looks like the following:
Finally, create a job to put everything together. Drag a START entry and four Transformation job entries to the work area. Link all of them in a row.
Use the first Transformation entry to execute the transformation that loads the president.
Double-click on 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 12 for the third Transformation entry, but this time, type
.*Manager.*as the value for theLEVELparameter.Repeat step 12 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 in the following query:
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, it 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 loading all records 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 with 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.