Introduction
Information stored in a database is unlikely to remain unchanged. Depending on how these changes occur, bulk updates may be done on the database.
These updates may be as simple as reloading an entire dataset and replacing the existing content. In some cases the table is restructured before reloading, in other cases, the table is truncated (emptied of its contents) but the structure is left as before.
More complex updates involve processing individual records (leaving others untouched). In some cases, the entire record is replaced; in others, only individual fields of a record are updated.
Database Update Functionality
FME's functionality for defining updates depends on the format used. Many formats have been harmonized to provide a consistent user experience, while others have not. This means there are two basic sets of functionality available.
- Simplified, Harmonized Dialogs: Cover most of the major formats
- FME Format Attributes: For formats without a harmonized interface
For more information on basic update functionality, including the differences between harmonized dialogs and format attributes, see the article An Introduction to Incremental Database Updates.
Database Update Methodology
The methods used within an FME workspace depend not only on the database format but also on the source of the updates and the type of update required.
FME can update all data in a database or in a specific table; it can also update single features while inserting or deleting others.
FME can also be used to detect changes between two source datasets and write the differences as database updates.
The following examples illustrate different combinations of functionality and methodology.
Database Access
Safe Software has provided a PostgreSQL training database for reading data in FME. However, this database is read-only. This entire tutorial series needs the ability to write to PostgreSQL (PostGIS). Please follow the instructions in "Creating a PostgreSQL/PostGIS Training Database with FME" to create your own training database.
Note: These tutorials can be completed with any database or your own personal PostgreSQL database.
Articles
Each example includes a series of steps to learn the technique described. Although each example is for a specific database format, the same techniques apply to any database updates carried out with FME.
Example 1: Updating a Whole Table
Converting to PostGIS: Create, Drop, or Truncate a Table: Updating the entire database table is very simple, particularly when the database format uses a simplified, harmonized interface in FME.
Example 2: Carrying out Mixed Operations
Updating Databases: Filtering Features to set Database Update Operation Type: Mixed operations are a combination of Insert, Update, and Delete commands inside a single workspace; for example, Record A needs to be deleted, while Record B needs to be modified.
Mixed operations require the ability to identify which features need which operation. This example demonstrates how to filter data depending on the operation type and how to set the operation. It is illustrated using a harmonized format, but includes instructions on how to apply the same technique using the format attribute fme_where
Example 3: Identifying Updates with Change Detection
Updating Databases: Using Change Detection to Set Operation Type: Expanding on the workspace from Example 2, this example performs mixed operations and identifies the operation type to apply using change detection methods.
Example 4: Updating Individual Fields of a Record
Updating Databases: Updating Individual Fields of a Record: On occasion, it is necessary only to update individual fields in a particular record, not the entire record. This example illustrates how to update only specific fields.
Example 5: UPSERTing Changes
Updating a PostgreSQL Database Using UPSERT: If the database supports it, UPSERTs can be performed in FME using the ChangeDetector.