Configuring pushdown optimization for Amazon Redshift using the ODBC Connector

Back Next

Overview

When you enable pushdown optimization for a task, the task pushes down the entire transformation logic as SQL queries to the underlying database for processing and the database runs the query. Since the entire transformation logic is encapsulated in an SQL query and there is no back-and-forth data movement between the database and the Informatica engine, the task is processed faster.

It is important to know what your primary use case is before you configure pushdown optimization. If your use case is to process data in the cloud for a mapping that reads from a Cloud Data Warehouse or Cloud Data Lake and writes to a Cloud Data Warehouse target such as Amazon Redshift, you can use pushdown optimization to push the processing logic to the warehouse or data lake using the applicable native connector as the source and Redshift as the target. The native connectors offer a wide range of capabilities that you can explore. For more information about configuring pushdown optimization and the supported functionalities using Amazon Redshift, see the help for Amazon Redshift Connector.

However, if you are using your on-premises data warehouse or any ODBC-based targets, ODBC based pushdown using the ODBC Connector is the better choice. You do not need any separate license to use ODBC pushdown. In this article, we'll configure pushdown optimization for a task that uses ODBC Connector to push transformation logic to process in the Amazon Redshift source and target databases. The ODBC connection must use the Redshift subtype in the connection.

You can set the pushdown optimization for the ODBC connection type that uses Amazon ODBC Redshift drivers to enhance the mapping performance. You must create a data source name in the ODBC datasource administrator. We will show you how to optimize an ODBC task to read from or write to Amazon Redshift. You can configure source or full pushdown for the task. Perform the following steps to optimize an Amazon Redshift ODBC task:

Configure the Amazon Redshift ODBC driver.

Create an ODBC connection with the ODBC subtype as Redshift and configure the Amazon Redshift database properties to which you want to connect.

Create a mapping that uses the configured ODBC connection and enable the task for pushdown optimization.

After you create an Amazon Redshift ODBC connection, select the value of the

Pushdown Optimization

property as

Full

To Source

in the advanced session properties. You can check the session log to verify that the pushdown optimization has taken place.

When you run the task enabled with pushdown optimization, the task converts the transformation logic to an SQL query. The task sends the query to the database, and the database uses the database resources to run the query. You can also create a temporary view, temporary sequence, and push logic across databases or schemas. For information about the advanced session properties that you can use with pushdown optimization using an Amazon Redshift ODBC connection, see the topic "Advanced Session Properties" in Tasks in the Data Integration help.

Amazon Redshift does not support upsert operations in a full pushdown optimization.

Rename Saved Search

Table of Contents

Configuring pushdown optimization for Amazon Redshift using the ODBC Connector

Configuring pushdown optimization for Amazon Redshift using the ODBC Connector

Overview

Overview