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 sources in mappings

Amazon Redshift sources in mappings

In a mapping, you can configure a Source transformation to represent a single Amazon Redshift source or multiple Amazon Redshift sources.
You can use multiple related Amazon Redshift standard objects as a source. You can select a standard object as the primary source, then you add one or more child objects.
The following table describes the Amazon Redshift source properties that you can configure in a Source transformation:
Property
Description
Connection
Name of the source connection.
Source type
Type of the source object. Select Single Object, Multiple Objects, Query, or Parameter.
Object
Name of the source object. Select the source object for a single source.
The following table describes the Amazon Redshift query options that you can configure in a Source transformation:
Property
Description
Filter
Filter value in a read operation. Click
Configure
to add conditions to filter records and reduce the number of rows that the Secure Agent reads from the source.
You can specify the following filter conditions:
  • Not parameterized
    . Use a basic filter to specify the object, field, operator, and value to select specific records.
  • Completely parameterized
    . Use a parameter to represent the field mapping.
  • Advanced
    . Use an advanced filter to define a more complex filter condition that uses the Amazon Redshift query format.
Sort
Not applicable.
The following table describes the Amazon Redshift source advanced properties that you can configure in a Source transformation:
Advanced Property
Description
S3 Bucket Name
Amazon S3 bucket name for the Amazon Redshift target data.
Use an S3 bucket in the same region as your Amazon Redshift cluster.
Enable Compression
Compresses staging files before writing the files to Amazon Redshift.
Task performance improves when the runtime environment compresses the staging files.
Default is selected.
Staging Directory Location
Amazon Redshift staging directory.
When you run a task in Secure Agent runtime environment, specify a directory path that is available on each Secure Agent machine in the runtime environment.
When you run a task in Hosted Agent runtime environment, leave the staging directory blank. The Hosted Agent creates a directory at a temporary location.
UnloadOptions Property File
Unload command options.
Add options to the unload command to write data from an Amazon Redshift object to an S3 bucket. You can add the following options:
  • DELIMITER
  • PARALLEL
  • ESCAPE
  • AWS_IAM_ROLE
When you run a task in the Secure Agent runtime environment, either specify the path of the property file that contains the unload options or specify the unload options directly in the
UnloadOptions Property File
field.
When you run a task in the Hosted Agent runtime environment, specify options directly in the
UnloadOptions Property File
field.
Turn on S3 Client Side Encryption
Indicates that the Secure Agent encrypts data by using a private encryption key.
Encryption Type
Select the source encryption type. You can select from the following encryption types:
  • SSE-S3
  • SSE-KMS
Default is
SSE-S3
.
Enable Downloading S3 Files in Multiple Parts
Downloads large Amazon S3 objects in multiple parts.
When the file size of an Amazon S3 object is greater than 5 MB, you can choose to download the object in multiple parts in parallel.
Part Size
Specifies the part size of an object. Default is 5 MB.
Infa Advanced Filter
Not applicable for Amazon Redshift Connector.
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.
SQL Query
Overrides the default query. Enclose column names in double quotes. The SQL query is case sensitive. Specify an SQL statement supported by the Amazon Redshift database.
Number of Sorted Ports
Number of columns used when sorting rows queried from the source. The agent adds an ORDER BY clause to the default query when it reads source rows. The ORDER BY clause includes the number of ports specified, starting from the top of the transformation. When you specify the number of sorted ports, the database sort order must match the session sort order.
Default is 0.
Select Distinct
Selects unique values. The agent includes a SELECT DISTINCT statement if you choose this option. Amazon Redshift ignores trailing spaces. Therefore, the agent might extract fewer rows than expected.
Source Table Name
You can override the default source table name.
Tracing Level
Sets the amount of detail that appears in the log file.
You can choose terse, normal, verbose initialization, or verbose data. Default is normal.
Do not use single quotes in the
WHERE
clause of a filter or query.