Solved

How to perform sequential delete and insert operations on same writer ?

  • 17 October 2018
  • 7 replies
  • 30 views
C
Badge

Hi,

I try to import an Excel file in a Postgres table.

But before inserting the rows of the Excel file, I have to delete all records in the destination table where the values of a column from Excel file are matching the values of a field in the Postgres table.

I achieved to perform the delete operation with an Aggregator transform that performs a groupby operation on the Excel column and a SQLExecutor transformer that deletes in the destination table all records whose values are matching one of the groupby values.

That part works fine.

But now, I don't find out how to plug after this the insert operation on a Postgres writer.

Because the SQLExecutor returns only the groupby values from the initiator.

I don't think I can use a DatabaseUpdater transformer with fme_db_operation for this context.

Thanks for any help.

icon

Best answer by takashi 18 October 2018, 02:26

View original

7 replies

takashi
Rank
Userlevel 2
Badge +17

Hi @christian_b, if you saved all the attributes into a list attribute before executing the DELETE statement, you can restore the input features with the ListExploder later. The Aggregator (Group By: value, Mode: Geometry - Assemble One Level) with the Generate List option can also be used instead of the ListBuilder in this screenshot.

Alternatively, this workflow is possible. Note: The Connection Runtime Order at the Unique port of the DuplicateFiter has to be set to run the upper AttributeCreator first. See here to learn more about Connection Runtime Order: Controlling Transformer Flow at Runtime

Hope this helps.

takashi
Rank
Userlevel 2
Badge +17

Hi @christian_b, if you saved all the attributes into a list attribute before executing the DELETE statement, you can restore the input features with the ListExploder later. The Aggregator (Group By: value, Mode: Geometry - Assemble One Level) with the Generate List option can also be used instead of the ListBuilder in this screenshot.

Alternatively, this workflow is possible. Note: The Connection Runtime Order at the Unique port of the DuplicateFiter has to be set to run the upper AttributeCreator first. See here to learn more about Connection Runtime Order: Controlling Transformer Flow at Runtime

Hope this helps.

The DatabaseDeleter could be another option.

 

 

C
Badge

Hi Takashi, thank you very much for this very accurate answer.

I found a solution this morning by using a FeatureMerger transformer behind my SQLExecutor and it works but I think it's not very elegant...

Your solution seems far better and I will give it a try.

About your second answer with the DatabaseDeleter transformer, I don't think it would work because the delete operation has to be done before the insert operation and in your graph both operations are working in parallel.

Have a nice day !

Christian

G
Badge +3

@christian_b

calling the sql executer for each value is not efficient at all.

Passing a list trough is not efficient either. (unless list is small )

I would go for @takashi's last version and replace the database deleter with a sql executor performing the delete with an "IN" statement (you can hold quite a lot of value's in a string). "IN" string created on attribute_value with a concatenator or attribute creator.

Call the sql executer with a sample of 1.

After that you can execute the writer.

C
Badge

@christian_b

calling the sql executer for each value is not efficient at all.

Passing a list trough is not efficient either. (unless list is small )

I would go for @takashi's last version and replace the database deleter with a sql executor performing the delete with an "IN" statement (you can hold quite a lot of value's in a string). "IN" string created on attribute_value with a concatenator or attribute creator.

Call the sql executer with a sample of 1.

After that you can execute the writer.

Hi gio, thank you for this answer.

 

Some points you mention seem very interesting but I don't really understand how to achieve your solution.

 

Is this your procedure :

 

1- DuplicateFilter

 

2- Create "IN" attribute with concatenator or attributecreator that contains the distinct filtered values

 

3- SQL Executor like "Delete from my_table where code IN @value(my_in_attribute)"

 

--> what do you mean with "sample of 1" ?

 

4- Execute the writer: but how to do this after my SQLExecutor that returns only the filtered values ?

 

 

G
Badge +3

@christian_b

Create the In string by concatenation.

Listbuilder (grouped by feature type), listduplicateremover on the value attribute (or a duplicate filter prior to listbuilder).

By "sample of 1" I mean only use one feature to trigger the sql-executor. (if u listbuild on the sheet you probably end up with just one feature anyway)

You can execute the writer by (for instance).

Create a "trigger" attribute after the "deleter" SQL-executor. Use the fme_feature type name.

Link/relate/merge this to the features to be written unconditionally (variable setter/retriever could also possible). Which needs the fme_feature_type exposed on the sheet.

Then test if the requestor and the supplier have same trigger value. Merged go to the writer.

C
Badge

@christian_b

Create the In string by concatenation.

Listbuilder (grouped by feature type), listduplicateremover on the value attribute (or a duplicate filter prior to listbuilder).

By "sample of 1" I mean only use one feature to trigger the sql-executor. (if u listbuild on the sheet you probably end up with just one feature anyway)

You can execute the writer by (for instance).

Create a "trigger" attribute after the "deleter" SQL-executor. Use the fme_feature type name.

Link/relate/merge this to the features to be written unconditionally (variable setter/retriever could also possible). Which needs the fme_feature_type exposed on the sheet.

Then test if the requestor and the supplier have same trigger value. Merged go to the writer.

Thanks gio for the precision.

 

I think I understand the process, but I'm not yet comfortable enough with FME.

 

I will give a try to this solution to discover another aspects of FME.

 

 

Reply


Community Stats

28,670
Posts
109,365
Replies
36,531
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.