Question

control sql table load order (edited for more info)

  • 24 January 2022
  • 8 replies
  • 1 view
tnarladni
Rank
Badge +9

I have data that would need to be loaded into different tables. But I need to load them to load in a specific table order because of foreign key constraint. I have put on a connection runtime order which only controls the very next transformer. One of the data paths has a statistics calculator so i thought if i put a statistics calculator on the path that needs to be loaded last, this would give me the order I need but they are still going first.

The first 2 are going in at the right order. The 3rd has the statistics calculator. It also has a tester where only the records that passed will be in the statistics calculator. The records that failed do not need to go anywhere. Also, all records can fail the tester. In that case, no related IDs are created for the 4th table so then the Foreign Key Constraint is not violated.

I added 2 feature Holders and 2 statistics calculators on the 4th path hoping the 3rd path will finish first, but the statistics calculator for the 3rd path would stop, and all the extra transformers in path 4 would complete first.

load_order

8 replies

geomancer
Rank
Userlevel 4
Badge +36

Several possibilities are discussed in the article Working with Foreign Keys: Writing Database Tables.

hkingsbury
Rank
Userlevel 5
Badge +29

i'd look at using FeatureWriters. Once the first table has had the features written, you can use the summary output to trigger the next FeatureWriter and so on

 image

Check out the Abley FME Firesides on Youtube: https://www.youtube.com/@ableyltd
jorge_rosales
Badge

If only the order is important, you can use as many readers as there are dependency levels in your data model. One reader for tables with no foreign key constraints, another reader for tables with a foreign key, another reader for tables with a foreign key that points to a table with a foreign key, and so on. Order readers in the workspace (to force write order) and use a single FeatureWriter transformer.

 

On the other hand, if you need to retrieve the primary keys of the inserted tables, you can chain a Feature Writer and FeatureReader and then use a FeatureMerger to assign created primary keys to the child tables.

 

takashi
Rank
Userlevel 2
Badge +17

Hi @tnarladni​ , which one are you using to insert records into the tables, regular writer, FeatureWriter, or SQLExecutor?

It would be helpful for thinking of a possible solution, if you could post screenshots illustrating the current workflow and your desired writing order.

tnarladni
Rank
Badge +9

Thinking thru this...i would take all the features from failed port of the tester in path 3 and write that to the table as it would not violate constraint. all passed features will go through the statistics calculator in path 3 and the summary data is loaded to table 3 via a feature writer. the Complete data would need to be feature merged with the feature writer's result otherwise there's not a guarantee they wont load first. Let me know if i'm on the right track?

 

load_order_solution

takashi
Rank
Userlevel 2
Badge +17

Thinking thru this...i would take all the features from failed port of the tester in path 3 and write that to the table as it would not violate constraint. all passed features will go through the statistics calculator in path 3 and the summary data is loaded to table 3 via a feature writer. the Complete data would need to be feature merged with the feature writer's result otherwise there's not a guarantee they wont load first. Let me know if i'm on the right track?

 

load_order_solution

I think @hkingsbury​ 's approach could solve the problem easily. This screenshot illustrates how you can wait for writing table1, table2 and table3 to complete before starting to write table4.sql-loading-order-mockup

tnarladni
Rank
Badge +9

I think @hkingsbury​ 's approach could solve the problem easily. This screenshot illustrates how you can wait for writing table1, table2 and table3 to complete before starting to write table4.sql-loading-order-mockup

except if all records failed the tester in path 3, merge would not work. but i would still want all records loaded in table4

geomancer
Rank
Userlevel 4
Badge +36

I think @hkingsbury​ 's approach could solve the problem easily. This screenshot illustrates how you can wait for writing table1, table2 and table3 to complete before starting to write table4.sql-loading-order-mockup

So give table 3 it's own FeatureWriter.sql-loading-order-mockup

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
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.