Table of Contents

Search

  1. Preface
  2. Part 1: Introduction to Amazon Redshift connectors
  3. Part 2: Data Integration with Amazon Redshift V2 Connector
  4. Part 3: Data Integration with Amazon Redshift Connector

Amazon Redshift Connectors

Amazon Redshift Connectors

Amazon Redshift V2 lookups in mappings

Amazon Redshift V2 lookups in mappings

You can create lookups for objects in an Amazon Redshift V2 mapping. You can retrieve data from an Amazon Redshift V2 lookup object based on the specified lookup condition.
Use an Amazon Redshift V2 Lookup transformation to look up data in an Amazon Redshift object. For example, the source table includes the customer code, but you want to include the customer name in the target table to make summary data easy to read. You can use the Amazon Redshift V2 Lookup transformation to look up the customer name in another Amazon Redshift object.
You can add the following lookups to a mapping:
  • Connected with cached or uncached
  • Unconnected with cached
  • Dynamic lookup cache
Use the JDBC URL specified in the connection properties to create lookups.
The following table describes the Amazon Redshift V2 lookup object properties that you can configure in a Lookup transformation:
Property
Description
Connection
Name of the lookup connection.
You can select an existing connection, create a new connection, or define parameter values for the lookup connection property.
If you want to overwrite the lookup connection properties at runtime, select the
Allow parameter to be overridden at run time
option.
Source Type
Type of the source object. Select Single Object or Parameter.
Parameter
A parameter file where you define values that you want to update without having to edit the task.
Select an existing parameter for the lookup object or click
New Parameter
to define a new parameter for the lookup object.
The
Parameter
property appears only if you select parameter as the lookup type.
If you want to overwrite the parameter at runtime, select the
Allow parameter to be overridden at run time
option.
When the task runs, the Secure Agent uses the parameters from the file that you specify in the advanced session properties.
Lookup Object
Name of the lookup object for the mapping.
Multiple Matches
Behavior when the lookup condition returns multiple matches. You can return all rows, any row, the first row, the last row, or an error.
You can select from the following options in the lookup object properties to determine the behavior:
  • Return first row
  • Return last row
  • Return any row
  • Return all rows
  • Report error
Filter
Not applicable
Sort
Not applicable
The following table describes the Amazon Redshift V2 advanced lookup properties that you can configure in a Lookup transformation:
Property
Description
S3 Bucket Name
1
Amazon S3 bucket name for staging the data.
You can also specify the bucket name with the folder path. If you provide an Amazon S3 bucket name that is in a different region than the Amazon Redshift cluster, you must configure the
REGION
attribute in the Unload command options.
Enable Compression
1
Compresses the staging files into the Amazon S3 staging directory.
The task performance improves when the Secure Agent compresses the staging files. Default is selected.
Staging Directory Location
1
Location of the local staging directory.
When you run a task in Secure Agent runtime environment, specify a directory path that is available on the corresponding Secure Agent machine in the runtime environment.
Specify the directory path in the following manner:
<staging directory>
For example,
C:\Temp
. Ensure that you have the write permissions on the directory.
Does not apply to
elastic mappings
.
Unload Options
1
Unload command options.
Add options to the Unload command to extract data from Amazon Redshift and create staging files on Amazon S3. Provide an Amazon Redshift Role Amazon Resource Name (ARN).
You can add the following options:
  • DELIMITER
  • ESCAPE
  • PARALLEL
  • NULL
  • AWS_IAM_ROLE
  • REGION
  • ADDQUOTES
For example: DELIMITER = \036;ESCAPE = OFF;NULL=text;PARALLEL = ON;AWS_IAM_ROLE=arn;aws;iam;;<account ID>;role/<role-name>;REGION = ap-south-1
You cannot use the NULL option in an
elastic mapping
.
Specify a directory on the machine that hosts the Secure Agent.
If you do not add the options to the Unload command manually, the Secure Agent uses the default values.
Treat NULL Value as NULL
1
Retains the null values when you read data from Amazon Redshift.
Encryption Type
1
Encrypts the data in the Amazon S3 staging directory.
You can select the following encryption types:
  • None
  • SSE-S3
  • SSE-KMS
  • CSE-SMK
You can only use
SSE-S3
encryption in an
elastic mapping
.
Default is None.
Download S3 Files in Multiple Parts
1
Downloads large Amazon S3 objects in multiple parts.
When the file size of an Amazon S3 object is greater than 8 MB, you can choose to download the object in multiple parts in parallel. Default is 5 MB.
Does not apply to
elastic mappings
.
Multipart Download Threshold Size
1
The maximum threshold size to download an Amazon S3 object in multiple parts.
Default is 5 MB.
Does not apply to
elastic mappings
.
Schema Name
Overrides the default schema name.
You cannot configure a custom query when you use the schema name.
Source Table Name
Overrides the default source table name.
When you select the source type as
Multiple Objects
or
Query
, you cannot use the
Source Table Name
option.
Pre-SQL
The pre-SQL commands to run a query before you read data from Amazon Redshift. You can also use the UNLOAD or COPY command. The command you specify here is processed as a plain text.
Post-SQL
The post-SQL commands to run a query after you write data to Amazon Redshift. You can also use the UNLOAD or COPY command. The command you specify here is processed as a plain text.
Select Distinct
1
Selects unique values.
The Secure Agent includes a
SELECT DISTINCT
statement if you choose this option. Amazon Redshift ignores trailing spaces. Therefore, the Secure Agent might extract fewer rows than expected.
If you select the source type as query or use the
SQL Query
property and select the
Select Distinct
option, the Secure Agent ignores the
Select Distinct
option.
SQL Query
1
Overrides the default SQL query.
Enclose column names in double quotes. The SQL query is case sensitive. Specify an SQL statement supported by the Amazon Redshift database.
When you specify the columns in the SQL query, ensure that the column name in the query matches the source column name in the mapping.
Temporary Credential Duration
1
The time duration during which an IAM user can use the dynamically generated temporarily credentials to access the AWS resource. Enter the time duration in seconds.
Default is 900 seconds.
If you require more than 900 seconds, you can set the time duration up to a maximum of 12 hours in the AWS console and then enter the same time duration in this property.
Tracing Level
Use the verbose tracing level to get the amount of detail that appears in the log for the Source transformation.
1
Does not apply to uncached lookups.
For more information about the Lookup transformation, see
Transformations
.