Amazon Redshift Connectors

Back Next

Rules for SQL ELT optimization in mappings that read from and write to Amazon Redshift

Rules for
SQL ELT optimization
in mappings that read from and write to Amazon Redshift

Consider the following guidelines when you configure full

SQL ELT optimization

for mappings that read from or write to Amazon Redshift:

When the Amazon Redshift source and target are in the same cluster, the Secure Agent does not use the UNLOAD and COPY commands to stage the data on Amazon S3 and then write to Amazon Redshift. The Secure Agent directly performs an insert, update, upsert, delete, or data driven operation to write the data to Amazon Redshift and the performance of the task is improved.

When you configure an update operation for data that contains the Time column for a mapping enabled with full

SQL ELT optimization

and you override the target query using the value specified in the

Override Target Query

field from the advanced target properties, the task runs but data is not processed.

When you assume an IAM role and run mappings enabled with source or full

SQL ELT optimization

to read data from Redshift, the assume role is not honored.

To honor the assume role and run mappings successfully, you must specify the AWS_IAM_ROLE property and set its value in the

Unload Options

field of the Source transformation.

For example,

AWS_IAM_ROLE=arn:aws:iam::0093:role/redshift_role

If the source and target objects in a mapping point to Amazon S3 buckets in different regions, specify the

REGION

attribute in the

COPY

command to set the Amazon S3 bucket for target in the same region as Amazon S3 bucket for source.

If the data that you read contains delimiters (|), quotes ("), and escape (\) characters and you set the

Unload Options

field in the Source transformation to

ADDQUOTES;DELIMITER = \174;

, the mapping fails.

To avoid this error, set the following properties in the

Copy Options

field in the Target transformation:

ESCAPE;REMOVEQUOTES;CSV=OFF;QUOTE=OFF;DELIMITER = \174;

When you read data from a single column table of Bigint, Datetime, and Boolean data type that contains NULL values, the null values are not written to the target.

To avoid this error, you must set

IGNOREBLANKLINES

in the

Copy Options

field of the Target transformation.

When you configure a Source transformation in a mapping to read data and you set

ESCAPE=OFF

or ON and the

ADDQUOTES

in the

Unload Options

field to add escape characters and quotes in the data, null values are written to the target.

To avoid this, you must add

QUOTE=\042

in the

Copy Options

field of the Target transformation and then run the mapping.

If the data contains an escape (\) character when you read data, you must specify ESCAPE=OFF in the

Unload Options

field of the Source transformation. If you do not set the property, the escape character gets duplicated in the target. For example, the data output 12\12\2012 appears as 12\\12\\2012.

When you read from and write data that contains float4 or double values, the float values show a slight change in the target.

When you specify a user defined function in a transformation, the function name is not displayed correctly in the session log.

If the mapping contains a Sequence Generator transformation, ensure that you map the NEXTVAL output field to the target. Do not map the CURRVAL output field to the target. Else, the mapping task does not partially push down the mapping logic to the point where the transformation is supported and runs without

SQL ELT optimization

When you configure a mapping with multiple objects at source where the table name and column name are the same or the table name is substring of the column name, the mapping fails.

You cannot perform data driven operations on target objects of the timestamptz data type.

When you run a mapping in full

SQL ELT optimization

with multi-objects at source using advance filters, the UNLOAD and COPY commands do not work across different clusters and the mapping fails.

When you define pre-SQL or post-SQL queries that modify the same object in a mapping where the source and target belong to different clusters, the task fails at runtime in full

SQL ELT optimization

When a mapping contains multiple pipelines, the logic of only one pipeline is pushed to the source if an unsupported function is encountered. The other pipelines run without

SQL ELT optimization

Rules for source
SQL ELT optimization
in mappings that read from Amazon Redshift

Consider the following guidelines when you configure source

SQL ELT optimization

for mappings that read from Amazon Redshift:

You can configure an SQL query or custom query in the advanced source property to push the mapping to Amazon Redshift.

When you use a custom query as a source or an SQL override, the table name alias is not generated as expected. The name starts with

"INF"

in the

SQL ELT optimization

query.

When you run a mapping by overriding the SQL query in full

SQL ELT optimization

, where the source column names are aliased, the mapping fails. Ensure that the alias names and the source column names are the same.

You cannot push a Router transformation with multiple output groups to the Amazon Redshift source.

When you use the query source type to read from Amazon Redshift, you can choose to retain the field metadata and save the mapping. Even if you edit the query and run the mapping, the field metadata specified at design time is retained.

You cannot set the read mode to

Direct

in Source and Lookup transformations.

COPY command options are not applicable for the timestamptz data type.

When you use the

[^\x00-\x7F]+

pattern in a REG_REPLACE() function and run a mapping with

SQL ELT optimization

, both the non-ASCII characters and spaces are replaced in the target. But, when you run the same mapping without

SQL ELT optimization

, only the non-ASCII characters are replaced and the spaces are retained in the target.

For more information about the patterns you can use in an expression, see Pattern-matching conditions.

Rules and guidelines for adding multiple source objects

Consider the following rules and guidelines when you add multiple source objects:

You must specify double quotes for the table name when you use a reserved word for the table name and the column name.

You cannot use a self join when you add multiple source objects.

When you use special characters in column names for an advanced relationship, the query formed is not correct and the mapping task fails.

You can use the full outer-join condition only with the

and

AND

operators.

When you override the schema name and configure an advanced filter on a related source object, the Secure Agent applies the advanced filter only on the parent object and not on the related source object.

When you select parent and child objects that have a primary key and foreign key relationship, and the foreign key of the related object is also a primary key in the table, the mapping task fails when you create a target.

When you select the Multiple Objects source type, add a source object, for example,

emp

, and define a primary key and foreign key relationship on different columns, for example,

emp.id

and

dept.d_id

, the mapping fails with the following error:

[FATAL] Unload/Copy command failed with error: Invalid operation: column emp.d_id does not exist.

The Select Related Objects list shows the join condition for the

dept

related object

asemp.d_id=dept.d_id

, even though

theemp

table does not have

d_id

column.

When you select the Multiple Objects source type, you cannot use a period(.) in the table name.

Rename Saved Search

Table of Contents

Amazon Redshift Connectors

Amazon Redshift Connectors

Rules for SQL ELT optimization in mappings that read from and write to Amazon Redshift

Rules for SQL ELT optimization in mappings that read from and write to Amazon Redshift

Rules for source SQL ELT optimization in mappings that read from Amazon Redshift

Rules and guidelines for adding multiple source objects

Rules for
SQL ELT optimization
in mappings that read from and write to Amazon Redshift

Rules for source
SQL ELT optimization
in mappings that read from Amazon Redshift