8

I have an input dataset whose records will be appended to an existing database. Prior to being appended, the data will go through heavy, time-intensive processing. I want to filter out records from the input dataset which already exist in the database to reduce processing time.

The difference between the input and database is illustrated here: Input and Database Difference

This an overview of the kind of process I am looking at. The input data will eventually feed into the database. Input Processing Workflow

My current solution involves using a Matcher transformer on the combined database and input, then filtering the NotMatched result using a FeatureTypeFilter to retain only the input records.

Is there a more efficient way to obtain the difference features?

  • 1
    are you using an oracle database? you can get the database to do the work between delta tables using MINUS stackoverflow.com/questions/2293092/… – Mapperz Jun 24 '15 at 18:35
  • 2
    Rather than reading in everything from the database, you may want to try using a SQLexecutor. If the _matched_records attribute is 0 on the initiator then it's an add – MickyT Jun 24 '15 at 19:36
4

If you have the database characteristics indicated by the diagram. Small input, tiny overlap, large target. Then the following sort of workspace may work quite efficiently, even though it will be doing multiple queries against the database.

enter image description here

So for each feature read from the input query for the matching feature in the database. Make sure there is suitable indexes in place. Test the _matched_records attribute for 0, do the processing and then insert into the database.

  • I found this to be the fastest solution. I'm guessing because it limits the amount of data that is drawn from the database into FME and keeps the processing SQL-side. – dexgecko Jun 26 '15 at 13:46
4

I didn't use FME, but I had a similar processing task that required using the output of a 5-hour processing job to identify three possible processing cases for a parallel database across a low-bandwidth network link:

  • New features to be added
  • Existing features to be updated
  • Exisitng features to be deleted

Since I did have a guarantee that all features would retain unique ID values between passes, I was able to:

  1. Run a processing script which generated a table of {uID,checksum} pairs across the important columns in the updated table
  2. Used the {uID,checksum} pairs generated in the previous iteration to transmit updates to the target table with the rows in the updated table where the uID was IN a subquery where checksums didn't match
  3. Transmit inserts from the updated table which an outer join subquery indicated had unmatched uIDs, and
  4. Transmitted a list of uIDs to delete features in the external table which an outer join subquery indicated no longer had matching uIDs in the current table
  5. Save the current {uID,checksum} pairs for the next day's operation

On the external database, I just had to insert the new features, update the deltas, populate a temporary table of deleted uIDs, and delete the features IN the delete table.

I was able to automate this process to propagate hundreds of daily changes to a 10-million row table with a minimum of impact to the production table, using less than 20 minutes of daily runtime. It ran with minimum administrative cost for several years without losing sync.

While it's certainly possible to do N comparisons across M rows, using a digest/checksum is a very attractive way to accomplish an "exists" test with much lower cost.

4

Use featureMerger , joining and grouping by the common fields from DATABASE AND INPUT DATA. enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.