fme_db_operation - what does "put" do?

brianatsafe wrote:

Hi @tim_wood,

The put option applies to DynamoDB. In DynamoDB it means :

"Creates a new item, or replaces an old item with a new item. "

 

 

You can read about these options here: https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/dynamodb/format_parameters_w.htm

we have added 'PUT' support for specific NoSQL and web service writers that have a concept of replacing a whole row rather than updating part of a row. This is the terminology they use for the formats themselves. This may also be possible in MongoDB as well.

Kind regards, Brian

 

How will fme_db_operation handle the same operation on other formats where it is called UPSERT or MERGE?

 

brianatsafe wrote:

Hi @tim_wood,

The put option applies to DynamoDB. In DynamoDB it means :

"Creates a new item, or replaces an old item with a new item. "

 

 

You can read about these options here: https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/dynamodb/format_parameters_w.htm

we have added 'PUT' support for specific NoSQL and web service writers that have a concept of replacing a whole row rather than updating part of a row. This is the terminology they use for the formats themselves. This may also be possible in MongoDB as well.

Kind regards, Brian

 

Thanks Brian.

 

When you say that PUT updates a whole row rather than part of a row, does this mean that UPDATE only updates part of a row? I thought that UPDATE would replace ALL the values and geometry (or at least the ones that have changed) in a target row with those from the matching record in the output of the FMW.

 

tim_wood wrote:

Thanks Brian.

 

When you say that PUT updates a whole row rather than part of a row, does this mean that UPDATE only updates part of a row? I thought that UPDATE would replace ALL the values and geometry (or at least the ones that have changed) in a target row with those from the matching record in the output of the FMW.

 

 

Hi Tim,

 

 

Yes, the update only will update part of the row. For example, a SQL `UPDATE` statement will change only the columns listed after `SET`, in a statement like: `UPDATE tablename SET colname_0='value_0', colname_1='value_1', ... , colname_n='value_n' WHERE condition;`

 

 

Brian

 

david_r wrote:

How will fme_db_operation handle the same operation on other formats where it is called UPSERT or MERGE?

 

Hi David. I believe that if the Writer supports that particular operation than you can use it in fme_db_operation. I will add a change request to update the AttributeCreator GUI with these options to compliment 'PUT'.

 

 

brianatsafe wrote:

 

Hi Tim,

 

 

Yes, the update only will update part of the row. For example, a SQL `UPDATE` statement will change only the columns listed after `SET`, in a statement like: `UPDATE tablename SET colname_0='value_0', colname_1='value_1', ... , colname_n='value_n' WHERE condition;`

 

 

Brian

 

OK so how does this fit with writing to ArcSDE? See...

 

https://knowledge.safe.com/articles/1193/updating-inserting-and-deleting-features-in-an-unv.html?childToView=37954#comment-37954

 

Do I have to put all the fields I want updated in "Update key fields"? I thought that was the unique ID for the row.

 

 

Or is it like this article, where Mita says that any fields you send to the writer will be updated?

 

https://knowledge.safe.com/articles/877/update-only-certain-columns-in-a-database-table.html

 

 

We have some big datasets where truncating and repopulating would result in the layer being incomplete for too long. I thought I could compare old and new datasets and set fme_db_operation accordingly to update the ArcSDE data in a more precise and efficient way. Have I misunderstood fme_db_operation and/or the ArcSDE Writer?

 

tim_wood wrote:

OK so how does this fit with writing to ArcSDE? See...

 

https://knowledge.safe.com/articles/1193/updating-inserting-and-deleting-features-in-an-unv.html?childToView=37954#comment-37954

 

Do I have to put all the fields I want updated in "Update key fields"? I thought that was the unique ID for the row.

 

 

Or is it like this article, where Mita says that any fields you send to the writer will be updated?

 

https://knowledge.safe.com/articles/877/update-only-certain-columns-in-a-database-table.html

 

 

We have some big datasets where truncating and repopulating would result in the layer being incomplete for too long. I thought I could compare old and new datasets and set fme_db_operation accordingly to update the ArcSDE data in a more precise and efficient way. Have I misunderstood fme_db_operation and/or the ArcSDE Writer?

 

It's pretty simple once you understand the mechanism. First, all the features entering the output feature type (writer) should contain the attribute fme_db_operation. Depending on the value, one of the following will happen:

 

 

fme_db_operation=INSERT

 

The feature will be appended to the output table.

 

 

fme_db_operation=UPDATE

 

The feature must have an attribute that has the same name as the "update key field". Any existing records in the database with a matching value will be updated with the values from the incoming feature.

 

 

fme_db_operation=DELETE

 

The feature must have an attribute that has the same name as the "update key field". Any existing records in the database with a matching value will be deleted.

 

 

Your use case is perfect for fme_db_operation, but you could also consider using the DatebaseUpdater and DatabaseDeleter transformers.

 

 

brianatsafe wrote:

 

Hi Tim,

 

 

Yes, the update only will update part of the row. For example, a SQL `UPDATE` statement will change only the columns listed after `SET`, in a statement like: `UPDATE tablename SET colname_0='value_0', colname_1='value_1', ... , colname_n='value_n' WHERE condition;`

 

 

Brian

 

I got inserts and deletes working straight away, but updates took a bit longer to do. I started off listing all the fields in "Update/Delete Key Fields" because I might be updating values for any of the fields. But this didn't work. I discovered I only needed to specify the Asset_ID which is the unique ID.

 

I presume that "Update/Delete Key Fields" is one field, or multiple fields that must match between the data being sent to the Writer and the data already in the database. Any records found in the database with values matching the Key Field(s) will be updated accordingly.