Skip to main content
Question

control sql table load order (edited for more info)

tnarladni
Enthusiast
Forum|alt.badge.img+16

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
Evangelist
Forum|alt.badge.img+50
  • geomancerEvangelist
  • Evangelist
  • January 24, 2022

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

hkingsbury
Celebrity
Forum|alt.badge.img+55
  • hkingsburyCelebrity
  • Celebrity
  • January 24, 2022

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

jorge_rosales
Contributor
Forum|alt.badge.img+8

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
Evangelist
  • takashiEvangelist
  • January 25, 2022

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
Enthusiast
Forum|alt.badge.img+16
  • tnarladniEnthusiast
  • Author
  • Enthusiast
  • January 25, 2022

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
Evangelist
  • takashiEvangelist
  • January 25, 2022
tnarladni wrote:

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
Enthusiast
Forum|alt.badge.img+16
  • tnarladniEnthusiast
  • Author
  • Enthusiast
  • January 26, 2022
takashi wrote:

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
Evangelist
Forum|alt.badge.img+50
  • geomancerEvangelist
  • Evangelist
  • January 26, 2022
takashi wrote:

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


Helpful Members This Week

Community Stats

31,501
Posts
119,418
Replies
39,118
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement

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.