PowerExchange Adapters for Informatica

PowerExchange for Amazon Redshift

• •You can read data from or write data to the following regions:
• - China(Ningxia)
• - EU(Paris)
• •You can use Amazon Redshift objects as dynamic sources and target in a mapping.
• •You can use octal values of printable and non-printable ASCII characters as a DELIMITER or QUOTE.
• •You can enter pre-SQL and post-SQL commands to run queries for source and target objects in a mapping.
• •You can define an SQL query for read data objects in a mapping to override the default query. You can enter an SQL statement supported by the Amazon Redshift database.
• •You can specify the maximum size of an Amazon S3 object in bytes when you download large Amazon S3 objects in multiple parts.
• •You can read unique values when you read data from an Amazon Redshift source.
• •When you upload an object to Amazon S3, you can specify the minimum size of the object and the number of threads to upload the objects in parallel as a set of independent parts.
• •You can choose to retain an existing target table, replace a target table at runtime, or create a new target table if the table does not exist in the target.
• •You can configure the Update Strategy transformations for an Amazon Redshift target in the native environment.
• •When you write data to Amazon Redshift, you can override the Amazon Redshift target table schema and the table name during run time.
• •When the connection type is ODBC, the Data Integration Service can push transformation logic to Amazon Redshift sources and targets using source-side and full pushdown optimization.
• •You can use Server-Side Encryption with AWS KMS (AWS Key Management Service) on Amazon EMR version 5.16 and Cloudera CDH version 5.15 and 5.16.
• •PowerExchange for Amazon Redshift supports AWS SDK for Java version 1.11.354.

PowerExchange for Amazon S3

• •You can read data from or write data to the following regions:
• - China(Ningxia)
• - EU(Paris)
• - AWS GovCloud (US)
• •You can use Amazon S3 objects as dynamic sources and target in a mapping.
• •When you run a mapping in the native environment or on the Spark engine to read data from an Avro, flat, JSON, ORC, or Parquet file, you can use wildcard characters to specify the source directory name or the source file name.
• •You can add a single or multiple tags to the objects stored on the Amazon S3 bucket to categorize the objects. Each tag contains a key value pair. You can either enter the key value pairs or specify the absolute file path that contains the key value pairs.
• •You can specify the maximum threshold size to download an Amazon S3 object in multiple parts.
• •When you upload an object to Amazon S3, you can specify the minimum size of the object and the number of threads to upload the objects in parallel as a set of independent parts.
• •When you create a data object read or write operation, you can read data present in the FileName port that contains the endpoint name and source path of the file.
• •You can add new columns or modify the columns in the Port tab directly when you create a data object read or write operation.
• •You can copy the columns of the source transformations, target transformations, or any other transformations from the Port tab and paste the columns in the data object read or write operation directly when you create a mapping to read or write an Avro, JSON, ORC, or Parquet file.
• •You can update the Amazon S3 file format without losing the column metadata in the Schema field of the column projection properties even after you configure the column projection properties for another Amazon S3 file format.
• •You can use Server-Side Encryption with AWS KMS (AWS Key Management Service) on Amazon EMR version 5.16 and Cloudera CDH version 5.15 and 5.16.
• •PowerExchange for Amazon S3 supports AWS SDK for Java version 1.11.354.

PowerExchange for Google BigQuery

PowerExchange for HBase

• •When you create an HBase data object, you can select an operating system profile to increase security and to isolate the design-time user environment when you import and preview metadata from a Hadoop cluster.
Note: You can choose an operating system profile if the Metadata Access Service is configured to use operating system profiles. The Metadata Access Service imports the metadata with the default operating system profile assigned to the user. You can change the operating system profile from the list of available operating system profiles.
• •You can use the HBase objects as dynamic sources and targets in a mapping.
• •You can run a mapping on the Spark engine to look up data in an HBase resource.

PowerExchange for HDFS

• •When you create a complex file data object, you can select an operating system profile to increase security and to isolate the design-time user environment when you import and preview metadata from a Hadoop cluster.
Note: You can choose an operating system profile if the Metadata Access Service is configured to use operating system profiles. The Metadata Access Service imports the metadata with the default operating system profile assigned to the user. You can change the operating system profile from the list of available operating system profiles.
• •When you run a mapping in the native environment or on the Spark engine to read data from a complex file data object, you can use wildcard characters to specify the source directory name or the source file name.
You can use the following wildcard characters:

? (Question mark)

The question mark character (?) allows one occurrence of any character.

* (Asterisk)

The asterisk mark character (*) allows zero or more than one occurrence of any character.

• •You can use complex file objects as dynamic sources and targets in a mapping.
• •You can use complex file objects to read data from and write data to a complex file system.
• •When you run a mapping in the native environment or on the Spark engine to write data to a complex file data object, you can overwrite target data, the Data Integration Service deletes the target data before writing new data.
• •When you create a data object read or write operation, you can read the data present in the FileName port that contains the endpoint name and source path of the file.
• •You can now view the data object operations immediately after you create the data object read or write operation.
• •You can add new columns or modify the columns, when you create a data object read or write operation.
• •You can copy the columns of the source transformations, target transformations, or any other transformations and paste the columns in the data object read or write operation directly when you read or write to an Avro, JSON, ORC, or Parquet file.

PowerExchange for Hive

