PowerExchange for Amazon Redshift User Guide

Back Next

Supported Pushdown Optimization Functions and Operators

The following table summarizes the availability of pushdown functions in an Amazon Redshift database:

Function	Pushdown	Function	Pushdown	Function	Pushdown
ABORT()	No	INITCAP()	Yes	REG_MATCH()	No
ABS()	Yes	INSTR()	Yes	REG_REPLACE	No
ADD_TO_DATE()	Yes	IS_DATE()	No	REPLACECHR()	No
AES_DECRYPT()	No	IS_NUMBER()	No	REPLACESTR()	No
AES_ENCRYPT()	No	IS_SPACES()	No	REVERSE()	No
ASCII()	No	ISNULL()	Yes	ROUND(DATE)	No
AVG()	Yes	LAST()	No	ROUND(NUMBER)	Yes
CEIL()	Yes	LAST_DAY()	Yes	RPAD()	Yes
CHOOSE()	No	LEAST()	No	RTRIM()	Yes
CHR()	Yes	LENGTH()	Yes	SET_DATE_PART()	No
CHRCODE()	No	LN()	Yes	SIGN()	Yes
COMPRESS()	No	LOG()	No	SIN()	Yes
CONCAT()	Yes	LOOKUP	No	SINH()	No
COS()	Yes	LOWER()	Yes	SOUNDEX()	No
COSH()	No	LPAD()	Yes	SQRT()	Yes
COUNT()	Yes	LTRIM()	Yes	STDDEV()	Yes
CRC32()	No	MAKE_DATE_TIME()	No	SUBSTR()	Yes
CUME()	No	MAX()	Yes	SUM()	Yes
DATE_COMPARE()	Yes	MD5()	No	SYSTIMESTAMP()	Yes
DATE_DIFF()	Yes	MEDIAN()	No	TAN()	Yes
DECODE()	Yes	METAPHONE()	No	TANH()	No
DECODE_BASE64()	No	MIN()	Yes	TO_BIGINT	Yes
DECOMPRESS()	No	MOD()	YesS	TO_CHAR(DATE)	Yes
ENCODE_BASE64()	No	MOVINGAVG()	No	TO_CHAR(NUMBER)	Yes
EXP()	Yes	MOVINGSUM()	No	TO_DATE()	Yes
FIRST()	No	NPER()	No	TO_DECIMAL()	Yes
FLOOR()	Yes	PERCENTILE()	No	TO_FLOAT()	Yes
FV()	No	PMT()	No	TO_INTEGER()	Yes
GET_DATE_PART()	Yes	POWER()	Yes	TRUNC(DATE)	Yes
GREATEST()	No	PV()	No	TRUNC(NUMBER)	Yes
IIF()	Yes	RAND()	No	UPPER()	Yes
IN()	Yes	RATE()	No	VARIANCE()	Yes
INDEXOF()	No	REG_EXTRACT()	No

You can use the following operators lists to use in an Amazon Redshift database:

+ , - , *, /, %

>, = , >= , <=

AND, OR, NOT

||, !=, ^=

The following table lists the transformation types that PowerExchange for Amazon Redshift supports for the source-side or full pushdown optimization:

Transformation	Source-Side	Full
Aggregator	Yes	Yes
Expression	Yes	Yes
Filter	Yes	Yes
Joiner	Yes	Yes
Lookup	Yes	Yes
Router	No	Yes
Sorter	Yes	Yes If you select the Distinct rows option in the Sorter transformation to push the transformation using full pushdown optimization, the Data Integration Service does not ignore the ORDER BY clause. If you select the All rows option in the Sorter transformation, the Data Integration Service ignores the ORDER BY clause.
Union	Yes	Yes
Update Strategy	No	Yes To push the Update Strategy transformation using full pushdown optimization, do not select the Forward Rejected Rows option in the Update Strategy transformation. Additionally, in the Run-time tab in the mapping, select the value of the Pushdown Compatibility property as Rows do not have the same key .