Overview
Configuration is provided for establishing connections with the Amazon Redshift service. The configuration is used in the REST Connection Manager.
Setup
Press 
icon to get more information about the connection parameters.
Obtain data
Use REST Source component to read data from Amazon Redshift.
Insert data
Use REST Destination component to insert/update/delete rows in a Amazon Redshift table.
Quick Start
In this QS, we will show you step-by-step how to create a connection to an Amazon Redshift REST API using COZYROC's REST Connection Manager.
Step 1. Run "Visual Studio" and right click on Connection Managers tab and select New Connection.
Step 2. Then "Add SSIS Connection Manager" popup window opens, scroll down and select "Connection managers for REST Connection" then click Add.
Step 3. From the Configuration drop down menu select Amazon Redshift.
Step 4. Provide the relevant [region] in the "Server" address field, as well as the Access Key and Secret Key.
Step 5. Provide the relevant Database, Schema and either a WorkgroupName or a ClusterIdentifier
Step 6. Input a SecretArn containing credentials that can authenticate you to the database. For more information on secret creation and AWS Secret Manager click here.
Step 7. Press the Test Connection button. When you see Test Connection succeeded, click OK.
Congratulations! You have successfully created a connection to an Amazon Redshift REST API using COZYROC's REST Connection Manager.
In this guide, we will show you how to read data from Amazon Redshift Service resource using the COZYROC REST Source component.
Step 1. Select an existing or create a new "Data Flow Task".
Step 2. Open the "SSIS Toolbox" and drag and drop a COZYROC "REST Source" component.
Step 3. Double click on the "REST Source" component and select an Amazon Redshift connection from the list. Select from available Amazon Redshift resources listed in the second list below.
Step 4. Click on the "Columns" tab and select the Amazon Redshift columns you want to read.
Step 5. Click the "Preview" tab to make sure all necessary resource columns are included.
Bravo! You have now learned how to read data from the **Amazon Redshift* service.
In this guide, we will show how to write data to the Amazon Redshift service resource using the COZYROC REST Destination component.
Step 1. Select an existing or create a new "Data Flow Task".
Step 2. Open the "SSIS Toolbox" and drag and drop a COZYROC "REST Destination" component.
Step 3. Double click on the "REST Destination" component and select an Amazon Redshift connection from the list. Also, select resource action and the Amazon Redshift resource you want to work on.
Step 4. Click on the "Mapping" tab and map the input columns for the process you are configuring.
Done. You have now learned how to write data into an Amazon Redshift resource.
Configuration
Base URL address: https://redshift-data.[region].amazonaws.com/.
- Amazon AWS Signature v4
-
The authentication uses a parameters-based authentication.
The authentication has the following user-defined parameters:
- Access Key: Required. Specify access key.
- Secret Key: Required. Specify secret key.
- Database: Required. The name of the database.
- Schema: Required. The schema used for the table retrieval and statement execution.
- SecretArn: Required. The name or ARN of the secret that enables access to the database.
- ClusterIdentifier: Specify ClusterIdentifier.
- WorkgroupName: Specify WorkgroupName.
- TablePattern: Specify if you want only a subset of the tables to be returned.
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
{{=aws4s.authorizationHeader(request, connection)}} -
Host:
{{=request.signedRequest.headers['Host']}} -
X-Amz-Content-Sha256:
{{=request.signedRequest.headers['X-Amz-Content-Sha256']}} -
X-Amz-Date:
{{=request.signedRequest.headers['X-Amz-Date']}}
Documentation: https://docs.aws.amazon.com/redshift/latest/mgmt/data-api-secrets.html.
- Amazon AWS Signature v4 Temporary Credentials
-
The authentication uses a parameters-based authentication.
The authentication has the following user-defined parameters:
- Access Key: Required. Specify access key.
- Secret Key: Required. Specify secret key.
- Database: Required. The name of the database.
- Schema: Required. The schema used for the table retrieval and statement execution.
- DbUser: Required. A user with access to the database.
- ClusterIdentifier: Specify ClusterIdentifier.
- WorkgroupName: Specify WorkgroupName.
- TablePattern: Specify if you want only a subset of the tables to be returned.
The following request parameters will be automatically processed during the authentication process:
-
Authorization:
{{=aws4s.authorizationHeader(request, connection)}} -
Host:
{{=request.signedRequest.headers['Host']}} -
X-Amz-Content-Sha256:
{{=request.signedRequest.headers['X-Amz-Content-Sha256']}} -
X-Amz-Date:
{{=request.signedRequest.headers['X-Amz-Date']}}
Documentation: https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html#data-api-calling-considerations-authentication.
Based on resource template BaseExtended.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=_.map(response.Databases, function(item) { return { Name: item }; })}}.
The action uses POST method.The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ListDatabases
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ListDatabases.html.
-
X-Amz-Target:
- Name
Template: ShortText.
Based on resource template BaseExtended.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=_.map(response.Schemas, function(item) { return { Name: item }; })}}.
The action uses POST method.The action has the following user-defined parameters:
- SchemaPattern: A pattern to filter results by schema name. Within a schema pattern, "%" means match any substring of 0 or more characters and "_" means match any one character. Optional.
- ConnectedDatabase: A database name. The connected database is specified when you connect with your authentication credentials. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ListSchemas
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ListSchemas.html.
- Name
Template: ShortText.
Based on resource template BaseExtended.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.Statements}}.
The action uses POST method.The action has the following user-defined parameters:
- RoleLevel: A value that filters which statements to return in the response. Optional.
- StatementName: The name of the SQL statement specified as input to 'BatchExecuteStatement' or 'ExecuteStatement' to identify the query. Optional.
- Status: The status of the SQL statement to list. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ListStatements
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ListStatements.html.
- Id
Template: ShortText.
- CreatedAt
Template: UnixTime.
- IsBatchStatement
Data type: DT_BOOL
- QueryParameters
Field components:
- An array.
- Contains the following components: name, value.
- QueryString
Template: ShortText.
- QueryStrings
Template: LongText. A composite field.
- ResultFormat
Template: ShortText.
- SecretArn
Template: ShortText.
- SessionId
Template: ShortText.
- StatementName
Template: ShortText.
- Status
Template: ShortText.
- UpdatedAt
Template: UnixTime.
Based on resource template BaseExtended.
- [Read] action
-
Endpoint URL address:
/.
The result is extracted from:{{=response.Tables}}.
The action uses POST method.The action has the following user-defined parameters:
- SchemaPattern: A pattern to filter results by schema name. Within a schema pattern, "%" means match any substring of 0 or more characters and "_" means match any one character. Optional.
- ConnectedDatabase: A database name. The connected database is specified when you connect with your authentication credentials. Optional.
- TablePattern: A pattern to filter results by table name. Within a table pattern, "%" means match any substring of 0 or more characters and "_" means match any one character. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ListTables
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ListTables.html.
- name
Template: ShortText.
- schema
Template: ShortText.
- type
Template: ShortText.
Based on resource template Base.
- [Read] action
-
The result is extracted from:{{=Redshift.executeCustomStatement(parameters)}}.
The result is paginated.The action has the following user-defined parameters:
- SQL: Required. The SQL statement text to run.
- Timeout: Specify timeout in seconds. Optional.
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html.
- [External]
-
The external fields list is extracted from:
{{=Redshift.getCustomQueryFields(parameters)}}.
- [Read] action
-
The result is extracted from:{{=Redshift.getData(resource, parameters)}}.
The result is paginated.The action has the following user-defined parameters:
- Where: Specify filter. Optional.
- Timeout: Specify timeout in seconds. Optional.
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html.
- [Create] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response)}}.The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ExecuteStatement -
application/x-amz-json-1.1:
{{=Redshift.create( resource, parameters, batch )}}
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html.
-
X-Amz-Target:
- [Update] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response)}}.
The action uses POST method.The action has the following user-defined parameters:
- KeyFields: Required. Specify comma (,) separated list of key fields.
- Where: Specify filter. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ExecuteStatement -
application/x-amz-json-1.1:
{{=Redshift.update( resource, parameters, item )}}
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html.
- [Upsert] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response)}}.
The action uses POST method.The action has the following user-defined parameters:
- KeyFields: Required. Specify comma (,) separated list of key fields.
- Where: Specify filter. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ExecuteStatement -
application/x-amz-json-1.1:
{{=Redshift.upsert( resource, parameters, item )}}
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html.
- [Delete] action
-
Endpoint URL address:
/.
The result is extracted from:{{=utils.ensureArray(response)}}.
The action uses POST method.The action has the following user-defined parameters:
- KeyFields: Required. Specify comma (,) separated list of key fields.
- Where: Specify filter. Optional.
The following request parameters will be automatically processed:
-
X-Amz-Target:
RedshiftData.ExecuteStatement -
application/x-amz-json-1.1:
{{=Redshift.delete( resource, parameters, item )}}
Documentation: https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_ExecuteStatement.html.
- [External]
-
The external fields list is extracted from:
{{=Redshift.getTableFields(resource)}}.
The external resource list is extracted from: {{=Redshift.getTables() }}.
Based on resource template Base.
- [Read] action
-
The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}}
-
_includeUserParameters:
- [Create] action
-
The action uses POST method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=item}}
-
_includeUserParameters:
- [Update] action
-
The action uses PUT method.The following request parameters will be automatically processed:
-
_includeUserParameters:
{{=parameters}} -
application/json:
{{=item}}
-
_includeUserParameters:
- [Delete] action
-
The action uses DELETE method.
Based on resource template Base.
- [Read] action
-
The result is paginated.The following request parameters will be automatically processed:
-
Content-Type:
application/x-amz-json-1.1 -
application/x-amz-json-1.1:
{{=Redshift.getRequestJson(parameters)}}
-
Content-Type:
- ShortText
Data type: DT_WSTR ( length 255 )
- LongText
Data type: DT_WSTR ( length 1000 )
- DateTime
Data type: DT_DBTIMESTAMP
- Date
Data type: DT_DBDATE
- UnixTime
Data type: DT_DBTIMESTAMP The gathered value is processed with
{{=value && new Date(value * 1000).toUTCString()}}expression.
Knowledge Base
Related documentation
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.