Amazon Redshift Connectors

Back Next

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

SQL ELT 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, Data Integration parses the column values differently when the source and target in the mapping are from the same cluster environment or across different cluster environments. The UNLOAD and COPY commands parse NULL and empty string values differently when the source and target are in different clusters.

You can enable the use

TempTableForRedshiftAPDO

property when you push down functions from mappings where the source and target are across different clusters. When you set the property and run the mapping, the mapping considers NULL and empty string values in the columns as NULL only. However, when the source and target is in the same cluster, Data Integration considers NULL as NULL and empty strings as empty strings in the columns.

Mappings without

SQL ELT optimization

parses the ISNULL() function differently. When you select the Treat NULL Value as NULL option, the mapping considers NULL and empty string values as NULL. When you don't select the Treat NULL Value as NULL option, the mapping considers NULL and empty strings values as empty strings.

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 without

SQL ELT optimization

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.

A mapping enabled for full or source

SQL ELT optimization

which contains a table column with a regular expression argument in the REG_MATCH() function fails. However, the mapping runs successfully when you do not enable

SQL ELT optimization

When you configure the REG_MATCH() expression:

Use double-slashes for parsing expressions in full

SQL ELT optimization

. For example, use REGMATCH(searchstring,'\\D W').

Use single-slash to parse expressions without

SQL ELT optimization

. For example, use REGMATCH(searchstring,'\D\W').

When you pass timestamp and date data values through an ASCII() function, the ASCII() function parses the values differently with and without

SQL ELT optimization

In a mapping without

SQL ELT optimization

, the ASCII() function returns the first digit of the day, while in full and source

SQL ELT optimization

, the function returns the first digit of the year.

For example, to read a date time MM/DD/YYYY HH24:MI:SS.US for a session, the ASCII() value returns the first character of MM without

SQL ELT optimization

and the first character of YYYY with

SQL ELT optimization

As a workaround, you can set the DateTime Format String property value to yyyy-mm-dd in the

Advanced Session Properties

when you enable mappings without

SQL ELT optimization

Rules and guidelines for SQL ELT optimization

Download Guide

Watch

Comments

Cloud Connectivity Homepage

Communities

Knowledge Base

Success Portal

0 COMMENTS

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Amazon Redshift Connectors

Amazon Redshift Connectors

Rules and guidelines for functions

Rules and guidelines for functions

Rules and guidelines for aggregate functions