Table of Contents

Search

  1. Preface
  2. Part 1: Introduction to Amazon Redshift connectors
  3. Part 2: Data Integration with Amazon Redshift V2 Connector
  4. Part 3: Data Integration with Amazon Redshift Connector

Amazon Redshift Connectors

Amazon Redshift Connectors

Rules and guidelines for configuring override target query

Rules and guidelines for configuring override target query

You can specify an override target query 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.