Skip to main content
Solved

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

  • October 17, 2018
  • 7 replies
  • 101 views
C
Forum|alt.badge.img

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.

Best answer by takashi

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.

View original
Did this help you find an answer to your question?

7 replies

takashi
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • Best Answer
  • October 18, 2018

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
Contributor
Forum|alt.badge.img+21
  • takashiContributor
  • Contributor
  • October 18, 2018
takashi wrote:

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
Forum|alt.badge.img
  • christian_b
  • Author
  • October 18, 2018

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

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • October 18, 2018

@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
Forum|alt.badge.img
  • christian_b
  • Author
  • October 19, 2018
gio wrote:

@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 ?

 

 

gio
Contributor
Forum|alt.badge.img+15
  • gioContributor
  • Contributor
  • October 19, 2018

@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
Forum|alt.badge.img
  • christian_b
  • Author
  • October 21, 2018
gio wrote:

@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


Helpful Members This Week

Community Stats

30,982
Posts
117,399
Replies
38,760
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.