Hi, I'm Ask INFA!
What would you like to know?
ASK INFAPreview
Please to access Ask INFA.
Not Found
English

Page Not Found

The page or file you are looking for has been moved or no longer exists.

The following links might help you find the content you need:


You can also search the Documentation Portal or visit the home page.

English
  • Data Integration Connectors
  • All Products

Table of Contents

Search

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

Amazon Redshift Connectors

Amazon Redshift Connectors

Back Next

Rules and guidelines for functions

Rules and guidelines for functions

Use the following rules and guidelines when pushing functions to 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
  • 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.
  • When you push MD5() to Amazon Redshift, the Secure Agent returns all the MD5 fields in the lower case. However, when you run a mapping without pushdown optimization, the Secure Agent returns the MD5 fields in the upper case.
  • When you use the IS_NUMBER function in a transformation and the input data contains d or D, for example in formats such as +3.45d+32 or +3.45D-32 , the function returns False or 0.
  • When you use the IN function in an expression, you must not include the
    CaseFlag
     attribute.
  • When you use the IN function and the arguments contain date and timestamp values, you must include the TO_DATE function in the expression.
  • When you use the REG_REPLACE function in an Expression transformation, ensure that the expressions used in the argument are supported by AWS.
  • When you use the ISNULL() function in an Expression transformation the column values are parsed differently when you run the mapping in the same cluster environment or across different cluster environments. This occurs because the UNLOAD and COPY commands parse the NULL and empty string values differently across clusters.
    You can enable the
    useTempTableForRedshiftAPDO
     property when you push down functions across different clusters. The NULL and empty string values present in the columns are considered as NULL only. However, when you configure a mapping within the same cluster, NULL is considered as NULL and an empty string is considered as an empty string in the columns.
    For mappings without pushdown optimization, ISNULL() function is parsed differently based on whether you select the
    Treat NULL Value as NULL
     option.
    When you select the
    Treat NULL Value as NULL
     option, the NULL and empty string values are considered as NULL. When you don't select the
    Treat NULL Value as NULL
    option, the NULL and empty string values are considered as empty string.

Rules and guidelines for aggregate functions

Use the following rules and guidelines when pushing aggregate functions to an Amazon Redshift database:
  • You cannot use conditional clauses in the aggregate expression functions.
  • You can use non-aggregate functions in aggregate expressions.
  • You cannot use nested aggregate functions directly. For example, you cannot specify
    SUM(AVG(col1))
     in the aggregate expression function columns. Use nested aggregations by adding multiple aggregate transformations in the mapping.
  • You can parameterize the
    GROUP BY
     fields and aggregate functions in a mapping task.
  • When you use STDDEV or VARIANCE functions for an expression that consists of a single value and run the mapping, the result of the function is NULL. When you run the mapping without pushing it down, the result of the function is 0.
  • During the field mapping, you must map only the fields that you added in the
    GROUP BY
     port. Else, the mapping runs in the non-PDO mode with an error message.
  • When you do not specify a port from an Amazon S3 flat file source in the
    GROUP BY
     clause of the aggregate function and map the port to a Redshift target, the mapping task runs successfully in the non-PDO mode with the following message:
    Pushdown optimization to the source stops before transformation [Aggregator] because [f_varchar] is a non-group by passthrough port, which is not allowed.
    The mapping fails when you push down a mapping with an Amazon S3 Avro or Parquet source.
0 COMMENTS
We’d like to hear from you!