An Introduction to Incremental Database Updates

Updating Database Records

This article discusses the different workbench GUI interfaces for database writers when undertaking incremental updates. For a full discussion and examples on database incremental updates with FME see the Tutorial: Updating Databases.

The majority of database formats supported by FME allow existing records to be updated. There are two broad approaches depending on whether the database writer Feature Type has been 'harmonized' or not.

By harmonizing the format interfaces, there is a greater consistency between formats, and settings that become familiar to a user for one format, are matched by every other format.

Currently, FME provides this simplified and harmonized interface for the most-used database formats. Other database formats - while waiting for harmonization - still support updates via a method of attribute tags.

Harmonized Formats

The format documentation indicates whether the database has been harmonized for incremental updates. Below is an example of a harmonized format compared with and unharmonized format.
 

For harmonized database writer feature types, FME provides a common GUI interface for controlling database updates and - in order to provide a consistent experience - this interface is standardized to be the same regardless of database format. One of the features of harmonized databases is a common table panel on the writer feature type that includes fme_db_operation in the Feature Operation menu:

Incremental Updates - Insert, Update & Delete

Usually you need to carry out a mixture or Insert, Delete, and/or Update tasks in the same workspace, where the operation type varies for each feature - incremental updates.

In this case the operation to carry out is defined in an attribute called fme_db_operation, with a value of either INSERT, DELETE, or UPDATE. The Feature Operation parameter is then set to fme_db_operation:

A similar technique is used for database formats that are not yet harmonized...

Update only

Updating records with a harmonized format is done through the Feature Type (Table) parameters dialog. In basic cases updates are done with the primary key field:

The user changes the Feature Operation parameter to Update, and in the Match Columns parameter selects the attribute that defines the key in the incoming data.

When run, each feature will overwrite a database record, where its key attribute value matches the database key column.

An alternative is to use the WHERE Clause parameter:

Here the user carries out the same update on "parkid", but only where the record status field is 'Active'

Non-Harmonized Formats

Updating Records

Non-harmonized formats have no consistent interface. Some - like this MySQL writer - have a Writer Mode parameter:

Others - like this DB2 writer - do not:

However, they all do support the use of two format attributes: fme_where and fme_db_operation

As with harmonized formats, the operation to carry out for each record is defined in an attribute called fme_db_operation, with a value of either INSERT, DELETE, or UPDATE. If all features are to be updated, then all features should be assigned an fme_db_operation value of UPDATE, using a transformer such as the AttributeManager.

The Update Column/Where Clause is similarly inconsistent. The above screenshots also show that the MySQL table has no Update Column parameter, whereas the DB2 table does.

They do both support defining this information using an attribute called fme_where:

Here - for example - the user writes to a MySQL database. Features are set to be updates, with the update happening where parkid (the database field) matches ParkId (the feature attribute).

fme_db_operation also supports PUT as a value for specific NoSQL and web service writers such as the DyanamoDB format. PUT denotes that the whole row should be replaced (rather than parts of it updated). The INSERT and UPDATE options still apply if used.