It's very common to have tables in a database where the values for the primary key column can be generated by using a database sequence (in those DBMS that have that feature; for example, Oracle) or simply by adding 1 to the maximum value in the table. Loading data into these tables is very simple. This recipe teaches you how to do this through the following exercise.
There are new offices at Steel Wheels. You have the data of the offices in a file that looks like this:
CITY;PHONE;ADDRESS;COUNTRY;POSTALCODE Sao Paulo;11 3289-3703;Avenida Paulista 1330;Brazil;01310-200 Sao Paulo;11 3104-1116;Rua Boa Vista, 51;Brazil;01014-001 Buenos Aires;11 4582-6700;Cabildo 2127;Argentina;C1428AAT
You have to insert that data into the Steel Wheels database.
For this recipe you will use the Pentaho sample database. If you don't have that database, you'll have to follow the instructions in the introduction of this chapter.
As you will insert records into the office table, it would be good if you explore that table before doing any insert operations. Here you have a sample query:
SELECT OFFICECODE, ADDRESSLINE1, CITY FROM OFFICES ORDER BY OFFICECODE; +------------+--------------------------+---------------+ | OFFICECODE | ADDRESSLINE1 | CITY | +------------+--------------------------+---------------+ | 1 | 100 Market Street | San Francisco | | 2 | 1550 Court Place | Boston | | 3 | 523 East 53rd Street | NYC | | 4 | 43 Rue Jouffroy D'abbans | Paris | | 5 | 4-1 Kioicho | Tokyo | | 6 | 5-11 Wentworth Avenue | Sydney | | 7 | 25 Old Broad Street | London | +------------+--------------------------+---------------+ 7 rows in set (0.00 sec)
- Create a transformation and create a connection to the
sampledatadatabase. - Use a Text file input step to read the
offices.txtfile with data about the new offices. - From the Data Warehouse category drag and drop a Combination lookup/update step, and create a hop from the previous step towards this one.
- Double-click the step, select the connection to the
sampledatadatabase, and typeofficesas the Target table. - Fill the Key fields grid as shown:
- In the Technical key field type
OFFICECODE. For the Creation of technical key fields leave the default values. Close the window. - From the Output category of steps, add an Update step.
- Double-click the step, select the connection to the
sampledatadatabase, and typeofficesas the Target table. - In the first grid add rows with the text
OFFICECODEboth under Table field and under Stream field1. As Comparator choose =. This way, you will update the rows whereOFFICECODEis equal to the office code in your stream. - In the lower grid add a row and type
PHONEboth under Table field and Stream field. Add a second row and typePOSTALCODEin both columns. - Close the window.
- It's time to save the transformation and run it to see what happens.
- As you might guess, three new offices have been added, with primary keys
8, 9, and10. Look at the results:SELECT OFFICECODE, ADDRESSLINE1, CITY FROM offices ORDER BY cast(officecode as unsigned); +------------+--------------------------+---------------+ | OFFICECODE | ADDRESSLINE1 | CITY | +------------+--------------------------+---------------+ | 1 | 100 Market Street | San Francisco | | 2 | 1550 Court Place | Boston | | 3 | 523 East 53rd Street | NYC | | 4 | 43 Rue Jouffroy D'abbans | Paris | | 5 | 4-1 Kioicho | Tokyo | | 6 | 5-11 Wentworth Avenue | Sydney | | 7 | 25 Old Broad Street | London | | 8 | Avenida Paulista 1330 | Sao Paulo | | 9 | Rua Boa Vista, 51 | Sao Paulo | | 10 | Cabildo 2127 | Buenos Aires | +------------+--------------------------+---------------+ 10 rows in set (0.00 sec)
In many situations, before inserting data into a table you have to generate the primary key. If the primary key is a simple sequence or the maximum primary key plus one, you can generate it by using a Combination lookup/update step.
In the recipe, for each row in your file, with the Combination lookup/update step, you look for a record in the offices table with the same values for address, city, and country.
Because the offices are new, (there aren't offices in the table with the same combination of address, city, and country values) the lookup fails. As a consequence, the step generates a key value as the maximum OFFICECODE in the table, plus 1. Then, it inserts a row with the generated primary key and the fields you typed in the grid.
Finally, the step adds to the stream the generated primary key value.
As a last task, we used that key to update the other fields coming into the file: POSTALCODE and PHONE.
The Combination lookup/update step is within the Data Warehouse category, because is mainly used for loading junk dimension tables. But as you could see, it can also be used in the particular situation where you have to generate a primary key.
In the recipe you generated the PK as the maximum plus one, but as you can see in the setting window, a database sequence can also be used instead.
Note
When you use the Combination lookup/update step for inserting, make sure that the columns that are not part of the list of key fields are nullable or have default values.
In the recipe the Combination lookup/update step just inserted new rows. Now suppose that you have a row that existed in the table. In that case the lookup would have succeeded and the step wouldn't have inserted a new row. Instead, it would just have returned the found OFFICECODE. That field would have been added to the stream, ready to be used further in the transformation, for example for updating other fields as you did in the recipe, or for being used for inserting data in a related table.