Has anyone have experience of using ChangeDetector/ UpdateDetector in FME. How is the performance like. Any best practice suggestion regarding reading/writing 100 feature classes?
The new DB is on different SDE/SQL Server version, hence I would prefer to use FME rather than SQL replication for this.
That's going to be tough to do with only FME, simply reading 4 million records by itself can take quite a while, and repeating it every hour it's going to put a lot of strain on both the database and network, not to speak of the machine running FME to detect changes.
My recommendation would be to introduce a new timestamp column in the existing feature classes, and e.g. use a table trigger to update the timestamp every time a record is updated. That way it's easy to only ask the database for the records changed since the last timestamp the workspace was executed.
That's going to be tough to do with only FME, simply reading 4 million records by itself can take quite a while, and repeating it every hour it's going to put a lot of strain on both the database and network, not to speak of the machine running FME to detect changes.
My recommendation would be to introduce a new timestamp column in the existing feature classes, and e.g. use a table trigger to update the timestamp every time a record is updated. That way it's easy to only ask the database for the records changed since the last timestamp the workspace was executed.
@david_r
Thanks David.
I have thought about this and can obtain updated records since last job run. However some records can be deleted in source table, which I need to delete in target table too
If your source database is versioned, the ArcSDE reader has an option to only read the difference between a version and the base version.
That would allow for an easy change detection on the database side.
If you create a new version after each extraction, the next day you only need the difference between that version and the base version.
You can find the information here
Hope this helps.
@sunsilk11 Enterprise (ArcSDE) Geodatabase supports two options for versioning:
- Versions - tree or branch as suggested by @erik_jan
- Archival or Historical versions - as suggested by @david_r
Both are pretty intrusive in your database, if you're not already using them.
A variant on the historical versioning is to implement a history table. The original SDE offered this as JTX I think. Table triggers can be used to populate the history table with the ID, type of edit (insert,update, delete) and the old JTX included a GML package the showed the changes.
@sunsilk11 Enterprise (ArcSDE) Geodatabase supports two options for versioning:
- Versions - tree or branch as suggested by @erik_jan
- Archival or Historical versions - as suggested by @david_r
Both are pretty intrusive in your database, if you're not already using them.
A variant on the historical versioning is to implement a history table. The original SDE offered this as JTX I think. Table triggers can be used to populate the history table with the ID, type of edit (insert,update, delete) and the old JTX included a GML package the showed the changes.
Thanks @markatsafe and @erik_jan
I am looking at the versioning option. I do have versioning enabled and therefore can get the changes since last job run.
When writing to Target feature class, I assume the FME operation should be set to fme_db_operation ?
@sunsilk11 Enterprise (ArcSDE) Geodatabase supports two options for versioning:
- Versions - tree or branch as suggested by @erik_jan
- Archival or Historical versions - as suggested by @david_r
Both are pretty intrusive in your database, if you're not already using them.
A variant on the historical versioning is to implement a history table. The original SDE offered this as JTX I think. Table triggers can be used to populate the history table with the ID, type of edit (insert,update, delete) and the old JTX included a GML package the showed the changes.
@sunsilk11 Correct use the fme_db_operation option. When FME reads from your ArcSDE version, it will automatically set the fme_db_operation value.
@sunsilk11 Enterprise (ArcSDE) Geodatabase supports two options for versioning:
- Versions - tree or branch as suggested by @erik_jan
- Archival or Historical versions - as suggested by @david_r
Both are pretty intrusive in your database, if you're not already using them.
A variant on the historical versioning is to implement a history table. The original SDE offered this as JTX I think. Table triggers can be used to populate the history table with the ID, type of edit (insert,update, delete) and the old JTX included a GML package the showed the changes.
@markatsafe. Thanks Mark. This seems to work ok.
Just to confirm, does the match columns need to be just the unique field or all the fields excluding OBJECTID, SHAPE_LENGTH ? How do I ensure it is updating the target table correctly with the delta
@markatsafe. Thanks Mark. This seems to work ok.
Just to confirm, does the match columns need to be just the unique field or all the fields excluding OBJECTID, SHAPE_LENGTH ? How do I ensure it is updating the target table correctly with the delta
@sunsilk11 It would normally be just the OBJECTID or the GlobalID for you Match Columns. Match Columns is the key to join your FME feature to the database record
@sunsilk11 Enterprise (ArcSDE) Geodatabase supports two options for versioning:
- Versions - tree or branch as suggested by @erik_jan
- Archival or Historical versions - as suggested by @david_r
Both are pretty intrusive in your database, if you're not already using them.
A variant on the historical versioning is to implement a history table. The original SDE offered this as JTX I think. Table triggers can be used to populate the history table with the ID, type of edit (insert,update, delete) and the old JTX included a GML package the showed the changes.
@markatsafe Hi Mark, Update/Insert seems to work fine. The delete doesn't work i.e. it's not deleting the feature on target db. The match column is PID, which is unique for this feature class.
The warning I get in log is below. The PID value is obviously null in source as it doesn't exist.
This is similar to bug mentioned in https://knowledge.safe.com/questions/95866/updating-data-in-an-esri-sde-from-an-esri-gdb-usin.html
Any suggested workaround, as we have FME 2019.1 on production, hence not ready to upgrade FME version ?
The Match Column attribute 'PID' has a null value or is not present on feature. Feature will thus match no rows. Rejecting feature
No rows matched DELETE query to table/feature class 'ElectricFC' where PID IS NULL
@markatsafe Hi Mark, Update/Insert seems to work fine. The delete doesn't work i.e. it's not deleting the feature on target db. The match column is PID, which is unique for this feature class.
The warning I get in log is below. The PID value is obviously null in source as it doesn't exist.
This is similar to bug mentioned in https://knowledge.safe.com/questions/95866/updating-data-in-an-esri-sde-from-an-esri-gdb-usin.html
Any suggested workaround, as we have FME 2019.1 on production, hence not ready to upgrade FME version ?
The Match Column attribute 'PID' has a null value or is not present on feature. Feature will thus match no rows. Rejecting feature
No rows matched DELETE query to table/feature class 'ElectricFC' where PID IS NULL
@sunsilk11 The fix is available in 2019.2 or 2020 betas.
A possible workaround would be to use OBJECTID if it still exists on the feature.
@david_r
Thanks David.
I have thought about this and can obtain updated records since last job run. However some records can be deleted in source table, which I need to delete in target table too
@sunsilk11
I'm late to the party... but you could still certainly use database trigger (on delete) to populate a new history table that tracks deletes and by storing the date of delete timestamp and the approproate identifying key, then utilize this table in your workflow to identify records that were deleted since the last run.