span8
span4
span8
span4
There are a number of ways to "let your database do the work",we'll take a look at the ways to use FME to perform a more effective write to Oracle.
When writing to a database using FME,errors can occur if there are constraints on the table that are being violated.Constraints are rules that restrict the possible values for a column in a database.Typical constraints include not null,unique,and keys (primary and foreign).The exercise outlined below will address a method to deal with a failure on load caused by writing to tables joined by a foreign key.
Although this exercise uses anOracledatabase,the concepts and methods discussed can be applied to other databases such asPostgreSQL/PostGIS,andSQL Server.
The goal of this exercise is to show the options you have for writing to database tables that have a constraint,such as a foreign key constraint.In the example,you will load a water pipe network (WMAINS) and also create an assets table (WASSETS) that has the pipe manufacturer attribute.The two tables are related through a foreign key constraint (called WMAIN_WASSETS).
Run the Workspace
1.Open the workspace: DatabaseWrite-Start.fmw.Run it.
The workspace is writing to two joined tables in Oracle.The parent table (WASSETS) has MANUFACTURERID as a primary key and the child table (WMAIN) has a primary key of MAINID with MANUFACTURERID as a foreign key.
The workspace fails throwing the following error:
|ERROR |Execution of statement `INSERT INTO "WMAIN" ( "MAINID","ENABLED",...) VALUES ( :"MAINID",:"ENABLED",...)' did not succeed;error was `ORA-02291: integrity constraint (WMAIN_WASSETS) violated - parent key not found'.(serverType=`ORACLE8I',serverName=`',userName=` ',password=`***',dbname=`')
The error occurs because the foreign key constraint (called WMAIN_WASSETS) on the WMAIN table was violated.To satisfy the constraint,you have to write to the parent table before writing the child table that has the foreign key.
The following examples illustrate different ways you can address this problem and write to the related tables.
Example 1 - Using FeatureHolder
The FeatureHolder is used to hold the features directed to the child table (WMAIN).Since there are only a few features being written to the parent table (WASSETS),it is able to complete the write before the FeatureHolder releases the features.This effectively stages the 亚搏在线workflow.
1.Before running this example,you need to clean-up the database tables.Drop and recreate the tables using the SQLCreator transformer.Open the transformer properties,select SQL Statement and select Run… Then Cancel to exit the transformer properties.
2.Add aFeatureHolderbefore the WMAIN table.
3.Run the workspace and inspect the log file and the results.
The FeatureHolder is useful for workspaces with smaller numbers of features.If there are a large number of records being written to the table and held in the FeatureHolder,it will impact the overall performance.
Example 2 - Using Connection Runtime Order
You can control the order of features exiting a transformer using the Connection Runtime Order option.This example uses connection runtime order to ensure parent records are inserted first.
1.Before running this example,you need to clean-up the database tables.Drop and recreate the tables using the SQLCreator transformer.Open the transformer properties,select SQL Statement and select Run… Then Cancel to exit the transformer properties.
2.Delete the FeatureHolder transformer from the previous step.
3.Set the Connection Runtime Order by right clicking on one of the output connectors that exit the AttributeManager.Ensure the WASSET connection is first.This will ensure that the WASSET records are inserted before their corresponding WMAIN record,ensuring that the WMAIN constraint is satisfied.
4.Set the Features Per Transaction to 1.You have to use a transition interval of 1 to guarantee that the WASSETS records has been committed before the WMAIN record is inserted.
5.Run the translation.
As you can see,setting a transaction interval of 1 has a big impact on performance as a commit is carried out for every feature that is written.It does ensure that the records are written into the parent table prior to writing into the child table.
Example 3 - Using FeatureWriter
The FeatureWriter transformer is an alternative way of writing data.Moving the write operation into the workspace 亚搏在线workflow gives you more flexibility over the ordering that feature types are written and how you can pre- and post-process the data.
In this example you'll use SQL calls to disable and then enable the WMAIN_WASSETS foreign key constraint.In between you'll load the data.
1.Drop and create the tables using the SQLCreator as described in Example 2 above so you start with a clean slate.
2.Disable or delete the Oracle writer.
3.Add a SQLCreator for the Oracle database and add the following SQL:
FME_SQL_DELIMITER ;ALTER TABLE "WMAIN" disable CONSTRAINT WMAIN_WASSETS;
The SQLCreator is executed before any of the FME readers are opened,so we're guaranteed the constraint is dropped before any features start to get written.
4.Add a FeatureWriter to the workspace canvas.Open the FeatureWriter parameters dialog.Select the Oracle Spatial format and select your dataset.
5.Import the Oracle tables,WMAIN and WASSETS using Oracle Non-Spatial as the format.
6.Connect the FeatureWriter as shown below in step 7.The order of the features types is not important since we disabled the constraint.
7.Add a SQLExecutor for your Oracle database and add the following SQL:
FME_SQL_DELIMITER ;ALTER TABLE "WMAIN" enable CONSTRAINT WMAIN_WASSETS;
8.Run the workspace and inspect the results.
What are the pros & cons of each example?Perhaps each approach is more appropriate for different scenarios and table constraints such as 1:M M:N etc.
Which gives the best performance?Can you trade performance for simplicity?
Which example might work best for an update 亚搏在线workflow?
Are there any other approaches?
What if there is an error,which approach has the easiest recovery?
Making use of Oracle Sequences when working with Oracle Tables
Writing to an Oracle Table with Foreign Keys
TheAttributeValidatorcan be used to check supported conditions prior to loading.This allows the user the chance to see and correct any features that may cause a failure on write.
Performing spatial queries on database tables using the FeatureReader
Let the Database Do the Work: Reading
Converting to PostGIS: Write to an Existing Table
Viewing and Inspecting PostGIS Data
Working with Alternatives or Versions in Smallworld
Converting to PostGIS: Create,Drop or Truncate a Table
© 2019 亚搏在线Safe Software Inc |Legal