{"id":164,"date":"2006-05-04T20:49:15","date_gmt":"2006-05-05T03:49:15","guid":{"rendered":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2006\/05\/04\/upserts-using-kettlemysql\/"},"modified":"2006-05-04T20:49:15","modified_gmt":"2006-05-05T03:49:15","slug":"upserts-using-kettlemysql","status":"publish","type":"post","link":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/2006\/05\/04\/upserts-using-kettlemysql\/","title":{"rendered":"UPSERTS using Kettle\/MySQL"},"content":{"rendered":"

Oracle Readers: Think MERGE Statement. \ud83d\ude42<\/p>\n

It’s common to need to generate surrogate keys as part of a Data Warehouse \/ Business Intelligence solution. Protecting your users from souce system changes, or using time variant data are just a couple of the reasons why one needs to generate a surrogate key when loading data into a DW.<\/p>\n

Here is a simple example. I’m loading Product data from an XML file into a MySQL table. The transformation in Kettle looks like this:<\/p>\n

\"upsert_kettle_transform.png\"<\/p>\n

The output coming out of the XML file has been flattened using XQuery so that it looks like a flat table struture. Here’s a screen capture of a nifty feature of Kettle where you can preview the output of a step:<\/p>\n

\"upsert_xml_data.png\"<\/p>\n

Ok, this is our source data. The ID you’re seeing is the OLTP key. We’ll refer to it as the Natural Key. This is the ORDER_ID, or CUSTOMER_ID, or well, you get the idea. This is the key that your source application uses in it’s relational structures.<\/p>\n

What we want is to INSERT or UPDATE (aka UPSERT) this record into our DW. We’ll INSERT if we don’t already have this NATURAL key in the DW and generate a surrogate primary key. We’ll UPDATE if we already have this NATURAL key in the warehouse.
\nOur table looks like:
\n\"upsert_product_erd.png\"<\/p>\n

The DDL to create the TABLE is important:<\/p>\n

CREATE TABLE INT_PRODUCT (
\nPRODUCT_ID INTEGER NOT NULL AUTO_INCREMENT<\/strong>,
\nPRODUCT_NAT_ID INTEGER NOT NULL,
\nTITLE TINYTEXT,
\nCONSTRAINT INT_PRODUCT_PK PRIMARY KEY (PRODUCT_ID<\/strong>),
\nUNIQUE INT_PRODUCT_NK (PRODUCT_NAT_ID)
\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;<\/p><\/blockquote>\n

Notice that our PRIMARY KEY is the Surrogate we’ve set up to be an AUTO_INCREMENTING column. This means that MySQL will generate a surrogate when we INSERT a record. All we have to do is get Kettle to match our unique contrainst (INT_PRODUCT_NK) and either INSERT\/UPDATE.<\/p>\n

I’ve used the INSERT \/ UPDATE operator in Kettle. The configuration looks like this:
\n\"upsert_upsert_config.png\"<\/a>
\nWhat we’re doing is configuring Kettle to do a lookup on the table to see if the natural key already exists. If it does, we’ll perform DML (insert or update) on the fields in the lower window.<\/p>\n

After running the transformation my INT_PRODUCT table looks like this.
\n\"upsert_surrogate.png\"
\nNow, I realized I didn’t choose very illustrative natural keys (all are 1,2,3) but the PRODUCT_ID comes from MySQL as a surrogate.<\/p>\n

Subsequent executions of this Kettle transformation will INSERT records as they arrive or UPDATE. MySQL and Kettle in combination are making quick work out of generating these surrogates!<\/p>\n

I invite Kettle experts to chime in on any optimizations\/quirks.<\/p>\n","protected":false},"excerpt":{"rendered":"

Oracle Readers: Think MERGE Statement. \ud83d\ude42 It’s common to need to generate surrogate keys as part of a Data Warehouse \/ Business Intelligence solution. Protecting your users from souce system changes, or using time variant data are just a couple of the reasons why one needs to generate a surrogate key when loading data into […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[15,11],"tags":[],"_links":{"self":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/164"}],"collection":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/comments?post=164"}],"version-history":[{"count":0,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/posts\/164\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/media?parent=164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/categories?post=164"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.nicholasgoodman.com\/bt\/blog\/wp-json\/wp\/v2\/tags?post=164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}