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 pushdown optimization

Rules and guidelines for pushdown optimization

Use the following rules and guidelines when configuring pushdown optimization for an Amazon Redshift database:
  • To push TO_DATE() and TO_CHAR() to Amazon Redshift, you must define the string and format arguments.
  • If you use the NS format as part of the ADD_TO_DATE() function, the agent does not push the function to Amazon Redshift.
  • If you use any of the following formats as part of the TO_CHAR() and TO_DATE() functions, the agent does not push the function to Amazon Redshift:
    • - NS
    • - SSSS
    • - SSSSS
    • - RR
  • To push TRUNC(DATE), GET_DATE_PART(), and DATE_DIFF() to Amazon Redshift, you must use the following formats:
    • - D
    • - DDD
    • - HH24
    • - MI
    • - MM
    • - MS
    • - SS
    • - US
    • - YYYY
  • To copy data from Amazon S3 to Amazon Redshift, you must use multiple data files by splitting large files. For more information, see the Amazon documentation.
  • When you use an upper case in the column name of a JSON file and configure pushdown optimization, the mapping fails with the following error:
    Error Reason: Invalid JSONPath format: Member is not an object.
  • When you configure a mapping with pushdown optimization and define an ORC source with date/time values, the Secure Agent might not write a few values correctly to the Redshift target.
  • When you define a Parquet source with decimal values having precision greater than 19,0 and use
    COPY
    command to write to a Redshift target, the mapping fails with pushdown optimization.
  • If the Amazon S3 bucket region and the Amazon Redshift region are different, specify the
    REGION
    attribute in the
    COPY
    command to enable full pushdown optimization.
    Does not apply to ORC and Parquet files.
  • If the data has values that are greater than the precision values, specify the attribute
    TRUNCATECOLUMNS=ON.
    Does not apply to ORC and Parquet files.
  • For the ORC and Parquet file types, specify
    AWS_IAM_ROLE
    in the COPY command, to enable full pushdown optimization.
  • You cannot use the assume role when the source has a Parquet or ORC file format and you enable full pushdown optimization for the mapping task.
  • You can use
    REPLACESTR()
    only to replace a single string value with a new string value.
    Syntax
    REPLACESTR (CaseFlag, InputString, OldString, NewString).
  • To push
    SUBSTR()
    to Amazon Redshift, you must define an integer value for the length argument.
  • You cannot enable full pushdown optimization for a mapping task when the task contains a mapping with a single transformation connected to multiple transformations downstream and vice-versa.
  • When you read data from an Amazon S3 flat file with Shift-JIS encoding, write to an Amazon Redshift target, and enable full pushdown optimization, the mapping fails.
  • When you configure a mapping that reads an Amazon S3 Avro source with column names in uppercase letters and uses an Amazon Redshift V2 connection to write data to an Amazon Redshift target, the COPY command writes blank rows to the target.
  • You cannot enable full pushdown optimization for an Avro source with Date and Decimal data types.