Amazon Redshift Connector Best Practices

Back Next

Pushdown Optimization

You can use pushdown optimization to push transformation logic to the source or target databases. Use pushdown optimization when you use the database resources to improve the task performance.

When you run a mapping configured for pushdown optimization, the mapping converts the transformation logic to an SQL query. The mapping sends the query to the database and then database executes the query. You can use full and source pushdown optimization for the ODBC connection type that uses Amazon ODBC Redshift drivers for mapping.

For a given mapping, the pushdown optimizer analyzes the transformation logic. If you select the full pushdown optimization to pushdown the data from the transformation to Amazon Redshift, the optimizer generates the appropriate SQL statement automatically.

When you perform an insert operation, the pushdown optimization generates the following sample transaction:

BEGIN;
CREATE TEMP VIEW PM_(…) AS SELECT (…) …
CREATE TEMP VIEW PM (…) AS SELECT (…) A JOIN (SELECT …) LEFT JOIN
INSERT INTO STG_CUSTOMER(…) SELECT (…)
INSERT INTO STG_CUSTOMER(…) SELECT(…)
DROP VIEW IF EXISTS …
DROP VIEW IF EXISTS …
COMMIT

The following lists the transformation types that you can use for the mapping configured for pushdown optimization:

Two sources in multiple pipelines

Expressions

Lookups with SQL override

Filters

Routers

Target

For more information about Amazon Redshift pushdown optimization, supported functions, and operators, see the following guides:

Amazon Redshift pushdown optimization on cloud, see

Amazon Redshift Connector User Guide

Amazon Redshift pushdown optimization on Big Data Management, see

PowerExchange for Amazon Redshift User Guide