Amazon Redshift Writer Feature Type Parameters

To access feature type parameters, click the gear icon on a feature type in the workspace. This opens the Feature Type Parameter Editor.

Tip  To always display the editor in FME Workbench, you can select View > Window Layout > Parameter Editor.

General

All feature types share similar General parameters, which may include Feature Type Name, Reader or Writer information, and Geometry.

In most Writer Feature Type parameter dialogs, you can also control Dynamic Schema Definitions. Some database formats accept Table or Index Qualifier prefixes on the output table feature type.

Note  Redshift provides automatic table optimization; tables created by FME are set for automatic management by Redshift.
Important  These parameters apply only to a selected feature type, not to the entire writer. If a feature type parameter listed here conflicts with a writer-level parameter, then the writer parameter will be ignored and this feature type parameter will be used.

Table Settings: General

This parameter lets the user specify how features will be written into the destination table. Supported feature operations are:

  • Insert: Append rows onto the destination table using attributes on features.
  • Update: Update existing table columns using attributes on features. A selection method must be specified in the Row Selection group.
  • Delete: Delete existing table rows. A selection method must be specified in the Row Selection group.
  • fme_db_operation: The feature operation will be determined by the attribute fme_db_operation on each input feature. A selection method must be specified in the Row Selection group. The value of fme_db_operation will be processed as follows:
    • If the value is null, empty, or missing, it will be treated as Insert.
    • The value will next be matched to Insert, Update, and Delete, case insensitively.
    • If there is no match, the feature will be rejected.
    • If there is a match, the matched feature operation will be performed on the feature.
Tip  The fme_db_operation attribute will cause feature rejection when Feature Operation is set to Insert, Update, or Delete.

More information on Feature Operations.

Controls how the feature type handles destination tables:

  • Use Existing – Write to an existing table If the destination table does not exist, the translation will fail.
  • Create If Needed – Create the destination table if it does not exist.
  • Drop and Create – (This option is not available in all formats.) Drop the destination table if it exists, and then create it. The writer will drop and re-create the table before writing any features to it. Tables will be overwritten when the first input feature is processed. If no features are sent to a feature type, then the corresponding table will not be overwritten.
  • Truncate Existing – (This option is not available in all formats.) If the destination table does not exist, the translation will fail. Otherwise, delete all rows from the existing table.

When inserting into a table, Row Selection is ignored. When updating and deleting from a table (if applicable, based on a format's available Feature Operation options), a condition needs to be specified for selecting which rows to operate on. This parameter group offers two methods to construct the selection condition:

Match Columns

The columns specified in the corresponding column picker dialog will be used for matching destination rows. All matching rows will be selected for update or delete. If any feature attributes corresponding to the specified match columns contain null or missing values, the feature will be rejected.

WHERE Clause

This parameter opens a WHERE Clause Builder. You can also type a WHERE clause inline, without launching the Builder. It is optional to start the clause with the word WHERE.

The WHERE Clause Builder makes it easy for users to reference feature attribute values, destination table columns, and invoke FME functions. The WHERE clause is first evaluated as an FME expression, before being passed onto the destination database.

If the WHERE clause is incorrect or if its evaluation results in failure, the translation will fail. Otherwise, if the WHERE clause passes FME evaluation but it is SQL invalid, the feature will be rejected or the translation will fail.

For advanced users, conditional FME expressions created through the Conditional Value editor can be used to create WHERE clauses.

Tip  You can set the WHERE Clause to an attribute. This supports workspace migration and existing workflows involving fme_where. (Direct support for fme_where has been deprecated.) To advanced users who are accustomed to using fme_where, if Feature Operation is set to Update, Delete, or fme_db_operation, an fme_where attribute that conflicts with Match Columns or WHERE Clause will result in feature rejection.

Table Settings: Advanced

Determines if the database function to vacuum and analyze the table is performed once the table is successfully written. This will build statistics for the table.

Values cannot usually be written to serial columns; available values come from the database. This writer option allows the writer to take values from incoming features.

If one of the attributes on an incoming feature corresponds with a serial column in the database table, there are two options:

  • No (default) – The value will come from the database sequence.
  • Yes – The value will come from the feature (which is the same behavior as other column types).
Note  For more information on SERIAL data types, see PostgreSQL documentation, section 8.1.4. Serial Types.