• •You can configure the following target schema strategy options for a Hive target:
• - RETAIN - Retain existing target schema
• - CREATE - Create or replace table at run time
• - APPLYNEWCOLUMNS - Alter table and apply new columns only
• - APPLYNEWSCHEMA - Alter table and apply new schema
• - FAIL - fail mapping if target schema is different
• - Assign Parameter
• •You can truncate an internal or external partitioned Hive target before loading data. This option is applicable when you run the mapping in the Hadoop environment.
• •You can create a read or write transformation for Hive in native mode to read data from Hive source or write data to Hive target.
• •When you write data to a Hive target, you can configure the following properties in a Hive connection:
• - Hive Staging Directory on HDFS. Represents the HDFS directory for Hive staging tables. This option is applicable and required when you write data to a Hive target in the native environment.
• - Hive Staging Database Name. Represents the namespace for Hive staging tables. This option is applicable when you run a mapping in the native environment to write data to a Hive target. If you run the mapping on the Blaze or Spark engine, you do not need to configure the Hive staging database name in the Hive connection. The Data Integration Service uses the value that you configure in the Hadoop connection.

PowerExchange for MapR-DB

PowerExchange for Microsoft Azure Blob Storage

• •You can run mappings in the Azure Databricks environment.
• •You can configure the US government Microsoft Azure end-points.
• •You can compress data in the following formats when you read data from or write data to Microsoft Azure Blob Storage:
• - None
• - Deflate
• - Gzip
• - Bzip2
• - Lzo
• - Snappy
• •You can use Microsoft Azure Blob Storage objects as dynamic sources and targets in a mapping.
• •You can read the name of the file from which the Data Integration Service reads the data at run-time in the native environment.
• •You can configure the relative path in Blob Container Override in the advanced source and target properties.

PowerExchange for Microsoft Azure Cosmos DB SQL API

• •You can run mappings in the Azure Databricks environment. Databricks support for PowerExchange for Microsoft Azure Cosmos DB SQL API is available for technical preview. Technical preview functionality is supported but is unwarranted and is not production-ready. Informatica recommends that you use these features in non-production environments only.

PowerExchange for Microsoft Azure Data Lake Store

• •You can run mappings in the Azure Databricks environment.
• •You can use complex data types, such as array, struct, and map, in mappings that run in the Hadoop environment. With complex data types, the respective engine directly reads, processes, and writes hierarchical data in Avro, JSON, and Parquet complex files. For an intelligent structure source, you can configure only the read operation.
• •You can create mappings to read and write Avro and Parquet files that contain only primitive data types in the native environment.
• •You can select a directory as a source in a mapping to read multiple files from the directory.
• •You can use Microsoft Azure Data Lake Store objects as dynamic sources and targets in a mapping.
• •You can create a Microsoft Azure Data Lake Store target using the Create Target option.

PowerExchange for Microsoft Azure SQL Data Warehouse

• •You can run mappings in the Azure Databricks environment.
• •You can configure the US government Microsoft Azure end-points in mappings that run in the native environment and on the Spark engine.
• •You can generate error files in the Microsoft Azure Blob Storage container. The error files contain rejected rows and the cause for the rejected rows.
• •You can define the batch size in advance target properties in the native environment.
• •You can configure full pushdown optimization to push transformation logic to source databases and target databases. Use pushdown optimization to improve task performance by using the database resources.
• •You can use Microsoft Azure SQL Data Warehouse objects as dynamic sources and targets in a mapping.

PowerExchange for Salesforce

• •You can use version 43.0 and 44.0 of Salesforce API to create a Salesforce connection and access Salesforce objects.
• •You can configure OAuth for Salesforce connections.
• •You can configure the native expression filter for the source data object operation.
• • You can parameterize the following read operation properties for a Salesforce data object:
• - SOQL Filter Condition
• - CDC Start Timestamp
• - CDC End Timestamp
• - PK Chunking Size
• - PK Chunking startRow ID
You can parameterize the following write operation properties for a Salesforce data object:
• - Set prefix for BULK success and error files
• - SFDC Success File Directory
• - Set the location of the BULK error files

PowerExchange for Snowflake

• •You can configure Okta SSO authentication by specifying the authentication details in the JDBC URL parameters of the Snowflake connection.
• •You can configure an SQL override to override the default SQL query used to extract data from the Snowflake source. Specify the SQL override in the Snowflake data object read operation properties.
• •You can choose to compress the files before writing to Snowflake tables and optimize the write performance. In the advanced properties. You can set the compression parameter to On or Off in the Additional Write Runtime Parameters field in the Snowflake data object write operation advanced properties.
• •The Data Integration Service uses the Snowflake Spark Connector APIs to run Snowflake mappings on the Spark engine.
• •You can read data from and write data to Snowflake that is enabled for staging data in Azure or Amazon.

PowerExchange for Teradata Parallel Transporter API

• •You can specify a replacement character to use in place of an unsupported Teradata unicode character in the Teradata database while loading data to targets.
• •If you specified a character used in place of an unsupported character while loading data to Teradata targets, you can specify version 8.x - 13.x or 14.x and later for the target Teradata database. Use this attribute in conjunction with the Replacement Character attribute. The Data Integration Service ignores this attribute if you did not specify a replacement character while loading data to Teradata targets.
• •When you write data to Teradata, you can override the Teradata target table schema and the table name during run time.