Amazon Redshift Connectors

Back Next

Rules and guidelines to override the target query

You can specify a target query override to override the update query that the Secure Agent generates for the update operation.

Consider the following rules and guidelines when you use the override target query property for an Amazon Redshift target:

Select

Update

for the

Treat source rows as

property in the advanced target properties.

You cannot use the override target query for an upsert operation.

Specify the override target query in the following format:

UPDATE <Target Schema Name here>.<Target TABLE NAME here>
  SET <column1> = {{TU}}.<column1>,
      <column2> = {{TU}}.<column2>,
      <column> = {{TU}}.<column>
  FROM {{TU}}
  WHERE <Target Schema Name>.<Target TABLE NAME here> .<update column1> = {{TU}}. <update column1>
  AND <Target Schema Name>.<Target TABLE NAME here> .<update column2> = {{TU}}. <update column2>
  AND … <Target Schema Name>.<Target TABLE NAME here> .<update column> = {{TU}}. <update column>

Column names for :TU must match the target table column names.

The

WHERE

column is mandatory in the query.

You cannot use left, right, or full outer joins in the

FROM

column of an override target query statement.

All the column names in the query must be qualified names and the table name and schema name must be associated with the column names.

Note: For the

SET

columns, Amazon Redshift does not allow a column name with a table. SET columns must have column names only.

You must specify the override target query with a valid SQL syntax because Amazon Redshift V2 Connector replaces :TU with a temporary table name and does not validate the update query.

You cannot change the order of the column mappings using the override target query.

You cannot specify multiple override target queries for an update operation.

When you use the override target query option, do not additionally configure an override for the schema and table name from the

Schema Name

and

Target table name

fields.

Amazon Redshift V2 targets in mappings

Download Guide

Watch

Comments

Cloud Connectivity Homepage